MySQL主从复制

实验环境准备

• node1为主，node2为从

修改配置文件

• node1上

[root@mysql-node1 ~]# vim /etc/my.cnf

服务器ID（对于主从复制环境，每个服务器都需要一个唯一的ID）

• node2上

[root@mysql-node2 ~]# vim /etc/my.cnf

服务器ID（对于主从复制环境，每个服务器都需要一个唯一的ID）

• 重启mysql

[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL. SUCCESS!

[root@mysql-node2 ~]# /etc/init.d/mysql.server restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

• 验证标识变量

#-e指定sql语句
[root@mysql-node1 ~]# mysql -uroot -p123456 -e "select @@server_id";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          10 |
+-------------+

[root@mysql-node2 ~]# mysql -uroot -p123456 -e "select @@server_id";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          20 |
+-------------+

实验

主开启bin-log日志

• 主开启二进制日志（bin-log日志会记录所有更改数据的语句（insert、update、delete等），不记录查询语句）
• 从不开启二进制日志功能

[root@mysql-node1 ~]# vim /etc/my.cnf

• 重启mysql

[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!

• 查看二进制日志在主机上的位置

[root@mysql-node1 ~]# cd /data/mysql/

• 使用mysqlbinlog查看二进制日志的内容

[root@mysql-node1 mysql]# mysqlbinlog mysql-bin.000001

创建用于主从复制的用户

master配置

[root@mysql-node1 ~]# mysql -uroot -p
Enter password:#生成专门用来做复制的用户，此用户是用于slave端做认证用的
mysql> create user huazi@"%" identified by "123456";#给他主从复制的权限
mysql> grant replication slave on *.* to huazi@"%";

#查看master的状态
mysql> show master status\G;
*************************** 1. row ***************************File: mysql-bin.000001Position: 481Binlog_Do_DB:Binlog_Ignore_DB:
Executed_Gtid_Set:

salve配置

[root@mysql-node2 ~]# mysql -uroot -p
Enter password:mysql> change master to master_host='172.25.254.10', -> master_user='huazi', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=481;

mysql> start slave;

mysql> show slave status\G;

测试

• 在master上建立一个db_hua库

mysql> create database db_hua;

• 在slave上查看，发现从上实现了与主的同步

#我们发现从上出现了db_hua这个库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_hua             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• 在master上建立一个表，并插入数据

mysql> use db_hua;mysql> create table userlist(-> name varchar(10) not null,-> password varchar(20) not null-> );mysql> insert into userlist values-> ("huazi","123456");

• 在slave上查看，发现实现了数据同步

mysql> use db_hua;mysql> show tables;
+------------------+
| Tables_in_db_hua |
+------------------+
| userlist         |
+------------------+mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
+-------+----------+

• 在slave上插入数据

mysql> insert into userlist values-> ("hua","789");mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
| hua   | 789      |
+-------+----------+

• 在master上查看，发现数据没有同步过来

mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
+-------+----------+

• slave上不能插入数据，如果插入数据后，master上同步不到

• 回收

mysql> delete from userlist where name="hua";

添加一台新的slave，如何实现数据的全部同步呢

• 准备一台机子mysql-node3，ip：172.25.254.30
• 修改配置文件，设置server-id为30

[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30

• master中对db_hua中的userlist表进行锁表，防止在备份的过程中不断有数据写入，保证备份前后数据的一致性

#锁表
mysql> flush tables with read lock;
#我们发现，此时不能插入数据
mysql> insert into userlist values-> ("hua","123");
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

• 使用mysqldump命令从master节点上备份数据

[root@mysql-node1 ~]# mysqldump -uroot --password=123456 db_hua userlist > back1.sql

• 将back1.sql复制到node3主机中

[root@mysql-node1 ~]# rsync back1.sql root@172.25.254.30:/root/

• 在node3上通过非交互式建库

[root@mysql-node3 ~]# mysql -uroot -p123456 -e "create database db_hua;"

• 在node3上通过非交互式导入back1.sql文件

[root@mysql-node3 ~]# mysql -uroot -p123456 db_hua < back1.sql

• 进入node3的数据库，发现已经导入成功

[root@mysql-node3 ~]# mysql -uroot -p123456
mysql> use db_hua;mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
+-------+----------+

• 在master上查看状态

mysql> show master status\G;
*************************** 1. row ***************************File: mysql-bin.000002Position: 154Binlog_Do_DB:Binlog_Ignore_DB:
Executed_Gtid_Set:

• node3上加入主从集群

mysql> change master to master_host='172.25.254.10',master_user='huazi',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;mysql> start slave;

• 查看node3上的slave状态

mysql> show slave status\G;

• master上解锁

mysql> unlock tables;

• master上插入数据

mysql> insert into userlist values -> ("hua","123");mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
| hua   | 123      |
+-------+----------+

• 在node3上查看，发现数据已经同步过来

mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |  #原数据
| hua   | 123      |  #新数据
+-------+----------+

• 在node2上查看，发现数据正常同步

mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
| hua   | 123      |
+-------+----------+

至此，就实现了一主两从

什么环境下主比较多，什么环境下从比较多？

• 有大量数据需要写入时，主比较多。有大量数据需要读取时，从比较多

延迟复制

• 延迟复制是用来控制sql线程的，和i/o线程无关
• 延迟复制不是i/o线程过段时间来复制，i/o是正常工作的
• 是日志已经保存在slave端了，那个sql要等多久进行回放
• 设置延迟是为了防止主的误操作

在node2上设置延迟

#先关闭sql线程
mysql> stop slave sql_thread;#设置延迟时间为60秒
mysql> change master to master_delay=60;#再开启sql线程
mysql> start slave sql_thread;

#我们发现时间已经改为了60秒
#即：每次主上修改数据后，从要等上60秒再进行数据同步
mysql> show slave status\G;

测试

• 在master上不小心删除了一条数据

mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
| hua   | 123      |
+-------+----------+mysql> delete from userlist where name="hua";

• 60秒之前，node2上

mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
| hua   | 123      |
+-------+----------+#立即将数据导出
[root@mysql-node2 ~]# mysqldump -uroot --password=123456 db_hua userlist > back2.sql

• 60秒之后，node2上

mysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
+-------+----------+

• master上做数据恢复

[root@mysql-node2 ~]# rsync back2.sql root@172.25.254.10:/root/[root@mysql-node1 ~]# mysql -uroot -p123456 db_hua < back2.sqlmysql> select * from userlist;
+-------+----------+
| name  | password |
+-------+----------+
| huazi | 123456   |
| hua   | 123      |
+-------+----------+

• 数据恢复后，slave上也会进行数据同步

并行复制

• 查看slave中的线程信息

mysql> show processlist;
+----+-------------+-----------+--------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db     | Command | Time  | State                                                  | Info             |
+----+-------------+-----------+--------+---------+-------+--------------------------------------------------------+------------------+
|  5 | root        | localhost | db_hua | Sleep   | 52137 |                                                        | NULL             |
|  6 | system user |           | NULL   | Connect | 54465 | Waiting for master to send event                       | NULL             |
|  7 | system user |           | NULL   | Connect | 53053 | Slave has read all relay log; waiting for more updates | NULL             |
|  8 | root        | localhost | NULL   | Query   |     0 | starting                                               | show processlist |
+----+-------------+-----------+--------+---------+-------+--------------------------------------------------------+------------------+

• 默认情况下slave中使用的是sql单线程回放复制master数据的
• 在master中多用户读写，如果使用sql单线程回放，那么就会造成组从复制延迟严重
• 开启MySQL的多线程回放可以解决上述问题

注意：如果slave上有延迟时间的设置，则不设置多线程回放

• 多线程回放是在slave中设置的

#在node3上
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30gtid_mode=on
enforce-gtid-consistency=on
slave-parallel-type=LOGICAL_CLOCK  #基于组提交
slave-parallel-workers=16   #开启线程数量
master_info_repository=TABLE  #master信息在表中记录，默认记录
relay_log_info_repository=TABLE  #回放日志信息在表中记录，默认记录
relay_log_recovery=ON    #日志回放恢复功能开启

#重启
[root@mysql-node3 ~]# /etc/init.d/mysqld restart[root@mysql-node3 ~]# mysql -uroot -p
Enter password:mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect |   18 | Slave has read all relay log; waiting for more updates | NULL             |
|  3 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
|  4 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
|  7 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 10 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 11 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |   18 | Waiting for an event from Coordinator                  | NULL             |
| 20 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+

• 此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求

gtid模式

未启用gtid时

• 在master端的写入时多用户读写，在slave端复制时单线程日志回放，所以slave端一定会延迟
• 这种延迟在slave端的延迟可能会导致数据同步不一致，当master挂掉后slave接管，一般会挑选一个和master延迟日志最接近的充当新的master
• 那么未接管master的主机继续充当slave角色并会指向到新的master上，作为其slave
• 这时候按照之前的配置我们需要知道新的master上的pos的id，但是我们无法确定新的master和slave之间差多少

当激活gtid后

• 当master出现问题后，slave2和master的数据最接近，会被作为新的master
• slave1指向新的master，但是slave1不会去检测新的master的pos id，只需要继续读取自己gtid_next即可

设置gtid

• master端和slave端都开启gtid

#master上
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin
gtid_mode=on
enforce-gtid-consistency=on

#slave上
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
gtid_mode=on
enforce-gtid-consistency=on

#slave上
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
gtid_mode=on
enforce-gtid-consistency=on

• 重启mysql

[root@mysql-node1 ~]# /etc/init.d/mysqld restart[root@mysql-node2 ~]# /etc/init.d/mysql.server restart[root@mysql-node3 ~]# /etc/init.d/mysqld restart

• 检测是否开启

[root@mysql-node1 ~]# mysql -uroot -p123456 -e "select @@session.gtid_next;"
+---------------------+
| @@session.gtid_next |
+---------------------+
| AUTOMATIC           | #开启
+---------------------+[root@mysql-node2 ~]# mysql -uroot -p123456 -e "select @@session.gtid_next;"
+---------------------+
| @@session.gtid_next |
+---------------------+
| AUTOMATIC           |
+---------------------+[root@mysql-node3 ~]# mysql -uroot -p123456 -e "select @@session.gtid_next;"
+---------------------+
| @@session.gtid_next |
+---------------------+
| AUTOMATIC           |
+---------------------+

• slave端设置

[root@mysql-node2 ~]# mysql -uroot -p
Enter password:
#先停掉slave
mysql> stop slave;mysql> change master to-> master_host="172.25.254.10",-> master_user='huazi',-> master_password="123456",-> master_auto_position=1;#再开启
mysql> start slave;

[root@mysql-node3 ~]# mysql -uroot -p
Enter password:#先停掉
mysql> stop slave;mysql> change master to-> master_host="172.25.254.10",-> master_user="huazi",-> master_password="123456",-> master_auto_position=1;#再开启
mysql> start slave;

测试

#master上
mysql> show master status\G;
*************************** 1. row ***************************File: mysql-bin.000003Position: 154Binlog_Do_DB:Binlog_Ignore_DB:
Executed_Gtid_Set:

#slave上（node2）
mysql> show slave status\G;

#slave上（node3）
mysql> show slave status\G;

主从复制的原理

架构

三个线程

实际上主从同步的原理就是基于bin-log日志进行数据同步的。在主从复制过程中，会基于3个线程来操作，一个主库线程，两个从库线程。

• 二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库线程可以将bin-log日志发送给从库I/O线程，当主库读取事件（Event）的时候，会在Bin-log上加锁，读取完成之后，再将锁释放掉
• 从库I/O线程会连接到主库，向主库发送请求更新Bin-log。这时从库的I/O线程就可以读取到主库的二进制日志转储线程发送的Bin-log更新部分，并且拷贝到本地的中继日志 （Relay log）
• 从库SQL线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步

主从复制三步骤

• 步骤1：Master将修改操作记录到二进制日志（bin-log）
• 步骤2：Slave将Master的binary log拷贝到它的中继日志（relay log）
• 步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的，而且重启后从接入点开始复制

具体操作

• slave端中设置了master端的ip，用户名，用户密码、日志文件名，和日志的Position，通过这些信息取得master的认证及信息
• master端在设定好bin-log启动后会开启binlog dump的线程
• master端的binlog dump线程把二进制的更新发送到slave端
• slave端开启两个线程，一个是I/O线程，一个是sql线程
  • i/o线程用于接收master端的二进制日志，此线程会在本地打开relay-log中继日志，并且保存到本地磁盘
  • sql线程读取本地relog中继日志进行回放

架构缺陷

• 主从架构采用的是异步机制
  • master更新完成后直接发送二进制日志到slave，但是slave是否真正保存了数据，master端不会检测
  • master端直接保存二进制日志到磁盘
• 当master端到slave端的网络出现问题或者master端直接挂掉，二进制日志可能根本没有到达slave
• master出现问题后，slave端接管master，这个过程中数据就丢失了
• 这样的问题出现就无法达到数据的强一致性，零数据丢失

半同步模式

原理

• 用户线程写入完成后master中的dump线程会把二进制日志推送到slave端
• slave中的i/o线程接收后保存到relay-log中继日志
• 保存完成后slave向master端返回ack进行确认
• 在未接受到slave的ack时，master端是不做提交的，一直处于等待，当收到ack后提交到存储引擎
• 在5.6版本中用到的是after_commit模式，after_commit模式是先提交再等待ack返回后输出ok

启用半同步模式

• 做半同步之前需要开启gtid

什么时候我们需要多个slave

• 当读取操作远远高于写操作时。我们采用一主多从架构
• 数据库外层接入负载均衡层并搭配高可用机制

总结

show master status显示信息中的position字段

在MySQL中，SHOW MASTER STATUS 命令的输出包含了关于当前主服务器（Master）二进制日志（Binary Log）状态的信息。其中，Position 字段提供了关于二进制日志文件中当前写入位置的关键信息。

• 具体来说，Position 字段表示的是在当前二进制日志文件中，下一个即将被写入的事件的起始字节偏移量。这个偏移量是从二进制日志文件的开头算起的。每当主服务器执行一个会改变数据库状态的操作（如 INSERT、UPDATE 或 DELETE 语句）时，这个操作会被记录为一个事件，并追加到当前的二进制日志文件中。Position 字段的值会随着新事件的写入而递增
• 在复制（Replication）环境中，从服务器（Slave）会使用这个 Position 值来确定它应该从主服务器的二进制日志文件的哪个位置开始读取事件。这样，从服务器就能够准确地复制主服务器上的所有更改。

举个例子，如果 SHOW MASTER STATUS 命令的输出如下：

+------------------+----------+--------------+------------------+---------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                   |
+------------------+----------+--------------+------------------+---------------------------------------+
| mysql-bin.000001 |      1543 |              |                  |                                       |
+------------------+----------+--------------+------------------+---------------------------------------+

• 在这个例子中，Position 字段的值是 1543。这意味着在当前的二进制日志文件mysql-bin.000001中，下一个事件将从文件的第 1543 个字节开始写入（实际上，由于字节偏移量是从0开始的，所以第 1543 个字节是文件中的第1544个位置，但通常我们按照偏移量来理解这个值）
• 如果设置了复制，从服务器将会从这个位置开始读取事件，以确保它能够复制主服务器上的所有最新更改