Friday, August 19, 2016

MySQL Database Master to Master Replication

DATABASE REPLICATION (Master to Master replication) (On Windows)
-----------------------------------------------------------------------------------------------------------
See 05_Replicate_Database_Master_to_Slaved.txt to see how to have another instance of mysql running. Steps A to D.
Then continue with this guide.
-----------------------------------------------------------------------------------------------------------
I have 2 servers one running on localhost Port 3310, and another running on localhost Port 3311.
From here on I'll the one running on port 3310 server 1 and the other server 2.

1. Do this on both servers.

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

2. Take a snapshot of taxcollection database.
   a) On server 1, run

mysql> USE taxcollection;
mysql> FLUSH TABLES WITH READ LOCK;

   b) leave mysql opened so that the locks are there, and make a snapshot by using another command prompt

mysqldump -u root -p -h localhost -P 3310 --opt taxcollection > taxcollection.sql

   c) now back to the opened mysql window that has READ LOCKS; run

mysql> UNLOCK TABLES;

3. Drop taxcollection database on BOTH servers since we have a snap shot now.
mysql> DROP DATABASE taxcollection

4. Shut down both servers. using
mysqladmin -P 3310 -h localhost -u root -p shutdown
mysqladmin -P 3311 -h localhost -u root -p shutdown

5. Change server 1's my.ini to have these values (pay attention to server-id and binlog_do_db)

log-bin=mysql-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog=1
server-id=1
log_bin=c:/wamp/bin/mysql/mysql5.6.17/mysql-bin.log
binlog_do_db=taxcollection

6. Start server 1. example command to start server 1.

mysqld --defaults-file="c:/wamp/bin/mysql/mysql5.6.17/my.ini"

7. in mysql on server 1 create user for server 2 to log in to replicate
mysql> CREATE USER 'replicator'@'localhost' identified by 'password';
mysql> GRANT replication slave on *.* to 'replicator'@'localhost';

8. get master's values to use later on server 2. in mysql on server 1. run

mysql> SHOW MASTER STATUS;
+------------------+----------+---------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+------------------+-------------------+
| mysql-bin.000011 |      718 | taxcollection |                  |                   |
+------------------+----------+---------------+------------------+-------------------+

9. Now, for server 2's my.ini file add/change these values (pay attention to server-id and binlog_do_db)
log-bin=mysql-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog=1
server-id=2
log_bin=c:/wamp/bin/mysql/mysqlslave/mysql-bin.log
binlog_do_db=taxcollection

10. Start server 2. example command to start server 2.

mysqld --defaults-file="c:/wamp/bin/mysql/mysqlslave/my.ini"

11. in mysql on server 2 create user for server 1 to log in to replicate

mysql> CREATE USER 'replicator'@'localhost' identified by 'password';
mysql> GRANT replication slave on *.* to 'replicator'@'localhost';

12. on server 2 create taxcollection database.

mysql> CREATE DATABASE taxcollection;

12.  on mysql on server 2, point it to server 1 by running the below using values recorded from step 8

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = 'localhost',MASTER_PORT = 3310, MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000011', MASTER_LOG_POS = 718;
mysql> START SLAVE;

13. get master's values to use on server 1.  On server 2's mysql run

mysql> SHOW MASTER STATUS;
+------------------+----------+---------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+------------------+-------------------+
| mysql-bin.000015 |      560 | taxcollection |                  |                   |
+------------------+----------+---------------+------------------+-------------------+
1 row in set (0.00 sec)

14. back on server 1's mysql. run these in mysql to point it to server 2.  use values from step 13. and change port number to point to server 2's port.
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = 'localhost',MASTER_PORT = 3311, MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000015', MASTER_LOG_POS = 560;
mysql> START SLAVE;

15. back on server 1. run
mysql> CREATE DATABASE taxcollection;

16. Test to see if it changes on one server is taking effect on other server and vice versa, for me it didn't work...
I had to do these steps again in 17 after empty taxcollection database are created on both servers.

17.
a) On server 1, get values.
SHOW MASTER STATUS;
+------------------+----------+---------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+------------------+-------------------+
| mysql-bin.000013 |     1227 | taxcollection |                  |                   |
+------------------+----------+---------------+------------------+-------------------+

b) On server 2, set values that we have from step 17 a.
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'localhost',MASTER_PORT = 3310, MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000013', MASTER_LOG_POS = 1227;
START SLAVE;

c) On server 2, get values.
SHOW MASTER STATUS;
+------------------+----------+---------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+------------------+-------------------+
| mysql-bin.000017 |     1211 | taxcollection |                  |                   |
+------------------+----------+---------------+------------------+-------------------+

d) Back on Server 1, set values that we have from step 17 c.
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'localhost',MASTER_PORT = 3311, MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000017', MASTER_LOG_POS = 1211;
START SLAVE;

18. Tested to see that updates to server 1 also showed up on server 2 and vice versa.

19. Push the snapshot we saved from step 2 back on to server 1.

mysql -u root -p -h localhost -P 3310 taxcollection < taxcollection.sql

20. Confirm that changes our taxcollection tables are also present on server 2...If not drop database taxcollection on both servers, create database on both servers..and repeat step 17.