第十章主从复制

一.主从复制简介

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

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

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

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

二.主从复制原理

1.主从复制的前提

1）两台或两台以上的数据库实例

2）主库要开启二进制日志

3）主库要有复制用户

4）主库的server_id和从库不同

5）从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）

6）从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos

IP：10.0.0.51
Port：3306
User：rep
Password：oldboy123
logFile：mysql-bin.000002
Pos：120

7）从库要开启相关线程：IO、SQL

8）从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志

9）从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来

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

主库：

1）主库binlog：记录主库发生过的修改事件
2）dump thread：给从库传送（TP）二进制日志线程

从库：

1）relay-log（中继日志）：存储所有主库TP过来的binlog事件
2）master.info：存储复制用户信息，上次请求到的主库binlog位置点
3）IO thread：接收主库发来的binlog日志，也是从库请求主库的线程
4）SQL thread：执行主库TP过来的日志

原理

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

3.主从复制搭建实战

主库操作:

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 ~]# mysql -uroot -poldboy123
#创建rep用户
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';

从库操作:

1）修改配置文件

#修改db02配置文件
[root@db02 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1，从库不等于1
server_id =5
#重启mysql
[root@db02 ~]# /etc/init.d/mysqld restart
#记录主库binlog及位置点
mysql> show master status;
#登陆数据库
[root@db02 ~]# mysql -uroot -poldboy123
#执行change master to 语句
mysql> change master to
    -> master_host='127.0.0.1',
    -> master_port=3309,
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=1165;

#从库开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

#从库查看启动结果
mysql> show slave status\G

回到顶部(go to top)

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

1.IO线程

连接主库

1）user password ip port
2）网络：不通，延时高，防火墙

请求binlog

1）binlog不存在或者损坏

更新relay-log和master.info

SQL线程
1）relay-log出现问题
2）从库做写入了

操作对象已存在（create）
操作对象不存在（insert update delete drop truncate alter）
约束问题、数据类型、列属性

2.处理方法一：

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

3.处理方法二：

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007

但是以上操作都是有风险存在的

4.处理方法三：

1）重新备份数据库，恢复到从库
2）给从库设置为只读

#在命令行临时设置
set global read_only=1;
#在配置文件中永久生效
read_only=1

回到顶部(go to top)

五.延时从库

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

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

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

2.延时从库配置方法

#停止主从
mysql>stop slave;
#设置延时为180秒
mysql>CHANGE MASTER TO MASTER_DELAY = 180;
#开启主从
mysql>start slave;
#查看状态
mysql> show slave status \G
SQL_Delay: 60
3.延时从库停止方法
#停止主从
mysql> stop slave;
#设置延时为0
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
#开启主从
mysql> start slave;

3.思考问题：

总数据量级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';

4.处理的思路：

1）停止SQL线程

mysql> stop slave sql_thread;

2）截取relaylog到误删除之前点

relay-log.info 获取到上次运行到的位置点，作为恢复起点
分析relay-log的文件内容，获取到误删除之前position

5.模拟故障处：

1）关闭延时

mysql -S /data/3308/mysql.sock
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;

2）模拟数据

mysql -S /data/3307/mysql.sock
source  /root/world.sql
use world;
create table c1 select * from city;
create table c2 select * from city;

3）开启从库延时5分钟

mysql -S /data/3308/mysql.sock
show slave status \G

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;

mysql -S /data/3307/mysql.sock
use world;
create table c3 select * from city;
create table c4 select * from city;

4）破坏，模拟删库故障。(以下步骤在5分钟内操作完成。)

mysql -S /data/3307/mysql.sock
drop database world;

5）从库，关闭SQL线程

mysql -S /data/3308/mysql.sock
stop slave sql_thread;

6）截取relay-log

起点：
cd /data/3308/data/
cat relay-log.info
./db01-relay-bin.000002
283
终点：
mysql -S /data/3308/mysql.sock
show relaylog events in 'db01-relay-bin.000002'
db01-relay-bin.000002 | 268047 
mysqlbinlog --start-position=283  --stop-position=268047 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql

6.恢复relay.sql

1）取消从库身份

mysql> stop slave;
mysql> reset slave all;

2）恢复数据

mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> use world
mysql> show tables;

回到顶部(go to top)

六.半同步复制

从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.半同步复制开启方法

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就会恢复到异步复制。

2.测试半同步

#创建两个数据库，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)
注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有

回到顶部(go to top)

七.过滤复制

1.主库：

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

binlog-do-db

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

binlog-ignore-db

2.从库：

白名单：只执行白名单中列出的库或者表的中继日志

--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

3.复制过滤配置：

[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 &

4.测试复制过滤：

第一次测试：

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;

posted @ 2019-11-16 14:54 lianbingrong 阅读(274) 评论(0) 编辑收藏举报

刷新页面返回顶部

lianbingrong

第十章 主从复制

一.主从复制简介

二.主从复制原理

1.主从复制的前提

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

3.主从复制搭建实战

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

1.IO线程

2.处理方法一：

3.处理方法二：

4.处理方法三：

五.延时从库

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

2.延时从库配置方法

3.思考问题：

4.处理的思路：

5.模拟故障处：

6.恢复relay.sql

六.半同步复制

1.半同步复制开启方法

2.测试半同步

七.过滤复制

1.主库：

2.从库：

3.复制过滤配置：

4.测试复制过滤：

第十章主从复制