Master-master replication (dual-master slave) for building a MySql Cluster

Master-master replication (dual-master slave) for building a MySql Cluster

The previous article introduced how to configure master-slave replication for MySQL. This article mainly describes master-slave replication:

 

First, describe the keywords that will appear in the article:

Server1, server2, server... indicates the Database server

(You are too lazy to write)

Mysql master-master replication Configuration

Server1 ip: 192.168.0.231
Server2 ip: 192.168.0.234


Change the mysql configuration file of the two hosts
Vim/etc/my. cnf
Add server1
Server-id = 1
Log-bin = mysqlmaster-bin.log
Auto-increment = 2
Auto-increment-offset = 1


Add server2
Server-id = 2
Log-bin = mysqlmaster-bin.log
Auto-increment = 2
Auto-increment-offset = 2

Note: The value of auto-increment is set to the total number of servers in the entire structure. In this experiment, the value is 2 for two servers.
Auto-increment-offset is used to set the starting point for automatic growth in the database, because the server has set an automatic Growth Value of 2, so their starting point must be different, this prevents primary key conflicts during data synchronization between the two servers.

You can also set replicate-do-db = database-name to specify the database to be synchronized.

Restart the mysql service to make the configuration take effect.

Log on to mysql

Add users required for Data Synchronization

Server 1

Mysql> grant replication slave on *. * TO 'clustered op1' @ '2017. 192.% 'identified by 'systop ';

On Server 2

Grant replication slave on *. * TO 'clustered op2' @ '192. 192.% 'identified by 'systop ';


View the master Status of the two servers

Server1

Show master status;
+ ------------------------ + ---------- + -------------- + ------------------ + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------------ + ---------- + -------------- + ------------------ + ------------------- +
| Mysqlmaster-bin.000002 | 120 |



Server2

Show master status;
+ ------------------------ + ---------- + -------------- + ------------------ + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------------ + ---------- + -------------- + ------------------ + ------------------- +
| Mysqlmaster-bin.000001 | 336 |


Binary Log Path

Run on Server 2

Change master to MASTER_HOST = '192. 168.0.231 ', MASTER_USER = 'mongoop1', MASTER_PASSWORD = 'systop', MASTER_LOG_FILE = 'mysql-bin.mongo02', MASTER_LOG_POS = 192;


Run on Server 1

Change master to MASTER_HOST = '192. 168.0.234 ', MASTER_USER = 'mongoop2', MASTER_PASSWORD = 'systop', MASTER_LOG_FILE = 'mysqlmaster-bin.mongo01', MASTER_LOG_POS = 192;



Enable the copy Function

Both machines run
Start slave;

View the replication connection status
Run
Show slave status \ G;
View Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Yes indicates that the startup is normal.

Test: both servers update data to check whether the data is updated.

Old tie!