MySQL master-slave synchronization operation documentation

MySQL master-slave synchronization operation documentation

From NSOP-WIKI

Directory

1. Synchronization principles

2. Basic Steps for synchronization settings (the following settings involve modifications to the my. CNF file)

2.1 configure the master (she4 zhi4 zhu3) server: Enable bin-log and synchronize accounts

2.2 set slave server: Set the content to be synchronized

2.3 copy master server data to slave server

2.3.1 Method 1: Stop the master server or one of the slave servers and copy data

2.3.2 Method 2: Set a lock on the master server or one of the slave servers to prohibit all read/write operations and copy data.

2.4 start Synchronization

3. instance reference:

4 FAQ

1. Synchronization principles

MySQL replication tracks all database changes (updates, deletions, and so on) in binary logs based on the master server ).

MySQL uses three threads to complete replication. The specific distribution is (gong1 zuo1 _ ju4 Ti3 fen1 bu4 shi4) the master has one related thread and the last two related threads;

The main thread can be understood as the BINLOG dump thread in the output of show processlist on the master server, Io thread and SQL thread on the slave server;

Master
Server creation sends the content in BINLOG to the slave server. Read the BINLOG of the master server from the server I/O thread
The content sent by the dump thread and the data is copied to the relay-log file in the server data directory. The SQL thread is used to read the relay log file (xian4 cheng2
Yong4 yu2 du2 qu3 zhong1 ji4) log and execute the updates contained in the log.

MySQL replication is unidirectional and asynchronous

Same as MySQL
The Step Mechanism records all database updates and deletions Based on the master node in binary logs. Therefore
Zhi Li _ Yin CI _ xiang) to enable the synchronization mechanism, you must enable the binary synchronization mechanism on the master node.
Logs. Each slave receives an update operation recorded in the binary log on the master. Therefore, a copy of this operation is executed on the slave. It should be very important to realize that
Logs are updated only when binary logs are enabled. All
Slave must copy existing data on the master when enabling binary logs. If the data on the slave (shang4 de0 shu3
Ju4) the data used when binary logs are enabled on the master node is not
If they are the same, slave synchronization will lose zookeeper. One of the methods used to copy the data on the master is to execute load data from Master on slave.
Statement. Note that (Yu Ju _ Bu Guo Yao Zhu Yi), load data from Master is from MySQL 4.0.0
(Zhi Hou Cai Kai Shi ke Yi), and only supports MyISAM on the master
Type table. Similarly, this operation requires a global read lock, so that no update operation will be performed on the master when the log is transferred to the slave. (Shang JIU Bu
Hui you Geng Xin Cao Zuo Le _) when the free lock table hot backup is implemented (in MySQL
5.0), the global read lock is unnecessary. Because of these restrictions, we recommend that you load data from
The master statement, or (Yu3 ju4 _ huo4) allows a long read lock on the master. Because each system loads data from
Master
The speed varies. A good measurement rule is that 1 MB of data can be copied per second. This is just a rough estimate, but the master and slave are both Pentium MHz machines and use
100 Mbit/s network connection can achieve this speed. After the master data has been fully copied on the slave, you can connect to the master and wait for updates.
(Shang ran Hou Deng Dai Chu Li Geng Xin le ). If
When the server or Server Load balancer is disconnected, Server Load balancer periodically tries to connect to the Server Load balancer instance until it can be reconnected and waits for updates. Retry Interval
-- Master-connect-retry option to control it, its Mo (xuan3 xiang4 lai2 kong4 zhi4 _ TA1 de0
Mo4) the value is 60 seconds. Each slave records the log location when it is disabled. Master does not know how many slave connections (Lian
Jie.

The MySQL synchronization function consists of three lines.
(One on the master and two on the slave. After the start slave statement is executed
Create an I/O thread. The I/O thread connects to the master and requests the master to send statements in binary logs. Create
Jian Yi Ge) thread to Send Logs to slave. In the result of executing the show processlist Statement on the master node
The BINLOG dump thread is. The I/O thread on slave reads the BINLOG dump of the master.
Statement sent by the thread, and copy them to the relay log (
Logs. The third is the SQL thread, which salve uses to read relay logs and then execute them to update data. As mentioned above, each mster/slave has three threads. Each
There are multiple threads on the master. It creates a thread for each slave connection, and each slave only has the I/O and SQL threads. In MySQL 4.0.2
Previously, synchronization only required two threads (one master and one slave each ). The I/O and SQL threads on the slave are merged into one. It does not use relay logs. On slave
The advantage of using two threads is to split the read log and execution into two independent tasks. If the task is executed slowly, the log reading task will not be slowed down. For example, if slave is stopped for a period of time

The I/O thread can quickly read all logs from the master after the slave is started, although the SQL thread may lag behind the I/O thread for several hours. If slave does not have all SQL threads
After the execution is complete, stop (zhi xing Wan JIU
Ting), but the I/O thread has read all the update logs and saved them in the local relay-log, therefore, after the slave is started again, it will continue to run
Line them. This allows you to clear the binary log on the master, because the slave does not need to read the Update log from the master. Execute show processlist
The statement will tell us what happens on the master and slave.

2. Synchronization settings (Tong bu she zhi ji Ben) (The following settings involve modifications to the my. CNF file)

2.1 set the master server: Enable bin-log and synchronize accounts

Server-id = N # Set ID

Log-bin # enable bin-log record. to specify the BINLOG file name, use the following syntax:

# Log-bin = BINLOG # The BINLOG file name is binlog.0, binlog.1 ......

Set-variable = BINLOG-ignore-DB = MySQL # update logs of MySQL are not recorded.

BINLOG-do-DB = # record the Update log of the specified database. You can also process the update only table content separately.

In addition, pay attention to two environment variables:

Innodb_flush_log_at_trx_commit = 1 # default: 1. In databases that use transactions, we strongly recommend that you explicitly set this value to 1 to ensure that the log buffer is written to the log file when each transaction is committed, and synchronize the write operation to the disk.

Sync_binlog = 1 # synchronize the operation to the hard disk after one BINLOG write operation. Setting a higher value can effectively reduce Io and achieve better performance, but it increases the risk of Bilog loss.

Authorize the synchronization account on the slave server:

Mysql> grant replication slave on *. * to synchronize account username @ slave server IP address identified by 'synchronize account password ';

To have the "load table from Master" and "load data from Master" permissions on the server, you also need to add "file" and "select" permissions;

2.2 set slave server: Set the content to be synchronized

Server-id = N # Set ID

Master-host = # Set the IP address of the master server

Master-user = # Set the synchronization account user name

Master-Password = # Set the password for the synchronization account (she4 zhi4)

Master-Port = # Set the TCP/IP Port. The default value is 3306.

Set-variable = replicate-ignore-DB = MySQL # Set the name of the ignored synchronization Database

Set-variable = replicate-db-DB = # Set the database name in the same pass_adm step. If you need to set the database name separately, you can set multiple data records for the table level. Please set it multiple times.

Here, you can use change master to modify the settings after logging on to the slave database.

2.3 copy master server data to slave server

2.3.1 Method 1: Stop (ting2 zhi3) the master server or one of the slave servers and copy data

Stop mysqld on the server where you want to copy data and stop the service completely. If it is a slave server, you need to stop the synchronization process before stopping mysqld.

Mysql> stop slave;

$ Mysqladmin-uroot-P Shutdown

After you confirm that mysqld is stopped, copy the data file IB * And master.info to the database directory to be synchronized and other related files to the new machine. BINLOG and relay-log do not need to be copied

2.3.2 Method 2: Set a lock on the master server or one of the slave servers to prohibit all read/write operations and copy data.

Run the following command on the master server:

Mysql> flush tables with read lock;

Export data to the slave database (you can use tar, SCP, mysqldump, and other methods, here the implementation is more flexible ):

$ Cd ~ /MySQL/

$ Tar zcvf var.tar.gz var/# The data directory may not be the VaR directory.

Then the var.tar.gz package is added to the slave server and decompressed to the suspect folder.

Run the following command on the master server:

Mysql> unlock tables;

Description
Ming: there are too many ways to copy data from the master server to the slave server: You can directly stop copying the mysqld Program (Cheng Xu
Kao) bei data, can be packaged and processed in tar mode, can be processed in SCP mode, and can be directly exported to the structure of the suspect orders table. The specific implementation method depends on the specific application, the number of copies
No data is written or changed.

2.4 start Synchronization

It is recommended to start mysqld from the database using -- skip-slave-start. After Entering MySQL, start slave to start synchronization.

Description
Ming:
Mysqld_safe is usually used to remember the startup parameters, such as -- skip-slva-start. If the slave database restarts due to unexpected reasons and the database is used during startup
Mysqld_safe -- skip-slave-start, you need to manually start the synchronization process, otherwise the synchronization process will always be in the stopped state

Run the following command on the server:

Mysql> show slave status

If the values of slave_io_running and slave_ SQL _running are "yes", it indicates that the slave I/O and SQL threads are running normally.

Note: We recommend that you use the change master to statement to set synchronization parameters. For details, refer to the help change master to syntax below:

Master_def: aster_def [master_def]...

Master_def:

Master_host = 'host _ name'

Master_user = 'user _ name'

Master_password = 'Password'

Master_port = port_num

Master_connect_retry = count

Master_log_file = 'master _ log_name'

Master_log_pos = master_log_pos

Relay_log_file = 'relay _ log_name'

Relay_log_pos = relay_log_pos

Master_ssl =

Master_ssl_ca = 'Ca _ file_name'

Master_ssl_capath = 'Ca _ directory_name'

Master_ssl_cert = 'cert _ file_name'

Master_ssl_key = 'key _ file_name'

Master_ssl_cipher = 'cipher _ list'

3. instance reference:

My. CNF configuration of primary database:

Server-id = 1 # Set ID

Log-bin # enable bin-log record

BINLOG-do-DB = Iknow # record the BINLOG update record of the specified database, one row for each database

BINLOG-do-DB = iknow_adm

BINLOG-do-DB =...

Now, create a new slave database B. Perform the following steps:

1. modify the configuration file: modify my. CNF and add the following items (_ Jia Ru Xia Xiang ):

Server-id = 2 # specify the server ID. Each server ID must be unique.

Log-bin # enable BINLOG

Master-connect-retry = 60 # Retry Interval for connecting to the master database

Master-host = XXX. XXX # specifies the IP address of master database A. virtual IP addresses cannot be used.

Master-Port = 3306 # specify the master database Port

Master-user = rep # specify the user name for synchronization as (Zhi Ding Tong Bu Yong Hu de Yong Hu Ming Wei) replication

Master-Password = rep # specify the password of the synchronization user as Password

Replicate-do-DB = Iknow # specify the database to be synchronized. Each database occupies one row.

Replicate-do-DB = iknow_adm

Replicate-do-DB =...

Log-slave-Updates # log on to the slave database for synchronization. the disk usage is large. We recommend that you remove it after the slave database runs stably. If you want to use it as the second-level primary database, this item is required

Here, you can use change master to modify the settings after logging on to the slave database.

2. copy Data: Copy data from database C to database B without providing external services (cong2 shu3 ju4 ku4 mei2 you3 dui4 wai4 ti2 gong1 FU2, therefore, you can directly stop copying data from the database.

Note:

Stop
From the database (Ting Cong Shu Ju Ku de Shi Hou), you need to explicitly execute stop slave and run show slave
Status, confirm that read_master_log_pos and exec_master_log_pos are consistent, and then execute mysqladmin-uroot
-P shutdown to stop mysqld.

Mysql> stop slave;

Mysql> show slave status

Make sure that read_master_log_pos is consistent with exec_master_log_pos.

$ Mysqladmin-uroot-P Shutdown

After mysqld is completely exited, run IB *, master.info, and the library CP under the VaR directory to the local directory var. $ date (mainly to shorten the service stop time of mysqld on C)

$ CD $ mysql/var; mkdir ../var. $ date

$ CP-r IB * Iknow iknow_adm master.info ../var. $ date/

$ SCP $ mysql/var. $ date B :~ /MySQL

3. Authorize synchronization for a slave database on the master database

Mysql> grant replication slave on *. * to replication @ machine B IP address identified by 'password;

4. Start mysqld on B

$ Mysqld_safe -- skip-slave-Start &

5. Go to MySQL to observe the slave status

Mysql> show slave status

6. Observe the master.info and relay-log.info

$ Cat $ mysql/master.info

$ Cat $ mysql/relay-log.info

You do not need to change the master here because master.info has recorded master_log_file and master_log_pos. This is a relatively safe and simple method.

7. Review and confirm that the replication slave configuration in my. CNF is correct. If the master.info file exists, MySQL is
But in some cases, such as the abnormal restart of mysqld, it may read the configuration in my. CNF. Therefore, we need to ensure that the replication in my. CNF
The slave configuration is correct.
Mysqld reads master.info preferentially under normal circumstances. Therefore, you must ensure that master.info does not exist in the primary database. Otherwise
(Shi4 bu4 cun2 zai4 de0 _ fou3 ze2 ke3 neng2 chu1 xian4 bu4) predictable consequences.

8. The change master to statement is used to explicitly change the master database in the MySQL command line. Execute (Ming Ling Xing Zhong Zhi) in the MySQL Command Line

Mysql> help change master

You can obtain detailed information about this statement. If you need to change the master database temporarily and do not want to restart the database after switching the master database, you can use the following statement to dynamically change the master database:

Mysql>
Change master to master_host = XXX. XXX master_user = 'replicase'
Master_password = 'Password' master_log_file = 'master-BINLOG .'
Master_log_pos = '';

The preceding statements have the following meanings:

Host Name or IP address of the master master_host Database

Master_user: the master database is the synchronization account authorized by the slave database.

Master_password the master database is the synchronization account password authorized by the slave database (zhu3 ku4 wei4)

BINLOG file name of master_log_file master database

BINLOG file offset of master_log_pos master database (Wen Jian Pian Yi)

Master_log_file
And master_log_pos must be in the master database startup (liang3 xiang4 xu1 yao4 zai4 qi3 dong4 zhu3 ku4
Zhi1) run the show Master STATUS Command to record the status and make sure that the status is correct. Otherwise, synchronization may fail.

4 FAQ

1.
SCP transfers the entire var directory to the new slave database (MU Lu Dao Xin de cong ku). After starting slave, it is found that synchronization (Hou _ fa
Xian Tong bu) cannot run normally. slave_io_running is normal, but slave_ SQL _running is in the no state, and a releylog file cannot be found in the MySQL error log.

A: This is caused by the existence of the reley-log.info file, which records the relaylog currently used by the database, while MySQL
Recognized relaylog
The name is named after the machine name. Therefore, if you copy the file from one machine to another, it cannot find the file. If you can confirm read_master_log_pos and
Exec_master_log_pos consistent, then you can directly Delete the relay-log.info, restart mysqld, and then start
Slave can solve the problem.

2. After starting slave,
The status of slave_io_running and slave_ SQL _running is no, and the log reports a message similar to "slave I/O thread:
Failed reading log event reconnecting to retry log 'tc-nsop-test00-bin .'
Position 08 "error

A: This is because the master database does not correctly authorize the synchronization account of the slave database (DA _ Zhe Shi You yu zhu Ku DUI Cong ku). After you change and confirm that the authorization is correct, restart slave to synchronize data.

3. if the error log displays the following prompt: "Warning: You shocould set server-ID to a non-0 value if master_host is set; we force server ID to 2 but this MySQL server will not act as a slave. ", and the status of slave_io_running and slave_ SQL _running is no

A: Check my. CNF of the master database. This error is caused by the absence of the server ID or Invalid server ID of the master database. After the master database server ID is changed and the master database is restarted, start slave to restore synchronization.

4. If the slave_ SQL _running status is no and the error log contains something similar to "error 'lock wait timeout exceeded; try restarting transaction' on query. Default Database :......" Such errors

A: This is because another SQL process locks the row while synchronizing the row, and the synchronization process waits for timeout due to a long lock time. Start slave directly.

5. How does the synchronization process of multiple slave databases affect the master database?

A: Normally, the IO and nic traffic of the master database increases. There is no speed limit for the BINLOG dump process of MySQL. Therefore, the BINLOG reading and data distribution are performed at full speed, which brings a great burden to the master database. In actual operations, try to start the slave database in sequence, and observe the master database Io after startup, so that the BINLOG dump process can start the next database after reading the BINLOG.