MySQL数据库的主从复制

• 概述
  单台MySQL数据库服务器，需要承载客户端的读取和写入操作的请求，那么弱客户端请求过多，服务器会不堪重负，那么可以选择MySQL数据库集群的方式来增加MySQL服务处理数据的效率，想web集群一样，但是不得不考虑的是多台MySQL数据库之间数据的一致性；
  

• MySQL主从复制原理
  

  • 在每一个失误更新之前，master在二进制日志记录这些改变，写入二进制日志完成后，master通知存储引擎提交事务

  • slave将master的Binary log复制到其中继日志中，首先slave开始一个I/O线程，I/O线程在master的Binary log中读取事件，如果已经跟上master，他会睡眠并等待master产生新的事件，I/O线程将这些事件写入到中继日志的Relay log中，中继日志通常存放在OS的缓存中，所以开销较小；

  • slave中的sql线程在中级日志Relay log中读取事件，并重放其中的时间而更新slave的数据，使其与master中的数据一致；

  • 复制类型

    • 基于语句的复制
      在主服务器上执行的sql语句，再从服务器上同样也会执行，MySQL默认选用基于语句的复制，效率比较高，MySQL5.0及之前的版本仅支持基于语句的复制(也叫做逻辑复制，logical replication),这在数据库并不常见。master记录下改变数据的查询，然后slave从中继日志中读取事件，并执行它，这些sql语句与master执行的语句一样
    • 基于行的复制
      把改变的内容复制过去，而不是把命令重新执行一遍
      混合型的复制
      默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行

• 主从复制方式

  • 基于二进制文件
    二级制日志文件
  • 基于GTID方式
    全局事务标示符自5.6版本开启的新型复制方式，GTID的组成：前面是server_uuid，后面是一个序列号
    例如：server_uuid：sequence number
    7800a22c-95ae-11e4-983d-080027de205a:10
    UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。
    Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。

  工作过程

  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。
  6、在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。
  优势：
  1、更简单的实现failover，不用以前那样在需要找二进制日志文件log_file和位置值log_pos。
  2、更简单的搭建主从复制。
  3、比传统的复制更加安全。
  4、GTID是连续的没有空洞的，保证数据的一致性，零丢失。     
  5、slave无需知道master的pos值和日志文件值，只需要知道master的ip、用户名、密码即可；
  

• 搭建MySQL主从复制

• 安装MySQL，yum方式安装
  两台安装方式一致，详见https://www.studyxin.com/MySQL/61.html
• 配置master主服务器

[root@localhost ~]# vim /etc/my.cnf         ##在mysqld区域添加以下内容
[mysqld]
server-id=1             ##配置server-id，让主服务器有唯一ID号
log-bin=mysql-bin       ##打开mysql日志，日志格式为二进制
skip-name-resolve       ##关闭名称解析，(非必填)

[root@localhost ~]# systemctl restart  mysqld.service       ##重启生效
[root@localhost ~]# mysql -uroot -p123123
##在master的数据库中建立一个备份账户：使得每一个slave使用标准的MySQL用户名和密码连接master，进行赋值操作的用户会授予replication slave权限
mysql> grant replication slave,replication client on *.* to slave@'192.168.100.%' identified by '123123';

##在master的数据库执行show master status，查看主服务器二进制状态及位置号
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      622 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 配置slave从服务器

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=2                     ##配置server-id,让从服务器有唯一ID号
relay-log=mysql-relay-bin       ##打开mysql日志，日志格式为二进制
read-only=1                     ##设置为只读权限
log-bin=mysql-bin               ##开启从服务器二进制日志
log-slave-updates=1             ##使得更新的数据写进二进制日志中

[root@localhost ~]# systemctl restart  mysqld.service
[root@localhost ~]# mysql -uroot -p123123
##让slave连接master，并开始重做master二进制日志中的事件
mysql> change master to master_host='192.168.100.105',
    -> master_user='slave',
    -> master_password='123123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=622;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;         ##执行start  slave，启动复制线程
Query OK, 0 rows affected (0.00 sec)

##使用show slave status\G;查看服务器状态，如下所示，也可使用show processlist \G;查看当前复制状态
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

• 测试主从同步
  在master服务器上创建数据库、表，并插入数据，查看slave同步状态

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

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

mysql> use hello;
mysql> create table good(id int not null);
mysql> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| good            |
+-----------------+
1 row in set (0.00 sec)

mysql> insert into good values (1);
mysql> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| good            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from good;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

slave验证

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

mysql> use hello;
mysql> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| good            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from good;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)