第十章·DBA-MySQL的主从复制

一.主从复制简介

2015年5月28日11时，12小时后恢复，损失：平均每小时106.48W$

1）高可用
2）辅助备份
3）分担负载

复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。

1）主服务器将所有数据和结构更改记录到二进制日志中。
2）从属服务器从主服务器请求该二进制日志并在本地应用其内容。
3）IO：请求主库，获取上一次执行过的新的事件，并存放到relaylog
4）SQL：从relaylog中将sql语句翻译给从库执行

二.主从复制原理

主从复制的前提

1）两台或两台以上的数据库实例
2）主库要开启二进制日志
3）主库要有复制用户
4）主库的server_id和从库不同
5）从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）
6）从库在第一次开启主从复制时，必须获知主库：ip，port，user，password，logfile，posIP：10.0.0.51
Port：3306
User：rep
Password：123
logFile：mysql-bin.000002
Pos：120
7) 从库要开启相关线程: IO、SQL
8）从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志
9）从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来

主从复制涉及到的文件和线程

线程:

1） dump线程: 主库上的线程，从binlog中取出数据交给从库的IO线程
2） IO线程: 从库上的线程, 连接主库dump线程取数据,取到数据写入缓存(relay log中)
3） SQL线程: 从库上的线程, 执行relay log中的SQL语句到数据库中

文件:
1） binlog日志: 主库上的文件,记录所有更改库表的语句
2） master.info: 从库上的文件,记录主库的binlog名字和位置点,IO线程更新/读取
3） relay-log.info: 从库上的文件,记录relay-log里的位置点,上一次SQL线程读取到哪里了,SQL线程更新/读取
4） relay-log：从库上的文件，记录从主库binlog拿来的新数据

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      326 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=326;

mysql> start slave;

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

原理

1）通过change master to语句告诉从库主库的ip，port，user，password，file，pos
2）从库通过start slave命令开启复制必要的IO线程和SQL线程
3）从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
4）从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的
5）主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
6）从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info
7）将TCP/IP缓存中的内容存到relay-log中
8）SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info

主从复制搭建实战

模拟生产环境用户写入数据

#!/bin/bash

mysql -uroot -p123 -e 'create database if not exists rep'
mysql -uroot -p123 -e 'create table if not exists rep.rep(id int)'
mysql -uroot -p123 -e 'truncate rep.rep'
num=1
while true;do
  mysql -uroot -p123 -e "insert into rep.rep value($num);commit;"
  (( num++ ))
  sleep 1
done

主库操作:

1）修改配置文件

#编辑mysql配置文件
[root@db01 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1，从库不等于1
server_id =1
#开启binlog日志
log_bin=mysql-bin

# 2.重启
[root@db01 ~]# /etc/init.d/mysqld restart

# 3.给主库做全备
[root@m01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /opt/full.sql.gz

# 4.将全备拷贝到从库
[root@m01 ~]# scp /opt/full.sql.gz 172.16.1.52:/opt/

2）创建主从复制用户

#登录数据库
[root@db01 ~]# mysql -uroot -p123
#创建rep用户
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by '123';

### 非生产环境
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |    69345 |
+------------------+----------+

从库操作:

# 1.修改配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2

# 2.重启数据库
[root@db02 ~]# /etc/init.d/mysqld restart

# 3.导入数据
[root@db02 ~]# zcat /opt/full.sql.gz |mysql -uroot -p

# 4.查找位置点
[root@db02 ~]# zcat /opt/full.sql.gz |head -25

# 5.执行change master to语句
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=40783;

# 6.开启主从复制
mysql> start slave;

# 7.查看主从复制状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

## 相关命令
## 开启主从复制：
start slave;

## 关闭主从复制:
stop slave;

## 重置主从复制：
stop slave;
reset slave all;

三.主从复制基本故障处理

1.IO线程故障

mysql> show slave status\G
Slave_IO_Running: No
Slave_SQL_Running: Yes

错误原因

1.IP错了
2.用户名错了
3.密码错了
4.文件名错了

CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=40783; // 不影响IO线程

排查流程

# IP错了：
[root@db02 ~]# ping 172.16.1.51

# 端口报错：
[root@db02 ~]# telnet 172.16.1.51 3306

# 用户名错了 和 密码错了：
[root@db02 ~]# mysql -urep -p123 -h172.16.1.51

## 报错：
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'rep'@'db01' (using password: YES)
## 解决办法：
vim /etc/my.cnf
[mysqld]
skip_name_resolve    # 禁止反向解析

/etc/init.d/mysqld restart

## 文件名错了
zcat /opt/full.sql.gz |head -25
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=40783;

## 解决方案：
stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=40783;
start slave;

2.SQL线程故障

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No

主库从库数据不一致导致

情况一: 主库有从库没有

CHANGE MASTER TO
MASTER_HOST='172.16.1.61',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=189016;

## 查看状态
mysql> show slave status\G
Last_Errno: 1049
Last_Error: Error 'Unknown database 'test_sql_thread'' on query. Default database:
'test_sql_thread'. Query: 'create table tb1(id int)'

#### 解决方案一：
# 1.停止从库的主从复制
mysql> stop slave;

# 2.跳过一次错误
mysql> set global sql_slave_skip_counter=1;

# 3.开启主从复制
mysql> start slave;

#### 解决方案二：
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
slave-skip-errors=1032,1062,1007,1049,1146

[root@db02 ~]# /etc/init.d/mysqld restart

#### 解决方案三：

1）重新备份数据库，恢复到从库
2）给从库设置为只读
#在命令行临时设置
set global read_only=1;
#在配置文件中永久生效
read_only=1

情况二：主库没有从库有

#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
#开启同步
mysql> start slave;

四.延时从库

普通的主从复制可能存在不足

1）逻辑损坏怎么办？
2）不能保证主库的操作，从库一定能做
3）高可用？自动failover？
4）过滤复制

企业中一般会延时3-6小时

延时从库配置方法

#停止主从
mysql>stop slave;

#设置延时为180秒
mysql>CHANGE MASTER TO MASTER_DELAY = 180;

#开启主从
mysql>start slave;

#查看状态
mysql> show slave status \G
SQL_Delay: 60
3.延时从库停止方法

# 没有主从时，配置
CHANGE MASTER TO
MASTER_HOST='172.16.1.61',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=189016,
MASTER_DELAY=120;

# 验证延时情况
mysql> show slave status\G
SQL_Remaining_Delay: 93

# 启停SQL线程
mysql> stop slave sql_thread;
mysql> start slave sql_thread;

# 启停IO线程
mysql> stop slave io_thread;
mysql> start slave io_thread;

五.半同步复制

从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。

半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。 出发点是保证主从数据一致性问题，安全的考虑。

5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；

半同步复制开启方法

1）安装（主库）

#登录数据库
[root@db01 ~]# mysql -uroot -poldboy123
#查看是否有动态支持
mysql> show global variables like 'have_dynamic_loading';
#安装自带插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#设置超时
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
#修改配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容（不用重启库）
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
检查安装：
mysql> show variables like'rpl%';
mysql> show global status like 'rpl_semi%';

2）安装（从库）

#登录数据库
[root@mysql-db02 ~]# mysql -uroot -poldboy123
#安装slave半同步插件
mysql>  INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
#重启io线程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;
#编辑配置文件（不需要重启数据库）
[root@mysql-db02 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容
[mysqld]
rpl_semi_sync_slave_enabled =1

注：相关参数说明

rpl_semi_sync_master_timeout=milliseconds
设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。

rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。 可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。

测试半同步

#创建两个数据库，test1和test2
mysql> create database test1;
Query OK, 1 row affected (0.04 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
#查看复制状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 768   |
| Rpl_semi_sync_master_net_wait_time         | 1497  |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 884   |
| Rpl_semi_sync_master_tx_wait_time          | 1769  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
#此行显示2，表示刚才创建的两个库执行了半同步
| Rpl_semi_sync_master_yes_tx                | 2     | 
+--------------------------------------------+-------+
14 rows in set (0.06 sec)
#从库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test1              |
| test2              |
+--------------------+
#关闭半同步（1:开启 0:关闭）
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
#查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 768   |
| Rpl_semi_sync_master_net_wait_time         | 1497  |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   | #状态为关闭
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 884   |
| Rpl_semi_sync_master_tx_wait_time          | 1769  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 2     | 
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

#再一次创建两个库
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)

#再一次查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 768   |
| Rpl_semi_sync_master_net_wait_time         | 1497  |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 884   |
| Rpl_semi_sync_master_tx_wait_time          | 1769  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
#此行还是显示2，则证明，刚才的那两条并没有执行半同步否则应该是4
| Rpl_semi_sync_master_yes_tx                | 2     | 
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有
{1}

六.过滤复制

主库：

白名单:只记录白名单中列出的库的二进制日志
binlog-do-db

黑名单：不记录黑名单列出的库的二进制日志
binlog-ignore-db

从库：

白名单：只执行白名单中列出的库或者表的中继日志
--replicate-do-db=test
--replicate-do-table=test.t1
--replicate-wild-do-table=test.t2

黑名单：不执行黑名单中列出的库或者表的中继日志
--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table

复制过滤配置:

[root@db01 data]# vim /data/3307/my.cnf 
#在[mysqld]标签下添加
replicate-do-db=world
#关闭MySQL
mysqladmin -S /data/3307/mysql.sock  shutdown
#启动MySQL
mysqld_safe --defaults-file=/data/3307/my.cnf &

测试复制过滤:

第一次测试:

1）主库：

[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock 
mysql> use world
mysql> create table t1(id int);

2）从库查看结果：

[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock 
mysql> use world
mysql> show tables;

第二次测试：

1）主库：

[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock 
mysql> use test
mysql> create table tb1(id int);

2）从库查看结果：

[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock 
mysql> use test
mysql> show tables;

延时从库: 思考问题

总数据量级500G，正常备份去恢复需要1.5-2小时

1）配置延时3600秒

mysql>CHANGE MASTER TO MASTER_DELAY = 3600;

2）主库

drop database db;

3）怎么利用延时从库，恢复数据？

提示：
1、从库relaylog存放在datadir目录下
2、mysqlbinlog 可以截取relaylog内容
3、show relay log events in 'db01-relay-bin.000001'
配置延时从库

mysql> change master to
    -> master_host='172.16.1.61',
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000014',
    -> master_log_pos=120,
    -> master_delay=300;

模拟数据

#!/bin/bash

mysql -uroot -p123 -e 'create database if not exists test_delay'
mysql -uroot -p123 -e 'create table if not exists test_delay.test_delay(id int)'
mysql -uroot -p123 -e 'truncate test_delay.test_delay'
num=1
while true;do
  mysql -uroot -p123 -e "insert into test_delay.test_delay value($num);commit;"
  (( num++ ))
  sleep 1
done

删除数据

mysql> drop database rep;

处理思路

# 1.先停掉从库上的SQL线程
mysql> stop slave sql_thread;

# 2.先停业务，停掉主库
[root@m01 ~]# /etc/init.d/mysqld stop

# 3.先给从库做全备
[root@db02 scripts]# mysqldump -A -R --triggers  > /tmp/2.sql

# 4.找到relay log的起始位置点
[root@db02 data]# cat  relay-log.info
./db02-relay-bin.000002
283
mysql-bin.000014
120

[root@db02 data]# mysqlbinlog -vvv --base64-output=decode-row db02-relay-bin.000002|grep -i drop -C 5

起始位置点：283
结束位置点：39979

起始位置点：40068
结束位置点：61644

[root@db02 data]# mysqlbinlog --start-position=283 --stop-position=39979 db02-relay-bin.000002 >/tmp/inc1.sql
[root@db02 data]# mysqlbinlog --start-position=40068 --stop-position=61644 db02-relay-bin.000002 >/tmp/inc2.sql
[root@db02 data]# scp /tmp/2.sql /tmp/inc* 172.16.1.61:/opt

# 5.启动主库
[root@m01 ~]# /etc/init.d/mysqld start

# 6.导入数据
mysql> source /opt/2.sql
mysql> source /opt/inc1.sql
mysql> source /opt/inc2.sql