mysql long丢失精度 mysql丢失更新

转载

epeppanda 2023-09-07 14:13:13

文章标签 mysql long丢失精度 mysql 数据库 sql sed 文章分类 MySQL 数据库

一、前言

二、基于mysqlbinlog工具的误更新恢复

三、基于binlog2sql的误更新恢复

一、前言

上周五，应用侧反应一个表在update时由于使用了左外连接。被误更新，导致全表数据丢失。反馈问题时间是上午11：30，误更新时间是10：20左右，此MYSQL数据库业务量较大，一小时的binlog日志量在2G左右。

解决思路：首先需要对这个表进行锁表，避免再有其他的更新，增加恢复量。然后开始考虑使用mysqlbinlog对日志筛选、转换、回滚语句，但是将近4G的binlog需要处理，工作量巨大。后来听同事说有专门的binlog解析工具binlog2sql，基于Python编写的。于是网上搜索安装包，安装的过程中又遇到了一些权限和依赖的问题。好在最后成功安装了binlog2sql，生成了该表的全部回滚语句成功进行了恢复。但是由于之前没有Mysql误更新恢复的经验，第一次安装binlog2sql也遇到很多坑，导致核心业务表在误更新后下午18：00才恢复，造成业务长时间中断。于是梳理处理过程、模拟案例，希望下次遇到同样问题能缩短恢复时间。

二、基于mysqlbinlog工具的误更新恢复

1、测试环境

mysql> show variables like '%binlog_format%';   --binlog模式必须是ROW模式，否则无法恢复。
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | binlog_format | ROW   |
 +---------------+-------+
 1 row in set (0.00 sec)mysql> select version();
 +-----------+
 | version() |
 +-----------+
 | 8.0.26    |
 +-----------+
 1 row in set (0.00 sec)mysql>

2、测试数据

mysql> select count(*) from test;
 +----------+
 | count(*) |
 +----------+
 |    10000 |
 +----------+
 1 row in set (0.05 sec)mysql> desc test;
 +-----------+------+------+-----+---------+-------+
 | Field     | Type | Null | Key | Default | Extra |
 +-----------+------+------+-----+---------+-------+
 | emp_no    | int  | NO   |     | NULL    |       |
 | salary    | int  | NO   |     | NULL    |       |
 | from_date | date | NO   |     | NULL    |       |
 | to_date   | date | NO   |     | NULL    |       |
 +-----------+------+------+-----+---------+-------+
 4 rows in set (0.00 sec)mysql>

3、模拟误更新

mysql> show master status;
 +-----------------+----------+--------------+------------------+--------------------------------------------+
 | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
 +-----------------+----------+--------------+------------------+--------------------------------------------+
 | mysqlbin.000001 |      156 |              |                  | d83239cd-ce27-11ec-b08e-080027e2598e:1-186 |
 +-----------------+----------+--------------+------------------+--------------------------------------------+
 1 row in set (0.00 sec)mysql> flush logs;   --先切换下binlog
 Query OK, 0 rows affected (0.01 sec)mysql> show master status;
 +-----------------+----------+--------------+------------------+--------------------------------------------+
 | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
 +-----------------+----------+--------------+------------------+--------------------------------------------+
 | mysqlbin.000002 |      156 |              |                  | d83239cd-ce27-11ec-b08e-080027e2598e:1-186 |
 +-----------------+----------+--------------+------------------+--------------------------------------------+
 1 row in set (0.00 sec)mysql> 
 mysql> update employees.test set salary=20000;  --这里不会写左连接的更新，就用忘写where条件的误更新模拟
 Query OK, 10000 rows affected (0.09 sec)
 Rows matched: 10000  Changed: 10000  Warnings: 0

4、锁表

mysql> lock table employees.test read;  --先锁表
 Query OK, 0 rows affected (0.00 sec)mysql>

5、筛选binlog中的update记录

[root@test2 log]#  mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysqlbin.000002 | grep  'UPDATE `employees`.`test`' | more | wc -l
20000
 [root@test2 log]#  mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysqlbin.000002 | grep -C 10  'UPDATE `employees`.`test`' | more
 # at 262646
 #220613  9:25:59 server id 11  end_log_pos 270842 CRC32 0x0f4fcdb0      Update_rows: table id 81
 # at 270842
 #220613  9:25:59 server id 11  end_log_pos 279038 CRC32 0x57ac6088      Update_rows: table id 81
 # at 279038
 #220613  9:25:59 server id 11  end_log_pos 287234 CRC32 0x4c698df0      Update_rows: table id 81
 # at 287234
 #220613  9:25:59 server id 11  end_log_pos 295430 CRC32 0xc1e70954      Update_rows: table id 81
 # at 295430
 #220613  9:25:59 server id 11  end_log_pos 301706 CRC32 0xea6a387f      Update_rows: table id 81 flags: STMT_END_F
 ### UPDATE `employees`.`test`
 ### WHERE
 ###   @1=10001 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2=60117 /* INT meta=0 nullable=0 is_null=0 */
 ###   @3='1986:06:26' /* DATE meta=0 nullable=0 is_null=0 */
 ###   @4='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
 ### SET
 ###   @1=10001 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2=20000 /* INT meta=0 nullable=0 is_null=0 */
 ###   @3='1986:06:26' /* DATE meta=0 nullable=0 is_null=0 */
 ###   @4='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
 ### UPDATE `employees`.`test`
 ### WHERE
 ###   @1=10001 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2=62102 /* INT meta=0 nullable=0 is_null=0 */
 ###   @3='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
 ###   @4='1988:06:25' /* DATE meta=0 nullable=0 is_null=0 */
 ### SET
 ###   @1=10001 /* INT meta=0 nullable=0 is_null=0 */
 ###   @2=20000 /* INT meta=0 nullable=0 is_null=0 */
 ###   @3='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
 ###   @4='1988:06:25' /* DATE meta=0 nullable=0 is_null=0 */
 ### UPDATE `employees`.`test`
 ### WHERE
  。。。。

如果误更新后立马发现了，立即对该表进行锁表，没有其他的更新，则可以使用基于事务的，及根据binlog里面的更新开始前的‘# at 295430’ 关键字和事务结束的COMMIT关键字截取筛选
如果误更新后一段时间后发现了，这期间又有很多其他的更新，需要倒叙的方式根据‘# at xxx’ 依次回滚
这两种情况对于没有切换binlog日志的误更新很合适，如果误更新后没有立即发现，然后对误更新的表进行了很多其他的更新，而且还切换了binlog日志，这种情况就都不合适了，可以使用下面的第二种方法，binlog2sql的分析工具，自动生成所有的回滚语句。

由于我们在误更新后换进行了其他的更新所以需要使用基于表的。

6、对筛选出来的误更新记录进行处理

sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' update2.sql | sed 's/### //g;s/\/\*.*/,/g' | sed  /@4/s/,//g | sed '/WHERE/{:a;N;/@4/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > rollback.sql
 --这一步是把update语句提取出来，去掉多余的标识符  这里面的@4要替换成该表的最后一列，一般表有几列就是@几
 [root@test2 log]# more rollback.sql 
 UPDATE `employees`.`test`
 SET
   @1=10001 ,
   @2=60117 ,
   @3='1986:06:26' ,
   @4='1987:06:26' 
 WHERE
   @1=10001 AND
   @2=20000 AND
   @3='1986:06:26' AND
   @4='1987:06:26' 
 UPDATE `employees`.`test`
 SET
   @1=10001 ,
   @2=62102 ,
   @3='1987:06:26' ,
   @4='1988:06:25' 
 WHERE
   @1=10001 AND
   @2=20000 AND
   @3='1987:06:26' AND
   @4='1988:06:25' 
 UPDATE `employees`.`test`[root@test2 log]#  sed  -i -r  '/WHERE/{:a;N;/@4/!ba;s/(@4=.*)/\1\;/g}' rollback.sql  --这一步是在where后面加;,切割update语句。
 [root@test2 log]# more rollback.sql 
 UPDATE `employees`.`test`
 SET
   @1=10001 ,
   @2=60117 ,
   @3='1986:06:26' ,
   @4='1987:06:26' 
 WHERE
   @1=10001 AND
   @2=20000 AND
   @3='1986:06:26' AND
   @4='1987:06:26' ;
 UPDATE `employees`.`test`
 SET
   @1=10001 ,
   @2=62102 ,
   @3='1987:06:26' ,
   @4='1988:06:25' 
 WHERE
   @1=10001 AND
   @2=20000 AND
   @3='1987:06:26' AND
   @4='1988:06:25' ;[root@test2 log]# sed -i 's/@1/emp_no/g;s/@2/salary/g;s/@3/from_date/g;s/@4/to_date/g' rollback.sql --这一步是把@1、@2、@3等替换成列名
 [root@test2 log]# more rollback.sql 
 UPDATE `employees`.`test`
 SET
   emp_no=10001 ,
   salary=60117 ,
   from_date='1986:06:26' ,
   to_date='1987:06:26' 
 WHERE
   emp_no=10001 AND
   salary=20000 AND
   from_date='1986:06:26' AND
   to_date='1987:06:26' ;
 UPDATE `employees`.`test`
 SET
   emp_no=10001 ,
   salary=62102 ,
   from_date='1987:06:26' ,
   to_date='1988:06:25' 
 WHERE
   emp_no=10001 AND
   salary=20000 AND

7、解锁表

mysql> unlock tables;
 Query OK, 0 rows affected (0.00 sec)

8、执行回滚语句

mysql> source /data2/3301/log/rollback.sql
  Query OK, 0 rows affected (0.01 sec)
 Rows matched: 0  Changed: 0  Warnings: 0Query OK, 0 rows affected (0.01 sec)
 Rows matched: 0  Changed: 0  Warnings: 0Query OK, 0 rows affected (0.01 sec)
 Rows matched: 0  Changed: 0  Warnings: 0Query OK, 0 rows affected (0.01 sec)
 Rows matched: 0  Changed: 0  Warnings: 0Query OK, 0 rows affected (0.01 sec)
 Rows matched: 0  Changed: 0  Warnings: 0Query OK, 0 rows affected (0.01 sec)
 Rows matched: 0  Changed: 0  Warnings: 0

。。。。。

上述方法比较适合对表的一条或几条数据的误更新，如果是全表的误更新，更新后没有其他的更新，也合适。对于全表的误更新，误更新后还有其他的更新，数量较少还能勉强恢复。如果误更新后有多次其他的更新操作，或者切换了binlog日志，这种方法就不适合了，只能使用binlog2sql工具

三、基于binlog2sql的误更新恢复

1、安装binlog2mysql
1）该工具的使用依赖以下三个包：
PyMySQL==0.7.8
wheel==0.24.0
mysql-replication==0.9
其中，每个包又会依赖其它包，所以安装这些包是一个比较麻烦的事情。
2）外网环境安装
可直接通过pip install安装，它会自动下载并安装依赖包的。
2.1.1 安装binlog2sql前先安装git和pip：
yum -y install epel-release
yum -y install git python-pip
2.1.2 安装binlog2sql：
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
3）内网环境安装
可手动安装这些包，目前，这些包已下载打包，并上传到百度云盘中，大家可自行下载。
链接：https://pan.baidu.com/s/1lC3tEB27JSWAL3-ukr19oA
提取码：ange
安装步骤：
# tar xvf binlog2sql.tar.gz
# cd binlog2sql/binlog2sql_dependencies/
# tar xvf setuptools-0.6c11.tar.gz
# cd setuptools-0.6c11
# python setup.py install
# cd ..
# tar xvf pip-9.0.1.tar.gz
# cd pip-9.0.1
# python setup.py install
# cd ..
# pip install *.whl mysql-replication-0.9.tar.gz
4）安装遇到的问题

（1）权限问题，如果mysql是用root用户安装的，那么安装这些包使用root用户不会有问题，如果用mysql安装，则需要给mysql用户sudo的权限。
（2）mysql-replication包的版本问题，我的环境是mysql8.0 使用mysql-replication-0.9的版本已经不兼容了，于是下载了最新的mysql-replication。下载地址：https://pypi.org/project/mysql-replication/#files

2、模拟误删除

mysql> update test set salary=20000;    --误更新
 Query OK, 500 rows affected (0.04 sec)
 Rows matched: 500  Changed: 500  Warnings: 0mysql> update test set salary=salary+100;    --误更新后的其他更新
 Query OK, 500 rows affected (0.01 sec)
 Rows matched: 500  Changed: 500  Warnings: 0mysql> show master status;
 +-----------------+----------+--------------+------------------+------------------------------------------+
 | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
 +-----------------+----------+--------------+------------------+------------------------------------------+
 | mysqlbin.000002 |    30854 |              |                  | d83239cd-ce27-11ec-b08e-080027e2598e:1-4 |
 +-----------------+----------+--------------+------------------+------------------------------------------+
 1 row in set (0.00 sec)mysql> flush logs;    
 Query OK, 0 rows affected (0.06 sec)mysql> flush logs;    --切换了两次binlog
 Query OK, 0 rows affected (0.03 sec)mysql> show master status;
 +-----------------+----------+--------------+------------------+------------------------------------------+
 | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
 +-----------------+----------+--------------+------------------+------------------------------------------+
 | mysqlbin.000004 |      196 |              |                  | d83239cd-ce27-11ec-b08e-080027e2598e:1-4 |
 +-----------------+----------+--------------+------------------+------------------------------------------+
 1 row in set (0.00 sec)mysql>

3、生成恢复语句

建立恢复用户，并赋予权限

create  user  binlog2sql@'%' identified by 'binlog2sql';
 create  user  binlog2sql@'localhost' identified by 'binlog2sql';
 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'%';
 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  binlog2sql@'localhost';
 flush privileges;python binlog2sql.py -ubinlog2sql  -pbinlog2sql  -ddatabase_name   -t   table1 table2  --start-file='mysql-bin.000007' 
 --start-datetime='2019-12-xx 06:00:00' --stop-datetime='2019-12-xx 11:30:00'  >/tmp/binlog.txt    python binlog2sql.py  --flashback -h127.0.0.1 -P 3306   -ubinlog2sql -pbinlog2sql -ddatabase_name -t table1 table2 
 --start-file='mysql-bin.000007' --start-position=763 --stop-position=1147 >/tmp/rollback.txtbinlog2sql有很多参数可以选。可以基于时间筛选、可以基于binlog文件筛选、可以基于postition筛选等等。
 cd /soft/binlog2sql/binlog2sql
 生成redo语句
 python binlog2sql.py -ubinlog2sql  -pbinlog2sql  -demployees   -ttest --start-file='mysqlbin.000002' --stop-file='mysqlbin.000004' >/data2/3301/log/binlog.txt   
 生成回滚语句
 python binlog2sql.py  --flashback -h127.0.0.1 -P3306   -ubinlog2sql -pbinlog2sql -demployees   -ttest --start-file='mysqlbin.000002' --stop-file='mysqlbin.000004' >/data2/3301/log/rollback3.sql 生成成回滚语句时还遇到下面的错误：
 :
   File "binlog2sql.py", line 124, in <module>
     only_tables=args.tables, nopk=args.nopk, flashback=args.flashback, stopnever=args.stopnever)
   File "binlog2sql.py", line 38, in __init__
     self.connection = pymysql.connect(**self.connectionSettings)
   File "/usr/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect
     return Connection(*args, **kwargs)
   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 690, in __init__
     self.connect()
   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 939, in connect
     raise exc
 pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 111] Connection refused)")

是因为binlog2mysql默认的端口是3306，如果mysql使用了非默认端口，需要特殊指定

注意，执行binlog2sql时会访问msyql的数据字典，因此数据库必须是运行状态

4、执行恢复语句

mysql> source /data2/3301/log/rollback3.sql

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：python脚本控制手机 python怎么控制手机

下一篇：Python的函数不包括 python函数不包括参数函数

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯