数据库主从

主从简介

在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。

想几个问题：

• 用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？
• 业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？

主从作用

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免影响业务

主从形式

• 一主一从
• 主主复制
• 一主多从—扩展系统读取的性能，因为读是在从库读取的
• 多主一从—5.7开始支持
• 联级复制

主从复制原理

主从复制步骤：

• 主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程

• 从库生成两个线程，一个I/O线程，一个SQL线程

  • I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
  • SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

主从复制配置

主从复制配置步骤：

1. 确保从数据库与主数据库里的数据一样
2. 在主数据库里创建一个同步账号授权给从数据库使用
3. 配置主数据库（修改配置文件）
4. 配置从数据库（修改配置文件）

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

mysql安装

分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，若有疑问请参考《mysql基础》与《mysql进阶》两篇文章。

mysql主从配置

确保从数据库与主数据库里的数据一样

为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

先查看主库有哪些数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| durui              |
| mysql              |
| performance_schema |
| qq                 |
+--------------------+
5 rows in set (0.00 sec)

再看从库有哪些数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

全备主库
全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql>  flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

备份主库并将备份文件传送到从库
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-$(date '+%y%d%m%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt/
all-220108182029.sql  data
[root@localhost ~]# scp /opt/all-220108182029.sql root@192.168.91.136:/opt/
root@192.168.91.136's password: 
all-220108182029.sql  

解除主库的锁表状态，直接退出交互式界面即可
mysql> exit
Bye

在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@localhost ~]# mysql -uroot -p123456 < /opt/all-220108182029.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| durui              |
| mysql              |
| performance_schema |
| qq                 |
+--------------------+
5 rows in set (0.00 sec)

在主数据库里创建一个同步账号授权给从数据库使用

mysql> grant replication slave on  *.*  to 'du'@'192.168.91.136' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| du            | 192.168.91.134 |
| du            | localhost      |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
5 rows in set (0.00 sec)

配置主数据库

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]                    在[mysqld]这段的后面加上如下内容
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin            启用binlog日志
server-id=20                 数据库服务器唯一标识符，主库的server-id值必须比从库的小


[root@localhost ~]# systemctl restart mysqld.service 
[root@localhost ~]# ss -anltup | grep mysql
tcp   LISTEN 0      80                 *:3306             *:*    users:(("mysqld",pid=126944,fd=25)) 


查看主库的状态
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)

配置从数据库

[root@localhost ~]# vim /etc/my.cnf
//添加如下内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=30    //设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log=mysql-relay-bin       //启用中继日志relay-log

[root@localhost ~]# systemctl restart mysql.service  重启从库的mysql服务
[root@localhost ~]# ss -anltup | grep mysql
tcp   LISTEN 0      80                 *:3306             *:*    users:(("mysqld",pid=19996,fd=30))

配置并启动主从复制
mysql> change master to \
    -> master_host='192.168.91.134',
    -> master_user='du',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.91.134
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 519
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes    此处必须为Yes
            Slave_SQL_Running: Yes    此处必须为Yes

测试验证

在主服务器上创建数据库qwe

mysql> create database qwe;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| durui              |
| mysql              |
| performance_schema |
| qq                 |
| qwe                |
+--------------------+
6 rows in set (0.00 sec)

在从数据库中查看数据是否同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| durui              |
| mysql              |
| performance_schema |
| qq                 |
| qwe                |
+--------------------+
6 rows in set (0.00 sec)

GTID主从

GTID概念介绍

GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。mysql主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。

GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。

GTID和Binlog的关系

• GTID在binlog中的结构

• GTID event 结构

  

• Previous_gtid_log_event
  Previous_gtid_log_event 在每个binlog 头部都会有每次binlog rotate的时候存储在binlog头部Previous-GTIDs在binlog中只会存储在这台机器上执行过的所有binlog，不包括手动设置gtid_purged值。换句话说，如果你手动set global gtid_purged=xx； 那么xx是不会记录在Previous_gtid_log_event中的。

• GTID和Binlog之间的关系是怎么对应的呢? 如何才能找到GTID=? 对应的binlog文件呢？
  假设有4个binlog: bin.001,bin.002,bin.003,bin.004
  bin.001 : Previous-GTIDs=empty; binlog_event有: 1-40
  bin.002 : Previous-GTIDs=1-40; binlog_event有: 41-80
  bin.003 : Previous-GTIDs=1-80; binlog_event有: 81-120
  bin.004 : Previous-GTIDs=1-120; binlog_event有: 121-160
  假设现在我们要找GTID=$A，那么MySQL的扫描顺序为:

• 从最后一个binlog开始扫描（即: bin.004）

• bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中

• bin.004的Previous-GTIDs=1-120，如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤，直到找到为止.

GTID相关参数

====== 开启GTID的必备条件 ======

gtid_mode=on    (必选)
enforce-gtid-consistency=1  （必选）
log_bin=mysql-bin           （可选）    #高可用切换，最好开启该功能
log-slave-updates=1     （可选）       #高可用切换，最好打开该功能

GTID工作原理

从服务器连接到主服务器之后，把自己执行过的GTID (Executed_Gtid_Set: 即已经执行的事务编码) 、获取到的GTID (Retrieved_Gtid_Set: 即从库已经接收到主库的事务编号) 发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主， 先change到同步最成功的那台从服务器， 等把GTID全部补全了，就可以把它提升为主了。

GTID是MySQL 5.6的新特性，可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找点儿，而无需像之前那样通过File_name和File_position找点儿了。

简而言之，GTID的工作流程为：

• master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
• slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
• sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
• 如果有记录，说明该GTID的事务已经执行，slave会忽略。
• 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
• 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

GTID主从配置

主从配置操作

主库配置。vi /etc/my.cnf，添加以下配置，重启mysql。

[root@localhost ~]#vim /etc/my.cnf
log-bin=mysql_bin
server-id=10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on

从库配置。vi /etc/my.cnf, 添加以下配置，重启mysql。

[root@localhost ~]#vim /etc/my.cnf
server-id=30
relay-log=myrelay
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE

主库授权复制用户。

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';

从库设置要同步的主库信息，并开启同步。

mysql> change master to master_host='192.168.91.136', \
    -> master_port=3306,master_user='slave',master_password='123456', \
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.91.136
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 438
               Relay_Log_File: myrelay.000003
                Relay_Log_Pos: 651
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

配置完成，验证

主库上创建名字为qqq的数据库
mysql> create database qqq;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qqq                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

从库上查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qqq                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mariadb数据库主从配置

主从复制原理

主库：二进制日志（保存的是主库的所有操作I/O线程）
从库：中继日志（是由从库I/O线程把主库日志里面的所有信息写入中继日志，SQL线程把中继日志里面倒过来的内容全部执行）

主从复制流程

1.安装两台虚拟机，操作系统以及版本必须一致

2.关闭两台主机防火墙和selinux，顺便修改主机名 master slave

3.安装数据库

yum -y install mariadb*

4.配置主库配置文件/etc/my.cnf.d/mariadb-server.cnf

log_bin=mysql-bin                 定义文件的前缀名
binlog_ignore_db=mysql            开启binlog日志
server_id=200                     指定服务id (主库id要小于从库id)

5.重启服务

systemctl restart mariadb.service

6.配置从库配置文件/etc/my.cnf.d/mariadb-server.cnf

log_bin=mysql-bin                 定义文件的前缀名
binlog_ignore_db=mysql            开启binlog日志
server_id=201                     指定服务id（主库id要小于从库id)

7.重启服务

systemctl restart mariadb.service

8.登录主数据库进行创建用户
并且给用户授权

grant replication slave on  *.*  to  'slave'@'%'  identified by  '1';

(第一个slave代表的是从库，）（%代表所有的用户），执行flush privileges刷新权限

9.登录从库进行指定主库的IP地址以及能够访问的用户名和密码

change master to master_host='ip地址',master_user='slave',master_password='密码';

(master_host指定的是主库的IP地址，后面的用户和密码是主库授权和创建的)

10.开启主从复制

start slave；

11.查看从库状态,验证主从是否配置成功（查看状态的I/o和SQL线程是否都为YES），也可以去主库创建一个数据库然后去从库验证

show slave status\G;

可能会发生的问题

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.
一般是事务回滚造成的：
解决办法：

1. mysql> stop slave;
2. mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
3. mysql> start slave;