mysql 主从_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，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，执行过的信息保存下来

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

主库：

1)主库binlog：记录主库发生过的修改事件

2)dump thread：给从库传送(TP)二进制日志线程

从库：

1)relay-log(中继日志)：存储所有主库TP过来的binlog事件

2)master.info：存储复制用户信息，上次请求到的主库binlog位置点

3)IO thread：接收主库发来的binlog日志，也是从库请求主库的线程

4)SQL thread：执行主库TP过来的日志

开启线程的两种方法

start slave IO_thread;start slave SQL_thread; start slave；

原理

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

主从复制搭建实战

主库操作:

1)修改配置文件

#编辑mysql配置文件[root@db01 ~]# vim /etc/my.cnf#在mysqld标签下配置[mysqld]#主库server-id为1，从库不等于1server_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，从库不等于1server_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='10.0.0.51',-> master_user='rep',-> master_password='oldboy123',-> master_auto_position=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)
• 约束问题、数据类型、列属性

处理方法一：

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

处理方法二：

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

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

处理方法三：

1)重新备份数据库，恢复到从库

2)给从库设置为只读

#在命令行临时设置set global read_only=1;#在配置文件中永久生效read_only=1查看状态mysql> show variables like 'read_only';+---------------+-------+| Variable_name | Value |+---------------+-------+| read_only     | ON    |+---------------+-------+

五.延时从库

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

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 GSQL_Delay: 603.延时从库停止方法#停止主从mysql> stop slave;#设置延时为0mysql> CHANGE MASTER TO MASTER_DELAY = 0;#开启主从mysql> start slave;

思考问题：

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

处理的思路：

1)停止SQL线程

mysql> stop slave sql_thread;

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

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

模拟故障处：

1)关闭延时

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

2)模拟数据

mysql -S /data/3307/mysql.socksource  /root/world.sqluse world;create table c1 select * from city;create table c2 select * from city;

3)开启从库延时5分钟

mysql -S /data/3308/mysql.sockshow slave status Gmysql>stop slave;mysql>CHANGE MASTER TO MASTER_DELAY = 300;mysql>start slave;mysql -S /data/3307/mysql.sockuse world;create table c3 select * from city;create table c4 select * from city

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

mysql -S /data/3307/mysql.sockdrop database world;

5)从库，关闭SQL线程

mysql -S /data/3308/mysql.sockstop slave sql_thread;

6)截取relay-log

起点：cd /data/3308/data/cat relay-log.info./db01-relay-bin.000002283终点：mysql -S /data/3308/mysql.sockshow 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 

恢复relay.sql

1)取消从库身份

mysql> stop slave;mysql> reset slave all;

2)恢复数据

mysql> set sql_log_bin=0;mysql> source /tmp/relay.sqlmysql> use worldmysql> show tables;

六.半同步复制

从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=1rpl_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和test2mysql> 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)注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没

七.过滤复制

主库：

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

• 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#关闭MySQLmysqladmin -S /data/3307/mysql.sock  shutdown#启动MySQLmysqld_safe --defaults-file=/data/3307/my.cnf &

测试复制过滤：

第一次测试：

1)主库：

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

2)从库查看结果：

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

第二次测试：

1)主库：

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

2)从库查看结果：

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