原理
主从复制相关线程
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关 系
mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志
MySQL8.0 取消 master.info 和 relay-log.info文件
主从复制特点
异步复制: 客户端性能良好
主从数据不一致比较常见
实现MySQL主从复制需要进行的配置：
- 主服务器：
- 开启二进制日志（8.0.28默认已开启）
- 为当前节点设置一个全局惟一的ID号
- 查看从二进制日志的文件和位置开始进行复制
- 创建一个用于slave和master通信有复制权限的用户账号
- 从服务器：
- 启动中继日志
- 使用有复制权限的用户账号连接至主服务器，并启动复制线程
- 启用slave服务
环境：我是用二进制安装MySQL 没有设置密码直接mysql进入数据库（mysqladmin password '123' 设置数据库密码 mysql -uroot -p123进入 ）
| IP | 主机名 | 数据库版本 |
| 192.168.26.102 | master | 8.0.28 |
| 192.168.26.103 | slave | 8.0.28 |
-
主节点master配置
1.修改mysql配置
[mysqld]
server_id=102 #为当前节点设置一个全局惟一的ID号
log-bin=/data/mysql/logbin/mysql-bin #将新产生的二进制文件放到此目录下便于查找
[root@master ~]# mkdir -p /data/mysql/logbin
[root@master ~]# chown -R mysql.mysql /data/mysql #修改目录的所有者，所属组为mysql
[root@master ~]# systemctl start mysqld #若开启失败检查排除/etc/my.cnf文件后，可以安装psmisc包，使用里面的killall命令关闭mysqld后再重新启动
（[root@master ~]# ps aux | grep mysqld [root@master ~]# killall mysqld ）
[root@master ~]# ll /data/mysql/logbin/
total 16
-rw-r-----. 1 mysql mysql 11276 Mar 21 16:00 mysql-bin.000001
-rw-r-----. 1 mysql mysql 36 Mar 21 15:52 mysql-bin.inde
2.创建一个有复制权限的用户账号
[root@master ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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.
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 102 |
+-------------+
1 row in set (0.00 sec)
##查看二进制文件和位置
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 157 | No |
+------------------+-----------+-----------+
1 row in set (0.00 sec)
##创建复制用户xkf 密码123
mysql> create user xkf@'192.168.26.%' identified WITH mysql_native_password by '123';
Query OK, 0 rows affected (0.00 sec)
##授予复制权限
mysql> grant replication slave on *.* to xkf@'192.168.26.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 73 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
-
二从节点slave配置
[root@slave ~]# vim /etc/my.cnf
[mysqld] server_id=103
read-only
[root@slave ~]# systemctl restart mysqld ##若重启失败同上下载psmisc
[root@slave ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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.
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 22 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.01 sec)
##使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> change master to
-> master_host='192.168.26.102',
-> master_user='xkf',
-> master_password='123',
-> master_port=3306,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=157;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
##查看MySQL的复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.26.102
Master_User: xkf
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 157
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
mysql> start slave; ##开启同步
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.26.102
Master_User: xkf
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 680
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 849
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
##当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了
#打开后，master上也自动开启了dump线程
mysql> show processlist;
+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 343 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
| 9 | xkf | 192.168.26.103:44514 | NULL | Binlog Dump | 42 | Source has sent all binlog to replica; waiting for more updates | NULL |
+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
[root@master ~]# ss -nt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
ESTAB 0 0 192.168.26.102:22 192.168.26.12:54359
ESTAB 0 0 192.168.26.102:22 192.168.26.12:58507
ESTAB 0 0 [::ffff:192.168.26.102]:3306 [::ffff:192.168.26.103]:44514
-
三验证
##主库创建
[root@master ~]# rz -E
rz waiting to receive.
[root@master ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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.
mysql> source /root/hellodb_innodb.sql ##将本地的文件导入数据库
##从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)