MySQL master/Master synchronization Configuration

Welcome to the Linux community forum and interact with 2 million technical staff to enter the actual environment Introduction: server name IP system MySQLodd. example. com192.168.1.w.rhel-5.85.5.16even.example.com192.168.1.115rhel-5.85.5.16 assume that the database to be synchronized is db_rocky (I) create a synchronization user on ODD [pl

Welcome to the Linux community forum and interact with 2 million technical staff> go to the actual environment: 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 suppose the library to be synchronized is db_rocky (I) create synchronization user on ODD [pl

Welcome to the Linux community forum and interact with 2 million technicians>

Practical 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

[Plain]

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

[Plain]

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

[Plain]

[Mysqld]

Binlog-do-db = db_rocky # database for which logs need to be logged. Multiple databases can be 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. Multiple databases can be separated by commas (,), or multiple binlog-do-db options are used.

Replicate-do-db = db_rocky # the database to be synchronized. Multiple databases can be 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. Multiple databases can be separated by commas (,), or multiple replicate-ignore-db options are used.

# 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

[Plain]

[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. Multiple databases can be 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. Multiple databases can be 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. Multiple databases can be separated by commas (,), or multiple binlog-do-db options are used.

Replicate-ignore-db = mysql, information_schema # databases that do not need to be synchronized. Multiple databases can be separated by commas (,), or multiple binlog-do-db options are used.

# 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

[Plain]

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

[Plain]

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 server ODD and EVEN respectively to specify the synchronization location:

In ODD

[Plain]

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

[1] [2]