Home >Database >Mysql Tutorial >MySQL master-slave replication practice-detailed explanation of replication code examples based on log points
This article mainly introduces the detailed explanationMySQL Master-slave replication practice - replication based on log points, which has certain reference value. Interested friends can refer to it.
Replication based on log points
1. Establish dedicated replication accounts on the main database and slave database
MariaDB [employees]> create user 'repl'@'172.%' identified by '123456';
Pay attention to the password in production Relevant specifications must be followed to achieve a certain password strength, and it is stipulated that the master database can only be accessed on a specific network segment on the slave database
2. Grant replication permissions on the master database and the slave database
MariaDB [employees]> grant replication slave on *.* to 'repl'@'172.%';
3. Configure the main library
Note that enabling binary logs requires restarting the service, and server_id is a dynamic parameter that can be combined with the command line and Configuration file to achieve persistent configuration without restarting. Note server_id is unique 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 and data. It is best to put them in different data partitions
4. Configure slave database
The option log_slave_update determines whether to store the relay log relay_log in the local binlog. If link replication is configured, this option is required. Note that the server_id is unique 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 slave library
Mysqldump is used here to back up the main library. In production, it is recommended that you use xtrabackup for lock-free hot backup (based on the innodb engine).
Back up the data of the employees database on the main library
The 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 into the slave library
mysql -u root -p < backup.sql
6. Start the replication link
Existing master@172.20.0.2 and slave@172.20.0.3, and the data has been synchronized to the slave database slave through mysqldump. Now on the slave server Configure the replication link on the 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. If an error occurs, you must 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: 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 the dump thread 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 On row 2, the Command for Binlog Dump is started, which proves that the replication thread has been successfully started.
9. Summary
Advantages
The technology is mature and there are relatively few BUGs
There are no restrictions on SQLquery, such as not all when copying based on GTID SQL can be used
Disadvantages
Reobtain the log offset of the new primary during failover It is more difficult
In a one-master and multiple-slave environment, if the old master goes down and a new master is elected in the cluster, other slave libraries must resynchronize the new master. Since the binlog of each DB exists independently, it is difficult to find the log point to start synchronization
The above is the detailed content of MySQL master-slave replication practice-detailed explanation of replication code examples based on log points. For more information, please follow other related articles on the PHP Chinese website!