MySQL主从

主库操作

1.主库配置server_id 2.主库开启binlog 3.主库授权从库连接的用户 4.查看binlog信息 5.导出所有数据

从库操作

1.从库配置server_id（跟主库不一致） 2.确认主库授权的用户可以连接主库 3.同步主库数据 4.配置主库信息（change master to） 5.开启slave

主从复制原理

2）文字描述 1.主库配置server_id和开启binlog 2.主库授权从库连接的用户 3.主库查看binlog信息，与服务器信息 4.从库配置跟主库不一致server_id 5.配置主从，通过change master to高速从库主库的信息：ip、用户、密码、端口、binlog位置点、binlog名字 6.从库开启IO线程和sql线程 7.从库连接主库以后，IO线程会向主库的dump线程发起询问，询问是否有新数据 8.dump线程被询问，去查找新数据，并将新数据返回给IO线程 9.IO线程拿到数据先写入TCP缓存 10.TCP缓存将数据写入中继日志，并返回给IO线程一个ACK 11.IO线程收到ACK会记录当前位置点到master.info 12.sql线程会读取relay-log，执行从主库获取的sql语句 13.执行完以后将执行到的位置点，记录到relay-log.info 主从中涉及到的文件或者线程 1）主库 1.binlog：主库执行的sql语句 2.dump线程：对比binlog是否更新，获取新的binlog 2）从库 1.IO线程：连接主库，询问新数据，获取新数据 2.SQL线程：执行从主库哪来的sql语句 3.relay-log：中继日志，记录从主库拿过来的binlog 4.master.info：记录主库binlog信息，会随着同步进行更新 5.relay-log.info：记录sql线程执行到了那里，下次从哪里开始执行

主从复制的搭建

主库操作

1）配置 [root@db03 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=/service/mysql/data/mysql-bin [root@db03 ~]# /etc/init.d/mysqld start 2）授权一个用户 mysql> grant replication slave on . to rep@'172.16.1.%' identified by '123'; Query OK, 0 rows affected (0.03 sec) 3）查看binlog信息 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 326 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 4）导出所有数据 mysqldump -uroot -p -A --master-data=2 --single-transaction > /tmp/full.sql

scp /tmp/full.sql 172.16.1.52:/tmp/

从库操作

1）配置 [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 [root@db02 ~]# /etc/init.d/mysqld start 2）验证主库用户 [root@db02 ~]# mysql -urep -p -h172.16.1.53 3）同步数据 [root@db02 ~]# mysql -uroot -p123 < /tmp/full.sql 4）配置主从 mysql> change master to -> master_host='172.16.1.53', -> master_user='rep', -> master_password='123', -> master_log_file='mysql-bin.000003', -> master_log_pos=326; Query OK, 0 rows affected, 2 warnings (0.02 sec) 5）开启线程 mysql> start slave; Query OK, 0 rows affected (0.04 sec) 6）查看主从 mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes

主从数据库出错

1）IO线程出错 mysql> show slave status\G Slave_IO_Running: No Slave_SQL_Running: Yes

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

排查思路

1.网络 [root@db02 ~]# ping 172.16.1.53 2.端口 [root@db02 ~]# telnet 172.16.1.53 3306 3.防火墙 4.主从授权的用户错误 5.反向解析 skip-name-resolve 6.UUID或server_id相同 2）SQL线程出错 mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No

原因：

1.主库有的数据，从库没有 2.从库有的数据，主库没有

处理方式一：自欺欺人

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

处理方式二：掩耳盗铃

1.编辑配置文件 [root@db01 ~]# vim /etc/my.cnf

在[mysqld]标签下添加以下参数

slave-skip-errors=1032,1062,1007

处理方式三：正解

重新同步数据，重新做主从

延时复制

延时从库只做备份，不提供任何对外服务

1.配置延时复制（已经有主从） 1.停止主从 mysql> stop slave; Query OK, 0 rows affected (0.03 sec)

2.配置延时时间 mysql> change master to master_delay=180; Query OK, 0 rows affected (0.01 sec)

3.开启主从 mysql> start slave; Query OK, 0 rows affected (0.00 sec) 2.配置延时复制（没有主从） 1.搭建出一台mysql 2.配置主从 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=424, -> master_delay=180; Query OK, 0 rows affected, 2 warnings (0.02 sec) 3.开启线程 mysql> start slave; Query OK, 0 rows affected (0.01 sec) 3.关闭延时从库 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to master_delay=0; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) 4.注意：延时从库恢复数据时不要关闭主库的binlog，实际上从库还是会执行主库执行错的语句，只不过又执行了重建语句

实例

思考问题：

总数据量级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.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 恢复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;

半同步复制

半同步复制概念从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.性能差，影响主库效率 2.半同步复制，有一个超时时间，超过这个时间恢复主从复制

配置半同步

1）主库操作

登录数据库

[root@db01 ~]# mysql -uroot -p123

查看是否有动态支持

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

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

过滤复制

过滤复制的方式 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 305 | 白名单 | 黑名单 | | +------------------+----------+--------------+------------------+-------------------+ 1）白名单

从库

replicate-do-db=test replicate-do-table=test.t1 replicate-wild-do-table=test.t*

主库

binlog-do-db=test binlog-do-table=test.t1 binlog-wild-do-table=test.t* 2）黑名单

从库

replicate-ignore-db=test replicate-ignore-table=test.t1 replicate-wild-ignore-table=test.t*

主库

binlog-ignore-db=test binlog-ignore-table=test.t1 binlog-wild-ignore-table=test.t*

配置过滤复制

1）主库创建两个库 mysql> create database wzry; Query OK, 1 row affected (1.00 sec) mysql> create database lol; Query OK, 1 row affected (0.00 sec) 2）第一台从库配置 [root@db02 data]# vim /etc/my.cnf [mysqld] server_id=2 replicate-do-db=wzry

[root@db02 data]# systemctl restart mysqld

查看主从状态

mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: wzry 3）配置第二台从库 [root@db03 ~]# vim /etc/my.cnf [mysqld] server_id=2 replicate-do-db=lol

[root@db03 ~]# systemctl restart mysqld

查看主从状态

mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: lol 4）验证过滤复制

1.主库操作

mysql> use wzry Database changed mysql> create table cikexintiao(id int); Query OK, 0 rows affected (0.26 sec) mysql> use lol Database changed mysql> create table fuleierzhuode(id int); Query OK, 0 rows affected (0.01 sec)

第一台从库查看

mysql> use wzry Database changed mysql> show tables; +----------------+ | Tables_in_wzry | +----------------+ | cikexintiao | +----------------+ 1 row in set (0.00 sec) mysql> use lol Database changed mysql> show tables; Empty set (0.00 sec)

第二台从库查看

mysql> use wzry Database changed mysql> show tables; Empty set (0.00 sec) mysql> use lol Database changed mysql> show tables; +---------------+ | Tables_in_lol | +---------------+ | fuleierzhuode | +---------------+ 1 row in set (0.00 sec)

配置过滤多个库

1）方法一： [root@db02 data]# vim /etc/my.cnf [mysqld] server_id=2 replicate-do-db=wzry,lol 2）方法二： [root@db02 data]# vim /etc/my.cnf [mysqld] server_id=2 replicate-do-db=wzry replicate-do-db=lol

过滤复制配置在主库

1.配置 [root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=/usr/local/mysql/data/mysql-bin binlog-do-db=wzry

3.在主库的wzry库和lol库添加数据

4.从库查看数据，只能看到wzry库的数据

过滤复制总结

配置在从库时

1.配置白名单：IO线程将主库的数据拿到了relay-log，但是sql线程只执行白名单配置的数据库相关语句 1.配置黑名单：IO线程将主库的数据拿到了relay-log，但是sql线程只不执行黑名单配置的数据库相关语句

配置在主库时

1.配置白名单：binlog只记录白名单相关的sql语句 2.配置黑名单：binlog只不记录黑名单相关的sql语句

基于GTID的主从复制

什么是GTID 1.全局事务标识符 2.组成：UUID + TID
f03a53e0-cd46-11ea-a2c4-000c292c767e:1 GTID主从复制的优点 1.GTID同步时开启多个SQL线程，每一个库同步时开启一个线程 2.binlog在rows模式下，binlog内容比寻常的主从更加简洁 3.GTID主从复制会记录主从信息，不需要手动配置binlog和位置点 GTID主从复制的缺点 1.备份时更加麻烦，需要额外加一个参数 --set-gtid=on 2.主从复制出现错误，没有办法跳过错误

搭建GTID主从复制

1）配置三台数据库

配置第一台主库

[root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=/usr/local/mysql/data/mysql-bin

配置第一台从库

[root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2

配置第二台从库

主库配置

[root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=/usr/local/mysql/data/mysql-bin gtid_mode=on enforce_gtid_consistency log-slave-updates

从库1的配置

[root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 log_bin=/usr/local/mysql/data/mysql-bin gtid_mode=on enforce_gtid_consistency log-slave-updates

从库2的配置

[root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=3 log_bin=/usr/local/mysql/data/mysql-bin gtid_mode=on enforce_gtid_consistency log-slave-updates 4）扩展

配置log-slave-updates参数的场景

1.基于GTID的主从复制 2.双主架构+keepalived 3.级联复制 4.MHA 5）主库创建用户 mysql> grant replication slave on . to rep@'172.16.1.5%' identified by '123'; 6）主库数据同步到从库 mysqldump -uroot -p -R --triggers --master-data=2 --single-transaction -A > /tmp/full.sql scp mysql < full.sql 7）从库配置主从 mysql> change master to -> master_host='172.16.1.51', -> master_user='rep', -> master_password='123', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.03 sec)