mysql主从复制运维_MySQL主从复制

1.1 为什么需要主从？

数据库损坏了(业务不能使用数据库)

原因：

外在原因

网络

业务应用有问题

本身原因

物理损坏：机器坏了、硬盘损坏、存储坏了、数据文件损坏

逻辑损坏：错误的drop、delete、truncate、update。。。

解决方案：

备份(物理损坏丢失数据)

主从复制

1.2 MySQL复制介绍

复制是MySQL的一项功能，允许服务器将更改从一个实例复制到另一个实例。

主服务器将所有数据和结构更改记录到二进制日志中。

从属服务器从主服务器请求该二进制日志并在本地应用其内容。

1.2.1 复制作用：

高可用

辅助备份

分担负载

1.2.2 应用场景：

应用场景1：从服务器作为主服务器的实时数据备份

应用场景2：主从服务器实现读写分离，从服务器实现负载均衡

应用场景3：把多个从服务器根据业务重要性进行拆分访问

1.2.3 复制前提

主服务器必须开启二进制日志(binlog)

多台服务器

保证开始复制时主从服务器结构一致。

对于新建的数据库可以直接进行复制；

对于运行了一段时间的数据库，就要通过备份让主从数据库结构保证一致。

主库必须要有对从库复制请求的用户

从库需要有relay-log设置，存放从主库传来的binlog

首次连接，从库需要change master to连接主库

需要保存change master信息需要存放在master.info文件中

通过relay-log.info里记录的已经使用过的事件，从库知道主库发生了变化

复制中的线程

主库

dump thread：负责响应从库的IO线程

从库

IO thread ：负责连接主库，请求binlog，接收binlog并写入relay-log

SQL thread ：复制执行relay-log中的事件

1.2.4 复制原理

异步复制过程

总体来说，复制有3个步骤：

主服务器把数据更改记录到二进制日志中。(这叫做二进制日志事件)

从服务器把主服务器的二进制日志拷贝到自己的中继日志中。

从服务器和主服务器之间建立master/slave连接，相关信息存放在从服务器的master.info文件中

从服务器用IO线程询问主服务器是否有新的binlog

主服务器用dump线程读取binlog并发送给从服务器

从服务器接收binlog并存放在relay-log中

从服务器的SQL线程执行relay-log中的binlog，写入数据库

执行完毕的relay-log放在relay-log.info文件中

从服务器重放中继日志中的事件，把更改应用到自己的数据上。

1.3 主从复制部署

两台主机安装mysql 5.6

注意在两台主机的配置文件中/etc/my.cnf中需要添加`server-id=`参数。

创建复制用户

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

初始化从库

[root@db01 ~]# mysqldump -uroot -poldboy123 -A -F > /tmp/server.sql

[root@db01~]# scp /tmp/server.sql 10.0.0.8:/tmp

在从库中source执行

开启主从复制

在主库中查看binlog起始点：

mysql> mysql>show master;+----------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------+----------+--------------+------------------+-------------------+

| log-bin.000013 | 120 | | | |

+----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

从库开启复制：

mysql>change master to

master_host='10.0.0.51',

master_port=3306,

master_user='repl',

master_password='123',

master_log_file='log-bin.000013',

master_log_pos=120;

注意：也可以设置relay-bin的名称。

检查状态

start slave;

show slave status\G

成功的标志是：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

1.4 监控主从复制状态

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:10.0.0.51Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: log-bin.000014Read_Master_Log_Pos:120Relay_Log_File: web01-relay-bin.000002Relay_Log_Pos:281Relay_Master_Log_File: log-bin.000014Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:120Relay_Log_Space:454Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1Master_UUID: d4519488-d005-11e7-a4ac-000c2924dc94

Master_Info_File:/application/mysql-5.6.38-linux-glibc2.12-x86_64/data/master.info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor the slave I/O thread to update it

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0

1 row in set (0.00 sec)

1.4.1 注意事项

Slave_*_Running：

Slave_IO_RunningI/O 线程正在运行、未运行还是正在运行但尚未连接到主服务器。可能值分别为Yes、No 或Connecting。

Slave_SQL_RunningSQL 线程当前正在运行、未运行,可能值分别为Yes、No

主服务器日志坐标：Master_Log_File和Read_Master_Log_Pos标识主服务器二进制日志中I/O 线程已经传输的最近事件的坐标。

如果Master_Log_File和Read_Master_Log_Pos的值远远落后于主服务器上的那些值，这表示主服务器与从属服务器之间事件的网络传输可能存在延迟。

中继日志坐标：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 线程)中存在延迟。即，它表示复制日志事件快于执行这些事件。

Last_IO_Error、Last_SQL_Error：分别导致I/O 线程或SQL 线程停止的最新错误的错误消息。在正常复制过程中，这些字段是空的。如果发生错误并导致消息显示在以上任一字段中，则错误值也显示在错误日志中。

Last_IO_Errno、Last_SQL_Errno：与分别导致I/O 线程或SQL 线程停止的最新错误关联的错误编号。在正常复制过程中，这些字段包含编号0。

Last_IO_Error_Timestamp、Last_SQL_Error_Timestamp：分别导致I/O 线程或SQL 线程停止的最新错误的时间戳，格式为YYMMDD HH:MM:SS。在正常复制过程中，这些字段是空的。

1.4.2 错误实例

从库binlog落后于主库

Master_Log_File: log-bin.000014Read_Master_Log_Pos:120

从库的logbin比主库的logbin慢的原因：

1. 网络问题

2. 主库dump线程繁忙

3. 从库IO线程繁忙

【扩展】

延时节点概念：是SQL线程延时，不是IO线程延时。

SQL线程报错

原因：

1. 主库操作对象在从库中不存在

2. 主库操作对象的属性和从库不一致

3. 主从操作顺序颠倒

解决方法：

1. 跳过错误

stop slave;set global sql_slave_skip_counter = 1;

start slave;

也可以在配置文件中跳过错误号码：

[mysqld]

slave-skip-errors = 1032,1062,1007

1.5 企业实例

背景：标准主从复制结构，在业务逻辑中有oldboy数据库，oldboy数据库下有t1表为生产表。

故障原因：开发人员在从库创建了一个oldgirl库，觉得不对，后又在主库中做了相同的操作。导致了从库复制失效。

解决方案：

主从复制故障及解决

stop slave; #<==临时停止同步开关。set global sql_slave_skip_counter= 1 ; #<==将同步指针向下移动一个，如果多次不同步，可以重复操作。

start slave;/etc/my.cnf

slave-skip-errors = 1032,1062,1007

如何避免问题？

从库设置为只读库

在my.cnf中添加read_only=1

单独在从库创建一个只读用户

在主库创建写用户

优点：

配置时不需要重启

故障切换时也不需要重启

1.6 主从架构演变

备份

1. 相当于实时备份

2. 使用从库备份

问题

如果从库只是作为备份服务器使用，那么主库的压力会增加，因为所有的业务都在主库进行读写(dump线程读取并发送给binlog)

解决方法：

1. 一主一从

分出部分读业务到从库(读写分离)

一主多从，分担压力(针对读业务多的需求)

但是这种一主多从的模式会使dump线程压力更大了

多级主从

使用中间库分担主库dump线程读取分发binlog的压力，由于中间库只作为分发者，不需要其他操作，为了提高中间库的性能，可以使用blackhole存储引擎。

双主模型

环状复制

1.7 高级应用架构

性能

读写分离——MySQLproxy、amoeba、xx-dbproxy等。

分库分表——cobar、自主研发等。

比较依赖于业务

实施思路：

判断语句类型

根据语句类型进行分发

负载均衡，分发到从库

会话持续性(减少用户认证之类的操作)

判断语句是否执行过(提高性能，减少重复操作)

高可用

MMM架构——mysql-mmm(google)(不在使用)

MHA架构——mysql-master-ha(日本DeNa)

MGR ——5.7 新特性MySQLGroup replication

PXC、MySQLCluster架构

1.8 多级主从部署(级联主从)

类似于一主一从的部署

不同之处在于主从之间多了一个中间服务器

[mysqld]

basedir= /application/mysql/datadir= /application/mysql/data/socket= /application/mysql/tmp/mysql.sock

character_set_server=utf8

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

server-id = 2log-bin=/tmp/log-bin

binlog-format=row

autocommit=1log-slave-updates

[client]

socket= /application/mysql/tmp/mysql.sock

在中间服务器的my.cnf文件中需要开启binlog并添加```log-slave-updates```参数，表示强制刷新binlog，否则binlog日志不会刷新。

相当于做了两套主从。

`reset slave；`重置slave(关闭状态)

1.9 扩展

1.9.1 复制延时

问题：

主服务器的错误操作会同步到从服务器，导致数据恢复比较麻烦。

解决方法：

采用复制延时，这样主服务器操作错误，从服务器由于延时复制可以在一段时间内避免应用错误操作，这样就可以及时恢复数据。

复制延时是在SQL线程的层面进行控制，不允许SQL线程实时的执行relay log中的操作。

如何设置：

stop slave;

change master to master_delay= 30; #单位是秒

start slave;

结果：

mysql>show slave status\G

SQL_Delay:30SQL_Remaining_Delay: NULL

生产场景中一般延时3-6小时

1.9.2 半同步复制

注重安全，不注重性能

普通异步主从中从库的同步率是不可控的，总会有延时的

对于安全性要求比较高的应用场景，比如金融、运营商等不会使用普通异步主从架构。

为了让MySQL更加能够适用于高安全性的场景才有了半同步复制。

半同步基于dump线程和IO线程，省略了SQL线程读取写入的部分

部署：

1、加载插件

主:

INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';

从:

INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

2、查看是否加载成功:

show plugins;

3、启动:

主:

SET GLOBAL rpl_semi_sync_master_enabled= 1;

从:

SET GLOBAL rpl_semi_sync_slave_enabled= 1;

只是临时启动，需要写入配置文件中。

4、重启从库上的IO线程

STOP SLAVE IO_THREAD;

START SLAVE IO_THREAD;

5、查看是否在运行

主:

show status like'Rpl_semi_sync_master_status';

从:

show status like'Rpl_semi_sync_slave_status';

1.9.3 主从同步的故障转移(failover)

MHA设计理念：

主服务器宕掉了，但是多台从服务器的数据和主服务器同步不完整，这时就需要整合多台从服务器中的同步的数据到新的主服务器中，尽量保证数据的完整性。

1. 选择新主

2. 数据补偿：判断新主服务器和其他从节点数据的新旧，补全自己的数据，尽量恢复到比较新的数据，或者去旧主服务器中获取binlog日志补全自己的数据

3. 启动新主，将其他从服务器指向新主

4. 公布新主

1.9.4 GTID复制

中继日志(relay log)：记录了events和position号

在执行的事务中打上一个唯一标签，这样就可以保证事务之间的连续性及唯一性

为了failover出现的更好的复制，5.6出现，5.7完善

GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。

它的官方定义如下：

GTID = source_id ：transaction_id

7E11FA47-31CA-19E1-9E56-C43AA21293967:29

[root@web01 ~]# cat /application/mysql/data/auto.cnf

[auto]

server-uuid=0b920fba-d0fa-11e7-aae4-000c292741de

注意：如果是克隆的mysql数据库，那么server-uuid相同会导致slave-IO无法启动，需要修改server-uuid

部署过程

环境：

需要两台mysql数据库服务器，一台为主服务器，一台为从服务器。

1、修改配置文件

主：

[mysqld]

log_bin= /tmp/log-bin

binlog-format =row

basedir= /application/mysql/datadir= /application/mysql/data

socket= /application/mysql/tmp/mysql.sock

server_id= 1gtid-mode =on #启用gtid类型，否则就是普通的复制架构

enforce-gtid-consistency = true#强制GTID的一致性

log-slave-updates = 1#slave更新是否记入日志

autocommit= 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

[client]

socket= /application/mysql/tmp/mysql.sock

```

从：

```shell

[mysqld]

log_bin= /tmp/log-bin

binlog-format=ROW

basedir= /application/mysql/datadir= /application/mysql/data/server_id= 2socket= /application/mysql/tmp/mysql.sock

gtid-mode =on

enforce-gtid-consistency = truelog_slave_updates= 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

autocommit= 1[client]

socket= /application/mysql/tmp/mysql.sock

注意：如果是新建的数据库可以不需要从库初始化；如果不是需要从库初始化，同步主从的结构属性

2、在主服务器添加复制用户

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

3、在从服务器上设置change master

mysql>change master to

master_host='10.0.0.51',

master_port=3306,

master_user='repl',

master_password='123',

master_auto_position=1

4、开启slave

start slave;

5、查看效果

在主库中添加一个数据，查看master

mysql>show master status;+----------------+----------+--------------+------------------+------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------+----------+--------------+------------------+------------------------------------------+

| log-bin.000003 | 552 | | | ff185ff4-cec5-11e7-9c86-000c2924dc94:1-2 |

+----------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.00 sec)

再从库中查看slave：

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:10.0.0.51Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: log-bin.000003Read_Master_Log_Pos:552Relay_Log_File: db02-relay-bin.000004Relay_Log_Pos:442Relay_Master_Log_File: log-bin.000003Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:552Relay_Log_Space:1252Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1Master_UUID: ff185ff4-cec5-11e7-9c86-000c2924dc94

Master_Info_File:/application/mysql-5.6.38/data/master.info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor the slave I/O thread to update it

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: ff185ff4-cec5-11e7-9c86-000c2924dc94:1-2Executed_Gtid_Set: ff185ff4-cec5-11e7-9c86-000c2924dc94:1-2Auto_Position:1

1 row in set (0.00 sec)