MySQL Replication

MySQL Replication Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves). Replication is asynchronous – your replication slaves do not need to be connected permanently to receive updates from the master, which means that updates can occur over long-distance connections and even temporary solutions such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases and even selected tables within a database.

Master

Bewerk het bestand

# vi /usr/local/my.cnf

Vul de andere server als IP adres in

[mysqld]
log-bin
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 192.168.100.69
master-user = slave1_user
master-password = slave1_password
master-connect-retry = 60
replicate-do-db = pdns
binlog-do-db = pdns
binlog-ignore-db = mysql

Aanmaken van de juiste user met rechten

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

Dump maken van de pdns database, dit voor latere import.

# mysqldump -u root -p pdns > export.sql

Nu dien je uit te loggen binnen MySQL omgeving. Herstart de MySQL server om de instellingen actief te maken.

# /usr/local/etc/rc.d/mysql-server restart

Replication slave

/etc/my.cnf

[mysqld]
server-id=2

Nieuwe Database aanmaken

mysql> CREATE DATABASE pdns;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY 'slave1_password';
mysql> FLUSH PRIVILEGES;

Dump inlezen op de slave

# mysql -u root -p pdns < export.sql

Nu dien je uit te loggen binnen MySQL omgeving. Herstart de MySQL server om de instellingen actief te maken.

# /usr/local/etc/rc.d/mysql-server restart

Master

Ga weer naar de Master server toe waar de MySQL server draait, log in.

# mysql -u root -p

Ga dan naar de betreffende database binnen MySQL en noteer de File naam:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> USE pdns;
mysql> SHOW MASTER STATUS;

Replication slave

Ga weer naar de Slave server toe.

Binnen MySQL een useraccount aanmaken en voer bij MASTER_LOG_FILE de naam van het bestand binnen de Master server in, die je net hebt genoteerd

mysql> CHANGE MASTER TO MASTER_HOST='81.4.79.81', MASTER_USER='slave1_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='ns1-bin.000001', MASTER_LOG_POS=98;
mysql> START SLAVE;

Handige commando’s

mysql> SHOW MASTER STATUS;
mysql> SHOW SLAVE STATUS;
mysql> SHOW PROCESSLIST;

Links

How to Set Up Replication