Detailed description of MySQL master-slave replication-log point-based replication, mysql master-slave

Detailed description of MySQL master-slave replication-log point-based replication, mysql master-slave

Log point-based Replication

1. Create a dedicated replication account for the master database and slave Database

MariaDB [employees]> create user 'repl'@'172.%' identified by '123456';

Note that the password in production must comply with the relevant specifications to achieve a certain password strength, and requires that the master database can be accessed on a specific network segment of the slave Database

2. Grant the copy permission to the master database and slave database.

MariaDB [employees]> grant replication slave on *.* to 'repl'@'172.%';

3. Configure the master database

Note that the service needs to be restarted to enable binary logs, while server_id is a dynamic parameter. You can combine the command line with the configuration file to achieve persistent configuration without restart. Note that server_id is unique in the cluster.

[mysqld]log_bin = /var/log/mysql/mariadb-binlog_bin_index = /var/log/mysql/mariadb-bin.indexbinlog_format = rowserver_id = 101

NOTE: separating logs from data is a good habit. It is best to put logs in different data partitions.

4. Configure slave Database

The log_slave_update option determines whether to store relay_log in the binlog of the Local Machine. If link replication is configured, this option is required. Note that server_id is unique in the cluster.

[mysqld]# replicationlog_bin = /var/log/mysql/mariadb-binlog_bin_index = /var/log/mysql/mariadb-bin.indexserver_id = 102# slavesrelay_log    = /var/log/mysql/relay-binrelay_log_index  = /var/log/mysql/relay-bin.indexrelay_log_info_file  = /var/log/mysql/relay-bin.infolog_slave_updates = ONread_only

5. initialize slave database data

Mysqldump is used to back up data in the master database. We recommend that you use xtrabackup for lock-free hot backup (based on the innodb engine) in production ).

Back up data in the employees database on the master database

Copy codeThe Code is as follows:
Mysqldump -- single-transaction -- master-data = 1 -- triggers -- routines -- databases employees-u root-p> backup. SQL

Mount the backup file backup. SQL to the slave server through scp or docker volume and import it to the slave database.

mysql -u root -p < backup.sql

6. Start the replication Link

Existing master@172.20.0.2 and slave@172.20.0.3, and data has been synchronized to slave database slave via mysqldump. Now configure replication link 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 Database

MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.01 sec)

7. Check the slave status on the slave Database

Slave_IO_Running and Slave_ SQL _Running must be YES. If an error occurs, read the prompt information of Last_IO_Error or Last_ SQL _Error in detail.

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: 0Master_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: conservative1 row in set (0.00 sec)

8. Check the dump thread in the master database.

Check whether the binlog dump thread has been correctly started

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: NULLProgress: 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: NULLProgress: 0.000

The Command "Binlog Dump" on row 2 is started, proving that the replication thread has been started successfully.

9. Summary

Advantages

1. Mature technology with fewer bugs
2. There are no restrictions on SQL queries. For example, not all SQL statements can be used for GTID-based replication.
   

Disadvantages

1. It is difficult to obtain the log offset of the new Master during failover.

In a master-slave multi-slave environment, if a new master is elected in the cluster after the old master is down, other slave databases need to re-Synchronize the new master, because the binlog of each database exists independently, it is difficult to find the log point to start synchronization.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.