1. Environment: centos5.5 x84, mysql5.152. two machines: master1: 192.168.5.143master2: 192.168.0.1493. Separately compile and install MySQL, and test the connection. 4. Create a user on both machines and grant the copy permission: master1:
- Mysql> Create
User 'test12' @ '% ';
- Mysql> grant replication slave
On *.*
To 'test12' @ '%'
Identified
'Test12 ';
Master2:
- Mysql> Create
User 'test34' @ '192. 168.5.143 ';
- Mysql> grant replication slave
On *.*
To 'test34' @ '192. 168.5.143'
Identified
'Test34 ';
The host can be set as long as it is connected to the other party. For permissions, you only need to have the replication slave permission. 5. Add my. CNF on master1:
- [Mysqld]
- # For repl
- Server-id = 1
- Log-bin = mysql-bin
- Auto-increment = 2 # Set to the total number of servers in the entire structure
- Auto-increment-offset = 1 # Set the starting point for automatic database growth to avoid primary key conflicts during data synchronization between the two servers
6. Add my. CNF on master2:
- [Mysqld]
- # For repl
- Server-id = 2
- Log-bin = mysql-bin
- Auto-increment = 2
- Auto-increment-offset = 1
7. Restart the services on the two machines. 8. Master master2 is specified on master1:
- Mysql> change master
To
- -> Master_host = '192. 168.0.149 ',
- -> Master_user = 'test34 ',
- -> Master_password = 'test34 ';
- Query OK, 0 rows affected
(0.19 Sec)
- Mysql> Start slave;
- Query OK, 0 rows affected
(0.00 Sec)
9. Master master1 is specified on master2:
- Mysql> change master
To
- -> Master_host = '192. 168.5.143 ',
- -> Master_user = 'test12 ',
- -> Master_password = 'test12 ';
- Query OK, 0 rows affected
(0.18 Sec)
- Mysql> Start slave;
- Query OK, 0 rows affected
(0.00 Sec)
10. test lab 1: Start two services and perform operations on any MySQL instance. Experiment 2: Start two services and add 20 warehouses using the tpcc tool on master1. The data occupies about 1.9 GB of hard disk and count (*) on the two machines. The data is consistent. Experiment 3: Start master1 and use the tpcc tool to add a warehouse and start master2. After synchronization for a period of time, calculate count (*) and the results are consistent. PS, MySQL master-master replication is actually two-way master-slave replication.