Principles and configuration methods of MySQL master-slave Replication)

I. Principles of Replication

MySQL replication tracks all database changes (updates, deletions, and so on) in binary logs based on the master server ). Each slave server receives updates recorded by the master server to the storage of its binary logs from the master server so that the slave server can perform the same update on its data copy.

One way to copy data from the MASTER server to the slave server is to use the load data from master statement. Note that load data from master currently only works on the MASTER server where all tables use the MyISAM storage engine. In addition, this statement gets global read locks.

MySQL uses three threads to execute the replication function. One of them is on the master server and the other two are on the slave server. When start slave is issued, an I/O thread is created from the server to connect to the master server and send statements recorded in its binary log.
The master server creates a thread to send the binary log content to the slave server. This thread can identify the Binlog Dump thread in the output of show processlist on the master server.
Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to a local file in the data directory of the slave server, that is, relay logs.
The first thread is an SQL thread, which is created from the server to read relay logs and execute updates contained in logs.
Multiple slave servers are created to create a thread for each currently connected slave server. Each slave server has its own I/O and SQL threads.

Ii. Status of the replication thread

1. Copy the status of the main thread

Copy codeThe Code is as follows:
Sending binlog event to slave
Binary logs are composed of various events. An event usually adds some other information to an update. The thread has read an event from the binary log and sent it to the slave server.
Finished reading one binlog; switching to next binlog
The thread has read the binary log file and is opening the next log file to be sent to the slave server.
Has sent all binlog to slave; waiting for binlog to be updated
The thread has read all major updates from the binary log and sent them to the slave server. The thread is currently idle, waiting for a new event in the binary log caused by a new update on the master server.
Waiting to finalize termination
A very simple state that occurs when the thread is stopped.

2. Copy the status from the I/O thread

Copy codeThe Code is as follows: Connecting to master
The thread is trying to connect to the master server.

Checking master version
The status that appears immediately after the connection is established with the master server.

Registering slave on master
The status that appears immediately after the connection is established with the master server.

Requesting binlog dump
The status that appears immediately after the connection is established with the master server. The thread sends a request to the master server to obtain the binary log Content starting from the request's binary log file name and location.

Waiting to reconnect after a failed binlog dump request
If a binary log dumping request fails (because there is no connection), the thread goes to sleep state and tries to reconnect regularly. You can use the-master-connect-retry option to specify the interval between retries.

Reconnecting after a failed binlog dump request
The thread is trying to reconnect to the master server.

Waiting for master to send event
The thread has been connected to the master server and is waiting for the arrival of binary log events. If the master server is idle, it will take a long time. Timeout occurs if the waiting duration is slave_read_timeout seconds. In this case, the thread considers the connection to be interrupted and attempts to reconnect.

Queueing master event to the relay log
The thread has read an event and copied it to the relay log for processing by the SQL thread.

Waiting to reconnect after a failed master event read
An error occurred while reading (because there is no connection. The thread will sleep for master-connect-retry seconds before attempting to reconnect.

Reconnecting after a failed master event read
The thread is trying to reconnect to the master server. After the connection is re-established, the status changes to Waiting for master to send event.

Waiting for the slave SQL thread to free enough relay log space
A non-zero relay_log_space_limit value is being used, and the relay log has grown to exceed the combined size. The I/O thread is waiting until the SQL thread processes the relay log Content and deletes some relay log files to free up enough space.

Waiting for slave mutex on exit
A very simple state that occurs when the thread is stopped.

3. Copy the status from the SQL thread

Copy codeThe Code is as follows:
Reading event from the relay log
The thread has read an event from the relay log and can process the event.

Has read all relay log; waiting for the slave I/O thread to update it
The thread has processed all the events in the relay log file and is waiting for the I/O thread to write new events to the relay log.

Waiting for slave mutex on exit
A very simple state that occurs when the thread is stopped.

Iii. Copy transfer and status files

The slave server receives logs uploaded from the master server by relay logs. The status file is used to record what logs have been received from the master server and what logs have been recovered.

Relay logs are in the same format as binary logs and can be read using mysqlbinlog. After the SQL thread executes all the events in the relay log and no longer needs them, it will be automatically deleted immediately. You can use the-relay-log and-relay-log-index Server options to overwrite the default relay log and index file name. The index file name is used to record the current use of relay logs.

Under the following conditions, a new relay log will be created:
1. Create a New Relay log each time the I/O thread starts.
2. When the log is refreshed; for example, use flush logs or mysqladmin flush-logs.
3. When the current relay Log File becomes too large. How to determine the meaning of "too big:
Max_relay_log_size, if max_relay_log_size> 0
Max_binlog_size, if max_relay_log_size = 0
Status File Names default to master.info and relay-log.info. The IO thread updates the master.info file, and the SQL thread updates the relay-log.info file.
The correspondence between the lines in the file and the columns displayed by show slave status is as follows:
Master.info file:
Copy codeThe Code is as follows: line description
1. the row number in the file
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6. Password (not displayed by show slave status)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key

Relay-log.info files:
Copy codeThe Code is as follows: line description
1 Relay_Log_File
2 Relay_Log_Pos
3 Relay_Master_Log_File
4 Exec_Master_Log_Pos

When backing up data from the server, you should also back up the two small files and relay log files. They are used to continue copying data from the server. If a relay log is lost but there is still a relay-log.info file, you can check the file to determine the extent to which the SQL thread has executed binary logs in the master server. You can then use the Master_Log_File and Master_LOG_POS options TO execute the change master to command the slave server TO re-read binary logs from this point. Of course, binary logs must still be stored on the master server. Therefore, it is recommended that you disable the feature of automatically deleting relay logs and manually write shell corners to prevent full space.

Iv. copy configuration steps

1. Create a User Dedicated to replication (we recommend that you do this) and use this account to log on to the master server from the server:
Copy codeThe Code is as follows:
Grant replication slave on *. * TO 'rep '@' % 'identified by 'logzgh ';


If you plan to use the load table from master or load data from master statement FROM the slave server host, you need to grant other permissions to the account:

Grant Account SUPER and RELOAD global permissions.
Grant SELECT permission to all tables to be loaded. Tables on the MASTER server that cannot be selected by this account are ignored by the load data from master.

2. Move the database file to the slave server

Scenario 1: If only the MyISAM table is used
Copy codeThe Code is as follows: mysql> flush tables with read lock;
(Refresh all tables and stop other writes. Do not exit the client to keep the read lock valid. If you exit, the read lock will be released .)
A simple method is to package and compress the data directory.

Copy codeThe Code is as follows: $ tar-cvf/home/mysql/snapshot.tar./data (on the master)
$ Tar-xvf/home/mysql/snapshot.tar (on slave)

The mysql database may not need to be synchronized because the permission table on the slave is different from that on the master database. In this case, exclude the compressed package when unzipping it.
At the same time in the compressed package does not contain any log files, and status files master.info, relay-log.info.

Copy codeThe Code is as follows: mysql> show master status;
+ ------ + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------ + ---- + ----- + ------ +
| Mysql-bin.000058 | 45036137 |
+ ------ + ---- + ----- + ------ +

Mysql> unlock tables;

Case 2: If InnoDB table is used

Method 1: Use the InnoDB Hot Backup tool. It can ensure snapshot consistency without requesting any lock on the master, and the log file name and offset location have been recorded in the snapshots to be used on the slave later.

Method 2: record the current log file and offset, and run the following command before the master is disabled:
Copy codeThe Code is as follows: mysql> flush tables with read lock;
Mysql> show master status;

Write down the log file and offset in the displayed result as soon as possible. Then, disable the master without unlocking, and ensure that the snapshots on the master are consistent with the record results.

Shut down the master server, $ mysqladmin-u root shutdown
Copy InnoDB data files, log files, and table structure definition files (. frm files ).

Scenario 3: it can be used in both MyISAM and InnoDB tables
Perform SQL dumping on the master without backing up binary logs as described above. Run the mysqldump-master-data command and then dump the result file to the slave.
However, this is slower than copying binary logs.

3. Modify the my. cnf File
My. cnf file on the master: (restart takes effect)
Copy codeThe Code is as follows: [mysqld]
Log_bin
Server_id = 1 (the value is a positive integer between 1 and 2 ^ 32-1)
My. cnf file on slave:
Copy codeThe Code is as follows: [mysqld]
Server_id = 2 (ID must be different from the master ID. If multiple slave instances exist, each slave must have a unique id .)

Configure slave extension options
Copy codeThe Code is as follows: master_host = db-master.mycompany.com
Master_port = 3306
Master_user = rep
Master_password = freitag
Master_connect_retry = 60 (if the master node is down or the slave is disconnected, slave periodically tries to connect to the master node. the Retry Interval is controlled by this option. The default value is 60 seconds .)
Report_host = db-slave.mycompany.com
Slave_net_timeout = 3600 (slave will be processed after 3600 seconds by default, if data from the master has not been received, it will be treated as a network disconnection .)

The server considers master.info to have a higher priority than my. cnf in the configuration file,
When slave is started for the first time, master.info does not exist. It reads the option values from my. cnf and saves them in master.info.
When you restart slave next time, it only reads the content of master.info, but does not read the option value in my. cnf.
TO use different option values, you can delete master.info and restart slave, or use the change master to Statement (recommended) TO reset the option values.

4. Start the slave server thread

Copy codeThe Code is as follows: mysqld_safe-user = mysql-skip-slave-start & (starts the MySQL server, but does not start the slave)
Set master_log_file and other parameters
Mysql> change master to MASTER_HOST = 'qa-sandbox-1 ′,
MASTER_USER = 'rep ',
MASTER_PASSWORD = 'logzgh ',
MASTER_LOG_FILE = 'mysql-bin.000007 ′,
MASTER_LOG_POS = 471632;

Mysql> start slave;
After these programs are executed, the slave server should connect to the master server and supplement any updates that have occurred since the snapshot.
If you forget to set the server-id value of the master server, the slave server cannot connect to the master server.

Note: To ensure maximum tolerance and consistency set by InnoDB replication,
Innodb_flush_log_at_trx_commit = 1 and sync-binlog = 1 should be used in the my. cnf file of the master server.
Copy codeThe Code is as follows:
Mysql> show variables; (check whether it is read-only. This option prevents slave from updating data except the slave thread or the SUPER User, make sure that slave does not accept updates from other clients .)
Mysql> show processlist; (check whether slave-start is enabled)

Start slave while starting mysql:
Copy codeThe Code is as follows: mysqld_safe-user = mysql-read-only & (start the MySQL server and the slave I/O thread at the same time)

Mysql> show slave statusg;

5. Switch slave to master, on slave:

Copy codeThe Code is as follows: mysql> stop slave;
Mysql> reset master;

V. Replication startup options

-Read_only
This option allows the slave server to only allow updates from the slave server thread or users with SUPER permissions. The slave server does not accept updates from customers.

-Replicate_do_db = db_name
The slave server is told to only replicate statements with the default database (selected by USE) db_name. To specify multiple databases, you must use this option multiple times. Each database is used once. Do not copy cross-database statements

-Replicate_do_table = db_name.tbl_name
Tells the slave server thread to copy only the specified table. To specify multiple tables, use this option multiple times. Each table is used once. Compared with-replicate-do-db, cross-database update is allowed.

-Replicate_ignore_db = db_name
The slave server is told not to copy the statement where the default database (selected by USE) is db_name. To ignore multiple databases, you must use this option multiple times and use it once for each database.

-Replicate-ignore-table = db_name.tbl_name
Tells the slave server thread not to copy or update any statements of the specified table (even if the statement may update other tables ). To ignore multiple tables, use this option multiple times.

-Replicate_wild_do_table = db_name.tbl_name
Tells the slave server thread to restrict the replication of updated tables to match the specified database and table name mode statements. The pattern can contain wildcards '%' and '_', which have the same meaning as the LIKE pattern matching operator. To specify multiple tables, use this option multiple times. Each table is used once. This option can be updated across databases.

-Replicate_wild_ignore_table = db_name.tbl_name
Tell the slave server thread not to copy statements that match the given wildcard pattern. To ignore multiple tables, use this option multiple times. This option can be updated across databases.

-Replicate_rewrite_db = from_name-> to_name
Indicates that if the default database (selected by USE) on the slave server is from_name on the master server, it is translated as to_name. Only statements containing tables are affected.

-Report_host = slave_name
Host Name or IP address reported to the master server during Server registration. This value appears in the output of show slave hosts on the master server. If you do not want the slave server to register on the master server, do not set this value.

-Report_port = slave_port
The TCP/IP Port Number connecting to the slave server, which is reported to the master server during Server registration.

-Skip_slave_start
Tells the slave server that the slave server thread is not started when the server is started. Use the start slave statement to START the thread later.

-Slave_skip_errors = [err_code1, err_code2 ,... | All]
Generally, when an error occurs, the replication stops. This gives you a chance to manually solve the inconsistency problem in the data. This option tells the SQL thread of the slave server to continue copying when the statement returns an error in any option value.
For example:
-Slave-skip-errors = 255.2,1053
-Slave-skip-errors = all

Vi. Configuration replication without stopping services

Method 1:

If you have backed up the MASTER server at a certain time point and recorded the binary log name and offset of the corresponding snapshot (output using the show master status Command), follow these steps:

1. Make sure that a unique server ID is assigned to the slave server.
2. Copy the backup file to the slave server.
3. Execute the following statement on the slave server and fill in the appropriate values for each option:
Copy codeThe Code is as follows: mysql> CHANGE MASTER
-> MASTER_HOST = 'master _ host_name ',
-> MASTER_USER = 'master _ user_name ',
-> MASTER_PASSWORD = 'master _ pass ',
-> MASTER_LOG_FILE = 'recorded _ log_file_name ',
-> MASTER_LOG_POS = recorded_log_position;

4. Execute the start slave Statement on the SLAVE server.

If you have not backed up the master server, here is a fast program for creating backups. All steps should be performed on the master server host.

1. Issue the statement:
Copy codeThe Code is as follows: mysql> flush tables with read lock;
2. Execute this command (or its variant) when the lock is still applied ):
Copy codeThe Code is as follows: shell> tar zcf/tmp/backup.tar.gz/var/lib/mysql
And copy it to the slave server.
3. Issue the statement and ensure that the output used later is recorded:
Copy codeThe Code is as follows: mysql> show master status;
4. Release the lock:
Copy codeThe Code is as follows: mysql> unlock tables;

Method 2:

An optional method is to dump the master server's SQL statement instead of the binary copy in the previous step. To do this, you can use mysqldump-master-data on the master server and load the SQL to your slave server. However, this is slower than binary replication.

VII. Others

1. You cannot copy data from the master server that uses the new binary log format to the slave server that uses the old binary log format.

2. When upgrading the slave server, you should first shut down the slave server, upgrade to the corresponding 5.1.x version, then restart the slave server and Start copying again. The slave server of Version 5.1 can read the old relay logs written before the upgrade and execute the statements contained in the logs. The relay logs created from the server after the upgrade are in the 5.1 format.

3. the same global character set and verification rules (-default-character-set and-default-collation) must always be used on the master server and slave server ). Otherwise, a duplicate key value error occurs on the slave server, because the key value that is considered unique in the master server's character set may not be unique in the slave server's character set.

4. Q: Do I need to always connect to the master server?
A: No, no. The slave server can be down or disconnected for several hours or even several days. After the slave server is reconnected, the new information is obtained.

5. Q: How do I know the latest comparison between the slave server and the master server? In other words, how do I know the date of the last query copied from the server?
A: You can view the results of the Seconds_Behind_Master column of the show slave status statement.

6. Q: How can I force the master server to block updates until synchronization from the slave server?
A: Use the following steps:
1. Execute these statements on the master server:
Copy codeThe Code is as follows: mysql> flush tables with read lock;
Mysql> show master status;
Records the log name and offset output by the SHOW statement. These are the copy coordinates.

2. on the slave server, issue the following statement. The parameter of the Master_POS_WAIT () function is the copy coordinate value obtained in the previous step:
Mysql> SELECT MASTER_POS_WAIT ('Log _ name', log_offset );
The SELECT statement is blocked until the slave server reaches the specified log file and offset. At this time, the slave server synchronizes with the master server, and the statement is returned.

3. On the master server, issue the following statement to allow the master server to start processing updates again:
Copy codeThe Code is as follows: mysql> unlock tables;

7. Q: How to Improve the system performance through replication?
A: You should set A server as the master server and direct all writes to the server. Configure as many slave servers and stack space as possible based on the budget, and distribute read operations between the master server and slave server. You can also use the-skip-innodb,-skip-bdb,-low-priority-updates, and-delay-key-write = ALL options to start the slave server, to increase the speed on the slave server. In this case, the slave server uses non-transaction MyISAM tables instead of InnoDB and BDB tables to improve the speed.