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

  1. pt-table-checksum 负责检测MySQL主从数据一致性

  2. pt-table-sync负责挡住从数据不一致时修复数据,让他们保存数据的一致性

  3. 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   |
+----+------+
本作品采用《CC 协议》,转载必须注明作者和本文链接
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!