Mysql Sync

From Newroco tech docs
Jump to navigationJump to search

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

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

Restart mysql

  1. service mysql restart

Log into mysql

  1. mysql -u root -p

Create a slave

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

Lock the database

  1. use DB-name;
  2. FLUSH TABLES WITH READ LOCK;
  3. 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

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

Back to the previous SSH session, unlock the DB

  1. UNLOCK TABLES;
  2. quit

On the slave

Log into mysql

  1. mysql -u root -p

Create the DB

  1. CREATE DATABASE DB-name;
  2. quit

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

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

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

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

Restart mysql

  1. service mysql restart

Log into mysql

  1. 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)

  1. 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;
  2. START SLAVE;

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

  1. 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.