In Linux, specify mysql database data to configure the master-master synchronization instance _ MySQL

Configure the master-master synchronization instance bitsCN.com for mysql database data in Linux

I. concept:

① Database synchronization (master-slave synchronization-write data to the slave server while writing data to the master database)
② Database synchronization (Master-master synchronization-two database servers write data to each other)

II. example

Master/master database synchronization server configuration
Database Server (A) master database IP address: 192.168.1.134
Database Server (B) master database IP address: 192.168.1.138
The user name for synchronization between the two servers is: bravedu password: brave123

I. primary database operation settings ():

① Create a user IP address (non-local IP address) that can be connected with the synchronization user name)

Grant replication slave on *. * to 'bravedu' @ '2017. 192.% 'identified by 'brave123 ';
Flush privileges;

② Change the mysql configuration file

[Mysqld]
Server-id = 1
Log-bin =/www/mysql/binlog (the path must be based on your own installation settings)
Binlog-do-db = dbname (name of the database to be synchronized)
Binlog-ignore-db = mysql

# Synchronization between the master and slave databases

Log-slave-updates
Sync_binlog = 1
Auto_increment_offset = 1
Auto_increment_increment = 2
Replicate-do-db = dbname
Replicate-ignore-db = mysql, information_schema

Restart the mysql server

③ Check the master database synchronization status IP: ***. 134

Mysql> flush tables with read lock;
Mysql> show master status/G
* *************************** 1. row ***************************
File: mysql-bin.000001 (note to use this when setting the slave server)
Position: 106 (use this parameter when setting the slave server)
Binlog_Do_DB: dbname
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

Mysql> unlock tables;
* ***** The master server has been set up to the current location *******


II. Slave Database operation settings (B ):

① Create a synchronization user name

Grant replication slave on *. * to 'bravedu' @ '2017. 192.% 'identified by 'brave123 ';
Flush privileges;

② Change the mysql configuration file

[Mysqld]
Server-id = 2
Log-bin =/www/mysql/binlog (the path must be based on your own installation settings)
Binlog-do-db = dbname (name of the database to be synchronized)
Binlog-ignore-db = mysql, information_schema
# Different places from master-slave synchronization
Binlog-do-db = dbname
Binlog-ignore-db = mysql
Log-slave-updates
Sync_binlog = 1
Auto_increment_offset = 2
Auto_increment_increment = 2

Restart the mysql server

View the master database synchronization status IP: ***. 138

Mysql> flush tables with read lock;
Mysql> show master status/G
* *************************** 1. row ***************************
File: mysql-bin.000005 (note to use this when setting the slave server)
Position: 106 (use this parameter when setting the slave server)
Binlog_Do_DB: dbname
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
Mysql> unlock tables;

③ Specify the master database server synchronization command
Note: IP addresses are the IP addresses, user names, passwords, log_file, and log_post servers of the master server.
This operation may need to unlock the table, stop the database, and start the table after running.

Mysql> stop slave;
# Set 192.168.1.138 database server configuration, so the host configuration file information is 134 information
Mysql> change master to master_host = '192. 168.1.134 ', master_user = 'bravedu', master_password = 'brave123', master_log_file = 'MySQL-bin.000005 ', master_log_pos = 192;
# Set 192.168.1.134 database server configuration, so the host and other configuration file information is 134 information
Mysql> change master to master_host = '192. 168.1.138 ', master_user = 'bravedu', master_password = 'brave123', master_log_file = 'MySQL-bin.000001 ', master_log_pos = 192;
Mysql> start slave;
Mysql> unlock tables;

④ Checking the synchronization status of the primary database will result in a lot of information, but you can only check the two statuses if they are both yes.

Mysql> show slave status/G;
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

So far, the master database synchronization is complete.

BitsCN.com