MySQL database bidirectional mirroring, circular mirroring (replication) _mysql

For bidirectional database mirroring, the data changes in database A are mirrored to database B, and the modifications in database B are also replicated to database A.

For circular database mirroring, multiple databases A, B, C, D, and so on, the modification of any of these databases should be mirrored to other databases at the same time.

Application: The same Zen Cart shop database and procedures, can be placed on different hosts, on any one of the host new orders, customer data, will join the other host database.

To achieve bidirectional or circular database mirroring, the first thing to solve is to prevent the database from automatically incrementing (auto_increment) field conflicts, so that multiple databases to generate the same increment value.

For example, A is the primary mirror of B, B is the primary mirror of C, and C is the primary mirror of a. The MySQL setup file on three hosts/etc/my.cnf the following parameters:

# Host One: United States host A, ip: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 Two: China host B, ip: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 Three: 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 identification, each database identifier must be unique;
Replicate-same-server-id: Set to 0 to prevent data circulation update;
Auto_increment_increment: This is one of the most important parameters in the loop mirror, which means that AutoIncrement is 10, which allows up to 10 databases to join the loop-mirrored array, and the AutoIncrement field does not repeat.
Auto_increment_offset: This is one of the most important parameters in the loop mirror, which represents the offset value, each database must have a unique offset value, and between 1 and auto_increment_increment.
Master-host: The IP of the primary database server;
Master-user: The mirror user name used to connect to the primary database;
Master-password: A mirrored password used to connect to the primary database;
Report-host: Provides the primary database for the reverse connection of the IP, because the primary database sometimes can not correctly determine the IP from the server, so it is best to fill out the server's own IP address.


In addition, sometimes you only need to mirror certain databases, and you can add them in my.cnf:
Replicate-do-db = db_name1
Replicate-do-db = db_name2
Replicate-do-db = Db_name3

This will only mirror Db_name1/db_name2/db_name3


If only some databases do not mirror, you can add in MY.CNF:
Replicate-ignore-db=db_name1
Replicate-ignore-db=db_name2
Replicate-ignore-db=db_name3

This ignores the three databases that are db_name1/db_name2/db_name3 when mirroring.