Crash Recovery
当发生掉电或硬件故障，MySQL重新启动时会自动进行Crash Recovery，从日志文件中读取pending的信息，rollback uncommit同时把已经提交的交易flush到datafile，这些信息可以从MySQL的错误日志中看到，这里我用kill -9模拟宕机：
使用mysqldump进行 SQL-Format备份和恢复
全量备份：
shell> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql
这里包含着备份时间点信息：
-- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='master-binlog.000015', MASTER_LOG_POS=194;
我们可以在备份时进行日志切换
shell> mysqldump --single-transaction --flush-logs --master-data=2 \ --all-databases > backup_sunday_1_PM.sql
这里包含这一个新的日志文件信息：
-- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='master-binlog.000016', MASTER_LOG_POS=194; -- -- Current Database: `data` --
加上参数–delete-master-logs 可以在备份的同时把不需要的binlog删除。
恢复的时候使用
shell> mysql < backup_sunday_1_PM.sql
恢复完成后，数据的状态处于 Sunday 1 p.m，如果要将数据前滚使用类似下面的命令
shell> mysqlbinlog gbichot2-bin.000016 gbichot2-bin.000017 | mysql
用于升级测试可以只导出结构
shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql
使用mysqldump进行 Delimited-Text备份和恢复
备份至的目录受 参数–secure-file-priv的限制，可能会出现
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
备份一个test数据库
root@scutech:~/test# mysqldump --tab=/var/lib/mysql-files/ test root@scutech:~/test# ll /var/lib/mysql-files/ total 288 drwxrwx--- 2 mysql mysql 4096 Jan 13 14:49 ./ drwxr-xr-x 65 root root 4096 Dec 25 13:59 ../ -rw-r--r-- 1 root root 1336 Jan 13 14:49 a.sql -rw-rw-rw- 1 mysql mysql 12 Jan 13 14:49 a.txt -rw-r--r-- 1 root root 1395 Jan 13 14:49 person.sql -rw-rw-rw- 1 mysql mysql 235718 Jan 13 14:49 person.txt -rw-r--r-- 1 root root 1442 Jan 13 14:49 person_myisam.sql -rw-rw-rw- 1 mysql mysql 153 Jan 13 14:49 person_myisam.txt -rw-r--r-- 1 root root 1516 Jan 13 14:49 shirt.sql -rw-rw-rw- 1 mysql mysql 110 Jan 13 14:49 shirt.txt -rw-r--r-- 1 root root 1287 Jan 13 14:49 t1.sql -rw-rw-rw- 1 mysql mysql 2 Jan 13 14:49 t1.txt -rw-r--r-- 1 root root 1287 Jan 13 14:49 t2.sql -rw-rw-rw- 1 mysql mysql 0 Jan 13 14:49 t2.txt -rw-r--r-- 1 root root 1287 Jan 13 14:49 t3.sql -rw-rw-rw- 1 mysql mysql 6 Jan 13 14:49 t3.txt
我们看到每个表备份了两个文件，一个建表的sql语句，一个数据文件。
恢复时使用：
root@scutech:~/test# mysql test < /var/lib/mysql-files/a.sql oot@scutech:~/test# mysqlimport test /var/lib/mysql-files/a.txt test.a: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
也可以在mysql客户端里面使用
mysql> use test Database changed mysql> load data infile 'a.txt' into table a; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement mysql> load data infile '/var/lib/mysql-files/a.txt' into table a; Query OK, 3 rows affected (0.04 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
使用binlog进行时间点恢复
查看当期binlog的状态：
mysql> show binary logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | master-binlog.000001 | 573 | | master-binlog.000002 | 217 | | master-binlog.000003 | 217 | | master-binlog.000004 | 217 | | master-binlog.000005 | 654 | | master-binlog.000006 | 217 | | master-binlog.000007 | 9556015 | | master-binlog.000008 | 3816423 | | master-binlog.000009 | 136315184 | | master-binlog.000010 | 48873328 | | master-binlog.000011 | 3145957 | | master-binlog.000012 | 1690153 | | master-binlog.000013 | 1869349 | | master-binlog.000014 | 1204862 | | master-binlog.000015 | 245 | | master-binlog.000016 | 461332 | +----------------------+-----------+ 16 rows in set (0.00 sec) mysql> show master status -> ; +----------------------+----------+--------------+------------------+-----------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-----------------------------------------------+ | master-binlog.000016 | 461332 | | | 8e17f3ff-1b31-11ea-95c6-fa163e0ec694:1-489093 | +----------------------+----------+--------------+------------------+-----------------------------------------------+ 1 row in set (0.01 sec)
查询binlog中的内容
root@scutech:/var/lib/mysql# mysqlbinlog master-binlog.000016|head /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200113 14:38:14 server id 135 end_log_pos 123 CRC32 0xc7ee4177 Start: binlog v 4, server v 5.7.28-log created 200113 14:38:14 # Warning: this binlog is either in use or was not closed properly. BINLOG ' VhAcXg+HAAAAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AXdB7sc=
导入使用
# mysqlbinlog --skip-gtids master-binlog.000016|mysql
恢复到指定时间点可以使用：
mysqlbinlog --stop-datetime="2020-01-13 18:16:52" master-binlog.000006|mysql
恢复到指定的 Event Positions
mysqlbinlog --stop-position=45883 master-binlog.000006 |tail -n 20
