MySQL8.0.33搭建主从复制

MySQL8.0.33搭建主从复制

复制原理

复制基于binlog实现redo log是InnoDb存储引擎独有的。在存储引擎层面实现。
binlog是存储所有的数据，包括非InnoDB。在server层面实现。3大线程
主库binlog dump线程
从库io线程
从库sql线程

主从规划

编辑配置文件

1、编辑配置文件，server-id支持在线修改
主库node01
[mysqld]
server-id=1001从库node02
[mysqld]
server-id=1002从库node03
[mysqld]
server-id=1003主库node01
8.0之后默认开启binlog，8.0之前需要在参数文件中设置log-bin=on
root@localhost [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

主库创建复制用户

2、主库创建复制用户
root@localhost [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)创建用户'repl'@'192.168.6.%'
root@localhost [mysql]> create user 'repl'@'192.168.6.%' identified by 'oracle';
Query OK, 0 rows affected (0.01 sec)给用户授权   
root@localhost [mysql]> grant replication slave on *.* to 'repl'@'192.168.6.%';
Query OK, 0 rows affected (0.00 sec)在从库node02、node03分别进行登录测试复制用户
[root@node02 mysql]# mysql -h192.168.6.101 -urepl -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 13
Server version: 8.1.0 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or 'h' for help. Type 'c' to clear the current pl@192.168.6.101 [(none)]> [root@node03 mysql]# mysql -h192.168.6.101 -urepl -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 14
Server version: 8.1.0 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or 'h' for help. Type 'c' to clear the current pl@192.168.6.101 [(none)]> 

主库全备份

3、主库全备份
导入测试数据root@localhost [mysql]> source ./hr.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)root@localhost [hr]> show tables;
+------------------+
| Tables_in_hr     |
+------------------+
| countries        |
| departments      |
| emp_details_view |
| employees        |
| job_grades       |
| job_history      |
| jobs             |
| locations        |
| order            |
| regions          |
+------------------+
10 rows in set (0.00 sec)[root@node01 mysql]# mysqldump --help[root@node01 mysql]# mysqldump -uroot -p -S /data/mysql/3306/data/mysql.sock --single-transaction --master-data=2 -E -R --triggers -A > node01_full_backup.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
Enter password: 
[root@node01 mysql]# ll -h node01_full_backup.sql 
-rw-r--r-- 1 root root 1.2M Jul 28 10:23 node01_full_backup.sql-- CHANGE MASTER TO MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=51851;root@localhost [hr]> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000001 |    51851 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

将备份scp到从库

4、将备份scp到从库
[root@node01 mysql]# scp node01_full_backup.sql root@node02:~
node01_full_backup.sql                                                  100% 1215KB  12.3MB/s   00:00    
[root@node01 mysql]# scp node01_full_backup.sql root@node03:~
node01_full_backup.sql                                                  100% 1215KB  15.4MB/s   00:00 

基于主库全备份恢复从库

5、基于主库全备份恢复从库[root@node02 mysql]# mysql -uroot -p --socket /data/mysql/3306/data/mysql.sock < node01_full_backup.sql 
Enter password: [root@node03 mysql]# mysql -uroot -p --socket /data/mysql/3306/data/mysql.sock < node01_full_backup.sql
Enter password:

建立主从复制

6、建立主从复制
执行change master to命令，语法如下从库执行
change master tomaster_host='',master_port=,master_user='',master_password='',master_log_file='',master_log_pos=;根据备份查找binlog的两个参数
[root@node02 mysql]# more node01_full_backup.sql | grep -m 1 "CHANGE MASTER TO"
-- CHANGE MASTER TO MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=51851;从库执行change master tomaster_host='192.168.6.101',master_port=3306,master_user='repl',master_password='oracle',master_log_file='binlog.000008',master_log_pos=24527;node02
root@localhost [(none)]> change master to->     master_host='192.168.6.101',->     master_port=3306,->     master_user='repl',->     master_password='oracle',->     master_log_file='bin.000001',->     master_log_pos=51851;
Query OK, 0 rows affected, 9 warnings (0.03 sec)node03
root@localhost [(none)]> change master to->     master_host='192.168.6.101',->     master_port=3306,->     master_user='repl',->     master_password='oracle',->     master_log_file='bin.000001',->     master_log_pos=51851;
Query OK, 0 rows affected, 9 warnings (0.02 sec)

开启主从复制

7、开启主从复制
在从库上执行node02
root@localhost [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)node03
root@localhost [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)nod02
root@localhost [(none)]> show slave statusG
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.6.101Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: bin.000001Read_Master_Log_Pos: 51851Relay_Log_File: node02-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: bin.000001Slave_IO_Running: YesSlave_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_Master_Log_Pos: 51851Relay_Log_Space: 531Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3306Master_UUID: c8175257-2b9c-11ee-bc8a-000c29004cdfMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 10Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.00 sec)node03
root@localhost [(none)]> show slave statusG
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.6.101Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: bin.000001Read_Master_Log_Pos: 51851Relay_Log_File: node03-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: bin.000001Slave_IO_Running: YesSlave_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_Master_Log_Pos: 51851Relay_Log_Space: 531Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3306Master_UUID: c8175257-2b9c-11ee-bc8a-000c29004cdfMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 10Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.00 sec)从库查询mysql.slave_master_info表
root@localhost [(none)]> select *from mysql.slave_master_infoG
*************************** 1. row ***************************Number_of_lines: 33Master_log_name: bin.000001Master_log_pos: 51851Host: 192.168.6.101User_name: replUser_password: oraclePort: 3306Connect_retry: 60Enabled_ssl: 0Ssl_ca: Ssl_capath: Ssl_cert: Ssl_cipher: Ssl_key: Ssl_verify_server_cert: 0Heartbeat: 30Bind: Ignored_server_ids: 0Uuid: Retry_count: 10Ssl_crl: Ssl_crlpath: Enabled_auto_position: 0Channel_name: Tls_version: Public_key_path: Get_public_key: 0Network_namespace: Master_compression_algorithm: uncompressedMaster_zstd_compression_level: 3Tls_ciphersuites: NULL
Source_connection_auto_failover: 0Gtid_only: 0
1 row in set (0.01 sec)主库执行
root@localhost [mysql]> show processlist;
+----+-----------------+--------------+-------+-------------+-------+-----------------------------------------------------------------+------------------+
| Id | User            | Host         | db    | Command     | Time  | State                                                           | Info             |
+----+-----------------+--------------+-------+-------------+-------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost    | NULL  | Daemon      | 52972 | Waiting on empty queue                                          | NULL             |
| 11 | root            | localhost    | mysql | Query       |     0 | init                                                            | show processlist |
| 17 | repl            | node02:59530 | NULL  | Binlog Dump |  2405 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 18 | repl            | node03:55990 | NULL  | Binlog Dump |  2385 | Source has sent all binlog to replica; waiting for more updates | NULL             |
+----+-----------------+--------------+-------+-------------+-------+-----------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)ID 17和18对应 主库的binlog  dump进程从库执行
root@localhost [(none)]> show processlist;
+----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+
| Id | User            | Host            | db   | Command | Time  | State                                                    | Info             |
+----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 17170 | Waiting on empty queue                                   | NULL             |
| 13 | root            | localhost       | NULL | Query   |     0 | init                                                     | show processlist |
| 14 | system user     | connecting host | NULL | Connect |  2482 | Waiting for source to send event                         | NULL             |
| 15 | system user     |                 | NULL | Query   |  2481 | Replica has read all relay log; waiting for more updates | NULL             |
| 16 | system user     |                 | NULL | Connect |  2482 | Waiting for an event from Coordinator                    | NULL             |
| 17 | system user     |                 | NULL | Connect |  2482 | Waiting for an event from Coordinator                    | NULL             |
| 18 | system user     |                 | NULL | Connect |  2482 | Waiting for an event from Coordinator                    | NULL             |
| 19 | system user     |                 | NULL | Connect |  2482 | Waiting for an event from Coordinator                    | NULL             |
+----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+
8 rows in set (0.00 sec)ID 14对应IO线程，15对应SQL线程至此，最简单的异步复制一主二从环境搭建完成