MySQL master-slave replication Principle and configuration method (more detailed)

MySQL's database has the following high-availability architectures: Clustering, read-write separation, and master preparation. And two of the following are done by copying. The following is a brief introduction to the principles and configuration of replication, as well as some common issues

First, the principle of replication

MySQL replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log. Each slave server receives a saved update from the primary server that the primary server has logged to the binary log so that the server can perform the same updates to its copy of the data.

One way to copy the data from the master server to the slave server is to use the load data from master statement. Note that the load DATA from master currently works only on the primary server where all tables use the MyISAM storage engine. Also, the statement obtains a global read lock.

MySQL uses 3 threads to perform the replication function, 1 of which are on the primary server and two on the slave server. When the start slave is emitted, an I/O thread is created from the server to connect to the primary server and let it send statements that are recorded in the binary log.
The primary server creates a thread that sends the contents of the binary log to the slave server. The thread can identify the Binlog dump thread in the output of show processlist on the primary server.
Reads the contents of the primary server Binlog dump thread from the server I/O thread and copies the data to a local file from the server data directory, which is the relay log.
The 3rd thread is a SQL thread that is created from the server to read the trunk log and perform the updates contained in the log.
There are multiple primary servers created from the server to create a thread from the server for each current connection, and each slave server has its own I/O and SQL threads.

Second, the state 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 consist of various events, and an event is usually an update with some additional information. The thread has read an event from the binary log and is sending it to the slave server.
Finished reading one binlog; Switching to next Binlog
The thread has finished reading the binary log file and is opening the next log file to be sent to the slave server.
Have sent all binlog to slave; Waiting for Binlog to be updated
The thread has read all the major updates from the binary log and has been sent to the slave server. The thread is now idle, waiting for new events to appear in the binary log caused by new updates on the primary server.
Waiting to finalize termination
A very simple state that occurs when a thread stops.

2. Replication from I/O thread state

Copy CodeThe code is as follows: Connecting to master
Line is impersonating attempts to connect to the primary server.

Checking Master Version
A status that occurs immediately after a connection with the primary server is established.

Registering slave on Master
A status that occurs immediately after a connection with the primary server is established.

Requesting Binlog Dump
A status that occurs immediately after a connection with the primary server is established. The thread sends a request to the primary server to request the contents of the binary log starting from the requested binary log file name and location.

Waiting to reconnect after a failed Binlog dump request
If the binary log dump request fails (because there is no connection), the thread goes to sleep and then periodically tries to reconnect. 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 primary server.

Waiting for master to send event
The thread is already connected to the primary server and is waiting for the binary log event to arrive. If the primary server is idle, it will last for a long time. If the wait lasts slave_read_timeout seconds, a timeout occurs. At this point, 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 is copying it to the trunk log for processing by the SQL thread.

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

Reconnecting after a failed master event read
The thread is trying to reconnect to the primary server. When the connection is re-established, the state becomes waiting for the master to send event.

Waiting for the slave SQL thread to free enough relay log space
A non-0 Relay_log_space_limit value is being used, and the trunk log has grown to its combined size beyond that value. The I/O thread is waiting until the SQL thread processes the trunk log content and removes some of the trunk log files to free up enough space.

Waiting for slave mutexes on exit
A very simple state that occurs when a thread stops.

3. Copy from SQL Thread state

Copy CodeThe code is as follows:
Reading event from the relay log
The thread has read an event from the trunk log and can handle the event.

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

Waiting for slave mutexes on exit
A very simple state that occurs when a thread stops.

Iii. replication delivery and status files

Log from the server to receive the logs uploaded from the master server. And rely on the status file to record which logs have been received from the primary server and which logs have been recovered.

The trunk log is the same format as the binary log and can be read with Mysqlbinlog. When the SQL thread finishes executing all the events in the trunk log and is no longer needed, it is automatically deleted immediately. You can override the default trunk log and index file names with the –relay-log and –relay-log-index server options. The purpose of the index file name is to record that the relay log is currently in use.

A new trunk log is created under the following conditions:
1. Create a new trunk log each time I/O thread starts.
2. When the log is refreshed, for example, with flush logs or mysqladmin flush-logs.
3. When the current trunk 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
The status file name defaults to Master.info and Relay-log.info. Where the IO thread updates the master.info file, the SQL thread updates the Relay-log.info file.
The correspondence between the lines in the file and the columns shown in Show SLAVE status is:
Master.info file:

Copy CodeThe code is as follows: Line description
1 line numbers in the file
2 Master_log_file
3 Read_master_log_pos
4 Master_host
5 Master_user
6 password (not shown by show SLAVE status)
7 Master_port
8 Connect_retry
9 master_ssl_allowed
Ten Master_ssl_ca_file
Master_ssl_ca_path
Master_ssl_cert
Master_ssl_cipher
Master_ssl_key

Relay-log.info file:

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 these two small files as well as the trunk log files. They are used to resume replication after recovering data from the server. If you lose the trunk log but still have the Relay-log.info file, you can determine the extent to which the SQL thread has executed the binary log in the primary server by examining the file. You can then use the Master_log_file and Master_log_pos options to perform change Master to tell the server to re-read the binary log from that point. Of course, the binary logs are required to remain on the primary server. Therefore, it is best to recommend the automatic removal of the characteristics of the relay log off, manually write shell angle to prevent space full problem.

Iv. Configuration steps for replication

1. Create a user dedicated to replication (recommended) to log on to the primary server from the server using the account:

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 the load DATA from master statement from a subordinate server host, you need to grant the account additional permissions:

Grant account super and reload global permissions.
Grant SELECT permission for all tables that you want to mount. Any table on the primary server for which the account cannot select is ignored by the load DATA from master.

2. Move the database files to the slave server

Case one: If only the MyISAM table is used

Copy CodeThe code is as follows:mysql> FLUSH TABLES with READ LOCK;
(Refresh all tables and block other writes, and do not exit the client to keep the read lock valid.) If you exit, the read lock will be released. )
The simplest way is to compress the data catalog.

Copy CodeThe code is as follows: $ tar-cvf/home/mysql/snapshot.tar./data (on Master)
$ tar-xvf/home/mysql/snapshot.tar (on slave)

It may not be necessary to synchronize the MySQL database because the permissions table on slave is not the same as master. At this point, unpack the package to exclude it.
Also do not include any log files in the package, and state 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 two: If the InnoDB table is used

Method One: Use the InnoDB hot Backup tool. It does not require any locks on master to be consistent with the snapshot, and the log file name and offset location are already recorded in the snapshot to be used on slave later.

Method Two: Record the current log file and the offset location, before the master shutdown execution:

Copy CodeThe code is as follows:mysql> FLUSH TABLES with READ LOCK;
mysql> SHOW MASTER STATUS;

Write down the log files and offset locations in the displayed results as soon as possible. Then, turn master off without unlocking, making sure that the snapshot on master and the results of the record are consistent.

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

Scenario Three: Can be used for both MyISAM and InnoDB tables
Do a SQL dump on master without backing up the binary log as described above. Run the Mysqldump–master-data command, and then dump the resulting file onto the slave.
However, this is slower than copying the binary log.

3. Modify the My.cnf file
MY.CNF file on Master: (restart in effect)

Copy CodeThe code is as follows: [MYSQLD]
Log_bin
Server_id=1 (value is a positive integer between 1 and 2^32-1)
my.cnf files on slave:
Copy CodeThe code is as follows: [MYSQLD]
server_id=2 (ID must be different from Master's ID.) If there are multiple slave, each slave must have a unique ID. )

Configuring extended options for Slave

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 Master is down or slave is disconnected, slave periodically tries to connect to master, and 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 default will be 3,600 seconds later, if you have not received data from the master, it will be treated as a network disconnection situation.) )

The server thinks the Master.info priority is higher than the configuration file my.cnf,
The first time you start slave, Master.info does not exist, it reads the option values from the MY.CNF, and then saves them in Master.info.
The next time you restart Slave, it reads only the contents of master.info, not the option values in MY.CNF.
To use different option values, you can either remove Master.info and restart Slave, or use the change MASTER to statement (recommended) to reset option values.

4. Start a thread from the server

Copy CodeThe code is as follows: Mysqld_safe–user=mysql–skip-slave-start & (start MySQL server but do not start slave)
Set parameters such as Master_log_file
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 you execute these programs, you should connect to the master server from the server and supplement any updates that have occurred since the snapshot.
If you forget to set the Server-id value of the primary server, you cannot connect to the primary server from the server.

Note: To ensure maximum possible tolerance and consistency of transactional InnoDB replication settings,
You should use Innodb_flush_log_at_trx_commit=1 and sync-binlog=1 in the my.cnf file of the master server.

Copy CodeThe code is as follows:
Mysql> Show variables; (check whether read-only, this option makes slave not be able to update data except slave threads or users with super privileges, ensuring that slave does not accept updates from other clients.) )
Mysql> show Processlist; (Check whether Slave-start)

Start slave at the same time that you start MySQL:

Copy CodeThe code is as follows: Mysqld_safe–user=mysql–read-only & (Start the MySQL server while starting the slave I/O thread)

mysql> SHOW SLAVE STATUSG;

5. Switch slave to master, on slave:

Copy CodeThe code is as follows:mysql> STOP SLAVE;
Mysql> RESET MASTER;

five. Copy startup options

–read_only
This option allows updates from the server to only be allowed from a server thread or a user with super privileges. You can ensure that updates from customers are not accepted from the server.

–replicate_do_db=db_name
Tells the copy of the statement that the server only makes the default database (selected by use) as db_name. To specify multiple databases, use this option more than once per database. Note that you do not copy cross-database statements

–replicate_do_table=db_name.tbl_name
Tells the server that the thread only makes a copy of the specified table. To specify more than one table, use this option more than once per table. Cross-database updates are allowed in contrast to –replicate-do-db.

–replicate_ignore_db=db_name
Tells the statement from the server not to copy the default database (selected by use) to db_name. to ignore multiple databases, use this option more than once per database.

–replicate-ignore-table=db_name.tbl_name
Tells the server thread not to copy any statements that update the specified table (even if the statement may update other tables). to ignore multiple tables, use this option more than once per table.

–replicate_wild_do_table=db_name.tbl_name
Tells the statement from the server thread to restrict replication of the updated table to match the specified database and table name patterns. Patterns can contain '% ' and ' _ ' wildcard characters, with the same meaning as the like pattern matching operator. To specify more than one table, use this option more than once per table. This option can be updated across databases.

–replicate_wild_ignore_table=db_name.tbl_name
Tells the server thread not to copy the statement that matches the wildcard pattern given by the table. to ignore multiple tables, use this option more than once per table. This option can be updated across databases.

–replicate_rewrite_db=from_name->to_name
Tells the slave server that if the default database (as selected by use) is from_name on the primary server, it translates to To_name. Only affects statements that contain tables

–report_host=slave_name
The host name or IP address that is reported to the primary server during the registration process from the server. This value appears in the output of the show SLAVE hosts on the primary server. Do not set this value if you do not want to register from the server itself on the primary server.

–report_port=slave_port
Connect the TCP/IP port number from the server, which is reported to the primary server from the server registration process.

–skip_slave_start
Tells from the server when the server starts without booting from the server thread. Use the start slave statement to start the thread at a later time.

–slave_skip_errors=[err_code1,err_code2,... | all]
Typically, replication stops when an error occurs, giving you a chance to manually resolve inconsistencies in the data. This option tells the SQL thread from the server to continue copying when the statement returns the error listed in any option value.
For example:
–slave-skip-errors=1062,1053
–slave-skip-errors=all

Six, non-stop configuration replication method

Method One:

If you have made a primary server backup at some point and recorded the binary log name and offset of the corresponding snapshot (through the output of the show MASTER Status command), take the following steps:

1. Ensure that a unique server ID number is assigned from the server.
2. Copy the backup file to the slave server.
3. In the Execute the following statement from the server, fill in the appropriate values for each option:

Copy CodeThe code is as follows:mysql> change MASTER to
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 from the server.

If you do not have a backup home server, here is a quick program to create a backup. 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. When still locked, execute the command (or its variant):
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 make sure to record the output that is used later:
Copy CodeThe code is as follows: Mysql>show MASTER STATUS;
4. Release the Lock:
Copy CodeThe code is as follows:mysql> UNLOCK TABLES;

Method Two:

An alternative approach is to dump the primary server's SQL instead of the binary copy in the previous step. To do this, you can use Mysqldump–master-data on the primary server, and later mount the SQL dump to your slave server. However, this is slower than binary replication.

Vii. Other

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

2. When upgrading from the server, you should first shut down from the server, upgrade to the appropriate 5.1.x version, then restart from the server and restart replication. Version 5.1 from the server is able to read the old trunk log written before the upgrade and execute the statements contained in the log. The trunk log created from the server after the upgrade is in 5.1 format.

3. The same global character set and proofing rules (–default-character-set, –default-collation) must always be used on both the master and slave servers. Otherwise, replication key-value errors are encountered on the server, because key values that are considered unique in the primary server's character set may not be unique from the server's character set.

4.Q: Do I need to always connect to the master server from the server?
A: No, not required. From the server can be down or disconnected for several hours or even days, after reconnecting to get updated information.

5.Q: How do I know the latest comparison from the server to the primary 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 do I force the primary server to block updates until it synchronizes from the server?
A: Use the following steps:
1. On the primary server, execute these statements:

Copy CodeThe code is as follows:mysql> FLUSH TABLES with READ LOCK;
mysql> SHOW MASTER STATUS;
Logs the log name and offset of the output of the show statement. These are the replication coordinates.

2. On the slave server, issue the following statement, where the parameter of the master_pos_wait () function is the resulting copy coordinate value in the previous step:
mysql> SELECT master_pos_wait (' Log_name ', log_offset);
The SELECT statement blocks until the specified log file and offset is reached from the server. At this point, the statement is returned from the server in sync with the primary server.

3. On the primary server, issue the following statement to allow the primary server to restart processing the update:

Copy CodeThe code is as follows:mysql> UNLOCK TABLES;

7.Q: How to improve the performance of the system through replication?
A: You should set up a server as the primary server and point all writes to that server. The server and stack space are then configured as much as possible from the budget, and read operations are distributed between the primary server and the slave server. You can also start from the server with the –skip-innodb, –skip-bdb, –low-priority-updates, and –delay-key-write=all options to increase speed from the server side. In this case, the non-transactional MyISAM table is used instead of the InnoDB and BDB tables in order to increase the speed.

MySQL master-slave replication Principle and configuration method (more detailed)