MySQL备份与恢复-mysqldump备份与恢复

这片博文主要用来介绍MySQL的备份与恢复：

MySQL的备份形式可以分为如下几种：

热备----即不停机备份
冷备----需要关闭MySQL，然后备份其数据文件。（停机备份一般是直接拷贝其datadir目录）
温备----在线备份，对应用影响大，通常加一个读锁【会阻塞写的应用】，意义不大，基本不用。

从导出的备份文件结构可分为如下几种：

逻辑备份---备份的数据是导出的SQL语句（如mysqldump, mysqlpump【MySQL5.7加入的】,mydumper）
物理备份--备份的是物理文件（如xtracebackup）

接下来会详细介绍这四种命令的通常用法（如果不特别说明，数据库存储引擎为INNODB）：

mysqldump备份与恢复

mysqldump的用法如下：

[root@test3 ~----all-databases [OPTIONS]              #备份所有的数据库#可以使用mysqldump --help查看mysqldump的更多参数，会在下面使用的时候介绍到经常用的参数。

备份单张表：

[root@test3 ~]# #备份INNODB存储引擎时建议加上参数--single-transaction【实际上是必须加】，这样会保证数据的一致性。查看一下备份出来的数据：[root@test3 ~]#           #可以看到基本就是SQL语句-- MySQL dump 10.13  Distrib 5.7.22, for linux-glibc2.12 (x86_64)---- Host: localhost    Database: employees-- -------------------------------------------------------- Server version    5.7.22-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 utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `departments`--DROP TABLE IF EXISTS `departments`;        #/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */; (  `dept_no` char(4) NOT NULL,  `dept_name` varchar(40) NOT NULL,  PRIMARY KEY (`dept_no`),  UNIQUE KEY `dept_name` (`dept_name`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `departments`--LOCK TABLES `departments` WRITE;/*!40000 ALTER TABLE `departments` DISABLE KEYS */;           INSERT INTO `departments` VALUES ('d009','Customer Service'),('d005','Development'),('d002','Finance'),('d003','Human Resources'),('d001','Marketing'),('d004','Production'),('d006','Quality Management'),('d008','Research'),('d007','Sales');/*!40000 ALTER TABLE `departments` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!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 2018-11-28  9:51:01该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间）。该选项自动关闭--lock-tables选项。[root@test3 mysql]# mysqldump -uroot -p123456 --single-transaction --master-data employees departments > dep.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@test3 mysql]# cat dep.sql -- MySQL dump 10.13  Distrib 5.7.22, for linux-glibc2.12 (x86_64)---- Host: localhost    Database: employees-- -------------------------------------------------------- Server version    5.7.22-log......------CHANGE MASTER TO MASTER_LOG_FILE='test3-bin.000001', MASTER_LOG_POS=20182;.....#mysqldump备份时常用的两个选项

--single-transaction                       Creates a consistent snapshot by dumping all tables in a                      single transaction. Works ONLY for tables stored in                      storage engines which support multiversioning (currently                      only InnoDB does); the dump is NOT guaranteed to be                      consistent for other storage engines. While a                      --single-transaction dump is in process, to ensure a                      valid dump file (correct table contents and binary log                      position), no other connection should use the following                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,                      TRUNCATE TABLE, as consistent snapshot is not isolated                      from them. Option automatically turns off --lock-tables.在备份INNODB数据库时，事实上必须加上--single-transaction参数，这个参数会保证备份出的数据是一致的，也就是备份的数据是当前执行此命令时刻点的数据。--single-transaction利用INNODB的MVCC特性，在备份数据会根据undo和redo得到一份快照数据。INNODB的MVCC特性在RR和RC的隔离级别下，得到的快照数据是不同，在RR得到的是当前事务开始时的快照数据，在RC时得到的最新的事务数据。因为数据库的正式环境一般是RC模式，因此这条命令在备份开始时，会设置当前会话的隔离级别为RR模式！

--single-transaction 参数说明

备份单个库和备份所有的库

[root@test3 mysql]# mysqldump -uroot -p123456 --single-transaction --master-data --databases cmdb hostinfo  >-uroot -p123456 --single-transaction --master-data --all-databases  >-- MySQL dump   Distrib .,  linux-glibc2.------ -------------------------------------------------------- Server version    .----- Position to start replication or point----=, MASTER_LOG_POS=------ `cmdb`

恢复数据的时候直接使用mysql导入即可：

mysql -uroot -p123456 < all.sql

切记在备份INNODB数据库时一定要加上--single-transaction 参数，为了基于PIT恢复也要加上--master-data参数。

mysqldump的备份过程

上面我们已经看到了mysqldump备份出的数据文件时对应sql语句，我们查看一下在执行mysqldump时，MySQL数据库做了哪些操作！

首先开启general_log日志，如下：

mysql> set global general_log=on;Query OK, 0 rows affected (0.00 sec)mysql> set global log_output="file";Query OK, 0 rows affected (0.00 sec)mysql> show variables like "%general%";+------------------+-----------------------+| Variable_name    | Value                 |+------------------+-----------------------+| general_log      | ON                    || general_log_file | /data/mysql/test3.log |+------------------+-----------------------+2 rows in set (0.00 sec)

然后再去使用mysqldump备份一次数据文件

[root@test3 ~]# mysqldump -uroot -p123456 --single-transaction --master-data --databases tpcc_test > dep.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@test3 ~]#

最后查看general_log日志，如下：

[root@test3 mysql]# --28T05::.014426Z       --28T05::.014662Z        Query    --28T05::.014792Z        Query    --28T05::.014915Z        Query    FLUSH --28T05::.015141Z       --28T05::.015213Z       --28T05::.015271Z        Query    START TRANSACTION --28T05::.015463Z        Query    SHOW VARIABLES LIKE --28T05::.018393Z       --28T05::.018467Z       --28T05::.018618Z        Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =  AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =  AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (--28T05::.021966Z        Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =  AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (--28T05::.022750Z        Query    SHOW VARIABLES LIKE --28T05::.023915Z       --28T05::.023977Z       --28T05::.024045Z       --28T05::.024100Z       --28T05::.024253Z        Query    show table status like --28T05::.024475Z        Query    SET SQL_QUOTE_SHOW_CREATE=--28T05::.024541Z        Query    SET SESSION character_set_results = --28T05::.024607Z       --28T05::.024737Z        Query    SET SESSION character_set_results = --28T05::.024806Z       --28T05::.025199Z       --28T05::.025582Z        Query    SELECT  *--28T05::.251932Z        Query    SET SESSION character_set_results = --28T05::.252069Z       --28T05::.252158Z        Query    --28T05::.252290Z        Query    SHOW TRIGGERS LIKE --28T05::.252706Z        Query    SET SESSION character_set_results = --28T05::.252771Z        Query    ROLLBACK TO SAVEPOINT sp                               #............以上的过程可以看到，mysqldump是在一个事务中备份的，因此在备份表时会产生undo日志，若是表数据太大，则undo日志也会很大，因此mysqldump在备份每张表时都设置savepoint，这样当这个表备份完成之后，就会回滚保存点，然后purge线程就会回收undo日志

mysqld的其余常见参数应用：

-d：只备份指定数据库表的结构（也就是建表语句）

[root@test3 ~]# mysqldump -uroot -p123456 -d --databases lianxi > dep.sql    #这样只备份出lianxi这个库中表的建表语句和创建库的语句[root@test3 ~]# mysqldump -uroot -p123456 -d lianxi tb2 > dep.sql            #如果这里只备份库下面的表，则还是没有建库语句，只有创建表的语句

-R, --routines ：备份时导出存储过程和自定义函数！

[root@test3 ~]# mysqldump -uroot -p123456 -R lianxi tb2 > dep.sql

-f, --force Continue even if we get an SQL error.。发生错误的时候也继续备份。

mysqldump还有很多参数，可以在用到的时候查看！

mysqldump是逻辑备份，单线程备份，单线程恢复，因此会比较慢。特别是恢复的时候，之前恢复过一个800多万行记录的数据，结果使用mysql逐条导入sql语句，搞库几个小时。

备份与恢复纠错

1：在使用mysql导入mysqldump的备份数据时，报了如下错误：

[root@test3 ~]# mysql -uroot -p123456 financesys < financesys.sql mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1118 (42000) at line 25: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

错误原因就是行太长了，但是线上环境修改字段属性有点不太合适，可以采用如下方法：

mysql> set global innodb_strict_mode=off;Query OK, 0 rows affected (0.00 sec)[root@test3 ~]# mysql -uroot -p123456 financesys < financesys.sql        #再导入就可以了mysql: [Warning] Using a password on the command line interface can be insecure.

从MySQL5.5.X版本开始，你可以开启InnoDB严格检查模式，尤其采用了页数据压缩功能后，最好是开启该功能。开启此功能后，当创建表（CREATE TABLE）、更改表（ALTER TABLE）和创建索引（CREATE INDEX）语句时，如果写法有错误，不会有警告信息，而是直接抛出错误，这样就可直接将问题扼杀在摇篮里。开启InnoDB严格检查模式涉及的参数是innodb_strict_mode，默认为OFF，支持动态开启，开启方式如下：set global innodb_strict_mode=1;连接地址

导入成功后，如果是线上环境，还需要把参数修改回去！

2：在使用mysqldump备份的时候出现如下问题

[root@test2 data]# mysqldump -uroot -p7abec53701c3eefb --databases financesys > financesys1.sqlmysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `fi_factincome` at row: 1303198

我查了一下，这个表大概有1500万行的记录，报错原因如下：

备份失败的原因：在向磁盘上备份的时候，数据的流向是这样的：MySQL Server 端从数据文件中检索出数据，然后分批将数据返回给mysqldump 客户端，然后 mysqldump 将数据写入到磁盘上。一般地，向 磁盘 上写入数据的速度较之Server端检索发送数据的速度要慢得多，这就会导致 mysqldump 无法及时的接受 Server 端发送过来的数据，Server 端的数据就会积压在内存中等待发送，这个等待不是无限期的，当 Server 的等待时间超过 net_write_timeout（默认是60秒）时它就失去了耐心，mysqldump 的连接会被断开，同时抛出错误 Got error: : Lost connection。其实该错误不是说数据库文件太多而导致出错，而是单张表数据量太大导致备份失败

问题的解决方案：增加 net_write_timeout 可以解决上述的问题的。在实践中发现，在增大 net_write_timeout 后，Server 端会消耗更多的内存，有时甚至会导致 swap 的使用（并不确定是不是修改 net_write_timeout 所至）。建议在mysqldump 之前修改 net_write_timeout 为一个较大的值（如1800），在 mysqldump 结束后，在将这个值修改到默认的60。（备注：net_write_timeout不是mysqldump的配置参数，而是mysql的参数）

mysqldump备份与恢复

更多相关文章

随机推荐