如何配置mysql主从复制（master 、salve）

前言

环境：CentOS 7.9、mysql 5.7
准备两台mysql服务器，都安装相同的mysql5.7版本，以下操作假设master已经运行了很久，已经有很多数据，现在要做一台slave服务器，安装好slave服务器上的mysql版本，版本与master服务器中mysql版本一致。

mysql主从复制原理

master服务器接收来自客户端的数据更新，而slave接收来自master服务器的binlog日志内容，并解析出SQL语句后再重新在slave上重做一遍，从而使得主从数据一致。
1、应用在master主库上进行数据更改（DDL、DML、DCL）操作，并将更改记录记入Binary Log日志；
2、slave从库IO线程将master主库上的日志复制到自己的中继日志（Relay Log）中；
3、slave从库SQL线程读取中继日志（Relay Log）中的事件，将其重做到从库；

1、指定master中的server-id

vim /etc/my.cnf
[mysqld]												#必须在mysqld下添加
server_id=1												#指定server_id,这id的值可以是整数,而且不能与其他实例相同,否则冲突

2、开启master中的binlog功能

vim /etc/my.cnf
[mysqld]												#必须在mysqld下添加
server_id=1												#指定server_id,这id的值可以是整数,而且不能与其他实例相同,否则冲突
log_bin=/usr/local/mysql/binlog		#开启binlog功能，binlog是文件的前缀，不是目录喔，生成的binlog日志都将是binlog.****开头的


注意:修改了mysql的配置文件，就必须重启mysql服务器:
service mysql.server restart
show variables like 'server_id';						#登录数据库命令查看server-id和binlog参数
show variables like 'log_bin';

3、 建立一个mysql主从复制专用用户rep并授权

root账号密码登录master服务器，然后建立一个mysql主从复制专用的用户rep：

grant replication slave on *.*  to 'rep'@'192.168.1.%' identified by 'aabb001';	#授权,权限必须是replication slave

说明:
replication slave是主从复制专用权限, *.* 表示所有库所有表的含义,当然也可以具体指定库和表,如:DEP.emp、DEP.*, 
'rep'@'192.168.1.%'表示用户名以及允许整个192.168.1.0网段使用rep该用户登录.

以上是mysql5.7版本中的直接建立用户并授权语句，如果是mysql8.0版本的话，还必须先创建用户再授权，因为mysql8.0版本用户和密码管理改变了，如下：

create user rep@'192.168.1.%'  identified with mysql_native_password by 'aabb001';	
grant replication slave on *.*  to 'rep'@'192.168.1.%'

4、对主库进行锁表并导出数据（全备）

flush table with read lock;		#可以查看锁表后的binlog日志文件和binlog日志偏移量
show master status;				#后面从库导出全备后,正式开始主从复制就是从这个binlog文件和偏移量开始的

再打开一个ssh窗口进行全备份数据:
mysqldump -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock  --events -A -B | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz

返回上一个ssh窗口解锁表:
unlock tables;

(这一步等待验证，听说可以不用锁表也能全备)

5、scp命令传输全备文件到slave服务器

scp  /server/backup/mysql_bak.2021-07-12.sql.gz root@192.168.1.2:/server/backup/

6、设置slave服务器的server-id和关闭binlog参数

vim /etc/my.cnf
[mysqld]												#必须在mysqld下添加
server_id=2												#指定server_id,这id的值可以是整数,而且不能与其他实例相同,否则冲突
#log_bin=/usr/local/mysql/binlog 						#关闭binlog功能,删除或注释这一行即可
#如果slave库也是作为主库的就也需要开启binlog功能，如AB互为主从,ABC级联主从的情况.

修改了配置参数文件,需要重启mysql服务:
service mysql.server restart
show variables like 'server_id';						#登录数据库命令查看server_id和binlog参数
show variables like 'log_bin';

7、开始还原slave从库

gzip -d  /server/backup/mysql_bak.2021-07-12.sql.gz		#解压包,原包会被删除
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock </server/backup/mysql_bak.2021-07-12.sql

说明：因为master备份时使用-A参数全备份，所以这里从库还原之后root密码和master的root密码一致了，因为mysql数据库的user表都是一样的。

8、登录slave从库并配置参数

方法一、
登录slave从库，在命令行执行以下的命令参数：

change master to
master_host='192.168.1.1',									#master主库IP
master_port=3306,											#master主库端口
master_user='rep',											#主从复制用户rep
master_password='aabb001',									#主从复制用户rep的密码
master_log_file='mysql-bin.00008',							#上面第4步在主库show master status时看到的binlog日志
master_log_pos=342;										    #上面第4步在主库show master status时看到的binlog日志偏移量

说明：命令等号两边不能有空格。

方法二、
不用登录从库，直接在Linux命令行配置：

mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock <<EOF
change master to											#change master 语句大小写均可
master_host='192.168.1.1',									
master_port=3306,											
master_user='rep',											
master_password='aabb001',				
master_log_file='mysql-bin.00008',							#其实这一行可以省略,前提是master备份时指定--master-data=1参数
master_log_pos=342;											#其实这一行可以省略,前提是master备份时指定--master-data=1参数
EOF

9、启动slave从库开始主从复制

mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock			#登录从库
start slave;														#启动主从复制

10、slave从库查看主从复制状态

mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock			#登录slave
show slave status\G													#查看从库状态(不用加;号)

重点关注以下3个参数:
slave_IO_Running、slave_SQL_Running、Seconds_Behind_Master
slave_IO_Running: YES 表示I/O线程工作正常
slave_SQL_Running: YES 表示I/O线程工作正常
Seconds_Behind_Master: 0 此参数表示主从复制延时秒数

11、教你一招无需熬夜，轻松部署mysql主从复制

master主库半夜执行定时计划任务，命令如下：

mysqldump -uroot -p'aabb001' -S  /usr/local/mysql/bin/mysql.sock -A --events -B -x --master-data=1 | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz

白天找机会把全备份文件导入从库，并配置从库，如下：

gzip -d /server/backup/mysql_bak.2021-07-12.sql.gz
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock <<EOF
change master to
master_host='192.168.1.1',									
master_post=3306,											
master_user='rep',											
master_password='aabb001',				
EOF

注意：以上的配置从库change master语句中并没有指定master_log_file和master_log_pos，那么从库是怎么知道从哪个位置开始进行主从复制呢？答案是master全备时指定了--master-data=1参数，实际上该参数在备份文件里添加了如下语句：

 --Position to start replication or point-in-time recovery from CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.00008',MASTER_LOG_POS=342;

12、查看master主库的I /O 线程状态

mysql -uroot -p'aabb001' -S  /usr/local/mysql/bin/mysql.sock		#登录master主库
show processlist\G													#查看master主库的I /O 线程状态

13、查看slave从库的I /O 线程、SQL线程状态

mysql -uroot -p'aabb001' -S  /usr/local/mysql/bin/mysql.sock		#登录slave从库
show processlist\G													#查看slave从库的I /O 线程、SQL线程状态

14、停止主从复制/停止从库复制

mysql -uroot -p'aabb001' -S  /usr/local/mysql/bin/mysql.sock		#登录slave从库
stop slave;