MySQL master-master replication is a common configuration challenge. Today we will walk through a dual-master setup. If you have any questions, please leave a comment below:
1. Environment Description.
Host: 192.168.0.231 (A)
Host: 192.168.0.232 (B)
MYSQL version is 5.1.21
2. Grant User Privileges.
A:
mysql> grant replication slave,file on *.* to ‘repl1′@’192.168.0.232′ identified
by ’123456′;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
B:
mysql> grant replication slave,file on *.* to ‘repl2′@’192.168.0.231′ identified
by ’123456′;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Then stop the MySQL server on both machines.
3. Configuration File.
Enable binary logging in the my.cnf file on both machines.
A:
user = mysql
log-bin=mysql-bin
server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
B:
user = mysql
log-bin=mysql-bin
server-id = 2
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
Refer to the manual for detailed explanations of these parameters.
The part in red is extremely important. If one MASTER goes down, the other takes over immediately.
The magenta part refers to the server frequently flushing logs. This ensures that if one server crashes, the logs are flushed to the other, thus guaranteeing data synchronization.
4. Restart the MySQL Server.
Execute the same steps on A and B
[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe &
[1] 4264
[root@localhost ~]# 071213 14:53:20 mysqld_safe Logging to ‘/usr/local/mysql/data/localhost.localdomain.err’.
/usr/local/mysql/bin/mysqld_safe: line 366: [: -eq: unary operator expected
071213 14:53:20 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
5. Enter the MySQL Shell.
A:
mysql> flush tables with read lock/G
Query OK, 0 rows affected (0.00 sec)
mysql> show master status/G
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 528
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
B:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status/G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 595
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
Then back up your own data to keep the data consistent between both machines.
There are many methods for this. Proceed to the next step after completion.
6. Execute the CHANGE MASTER TO Command on Each Machine.
A:
mysql> change master to
-> master_host=’192.168.0.232′,
-> master_user=’repl2′,
-> master_password=’123456′,
-> master_log_file=’mysql-bin.000004′,
-> master_log_pos=595;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
B:
mysql> change master to
-> master_host=’192.168.0.231′,
-> master_user=’repl1′,
-> master_password=’123456′,
-> master_log_file=’mysql-bin.000007′,
-> master_log_pos=528;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
7. Check Whether the IO Thread and SLAVE Thread Are Running on Each Machine.
A:
mysql> show processlist/G
*************************** 1. row ***************************
Id: 2
User: repl
Host: 192.168.0.232:54475
db: NULL
Command: Binlog Dump
Time: 1590
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 1350
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 1149
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 4. row ***************************
Id: 5
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
4 rows in set (0.00 sec)
B:
mysql> show processlist/G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 2130
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1223
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 4
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 4. row ***************************
Id: 5
User: repl2
Host: 192.168.0.231:50718
db: NULL
Command: Binlog Dump
Time: 1398
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)
If the part in red does not appear, check the error log file in the DATA directory.
8. Release the Locks on Each Machine, Then Test Data Insertion.
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Comparison of tables on both machines before insertion:
A:
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| t11_innodb |
| t22 |
+—————-+
B:
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| t11_innodb |
| t22 |
+—————-+
Insert from Machine A
A:
mysql> create table t11_replicas
-> (id int not null auto_increment primary key,
-> str varchar(255) not null) engine myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t11_replicas(str) values
-> (‘This is a master to master test table’);
Query OK, 1 row affected (0.01 sec)
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| t11_innodb |
| t11_replicas |
| t22 |
+—————-+
3 rows in set (0.00 sec)
mysql> select * from t11_replicas;
+—-+—————————————+
| id | str |
+—-+—————————————+
| 1 | This is a master to master test table |
+—-+—————————————+
1 row in set (0.00 sec)
Now check Machine B:
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| t11_innodb |
| t11_replicas |
| t22 |
+—————-+
3 rows in set (0.00 sec)
mysql> select * from t11_replicas;
+—-+—————————————+
| id | str |
+—-+—————————————+
| 1 | This is a master to master test table |
+—-+—————————————+
1 row in set (0.00 sec)
Now insert data from Machine B in reverse:
B:
mysql> insert into t11_replicas(str) values(‘This is a test 2′);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t11_replicas;
+—-+—————————————+
| id | str |
+—-+—————————————+
| 1 | This is a master to master test table |
| 2 | This is a test 2 |
+—-+—————————————+
2 rows in set (0.00 sec)
Let’s check A
A:
mysql> select * from t11_replicas;
+—-+—————————————+
| id | str |
+—-+—————————————+
| 1 | This is a master to master test table |
| 2 | This is a test 2 |
+—-+—————————————+
2 rows in set (0.00 sec)
Done. Now both tables are acting as MASTER to each other.
Regarding multi-master auto-increment field conflicts.
See the specific article at:
http://dev.mysql.com/tech-resources/articles/advanced-mysql-replication.html
In the mailing list, I saw discussions about online synchronization and ignoring databases/tables. For specifics, see:
http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html