MySQL master master synchronization configuration steps

MySQL master master synchronization configuration
The server name IP system MySQL odd.example.com 192.168.1.116 rhel-5.8 5.5.16 even.example.com 192.168.1.115 rhel-5.8 5.5.16
Assume that the library to be synchronized is db_rocky
1. to create a synchronous user
On the ODD

 
mysql> grant replication slave on *.* to 'water'@'192.168.1.115' identified by 'cdio2010'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec) 

On the EVEN

 
mysql> grant replication slave on *.* to 'water'@'192.168.1.116' identified by 'cdio2010'; 
Query OK, 0 rows affected (0.11 sec) 
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec) 

2. modify the /etc/ my.cnf configuration file by adding the following:
On the ODD

 
[mysqld] 
binlog-do-db=db_rocky # A database that requires base-logged data . If there are multiple databases separated by commas , Or use multiple binlog-do-db options  
binlog-ignore-db=mysql # A database that does not require a base log . If there are multiple databases separated by commas , Or use multiple binlog-do-db options  
replicate-do-db=db_rocky # The database that needs to be synchronized . If there are multiple databases separated by commas , Or use multiple replicate-do-db options  
replicate-ignore-db=mysql,information_schema # A database that does not require synchronization . If there are multiple databases separated by commas , Or use multiple replicate-ignore-db options  
# The synchronization : 
# ensure slave Hang on any 1 Taiwan master The top will receive another 1 a master Write to  
log-slave-updates 
sync_binlog=1 
auto_increment_offset=1 
auto_increment_increment=2 
slave-skip-errors=all # To filter out 1 Mistakes that don't really matter  

On the EVEN

 
[mysqld] 
server-id=2 # Set up the 1 A different id , notice here my.cnf There's a default value in there  1  , change the default value, but can not be added 1 a server-id 
binlog-do-db=db_rocky # Need to record 2 Base log database . If there are multiple databases separated by commas , Or use multiple binlog-do-db options  
binlog-ignore-db=mysql # A database that does not require a base log . If there are multiple databases separated by commas , Or use multiple binlog-ignore-db options  
# The database that needs to be synchronized  
replicate-do-db=db_rocky # The database that needs to be synchronized . If there are multiple databases separated by commas , Or use multiple binlog-do-db options  
replicate-ignore-db=mysql,information_schema # A database that does not require synchronization . If there are multiple databases separated by commas , Or use multiple binlog-do-db options  
# The synchronization : 
# ensure slave Hang on any 1 Taiwan master The top will receive another 1 a master Write to  
log-slave-updates 
sync_binlog=1 
auto_increment_offset=2 
auto_increment_increment=2 
slave-skip-errors=all # To filter out 1 Mistakes that don't really matter  

3. Restart the mysql service on the ODD EVEN server separately
4. Check the status of the main server on server ODD and EVEN respectively
In ODD

 
mysql> flush tables with read lock;# Prevent access to new data  
Query OK, 0 rows affected (0.00 sec) 
mysql> show master status\G; 
*************************** 1. row *************************** 
File: mysql-bin.000007 
Position: 438 
Binlog_Do_DB: db_rocky 
Binlog_Ignore_DB: mysql 
1 row in set (0.00 sec) 

In EVEN

 
mysql> flush tables with read lock; 
Query OK, 0 rows affected (0.00 sec) 
mysql> show master status\G; 
*************************** 1. row *************************** 
File: mysql-bin.000008 
Position: 107 
Binlog_Do_DB: db_rocky 
Binlog_Ignore_DB: mysql 
1 row in set (0.01 sec) 

All measures respectively in server ODD, EVEN change master statement specifies synchronization position:
In ODD

 
mysql> change master to master_host='192.168.1.115',master_user='water',master_password='cdio2010', 
-> master_log_file='mysql-bin.000008',master_log_pos=107; 
Query OK, 0 rows affected (0.05 sec) 

In EVEN

 
mysql> change master to master_host='192.168.1.116',master_user='water',master_password='cdio2010', 
-> master_log_file='mysql-bin.000007',master_log_pos=438; 
Query OK, 0 rows affected (0.15 sec) 

Note: master_log_file, master_log_pos are determined from the status values found by the primary server above
master_log_file corresponds to File, master_log_pos corresponds to Position
In ODD EVEN

 
mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 

㈥ respectively on the server ODD, EVEN start threads from the server

 
mysql> start slave; 
Query OK, 0 rows affected (0.00 sec) 

Check the status of slave servers on servers ODD and EVEN respectively:

 
mysql> grant replication slave on *.* to 'water'@'192.168.1.116' identified by 'cdio2010'; 
Query OK, 0 rows affected (0.11 sec) 
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec) 

0
㈦ test

 
EVEN  on  
mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| db_rocky | 
| mysql | 
| performance_schema | 
| test | 
+--------------------+ 
5 rows in set (0.00 sec) 
mysql> use db_rocky; 
Database changed 
mysql> show tables; 
Empty set (0.00 sec) 
mysql> create table water (id int); 
Query OK, 0 rows affected (0.04 sec) 
mysql> insert into water values(1); 
Query OK, 1 row affected (0.01 sec) 
mysql> commit; 
Query OK, 0 rows affected (0.00 sec) 
 in  ODD  on  
mysql> show tables; 
+--------------------+ 
| Tables_in_db_rocky | 
+--------------------+ 
| t_rocky | 
| water | 
+--------------------+ 
2 rows in set (0.00 sec) 
mysql> select * from water; 
+------+ 
| id | 
+------+ 
| 1 | 
+------+ 
1 row in set (0.00 sec)