MySQL8.0.33搭建主从复制

复制原理

复制基于binlog实现

redo log是InnoDb存储引擎独有的。在存储引擎层面实现。
binlog是存储所有的数据，包括非InnoDB。在server层面实现。


3大线程
主库binlog dump线程
从库io线程
从库sql线程

主从规划

IP	主机名	主从	os用户名	os密码	db用户名	db密码	MySQL版本	server-id
192.168.6.101	node01	主	root	oracle	root	oracle	8.0.33	1001
192.168.6.102	node02	从	root	oracle	root	oracle	8.0.33	1002
192.168.6.103	node03	从	root	oracle	root	oracle	8.0.33	1003

编辑配置文件

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 distribution

Copyright (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 input statement.

repl@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 distribution

Copyright (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