学习mysql第八天--主从复制

1.介绍

两台或以上数据库实例，通过二进制日志，实现数据的“同步”关系
核心功能：辅助备份，处理物理损坏

2.主从复制前提（搭建过程）

##两台以上mysql实例 ,时间同步，网络畅通，server_id,server_uuid不同
##主库开启二进制日志binlog，建立专用的复制用户
##从库需要提前补课，将之前主库中的复制过来
##从库：主库的连接信息，确认复制起点
##从库：开启专用的复制线程
线程：Dump IO sql

3.搭建

（1）实例准备

Systenctl restart 3307.8.9
ps -ef |grep mysqld

（2）检查server_id

#mysql -S /data/3307/mysql.sock -e "select @@server_id"
#mysql -S /data/3308/mysql.sock -e "select @@server_id"
#mysql -S /data/3309/mysql.sock -e "select @@server_id"

检查binlog是否开启
#mysql -S /data/3308/mysql.sock -e "select @@log_bin";
开启binlog
[root@db01 3307]# vim /data/3307/my.cnf 
log_bin=/data/3307/data/mysql-bin

（3）主库建立复制用户

将3307作为主库
#mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.247.%' identified by '123'";
验证用户
mysql -uroot -p -S /data/3307/mysql.sock -e "select user,host from mysql.user";

（4）主库备份恢复到从库

3307进行全备：
mysqldump  -S /data/3307/mysql.sock -A --master-data=2 --single-transaction > /tmp/all.sql
在备份文件找到备份标志用在主从连接语句中：
#grep "\-- CHANGE MASTER TO" /tmp/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=449;

恢复（这一步就是补课，为了保证从库和主库数据一致）

Mysql  -S /data/3308/mysql.sock < /tmp/all.sql 
mysql  -S /data/3309/mysql.sock < /tmp/all.sql 

（5）告知从库复制信息

在数据库中进行查看命令：mysql> help change master to;
修改：
CHANGE MASTER TO
MASTER_HOST=‘192.168.247.10’, 主库地址
MASTER_USER=‘repl’, 主库用户
MASTER_PASSWORD=‘123’, 密码
MASTER_PORT=3307, 端口
MASTER_LOG_FILE=‘mysql-bin.000001’, 全备结束的日志
MASTER_LOG_POS=738, 全备结束的pos号
MASTER_CONNECT_RETRY=10; 连接数

查看全备结束的日志号：# grep “-- CHANGE MASTER TO” /tmp/all.sql
– CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002’, MASTER_LOG_POS=449;

从库进行连接：
#mysql -S /data/3308/mysql.sock
CHANGE MASTER TO
  MASTER_HOST='192.168.247.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=449,
  MASTER_CONNECT_RETRY=10;

#mysql  -S /data/3309/mysql.sock
CHANGE MASTER TO
  MASTER_HOST='192.168.247.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=449,
  MASTER_CONNECT_RETRY=10;

以上就是完成了 从库：主库的连接信息，确认复制起点
如果命令错了：先停止复制：stop slave;
重置复制：reset slave;

（6）开启专用的线程

打开从库
#mysql -uroot -p -S /data/3308/mysql.sock
Start slave
#mysql -uroot -p -S /data/3309/mysql.sock
Start slave

（7）验证

#mysql -uroot -p -S /data/3308/mysql.sock -e "show slave status\G"|grep Running:
Enter password: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#mysql -uroot -p -S /data/3309/mysql.sock -e "show slave status\G"|grep Running:
Enter password: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
出现2个yes即可

如果搭建失败，执行下面命令：

Mysql -S /data/3308/mysql.sock -e “stop slave;reset slave all;”

4.主从复制的原理

（1）主从中涉及到的资源

1>文件:
主库：binlog文件mysql-bin.000001

db01-relay 文件 ：存储接受主库的binlog 
show  variables like '%relay%';查看位置
位置：默认从库的数据目录下。db01-relay-bin.000001  db01-relay-bin.000002
手工定义的方法：
Relay_log_basename=/data/（需要提前创建好目录并加权限）

Master.info ：存储连接主库的信息，就是change master to 里面的信息。存储已经接受到的binlog的位置点信息
位置：默认存储在从库数据路径下；          
Show variables like ‘%master%’;
手工定义存放类型是文件还是表：
master_info_repository=FILE/TABLE;可以设置存放的位置，存放到文件file或表table，存到表会提高性能

Relay-log.info  :记录从库回放的relay-log的位置点
位置：默认在从库的数据路径下relay-log.info
mysql> show variables like '%relay%';
手工定义存放类型是文件还是表：relay_log_info_repository=FILE/TABLE

2>线程

主库：
Binlog_dump_Thread:
作用：用来接收从库请求，并且投递binlog给从库
Show processlist;
从库：
IO线程：请求binlog日志，接收binlog日志
SQL线程：执行（回放）relay日志

（2）主从复制原理过程

过程：
1）从库： change master to 位置信息写入从库的master.info中，执行start slave连接到主库（启动sql lo线程）
2）主库：分配Binlog_dump_Thread线程，专门和从库的IO通信
3）从库：lo线程会根据master.info录的binlog文件名和position号，请求主库DUMP最新日志
4）主库：DUMP线程检查主库的binlog日志，如果有新的，传送给从从库的IO_T
5）从库：Io线程接收到binlog日志，日志放在tcp/ip缓存中，此时网络层层面返回ACK给主库，主库完成
6）从库：io会将缓存中的数据存储到relaylog中，更新master.info文件binlog 文件名和postion，IO工作完成
7）从库：sql线程读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
8）SQL回放完成之后，会更新relay-log.info文件。

5.主从故障监控

（1）主库方面

Show processlist;
查询具体从库：Show slave hosts;
上面2个命令查看连接的从库信息

（2）从库方面

Show slave status\G
主库相关信息监控
Master_Host:192.168.247.10
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 449

从库的relay-log 状态，来自于Relay.info ，一般用于主库延迟；以及relay-log与binlog对应的位置
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 829295
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 154

从库的线程状态，
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
具体报错信息
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

过滤复制相关信息，如我只想复制db1库
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

6.主从故障分析和处理

（1）怎么看故障

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

（2）IO线程方面故障

1>连接主库故障
故障提示信息：NO ；Connecting；
原因：连接信息有误；网络故障；防火墙；最大连接数上限
排查方法：使用用户名 密码 端口 等信息进行登录数据库，看能不能登录来排查
处理方法：先关闭线程stop slave；再关闭主从连接reset slave all;重新输入连接信息charmaster to；在start slave；

2>请求日志故障
故障提示信息：Last_IO_Errno: NO
原因：主库日志损坏；从库方面，二进制日志位置点不对，日志起点写错；server_id重复
排查方法：show slave status\G;
观察这2个位置：Master_Log_File: mysql-bin.000002；Read_Master_Log_Pos: 154
注意: 在主从复制环境中,严令禁止主库中reset master; 可以选择expire 进行定期清理主库二进制日志

（3）Sql线程故障：回放中继日志

1>中继日志损坏relay.log
细节：单独关闭sql线程：stop slave sql_thread ; start slave io_thread ;

提示：

处理：现在是relay log损坏。我们要根据已经执行完毕的relaylog位置点367找到对应的master log位置点；然后将masterlog后的到154位置点的binlog日志恢复到从库即可

怎么找位置点：去relay.info的文件中，这里就是已经运行完毕的relay.log对应传递来的binlog为位置点

恢复：将主从停止，重新charmaster to 将binlog154的位置点写到连接信息中，重新建立连接即可；

2>日志回放失败，执行不了sql
原因：sql语句没有对应的表或库；创建的对象已存在；约束冲突；主从配置不同；sql_mode不兼容；主从版本差异；
操作：先在从库创建了t1库，完了又在主库中创建了t1库，此时就会报错；
报错提示：

解决：

方法1：在从库执行反操作，对于创建的库表，就删除，在Start slave；
方法2：跳过这个报错；Stop slave  ；Set global sql_slave_skip_counter=1
Start slave

方法3：pt工具