MySQL 8.4 配置复制

原创

OrangeDBM

发布于 2025-04-23 13:59:12

2370

参考文档:https://dev.mysql.com/doc/refman/8.4/en/replication-configuration.html

1.先在源数据库主机的my.cnf添加这几项

[mysqld]server-id = 2binlog_format=rowlog-bin = /u01/mysql3308/data/binloginnodb_flush_log_at_trx_commit=1sync_binlog=1skip_networking=off

保持与其他服务器ID不同

server-id

最大化持久性和一致性

innodb_flush_log_at_trx_commit=1sync_binlog=1

2.然后数据库复制从主机的my.cnf添加这几项

[mysqld]server-id = 21binlog_format=rowlog-bin = /u01/mysql3308/data/binloginnodb_flush_log_at_trx_commit=1sync_binlog=1skip_networking=off

3.在源数据库上创建复制用户并授权

mysql> CREATE USER 'repl'@'%' IDENTIFIED with mysql_native_password BY 'password';          Query OK, 0 rows affected (0.15 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';Query OK, 0 rows affected (0.05 sec)

4.在源数据库上获取二进制坐标信息

mysql> flush tables with read lock;Query OK, 0 rows affected (0.03 sec)mysql> show binary log status\G;*************************** 1. row ***************************File: binlog.000079Position: 1901Binlog_Do_DB:Binlog_Ignore_DB:Executed_Gtid_Set: 4966325d-1509-11f0-a15f-525400381583:1-1461 row in set (0.00 sec)ERROR:No query specified

5.在源数据库上创建数据快照

[root@60 mysql3308]# mysqldump -uroot -p --all-databases --source-data > dump.dbEnter password:

6.在源数据库上释放只读锁

mysql> UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec)

7.查看一下dump文件的二进制坐标信息

[root@60 mysql3308]# more dump.db-- MySQL dump 10.13Distrib 8.4.4, for Linux (x86_64)---- Host: localhost Database:…CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000079', SOURCE_LOG_POS=1901;

8.在复制从数据库上执行复制命令

mysql>CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.3.60',SOURCE_PORT=3308,SOURCE_USER='repl', SOURCE_PASSWORD='password', SOURCE_LOG_FILE='binlog.000079',SOURCE_LOG_POS=1901;

9.把dump文件拷贝到复制从数据库主机上

[root@60 mysql3308]# scp dump.db root@192.168.3.61:/u01/mysql3308/

10.在复制从数据库上执行刚才从源库备份的dump文件

[root@61 mysql3308]# mysql -uroot -p < dump.dbEnter password:

11.在复制 从库上启用复制

mysql> start replica;Query OK, 0 rows affected (0.50 sec)mysql> show replica status\G;*************************** 1. row ***************************Replica_IO_State: Queueing source event to the relay logSource_Host: 192.168.3.60Source_User: replSource_Port: 3308Connect_Retry: 60Source_Log_File: binlog.00007 Read_Source_Log_Pos: 198Relay_Log_File: 61-relay-bin.000005Relay_Log_Pos: 369Relay_Source_Log_File: binlog.000079 Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Source_Log_Pos: 198Relay_Log_Space: 1199Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File:Source_SSL_CA_Path:Source_SSL_Cert:Source_SSL_Cipher:Source_SSL_Key:Seconds_Behind_Source: 0Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Source_Server_Id: 2Source_UUID: 4966325d-1509-11f0-a15f-525400381583Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: waiting for handler commitSource_Retry_Count: 10Source_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Source_SSL_Crl:Source_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set: 4966325d-1509-11f0-a15f-525400381583:1-147Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Source_TLS_Version:Source_public_key_path:Get_Source_public_key: 0Network_Namespace:1 row in set (0.30 sec)ERROR:No query specified

12.在源数据库上创建一张表

mysql> create table t as select 1 id;Query OK, 1 row affected (0.46 sec)Records: 1Duplicates: 0Warnings: 0mysql> select * from t;+----+| id |+----+|1 |+----+1 row in set (0.00 sec)

13.在复制从库上查看表

mysql> select * from t;+----+| id |+----+|1 |+----+1 row in set (0.00 sec)

14.检查复制从库上复制运行情况

mysql> show replica status\G;*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.3.60Source_User: replSource_Port: 3308Connect_Retry: 60Source_Log_File: binlog.000082Read_Source_Log_Pos: 1356Relay_Log_File: 61-relay-bin.000008Relay_Log_Pos: 1567Relay_Source_Log_File: binlog.000082Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Source_Log_Pos: 1356Relay_Log_Space: 2026Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File:Source_SSL_CA_Path:Source_SSL_Cert:Source_SSL_Cipher:Source_SSL_Key:Seconds_Behind_Source: 0Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids: Source_Server_Id: 2Source_UUID: 4966325d-1509-11f0-a15f-525400381583Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesSource_Retry_Count: 10 Source_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Source_SSL_Crl: Source_SSL_Crlpath:Retrieved_Gtid_Set: 4966325d-1509-11f0-a15f-525400381583:140-150Executed_Gtid_Set: 4966325d-1509-11f0-a15f-525400381583:1-150Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Source_TLS_Version:Source_public_key_path:Get_Source_public_key: 0Network_Namespace:1 row in set (0.00 sec)ERROR:No query specified