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;