Detailed explanation of MySQL replication principles and practical applications

This article uses examples to illustrate the principles and practical applications of MySQL replication. Share with you for your reference, the details are as follows:

The replication function is to copy the data on a MySQL database to one or more MySQL slave databases.

How replication works: All DDL and DML statements executed on the master server are recorded in binary logs, which are retrieved by slave servers connected to it, copied to the slaves, and saved as relay logs.

This process is handled by a thread called the IO thread, and another thread called the SQL thread executes the statements in the relay log sequentially.

There are several replication topologies:

1. Traditional replication, one master and multiple slaves, one master server and multiple slave servers.

2. Chain replication: one server copies from the master database, and another server copies from this server. The intermediate server is also called the relay master database.

3. Master-master replication, the two master libraries accept writes and replication from each other.

4. Multi-source replication: one slave database replicates from multiple master databases.

1. How to copy

1. Enable binary logging on the master.

2. Create a replication user on the master database.

3. Set a unique server_id on the slave.

4. Back up data from the main database.

5. Restore the data backed up in the master database on the slave database.

6. Execute the CHANGE MASTER TO command.

7. Start copying.

2. The specific steps are as follows:

1. On the master database, enable binary logging and set server_id.

#Set server_id
server_id = 1
#Open binlog log-bin = mysql-bin

2. Create a replication user on the master database

create user 'username'@'%' identified by 'password';
grant replication slave on *.* to 'username'@'%';

3. Set server_id on the slave

#Set server_id
server_id = 10

4. Back up the data on the main database

mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --master-data > export path

5. Restore the data exported from the master database on the slave database

mysql -u root -p -f <main database backup file.sql

6. Execute the CHANGE MASTER TO command on the slave

CHANGE MASTER TO 
MASTER_HOST='Master Library IP',
MASTER_USER = 'Master database replication user',
MASTER_PASSWORD='Password',
MASTER_LOG_FILE='Binary log name',
MASTER_LOG_POS=binary log position;

The binary log name and binary log position are already included in the backup master library file, similar to the following:

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

7. Run start slave on the slave library, and then show slave status\G; to view the replication status;

3. Setting up Master-Master Replication

Assume that the master libraries are master1 and master2.

1. Set master2 to read-only

set @@global.read_only = on;

2. Create a replication user on master2. If it exists, you do not need to create it.

create user 'username'@'%' identified by 'password';
grant replication slave on *.* to 'username'@'%';

3. Make sure that the binary log is turned on on master2 and check the coordinates of the binary log on master2

show master status;

4. According to the information in step 2, execute the CHANGE MASTER TO command on master1

CHANGE MASTER TO 
MASTER_HOST='MASTER2 host IP',
MASTER_USER = 'MASTER2 replication user',
MASTER_PASSWORD='Password',
MASTER_LOG_FILE='Binary log name',
MASTER_LOG_POS=binary log position;

5. Enable slave mode on master1

start slave;

6. Set master2 to read and write

set @@global.read_only = off;

4. Setting up multi-source replication

Set server3 as the slave library of server1 and server2.

1. Set the binary log and server_id of server1 and server2. For specific operations, refer to the above.

2. Create replication users on server1 and server2. For specific operations, refer to the above.

3. Set server_id on server3.

4. Back up the data of server1 and server2.

5. Restore the data backed up on server1 and server2 on server3.

6. On server3, change the replication repository from FILE to TABLE.

stop slave;
set global master_info_repository = 'TABLE';
set global relay_log_info_repository = 'TABLE';

You also need to modify the configuration file:

[mysqld]
master-info-repository = TABLE
relay-log-info-repository = TABLE

7. On server3, execute the CHANGE MASTER TO command and name the channel

CHANGE MASTER TO 
MASTER_HOST='server1 host IP',
MASTER_USER = 'server1 replication user',
MASTER_PASSWORD='Password',
MASTER_LOG_FILE='server1 binary log name',
MASTER_LOG_POS=server1 binary log position FOR CHANNEL 'server1';

CHANGE MASTER TO 
MASTER_HOST='server2 host IP',
MASTER_USER = 'server2 replication user',
MASTER_PASSWORD='Password',
MASTER_LOG_FILE='server2 binary log name',
MASTER_LOG_POS=server2 binary log position FOR CHANNEL 'server2';

8. On server3, execute the START SLAVE FOR CHANNEL statement for each channel.

start slave for channel 'server1';
start slave for channel 'server2';

9. Check the synchronization status, show slave status\G;

To obtain the slave status of the specified channel, show slave status for channel 'channel name'\G;

5. Set up replication filters

You can choose which tables or databases to replicate. On the master, you can use the --binlog-do-db and --binlog-ignore-db options to select the databases to record changes in order to control the binary log. A better approach is to control the slave library.

1. Copy the specified database

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

2. Copy the specified table

CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = ('db1.table1');

3. If you want to use wildcards to select tables

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.tb_%');

4. Ignore the database

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2);

5. Ignore the specified table

CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = ('db1.table1');

6. Switch the slave library from master-slave replication to chain replication

For example, now server A is the master database, server B and server C are slave databases, and they are replicated to server A. Now I want to use server C as a slave library of server B.

1. Stop the slave library on server C

stop slave;
show slave status\G;

Record the values ​​of Relay_Master_Log_File and Exec_Master_Log_Pos

2. Stop the slave database on server B

stop slave;
show slave status\G;

Record the values ​​of Relay_Master_Log_File and Exec_Master_Log_Pos

3. Compare the log position of server B with that of server C to find out which one is the latest synchronization of server A. Usually, server C stops running from the library first, and the log of server B will be earlier.

4. On server C, use the START SLAVE UNTIL statement to synchronize it to the log position of server B:

START SLAVE UNTIL MASTER_LOG_FILE='The name of the server B log in the previous step', MASTER_LOG_POS=the position of the server B log in the previous step;

5. On server C, check show slave status. Exec_Master_Log_Pos and Until_Log_Pos should be the same.

6. On server B, check the status of the master database and start the slave database.

show master status;
start slave;
show slave status\G;

7. On server C, stop the slave database and execute the CHANGE MASTER TO command.

stop slave;
CHANGE MASTER TO
MASTER_HOST='IP of server B',
MASTER_USER = 'Server B replication user',
MASTER_PASSWORD='Password',
MASTER_LOG_FILE='Get the log name through show master status in the previous step',
MASTER_LOG_POS=The log position was obtained by show master status in the previous step;

8. On server C, start replication and check the status

start slave;
show slave status\G;

7. Switch chain replication to master-slave replication

Server A->Server B->Server C. If you want Server C to be the slave of Server A directly, what should you do?

1. On server B, stop the slave database and record the status of the master database.

stop slave;
show master status\G;

2. On server C, make sure the slave's delay has been caught up, Relay_Master_Log_File and Exec_Master_Log_Pos should be equal to the master status on server B.

Once the delay is caught up, the slave database is stopped.

stop slave;

3. On server B, obtain the log coordinate values ​​(Relay_Master_Log_File and Exec_Master_Log_Pos) of server A from show slave status and start the slave.

show slave status\G;
start slave;

4. On server C, stop the slave database and execute the CHANGE MASTER TO command to point to server A.

stop slave;
CHANGE MASTER TO 
MASTER_HOST='IP of Server A',
MASTER_USER = 'Server A's replication user',
MASTER_PASSWORD='Password',
MASTER_LOG_FILE = 'Log obtained in the previous step',
MASTER_LOG_POS=log position obtained in the previous step;

5. On server C, start the slave database and check its status.

start slave;
show slave status\G;

8. Set up delayed replication

Why do you need delayed replication? It is possible that a catastrophic statement is executed on the primary database and you must recover from the point in time in the backup. If the database size is too large, this will cause a long downtime.

To avoid this situation, you can use a delayed slave. If a disaster occurs and the delayed slave has not yet executed the catastrophic statement, you can stop replication first, let the slave skip the disaster statement, and finally promote the slave to the master.

1. Stop running from the library

stop slave;

2. Set the delay time in seconds

CHANGE MASTER TO MASTER_DELAY = 3600;
start slave;

3. Check the slave status

show slave status\G;

SQL_Delay: The number of seconds that the slave database is delayed from the master database.

SQL_Remaining_Delay: The number of seconds remaining in the delay. When the delay is maintained, this value is NULL.

Slave_SQL_Running_State: The state of the SQL thread

9. Setting up GTID replication

The global transaction identifier GTID is a unique identifier created in the program and associated with each transaction submitted on the master. This identifier is unique, not only on the master database, but also on other slave databases.

All the replication described above requires specifying the location of the binary file and the replication starting point. If you switch the master of one slave library to another, you must re-obtain the binary file location, which will be troublesome.

To avoid this, you can use GTID-based replication. MySQL uses GTID to automatically detect the position of the binary log.

1. Enable GTID in my.cnf in all databases

[mysqld]
gtid_mode = ON
enforce-gtid-consistency = 1
skip_slave_start

2. Set the master database to read-only to ensure that the data in the master database is consistent with that in the slave database.

set @@global.read_only = on;

3. Restart all slave libraries to make GTID take effect.

4. Restart the main library.

5. Execute the CHANGE MASTER TO command on the slave to set GTID replication

CHANGE MASTER TO
MASTER_HOST='Master Library IP',
MASTER_PORT=3306,
MASTER_USER = 'copy user',
MASTER_PASSWORD='Password',
MASTER_AUTO_POSITION=1;

6. Execute start slave on all slave libraries; and check the status.

10. Setting up semi-synchronous replication

By default, replication is asynchronous, and the master does not know whether the write operation has reached the slave. If there is a delay between the master and the slave, the master crashes and the data that has not yet reached the slave will be lost.

To solve this problem, in semi-synchronous replication, the master library will wait until at least one slave library receives the written data.

1. Install the rpl_semi_sync_master plug-in on the master database

install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

For Windows, please use the following:

install plugin rpl_semi_sync_master SONAME 'semisync_master.dll';

2. Confirm that the plugin is activated

select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';

3. Enable semi-synchronous replication and adjust the timeout

set @@global.rpl_semi_sync_master_enabled=1;
set @@global.rpl_semi_sync_master_timeout=100;

4. Install the rpl_semi_sync_slave plug-in on the slave library

install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

For Windows, please use the following:

install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll';

5. Confirm that the plugin is activated

select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';

6. On the slave library, enable semi-synchronous replication and restart the slave library IO thread

set global rpl_semi_sync_slave_enabld = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

7. Check the semi-synchronization status in the following ways

show status like 'rpl_semi_sync_master_clients';

View the number of clients connected to the master database in semi-synchronous mode

show status like 'rpl_semi_sync_master_status';

The master database switches between asynchronous and semi-synchronous replication. On means semi-synchronous replication, and off means asynchronous replication.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.