Shulou(Shulou.com)06/02 Report--

This article mainly shows you "mysql how to achieve multi-master two-way and cascade replication", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql how to achieve multi-master two-way and cascade replication" this article.

one。 Unscramble

Switching offline has no effect on the web service, since the code is the same, so it's the same wherever it is deployed. The difference is the data in the database. This requires that our master library and every slave library deployed on each native PC can achieve autonomous data synchronization.

With regard to possible conflicts, we have avoided them at the business level. Different units will not update the same field. Different units will not insert in the same table. So now we only need to care about the automatic synchronization of mysql and the automatic retransmission of offline reconnection.

II. Web design

At the 1.web backend, I set up read-write separation (just to install it, in fact, it is not necessary, after all, the concurrency of private network is not high)

two。 Online, unit access, all select local mysql,update/insert/delete total server mysql

3. When offline, the access of the unit is in the local mysql.

(this is easy to achieve. For different domain names and addresses corresponding to different projects, the database router is set in advance in the project, and it is up to users to decide which domain name and address to access. In fact, if the network is cut off and the main server cannot be accessed, he will naturally access the domain name and address of the local service)

3. MySQL design

1. There is no doubt that the synchronization method is the version of binlog,mysql that comes with mysql. Choose version 5.7 or above.

two。 Multi-master bidirectional: the mysql on each native PC should be synchronized with the mysql on the master server.

3. The master server should enable cascading replication and synchronize the binlog generated by the subordinate unit PC to the PC of other units. So that PC of other units can use this part of the data when offline.

IV. Practical 1.mysql architecture diagram (draft)

two。 Configuration

In the first stage, start the master function of all libraries, and create an access account in the library for use by other libraries:

Main main library (master server)

① finds the startup file my.ini or my.cnf under the mysql installation path, and puts the following information under [mysqld] of the startup file:

Log_bin=mysql-binbinlog_format=MIXEDsync_binlog=1expire_logs_days=7binlog-do-db=equip_systemslave-skip-errors=allmaster_info_repository=tablerelay_log_info_repository=tablelog-slave-updates=1

Definition of each parameter:

Log_bin=mysql-bin: when configured as mysql-bin, mysql enables the binlog feature

The recording mode of binlog_format=MIXED:binlog, and the mixed recording mode of MIXED

Sync_binlog=1:

The number of transactions that need to be committed to trigger binlog to be flushed from cache to disk. A default of 0 means that it is controlled by the disk file system, and a value of 1 means that each transaction committed is refreshed (this is the safest time. Only 1 transaction is lost when the service is abnormal, but IO consumption is the largest and high concurrency is prohibited). The common DBA is generally set to 100. The concurrency of this project is low, which can be set to 1.

The validity period of expire_logs_days=7:binlog. Setting it to 7 means that the binlog will be deleted after 7 days.

Binlog-do-db=equip_system: name of the database to be synchronized

Slave-skip-errors=all: indicates which exceptions should be skipped when an exception occurs in synchronization. Setting it to all means that the synchronization of all exceptions is skipped directly. Whether it can be set to all, it should be combined with the specific business of the project. This project can.

Master_info_repository=table: optional

Set to table, which is more stable and can be automatically resumed when the service is restarted

Relay_log_info_repository=table: optional

Set to table, which is more stable and can be automatically resumed when the service is restarted

Log-slave-updates=1: a configuration of 1 means cascading replication is enabled

Remember to modify the server-id. The interconnected mysql within the architecture cannot be the same.

Server-id=100

② restarts the sql service

③ enters the mysql command line and executes the following command

If values is ON, binlog is enabled successfully.

Show variables like'% log_bin%'

Several accounts are created from the main database. Note that the account password is determined in advance and memorized. If you make a mess, it will be very big:

CREATE USER 'synchronized library account name' @ 'synchronized library account password'; GRANT REPLICATION SLAVE ON *. * TO 'synchronized library account name' @ 'synchronized library ip'

Refresh permissions:

Flush privileges

View master status:

Show master status

Return the result:

This value in the shape of "mysql-bin.000003" should be recorded and used when connecting from the main library.

The first phase configuration of the main library has been completed.

From the master library:

① finds the startup file my.ini or my.cnf under the mysql installation path, and puts the following information under [mysqld] of the startup file:

Log_bin=mysql-binbinlog_format=MIXEDsync_binlog=1expire_logs_days=7binlog-do-db=equip_systemslave-skip-errors=allmaster_info_repository=tablerelay_log_info_repository=table

The above parameters are the same as the master database, except that cascading replication does not need to be enabled from the master library. Remember to modify server-id

The later process is the same as the configuration of the main library, after all, the master function is enabled, so I won't repeat it here. Just record the value of the output in the form of "mysql-bin.000003".

In one stage, the master function configuration of each library is completed.

In the second stage, configure the slave function of each library, that is, establish a connection with the library to be synchronized

From the master database, you only need to establish a connection with the master library.

Execute the following command from the mysql command line:

Set the parameters for connecting to the master:

The port number of the ip', MASTER_PORT= of the other library to be synchronized by CHANGE MASTER TO MASTER_HOST=', the account name created by the MASTER_USER=' database for you, the password created by the MASTER_PASSWORD=' library for you, and the value of File produced by the master status of the MASTER_LOG_FILE=' library.

Start slave to establish a connection:

Start slave

View connection status:

Show slave status\ G

Return the result:

When both Running are Yes, the connection is successful. If it is not successful, please consult the data debug by yourself. This is the other party's library is not open.

The rest of the slave operations are the same as setting connection parameters, starting slave to establish a connection, and checking the connection status.

Main main library:

The main library process is also to set connection parameters, start slave to establish a connection, and check the connection status. The difference is that you need to set multiple master connection parameters, so there is a small change in the command to set the connection parameters, which requires the setting of one more channel channel, as follows:

Configure the connection parameters with slave library 2

CHANGE MASTER TO MASTER_HOST=' from the ip', MASTER_PORT=' of master library 1 from the port number of master library 1, the account name created for you by MASTER_USER=' from master library 1, the password created for you by MASTER_PASSWORD=' from master library 1, and the file value 'for channel' of the master status of master library 1 from MASTER_LOG_FILE='

Configure the connection parameters to slave library 2:

CHANGE MASTER TO MASTER_HOST=' from the ip', MASTER_PORT=' of master library 2 from the port number of master library 2, the account name created for you by MASTER_USER=' from master library 2, the password created for you by MASTER_PASSWORD=' from master library 2, and the file value 'for channel' of the master status of master library 2 from MASTER_LOG_FILE='

How many are matched with several:

...

Start slave:

Start slave

View slave status:

Show slave status\ G

Multiple status are returned. Check them in turn and check them in turn.

The above is all the contents of the article "how to achieve multi-master bidirectional and cascade replication in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.