Backup&Recovery - Recovery - 《Mysql》

如果mysqldump方式备份时使用了—all-databases或者—databases时，使用以下方式恢复全库

shell> mysql < dump.sql
mysql> source dump.sql

恢复到指定库

shell> mysql db1 < dump.sql

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

假设现在有一个灾难性的破坏发生在星期三的上午8点。现在有星期一的全备，以及bin-log日志。
先需要恢复数据。
1/恢复全备

shell> mysql < backup_sunday_1_PM.sql

2/恢复bin-log

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 ... | mysql

恢复分割文本的方式的备份

shell> mysql db1 < t1.sql
shell> mysqlimport db1 t1.txt

或者使用SQL LOAD加载数据

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

BIN-LOG的时间点恢复
想要基于时间点的恢复，bin-log必须要打开。mysqlbinlog程序会把binlog日志转换成text文本格式，然后再应用

查看bin-log列表

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| centos7.000002 |       177 |
| centos7.000003 |       199 |
| centos7.000004 |       177 |
| centos7.000005 |       177 |
| centos7.000006 |       513 |
+----------------+-----------+
5 rows in set (0.00 sec)

查看当前使用的bin-log

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| centos7.000020 |      154 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

应用bin-log日志文件中的事件

shell> mysqlbinlog binlog_files | mysql -u root -p

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

应用bin-log日志时最好不要使用多个连接去恢复，假如第一个binlog中包含一个临时表，第二binlog中要使用这个临时表。当第一个binlog应用完成时，会删除临时表，到了第二个binlog应用时就会报错。为了避免这种情况，使用一个连接去恢复所有的binlog：

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

如果binlog太多，可以使用另一种方法，将binlog的内容写到另一个文件，然后进行应用：

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

当读取的bin-log中包含gtid时，使用—skip-gtids：

shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"

基于Events Position的时间点恢复
假设星期三中午1点，执行了一条DROP TABLE ,过了一个小时后发现删错了，现在需要恢复
1/恢复全备
2/找到drop 的位置

shell> mysqlbinlog   --start-datetime="2020-05-27 12:59:00" --stop-datetime="2020-05-27 13:06:00" \
  --verbose /var/lib/mysql/bin.123456 | grep -C 12 "DROP TABLE"
# at 1868
#200527 13:00:30 server id 2  end_log_pos 1985 CRC32 0x8b894489     Query    thread_id=8    exec_time=0    error_code=0
use `pets`/*!*/;
SET TIMESTAMP=1590598830/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!80005 &~0x1003ff00*//*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `cats` /* generated by server */
/*!*/;
# at 1985
#200527 13:05:06 server id 2  end_log_pos 2050 CRC32 0x2f8d0249     Anonymous_GTID    last_committed=6    sequence_number=7    rbr_only=yes    original_committed_timestamp=0    immediate_commit_timestamp=0    transaction_length=0
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=0 (1969-12-31 19:00:00.000000 EST)
# immediate_commit_timestamp=0 (1969-12-31 19:00:00.000000 EST)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2050
#200527 13:05:06 server id 2  end_log_pos 2122 CRC32 0x56280bb1     Query    thread_id=8    exec_time=0    error_code=0

由bin-log分析来看，DROP TABLE cats 发生在# at 1868，# at 1985之间。
3/分两步应用bin-log

shell> mysqlbinlog --start-position=1006 --stop-position=1868 /var/lib/mysql/bin.123456 \
         | mysql -u root -p

shell> mysqlbinlog --start-position=1985 /var/lib/mysql/bin.123456 \
         | mysql -u root -p

第一步将日志恢复到删表之前，第二步从删表之后的位置开始直到最后。