Detailed explanation of MySQL master slave replication replication based on log points
- 2021-07-22 11:44:11
- OfStack
Log point-based replication
1. Establish a dedicated replication account on the master library and the slave library
MariaDB [employees]> create user 'repl'@'172.%' identified by '123456';
Note that the password in production must be in accordance with the relevant specifications to achieve a certain password strength, and it is stipulated that the master library can only be accessed on a specific network segment on the slave library
2. Grant replication permissions on master and slave libraries
MariaDB [employees]> grant replication slave on *.* to 'repl'@'172.%';
3. Configure the main library
Note that enabling binary logging requires a restart of the service, and server_id is a dynamic parameter that can be combined with a command line and configuration file to achieve a restart-free persistent configuration. Note that server_id is only 1 in the cluster.
[mysqld]
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
binlog_format = row
server_id = 101
NOTE: It is a good habit to separate logs from data, preferably in different data partitions
4. Configure the slave library
The option log_slave_update determines whether to store the relay log relay_log to the native binlog, which is required if link replication is configured. Note that server_id is only 1 in the cluster.
[mysqld]
# replication
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
server_id = 102
# slaves
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
log_slave_updates = ON
read_only
5. Initialize the data from the library
Here, mysqldump is used for backup on the main library. In production, it is recommended to use xtrabackup for lock-free hot standby (based on innodb engine).
Back up the data of employees database on the main library
mysqldump --single-transaction --master-data=1 --triggers --routines --databases employees -u root -p >> backup.sql
Mount the backup file backup. sql on the slave server through the scp or docker volume volume and import it into the slave library
mysql -u root -p < backup.sql
6. Start the replication link
master @ 172.20. 0.2 and slave @ 172.20. 0.3 exist, and data has been synchronized to slave library slave through mysqldump. Replication links are now configured on slave server slave
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mariadb-bin.000029', MASTER_LOG_POS=516;
Query OK, 0 rows affected (0.02 sec)
Start the replication link on the slave library
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
7. Check the slave status on the slave library
Slave_IO_Running and Slave_SQL_Running must be YES, read Last_IO_Error or Last_SQL_Error in detail if errors occur
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000029
Read_Master_Log_Pos: 516
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 539
Relay_Master_Log_File: mariadb-bin.000029
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 516
Relay_Log_Space: 831
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)
8. Check dump threads in the main library
Check whether the binlog dump thread has been started correctly
MariaDB [(none)]> show processlist \G
*************************** 1. row ***************************
Id: 7
User: root
Host: 172.20.0.1:41868
db: employees
Command: Sleep
Time: 56
State:
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 10
User: repl
Host: 172.20.0.3:45974
db: NULL
Command: Binlog Dump
Time: 246
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000
You can see that the command Command is Binlog Dump is started on row 2, which proves that the replication thread has been successfully started
9. Summary
Advantages
The technology is mature, and BUG is relatively few There are no restrictions on SQL queries, such as not all SQL can be used when replicating based on GTIDDisadvantages
It is difficult to retrieve the log offset of the new master during failover
In a one-master multi-slave environment, if a new master is selected in the cluster after the old master goes down, other slave libraries need to resynchronize the new master. Because each DB's binlog exists independently, it is difficult to find the log point to start synchronization