Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to achieve master-master synchronization in MySQL5.6". In daily operation, I believe many people have doubts about how to achieve master-master synchronization in MySQL5.6. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to achieve master-master synchronization in MySQL5.6". Next, please follow the editor to study!

Introduction to actual combat environment:

Server name IP system MySQLodd.example.com192.168.1.115rhel-5.85.6.24even.example.com192.168.1.116rhel-5.85.6.24

Suppose the library to be synchronized is db_rocky

㈠ creates synchronous users

On ODD

Mysql > grant replication slave on *. * to 'water'@'192.168.1.116' 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.115' identified by' cdio2010'

Query OK, 0 rows affected (0.11 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

㈡ modifies the / etc/my.cnf configuration file to add the following:

On ODD

[mysqld]

Server-id=1

Binlog-do-db=db_rocky # requires a database that records binary logs.

Binlog-ignore-db=mysql # does not need a database that records binary logs.

Replicate-do-db=db_rocky # databases that need to be synchronized.

Replicate-ignore-db=mysql,information_schema # databases that do not require synchronization.

Note: if there are multiple databases that can be separated by commas, or use multiple binlog-do-db, binlog-do-db, replicate-do-db, replicate-ignore-db options

# synchronization parameters:

# make sure that slave hangs on any master and will receive the write message from another master

Log-slave-updates

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=1

Slave-skip-errors=all # filters out some errors that are not a big deal

On EVEN

[mysqld]

Server-id=2 # set a different id number

Note: there is a default value of 1 in my.cnf. Change the default value to another number instead of adding a new server-id.

Binlog-do-db=db_rocky # requires a database that records binary logs.

Binlog-ignore-db=mysql # does not need a database that records binary logs.

# databases to be synchronized

Replicate-do-db=db_rocky # databases that need to be synchronized.

Replicate-ignore-db=mysql,information_schema # databases that do not require synchronization.

# if there are multiple databases that can be separated by commas, or use multiple binlog-do-db, binlog-do-db, replicate-do-db, replicate-ignore-db options

# synchronization parameters:

# make sure that slave hangs on any master and will receive the write message from another master

Log-slave-updates

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=2

Slave-skip-errors=all # filters out some errors that are not a big deal

㈢ restarts the mysql service on servers ODD and EVEN respectively

# server mysqld restart

㈣ checks the status of the primary server on the 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)

㈤ specifies the synchronization location with the change master statement on the server ODD and EVEN, respectively:

In ODD

Mysql > change master to master_host='192.168.1.116',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.115',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 is determined by the status value found by the master server above

Master_log_file corresponds to File,master_log_pos corresponds to Position.

Execute on ODD, EVEN

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

㈥ starts slave server threads on 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 focus on the following two parameters:

...

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

...

On EVEN:

Mysql > show slave status\ G

* * 1. Row *

Mainly focus on the following two parameters:

...

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

...

㈦ test

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.01sec)

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)

At this point, the study of "how to achieve master-master synchronization in MySQL5.6" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.