1、新建主从复制

# 主节点

# 安装mysql5.7
[root@centos79-mysql01 ~]# vim /etc/my.cnf
[mysqld]
server-id=21
log-bin

[root@centos79-mysql01 ~]# service mysqld restart

[root@centos79-mysql01 ~]# mysql -uroot -p123456

# 查看二进制文件和位置
mysql> show master logs;
+-----------------------------+-----------+
| Log_name                    | File_size |
+-----------------------------+-----------+
| centos79-mysql01-bin.000001 |       177 |
| centos79-mysql01-bin.000002 |       437 |
| centos79-mysql01-bin.000003 |       154 |
+-----------------------------+-----------+
3 rows in set (0.01 sec)

# 创建复制用户
mysql> grant replication slave on *.* to copyuser@'192.168.%' identified by '123456';

# 从节点
# 安装mysql5.7
[root@centos79-mysql02 ~]# vim /etc/my.cnf
[mysqld]
server-id=22

[root@centos79-mysql02 ~]# service mysqld restart

[root@centos79-mysql02 ~]# mysql -uroot -p123456

# 使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> CHANGE MASTER TO   MASTER_HOST='192.168.1.21',MASTER_USER='copyuser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='centos79-mysql01-bin.000003',MASTER_LOG_POS=154;

mysql> start slave;

mysql> show slave status\G

2、主服务器运行一段时间后，新增从节点服务器

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点

• 通过备份恢复数据至从服务器
• 复制起始位置为备份时，二进制日志文件及其POS

[root@centos79-mysql01 ~]# mkdir /backup
[root@centos79-mysql01 ~]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos79-mysql01 ~]# ll /backup/
total 864
-rw-r--r-- 1 root root 884286 Nov 19 18:39 fullbackup_2022-11-19_18:39:41.sql

[root@centos79-mysql01 ~]# scp -r /backup 192.168.1.23:/

#建议优化主从节点性能
set global innodb_flush_log_at_trx_commit=2;
set global sync_binlog=0;

# 新的从节点
# 安装mysql5.7
[root@centos79-mysql03 ~]# vim /etc/my.cnf
[mysqld]
server-id=23
read-only

[root@centos79-mysql03 ~]# service mysqld restart

#配置从节点，从完全备份的位置之后开始复制
[root@centos79-mysql03 backup]# grep '^CHANGE MASTER' fullbackup_2022-11-19_18\:39\:41.sql
CHANGE MASTER TO MASTER_LOG_FILE='centos79-mysql01-bin.000004', MASTER_LOG_POS=154;

[root@centos79-mysql03 backup]# vim fullbackup_2022-11-19_18\:39\:41.sql
CHANGE MASTER TO MASTER_HOST='192.168.1.21',MASTER_USER='copyuser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='centos79-mysql01-bin.000004',MASTER_LOG_POS=154;

MASTER_HOST='192.168.1.21',MASTER_USER='copyuser',MASTER_PASSWORD='123456',MASTER_PORT=3306,

[root@centos79-mysql03 ~]# mysql -uroot -p123456 < /backup/fullbackup_2022-11-19_19\:16\:19.sql

mysql> start slave;

mysql> show slave status\G

3、当master宕机，提升一个slave成为新的master

# 找到哪个从节点的数据库是最新，让它成为新master
[root@centos79-mysql03 ~]# cat /data/mysql/relay-log.info
7
./centos79-mysql03-relay-bin.000002
1185
centos79-mysql01-bin.000004
1008
0
0
1

# 新master修改配置文件，关闭read-only配置
[root@centos79-mysql02 ~]# vim /etc/my.cnf
read-only=OFF
log-bin

[root@centos79-mysql02 ~]# service mysqld restart

# 清除旧的master复制信息
mysql> set global read_only=off;
mysql> stop slave;
mysql> reset slave all;

mysql> grant replication slave on *.* to copyuser@'192.168.%' identified by '123456';

# 在新master上完全备份
[root@centos79-mysql02 ~]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 > /backup/backup_`date +%F_%T`.sql

[root@centos79-mysql02 ~]# scp /backup/backup_2022-11-19_19\:52\:18.sql 192.168.1.23:/backup/
# 分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据

# 其它所有 slave 重新还原数据库，指向新的master
[root@centos79-mysql03 ~]# vim /backup/backup_2022-11-19_19\:52\:18.sql
CHANGE MASTER TO MASTER_HOST='192.168.1.22',MASTER_USER='copyuser',MASTER_PASSWORD='123456',MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

[root@centos79-mysql03 ~]# mysql -uroot -p123456
mysql> stop slave;
mysql> reset slave all;
mysql> set sql_log_bin=off;
mysql> source /backup/backup_2022-11-19_19:52:18.sql
mysql> set sql_log_bin=on;
mysql> start slave;

4、三台主机实现级联复制

# 在192.168.1.21充当master
# 在192.168.1.22充当级联slave
# 在192.168.1.23充当slave

# 在master实现
[root@centos79-mysql01 ~]# vim /etc/my.cnf
[mysqld]
server-id=21
log-bin

[root@centos79-mysql01 ~]# service mysqld restart

[root@centos79-mysql01 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to copyuser@'192.168.%' identified by '123456';

[root@centos79-mysql01 ~]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 > /backup/all.sql

[root@centos79-mysql01 ~]# scp -r /backup 192.168.1.22:/
[root@centos79-mysql01 ~]# scp -r /backup 192.168.1.23:/


# 在中间级联slave实现
[root@centos79-mysql02 ~]# vim /etc/my.cnf
[mysqld]
server-id=22
log-bin
read-only
log_slave_updates         #级联复制中间节点的必选项,MySQL8.0此为默认值

[root@centos79-mysql02 ~]# service mysqld restart

[root@centos79-mysql02 ~]# vim /backup/all.sql
CHANGE MASTER TO MASTER_HOST='192.168.1.21',MASTER_USER='copyuser',MASTER_PASSWORD='123456',MASTER_PORT=3306, MASTER_LOG_FILE='centos79-mysql01-bin.000006', MASTER_LOG_POS=154;

[root@centos79-mysql02 ~]# mysql -uroot -p123456
mysql> set sql_log_bin=0;
mysql> source /backup/all.sql
mysql>  show master logs;
+-----------------------------+-----------+
| Log_name                    | File_size |
+-----------------------------+-----------+
| centos79-mysql02-bin.000001 |       177 |
| centos79-mysql02-bin.000002 |       437 |
| centos79-mysql02-bin.000003 |       154 |
+-----------------------------+-----------+
3 rows in set (0.00 sec)

mysql> set sql_log_bin=0;
mysql> start slave;


# 在第三个节点slave上实现

[root@centos79-mysql03 ~]# vim /etc/my.cnf
[mysqld]
server-id=23
read-only

[root@centos79-mysql03 ~]# service mysqld restart
CHANGE MASTER TO MASTER_HOST='192.168.1.22',MASTER_USER='copyuser',MASTER_PASSWORD='123456',MASTER_PORT=3306, MASTER_LOG_FILE='centos79-mysql02-bin.000003', MASTER_LOG_POS=154;

[root@centos79-mysql03 ~]# mysql -uroot -p123456
mysql> source /backup/all.sql
mysql> start slave;

# 报错
mysql> show slave status\G
Slave_IO_Running: Connecting
Last_IO_Error: error connecting to master 'copyuser@192.168.1.22:3306' - retry-time: 60  retries: 1

# 解决方法
去上游服务器查看或修改用户名密码，然后重新启用slave