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.