Mysql主从

Mysql主从复制（Replication）可以实现将数据从一台数据库服务器（master）复制到一或多台数据库服务器（slave）
默认情况下属于异步复制，无需维持长连接 通过配置，可以复制所有的库或者几个库。

其作用有：1.实时灾备，用于故障切换；2.读写分离，提供查询服务，分担服务器压力；3.备份，避免影响业务

主从复制原理

mysql主从复制中：
第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

mysql主从的结构也可以分为图示几种

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效。
双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。
多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

配置主从（传统模式）

###在主数据库的客户端。（这里开了两个端口方便分别操作数据库和终端）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysqlmaster        |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.08 sec)

[root@localhost ~]# mysqldump -uroot -plocalhost --all-databases > all.sql   //全备主库
[root@localhost ~]# ll
total 654372
drwxr-xr-x  2 root root       227 Apr  3 21:12 0402
-rw-r--r--  1 root root    800932 May 15 04:48 all.sql

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
(此锁表的终端必须在备份完成以后才能退出）（实际环境备份时要避免备份时同时有写入造成备份不一致）

//将主数据库备份文件传送到从库
[root@localhost ~]# scp /root/all.sql root@192.168.161.200:/root/
The authenticity of host '192.168.161.200 (192.168.161.200)' can't be established.
ECDSA key fingerprint is SHA256:fqEcu4RiJVsKLE8UMBFBFr+fVGyOxGj1RdzNEmA7/WQ.
ECDSA key fingerprint is MD5:a1:95:bd:a3:bf:ea:b0:e0:39:84:93:69:bc:e9:59:a3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.161.200' (ECDSA) to the list of known hosts.
root@192.168.161.200's password:
all.sql                                          100% 6019KB  37.0MB/s   00:00

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

//在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@'192.168.161.200' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
//授权
mysql> grant replication slave on *.* to 'repl'@'192.168.161.200';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//修改主数据库配置
[root@localhost ~]# vim /etc/my.cnf
添加如下两行：
log-bin=mysql-bin  ：启用binlog日志
server-id=2 ：数据库服务器唯一标识符，主库的server-id值必须比从库的小
[root@localhost ~]# systemctl restart mysqld

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

###配置从数据库
[root@ashen1 ~]# vim /etc/my.cnf    //[mysqld]下添加如下两行：
relay-log=mysql-relay-bin ：启用中继日志relay-log
server-id=3 ：置从库的唯一标识符，从库的server-id值必须大于主库的该值

[root@ashen1 ~]# systemctl restart mysqld
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 


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

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.161.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000007
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes    //这里两个线程都开启了
            Slave_SQL_Running: Yes
。。。。。

##测试
#从数据库上内容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.08 sec)

###在主数据库添加新的数据库cy
mysql> create database cy；
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cy                 |
| mysql              |
| mysqlmaster        |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.08 sec)

##再去查看从数据库，发现也更新了库cy
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cy                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.08 sec)

GTID模式

MySQL传统复制是基于MySQL二进制文件(mysql-bin.000001)，加上对应日志文件中每个事件的偏移量位置点(postion)。 GTID事物是全局唯一性的，且一个事务对应一个GTID 一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。 GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。

基于GTID主从复制的步骤：

1.master数据改变时，会在事务前产生一个GTID，通过binlog dump记录到master的binlog中。
2.slave通过IO Thread将binlog中变更的数据，写入到slave的relay log中(中继日志)。
3.slave通过sql Thread读取relay log中的GTID，然后对比slave的binlog是否有此记录。
4.如果slave的binlog存在该GTID的记录，那么忽略掉。
5.如果slave的binlog不存在该GTID的记录，那么就执行该GTID事务，并一同记录到slave的binlog中。

基于GTID复制的优点

• 1.可以很方便的进行故障转移，记录master最后事务的GTID值。比如master：A，slave：B,C。当A挂了后，B执行了所有A传过来的事务。当C连接到B后，在自己的binlog找到最后一次A传过来的GTID。然后C将这个GTID发送给B，B获取到这个GTID，就开始从这个GTID的下一个GTID开始发送事务给C。这种自我寻找复制位置的模式减少事务丢失的可能性以及故障恢复的时间。
• 2.slave不会丢失master的任何修改(开启了log_slave_updates)

基于GTID复制的缺点：

• 1.不支持非事务引擎。
• 2.故障处理比较复杂，需要注入空事务。
• 3.不支持sql_slave_skip_counter(一般用这个来跳过基于binlog主从复制出现的问题。)
• 4.对执行的sql有一定的限制。
• 5.为了保证事务的安全性，create table … select无法使用。不能使用create temporary table创建临时表。不能使用关联更新事务表和非事务表。

### GTID模式在配置上是基于传统模式的，这里是在上面的传统模式后进行修改

### 在数据库配置上添加启用GTID模式，主从都要加

[root@wow ~]# vim /etc/my.cnf
....
gtid_mode=ON
enforce-gtid-consistency=true

## 从数据库重新开启slave模式

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='192.168.161.100',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

## 测试

##在主数据库上
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

##在从数据库上
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

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

##在主数据库
mysql> create database test2;
Query OK, 1 row affected (0.05 sec)

在从数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test2              |
+--------------------+
6 rows in set (0.00 sec)