Configure the MySQL Master Server
Step 1 : edit /etc/mysql/my.cnf file.
#skip-networking
#bind-address = 127.0.0.1
(add below line in /etc/mysql/my.cnf file)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = replicationdb
step 2 : Restart Mysql server
#/etc/init.d/mysql restart
Step 3 : create a user with replication privileges:
#mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO ’replicationuser’@’%’ IDENTIFIED BY ‘’;
mysql>FLUSH PRIVILEGES;
mysql>USE replicationdb;
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
Result of above command:
+—————+———-+————–+——————+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+—————+———-+————–+——————+
| mysql-bin.005 | 180 | replicationdb | |
+—————+———-+————–+——————+
1 row in set (0.00 sec)
Please remeber above information will need in slave server configuration
mysql>quit;
Step 4 : Dump replicationdb from the Master server
#mysqldump -u root -p replicationdb > replicationdb.sql
Above command create dump of replicationdb in the file replication.sql. Transfer this file to your slave server!
Step 5 : Unlock the tables
#mysql -u root -p
mysql>UNLOCK TABLES;
mysql>quit;
Configure The Slave Server
Step 1 : Create the database replicationdb
#mysql -u root -p
mysql>CREATE DATABASE replicationdb;
mysql>quit;
mysql>CREATE DATABASE replicationdb;
mysql>quit;
Step 2 : Extract the dump database in replicationdb database
#mysql -u root -p replicationdb < /path/to/replicationdb.sql
Step 3 : Add the below lines into /etc/mysql/my.cnf
server-id=2
master-host=192.168.10.175
master-user=replicationuser
master-password=secret
replicate-do-db=replicationdb
Step 3 : Restart MySQL:
# /etc/init.d/mysql restart
Step 4 : Allow slave user to connet to Remote Master server
# mysql -u root -p
mysql>SLAVE STOP;
mysql>CHANGE MASTER TO MASTER_HOST=’192.168.10.175′, MASTER_USER=’replicationuser’, MASTER_PASSWORD=’’, MASTER_LOG_FILE=’mysql-bin.005′, MASTER_LOG_POS=180;
mysql>START SLAVE;
mysql>quit;
Configuration is Complete now! So whenever replicationdb is updated on the master server, all changes will be replicated to replicationdb on the slave server.
