Sync two mysql database on Centos 6/MYSQL 5.1.7x

Sync two mysql database on Centos 6.5 and MYSQL 5.1.7x

Install on both servers, master and slave,  apache, mysql and phpmyadmin.

You can install all that with this script.

First we will setup the master on ip address 192.168.1.9:

Go on master edit /etc/my.cnf and add after [mysqld] (keep all other lines intact) :

server-id = 1
binlog-do-db=test
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Master my.cnf mysqlYou must have server-id = 1  (because this is the master)

Database name will be in this case test

Save my.cnf and restart mysql

service mysqld restart

Connect to mysql as root

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '1password2';

We add on master a user (slave_user) with password (1password2)

FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

show-master-status-mysqlWrite down the mysql-bin.000002 and Position 333 numbers, we required these numbers later on Slave server

quit;

Dump all database from master in tmp directory (later we will import dumb.db on slave)

mysqldump -u root -p --all-databases --master-data > /tmp/dump.db

Connect again to mysql as root and unlock the tables

mysql -u root -p
UNLOCK TABLES;

Upload dumb.db on slave server

scp /tmp/dump.db [email protected]:/tmp/

Config Slave server on 192.168.1.10

Add the following in /etc/my.cnf after [mysqld]

server-id = 2
master-host=5.192.186.9
master-connect-retry=60
master-user=slave_user
master-password=1password2
replicate-do-db=test
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

slave-mycnf-mysql-syncYou must have on slave server-id = 2

Save my.cnf

mysql -u root -p < /tmp/dump.db
service mysqld restart

Connect on mysql slave as root

mysql -u root -p
slave stop;
CHANGE MASTER TO MASTER_HOST='192.168.1.9', MASTER_USER='slave_user', MASTER_PASSWORD='1password2', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=333;
slave start;
show slave status\G

slave-config-mysql

connect-mysql-slave-to-master

Of course you can add in mysql master another user with all privilegies for your database. You can use it for whatever you want.

How to re-sync MySQL Master-Slave

On slave server:

mysql-u root -p
STOP SLAVE;

On master server

mysql-u root -p
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p --all-databases --master-data > /tmp/dump.db
UNLOCK TABLES;
quit;
scp /tmp/dump.db [email protected]:/tmp/

On slave server

mysql -u root -p < /tmp/dump.db
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.9', MASTER_USER='slave_user', MASTER_PASSWORD='1password2', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=333;
START SLAVE;
show slave status \G

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *