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

This article shows you how to achieve master-master synchronization in mysql, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

A server:

Host 192.168.1.101

Port 3306

Server B:

Host 192.168.1.102

Port 3306

1. Authorized users:

A server

Mysql > grant replication slave,file on *. * to identified by '1234569'

Query OK, 0 rows affected (0.01 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.01 sec)

B server

Mysql > grant replication slave,file on *. * to identified by '1234568'

Query OK, 0 rows affected (0.01 sec)

> flush privileges

Query OK, 0 rows affected (0.01 sec)

2, profile

A server

Log-bin=mysql-bin

Server-id = 1

Binlog-do-db=test

Binlog-ignore-db=mysql

Replicate-do-db=test

Replicate-ignore-db=mysql

Log-slave-updates

Slave-skip-errors=all

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=1

Master-host = 192.168.1.102

Master-user = repl8

Master-password = 1234568

Master-port = 3306

B server

Log-bin=mysql-bin

Server-id = 2

Binlog-do-db=test

Binlog-ignore-db=mysql

Replicate-do-db=test

Replicate-ignore-db=mysql

Log-slave-updates

Slave-skip-errors=all

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset = 2

Master-host = 192.168.1.101

Master-user = repl9

Master-password = 1234569

Master-port = 3306

Start the server and ok.

Q: the following error occurred when logging in to MySQL on another machine:

ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x'

A: the reason is that MySQL takes security into account and the default configuration only allows you to log in locally

Open the / etc/mysql/my.cnf file and find bind-address = 127.0.0.1 and change it to bind-address = 0.0.0.0

Restart mysql: sudo / etc/init.d/mysql restart

= =

101 increased, 102 out of sync?

Mysql-h292.168.1.102-urepl8-p1234568 ok

102 increase, 101 synchronization

Mysql-h292.168.1.101-urepl9-p1234569 ok

Compare the show slave status\ G of the two groups

one hundred and two

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

Sudo / etc/init.d/mysql restart

Sudo service mysql restart will become: lave_IO_Running: YES

Problems that occur (multi-master self-growing ID repeat)

Solution:

All we have to do is make sure that the self-growing data inserted on the two servers is different.

For example: a check odd ID,B and insert even ID. Of course, if there are many servers, you can define the algorithm, as long as it is different.

Here we add parameters to A _ B to implement parity insertion.

Add parameters to A:my.ini

Auto_increment_offset = 1

Auto_increment_increment = 2

So the numerical value generated by the auto_increment field of An is: 1, 3, 5, 7, … Waiting for odd ID.

Add parameters to B:my.ini

Auto_increment_offset = 2

Auto_increment_increment = 2

So the value generated by the auto_increment field of B is: 2, 4, 6, 8,... Wait for the even number ID.

As you can see, your auto_increment field is never duplicated between different servers, so there is nothing wrong with the Master-Master structure. Of course, you can also use 3, 4, or N servers, just make sure that auto_increment_increment = N and set auto_increment_offset to the appropriate initial value, so that our MySQL can have dozens of primary servers at the same time without self-growing ID duplication.

Here we are talking about two MYSQL servers, which you can also expand to multiple, similar to the implementation method.

A-> B-> C-> D-> A

Such a ring backup structure is formed, and finally, we should remember that the self-growing ID (primary key) should be designed well, otherwise it will go wrong.

The above is how to achieve master-master synchronization in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.