Specify an instance of master master synchronization for mysql database data configuration under Linux

1. The concept:

1. Database synchronization (master and slave synchronization -- master database writes data to slave server at the same time)
(2) database synchronization (master master synchronization -- two database servers write data to each other)

2. For example

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

1. Master database operation Settings (A) :

Create the user IP address (not native IP) to which the synchronous user name is allowed to connect

grant replication slave on *.* to 'bravedu'@'192.168.1.%' identified by 'brave123';
flush privileges;

Change the mysql configuration file

[mysqld]  
server-id = 1  
log-bin=/www/mysql/binlog/binlog ( The path should be set according to your installation )
binlog-do-db = dbname ( The database name to synchronize ) 
binlog-ignore-db=mysql

# corresponds to master slave database synchronization in different places

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

View 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 here   This is used when setting up the slave server )
Position: 106             ( Note that this is used when setting up the slave server )
Binlog_Do_DB: dbname
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

mysql>unlock tables;
***** The primary server has been set up at the current location *******

2. Operation Settings from the database (B) :

Create a synchronized user name

grant replication slave on *.* to 'bravedu'@'192.168.1.%' identified by 'brave123';
flush privileges;

Change the mysql configuration file

[mysqld]  
server-id = 2  
log-bin=/www/mysql/binlog/binlog ( The path should be set according to your installation )
binlog-do-db = dbname ( The database name to synchronize ) 
binlog-ignore-db= mysql,information_schema
# Different places relative to 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

Check 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 here   This is used when setting up the slave server )
Position: 106             ( Note that this is used when setting up the slave server )
Binlog_Do_DB: dbname
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql>unlock tables;

Specify the master master database server synchronization instruction
Note: IP hosts IP, username, password,log_file,log_post
Perhaps this operation requires unlocking the table, stopping the database state, and starting it after running

mysql > stop  slave;
# Set up the 192.168.1.138 Database server configuration   then host  Profile information   is  134 The information of 
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=106;
# Set up the 192.168.1.134  Database server configuration   then host  Configuration file information   is  134 The information of 
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=106; 
mysql > start slave;
mysql > unlock tables;

(4) check the master database synchronization state will come out a lot of information but mainly look at the two states on the line if they are yes

mysql>show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

At this point, the master master database synchronization configuration is successfully completed.