MySQL备份和恢复 · php开发笔记

#### 一、备份恢复策略进行备份或恢复操作时需要考虑一些因素 1、确定要备份的表的存储引擎是事务型还是非事务型，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。 2、确定使用全备份还是增量备份。全备份的优点是备份保持最新备份，恢复的时候可以花费更少的时间；缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。增量备份相反，只需要备份每天的增量日志，备份时间少，对负载压力也小；缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间长一些。 3、可以考虑采用复制的方法来做异地备份，但不能代替备份，它对数据库的误操作也无能为力。 4、要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行。 5、确保 MySQL 打开 log-bin 选项，有了 binlog，MySQL 才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。 6、经常做备份恢复测试，确保备份是有效的，是可以恢复的。 #### 二、binlog MySQL中的日志比较重要的有binlog（归档日志）、redo log（重做日志）以及undo log。binlog我们中文一般称作归档日志，当我们搭建MySQL主从的时候就离不开binlog。 binlog是MySQL Server层的日志，而不是存储引擎自带的日志，它记录了所有的 DDL和DML(不包含数据查询语句)语句，而且是以事件形式记录，还包含语句所执行的消耗的时间等，需要注意的是： * binlog是一种逻辑日志，他里边所记录的是一条SQL语句的原始逻辑，例如给某一个字段 +1，注意这个区别于redo log的物理日志（在某个数据页上做了什么修改）。 * binlog文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于redo log，redo log是循环写入的，即后面写入的可能会覆盖前面写入的。 * 一般来说，我们在配置binlog的时候，可以指定binlog文件的有效期，这样在到期后，日志文件会自动删除，这样避免占用较多存储空间。根据MySQL官方文档的介绍，开启binlog之后，大概会有1%的性能损耗，不过这还是可以接受的，一般来说，binlog有两个重要的使用场景： * MySQL主从复制时：在主机上开启binlog，主机将binlog同步给从机，从机通过binlog来同步数据，进而实现主机和从机的数据同步。 * MySQL数据恢复，通过使用mysqlbinlog 工具再结合binlog文件，可以将数据恢复到过去的某一时刻。 #### 三、逻辑备份和恢复在MySQL中，逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法，因此，对于不同存储引擎混合的数据库，逻辑备份会简单一点。 ##### 3.1 备份 ###### 3.1.1 语法 MySQL中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在MySQL中，可以使用mysqldump工具来完成逻辑备份。 ~~~sql // 备份指定的数据库或者数据库中的某些表 shell> mysqldump [options] db_name [tables] // 备份指定的一个或多个数据库 shell> mysqldump [options] --database DB1 [DB2,DB3...] // 备份所有数据库 shell> mysqldump [options] --all-database ~~~ 如果没有指定数据库中的任何表，默认导出所有数据库中的所有表。 ###### 3.1.2 示例 1、备份所有数据库 ~~~sql shell>mysqldump -uroot -p --all-database > all.sql ~~~ 2、备份数据库 test ~~~sql shell>mysqldump -uroot -p test > test.sql ~~~ 3、备份数据库test下的表emp ~~~sql shell> mysqldump -uroot -p test emp > emp.sql ~~~ 4、备份数据库test下的表emp和dept ~~~sql shell> mysqldump -uroot -p test emp dept > emp_dept.sql ~~~ 5、备份数据库test下的所有表为逗号分割的文本，备份到/tmp ~~~sql shell> mysqlddump -uroot -p -T /tmp test emp --fields-terminated-by ','shell> more emp.txt 1,z12,z23,z34,z4 ~~~ 注意：为了保证数据备份的一致性，myisam存储引擎在备份时需要加上-l参数,表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。但是对于事务存储引擎来说，可以采用更好的选项`--single-transaction`，此选项使得innodb存储引擎得到一个快照(snapshot)，使得备份的数据能够保证一致性。 ##### 3.2 完全恢复 ###### 3.2.1 语法 mysqldump的恢复也很简单，将备份作为输入执行即可 ~~~sql mysql -uroot -p db_name < backfile ~~~ 注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做 ~~~sql mysqlbinlog binlog-file | mysql -uroot -p ~~~ ###### 3.2.2 完整的mysqldump备份与恢复示例 1、凌晨2:00，备份数据库 ~~~sql root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp Enter password: ~~~ 其中-l参数表示给所有表加读锁，-F表示生成一个新的日志文件，此时，t2中emp表的数据如下： ~~~sql # 为了便于测试，执行 reset master 删除所有 binlog。 MySQL [(none)]> reset master; Query OK, 0 rows affected (0.00 sec) # 此时只有一个 binlog 日志文件 mysql-bin.000001 MySQL [t2]> select * from test; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | +------+------+ 2 rows in set (0.00 sec) ~~~ 2、备份完毕后，插入新的数据 ~~~sql # 因为上一步执行是加入了 -F 选项, 所以接下来的操作会被记录到新的二进制文件，即名为 mysql-bin.000002 的文件 MySQL [t2]> insert into test values (3,'c'); Query OK, 1 row affected (0.00 sec) MySQL [t2]> insert into test values (4,'d'); Query OK, 1 row affected (0.00 sec) ~~~ 3、删库跑路:数据库突然故障，数据无法访问，需要恢复备份 ~~~sql # 这里为了便于测试，不把删库操作记入日志，当前 session 设置 sql_log_bin 为 off。 # 删库后，执行 flush logs，让后续的 binlog 到新的文件中，即名为 mysql-bin.000003中 MySQL [t2]> set sql_log_bin = 0; Query OK, 0 rows affected (0.00 sec) MySQL [t2]> show variables like "%sql_log_bin%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) MySQL [t2]> drop database t2; Query OK, 1 row affected (0.01 sec) MySQL [(none)]> flush logs; Query OK, 0 rows affected (0.22 sec) MySQL [t2]> drop database t2; Query OK, 3 rows affected (0.23 sec) MySQL [(none)]> exit; ~~~ 4、数据恢复 ~~~sql root@bogon:/usr/local/mysql/bin# ./mysql -e "create database t2" root@bogon:/usr/local/mysql/bin# ./mysql t2 < t2.dmp ******************************************************************* MySQL [t2]> select * from test; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | +------+------+ 2 rows in set (0.00 sec) ~~~ 5、使用mysqlbinlog恢复自mysqldump备份以来的binglog 根据前面操作的内容，可知从备份的时间点到删库的时间点之间的操作被记录到了mysql-bin.000002文件中 ~~~sql root@bogon:/usr/local/mysql/bin# ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000002 | ./mysql t2 ******************************************************* MySQL [t2]> select * from test; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +------+------+ 4 rows in set (0.00 sec) ~~~ 至此，数据恢复成功。 ##### 3.3 基于时间点恢复由于误操作，比如误删除了一张表，这时使用完全恢复时没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作语句，再恢复后面执行的语句，完成恢复。这种恢复叫不完全恢复，在MySQL中，不完全恢复分为基于时间点的恢复和基于位置的恢复。基于时间点恢复的操作步骤： 1、如果是上午 10 点发生了误操作，可以用以下语句用备份和 binlog 将数据恢复到故障前 ~~~sql shell>mysqlbinlog --stop-date="2017-09-30 9:59:59" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword ~~~ 2、跳过故障时的时间点，继续执行后面的 binlog，完成恢复 ~~~sql shell>mysqlbinlog --start-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword ~~~ ##### 3.4 基于位置恢复和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条sql语句同时执行。恢复的操作步骤如下： 1、在shell下执行命令 ~~~sql shell>mysqlbinlog --start-date="2019-09-30 9:59:59" --stop-date="2019-09-30 10:01:00"/data/mysql/mysql-bin.123456 > /tmp/mysql_restore.sql ~~~ 该命令将在/tmp目录创建小的文本文件，编辑此文件，知道出错语句前后的位置号，例如前后位置号分别为368312和368315。 2、恢复了以前的备份文件后，应从命令行输入下面的内容 ~~~sql shell>mysqlbinlog --stop-position="368312" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword shell>mysqlbinlog --start-position="368315" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword ~~~ 上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个sql语句记录之前的set timestamp语句，因此恢复的数据和相关的mysql日志将反映事务执行的原时间。 #### 四、表的导入导出 ##### 4.1 导出在某些情况下，为了一些特定的目的，经常需要将表里的数据导出为某些符号分割的纯数据文本，而不是sql语句： * 用来作为Excel显示。 * 单纯为了节省备份空间。 * 为了快速的加载数据，load data的加载速度比普通sql加载要快20倍以上。 ###### 4.1.1 使用select ...into outfile ... 命令来导出数据使用select ...into outfile ... 命令来导出数据，具体语法如下 ~~~sql mysql> select * from tablename into outfile 'target_file' [option]; ~~~ 其中option参数可以是以下选项 ~~~sql fields terminated by 'string' // 字段分隔符，默认为制表符'\t' fields [optionally] enclosed by 'char' // 字段引用符，如果加 optionally 选项则只用在 char、varchar 和 text 等字符型字段上，默认不使用引用符 fields escaped by ‘char’ // 转移字符、默认为 \ lines starting by 'string' // 每行前都加此字符串，默认'' lines terminated by 'string' // 行结束符，默认为'\n' # char 表示此符号只能是单个字符，string表示可以是字符串。 ~~~ 例如，将test表中数据导出为数据文本，其中，字段分隔符为“,”,字段引用符为“"”,记录结束符为回车符 ~~~sql MySQL [t2]> select * from test into outfile '/data/mysql/outfile.txt' fields terminated by "," enclosed by '"';Query OK, 4 rows affected (0.02 sec) ~~~ ~~~sql zj@bogon:/data/mysql$ more outfile.txt"1","a","helloworld""2","b","helloworld""3","c","helloworld""4","d","helloworld" ~~~ 发现第一列是数值型，如果不希望字段两边用引号引起，则语句改为 ~~~sql MySQL [t2]> select * from test into outfile '/data/mysql/outfile2.txt' fields terminated by "," optionally enclosed by '"';Query OK, 4 rows affected (0.03 sec)zj@bogon:/data/mysql$ more outfile2.txt1,"a","helloworld"2,"b","helloworld"3,"c","helloworld"4,"d","helloworld" ~~~ ###### 4.1.2 转义字符测试转义字符，MySQL导出数据中需要转义的字符主要包括以下3类： 1、转义字符本身 2、字段分隔符 3、记录分隔符 ~~~sql MySQL [t2]> update test set content = '\\"##!aa' where id=1;Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0MySQL [t2]> select * from test into outfile '/data/mysql/outfile3.txt' fields terminated by "," optionally enclosed by '"';Query OK, 4 rows affected (0.03 sec)*******************************************zj@bogon:/data/mysql$ more outfile3.txt1,"a","\\\"##!aa"2,"b","helloworld"3,"c","helloworld"4,"d","helloworld" ~~~ 注意：`select ... into outfile ...`产生的输出文件如果在目标目录下有重名文件，将不会被创建成功，源文件不会被自动覆盖。 ###### 4.1.3 使用mysqldump导出数据为文本使用mysqldump导出数据为文本的具体语法如下 ~~~sql mysqldump -u username -T target_dir dbname tablename [option] ~~~ 例子 ~~~sql root@bogon:/usr/local/mysql/bin# ./mysqldump -uroot -p -T /data/mysql/dump t2 test --fields-terminated-by ',' --fields-optionally-enclosed-by '"'**************** test.txt **********************zj@bogon:/data/mysql/dump$ more test.txt1,"a","\\\"##!aa"2,"b","helloworld"3,"c","helloworld"4,"d","helloworld"***************** test.sql *********************zj@bogon:/data/mysql/dump$ more test.sql-- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64)---- Host: localhost Database: t2-- -------------------------------------------------------- Server version 5.7.18-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8mb4 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `test`--DROP TABLE IF EXISTS `test`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `test` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `content` varchar(100) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client */;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2017-09-25 11:14:06 ~~~ 可以发现，除多了一个表的创建脚本文件，mysqldump和`select ... into outfile ...`的选项和语法非常相似。其实mysqldump实际调用的就是后者提供的接口，并在其上面添加了一些新的功能而已。 ##### 4.2 导入导入用`select ... into outfile`或者mysqldump导出的纯数据文本。和导出类似，导入也有两种不同的方法，分别是`load data infile...`和mysqlimport，它们的本质是一样的，区别只是在于一个在MySQL内部执行，另一个在MySQL外部执行。 ###### 4.2.1 使用load data infile...命令使用load data infile...命令，具体语法如下 ~~~sql mysql> load data [local]infile 'filename' into table tablename [option] ~~~ ###### 4.2.2 使用mysqldump实现语法 ~~~sql shell> mysqlimport -uroot -p [--local] dbname order_tab.txt [option] ~~~ 注意：如果导入和导出是跨平台操作的（windows 和 linux），那么要注意设置参数`line-terminated-by`，windows上设置为`line-terminated-by='rn'`, linux上设置为`line-terminated-by='n'`。