MySQL database two-way image and circulating image (copying)

For two-way database images, data changes of database A must be mirrored to database B, and modifications in database B must be copied to database A at the same time.

For cyclic database images, multiple databases A, B, C, and D must be mirrored to other databases at the same time.

Applications: databases and programs of the same Zen Cart shop can be placed on different hosts. Orders and customer information added to any host are added to other host databases at the same time.

To implement two-way or cyclic database images, you must first prevent conflicts between the AUTO_INCREMENT field in the database to avoid multiple databases generating the same increment value.

The following uses three cyclic images as an example. A is the primary image of B, B is the primary image of C, and C is the primary image of. The following parameters are added to the MySQL setting file/etc/my. cnf on the three hosts:

# HOST 1: US host A, IP Address: 100.101.102.201
[Mysqld]
Server-id = 10
Log-bin = mysql-bin
Log-slave-updates
Replicate-same-server-id = 0
Auto_increment_increment = 10
Auto_increment_offset = 1
Master-host = 100.101.102.203
Master-user = repl_user
Master-password = repl_password
Report-host = 100.101.102.201

# Host 2: China host B, IP Address: 100.101.102.202
[Mysqld]
Server-id = 20
Log-bin = mysql-bin
Log-slave-updates
Replicate-same-server-id = 0
Auto_increment_increment = 10
Auto_increment_offset = 2
Master-host = 100.101.102.201
Master-user = repl_user
Master-password = repl_password
Report-host = 100.101.102.202

# Host 3: local host C, IP: 100.101.102.203
[Mysqld]
Server-id = 30
Log-bin = mysql-bin
Log-slave-updates
Replicate-same-server-id = 0
Auto_increment_increment = 10
Auto_increment_offset = 3
Master-host = 100.101.102.202
Master-user = repl_user
Master-password = repl_password
Report-host = 100.101.102.203

Simple Description:
Server-id: Database id. Each database id must be unique;
Replicate-same-server-id: set to 0 to prevent data from being updated cyclically;
Auto_increment_increment: This is one of the most important parameters in a circular image, indicating that the automatic increment is 10, which allows up to 10 databases to join the array of this circular image, and the automatic increment field will not be repeated.
Auto_increment_offset: This is one of the most important parameters in the cyclic image, indicating the offset value. The offset value of each database must be unique and must be between 1 and auto_increment_increment.
Master-host: IP address of the master database server;
Master-user: The image user name used to connect to the master database;
Master-password: The image password used to connect to the master database;
Report-host: the IP address provided to the primary database for reverse connection. Because the primary database sometimes cannot correctly determine the IP address of the slave server, it is best to fill in the IP address of the slave server.

In addition, sometimes you only need to mirror some databases. You can add the following to my. cnf:
Replicate-do-db = db_name1
Replicate-do-db = db_name2
Replicate-do-db = db_name3

In this way, only the db_name1/db_name2/db_name3 image is used.

If only some databases do not need images, you can add the following to my. cnf:
Replicate-ignore-db = db_name1
Replicate-ignore-db = db_name2
Replicate-ignore-db = db_name3

In this way, the three databases db_name1/db_name2/db_name3 are ignored.