Mysql Sync

This page will describe how to build a master and slave mysql server where the slave copies all changes made to the master DB. This page will assume you already have packages mysql-server and mysql-client installed on master and slave.

On the master

Edit /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address            = your.ser.ver.ip
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = <DB-name>

Restart mysql

service mysql restart

Log into mysql

mysql -u root -p

Create a slave

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

Lock the database

use DB-name;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Save the "File" and "Position" values, we'll need them later. Now open a new SSH session to the server and dump the database

mysqldump -u root -p --opt DB-name > DB-name-dump.sql

Back to the previous SSH session, unlock the DB

UNLOCK TABLES;
quit

On the slave

Log into mysql

mysql -u root -p

Create the DB

CREATE DATABASE DB-name;
quit

Copy the dump you made on the master server to the slave and import it

mysql -u root -p DB-name < /path/to/DB-name-dump.sql

Edit /etc/mysql/mysql.conf.d/mysqld.cnf

server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = DB-name

Restart mysql

service mysql restart

Log into mysql

mysql -u root -p

Set and enable the replication (set MASTER_LOG_FILE and MASTER_LOG_POS to the "File" and "Position" values you saved earlier from the master server)

CHANGE MASTER TO MASTER_HOST='ma.st.er.ip',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

You can test if the replication works by creating a test table on the master and by running this command

SHOW SLAVE STATUS\G

Modify master config on the slave

In case you ever need to change the IP of the master, this might help: https://dba.stackexchange.com/questions/30782/update-mysql-slave-when-changing-the-hostname-ip-of-master

If that doesn't work, you need to stop the slave, reset/delete it, delete the database on the slave and redo the replication from zero.