MYSQL主从

MySQL
478
0
0
2022-11-14
基于mysql分布式悲观锁原理
上一篇
Inno DB 事务的提交
下一篇

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   |
+----+------+
基于mysql分布式悲观锁原理
上一篇
Inno DB 事务的提交
下一篇