MySQL master/Master synchronization Configuration

RealWar environment Introduction:

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

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

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

[Mysqld] BINLOG-do-DB = db_rocky # database for which logs need to be logged. if multiple databases can be separated by commas (,), or multiple BINLOG-do-DB options BINLOG-ignore-DB = MySQL # databases that do not need to record hexadecimal logs. if multiple databases can be separated by commas (,), or multiple BINLOG-do-DB options replicate-do-DB = db_rocky # the database to be synchronized. if multiple databases can be separated by commas (,), or multiple replicate-do-DB options replicate-ignore-DB = MySQL, information_schema # databases that do not need to be synchronized. if multiple databases can be separated by commas, or multiple replicate-ignore-DB options are used # synchronization parameters: # ensure that slave will receive the write information of another master on any master. log-slave-updatessync_binlog = 1auto_increment_offset = 1auto_increment_increment = 2slave-skip-errors = all # filter out some errors that do not have any major problems

On even

[Mysqld] server-id = 2 # set a different ID. the default value in CNF is 1. Change the default value, instead of adding a server-idbinlog-do-DB = db_rocky # database that needs to record binary logs. if multiple databases can be separated by commas (,), or multiple BINLOG-do-DB options BINLOG-ignore-DB = MySQL # databases that do not need to record hexadecimal logs. if multiple databases can be separated by commas, or multiple BINLOG-ignore-DB options are used # the database to be synchronized replicate-do-DB = db_rocky # the database to be synchronized. if multiple databases can be separated by commas (,), or multiple BINLOG-do-DB options replicate-ignore-DB = MySQL, information_schema # databases that do not need to be synchronized. if multiple databases can be separated by commas, or multiple BINLOG-do-DB options are used # synchronization parameters: # ensure that slave will receive the write information from another master on any master. log-slave-updatessync_binlog = 1auto_increment_offset = 2auto_increment_increment = 2slave-skip-errors = all # filter out some errors with no 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

Mysql> flush tables with read lock; # prevent 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_rockybinlog_ignore_db: mysql1 row in SET (0.00 Sec)

In the 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_rockyBinlog_Ignore_DB: mysql1 row in set (0.01 sec)

(V) use the change master Statement on the server odd and even respectively to specify the synchronization location:

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 the 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 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

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

(6) Start the slave server thread on the server odd and even respectively.

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

View the slave server status on the server odd and even respectively:

On odd, mysql> show slave status \ G; **************************** 1. row *************************** mainly focuses on the following two parameters :...... slave_io_running: Yes slave_ SQL _running: Yes ...... on even: mysql> show slave status \ G; ***************************** 1. row *************************** mainly focuses on the following two parameters :...... slave_io_running: Yes slave_ SQL _running: Yes ......

(Vii) test

Mysql> show databases; + rows + | database | + ------------------ + | information_schema | db_rocky | MySQL | performance_schema | test | + ------------------ + 5 rows in SET (0.00 Sec) mysql> Use db_rocky; database changedmysql> 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)