Detailed operation process of MySQL master/Master Synchronization
This document is recorded when MySQL dual-master is deployed online at the company. For security purposes, IP addresses are changed to intranet IP addresses.
Version:
# Mysql-V
Mysql Ver 14.14 Distrib 5.5.37, fordebian-linux-gnu (x86_64) using readline 6.2
1. Enable the bin-log function for the master database and configure the server-id
Modify the my. cf configuration file, enable the bin-log function, and configure the server-id.
# Cat/etc/mysql/my. cnf
[Myqld]
Server-id = 1
Log_bin =/var/log/mysql/
Slave-net-timeout = 60
# The default value of salve-net-timeout is 3600 seconds. The shortening time is to prevent the illusion of Double YES.
# (In fact I have met, reference address: http://www.cnblogs.com/billyxp/p/3470376.html)
If you want to specify the databases to be synchronized or not to be synchronized, you can use the following parameters:
# Binlog-do-db = osyunweidb # Name of the database to be synchronized. If multiple databases exist, repeat this parameter. Each database has one row.
# Binlog-ignore-db = mysql # Do not synchronize the mysql System Database
2. Check whether bin-log and server-id are enabled:
View the command show variables like 'Log _ bin'; show variables like 'server _ id ';
Mysql> show variables like 'Log _ bin ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Log_bin | ON |
+ --------------- + ------- +
1 rowin set (0.00 sec)
Mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 1 |
+ --------------- + ------- +
1 rowin set (0.00 sec)
3. Create a copy authorized user
Mysql> grant replication slave on *. * to replication @ '%' identified by '000000'; # authorize this user to copy all tables.
Mysql> flush privileges; # refresh Permissions
4. Lock table, record the log-binfile name and location
Mysql> flush tables with read lock; # lock all tables. data cannot be written to the database.
QueryOK, 0 rows affected( 0.05 sec)
Mysql> show master status; # view the latest bin-log file and location
+ ------------------------ + ------------ + --------------------- + ----------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------------ + ------------ + --------------------- + ----------------------- +
| Mysql-bin.000001 | 26314 |
+ ------------------------ + ------------ + --------------------- + ----------------------- +
1row in set (0.00 sec)
5. Lock Table Status full backup mysql Data
Because you exit the current mysql login window, the table lock function becomes invalid. You need to clone a session for full backup.
# Mysqldump-uroot-p-A-B>/tmp/mysql_bak_2014_10_30. SQL .gz
Check the backup data size to confirm that the backup is successful.
# Ls-l/tmp/mysql_bak_2014_10_30. SQL .gz
-Rw-r -- 1 root 339222 October 30 11: 01/tmp/mysql_bak_2014_10_30. SQL .gz
6. Unlock the table
Mysql> unlock tables;
Or quit.
7. Enable the bin-log function from the slave database and configure the server-id
After the bin-log function is enabled from the slave database, the synchronization configuration will be performed on the master database.
# Cat/etc/mysql/my. cnf
[Myqld]
Server-id = 2
Log_bin =/var/log/mysql/mysql-bin.log
Slave-net-timeout = 60
# The default value of salve-net-timeout is 3600 seconds. The shortening time is to prevent the illusion of Double YES.
# (Reference: http://www.cnblogs.com/billyxp/p/3470376.html)
#/Etc/init. d/mysqlrestart
8. Check whether the slave database bin-log and server-id are enabled.
View the command show variables like 'Log _ bin'; show variables like 'server _ id ';
Mysql> show variables like 'Log _ bin ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Log_bin | ON |
+ --------------- + ------- +
1row in set (0.00 sec)
Mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 2 |
+ --------------- + ------- +
1row in set (0.00 sec)
9. Import Full backup data from the master database
Extract data from the slave database.
# Gzip-d mysql_bak_2014_10_30. SQL .gz
Log onto mysql to import data
Mysql> source/root/mysql_bak_2014_10_30. SQL
10. Record the bin-log information of the slave Database
When importing full backup data from the slave database, the content of the master database is the same as that of the slave database, but the bin-log location is not necessarily the same.
Mysql> show master status; # view the latest bin-log file and location
+ ------------------------ + ------------ + --------------------- + ----------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------------ + ------------ + --------------------- + ----------------------- +
| Mysql-bin.000003 | 2328055 |
+ ------------------------ + ------------ + --------------------- + ----------------------- +
1row in set (0.00 sec)
11. Synchronize master database settings from the slave Database
Here, the binlog file and Location status are the Location status viewed by the master database in show master status when the master database locks the table in Step 4.
CHANGE MASTER
MASTER_HOST = '10. 0.0.2 ',
MASTER_PORT = 8306,
MASTER_USER = 'replicase ',
MASTER_PASSWORD = '000000 ',
MASTER_LOG_FILE = 'mysql-bin.000001 ',
MASTER_LOG_POS = 26314;
12. enable synchronization from the slave database and check whether the synchronization is successful.
Use start slave to enable the synchronization function. Use show slave status \ G to check whether the synchronization is successful.
Mysql> start slave;
QueryOK, 0 rows affected( 0.00 sec)
Mysql> show slave status \ G # \ G does not output by table
* *************************** 1. row ***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 10.0.0.2
Master_User: replication
Master_Port: 8306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 136270
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 72697
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98758
Relay_Log_Space: 110366
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 622 # Check the master-slave synchronization latency. If the latency is high, optimization may be required.
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1row in set (0.00 sec)
# The SQL thread and I/O thread are both YES, and the slave configuration is successful.
13. Configure synchronization slave database for the master database
Since the slave database is full-backup, the replication account configured on the master database is also imported, so there is no need to authorize the replication user on the slave database.
The binlog file and Location status on the slave database are the Location status Viewed from the show master status when the slave database is just imported.
CHANGEMASTER
MASTER_HOST = '2017. 16.0.2 ',
MASTER_PORT = 3306,
MASTER_USER = 'replicase ',
MASTER_PASSWORD = '000000 ',
MASTER_LOG_FILE = 'mysql-bin.000003 ',
MASTER_LOG_POS = 2328055;
# Modify the relevant information and paste the configuration directly in mysql.
14. enable synchronization and check whether the synchronization is successful.
Use start slave to enable the synchronization function. Use show slave status \ G to check whether the synchronization is successful.
Mysql> start slave;
QueryOK, 0 rows affected( 0.00 sec)
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 172.16.0.2
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 556
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1row in set (0.00 sec)
# Both the IO thread and the SQL thread are normal.
15. Mutual master-slave Test
Create a database for each of the two mysql databases to check whether the two databases can be synchronized.
Run create database test01 on the master database;
Execute create database test02 from the database;
Run show databases on the two databases to check whether there are both test01 and test02 tables.
My dual-master test is successful.
Production Environment MySQL master/Master synchronization primary key conflict handling
Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization
MySQL master/Master synchronization Configuration
This article permanently updates the link address: