Lets assume you have the following configuration
DBServer1 : 172.20.10.217 = (IP Address of Master1/Slave 2)
DBServer2 : 172.20.10.218 = (IP Address of Master2/Slave 1)
Procedure
1. Install mysql on Master1 and Slave1
2. On Master1, make changes in my.cnf :
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 # Replication log-bin=mysql-bin.log server-id=1 [mysql.server] user=mysql basedir=/var/lib/mysql [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
3. On Master1, restart mysql.
[root@db1 ~]# /etc/init.d/mysqld start Starting MySQL: [ OK ]
4. On Master1, create a replication slave account in mysql.
mysql> grant replication slave on *.* to 'repl'@172.20.10.218 identified by 'slave';
5. On Master1 Check the current log position of the database
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 100 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
6. Now edit my.cnf on Slave1:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 # Replication log-bin=mysql-bin.log server-id=2 [mysql.server] user=mysql basedir=/var/lib/mysql [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
7. Restart Mysql on Slave1
[root@db2 ~]# /etc/init.d/mysqld start Starting MySQL: [ OK ]
8. Login to MySQL stop and reset the slave.
mysql> stop slave mysql> reset slave
9. Follow the current position of the logs from Master DB based on the master status above
mysql> CHANGE MASTER TO MASTER_HOST='172.20.10.217', MASTER_USER='repl', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=100; Query OK, 0 rows affected (0.09 sec)
10. Restart mysql Slave1 and check the slave status:
mysql> start slave; mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.20.10.217 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: MASTERMYSQL01-bin.000004 Read_Master_Log_Pos: 100 Relay_Log_File: MASTERMYSQL02-relay-bin.000015 Relay_Log_Pos: 3630 Relay_Master_Log_File: MASTERMYSQL01-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4 Relay_Log_Space: 3630 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1519187 1 row in set (0.00 sec)
Take note of the highlight lines Slave_IO_Running and Slave_SQL_Running state value must be YES.
11. On Master1:
mysql> show master status; +------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ |MysqlMYSQL01-bin.000004 | 100 | | | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec
The above scenario is consider as Master-Slave!
Next we will create a slave master scenario for the same systems and so the scenario when combined can be called as master master.
12. On Master2/Slave1 Create a replication slave account:
mysql> grant replication slave on *.* to 'repl'@172.20.10.217 identified by 'slave2';
13. On Master2/Slave1 Check the current log position of the database
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 100 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
14. On Master1/Slave2 Follow the current position of the logs from Master1 DB based on the master status above
mysql> CHANGE MASTER TO MASTER_HOST='172.20.10.218', MASTER_USER='repl', MASTER_PASSWORD='slave2', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=100; Query OK, 0 rows affected (0.09 sec)
15. On Master1/Slave2 Start Slave:
mysql> start slave;
16. On mysql Master1/Slave2:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.10.218
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Mysql1MYSQL02-bin.000004
Read_Master_Log_Pos: 100
Relay_Log_File: Mysql1MYSQL01-relay-bin.000004
Relay_Log_Pos: 120
Relay_Master_Log_File: Mysql1MYSQL02-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 100
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 103799
1 row in set (0.00 sec)
ERROR:
No query specified
Check the Slave_IO_Running and Slave_SQL_Running make sure states are in Yes . To test that its working fine create tables in the database and you will see changes in slave.
You have now a setup called Master-MasterReplication!