MySQL master/master synchronization configuration_mysql

Configuring bitsCN.com for MySQL master/master synchronization

MySQL master/master synchronization configuration

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

[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. 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

[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

[Plain]

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

[Plain]

Mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

(6) start the slave server thread on the server ODD and EVEN respectively.

[Plain]

Mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

View the slave server status on the server ODD and EVEN respectively:

[Plain]

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

[Plain]

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)

BitsCN.com