[Linux]Mysql之主从同步

AB复制

一、主从复制概述

        主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

主从复制的作用

1.做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。。
2.读写分离，使数据库能支撑更大的并发。1--在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）2--在从服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）

二、主从复制原理---通过binlog日志做主从同步的原理

原理：
实现整个主从复制，需要由slave服务器上的IO进程和Sql进程共同完成.
要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己slave端完全顺序的执行日志中所记录的各种操作。
===========================================
1. 主服务器接收到客户端的读写请求后把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。
2. 备库I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 备库SQL线程读取中继日志中的事件进行本地回放，将其重放到备库数据库之上。
详细步骤： 
1.数据变更：主库数据发生变更(DDL DML DCL)，写入本地Bin Log文件。
2.从库请求：从库IO线程发起binlog dump线程，请求主库的binlog数据。
3.数据传输：主库I/O线程推送Bin Log文件到从库。
4.写入中继日志：从库IO线程将binlog数据写入中继日志。
5.数据应用：从库SQL线程读取中继日志事件，在本地数据库上重新执行一遍SQL语句
============
master            负责写 -----A
slave             负责读取复制  -----B
I/O               负责通信读取binlog日志并写入从库的中继日志
SQL               负责写数据
relay log         中继日志，存储从主服务器接收到的binlog事件

binlog dump thread的主要职责包括：

1. 读取主服务器上的binlog。
2. 将读取到的binlog事件流式传输到从属服务器。
3. 监听从属服务器是否请求了新的binlog事件或断开了连接。

 三、主从复制binlog日志方式

192.168.140.128    mysql-master
192.168.140.129    mysql-slave
准备两台机器，关闭防火墙和selinux。---两台机器环境必须一致。时间也得一致
两台机器配置hosts解析
两台机器安装mysql
# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
略...
[root@mysql-master ~]# systemctl start mysqld
[root@mysql-master ~]# systemctl enable mysqld

（1）配置主服务
在主服务器上，启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。   

[root@master ~]# vim /etc/my.cnf      #编辑主服务器的配置文件 my.cnf  
[mysqld]                              
log-bin=/var/log/mysql/mysql-bin      #启用二进制日志功能
server-id=1
[root@master ~]# mkdir /var/log/mysql  #创建日志目录
[root@master ~]# chown mysql.mysql /var/log/mysql #并赋予权限
[root@master ~]# systemctl restart mysqld   #重启服务  

（2）创建用于主从同步的用户

在主数据库上创建用于同步的用户：在主数据库上创建一个用于同步的用户，并赋予复制权限。

[root@master ~]# mysql -uroot -p'Yunjisuan666@'  #登录mysql
创建主从同步的用户：
mysql> GRANT REPLICATION SLAVE ON *.*  TO  'repl'@'%'  identified by 'Yunjisuan666@';
mysql> flush privileges;repl用户名可以自定义

（3）获取主服务器上的复制相关信息

包括主服务器上的二进制日志文件（File），位置信息（Position） binlog 日志格式等。使用这些信息，可以在从服务器上进行配置，设置从哪个二进制日志文件的哪个位置开始进行数据复制和同步。从而实现主服务器与从服务器的数据一致性。

mysql> show master status \G

（4）配置从数据库

配置从数据库连接到主数据库，启用主从同步功能。

[root@slave ~]# vim /etc/my.cnf    #设置为mysql的从服务器，开启binlog
[mysqld]
server-id=2
log-bin=mysql-bin
[root@slave ~]# systemctl restart mysqld   #重启数据库，使配置修改生效
登录mysql
[root@slave ~]# mysql -uroot -p'Yunjisuan666@'
mysql> \e                 #在从数据库上连接到主数据库，并指定要复制的日志文件和位置。
CHANGE MASTER TO 
MASTER_HOST='mysql-master',
MASTER_USER='repl',
MASTER_PASSWORD='Yunjisuan666@',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=849;      -> ;                 
mysql> start slave;

 （5）检查主从同步状态

mysql> show slave status\G 

参数解释：
CHANGE MASTER TO
MASTER_HOST='192.168.140.128',          #主服务器ip
MASTER_USER='replication',              #主服务器用户
MASTER_PASSWORD='password',             #用户密码
MASTER_PORT=3306,                       #端口
MASTER_LOG_FILE='master2-bin.001',      #binlog日志文件名称
MASTER_LOG_POS=4;                       #日志位置，最后的;可以不加

测试

再主服务器上
mysql> create database testdb;   #创建一个库
Query OK, 1 row affected (0.10 sec)
mysql> \q在从服务器上
mysql> show databases;           #查看是否存在testdb

四、M-S 架构GTID 基于事务ID复制

1、什么是GTID？

全局事务标识：global transaction identifiers 是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。

2、GTID工作原理

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

3、部署主从复制

1、架构：

一主一从

准备环境两台机器，关闭防火墙和selinux。---两台机器环境必须一致。时间也得一致
192.168.140.128  mysql-master
192.168.140.129  mysql-slave1# setenforce 0                #临时关闭selinux
# vim /etc/sysconfig/selinux  #改为SELINUX=disabled   永久关闭selinux
# systemctl stop firewalld && systemctl disable firewalld  #关闭防火墙
# ntpdate cn.pool.ntp.org     #同步为中国时间

两台机器安装mysql5.7
[root@mysql-master ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
安装略...   #启动mysql服务并设置为开机自启
[root@mysql-master ~]# systemctl start mysqld
[root@mysql-master ~]# systemctl enable mysqld
[root@mysql-master ~]# netstat -lntp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      11669/mysqld
[root@mysql-slave ~]# netstat -lntp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      11804/mysqld
配置并修改密码
略....

master操作：
[root@mysql-master ~]# vim /etc/my.cnf   #在[mysqld]下添加如下内容
server-id=1     #定义server id master必写 
log-bin = /var/log/mysql-bin/mylog #开启binlog日志，master服务器必写#也可以写login-bin=mylog，默认放在/var/lib/mysql下
gtid_mode = ON    #开启gtid
enforce_gtid_consistency=1   #强制gtid
[root@mysql-master ~]# mkdir /var/log/mysql-bin
[root@mysql-master ~]# chown mysql.mysql /var/log/mysql-bin/
[root@mysql-master ~]# systemctl restart mysqld   #重启
主服务器创建账户：
mysql> grant replication slave,reload,super on *.*  to 'slave'@'%' identified by 'Yunjisuan666@';
#注:生产环境中密码采用高级别的密码，实际生产环境中将'%'换成slave的ip;用户名slave可以自定义
mysql> flush privileges;
​
注意:如果不成功删除以前的binlog日志
replication slave：拥有此权限可以查看从服务器，从主服务器读取二进制日志。
super权限：允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句
reload权限：必须拥有reload权限，才可以执行flush  [tables | logs | privileges]

slave操作：
[root@mysql-slave ~]# vim /etc/my.cnf  #添加如下配置
log-bin=/var/log/mysql-bin/mylog  #可以不开
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@mysql-slave ~]# mkdir /var/log/mysql-bin
[root@mysql-slave ~]# chown mysql.mysql /var/log/mysql-bin 
[root@mysql-slave ~]# systemctl restart mysqld

在MySQL中，二进制日志文件通常存储在/var/log/mysql-bin目录下。为了使MySQL能够访问并写入这个目录，需要将其所有者和所属组设置为MySQL（一般为mysql.mysql）。 

[root@mysql-slave ~]# mysql -uroot -p'Yunjisuan666@'   
mysql> \e
change master to
master_host='192.168.140.128',    
master_user='slave',              
master_password='Yunjisuan666@',
master_auto_position=1;           -> ;
Query OK, 0 rows affected, 2 warnings (0.02 sec)change master to    是用于配置主从复制的命令，
master_host         指定主数据库的IP地址，
master_user         指定用于与主服务器进行身份验证的用户名，
master_password     指定用于与主服务器进行身份验证的密码，
master_auto_position启用自动复制功能，指定复制的起始位置点，默认为1表示从最开始进行复制。

验证主从同步是否实现 

mysql> start slave;   #启动slave角色
Query OK, 0 rows affected (0.00 sec)
​
mysql> show slave status\G  #查看slave状态，验证sql和IO是不是yes。

SHOW SLAVE STATUS命令用于查看从服务器的主从复制状态信息。添加"\G"选项将结果以可读的方式显示，每个状态信息以一行显示。当执行SHOW SLAVE STATUS \G命令时，将返回一个结果集，其中包含了主从复制的相关信息，包括：Slave_IO_State: 表示从服务器当前的I/O线程状态。如果值为Waiting for master to send event说明I/O线程正常工作。
Slave_IO_Running: 表示从服务器的I/O线程是否正在运行。如果值为Yes，表示I/O线程正常工作。
Slave_SQL_Running: 表示从服务器的SQL线程是否正在运行。如果值为Yes，表示SQL线程正常工作。
Master_Log_File: 表示当前从服务器正在读取的二进制日志文件。
Read_Master_Log_Pos: 表示当前从服务器已经读取的二进制日志文件的位置。
Relay_Log_File: 表示当前从服务器正在写入的中继日志文件。
Relay_Log_Pos: 表示当前从服务器正在写入的中继日志文件的位置。
Seconds_Behind_Master: 表示从服务器相对于主服务器的延迟时间（以秒为单位）。如果值为0，表示从服务器与主服务器同步。

 

 测试

在slave上面查看一下有没有同步过去：

[root@mysql-slave ~]# mysql -uroot -p'qf123'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
主从同步完成。注意: 在关闭和启动mysql服务的时候按顺序先启动master。

面试题——mysql主从，master宕机，如何进行切换？

主机故障或者宕机，主从切换
​
1)在salve执行：
mysql> stop slave；
mysql> reset master；
2)查看是否只读模式：show variables like 'read_only';
只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。
或者不重启使用命令临时关闭只读，但下次重启后失效：set global read_only=off;
3)查看show slave status \G;
4)在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常

故障排错 

#### UUID一致，导致主从复制I/O线程不是yes（虚拟机克隆容易造成）
​
> 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
​
致命错误：由于master和slave具有相同的mysql服务器uuid，导致I/O线程不进行；这些uuid必须不同才能使复制工作。
​
问题提示主从使用了相同的server UUID，一个个的检查：
​
检查主从server_id
​
主库：
​
mysql>  show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)
​
从库：
​
mysql>  show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.01 sec)
​
server_id不一样，排除。
​
检查主从状态：
​
主库：
​
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库：
​
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      306 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
​
File一样，排除。
​
最后检查发现他们的auto.cnf中的server-uuid是一样的。。。
​
[root@localhost ~]# vim /var/lib/mysql/auto.cnf
​
[auto]
​
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f
​
修改uuid并重启服务

练习：一主双从

一主双从（gtid方式）：

在准备一台虚拟机做为slave2，关闭防火墙和selinux
192.168.140.130       #mysql-slave2
192.168.140.128       #mysql-master
slave2操作：
安装mysql5.7
[root@mysql-slave2 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
安装略...
[root@mysql-slave2 ~]# systemctl start mysqld
[root@mysql-slave2 ~]# systemctl enable mysqld开始配置slave2
[root@mysql-slave2 ~]# vim /etc/my.cnf
server-id=3    #每台机器的id不一样
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@mysql-slave2 ~]# systemctl restart mysqld
[root@mysql-slave2 ~]# mysql -uroot -p'Yunjisuan666@'
mysql> \e
change master to
master_host='192.168.140.128',
master_user='slave',
master_password='Yunjisuan666@',
master_auto_position=1;-> ;
mysql> start slave;  #将slave启动起来
mysql> show slave status\G  #查看一下状态

测试：

在master上面在创建一个库：

[root@mysql-master ~]# mysql -uroot -p'Yunjisuan666@'
mysql> create database testdb;
mysql> create table testdb.t1(id int);
mysql> insert into testdb.t1 values (1);

两台slave ， 测试是否有数据同步

注意: 在关闭和启动mysql服务的时候按顺序先启动master。

常见（数据库复制相关）面试题

一.数据库同步(复制)的机制(方式)有几种？
复制类型：
（1）异步复制（默认）
主库执行完DDL/DML操作后发送给从库，此时主库认为此次操作成功，无论从库是否完成复制。（速度快）
需要注意：主库写完binlog后从库还没有开始或完成复制，导致主从数据暂时不一致。若此时主库宕机则从库将丢失部分数据；若此时从库被升为新的主库则整个数据库将永久丢失部分数据。
（2）半同步复制
主库执行完DDL/DML操作后发送给从库，当其中一个从库完成复制并返回成功信息后，主库才认为此次操作成功。需要注意：主库只需等待一个从库的回应，且超时时间可以设置，超时后自动降级为异步复制。
（3）同步复制
主库执行完DDL/DML操作后发送给从库，当所有从库都完成复制并返回成功信息后，主库才认为此次操作成功。需要注意：主库必须等待所有从库，延迟较大。
（4）延迟复制
从库延迟一段时间再从主库复制。
​
二.复制方式：
1.基于行的复制（ROW）：主库直接记录数据并复制到从库
2.基于语句的复制（STATEMENT）：主库记录造成数据更改的语句，从库读取并执行这些语句 → 通过在主库上记录二进制日志，在从库重放日志
3.基于GTID的复制：使用全局事务ID（GTID）