mysql的主从复制

一、mysql主从复制

1.主从复制主库的操作

1.主库配置server_id
2.主库开启binlog日志
3.授权从库连接的用户
4.主库需要确定binlog的名字和位置点
5.需要知道主库的信息：ip ，port，user，密码

2.主从复制从库的操作

1.配置server_id，与主库不同
2.同步主库的数据
3.从库配置主库信息（change matser to）
4.从库开启IO线程和SQL线程

3.主从复制的原理

1）画图讲原理

2）文字描述原理

1.主库配置server_id和binlog
2.主库授权从库连接的用户
3.主库查看位置点个binlog名字
4.从库配置serevr_id
5.通过change master to语句告诉从库主库的信息：host,user,password,port,binlog_file,binlog_pos
6.从库开启IO线程和SQL线程（start slave）
7.从库连接主库后，会去询问主库的dump线程，是否有比从库master.info里面记录的更新的binlog名字或位置点
8.主库接收IO线程的询问后，dump线程会去查询binlog，如果有新数据就会将新数据的binlog传给IO线程
9.IO线程拿到新数据的binlog以后，会将内容存储到TCP\IP缓存
10.TCP\IP缓存收到数据，会返回给IO线程一个ACK
11.IO线程会更新master.info新的位置点或binlog名，然后继续去主库获取新数据
12.TCP\IP会将新数据的binlog存到中继日志relay_log
13.SQL线程会去读取中继日志的内容，并且去relay-log.info对比，如果有新数据就执行新数据的内容
14.SQL线程执行完新数据，将新数据执行完的位置点更新到relay-log.info

4.主从复制涉及到的文件和信息

1）主库

1.主库的binlog：记录主库发变化的过程
2.dump线程：对比binlog是否更新，并将新数据的binlog发送给主库

2）从库

1.IO线程：连接主库，接收主库发送过来新数据的binlog日志
2.SQL线程：执行主库传过来的新数据的binlog日志
3.relay-log（中继日志）：存储所有主库传过来的binlog日志
4.master.info：记录上一次请求到主库的binlog名字和位置点
5.relay-log.info：记录上一次执行relay-log的位置点，下一次从该点执行后面的内容

二、主从复制搭建

1.主库操作

1）配置主库配置文件

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

[root@db01 ~]# systemctl restart mysql

2）主库授权一个从库连接的用户

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

#grant replication slave 是一个全局授权，不能指定单个库进行同步，可以使用过滤复制实现单库同步

3）查看主库binlog信息

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      327 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.从库操作

1）配置从库配置文件

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

[root@db02 ~]# systemctl restart mysql

2）配置主从同步

#从库执行
mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=327,
    -> master_port=3306;

3）开启IO线程和SQL线程

#开启IO线程与SQL线程
mysql> start slave;

#查看状态
mysql> show slave status\G

3.数据库运行中添加主从

1.准备纯净的从库环境
2.主库进行全备
    #不打点备份
    [root@db01 ~]# mysqldump -uroot -p123 -A > /backup/no_full.sql
    #打点备份
    [root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /backup/yes_full.sql

3.将数据传输到新的数据库
    [root@db01 ~]# scp /backup/*.sql 172.16.1.53:/tmp/
4.模拟生产库数据写入
    mysql> create database zhucong;
    mysql> use zhucong 
    mysql> create table test(id int);
    mysql> insert into test values(1);
5.新库导入主库的全备数据
    mysql < /tmp/yes_full.sql
6.查看同步的位置点
    [root@db03 ~]# head -50 /tmp/yes_full.sql | grep 'MASTER_LOG_POS'
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=834;
7.从库配置主从
    mysql> change master to
    -> master_host='172.16.1.51',
    -> master_user='rep',
    -> master_password='123',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=834,
    -> master_port=3306;
8.开启IO线程和SQL线程
    start slave;
9.查看主从状态
    show slave status;
10.查看数据

三、主从复制故障

1.IO线程故障

#如果IO线程为no

1.检测网络
    [root@db02 ~]# ping 172.16.1.51
2.检测端口
    [root@db02 ~]# telnet 172.16.1.51 3306
    [root@db02 ~]# tcping 172.16.1.51 3306
    172.16.1.51 port 3306 open.
3.查看防火墙
4.测试主从用户能否连接
    grant replication slave on backup.* to test@'172.16.1.5%' identified by '123';
    [root@db02 ~]# mysql -urep -p123 -h172.16.1.51
5.反向解析
    [root@db01 ~]# mysql -uroot -p123 -h172.16.1.51
    ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)
    skip_name_resolve

2.SQL线程故障

#可能出现的情况
1.主库有的数据，从库没有
    主库：有a库
    从库：没有a库
    对主库的a库操作时，从库会出现问题
2.主库没有的数据，从库有
    从库：有b库
    主库：刚打算创建b库
    主库创建从库已有的数据库，从库会出现错误
3.主库与从库数据库结构不一致

#处理办法一：
    #停止主从
    mysql> stop slave;
    #跳过一个错误，执行下一个命令
    mysql> set global sql_slave_skip_counter=1;
    #启动主从
    mysql> start slave;

#处理方法二：
    1.先清空从库信息    reset slave all;
    2.同步主库所有数据
    3.重新配置主从

3.UUID相同时会报错

#由于UUID相同导致的主从失败
#报错：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;  these UUIDs must be different for replication to work.

#解决方法：
    1.删除UUID，重启数据库
    2.修改UUID，重启数据库

四、延时从库

1.已经有主从的情况

1.停止从库
    mysql> stop slave;
2.设置延时180秒
    mysql> change master to master_delay=180;
3.开启主从
    mysql> start slave;
4.查看主从状态
    mysql> show slave status\G
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        SQL_Delay: 180                    #延时时间
        SQL_Remaining_Delay: 58            #执行语句倒计时，如果主库没有在执行语句，则为NULL
5.主库增删改数据，从库查看

2.如果没有主从复制

1.配置主库从库server_id，主库开启binlog
2.保证主库从库数据一致
3.执行change语句
    change master to
    master_host='172.16.1.51',
    master_user='rep',
    master_password='123',
    master_log_file='mysql-bin.000001',
    master_log_pos=120,
    master_delay=180;

3.延时从库停止

1.停止主从
    mysql> stop salve;
2.配置延时为0
    mysql> change master to master_delay=0;
3.启动主从
    mysql> start slave;

思考：延时从库倒在哪里延时？

延时从库是在SQL线程延时的，主库执行完操作，binlog内容已经被IO线程拿个relay-log中，SQL线程会延时180秒执行

4.使用延时从库恢复数据

1）场景

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

2）环境准备

1.进行每日的全备
    [root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /backup/28full.sql

2.延时从库设置为3600秒
    mysql> stop slave;                                                                               
    mysql> change master to master_delay=3600;
    mysql> start slave;

3.主库插入新数据
    mysql> create database yanshi1;
    mysql> use yanshi1;
    mysql> create table yanshi1(id int);
    mysql> insert into yanshi1 values(1);

3）模拟删除数据库

mysql> drop database linux;
Query OK, 2 rows affected (0.01 sec)

4）使用延时从库恢复数据

1.停止sql线程
    mysql> stop slave sql_thread;
2.查看状态
    mysql> show slave status\G
        Slave_IO_Running: Yes
        Slave_SQL_Running: No
3.备份从库数据
    [root@db02 data]# mysqldump -uroot -p123 -B linux test xiaomage > /tmp/full.sql
4.截取relay-log
    1）确认起始位置点
        [root@db02 data]# cat relay-log.info 
        ./db02-relay-bin.000002
        283
    2）结束位置点
        [root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv db02-relay-bin.000002
        drop语句之前，commit语句之后
        # at 827
    3）截取数据
        [root@db02 data]# mysqlbinlog --start-position=283 --stop-position=827 db02-relay-bin.000002 > /tmp/yanshi.sql

5.将备份的sql文件传送到主库
    [root@db02 data]# scp /tmp/full.sql 172.16.1.51:/tmp/
    [root@db02 data]# scp /tmp/yanshi.sql 172.16.1.51:/tmp/
6.恢复数据到主库
    mysql> source /tmp/full.sql;
    mysql> source /tmp/yanshi.sql;
7.开启SQL线程
    mysql> start slave sql_thread;

#注意：如果使用的是延时从库，恢复数据时千万不要停止binlog，如果没有延时从库，使用binlog恢复数据时，可以停止binlog

五、半同步复制

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

#半同步其实就是mysql自带的一个插件
    1.效率低
    2.影响主库性能
    3.半同步复制有一个超时时间，如果超过这个时间，会恢复异步复制

1.半同步复制配置

1）主库

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

2）从库

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

3）测试半同步复制

#创建两个数据库，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.确认两台从库主从状态

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

show slave status;

2.过滤复制的两种方式

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      221 |   白名单      |   黑名单         |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

1）白名单

白名单：只执行白名单中列出的库或者表的中继日志
#参数：
#配置白名单，只同步哪个库
replicate-do-db=test
#配置白名单，只同步哪个库下的哪个表
replicate-do-table=test.t1
#配置白名单，只同步哪个库下面t开头的表
replicate-wild-do-table=test.t*

2）黑名单

黑名单：不执行黑名单中列出的库或者表的中继日志
#参数：
#配置黑名单，不同步哪个库
replicate-ignore-db=test
#配置黑名单，不同步哪个库下的哪个表
replicate-ignore-table=test.t1
#配置黑名单，不同步哪个库下面t开头的表
replicate-wild-ignore-table=test.t*

3.配置过滤复制

1）主库创建两个库

create database wzry;
create database lol;

2）第一台从库配置白名单

[root@db02 data]# vim /etc/my.cnf
[mysqld]
server_id=3
replicate-do-db=lol

[root@db02 ~]# systemctl restart mysql

#查看主从状态
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: lol

3）第二台数据库配置

[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
replicate-do-db=wzry

[root@db03 ~]# systemctl restart mysql

#查看主从状态
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: wzry

4）主库建表测试

mysql> use lol;
mysql> create table bierjiwote(id int);
Query OK, 0 rows affected (0.01 sec)

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

5）从库查看

#从库1查看：
mysql> use lol;
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| bierjiwote    |
| wangtong1     |
+---------------+
2 rows in set (0.01 sec)

mysql> use wzry;
mysql> show tables;
+----------------+
| Tables_in_wzry |
+----------------+
| dianxin2       |
+----------------+
1 row in set (0.00 sec)

#从库2查看：
mysql> use lol;
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| wangtong1     |
+---------------+
1 row in set (0.00 sec)

mysql> use wzry;
mysql> show tables;
+----------------+
| Tables_in_wzry |
+----------------+
| cikexintiao    |
| dianxin2       |
+----------------+
2 rows in set (0.00 sec)

4.扩展

#如果想配置多个白名单写法：
    [root@db03 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=3
    replicate-do-db=wzry
    replicate-do-db=lol

    #或者
    [root@db03 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=3
    replicate-do-db=wzry,lol

5.如果将白名单配置到主库

1.配置白名单
    [root@db01 data]# vim /etc/my.cnf
    [mysqld]
    server_id=1
    log_bin=mysql-bin
    binlog-do-db=lol
2.主库查看白名单
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000004 |      120 | lol          |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

3.在主库的wzry和lol库创建表
4.在从库查看，只有配置白名单的库进行了同步

6.过滤复制总结

#从库配置时
1.配置白名单：IO线程将数据从主库的binlog拿到了从库的relay-log，但是SQL线程只执行白名单设置库或者表的语句
2.配置黑名单：IO线程将数据从主库的binlog拿到了从库的relay-log，但是SQL线程不执行黑名单设置库或者表的语句

#主库配置时：
1.配置白名单：主库只记录白名单设置的库或表相关SQL到binlog
2.配置黑名单：主库不记录黑名单设置的库或表相关SQL到binlog