MYSQL主从 - 乐耶园

1.主从复制原理

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

2.搭建主从复制

2.1 主服务器配置

2.1.1 开启binlog

 
vim /etc/my.cnf
 
# 开启
[mysqld]
server_id=1
log-bin=mysql-bin
 
# 重启mysql服务
systemctl restart mysqld

2.1.2 添加用户(mysql8需要先添加用户)

语法：CREATE USER ‘用户名‘@’主机’ IDENTIFIED BY ‘密码’;

 
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
 
-- 查看
SELECT user,host FROM mysql.user;
 
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

2.1.3 删除用户


DROP USER 'test'@'%';

2.1.4 授权一个账号

主要让从服务器可以通过该账号访问binlog日志的内容

语法：GRANT ALL PRIVILEGES ON 数据库名.表名 TO ‘用户名‘@’主机地址’ WITH GRANT OPTION;

 
-- 主要让从服务器可以通过该账号访问binlog日志的内容
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;
 
-- 刷新权限
FLUSH PRIVILEGES;

2.1.5 查看最新的binlog日志

 
SHOW MASTER STATUS;
 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1778 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2.2 从服务器配置

2.2.1 开启binlog

 
vim /etc/my.cnf
 
# 开启
[mysqld]
server_id=2 # 不能和主服务器一致
log-bin=mysql-bin
 
# 重启mysql服务
systemctl restart mysqld
# 可以使用以下语句查看server-id
SHOW VARIABLES LIKE "%server_id%";

2.2.2 停止从服务器


STOP SLAVE;

2.2.3 配置

CHANGE MASTER TO MASTER_HOST=’主机地址’,MASTER_USER=’授权用户名’,MASTER_PASSWORD=’授权用户的密码’,MASTER_LOG_FILE=’二进制日志文件的名称’,MASTER_LOG_POS=记录的pos位置；


CHANGE MASTER TO MASTER_HOST='192.168.179.131',MASTER_USER='test',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=1778;

2.2.4 启动从服务器


START SLAVE;

2.2.5 查看配置状态

 
SHOW SLAVE STATUS\G;
 
Slave_IO_Running: Yes -- 代表配置成功
Slave_SQL_Running: Yes

2.2.6 查看pos节点

可以根据pos节点去关联从数据库

 
SHOW BINLOG EVENTS IN "mysql-bin.000004"\G;
 
*************************** 10. row ***************************
   Log_name: mysql-bin.000004
        Pos: 803
 Event_type: Query
  Server_id: 1
End_log_pos: 911
       Info: use `mysql`; DROP USER 'test'@'%' /* xid=56 */
*************************** 11. row ***************************
   Log_name: mysql-bin.000004
        Pos: 911
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 990
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 12. row ***************************

2.2.7 注意

如果虚拟机是克隆的会报以下错误：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

查看uuid


SHOW VARIABLES LIKE '%server_uuid%';

解决方法

 
vim data/auto.cnf
# 修改成随机的就行
server-uuid=06db02f2-1d6a-11ed-a8f9-000c29acf30b

3. 主从复制问题

3.1 数据一致性问题

从库中对数据进行更改，可能导致主库不同步

3.1.1 安装percona-toolkit工具

percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息，磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等

pt-table-checksum 负责检测MySQL主从数据一致性
pt-table-sync负责挡住从数据不一致时修复数据，让他们保存数据的一致性
pt-heartbeat 负责监控MySQL主从同步延迟

 
# 安装依赖
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL -y
# 安装rpm包
wget https://repo.percona.com/pt/yum/release/8.0/RPMS/x86_64/percona-toolkit-3.4.0-3.el8.x86_64.rpm
# 安装
yum install -y percona-toolkit-3.4.0-3.el8.x86_64.rpm
# 查看版本
pt-table-checksum --version

3.1.2 检测一致性

注意：从服务器也要创建一个test用户，和前面一样即可

–nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用–databases来指定需要检查的数据库。

–no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。

–replicate-check-only :只显示不同步的信息。

–replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。

–databases= ：指定需要被检查的数据库，多个则用逗号隔开。

–tables= ：指定需要被检查的表，多个用逗号隔开

–host= ：Master的地址

–use= ：用户名

–password=：密码

–port= ：端口

 
pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=test --tables=test --user=test --password=123456
 
# DIFFS 表示不相同的数据
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
08-31T11:13:33      0      1        2          1       1       0   0.737 test.test

如果报错：Diffs cannot be detected because no slaves were found

 
# 关闭防火墙即可
systemctl stop firewalld

3.2 数据一致恢复 pt-table-sync

–replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。

–databases= : 指定执行同步的数据库，多个用逗号隔开。

–tables= ：指定执行同步的表，多个用逗号隔开。

–sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。

h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。

u=root ：帐号。

p=123456 ：密码。

P=3306: 端口

–print ：打印，但不执行命令。

–execute ：执行命令。

3.2.1 查看执行流程

 
# 打印恢复执行的流程 192.168.179.132是从服务器地址
pt-table-sync --sync-to-master h=192.168.179.132,u=test,p=123456,P=3306 --databases=test --tables=test --print
 
# 执行流程
DELETE FROM `test`.`test` WHERE `id`='3' LIMIT 1 /*percona-toolkit src_db:test src_tbl:test src_dsn:P=3306,h=192.168.179.131,p=...,u=test dst_db:test dst_tbl:test dst_dsn:P=3306,h=192.168.179.132,p=...,u=test lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:13344 user:root host:localhost.localdomain*/;

3.2.2 恢复数据一致性

可以使用定时脚本进行数据一致性恢复

 
# 执行后从库数据会跟随主库数据进行同步
# 主库数据
+----+------+
| id | name |
+----+------+
|  1 | 20   |
|  2 | 30   |
+----+------+
# 从库数据
+----+------+
| id | name |
+----+------+
|  1 | 20   |
|  2 | 30   |
|  3 | 333  |
+----+------+
 
# 执行数据进行恢复
pt-table-sync --sync-to-master h=192.168.179.132,u=test,p=123456,P=3306 --databases=test --tables=test --execute;
 
# 恢复后从库数据
+----+------+
| id | name |
+----+------+
|  1 | 20   |
|  2 | 30   |
+----+------+

	vim /etc/my.cnf

	# 开启
	[mysqld]
	server_id=1
	log-bin=mysql-bin

	# 重启mysql服务
	systemctl restart mysqld

	CREATE USER 'test'@'%' IDENTIFIED BY '123456';

	-- 查看
	SELECT user,host FROM mysql.user;

	+------------------+-----------+
	\| user \| host \|
	+------------------+-----------+
	\| test \| % \|
	\| mysql.infoschema \| localhost \|
	\| mysql.session \| localhost \|
	\| mysql.sys \| localhost \|
	\| root \| localhost \|
	+------------------+-----------+

	-- 主要让从服务器可以通过该账号访问binlog日志的内容
	GRANT ALL PRIVILEGES ON . TO 'test'@'%' WITH GRANT OPTION;

	-- 刷新权限
	FLUSH PRIVILEGES;

	SHOW MASTER STATUS;

	+------------------+----------+--------------+------------------+-------------------+
	\| File \| Position \| Binlog_Do_DB \| Binlog_Ignore_DB \| Executed_Gtid_Set \|
	+------------------+----------+--------------+------------------+-------------------+
	\| mysql-bin.000004 \| 1778 \| \| \| \|
	+------------------+----------+--------------+------------------+-------------------+

	SHOW SLAVE STATUS\G;

	Slave_IO_Running: Yes -- 代表配置成功
	Slave_SQL_Running: Yes

	SHOW BINLOG EVENTS IN "mysql-bin.000004"\G;

	************************* 10. row *************************
	Log_name: mysql-bin.000004
	Pos: 803
	Event_type: Query
	Server_id: 1
	End_log_pos: 911
	Info: use `mysql`; DROP USER 'test'@'%' /* xid=56 */
	************************* 11. row *************************
	Log_name: mysql-bin.000004
	Pos: 911
	Event_type: Anonymous_Gtid
	Server_id: 1
	End_log_pos: 990
	Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
	************************* 12. row *************************

	vim data/auto.cnf
	# 修改成随机的就行
	server-uuid=06db02f2-1d6a-11ed-a8f9-000c29acf30b

	# 安装依赖
	yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL -y
	# 安装rpm包
	wget https://repo.percona.com/pt/yum/release/8.0/RPMS/x86_64/percona-toolkit-3.4.0-3.el8.x86_64.rpm
	# 安装
	yum install -y percona-toolkit-3.4.0-3.el8.x86_64.rpm
	# 查看版本
	pt-table-checksum --version

	pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=test --tables=test --user=test --password=123456

	# DIFFS 表示不相同的数据
	TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
	08-31T11:13:33 0 1 2 1 1 0 0.737 test.test

	# 打印恢复执行的流程 192.168.179.132是从服务器地址
	pt-table-sync --sync-to-master h=192.168.179.132,u=test,p=123456,P=3306 --databases=test --tables=test --print

	# 执行流程
	DELETE FROM `test`.`test` WHERE `id`='3' LIMIT 1 /percona-toolkit src_db:test src_tbl:test src_dsn:P=3306,h=192.168.179.131,p=...,u=test dst_db:test dst_tbl:test dst_dsn:P=3306,h=192.168.179.132,p=...,u=test lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:13344 user:root host:localhost.localdomain/;

	# 执行后从库数据会跟随主库数据进行同步
	# 主库数据
	+----+------+
	\| id \| name \|
	+----+------+
	\| 1 \| 20 \|
	\| 2 \| 30 \|
	+----+------+
	# 从库数据
	+----+------+
	\| id \| name \|
	+----+------+
	\| 1 \| 20 \|
	\| 2 \| 30 \|
	\| 3 \| 333 \|
	+----+------+

	# 执行数据进行恢复
	pt-table-sync --sync-to-master h=192.168.179.132,u=test,p=123456,P=3306 --databases=test --tables=test --execute;

	# 恢复后从库数据
	+----+------+
	\| id \| name \|
	+----+------+
	\| 1 \| 20 \|
	\| 2 \| 30 \|
	+----+------+