MySQL master/Master synchronization Configuration
| 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 database to be synchronized is db_rocky.
(I) Create a synchronization user
On ODD
Copy codeThe Code is as follows:
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 EVEN
Copy codeThe Code is as follows:
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)
(Ii) modify the/etc/my. cnf configuration file and add the following content to it::
On ODD
Copy codeThe Code is as follows:
[Mysqld]
Binlog-do-db = db_rocky # database for which logs need to be logged. If multiple databases are separated by commas (,), or multiple binlog-do-db options are used.
Binlog-ignore-db = mysql # databases that do not need to record hexadecimal logs. If multiple databases are separated by commas (,), or use the binlog-do-db option.
Replicate-do-db = db_rocky # the database to be synchronized. If multiple databases are separated by commas, or multiple replicate-do-db options are used.
Replicate-ignore-db = mysql, information_schema # databases that do not need to be synchronized. If multiple databases are separated by commas, or use multiple replicate-ignore-db options
# Synchronization parameters:
# Ensure that slave will receive write information from another master when it is mounted to any master.
Log-slave-updates
Sync_binlog = 1
Auto_increment_offset = 1
Auto_increment_increment = 2
Slave-skip-errors = all # filter out some errors without any major problems
On EVEN
Copy codeThe Code is as follows:
[Mysqld]
Server-id = 2 # set a different id. Note that the default value in my. cnf is 1. Change the default value instead of adding a server-id.
Binlog-do-db = db_rocky # database for which binary logs need to be recorded. If multiple databases are separated by commas, or multiple binlog-do-db options are used
Binlog-ignore-db = mysql # database that does not need to record the hexadecimal log. If multiple databases are separated by commas, or multiple binlog-ignore-db options are used
# Databases to be synchronized
Replicate-do-db = db_rocky # the database to be synchronized. If multiple databases are separated by commas (,), or use the binlog-do-db option.
Replicate-ignore-db = mysql, information_schema # databases that do not need to be synchronized. If multiple databases are separated by commas, or use multiple binlog-do-db options
# Synchronization parameters:
# Ensure that slave will receive write information from another master when it is mounted to any master.
Log-slave-updates
Sync_binlog = 1
Auto_increment_offset = 2
Auto_increment_increment = 2
Slave-skip-errors = all # filter out some errors without any major problems
(3) restart the mysql service on the odd even server.
(4) view the status of the master server on the server ODD and EVEN respectively.
In ODD
Copy codeThe Code is as follows:
Mysql> flush tables with read lock; # prevents new data entry
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 the EVEN
Copy codeThe Code is as follows:
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)
(V) use the change master Statement on the ODD and EVEN servers to specify the synchronization location.:
In ODD
Copy codeThe Code is as follows:
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 the EVEN
Copy codeThe Code is as follows:
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 and master_log_pos are determined by the Status values found by the master server above.
Master_log_file corresponds to File, and master_log_pos corresponds to Position
On ODD EVEN
Copy codeThe Code is as follows:
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
(6) Start the slave server thread on the server ODD and EVEN respectively.
Copy codeThe Code is as follows:
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
View the slave server status on the server ODD and EVEN respectively:
Copy codeThe Code is as follows:
On ODD
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Pay attention to the following two parameters:
...
...
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
...
...
On EVEN:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Pay attention to the following two parameters:
...
...
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
...
...
(Vii) test
Copy codeThe Code is as follows:
On EVEN
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)
On ODD
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)
