• 周四. 4月 25th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

mysql主从恢复实战

admin

11月 28, 2021

mysql主从发生复制异常时,且从库binlog日志和主库目前日志序号相差很大,无法通过常规手段恢复数据库复制状态,可考虑通过备份主库重新搭建从库的方式进行异常恢复。

1.1.  从库恢复数据

通过备份工具可把主库的数据进行在线热备

###############mysqldump方式(直接生成SQL语句,在恢复的时候执行备份的SQL语句实现数据库数据的重现。逻辑备份其备份、还原慢,但备份文件占用的空间小

#1.备份

mysqldump -ubakuser -p123456  -h127.0.0.1 -P3310 –opt –hex-blob –single_transaction -R –default-character-set=utf8 –master-data=2 –all-databases>/temp/dbfull.sql

#2.恢复

mysql>source /temp/dbfull.sql;

###############xtrabakcup方式(拷贝相关数据文件。物理备份其备份还原快,备份文件占用空间大。

#1.备份

innobackupex –defaults-file=/data/mysql/my.cnf –user=root –password=******* /opt/idbbak/

#2.恢复

//–apply-log选项的命令是准备在一个备份上启动mysql服务

# innobackupex –defaults-file=/etc/my.cnf –user=root –apply-log /opt/idbbak/2019-05-18_16-35-12

//–copy-back 选项的命令从备份目录拷贝数据,索引,日志到my.cnf文件里规定的初始位置

# innobackupex –defaults-file=/etc/my.cnf –user=root –copy-back /opt/idbbak/2019-05-18_16-35-12

//文件目录授权之后 启动数据库

chown -R mysql.mysql /opt/data

备注:如果直接拷贝master的数据文件来建立slave,注意要删除auto.cnf,重启使MySQL重新生成一个新的server_uuid,否则复制将会异常

1.2.  配置主从关系

在线主从配置: 之后的操作都在从库上执行

#1.确认备份主库的 gtid位置

##mysqldump方式

#less /temp/dbfull.sql

备份当时gtid 已执行:

SET @@GLOBAL.GTID_PURGED=’9f39465a-7e30-11e9-8f85-fa163e5def9a:1-4′;

 

##xtrabackup方式

cat /opt/idbbak/2019-05-18_16-35-12/xtrabackup_info

GTID of the last change ‘9f39465a-7e30-11e9-8f85-fa163e5def9a:1-4’

 

#配置主从关系,登录从数据库

mysql>stop slave;

mysql>reset master;

mysql>SET @@GLOBAL.GTID_PURGED= ‘9f39465a-7e30-11e9-8f85-fa163e5def9a:1-4’

mysql>change master to

      master_host = ‘10.1.1.12’,

      master_port = 3310,

      master_user = ‘repl’,

      master_password = ‘********’,

      master_auto_position=1;

mysql>start slave;

mysql>show slave status;

命令查看下列参数值为

Slave_IO_Running:YES

Slave_SQL_Running:YES

1.3.  双主配置

前面搭建完主从之后,在主库上。(主库,从库上 都需要存在复制用户 如上; repl)

#配置主从关系,登录主数据库

mysql>stop slave;

mysql>reset slave;

mysql>change master to

      master_host = ‘10.1.1.13,

      master_port = 3310,

      master_user = ‘repl’,

      master_password = ‘********’,

      master_auto_position=1;

mysql>start slave;

mysql>show slave status;

命令查看下列参数值为

Slave_IO_Running:YES

Slave_SQL_Running:YES

1.4.  注意相关配置参数

#每个server服务的标识,在master/slave环境中,此变量一定要不一样

server-id=1

每个server服务的标识,在master/slave环境中,此变量一定要不一样

 

#制以什么格式记录二进制日志的内容

binlog_format=row

 

#设定所接受的包的大小

max_allowed_packet=1G

 

#队列中Pending的事件所占用的最大内存,默认为16M

#需要大于主库max_allowed_packet的大小

slave_pending_jobs_size_max=2G 

 

半同步参数

##主库my.cnf

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_enabled=1

 

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)

 

##从库 my.cnf

plugin-load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=1

 

mysql> show status like ‘Rpl_semi_sync_slave_status’;

+—————————-+——-+

| Variable_name              | Value |

+—————————-+——-+

| Rpl_semi_sync_slave_status | ON    |

+—————————-+——-+

1 row in set (0.20 sec)

【补充】mysql异步复制、半同步复制、同步复制的区别

异步复制(Asynchronous replication)

1、逻辑上

MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从库上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。

2、技术上

主库将事务 Binlog 事件写入到 Binlog 文件中,此时主库只会通知一下 Dump 线程发送这些新的 Binlog,然后主库就会继续处理提交操作,而此时不会保证这些 Binlog 传到任何一个从库节点上。

全同步复制(Fully synchronous replication)

1、逻辑上

指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。

2、技术上

当主库提交事务之后,所有的从库节点必须收到、APPLY并且提交这些事务,然后主库线程才能继续做后续操作。但缺点是,主库完成一个事务的时间会被拉长,性能降低。

半同步复制(Semisynchronous replication)

1、逻辑上

是介于全同步复制与全异步复制之间的一种,主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可,主库不需要等待所有从库给主库反馈。同时,这里只是一个收到的反馈,而不是已经完全完成并且提交的反馈,如此,节省了很多时间。

2、技术上

介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。

                                                                                                                                                                                                                                                                                        2021年7月21日

                                                                                                                                                                                                                                                                                                 祝好!

 

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注