linux差异备份mysql出错_MySQL备份可能遇到的坑

最新推荐文章于 2023-07-13 16:26:03 发布
最新推荐文章于 2023-07-13 16:26:03 发布
阅读量492 收藏
点赞数
文章标签： linux差异备份mysql出错
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39522103/article/details/113400600
版权
本文详细探讨了在Linux环境下使用mysqldump、mysqlpump、mydumper和XtraBackup进行MySQL差异备份时可能遇到的问题。在不同工具下，由于DML操作、并发写入和一致性控制的不同处理，可能导致备份数据与实际数据不一致，进而影响到后续的数据库恢复和复制结构的建立。文章通过实例展示了问题的产生原因，并提出了相应的解决建议。
摘要由CSDN通过智能技术生成

MySQL备份工具，支持各种参数选项，使用不同的选项极有可能影响备份处理过程。本文使用我们常规认为合理的备份参数，测试/验证是否存在容易忽视的坑

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

# 常规备份参数

# mysqldump

shell> mysqldump --single-transaction --master-data=2 -B replcrash >dbname_dump_serverid_`date +%Y%m%d`.sql

# mysqlpump

shell> mysqlpump --single-transaction -B replcrash >dbname_pump_serverid_`date +%Y%m%d`.sql

# mydumper

shell> mydumper -B replcrash -o /data/backup/mydumper

# XtraBackup

# backup

shell> innobackupex [--defaults-file=MY.CNF] BACKUP-ROOT-DIR

# apply-log

shell> innobackupex --apply-log [--defaults-file=MY.CNF] BACKUP-DIR

# copy-back

shell> innobackupex --copy-back [--defaults-file=MY.CNF] BACKUP-DIR

常规备份参数

官方社区版MySQL 5.7.19 基于Row+Position搭建的一主一从异步复制结构：Master->{Slave}

ROLE

HOSTNAME

BASEDIR

DATADIR

IP

PORT

Master

ZST1

/usr/local/mysql

/data/mysql/mysql3306/data

192.168.85.132

3306

Slave

ZST1

/usr/local/mysql

/data/mysql/mysql3308/data

192.168.85.132

3308

每次使用备份文件还原数据库后，重新搭建这个复制结构

备份工具版本：mysqldump、mysqlpump是MySQL 5.7.19中自带的；mydumper version 0.9.3、innobackupex version 2.4.8

一、mysqldump

1.1、DML操作对备份的影响

创建两张测试表

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

# 创建两张测试表(192.168.85.132,3306)usereplcrash;create tablepy_user_innodb(

uidint not nullauto_increment,

namevarchar(32),

add_timedatetime default current_timestamp,

server_idvarchar(10),primary key(uid),key(name)

)engine=innodb;create tablepy_user_myisam(

uidint not nullauto_increment,

namevarchar(32),

add_timedatetime default current_timestamp,

server_idvarchar(10),primary key(uid),key(name)

)engine=myisam;

View Code

运行下面的脚本持续往测试表中写入数据

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

#!/user/bin/python

importstringimportrandomimportMySQLdbimporttime

conn= MySQLdb.connect(host='192.168.85.132',

port=3306,

user='mydba',

passwd='mysql5719',

db='replcrash')"""create table py_user(

uid int not null auto_increment,

name varchar(32),

add_time datetime default current_timestamp,

server_id varchar(10),

primary key(uid),

key(name)

);"""

whileTrue:

r_name= ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(random.randint(20,30)))printr_name

cursor=conn.cursor()

cursor.execute("insert into py_user_myisam(name,add_time,server_id) values('%s',now(),@@server_id);" %str(r_name))

cursor.execute("insert into py_user_innodb(name,add_time,server_id) values('%s',now(),@@server_id);" %str(r_name))

conn.commit()

time.sleep(0.001)

Python DML

开启general_log，用来查看mysqldump执行过程

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

# 开启general_log

mydba@192.168.85.132,3306 [replcrash]> set global general_log_file='/data/mysql/mysql3306/data/mysql-general.log';

mydba@192.168.85.132,3306 [replcrash]> set global general_log=1;

# 清空general_log

[root@ZST1 logs]#cat /dev/null > /data/mysql/mysql3306/data/mysql-general.log

# 备份replcrash数据库

[root@ZST1 backup]# mysqldump-h127.0.0.1 -P3306 -uroot -p --single-transaction --master-data=2 replcrash >/data/backup/replcrash_dump_1323306_`date +%Y%m%d`.sql

View Code

使用备份文件搭建复制

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

# 还原实例清空GTID信息

mydba@192.168.85.132,3308 [replcrash]>reset master;

# 还原数据

[root@ZST1 backup]# mysql-h127.0.0.1 -P3308 -uroot -p replcrash

# 搭建复制

mydba@192.168.85.132,3308 [replcrash]>change master to

master_host='192.168.85.132',

master_port=3306,

master_user='repl',

master_password='repl',

master_auto_position=1;

# 启动复制，查看复制状态

mydba@192.168.85.132,3308 [replcrash]>start slave;

mydba@192.168.85.132,3308 [replcrash]>show slave status\G*************************** 1. row ***************************Master_Log_File: mysql-bin.000183Read_Master_Log_Pos:1541377Relay_Log_File: relay-bin.000002Relay_Log_Pos:741Relay_Master_Log_File: mysql-bin.000183Slave_IO_Running: Yes

Slave_SQL_Running: No

Exec_Master_Log_Pos:1042768Last_SQL_Errno:1062Last_SQL_Error: Could not execute Write_rows event on table replcrash.py_user_myisam; Duplicate entry'332' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event''s master log mysql-bin.000183, end_log_pos 1043062Retrieved_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:251874-253268Executed_Gtid_Set: 8ab82362-9c37-11e7-a858-000c29c1025c:1-251874Auto_Position:1

View Code

从上面的结果中可以看到，主键冲突了，在从库查询一下这个表中大于等于冲突key的数据

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

# 查询从库出错表大于等于冲突key的数据

mydba@192.168.85.132,3308 [replcrash]> select * from replcrash.py_user_myisam where uid>=332;+-----+--------------------------------+---------------------+-----------+

| uid | name | add_time | server_id |

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

| 332 | X1LME9HO5V7WXNOKBVZE | 2018-01-02 09:05:07 | 1323306 |

| 333 | 2PBFQ7KS4BPIJ27G88EYXWEDSX5 | 2018-01-02 09:05:07 | 1323306 |

| 334 | E85Y2SS9UD0FZG4YGCNTRSWA8L | 2018-01-02 09:05:07 | 1323306 |

| 335 | Y2TQOEVJ58NN7EREL4WRZ | 2018-01-02 09:05:07 | 1323306 |

| 336 | O0MEATAXYIAE2V2IZG96YVQ56WEUHF | 2018-01-02 09:05:07 | 1323306 |

| 337 | A6QKRWEXHRGUA3V2CH61VXUNBVA3H2 | 2018-01-02 09:05:07 | 1323306 |

| 338 | NYCSI1HS61BN6QAVVYTZSC | 2018-01-02 09:05:07 | 1323306 |

| 339 | 7CFC1JQPIQGNC97MDTT8ZIMIZL7D | 2018-01-02 09:05:07 | 1323306 |

| 340 | GA78AR4Z12WQTEAM41JB | 2018-01-02 09:05:07 | 1323306 |

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

9 rows in set (0.08 sec)

View Code

我们查看mysqldump备份文件获取的binlog pos

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[root@ZST1 backup]# morereplcrash_dump_1323306_20180102.sql--GTID state at the beginning of the backup

SET @@GLOBAL.GTID_PURGED='8ab82362-9c37-11e7-a858-000c29c1025c:1-251873';-- Position to start replication or point-in-timerecovery from-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000183', MASTER_LOG_POS=1042441;

[root@ZST1 backup]#

View Code

这里的pos信息是mysqldump通过SHOW MASTER STATUS获取。查看mysqldump得到的general-log;

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[root@ZST1 data]# vim /data/mysql/mysql3306/data/mysql-general.log

...2018-01-02T01:05:07.693104Z 10 Query FLUSH /*!40101 LOCAL*/TABLES2018-01-02T01:05:07.694738Z 9 Query insert into py_user_myisam(name,add_time,server_id) values('7ATZSNFNIBW5DZNMNZYBMV',now(),@@server_id)2018-01-02T01:05:07.701616Z 9 Query insert into py_user_innodb(name,add_time,server_id) values('7ATZSNFNIBW5DZNMNZYBMV',now(),@@server_id)2018-01-02T01:05:07.702139Z 10Query FLUSH TABLES WITH READ LOCK2018-01-02T01:05:07.702344Z 9Query commit2018-01-02T01:05:07.702411Z 10Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2018-01-02T01:05:07.702597Z 10 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT*/

2018-01-02T01:05:07.702721Z 10 Query SHOW VARIABLES LIKE 'gtid\_mode'

2018-01-02T01:05:07.713019Z 10Query SELECT @@GLOBAL.GTID_EXECUTED2018-01-02T01:05:07.713179Z 10Query SHOW MASTER STATUS2018-01-02T01:05:07.725821Z 10Query UNLOCK TABLES2018-01-02T01:05:07.732125Z 9 Query insert into py_user_myisam(name,add_time,server_id) values('X1LME9HO5V7WXNOKBVZE',now(),@@server_id)2018-01-02T01:05:07.733237Z 9 Query insert into py_user_innodb(name,add_time,s

最低0.47元/天 解锁文章
weixin_39522103
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【MySQL】ERROR 1412 (HY000) Table definition has changed, please retry transaction
九师兄
07-19 5048
1.概述 我们在两个MySQL的session中，在A session中，创建一个表 设置不自动提交 mysql> set autocommit = 0; 然后在B session中也设置不自动提交，而且建立了一个表 mysql> set autocommit = 0; mysql> create table table_2 (id int,name varchar(10)); Query OK, 0 rows affected (0.04 sec) mysql> inser.
linux centos MySql备份和恢复方案.zip
08-14
综上所述，"linux centos MySql备份和恢复方案.zip" 文件可能包含了如何在Linux CentOS系统上有效实施MySQL备份和恢复的详细步骤、最佳实践以及可能的脚本或配置示例。通过学习和应用这些方案，可以确保您的MySQL...
mysql Error 1412: Table definition has changed, please retry transaction
Murinedj的博客
11-08 3966
执行语句“select * from rizhi.XXX_a_XXX into outfile “D:\a.txt”;”突然报错注意，是执行突然报错，上次执行该语句没有报错，文件导出正常。
执行查询报错：Cause: java.sql.SQLException: Table definition has changed, please retry transaction ； uncate
liang1103的专栏
07-13 1209
同时select * from innodb_trx可以看出连接未释放（至于连接未释放，是因为commit的时候没有指定参数true，这依赖于mysql的默认行为）。，它导致了语句缓存，中间执行了truncate某个分区（另外一个连接的），但是原先的mapper未失效，故发生该问题。解决方法：SqlSession重新getMapper（因为为了效率，我们mybatis配置默认的执行器为REUSE），即不会出现该问题。开始查询报错表不存在，因为表名写错了，重新建了表，再查询报错。
这 10 个 MySQL 经典错误，遇到过才是老手
光线技术博客
05-18 383
今天就给大家列举 MySQL 数据库中，最经典的十大错误案例，并附有处理问题的解决思路和方法，希望能给刚入行，或数据库爱好者一些帮助。 今后再遇到任何报错，我们都可以很淡定地去处理。学习任何一门技术的同时，其实就是自我修炼的过程。沉下心，尝试去拥抱数据的世界！ Top 1：Too many connections（连接数过多，导致连接不上数据库，业务无法正常进行） 问题还原 mysql> show variables like '%max_connection%'; | Var
Mysql锁-锁类型
qq_35519587的博客
04-26 658
根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。 全局锁 顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。 当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和...
[文件数据]MYSQL全系列自导入数据库备份程序 v1.0_faisunsql.zip
最新发布
03-18
常见的备份类型有完整备份、增量备份和差异备份。全系列自导入数据库备份程序可能涵盖了这些备份类型，让用户可以根据需求选择合适的备份策略。 3. **自动化备份**：自动备份功能可以定期执行备份任务，无需人工...
mysql配置文件my.cnf
01-20
1. **备份**：升级MySQL时，原有配置可能不适应新版本，因此备份`my.cnf`是必要的，防止配置丢失或出错。 2. **修改**：根据系统负载和需求调整参数。例如，增加`max_connections`应对高并发，增大`innodb_buffer_...
navicat for mysql
08-28
【Navicat for MySQL：一款强大的数据库管理工具】 ...通过使用Navicat，用户可以更高效地管理数据库，提升工作效率，同时降低出错的可能性。如果你是MySQL的使用者，那么Navicat for MySQL绝对值得你尝试。
navicat8lite_mysql_cs_lemao.rar数据库图形化工具
01-09
《Navicat8Lite_mysql_cs_lemao：数据库图形化管理工具详解》 在IT行业中，数据库管理是一项至关重要的任务，而高效的工具则能够极大提升工作效率。Navicat8Lite_mysql_cs_lemao是一款专为MySQL数据库设计的图形化...
mysql的备份与恢复（方案一）
woody_0011的博客
12-24 748
一、 mysqldump备份结合binlog日志恢复 MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。 1、binlog介绍 mysql的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启binlog)，还包括了这
Mysql主从复制
Warren Blog
12-09 547
Mysql传统主从复制 参考文章 官方文档：https://dev.mysql.com/doc/refman/5.7/en/replication.html 深入Mysql复制: https://www.cnblogs.com/f-ck-need-u/p/9155003.html sync_binlog字段解释: https://www.cnblogs.com/Cherie/p/330950...
mysql实战45讲---6 全局锁和表锁
kevin_pull的专栏
06-28 405
6全局锁和表锁 数据库的锁设计是为了处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源访问的规则，而锁就是用来实现这些访问规则的重要数据结构。 根据加锁的范围，mysql里面的锁大致可以分为全局锁、表锁和行锁三类。 全局锁 全局锁就是对整个数据库实例加锁，mysql提供了一个加全局读锁的方法，命令是flush tables with read lock(F...
mysql回滚update_MySQL【Update误操作】回滚
weixin_30542979的博客
01-19 2033
mysql5.7binlog/*!*/;#at15937710#at15937814#17052613:00:15serverid1end_log_pos15938129CRC320x06901892Table_map:`service`.`apply`mappedtonumber108#at15938129#17052613:00:15server...
polardb-mysql慢查询报错1412
white_while的博客
02-18 644
慢查询会报错ERROR 1412 (HY000) Table definition has changed, please retry transaction 解决方式：暂时 升级配置 优化慢查询 关闭只读实例 最终阿里云修复发布hotfix版本
MySQl binlog：row event释疑
weixin_34126557的博客
12-08 125
上周同事问我一个SQL在主库转成ROW格式的binlog发送到备库了，现在要找到这条delete，然而通过mysqlbinlog dump出来发现有“好多条”这个delete 语句，大概如下：#at 219#131203xx server id 1 end_log_pos 219 Table_map: xx mapped to number 35#131203xx serve...
在线添加索引遇到的错误：Table definition has changed, please retry transaction
weixin_30865427的博客
03-27 3345
现象：在线上环境，一条慢查询没有用到索引，表大小适中，加索引实际在3-5S内，决定在线添加。 mysql版本：5.1.56-community-log ,plugin innodb版本：1.0.15。 在添加索引的过程中，有原来的慢查询对此表进行访问，程序端返回错误： "1412: Table definition has changed, please retry transactio...
MySQL备份可能遇到的坑
06-08
备份 MySQL 数据库时可能会遇到以下问题： 1. 备份时出现错误：备份 MySQL 数据库时可能会遇到各种错误，例如无法连接到数据库、备份文件过大等等。在备份前，最好先检查数据库连接是否正常，备份的路径是否正确，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值