Analysis of MySQL replication and Analysis of mysql

Analysis of MySQL replication and Analysis of mysql

MySQL replication is based on binlog.

 

The procedure is as follows:

It involves three threads, the DUMP thread of the master database, the IO thread of the slave database and the SQL thread.

1. The master database records all operations in the binlog. When replication is enabled, the DUMP thread of the master database sends the binlog content to the slave database according to the request of the slave database IO thread.

2. After receiving the binlog event from the database IO thread to the DUMP thread of the master database, write it to the local relay-log.

3. Replay events in relay-log from the SQL thread of the database.

In fact, before MySQL 4.0, replication only has two threads, one for the master and one for the slave. On the Slave side, this thread is also responsible for receiving binlog events from the master database and event replay. Therefore, relay-log is not used, which is easy to cause, when the replaying speed of the binlog event is slow, the acceptance of the binlog event will be affected.

 

Replication setup

The basic steps are as follows:

1. Configure the master database and slave Database

2. Create a copy account

3. Create a master database consistent Snapshot

4. Create a slave database based on the master database Snapshot

5. enable replication

 

The detailed steps are as follows:

1. Configure the master database and slave Database

Master Database

Enable binlog and set server-id

[mysqld]log-bin=mysql-binserver-id=1

In a group of replication structures, each server must be configured with a unique server-id. The valid range of this value is 1 ~ 232-1.

If the server-id is set to 0, MySQL automatically changes it to 1. In this case, replication is not affected.

If the server-id is not explicitly set, MySQL also sets it to 1, but the IO thread reports an error during the slave connection.

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server_id was not set'

 

Slave Database

Set server-id

[mysqld]server-id=2

On the slave server, you can choose not to enable binlog. After binlog is enabled, you can set the log_slave_updates parameter to 1 if you want to record the replay time to binlog as well.

 

After setting, restart the database.

 

2. Create a replication account

Run on the master database

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

 

3. Create a master database consistent Snapshot

Here, I use mysqldump to create database snapshots

# Mysqldump -- master-data = 2-R -- single-transaction-A> 3306_20160815. SQL

In the generated backup file, we can obtain the master status when performing a consistent snapshot on the database, including the name and location of the binary file.

# Head-30 3306_20160815. SQL

...-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=120;...

 

4. Create a slave database based on the master database Snapshot

# Mysql <3306_20160815. SQL

 

5. enable replication

Execute the change master to command based on the status of the MASTER database obtained in step 1.

mysql> CHANGE MASTER TO         MASTER_HOST='master_host_name',         MASTER_USER='replication_user_name',         MASTER_PASSWORD='replication_password',         MASTER_LOG_FILE='recorded_log_file_name',         MASTER_LOG_POS=recorded_log_position;

After the change master to command is executed, the slave database does not connect TO the MASTER database, but writes the information TO the master.info and relay-log.info under the slave database data directory. If MASTER_LOG_FILE is not explicitly specified, it is null by default, because no connection is established with the master database, and the file name of the binlog of the master database is unknown. It can only be known when a connection is established with the master database. If MASTER_LOG_POS is not explicitly specified, the default value is 4, that is, the first 4 bytes of binlog are ignored and read from the first event.

 

Enable the copy Function

Mysql> start slave;

 

View the copy status

Mysql> show slave status \ G

Pay attention to the following two variables. If YES, the replication is successful.

...Slave_IO_Running: YesSlave_SQL_Running: Yes...

 

Note: The above construction scenario is based on the data already on the master database. It would be easier to build in a new environment.

You only need to obtain the status information of the master database.

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000016 |      120 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec

 

MySQL replication format

There are three formats for MySQL replication:

Statement-based replication (SBR)

Statement-based replication, that is, the SQL statements executed on the master node are replayed on the slave intact. The replication format is displayed in MySQL 3.23.

Advantages:

1. Saving binlog space.

2. It can be used for review. After all, all DML statements are directly recorded in the binlog.

Disadvantages:

1. The execution results of many functions on the master and slave nodes are inconsistent.

LOAD_FILE()，UUID(), UUID_SHORT()，USER()，FOUND_ROWS()，SYSDATE()，GET_LOCK()，IS_FREE_LOCK()，IS_USED_LOCK()，MASTER_POS_WAIT()，RAND()，RELEASE_LOCK()，SLEEP()，VERSION()

2. For DELETE and UPDATE operations, the LIMIT clause is included but the order by clause is not included, which may result in inconsistent execution results of the master and slave nodes.

3. Compared with row-based replication, the INSERT... SELECT operation on the master node requires more row locks.

4. Make sure that the execution result of the UDF is correct.

 

Row-based replication (RBR)

Row-based replication, introduced by MySQL 5.1, records the rows involved in DML operations relative to the SBR.

Advantages:

1. Security. All changes on the master can be copied to the slave.

2. perform the following operations with fewer row locks.

INSERT... SELECT

INSERT operation with AUTO_INCREMENT Column

In UPDATE and DELETE, the WHERE condition is not indexed.

Disadvantages:

1. A large number of logs are generated.

For example, a table has 1 million records. If I perform the delete operation without any conditions, in statement-based replication, only the delete from table record is recorded in the binlog, however, in row-based replication, records are recorded, and each record is similar to delete from table where...

This will cause the following problems:

1> If binlog is used for restoration, it takes a longer time.

2> when writing data to binlog, The binlog lock takes a long time because of the large amount of data, which affects the database concurrency.

3> large logs may put a lot of pressure on disk IO and network IO.

4> increase the latency of slave.

2. Binary logs are not verified.

3. Database-level replication is not recommended.

Including -- replicate-do-db, -- replicate-ignore-db, -- replicate-rewrite-db

 

MIXED

MIXED is a combination of the two. It automatically switches between the two modes based on the executed statements and the involved storage engine. By default, the statement-based replication mode is used. When unsafe statements is encountered, the row-based replication mode is switched.

For the switching conditions, refer to the official documentation: http://dev.mysql.com/doc/refman/5.7/en/binary-log-mixed.html

 

Before MySQL 5.7.7, the statement-based replication mode is used by default. From MySQL 5.7.7, the row-based replication mode is used by default.

 

Copy objects involved

Relay-log

Relay-log stores the binlog events read from the library IO thread from the master database. Similar to the binlog format, you can use mysqlbinlog to parse the content.

The relay-log and relay-log.index file names can be set by configuring the relay_log and relay_log_index parameters.

 

The relay-log will be switched in the following circumstances:

1. When the slave IO thread is started.

2. Execute the flush logs operation.

3. The value reaches the value set by max_relay_log_size. The default value is 0, that is, the value of max_binlog_size is used as the size of max_relay_log_size.

After the slave SQL thread replays all events in a relay-log file, it will automatically delete the relay-log file (controlled by the relay_log_purge parameter ), therefore, there is no command to explicitly Delete relay-log.

 

Master.info

This file stores the host name and port of the master database, the username and password of the copied account, and the location information of the slave database receiving the binlog event of the master database, the IO thread of the database knows where to obtain the binlog event of the master database next time.

This location information corresponds to Master_Log_File and Read_Master_Log_Pos in show slave status \ G.

As follows:

# Cat master.info

23mysql-bin.000014120192.168.244.10replrepl330660001800.0000cad449f2-5d4f-11e6-b353-000c29c64704864000

 

Relay-log.info

This file records the replay information of the slave database, so that even if the database is restarted, the SQL thread knows where to start replay.

This location information corresponds to Relay_Master_Log_File and Exec_Master_Log_Pos in show slave status \ G.

# Cat relay-log.info

7./mysqld-relay-bin.000024283mysql-bin.0000141200011

 

Crash-Safe Replication

Each time slave accepts binlog or applies relay-log, it must modify the master.info or relay-log.info information and synchronize it to the disk, which leads to a large number of disk operations, so, these two files are changed asynchronously. Although these files will be modified each time, they are persisted to the disk but left to the operating system for processing. When the memory and disk information are inconsistent, if the operating system is down at this time, the information in the memory will not be synchronized to the disk in time. After the operating system recovers, the master.info and relay-log.info data remains old, so it is re-executed from the executed part.

MySQL 5.6 provides two parameters to improve this problem by saving the contents of master.info and relay-log.info in a table rather than in a disk file.

Master-info-repository

It can be set to TABLE or FILE. If FILE is used, the copied location information is still saved in master.info. If it is set to TABLE, the information is reported to be saved in mysql. in slave_master_info, the default value is FILE.

Relay-log-info-repository

Can be set to TABLE or FILE, if FILE is used, the information that applies relay-log is saved in the relay-log.info, if set to TABLE, saved in mysql. in slave_relay_log_info, the default value is FILE.

 

By setting the preceding two variables as tables, you can implement "Crash-Safe Replication". All the above operations will be performed in a transaction.

 

Summary

1. Enabling the replication function does not increase the overhead of the server, mainly because of the overhead caused by enabling binlog, including the overhead of append write operations for binlog files and the overhead caused by system calling fsync.

2. The replication function of MySQL is backward compatible, because the binlog of a newer version of MySQL will enter a new event type. Therefore, you can use a newer version of MySQL as the slave database.

3. MySQL replication is asynchronous.

4. The delayed replication function is added in MySQL 5.6, which is controlled by the master_delay parameter.

 

Reference

1. http://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html

2. MariaDB Principle and Implementation