mysql双机热备（主从，主主备份）以及基于gtid主备的配置

双机热备：保持两个数据库的状态自动同步。对任何一个数据库的操作都自动同步到另外一个数据库，始终保持两个数据库数据一致。

说到mysql的备份，那就必须要了解（不是理解）mysql的备份原理，借用一整很多大神都用的图：

这个备份的过程分为两部分：
主（Master）： 打开复制模式之后，主服务器Master， 会把自己的每一次改动都记录到 二进制日志 Binarylog 中。
从（Slave）： 打开复制模式之后，从服务器Slave的I/O线程， 会用master上的账号登陆到 master上， 读取master的Binarylog, 写入到自己的中继日志 Relaylog， 然后自己的sql线程会负责读取这个中继日志，并执行一遍。 如是，主服务器上的更改就同步到从服务器上了。

实验需求：
本次实验用的是mysql5.7.20版本：

[[e-mail] ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using  EditLine wrapper

mysql5.7.20的安装如果还有问题，可以借鉴：
http://blog.csdn.net/weixin_37998647/article/details/78649092

实验两台主机的IP分别是：
192.168.1.121 我们称之为A，
192.168.1.185 我们称之为B，
方便起见，直接关掉防火墙，selinux

废话已经太多了，下面直接开干吧：
1、在A上给用于备份的用户授权（我们直接用root用户）：

mysql> grant replication slave on *.* to 'root'@'192.168.1.185' identified by 'abc123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'root'@'192.168.1.185' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

（报错是因为密码太简单，该本版的mysql带有密码强壮度校验的模块，按照上面的方法设置一下就可以了。）
2. 开启主服务器的 binarylog。

[[e-mail] ~]# vim /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log-bin = mysql-bin
binlog_format = mixed
server-id = 1

read-only = 0
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-ignore-db = zabbix
auto-increment-increment = 10
auto-increment-offset = 1

在最后面加了一些参数：
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
read-only = 0
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-ignore-db = zabbix
auto-increment-increment = 10
auto-increment-offset = 1
解释一下这些参数的的意义：
server-id 必须保证每个服务器不一样。 这可能和循环同步有关。 防止进入死循环。

binlog-do-db 用来表示，只把哪些数据库的改动记录到binary日志中。 可以写上关注hello数据库。 也可以把它注释掉了。 只是展示一下。 可以写多行，表示关注多个数据库。

binlog-ignore-db 表示，需要忽略哪些数据库。我这里忽略了其他的5个数据库。

后面两个用于在 双主（多主循环）互相备份。 因为每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。 解决这个问题的办法就是让每个数据库的自增主键不连续。 我假设需要将来可能需要10台服务器做备份， 所以auto-increment-increment 设为10. 而 auto-increment-offset=1 表示这台服务器的序号。 从1开始， 不超过auto-increment-increment。
这样做之后， 我在这台服务器上插入的第一个id就是 1， 第二行的id就是 11了， 而不是2，（同理，在第二台服务器上插入的第一个id就是2， 第二行就是12， 这个后面再介绍） 这样就不会出现主键冲突了。

保存， 重启mysql。

1. 获取主服务器状态， 和同步初态。

[[e-mail] ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 775
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hello              |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
8 rows in set (0.00 sec)

如果是刚安装的就没有“hello”和zabbix“”这两个库，上面我们已经设置了备份不管zabbix库，这里只关注hello库就好了。

先锁定 hello数据库：

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.15 sec)

导出数据，我们这里只用导出hello数据库，（如果你有多个数据库作为初态的话， 需要导出所有这些数据库：）

[root@ansible ~]# mysqldump --master-data -uroot -p hello > hello.sql
Enter password:
[root@ansible ~]# ll
-rw-r--r-- 1 root root  2632 Jan 22 13:36 hello.sql

然后查看A服务器的binary日志位置：

记住这个文件名和 位置， 等会在从服务器上会用到。

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1277
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,performance_schema,sys,zabbix
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

Master上的操作已经OK了， 可以解除锁定了：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

把导出的hello.sql传到185上去。

[root@ansible ~]# scp hello.sql 192.168.1.185:
hello.sql                                  100% 2632     2.6KB/s   00:00

1. 设置从服务器 B 需要复制的数据库

打开从服务器 B 的 /etc/my.cnf 文件：

[root@localhost ~]# vim /etc/my.cnf

在配置文件最后加上：
log-bin = mysql-bin
binlog_format = mixed
server-id = 2

replicate-ignore-db = information_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = sys
replicate-ignore-db = zabbix
relay_log = mysqld-relay-bin
log-slave-updates = ON

解释一下参数：
server-id 必须保证每个服务器不一样。 这可能和循环同步有关。 防止进入死循环。

replicate-ignore-db 复制时需要排除的数据库， 我使用了，这个。 除开系统的几个数据库和zabbix之外，所有的数据库都复制。

relay_log 中继日志的名字。 前面说到了， 复制线程需要先把远程的变化拷贝到这个中继日志中， 在执行。

log-slave-updates 意思是，中继日志执行之后，这些变化是否需要计入自己的binarylog。 当你的B服务器需要作为另外一个服务器的主服务器的时候需要打开。 就是双主互相备份，或者多主循环备份。 我们这里需要， 所以打开。
保存， 重启mysql。

1. 从上导入初态。
   把刚才从A服务器上导出的 hello.sql 导入到 B的hello数据库中， 如果B现在没有hello数据库，请先创建一个， 然后再导入：
   创建数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hello              |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database hello;
Query OK, 0 rows affected (0.01 sec)

mysql> create database hello default charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hello              |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

把hello.sql 上传到B上， 然后导入：

[root@localhost ~]# mysql -uroot -p hello < hello.sql
Enter password:

开启同步, 在B服务器上执行：

mysql> change master to master_host='192.168.1.121',master_user='root',master_password='abc123',master_log_file='mysql-bin.000001',master_log_pos=1277;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

重启mysql，查看slave状态：

[[e-mail] ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[[e-mail] ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.121
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1277
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys,zabbix
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                   ……

至此，主从复制已经配置好了。现在测试一下：
在A上：

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

B上show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hello              |
| mysql              |
| performance_schema |
| sys                |
| test               |
| zabbix             |
+--------------------+

说明主从复制已经成功了

如果只要求主从复制，那么到这里已经完成了，后面的内容可以不需再看

此时暂且不要在B上操作。
上面的结果已经看到A上的操作可以自动同步到B上，但是B上的操作还不能同步到A上。
如果要做B复制到A，跟上面的操作基本一样，那下面的操作就不解释了。
主主复制，即互为主备，双机热备：
1. 在B中创建用户；

mysql> grant replication slave on *.* to 'root'@'192.168.1.121' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

1. 打开 /etc/my.cnf ， 开启B的binarylog：

[[e-mail] ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log-bin = mysql-bin
binlog_format = mixed
server-id = 2

replicate-ignore-db = information_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = sys
replicate-ignore-db = zabbix
relay_log = mysqld-relay-bin
log-slave-updates = ON


read-only = 0
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-ignore-db = zabbix
auto-increment-increment = 10
auto-increment-offset = 2

1. 此时不需要导出B的初态了，因为它刚刚才从A导过来。 直接记住它的master日志状态：

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,performance_schema,sys,zabbix
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

1. 登录到A 服务器。 开启中继：

[[e-mail] ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log-bin = mysql-bin
binlog_format = mixed
server-id = 1

read-only = 0
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-ignore-db = zabbix
auto-increment-increment = 10
auto-increment-offset = 1

replicate-ignore-db = information_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = sys
replicate-ignore-db = zabbix
relay_log = mysqld-relay-bin
log-slave-updates = ON

1. 启动同步：

mysql> change master to master_host = '192.168.1.185',master_user='root',master_password='abc123',master_log_file='mysql-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

然后重启mysql服务。

然后查看，slave状态是否正常：

如果出现：
Slave_IO_Running: No
Slave_SQL_Running: No
解决办法：先停掉mysql服务。 找到这三个文件，把他们删掉。 一定要先停掉mysql服务。不然还是不成功。你需要重启一下机器了。 或者手动kill mysqld。

[root@ansible ~]# cd /var/lib/mysql
[root@ansible mysql]# rm -fr relay-log.info ansible-relay-bin.000001 ansible-relay-bin.index

再登录启动同步，之后再重启

mysql> change master to master_host = '192.168.1.185',master_user='root',master_password='abc123',master_log_file='mysql-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

重启之后再查看：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.185
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys,zabbix
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
      ……

出现连个NO的原因是：是因为我们在配置A的中继文件时改了中继文件名，但是mysql没有同步。

至此主主同步已经开启了。
验证一下，方法在B上插入数据，在A上查看。

===============================================

下面介绍一下基于gtid模式的复制配置：

配置文件的最后加上gtid_mode=on ； enforce_gtid_consistency=on 这两项。即
主的配置：

# cat /etc/my.cnf
log-bin=mysql-bin
binlog_format=mixed
server-id=1
auto-increment-increment = 2
auto-increment-offset = 1
gtid_mode=on
enforce_gtid_consistency=on

从的配置：

gtid_mode=on
enforce_gtid_consistency=on
log-bin = mysql-bin
binlog_format = mixed
server-id = 2

主给从授权：

grant replication slave on *.* to 'root'@192.168.1.121 identified by 'abc123';

从找主：

change master to master_host='192.168.1.185',master_user='ha',master_password='Lockey+123',master_auto_position=1;

线上业务量比较大的时候，往往主库会不停的写入，此时就需要用到gtid 模式。他的好处就是，出库不用锁库，而且自动找到position 的位置，不用担心position不停变化而无法同步。