数据库 - MySQL 主从问题详解 - 《博客专栏》

生产环境搭建主从
- 在一台服务器上装两个mysql
- 配置主从
主从复制(过程)原理
- 涉及到的文件
- 涉及到的线程
主从复制监控
- 参数详情
主从故障分析
- io故障
- sql故障
主从延时问题以及监控
- 主库的原因
- 从库原因

生产环境搭建主从

一个跑了很长时间的数据库给他做主从，要保证数据一样

做一台服务器两个数据库的主从
首先带全备一下主库，指定master-data用于定义二进制主从的开始点

在一台服务器上装两个mysql

二进制安装，上传mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz到服务器上

#解压
tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
mv mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql-5.7.24
#创建相应的目录
mkdir -p /data/app
mkdir -p /data/{3306,3307}/data
mkdir -p /data/socket
ln -s /data/mysql-5.7.24 /data/app/mysql-5.7.24
#3306实例配置
cat > /data/3306/my.cnf <<cao
[mysqld]
user=mysql
basedir=/data/app/mysql-5.7.24
datadir=/data/3306/data
socket=/data/socket/mysql_3306.sock
port=3306
server_id=6
log_bin=/data/3306/mysql-bin
cao
#3307实例配置
cat > /data/3307/my.cnf <<cao
[mysqld]
user=mysql
basedir=/data/app/mysql-5.7.24
datadir=/data/3307/data
socket=/data/socket/mysql_3307.sock
port=3307
server_id=7
log_bin=/data/3306/mysql-bin
cao
#创建mysql用户并授权
useradd -s /sbin/nologin mysql
chown -R mysql:mysql /data
#初始化
/data/app/mysql-5.7.24/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-5.7.24 --datadir=/data/3306/data
/data/app/mysql-5.7.24/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-5.7.24 --datadir=/data/3307/data
#编写systemd的启动脚本
--3306
cat > /etc/systemd/system/mysqld3306.service <<cao
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql-5.7.24/bin/mysqld --defaults-file=/data/3306/my.cnf
LimitNOFILE = 5000
cao
--3307
cat > /etc/systemd/system/mysqld3307.service <<cao
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql-5.7.24/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
cao
#启动测试
systemctl start mysqld{3306,3307}
yum -y install net-tools
netstat -tnlp
#写入环境变量，修改mysql密码
echo 'export PATH=$PATH:/data/app/mysql-5.7.24/bin' >> /etc/profile
source /etc/profile
mysqladmin -uroot -p password 123 -S /data/socket/mysql_3306.sock
mysqladmin -uroot -p password 123 -S /data/socket/mysql_3307.sock

配置主从

#主库创建授权用户
mysql -S /data/socket/mysql_3306.sock -e "grant replication slave on *.* to repl@'172.16.0.0' identified by '123'"
#主库全量备份，恢复到从库
mysqldump -S /data/socket/mysql_3306.sock -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full.sql
#从库恢复
mysql -S /data/socket/mysql_3307.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql;
#指定恢复的节点
查看具体用法 help change master to ，下列的参数需要跟全量备份的节点对照
[root@localhost ~]# cat /tmp/full.sql | sed -n 22p
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=446;
mysql> CHANGE MASTER TO
  MASTER_HOST='172.16.0.14',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
#开启slave线程(IO,SQL)
start slave;
show slave status \G

主从复制(过程)原理

MySQL 主从问题详解 - 图1

涉及到的文件

主库：
    binlog
从库：
    relaylog    中继日志
    master.info    主库的信息文件
    relaylog.info    relaylog应用的信息

涉及到的线程

主库：
    binlog_dump thread : dump_t
从库：
    slave_io_thread : io_t
    slave_sql_thread : sql_t

主从复制监控

show slave status \G

参数详情

主库的有关信息（master.info）：
Master_Host: 172.16.0.14
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
***********************************
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 154
***********************************
从库中继日志信息有关（relay.info）：
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000006
从库的线程运行状态：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
过滤复制有关的信息：
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
从库延迟的时间：
Seconds_Behind_Master: 0
人为延时从库：
SQL_Delay: 0
SQL_Remaining_Delay: NULL
gtid复制有关的：
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

主从故障分析

io故障

--线程故障（connect）
网络、防火墙、连接信息不对、用户没权限、连接数上限
排查：手动mysql -urepl -p123 -P3306测试
--请求binlog（no）
master节点做了reset操作、master节点binlog损坏、master节点没开binlog日志
解决：
    从库停止slave同步，reset slave，重新change master to，启动slave
--存储binlog到relaylog

sql故障

造成原因：
    对象已存在，无法执行dml，主键冲突，约束冲突等
解决：
    重建主从，把握一个原则，一切以主库为主
避免方法：
    1、从库只读
        read_only
        super_read_only
    2、使用读写分离中间件
        mycat
        proxysql
        maxscale

主从延时问题以及监控

主从延时的估算时间值

mysql> show slave status \G

Seconds_Behind_Master: 0

该结果只做参考，具体还需查看binlog和relay_log定位问题

主库的原因

1、binlog写入不及时
    解决：sync_binlog=1    实时同步
2、默认情况下 dump_t 是串行挨个传输binlog的，并发事务量大或者大事物
    解决：必须GTID，使用group commit方式，可以支持dump_t的并行模式

#主库的binlog位置
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 154

从库原因

1、传统复制（classic）中，从库的sql线程是单线程
    解决，使用GTID，5.6版本基于库的多线程单库还是串行，5.7版本是基于事务的多线程（MTS）
2、主从的参数配置不一样
3、主从的索引不一样
4、版本差异

mysql> show slave status \G
*************************** 1. row ***************************
#从库的binlog位置
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 154
#从库的sql线程回放relay_log的位置
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
#从库的sql线程回放relay_log的位置对应的binlog位置
Exec_Master_Log_Pos: 154
Relay_Log_Space: 531
#也可以通过查看数据库data目录下的 relay_log.info 来对照relay_log和bin_log的位置关系