2019-04-17-MySQL主从复制基础

0.企业高可用性标准 ***
0.1 全年无故障率(非计划内故障停机)
99.9% ----> 0.0013652460=525.6 min
99.99% ----> 0.00013652460=52.56 min
99.999% ----> 0.000136524*60=5.256 min
0.2 高可用架构方案
负载均衡:有一定的高可用性
LVS Nginx
主备:有高可用性,但是需要切换
KA MHA
真正高可用:
NDB Cluster Oracle RAC Sysbase cluster InnoDB Cluster（MGR）
============================================

1. 主从复制简介 **

1.1. 基于二进制日志复制的
1.2. 主库的修改操作会记录二进制日志
1.3. 从库会请求新的二进制日志,并回放,最终达到主从数据同步

2. 主从复制前提(搭建主从的过程) ***

2.1 两台以上mysql实例
2.2 主库开启二进制日志
2.3 专用的复制用户
2.4 保证主从开启之前的某个时间点,从库数据是和主库一致(补课)
2.5 告知从库,复制user,passwd,IP port,以及复制起点(change master to)
2.6 线程(三个):Dump thread  IO thread  SQL thread 开启(start slave)

3. 主从复制搭建(Classic replication) ***
   3.1 清理主库数据

rm -rf /data/3307/data/*

3.2 重新初始化3307

mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3307/data

3.3 修改my.cnf ,开启二进制日志功能

[root@db01 3307]# vim /data/3307/my.cnf 
log_bin=/data/3307/data/mysql-bin

3.4 启动所有节点

[root@db01 3307]# systemctl start mysqld3307
[root@db01 3307]# systemctl start mysqld3308
[root@db01 3307]# systemctl start mysqld3309
[root@db01 3307]# ps -ef |grep mysqld
mysql      3684      1  4 09:59 ?        00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
mysql      3719      1  7 09:59 ?        00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
mysql      3754      1  8 09:59 ?        00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
[root@db01 3307]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
[root@db01 3307]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
[root@db01 3307]# mysql -S /data/3309/mysql.sock -e "select @@server_id"

3.5 主库中创建复制用户

[root@db01 3307]# mysql -S /data/3307/mysql.sock 
db01 [(none)]>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
db01 [(none)]>select user,host from mysql.user;

3.6 备份主库并恢复到从库

[root@db01 3307]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction  -R --triggers >/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=653;
[root@db01 3307]# mysql -S /data/3308/mysql.sock
db01 [(none)]>source /backup/full.sql

3.7 告知从库关键复制信息
ip port user password binlog position
[root@db01 3307]# mysql -S /data/3308/mysql.sock
db01 [mysql]>help change master to

CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=653,
MASTER_CONNECT_RETRY=10;

CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

3.8 开启主从专用线程
start slave ;
3.9 检查复制状态
db01 [mysql]>show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

4. 主从复制的原理 (Classic Replication)*****
   4.1 主从中设置到的文件和线程
   4.1.1 线程
   主:
   DUMP THREAD
   从:
   IO THREAD
   SQL THREAD
   4.1.2 文件
   主:
   mysql-bin.000001
   从:
   db01-relay.000001 ===>中继日志
   master.info ===》主库信息记录日志
   relay-log.info ===> 记录中继应用情况信息
   4.2 主从复制原理

image.png
image.png

主从复制原理描述：
1.change master to 时，ip pot user password binlog position写入到master.info进行记录

2. start slave 时，从库会启动IO线程和SQL线程
   3.IO_T，读取master.info信息，获取主库信息连接主库
3. 主库会生成一个准备binlog DUMP线程，来响应从库
4. IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
5. DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
6. IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
   8.IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
   9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
   10.SQL_T回放完成之后，会更新relay-log.info文件。
7. relay-log会有自动清理的功能。
   细节：
   1.主库一旦有新的日志生成，会通知给binlog dump ，再和IO沟通，IO线程再请求
8. 主从故障监控\分析\处理 *****
   5.1 线程相关监控
   主库:
   show full processlist;
   每个从库都会有一行dump相关的信息
   HOSTS:
   db01:47176
   State:
   Master has sent all binlog to slave; waiting for more updates
   如果现实非以上信息,说明主从之间的关系出现了问题
   从库:
   show slave status \G;
   db01 [(none)]>show slave status \G
   *************************** 1. row ***************************
   主库有关的信息(master.info):重点关注
   Master_Host: 10.0.0.51
   Master_User: repl
   Master_Port: 3307
   Connect_Retry: 10
   Master_Log_File: mysql-bin.000001
   Read_Master_Log_Pos: 444

从库relaylog的信息(已经执行过的relaylog的位置点):重点关注
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320

从库 两个线程的基本状态:(重点关注)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

说明:在正常的主从复制关系中,都是YES
过滤复制有关的参数:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

最后一次错误的代号及具体的报错信息(了解)
Last_Errno: 0
Last_Error:

是否自动跳过复制错误(谨慎使用)
Skip_Counter: 0

从库和主库延时的秒数:
Seconds_Behind_Master: 0

分别记录的IO和SQL报错的具体信息(重点关注)
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

延时从库的状态
SQL_Delay: 0
SQL_Remaining_Delay: NULL
GTID复制有关的状态
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

5.2 主从复制故障分析