SQL 第一话·操作问题及解决案例

最新推荐文章于 2023-05-05 11:46:55 发布

if 0 = -I can

最新推荐文章于 2023-05-05 11:46:55 发布

阅读量877

点赞数 1

分类专栏： MySQL 文章标签： SQL误操作

本文链接：https://blog.csdn.net/weixin_42018518/article/details/88877969

版权

MySQL 专栏收录该内容

31 篇文章 4 订阅

订阅专栏

写在最前：

一、MySQL版本：

1.执行更新操作（如如此不带where更新语句）

2.在mysql中执行以下命令，即可查看二进制日志文件的列表：

8.跳过故障SQL，继续执行binlog，完成恢复。

9.查看是否恢复：

二、MySQL版本：

1.执行更新操作

2.在mysql中执行以下命令，即可查看二进制日志文件的列表：

4.查看xtrabackup_slave_info文件，提取其中的master_log_file和master_log_pos信息，然后在目标库上进行change master to操作：

`写在最前：`

一定要注意MySQL版本，binlog日志模式，以及是否启用GTID。

一定要和最近的备份配合使用，一般情况下先恢复备份，再利用备份之后的binlog日志进行恢复。

`一、MySQL版本：`

mysql> select version();

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

| version() |

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

| 5.6.16-log |

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

1 row in set (0.00 sec)

`1.执行更新操作（如如此不带where更新语句）`

update t_comment_thread set content = '\s \r\n test ';

`2.在mysql中执行以下命令，即可查看二进制日志文件的列表：`

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000005 | 7849709 |

| mysql-bin.000006 | 3958641 |

| mysql-bin.000007 | 3961779 |

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

3 rows in set (0.00 sec)

`3.查看binlog日志模式`

mysql> show variables like '%binlog_format%';

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

| Variable_name | Value |

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

| binlog_format | MIXED |

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

`4.查看是否开启全局事物ID（GTID）`

mysql> show variables like '%gtid%';

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

| Variable_name | Value |

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

| enforce_gtid_consistency | OFF |

| gtid_executed | |

| gtid_mode | OFF |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

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

6 rows in set (0.00 sec)

`5.查找误操作执行的时间点`

# mysqlbinlog --start -d higo_comment mysql-bin.000005

mysqlbinlog: unknown variable 'default-character-set=utf8mb4'

原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个指令。

# mysqlbinlog --no-defaults -d higo_comment mysql-bin.000007

SET @@session.character_set_client=33,@@session.collation_connection=83,@@session.collation_server=83/*!*/;

BEGIN

/*!*/;

# at 1980381

#180514 15:17:53 server id 203303306 end_log_pos 1980527 CRC32 0x7cf96b57 Query thread_id=6546 exec_time=0 error_code=0

SET TIMESTAMP=1526282273/*!*/;

update t_comment_thread set content='\s \r \n test'

/*!*/;

# at 1980527

#180514 15:17:53 server id 203303306 end_log_pos 1980558 CRC32 0x57f99b40 Xid = 132289

COMMIT/*!*/;

# at 1980558

#180514 15:19:49 server id 203303306 end_log_pos 1980658 CRC32 0x3ef52424 Query thread_id=6546 exec_time=0 error_code=0

SET TIMESTAMP=1526282389/*!*/;

BEGIN

/*!*/;

# at 1980658

# at 1980690

#180514 15:19:49 server id 203303306 end_log_pos 1980690 CRC32 0x5f88517c Intvar

SET INSERT_ID=17387/*!*/;

#180514 15:19:49 server id 203303306 end_log_pos 1981057 CRC32 0x4a9e46aa Query thread_id=6546 exec_time=0 error_code=0

SET TIMESTAMP=1526282389/*!*/;

INSERT INTO t_comment_thread SET topic_id = '3489', account_id = '261686155106473944', reply_id = '0', reply_account_id = '0', content = '222222', anonymous = '0', device_id = '', ip = '0', extra = '[]', sticky_order = '0', status = '1', report_status = '0', operator = ''

/*!*/;

# at 1981057

#180514 15:19:49 server id 203303306 end_log_pos 1981088 CRC32 0xa8facb78 Xid = 132293

COMMIT/*!*/;

DELIMITER ;

# End of log file

`6.备份相关日志文件`

# mysql-bin.000007 mysql-bin.000007.bak(如果涉及到需要恢复全备，请备份全备后的所有binlog日志)

`7.可以使用以下语句将数据恢复到故障前`

# mysqlbinlog --no-defaults --stop-datetime='2018-05-14 15:17:53' -d higo_comment mysql-bin.000005 | mysql -uroot -pxxxxx -h127.0.0.1 -P3306

Warning: Using a password on the command line interface can be insecure.

`8.跳过故障SQL，继续执行binlog，完成恢复。`

# mysqlbinlog --no-defaults -d higo_comment --start-datetime='2018-05-14 15:19:49' mysql-bin.000007.bak | mysql -uroot -pxxxxx -h127.0.0.1 -P3306

`9.查看是否恢复：`

mysql> select id,content from t_comment_thread order by id desc \G;

*************************** 1. row ***************************

id: 17387

content: 222222

*************************** 2. row ***************************

id: 17203

content: 好想出柜，好难过

2 row in set (0.01 sec)

. . . . .

`二、MySQL版本：`

mysql> select version();

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

| version() |

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

| 5.7.16-log |

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

1 row in set (0.00 sec)

`1.执行更新操作`

update t_comment_thread set content = '\s \r\n test ';

`2.在mysql中执行以下命令，即可查看二进制日志文件的列表：`

mysql> show binary logs;

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

| Log_name | File_size |

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

| log_bin.000001 | 154 |

| log_bin.000002 | 1013 |

| log_bin.000003 | 1451 |

| log_bin.000004 | 5399054 |

| log_bin.000005 | 2471302 |

| log_bin.000006 | 194 |

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

6 rows in set (0.00 sec)

`3.查看binlog日志模式：`

mysql> show variables like '%binlog_format%';

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

| Variable_name | Value |

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

| binlog_format | ROW |

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

1 row in set (0.01 sec)

`4.查看是否开启全局事物ID（GTID）`

mysql> show variables like '%gtid%';

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

| Variable_name | Value |

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

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

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

8 rows in set (0.00 sec)

`5.查找误操作执行的位置点:`

# mysqlbinlog --no-defaults log_bin.000006

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180513 18:45:55 server id 330602022 end_log_pos 123 CRC32 0x3c73ef07 Start: binlog v 4, server v 5.7.16-log created 180513 18:45:55

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

Yxf4Wg8mlrQTdwAAAHsAAAABAAQANS43LjE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AQfvczw=

'/*!*/;

# at 123

#180513 18:45:55 server id 330602022 end_log_pos 194 CRC32 0x8603b990 Previous-GTIDs

# 6440331e-54c8-11e8-885c-b083fec202a4:1-26

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Row模式下解析binlog日志

[root@yz-dbinf-00 binlogs]# mysqlbinlog --no-defaults --base64-output="decode-rows" -vv log_bin.000006

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180513 18:45:55 server id 330602022 end_log_pos 123 CRC32 0x3c73ef07 Start: binlog v 4, server v 5.7.16-log created 180513 18:45:55

# Warning: this binlog is either in use or was not closed properly.

# at 123

#180513 18:45:55 server id 330602022 end_log_pos 194 CRC32 0x8603b990 Previous-GTIDs

# 6440331e-54c8-11e8-885c-b083fec202a4:1-26

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

由于基于ROW的不同于MIXED模式，文件比较大，所以将日志导入到文件中，然后查找位置点

# mysqlbinlog --no-defaults --base64-output='decode-rows' -vv --skip-gtids -d higo_comment log_bin.000006 -r /tmp/xxxx_comment.log

# vim /tmp/xxxx_comment.log

172499 #180514 2:47:36 server id 330602022 end_log_pos 1463677 CRC32 0x7233bf08 Xid = 1437

172500 COMMIT/*!*/;

172501 # at 1463677

172502 # at 1463742

172503 #180514 2:47:36 server id 330602022 end_log_pos 1463886 CRC32 0x76bd270a Query thread _id=192808 exec_time=0 error_code=0

172504 SET TIMESTAMP=1526237256/*!*/;

172505 /*!40000 ALTER TABLE `t_comment_thread` ENABLE KEYS */

172506 /*!*/;

172507 # at 1463886

172508 # at 1463951

172509 #180514 2:52:29 server id 330602022 end_log_pos 1464031 CRC32 0x4361761a Query thread _id=192808 exec_time=0 error_code=0

172510 SET TIMESTAMP=1526237549/*!*/;

172511 SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/;

172512 SET @@session.sql_mode=1436549120/*!*/;

172513 /*!\C utf8mb4 *//*!*/;

172514 SET @@session.character_set_client=45,@@session.collation_connection=46,@@session.collation_se rver=46/*!*/;

172515 BEGIN

172516 /*!*/;

172517 # at 1464031

172518 #180514 2:52:29 server id 330602022 end_log_pos 1464128 CRC32 0x33fa28bb Table_map: `hi go_comment`.`t_comment_thread` mapped to number 171

172519 # at 1464128

172520 #180514 2:52:29 server id 330602022 end_log_pos 1472343 CRC32 0xb2e210fe Update_rows: t able id 171

172521 # at 1472343

`6.备份相关日志文件`

# cp log_bin.000006 log_bin.000006.bak(如果涉及到需要恢复全备，请备份全备后的所有binlog日志)

`7.导出第一个UPDATE前的数据(若期间有数据更新，跳过故障SQL，继续执行binlog，完成恢复。)`

[root@yz-dbinf-00 binlogs]# mysqlbinlog --no-defaults --skip-gtids -d xxxx_comment --stop-position=1464031 log_bin.000010 | mysql -uroot -pxxxxx -h127.0.0.1 -P2022

mysql: [Warning] Using a password on the command line interface can be insecure.

`8.查看是否恢复`

mysql> select id,content from t_comment_thread order by id desc \G;

*************************** 1. row ***************************

id: 17203

content: 好想出柜，好难过

1 row in set (0.01 sec)

. . . . . .

三、写在最后（全备恢复）

1.找到该数据库全备位置：

2.找到所需备份，在当前服务器上进行还原：

# innobackupex --apply-log --redo-only [备份文件（绝对路径）]

3.将还原的文件复制到目标库

# rsync -avprP -e ssh [备份文件（绝对路径）] IP:[目标库data路径（绝对路径）]/data/

# chown -R mysql.mysql data

# 重启当前MySQL实例

4.查看xtrabackup_slave_info文件，提取其中的master_log_file和master_log_pos信息，然后在目标库上进行change master to操作：

# cat data/xtrabackup_slave_info

CHANGE MASTER TO MASTER_LOG_FILE='log_bin.00000*', MASTER_LOG_POS=number

mysql> CHANGE MASTER TO MASTER_HOST='master_ip',MASTER_PORT=port,MASTER_USER='username',MASTER_PASSWORD='password',MASTER_LOG_FILE='log_bin.00000*',MASTER_LOG_POS=number;

若开启【GTID】：

# cat xtrabackup_slave_info

SET GLOBAL gtid_purged='26c46462-4381-11e8-be15-44a8424a5d99:1-52499471, 5df3efc3-4473-11e8-8588-44a842480cba:1-31';
CHANGE MASTER TO MASTER_AUTO_POSITION=1

mysql> SET GLOBAL gtid_purged='26c46462-4381-11e8-be15-44a8424a5d99:1-52499471, 5df3efc3-4473-11e8-8588-44a842480cba:1-31';

mysql> CHANGE MASTER TO MASTER_HOST='master_ip',MASTER_PORT=port,MASTER_USER='username',MASTER_PASSWORD='password',MASTER_AUTO_POSITION=1;

if 0 = -I can

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SQL 第一话·操作问题及解决案例

目录写在最前：一、MySQL版本：1.执行更新操作（如如此不带where更新语句）2.在mysql中执行以下命令，即可查看二进制日志文件的列表：3.查看binlog日志模式4.查看是否开启全局事物ID（GTID）5.查找误操作执行的时间点6.备份相关日志文件7.可以使用以下语句将数据恢复到故障前8.跳过故障SQL，继续执行binlog，完成恢复。9.查看...
复制链接

扫一扫