主从复制管理和故障处理方法

1、二进制日志管理回顾

1.1 如何进行配置

server_id=7

log_bin=/data/mysql/mysql-bin    ---》/data/mysql/mysql-bin.000001

binlog_format=row                ---》RBR

sync_binlog=1                    ---》每次事务提交都保证日志落地

1.2 

show  master status;

show binary logs;

show binlog events in 'mysql-bin.000001';

show relaylog events in 'mysql-relay.000058' from 208143277 limit 100;

mysqlbinlog 

###跳过一个事务

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 

;start slave ;

show slave status \G;

查看正在执行的事务

select trx_id,trx_started,trx_wait_started,trx_mysql_thread_id,trx_query,trx_isolation_level from  information_schema.INNODB_TRX;

文件:

M:

binlog:记录主库的数据变化

S:

relaylog:中继日志,存储从主库请求的二进制日志的存储位置

master.info:存储用户,密码,IP,port,记录上次请求过的binlog位置

relay-log.info:记录了上次SQL线程执行过的relaylog的位置点

线程:

M:

dump(IO)thread(投递线程):

主库发从二进制日志给从库的线程

S:

IO thread:

请求binlog,接收binlog的线程

SQL thread:

执行relay日志的线程

主从复制原理文字说明:

1.

(1)从库IO线程,查看master.info信息,获取IP,port,user,password,file,pos

(2)通过 IP,port,user,password,连接到主库.

(3)拿着 file(mysql-bin.000003),pos(120),请求主库

2.主库判断如果有新的binlog(mysql-bin.000003,800)

3.通过Dump线程读取binlog,从3号文件的120开始发送二进制日志事件

4.从库IO线程,接收binlog日志

5.缓存binlog到TCPIP缓存

6.IO线程回复一个ACK确认给dump线程,主库收到后,主库此次复制工作就完成了.

7.更新master.info文件,file,pos被更新为最新请求的值

8.TCPIP缓存数据,写入relay-log中

9.SQL线程,读取relay-log.info,获取到上次已经执行过的位置信息

10.到relay-log找最新的日志进行执行

11.再次更新relay-log.info

12.已经应用过的relay-log,会被自动清理

主库:

show processlist;

show master status ;

从库线程状态:

show slave status\G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5.线程的管理：

(1)启动和关闭两个线程

stop  slave;

start slave;

(2)单独启动关闭一个线程

stop  slave io_thread;

stop  slave sql_thread;

5、 主从复制故障 ******

5.1 IO线程故障

作用及故障原因:

(1)连接master

user ,password ,ip ,port

网络不通,防火墙

master没启动

master连接数上限

master压力太大

skip-name-resolve(主从在一台机器)

(2)请求,接收binlog

binlog文件

找不到

损坏

断节

故障模拟:

主库:

reset master ;

create database asdasd;

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000003' at 248, the last event read from '/data/3307/data/mysql-bin.000003' at 248, the last byte read from '/data/3307/data/mysql-bin.000003' at 248.'

处理方案:

stop slave;

reset slave all;

使用备份恢复,重新初始化数据.

CHANGE MASTER TO

MASTER_HOST='10.0.0.11',

MASTER_USER='repl',

MASTER_PASSWORD='123',

MASTER_PORT=3307,

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=154,

MASTER_CONNECT_RETRY=10;

start slave;

(3)日志写入relay-log

(4)读写master.info

5.2 SQL线程故障

(1)读写relay-log.info

(2)relay-log损坏,断节,找不到

(3)接收到的SQL无法执行

1.要创建的数据库对象,已经存在

2.要删除或修改的对象不存在

3.DML语句不符合表定义及约束时.

归根揭底的原因都是由于从库发生了写入操作.

以下是有风险的操作:

处理方法(以从库为核心的处理方案)：

stop slave; #<==临时停止同步开关。

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

start slave;

/etc/my.cnf

slave-skip-errors = 1032,1062,1007

但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。

把握一个原则,一切以主库为主.

一个万金油的解决方案:将从库设定为只读库.

命令行:

set global read_only=1;

永久设置:

/etc/my.cnf

read_only=1

以上权限只能对普通用户生效.

管理员read_only

innodb_read_only=1

4.3 主从复制延时过高 *******

主库做了一个变更,从库很久才追上

Seconds_Behind_Master: 0

(1)主库写binlog不及时

控制binlog从内存写入磁盘的控制开关

每次事务提交都立即刷新binlog到磁盘(双一标准中的其一)

sync_binlog=1

每次事务提交不立即写入磁盘,靠操作系统判断什么时候写入

sync_binlog=0

(2)dump线程压力大

从库越多,压力越大

(3)IO线程阻塞

大事务------>拆成小事务

事务量大---->group commit

(4)SQL线程慢(Classic replication)

1.默认只有一个SQL线程,从库中的事务都是一个一个来执行的

2.如果主库的

①并发事务数很多

②大事务

都会造成从库延时

5.6 多线程复制(多sql线程),有局限性,针对不同库的事务进行并发,在有些情况下可以解决①

大事务问题,只能在主库方面,将大事务拆成小事务

show slave status\G

Seconds_Behind_Master:

普通的主从复制可能存在不足

1、 逻辑损坏怎么办？

延时从库:从库落后于主库一段时间.

SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行

一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 300;

mysql>start slave;

mysql> show slave status \G

SQL_Delay: 300

2. 主库误操作,怎么使用延时从库

(0)停止主库业务.

(1)立即停止从库SQL线程

stop slave sql_thread;

(2)手工模拟sql线程工作,并截止到误操作之前

    读取relay-log.info ,获取到上次执行到的位置,作为继续执行relay-log的起点

    分析relay-log内容,获取到误操作的位置点.

    截取这段日志,恢复到从库.

(3)切为主库

3.故障模拟:

(1)模拟数据及故障

 mysql -S /data/3307/mysql.sock 

 create database delay charset utf8;

 use delay ;

 create table t1 (id int);

 insert into t1 values(1),(2),(3);

 commit;

 drop database delay;

(2)停从库的sql线程

stop slave sql_thread;

(3) 手工模拟sql线程工作,并截止到误操作之前

    1.读取relay-log(show slave status\G)

    Relay_Log_File: db01-relay-bin.000002

    Relay_Log_Pos: 283

    2.找到误删除的位置

    show relaylog events in 'db01-relay-bin.000002'

    db01-relay-bin.000002 | 693 | drop database delay  

    3.截取relaylog

    mysqlbinlog --start-position=283 --stop-position=693 db01-relay-bin.000002>/tmp/relay.sql

    4.恢复relaylog

    source /tmp/relay.sql

主从复制——delayed（延时从库）

会专门找一个节点，配置成延时节点，尽可能防止逻辑损坏，一般情况下这个节点会被用备份

我们配置的是SQL_thread的延时

生产环境中一般设置在3-6小时。

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 300;

mysql>start slave;

mysql> show slave status \G

SQL_Delay: 300

如何使用延时从库，解决数据库逻辑损坏？

模拟故障：

1、连接到主库,误删除

mysql -uroot -p123 -S /data/3307/mysql.sock

drop database ffd;

2、停止主库

mysqladmin -uroot -p123 -S /data/3307/mysql.sock shutdown 

3、停止从库SQL线程

stop slave sql_thread;

4、截取relay-log到drop之前的位置

4.1 起始点获取

[root@db01 data]# cat relay-log.info 

./db01-relay-bin.000003

378

4.2 结束点

mysql> show relaylog events in 'db01-relay-bin.000003';

mysqlbinlog --start-position=378 --stop-position=473  db01-relay-bin.000003 >/tmp/relay.sql

5、恢复数据

mysql> set sql_log_bin=0;

mysql> source /tmp/relay.sql

6、从库替代主库

mysql -uroot -p123 -S /data/3308/mysql.sock

stop slave;

reset slave all;

------------------------------------

半同步复制

出发点是保证主从数据一致性的问题，安全的考虑

5.5 出现的概念，但是不建议使用，性能太差

5.6以后出现group commit 组提交功能，来提升开启版同步复制的性能

5.7  更加完善了，在group commit基础上出现了MGR

5.7的增强半同步复制的新特性：after commit;  after sync;

------

加载插件

主:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从:

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

查看是否加载成功:

show plugins;

启动:

主:

SET GLOBAL rpl_semi_sync_master_enabled = 1;

从:

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重启从库上的IO线程

STOP SLAVE IO_THREAD;

START SLAVE IO_THREAD;

查看是否在运行

主:

show status like 'Rpl_semi_sync_master_status';

从:

show status like 'Rpl_semi_sync_slave_status';    

主从复制高级功能

1.半同步复制：

功能：尽可能保证主从数据一致性问题。牺牲主库一定的业务性能,commit

实现过程：

保证IO线程将日志从TCPIP缓存，写入到relaylog才会返回ACK给主库。

会阻塞主库的commit操作，这里会有个超时时间，10秒钟，如果从库还没返回ACK，强制切换为异步复制过程。

1.1 加载插件

主:

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从:

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

查看是否加载成功:

show plugins;

1.2 启动:

主:

SET GLOBAL rpl_semi_sync_master_enabled = 1;

从:

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

1.3 重启从库上的IO线程

STOP SLAVE IO_THREAD;

START SLAVE IO_THREAD;

1.4 查看是否在运行

主:

show status like 'Rpl_semi_sync_master_status';

从:

show status like 'Rpl_semi_sync_slave_status';

-----------------------------------

2、过滤复制

2.1 主库方面控制

" gjl [world]>show master status ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |     2691 |              |                  |                   |

Binlog_Do_DB=白名单 ，在此参数中的库，记录二进制日志

Binlog_Ignore_DB=黑名单 ，在此参数中的库，不记录二进制日志

2.2 从库方面控制

Replicate_Do_DB:  白名单，在此参数中的库，复制

Replicate_Ignore_DB: 黑名单，在此参数中的库，不复制

Replicate_Do_Table: 白名单，在此参数中的表，复制

Replicate_Ignore_Table: 黑名单，在此参数中的表，不复制

模糊的表名字

Replicate_Wild_Do_Table: wolrd.t*

Replicate_Wild_Ignore_Table: 

写法：

vim /data/3308/my.cnf

replicate_do_db=world

replicate_do_db=db1

环境准备:

0.关闭原有3306数据库,并清理/application/mysql/data下所有数据

pkill mysqld 

rm -rf /application/mysql/data/*

1. 

备份三台机器/etc/my.cnf 文件 

mv /etc/my.cnf /tmp

2.书写配置文件:

master: 10.0.0.11

vim /etc/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/application/mysql/data

socket=/tmp/mysql.sock

log-error=/var/log/mysql.log

log_bin=/data/mysql/mysql-bin

binlog_format=row

skip-name-resolve

server-id=11

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[client]

socket=/tmp/mysql.sock

slave1：10.0.0.12

vim /etc/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/application/mysql/data

socket=/tmp/mysql.sock

log-error=/var/log/mysql.log

log_bin=/data/mysql/mysql-bin

binlog_format=row

skip-name-resolve

server-id=12

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[client]

socket=/tmp/mysql.sock

slave2：10.0.0.13

vim  /etc/my.cnf

[mysqld]

basedir=/application/mysql

datadir=/application/mysql/data

socket=/tmp/mysql.sock

log-error=/var/log/mysql.log

log_bin=/data/mysql/mysql-bin

binlog_format=row

skip-name-resolve

server-id=13

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

[client]

socket=/tmp/mysql.sock

3.重新初始化三台机器数据

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql

4. 分别启动三台数据库服务器

/etc/init.d/mysqld start

3. GTID

3.1 介绍

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

它的官方定义如下：

GTID = source_id ：transaction_id

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

3.2 什么是sever_uuid，和Server-id 区别？

source_id 也叫uuid   默认在是第一次启动数据库时，自动生成的

/application/mysql/data/auto.cnf   

手工删除掉此文件，重启数据库，可以生成新的。

3.3 重要参数：

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

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

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

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

3.4 基于GTID的复制构建

master:11

slave:12,13

11:

reset master;

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

12\13:

reset master;

change master to master_host='10.0.0.11',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;

start slave;

来自为知笔记(Wiz)