第1章 MySQL主从同步介绍
1.1 数据库损坏介绍
数据库损坏可以理解为业务不能使用数据库的情况，主要有外在原因和数据库本身的原因两方面。
- 外在原因：
- 网络原因
- 业务应用存在问题导致的客户端损坏
- 数据库本身的原因：
- 物理损坏：服务器、硬盘、存储、数据文件的损坏
- 逻辑损坏：误 drop 、delete 、truncate ......update等的操作命令
- 解决方法：
- 备份
- 主从同步
1.2 MySQL同步简介
复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。主服务器将所有数据和结构更改记录到二进制日志中。从属服务器从主服务器请求该二进制日志并在本地应用其内容。
1.3 主从同步的作用
- 高可用
- 辅助备份
- 负载分担
1.4 MySQL同步应用场景
- 从服务器作为主服务器的实时数据备份
- 主从服务器实现读写分离，从服务器实现负载均衡
- 把多个从服务器根据业务重要性进行拆分访问
第2章 MySQL同步的结构原理
2.1 MySQL同步原理总览
- 至少两台服务器（一主一从或多主多从）通过二进制日志文件进行主从同步
- 主服务器的二进制日志自动同步到从服务器上再运行一遍
提示：通过网络连接两台服务器，一般都会出现延迟的状态，所以也可以说是异步的
2.2 MySQL同步原理详解（第一次开启主从同步）
- 从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user、password、port、ip），并且让从库知道，二进制日志的起点位置（file名、position号）
- 从库的IO和主库的dump线程建立连接
- 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求
- 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程
- 从库IO线程接收binlog evnets，并放到本地relay-log中，传送过来的信息会记录到info中。
- 从库应用relay-log，并且把应用过的记录存放到relay-log.info，默认情况下已经应用过的relay会自动被清理purge
至此第一次主从同步就已经完成，一旦主从运行起来就不需要再手工执行change master to了，因为信息都会被存放到master.info中，之后的每次同步其他的过程都是一样的。
2.3 主从同步的必要条件
- 主服务期一定要打开二进制日志
- 必须两台以上服务器（或者是多个实例）
- 从服务器需要一次数据初始化
- 如果主从服务器都是新搭建的话，可以不做初始化
- 如果主服务器已经运行了很长时间，可以通过备份将主库数据恢复到从库。
- 主库必须要有对从库复制请求的用户。
- 从库需要有relay-log设置，存放从主库传送过来的二进制日志
- 在第一次的时候，从库需要change master to 去连接主库。
- change master信息需要存放到info中
- 从库是通过relay-log.info记录的已经应用过的relay-log信息感知主库发生了新的变化。
- 在复制过程中涉及到的线程：
- 从库会开启一个IO thread，负责连接主库，请求binlog，接收binlog并写入relay-log。
- 从库会开启一个SQL thread，负责执行relay-log中的事件。
- 主库会开启一个dump thrad，负责响应从IO thread的请求。
2.3.1 复制中的线程
- 主库：Dump thread
- 从库：IO thread、SQL thread
2.3.2 复制中的日志及文件
- 主库：binlog
- 从库：Relay-log、Relay-index、info、Relay-log.info
第3章 基本主从同步配置实践
3.1 配置思路
- 两个节点
- 主库binlog开启，从库开启relay-log
- server-id不同
- 主库开启复制账户
- 主库备份并记录二进制文件和position
- 从库change master to连接主库
- 启动从库复制
- 验证主从
3.2 配置实践
3.2.1 修改配置文件
3.2.1.1 主库配置文件
[root@db01 ~]# vim /etc/my.cnf [mysqld] server-id = 41 basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /tmp/mysql.sock port = 3306 log-bin = /usr/local/mysql/data/mysql-bin [mysqld_safe] log-error= /tmp/mysql.err pid-file= /usr/local/mysql/data/mysql.pid [client] port = 3306 socket = /tmp/mysql.sock
3.2.1.2 从库配置文件
[root@db02 ~]# vim /etc/my.cnf [mysqld] server-id = 42 basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /tmp/mysql.sock port = 3306 log-bin = /usr/local/mysql/data/mysql-bin [mysqld_safe] log-error= /tmp/mysql.err pid-file= /usr/local/mysql/data/mysql.pid [client] port = 3306 socket = /tmp/mysql.sock
3.2.2 主库中创建从库用户
[root@db01 ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.17 sec)
3.2.3 全备主库并复制给从库
[root@db01 ~]# mysqldump -uroot -p123456 -A -B -F --master-data=2 > /tmp/full.sql [root@db01 ~]# scp /tmp/full.sql 10.0.0.42:/tmp/
3.2.4 查看备份文件使用的log-bin文件和position号
[root@db02 ~]# vim /tmp/full.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;
3.2.5 从库还原主库备份
[root@db02 ~]# mysql -uroot -p123456 mysql> source /tmp/full.sql;
3.2.6 从库连接主库
[root@db02 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.41', # 主库IP地址
-> MASTER_USER='repl', # 主库中连接从库的用户
-> MASTER_PASSWORD='123456', # 主库中连接从库的密码
-> MASTER_PORT=3306, # 主库数据库端口号
-> MASTER_LOG_FILE='mysql-bin.000002', # 备份文件中的log-bin文件
-> MASTER_LOG_POS=120; # 备份文件中的position号
Query OK, 0 rows affected, 2 warnings (0.21 sec)
- 相关命令：
CHANGE MASTER TO MASTER_HOST='10.0.0.41', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;
3.2.7 开启从库进程
[root@db02 ~]# mysql -uroot -p123456 mysql> start slave; Query OK, 0 rows affected (0.13 sec)
3.2.8 验证从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: db02-relay-bin.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes # 显示为Yes表示从库线程状态正常
Slave_SQL_Running: Yes # 显示为Yes表示从库线程状态正常
...省略部分输出内容...
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
...省略部分输出内容...
3.2.9 查看同步情况
3.2.9.1 主库更新数据库
[root@db01 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.13 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table test (
-> id int(4) not null auto_increment,
-> name varchar(20),
-> age int(3),
-> primary key (id)
-> );
Query OK, 0 rows affected (0.61 sec)
mysql> insert into test values(1,'leon',18),(2,'shadow',20),(3,'shuaige',26),(4,'meinv',19),(5,'wangzi',28);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test.test;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | leon | 18 |
| 2 | shadow | 20 |
| 3 | shuaige | 26 |
| 4 | meinv | 19 |
| 5 | wangzi | 28 |
+----+---------+------+
5 rows in set (0.00 sec)
3.2.9.2 从库同步数据库
[root@db02 ~]# mysql -uroot -p123456 mysql> select * from test.test; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | leon | 18 | | 2 | shadow | 20 | | 3 | shuaige | 26 | | 4 | meinv | 19 | | 5 | wangzi | 28 | +----+---------+------+ 5 rows in set (0.00 sec)
提示：注意同步时注意需要关闭防火墙和SELinux。
第4章 主从同步问题汇总
4.1 忘记root密码
【问题现象】：
[root@db01 ~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
【解决方法】：
[root@db01 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
[root@db01 ~]# mysql -S /tmp/mysql.sock
mysql> update user set password=password('123456') where user='root' and host='localhost';
mysql> flush privileges;
4.2 Slave_IO_Running: Connecting
【问题现象】：
mysql> show slave status\G; Last_IO_Error: error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1
【原因分析】：
1、主机没启动，或者宕机
2、网络通信问题
3、防火墙未关闭
4、repl用户名或密码发生了变更
4.3 主从库UUID相同
【问题现象】：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
【解决办法】：
更改任意主库或从库的UUID信息即可：
[root@db02 ~]# uuidgen 0e728e35-2a8a-43a1-ba9a-c1d2b680cf51 [root@db02 ~]# vim /usr/local/mysql/data/auto.cnf [auto] server-uuid=0e728e35-2a8a-43a1-ba9a-c1d2b680cf51 [root@db02 ~]# /etc/init.d/mysqld restart
4.4 Master_Log_File中的二进制日志文件落后主库
【问题现象】：
从库中记录的，主库已经给从库传送的binlog事件的坐标，一般在繁忙的生产环境下，会落后于主库（show master status）
Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 729
【原因分析】：
若落后的很多（20以上）的原因可能为：
- 网络问题
- 主库DUMP太繁忙
- 从库IO线程太忙
4.5 Last_SQL_Error
【原因分析】：
- 主库做操作的对象，在从库不存在
- 主库做操作的对象属性不一致
- 主库做操作的对象，从库已经存在
- ......等等
第5章 监控主从同步状态
5.1 主从同步关键信息
5.1.1 Slave_*_Running
- Slave_IO_Running I/O 线程正在运行、未运行还是正在运行但尚未连接到主服务器。可能值分别为Yes、No 或 Connecting。
- Slave_SQL_Running SQL 线程当前正在运行、未运行，可能值分别为 Yes、No
5.1.2 主服务器日志坐标
- Master_Log_File 和 Read_Master_Log_Pos 标识主服务器二进制日志中 I/O 线程已经传输的最近事件的坐标。
- 如果Master_Log_File和Read_Master_Log_Pos 的值远远落后于主服务器上的那些值，这表示主服务器与从属服务器之间事件的网络传输可能存在延迟。
5.1.3 中继日志坐标
- Relay_Log_File 和 Relay_Log_Pos 列标识从属服务器中继日志中 SQL 线程已经执行的最近事件的坐标。这些坐标对应于 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列标识的主服务器二进制日志中的坐标。
- 如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的输出远远落后于 Master_Log_File 和Read_Master_Log_Pos 列（表示 I/O 线程的坐标），这表示 SQL 线程（而不是 I/O 线程）中存在延迟。即，它表示复制日志事件快于执行这些事件。
5.1.4 Last_IO_Error、Last_SQL_Error
- 分别导致 I/O 线程或 SQL 线程停止的最新错误的错误消息。在正常复制过程中，这些字段是空的。如果发生错误并导致消息显示在以上任一字段中，则错误值也显示在错误日志中。
5.1.5 Last_IO_Errno、Last_SQL_Errno
- 与分别导致 I/O 线程或 SQL 线程停止的最新错误关联的错误编号。在正常复制过程中，这些字段包含编号 0。
5.1.6 Last_IO_Error_Timestamp、Last_SQL_Error_Timestamp
- 分别导致 I/O 线程或 SQL 线程停止的最新错误的时间戳，格式为 YYMMDD HH:MM:SS。在正常复制过程中，这些字段是空的。
5.2 查看整体状态
[root@db02 ~]# mysql -uroot -p123456 -e "show slave status\G;"
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 748
Relay_Log_File: db02-relay-bin.000003
Relay_Log_Pos: 911
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 748
Relay_Log_Space: 1083
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 41
Master_UUID: 0b1a934f-a67f-11e7-95e2-000c2982d598
Master_Info_File: /usr/local/mysql-5.6.34/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
5.3 查看关键线程状态
[root@db02 ~]# mysql -uroot -p123456 -e "show slave status\G;" | egrep "Running|Behind_Master" | head -3
Slave_IO_Running: Yes # 获取bin-log并存储到relay-log的线程
Slave_SQL_Running: Yes # 运行relay-log中的events到从库的线程
Seconds_Behind_Master: 0 # 落后主库的时间
第6章 主从同步故障企业案例实战
6.1 企业环境说明
- 背景：标准主从复制结构，在业务逻辑中有leon数据库，leon数据库下有t1表为生产表。
- 故障原因：开发人员在从库创建了一个leon1库，觉得不对，后又在主库中做了相同的操作。导致了从库复制失效。
6.2 解决方案
在mysql中可以跳过某些错误。
6.2.1 解决方法一：MySQL语句
mysql> stop slave; # 临时停止同步开关 mysql> set global sql_slave_skip_counter=1; # 将同步指针向下移动一个，如果多次不同步可以重复操作 mysql>start slave; # 重新启动服务
6.2.2 解决方法二：配置文件
[root@db01 ~]# vim /etc/my.cnf slave-skip-errors = 1032,1062,1007 # 跳过错误的代码
6.2.3 解决办法三：重新搭建主从复制（推荐）
过程略。
第7章 主从复制架构演变
7.1 基本结构
- 一主一从：使用从库实时备份。如果从库只做备份服务器用，那么主库的压力会不减反增，因为所有的读写业务都在主库实现，dump线程读取并投递binlog会增加。
- 一主多从：应对读多的业务需求。一旦发展成这个架构，会出现dump线程读取并投递binlog，导致主库的压力更大了。
- 多级主从：采用中间库缓解主库dump的压力。但会出现中间库瓶颈的问题：权衡性能和安全后中间库可以选择blackhole引擎适当的提高MySQL的效率。
- 双主：双主未必能提升写入性能，因为每一个主的写入都会实时同步到另外的上，因此一般生产用的不多。
- 循环复制
7.2 高级应用架构演变
- 读写分离：MySQL proxy、amoeba、xx-dbproxy等。
- 分库分表：cobar、自主研发等。
- MMM架构：mysql-mmm（google，已经淘汰了）
- MHA架构：mysql-master-ha（日本DeNa，有人说已经过时，但还可以使用）
- MGR：7新特性 MySQL Group replication
- PXC、MySQL Cluster架构
7.3 读写分离简介
7.3.1 实现思路
- 判断语句类型，根据语句类型进行分发
- 分发的负载均衡
- 会话持续性
- 判断语句是否执行过，如果执行过，就不再进行负载均衡。
7.3.2 读写分离软件
- atlas：配置简单，比较实用
- 自主开发：依赖于业务
- cober：依赖与业务
7.4 防止从库误操作
7.4.1 设置从库为只读库
[root@db03 ~]# vim /etc/my.cnf [mysqld] read_only = 1 # 重启数据库即可
7.4.2 给从库创建只读用户
跟开发人员明确说明读的时候只给 ruser，写的时候使用wuser（开发制度），添加完成后只要使用新建的ruser和wuser登录数据库进行测试即可：
[root@db01 ~]# mysql -uroot -p123456 # 创建读用户 mysql> grant select on leon.* to ruser@'localhost' identified by '123456'; # 创建写用户 mysql> grant select,insert,update,delete,alter,drop on leon.* to wuser@'localhost' identified by '123456';
7.4.3 选择方式
- 推荐使用第二种方式：
- 配置时不用重启
- 将来如果主库宕机，故障切换时也不需要重启数据库。
7.5 多级主从复制实践
7.5.1 编辑MySQL配置文件
7.5.1.1 主库配置文件
[root@db01 ~]# vim /etc/my.cnf [mysqld] server-id = 41 basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /tmp/mysql.sock port = 3306 log-bin = /usr/local/mysql/data/mysql-bin [mysqld_safe] log-error= /tmp/mysql.err pid-file= /usr/local/mysql/data/mysql.pid [client] port = 3306 socket = /tmp/mysql.sock
7.5.1.2 中间库配置文件
[root@db02 ~]# vim /etc/my.cnf [mysqld] server-id = 42 log-slave-updates basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /tmp/mysql.sock port = 3306 log-bin = /usr/local/mysql/data/mysql-bin [mysqld_safe] log-error= /tmp/mysql.err pid-file= /usr/local/mysql/data/mysql.pid [client] port = 3306 socket = /tmp/mysql.sock
7.5.1.3 从库配置文件
[root@db03 ~]# vim /etc/my.cnf [mysqld] server-id = 43 read_only = 1 basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /tmp/mysql.sock port = 3306 log-bin = /usr/local/mysql/data/mysql-bin [mysqld_safe] log-error= /tmp/mysql.err pid-file= /usr/local/mysql/data/mysql.pid [client] port = 3306 socket = /tmp/mysql.sock
7.5.2 重启服务器
[root@db01 ~]# /etc/init.d/mysqld restart [root@db02 ~]# /etc/init.d/mysqld restart [root@db03 ~]# /etc/init.d/mysqld restart
7.5.3 备份当前主库数据
[root@db01 ~]# mysqldump -uroot -p123456 -A -B -F --master-data=2 > /tmp/full.sql
7.5.4 查看备份文件中二进制文件信息
[root@db01 ~]# vim /tmp/full.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=120;
7.5.5 备份文件拷贝到中间库和从库
[root@db01 ~]# scp /tmp/full.sql 10.0.0.42:/tmp [root@db01 ~]# scp /tmp/full.sql 10.0.0.43:/tmp
7.5.6 还原备份文件并连接主库
7.5.6.1 中间库
[root@db02 ~]# mysql
mysql> source /tmp/full.sql;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.41',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000011',
-> MASTER_LOG_POS=120;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 444
Relay_Log_File: db02-relay-bin.000030
Relay_Log_Pos: 607
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略部分输出内容...
7.5.6.2 从库
[root@db03 ~]# mysql
mysql> source /tmp/full.sql;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.42',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000011',
-> MASTER_LOG_POS=120;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.42
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 120
Relay_Log_File: db03-relay-bin.000015
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略部分输出内容...
7.5.7 进行测试
7.5.7.1 主库中操作
[root@db01 ~]# mysql -uroot -p123456
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> create table t1(name varchar(20));
Query OK, 0 rows affected (0.36 sec)
mysql> insert into t1 values('xiaoming');
Query OK, 1 row affected (0.21 sec)
mysql> select * from t1;
+----------+
| name |
+----------+
| xiaoming |
+----------+
1 row in set (0.00 sec)
7.5.7.2 从库中查看
[root@db03 ~]# mysql -uroot -p123456 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | test | +----------------+ 2 rows in set (0.00 sec) mysql> select * from t1; +----------+ | name | +----------+ | xiaoming | +----------+ 1 row in set (0.00 sec)
第8章 主从复制高级功能
8.1 延时复制
8.1.1 延时复制介绍
对于误删除、误修改（delete drop update）类似的逻辑性错误，普通从库可能很快就会应用此错误操作，对于业务是灾难性的。常规处理方法是找备份恢复并应用binlog到错误操作之前。
由此产生了延时从库。正常主从复制的过程是：误操作binlog—>dump THREAD—>IO THREAD—>relay—>SQL THREAD—>从数据库；而延时从库是控制SQL THREAD不实时的进行应用relay-log操作，从而进行延时同步。
通俗的理解延时同步就是人为设置从库延迟一段时间同步主库，以保证防止主库中的误操作对数据的影响。bin-log文件是实时同步到从库，但是延时一段时间再执行，而不是延时一段时间再同步bin-log文件。
延时同步主要应用在备份库中，一般生产中延时3-6小时。
8.1.2 延时复制配置
[root@db02 ~]# mysql -uroot -p123456 mysql> stop slave; Query OK, 0 rows affected (0.22 sec) mysql> change master to master_delay = 30; Query OK, 0 rows affected (0.20 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)
8.1.3 查看测试结果
8.1.3.1 主库操作
[root@db01 ~]# mysql -uroot -p123456 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | test | +----------------+ 2 rows in set (0.00 sec) mysql> drop table t1; Query OK, 0 rows affected (0.11 sec)
8.1.3.2 从库查看
[root@db02 ~]# mysql -uroot -p123456
mysql> show slave status\G;
*************************** 1. row ***************************
Seconds_Behind_Master: 22 # 提示还有22秒进行更新
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.01 sec)
8.2 半同步复制
8.2.1 半同步复制介绍
普通主从是异步的，意味着从库同步到什么程度是不可控的，对于安全性要求比较高的应用场景，如金融、运营商等要求数据安全性极高的企业，为了让mysql更加能够适用于高安全性的场景，产生了半同步复制，半同步复制是为了安全，不是为了性能。
8.2.2 配置半同步复制
8.2.2.1 主库配置
[root@db01 ~]# mysql -uroot -p123456 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected (0.29 sec) mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; Query OK, 0 rows affected (0.01 sec) # 查看是否已经开启延时 mysql> show status like 'Rpl_semi_sync_master_status'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Rpl_semi_sync_master_status | ON | +-----------------------------+-------+ 1 row in set (0.00 sec)
8.2.2.2 从库配置
[root@db02 ~]# mysql -uroot -p123456 mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected (0.26 sec) mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; Query OK, 0 rows affected (0.10 sec) mysql> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.11 sec) mysql> START SLAVE IO_THREAD; Query OK, 0 rows affected (0.00 sec) # 查看是否已经开启延时 mysql> show status like 'Rpl_semi_sync_slave_status'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.11 sec)
8.3 复制故障切换（failover）
8.3.1 故障切换的原理
- 选择新的主库：尽量找一个数据相对新的节点
- 数据补偿：判断新主库和其他从节点数据的新旧，将自己没有拿过来，尽量恢复到比较新或者去数据补偿服务器，获取到自己没有的那段binlog。binlog server
- 启动新主库：将其他从库change master to 到新主库上
- 告诉应用服务器原来的主库已经不适用了，需要连接新的主库
8.4 5.6新特性：GTID复制
8.4.1 GTID复制简介
全局事务ID，对于一个事务组里的events 打以一个唯一标签，是为了更好地做failover而出现的复制方式，从5.6版本开始支持，5.7版本进行完善。GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。
8.4.2 GTID复制配置
8.4.2.1 配置主节点
[root@db01 ~]# vim /etc/my.cnf [mysqld] server-id = 41 # 同一个复制拓扑中的所有服务器的id号必须惟一 basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /tmp/mysql.sock port = 3306 log-bin = /usr/local/mysql/data/mysql-bin binlog-format=ROW # 二进制日志格式，强烈建议为ROW gtid-mode=on # 启用gtid类型，否则就是普通的复制架构 enforce-gtid-consistency=true # 强制GTID的一致性 log-slave-updates=1 # slave更新是否记入日志 [mysqld_safe] log-error= /tmp/mysql.err pid-file= /usr/local/mysql/data/mysql.pid [client] port = 3306 socket = /tmp/mysql.sock
8.4.2.2 配置从节点
[root@db02 ~]# vim /etc/my.cnf [mysqld] server-id = 42 basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /tmp/mysql.sock port = 3306 log-bin = /usr/local/mysql/data/mysql-bin binlog-format=ROW gtid-mode=on enforce-gtid-consistency=true log_slave_updates = 1 [mysqld_safe] log-error= /tmp/mysql.err pid-file= /usr/local/mysql/data/mysql.pid [client] port = 3306 socket = /tmp/mysql.sock
8.4.2.3 在主节点创建用户
[root@db01 ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.17 sec)
8.4.2.4 从库连接主库
[root@db02 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.41',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
8.4.2.5 开启从库线程
[root@db02 ~]# mysql -uroot -p123456 mysql>start slave; Query OK, 0 rows affected (0.12 sec) mysql> show slave status\G; *************************** 1. row *************************** Auto_Position: 1
我的微信
如果有技术上的问题可以扫一扫我的微信
