MySQL备份与恢复(二)
- 2019 年 11 月 6 日
- 笔记
MySQL备份与恢复(二)
备份与恢复方法示例
前天的文章中简单写了备份与恢复的方法，今天我们主要来看看不同的备份方法备份出来的文件结果，以及它们的恢复方法。
首先我们来看mysqldump备份出来的文件的内容，现将表t进行备份：
root@localhost:3306 [test]>select * from t; +----+-----+ | id | age | +----+-----+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +----+-----+ 5 rows in set (0.08 sec) root@localhost:3306 [test]>exit Bye C:Usersyeyz>mysqldump --single-transaction -uroot -p test t > dump.sql Enter password: ********
然后打开备份文件，我们可以看到备份文件中的内容如下：
-- MySQL dump 10.13 Distrib 5.7.24, for Win32 (AMD64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.7.24-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 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=154; -- -- Table structure for table `t` -- DROP TABLE IF EXISTS `t`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t` ( `id` int(11) NOT NULL, `age` int(11) NOT NULL, KEY `idx_t` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t` -- LOCK TABLES `t` WRITE; /*!40000 ALTER TABLE `t` DISABLE KEYS */; INSERT INTO `t` VALUES (1,5),(2,4),(3,3),(4,2),(5,1); /*!40000 ALTER TABLE `t` 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 2019-01-20 19:24:07
可以看到第22行是当前数据库的二进制日志位置，这通常用于在从库同步主库的数据的时候，第31-35行是表的定义，38行是表的数据，而且只备份了数据库test中的t这一个表。
当我们想恢复这个备份文件的时候，我们可以通过mysql -u<user> -p dbname < dump.sql的命令来进行恢复，示例如下：
root@localhost:3306 [test]>show tables; +----------------+ | Tables_in_test | +----------------+ | t | | v_t | | z | | zz | +----------------+ 4 rows in set (0.00 sec) root@localhost:3306 [test]>drop table t; Query OK, 0 rows affected (0.77 sec) root@localhost:3306 [test]>exit Bye C:Usersyeyz>mysql -uroot -p test < dump.sql Enter password: ******** C:Usersyeyz>mysql -uroot -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 Server version: 5.7.24-log MySQL Community Server (GPL) Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. root@localhost:3306 [test]>use test Database changed root@localhost:3306 [test]>show tables; +----------------+ | Tables_in_test | +----------------+ | t | | v_t | | z | | zz | +----------------+ 4 rows in set (0.00 sec) root@localhost:3306 [test]>select * from t; +----+-----+ | id | age | +----+-----+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +----+-----+ 5 rows in set (0.00 sec)
再来看看select into outfile导出文件的试验过程，话不多说，备份的代码如下：
root@localhost:3306 [test]>select * from t; +----+-----+ | id | age | +----+-----+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +----+-----+ 5 rows in set (0.00 sec) root@localhost:3306 [(none)]>use test Database changed root@localhost:3306 [test]>select * from t into outfile 'back.sql' ; Query OK, 5 rows affected (0.01 sec) #查看文件内容 1 5 2 4 3 3 4 2 5 1
我们可以看到，这种备份方法相当于只备份了表里面的数据，而没有备份表的结构。如果我们觉得这个备份结果的格式不是我们想要的，还可以改成其他的风格，如下：
root@localhost:3306 [test]>select * from t into outfile 'back3.sql' fields termi nated by ',' lines starting by '本行记录内容:' terminated by 'rn' ; 本行记录内容:1,5 本行记录内容:2,4 本行记录内容:3,3 本行记录内容:4,2 本行记录内容:5,1
代码中的fields terminated表示列之间的分隔符，lines starting表示每一行开始的符号，lines terminated表示每一行结束的符号，windows中'rn'表示换行。
这种备份方式恢复的时候需要采用load data infile的方式，下面给出例子：
root@localhost:3306 [test]>show tables; +----------------+ | Tables_in_test | +----------------+ | t | | v_t | | z | | zz | +----------------+ 4 rows in set (0.00 sec) root@localhost:3306 [test]>drop table t; Query OK, 0 rows affected (0.38 sec) root@localhost:3306 [test]>load data into table t ignore 1 lines infile 'data.sql'; root@localhost:3306 [test]>load data into table t ignore 1 lines infile 'e:/mysq l-5.7.24-win32/data/test/back.sql'; root@localhost:3306 [test]>load data infile 'e:/mysql-5.7.24-win32/data/test/ba ck.sql' into table t; ERROR 1146 (42S02): Table 'test.t' doesn't exist root@localhost:3306 [test]>create table t (id int,age int); Query OK, 0 rows affected (0.63 sec) root@localhost:3306 [test]>load data infile 'e:/mysql-5.7.24-win32/data/test/back.sql' into table t; Query OK, 5 rows affected (0.21 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 root@localhost:3306 [test]>select * from t; +------+------+ | id | age | +------+------+ | 1 | 5 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 1 | +------+------+ 5 rows in set (0.00 sec) root@localhost:3306 [test]>
上面的例子有个小问题需要注意一下，就是我们在导入一个表数据的时候，这个表不能删除，表结构必须存在，不然是没办法导入的，会提示table doesn't exist的错误。
再来看看mysqlimport方法，从本质上来说，它是load data infile的命令接口，而且大多数的选项都和load data infile语法相同，它的语法格式如下：
mysqlimport dbname text1 text2
与load data infile不同的是，mysqlimport可以用来导入多张表，并且通过–user-thread参数并发地导入不同的文件，这里的并发值得是并发导入多个文件，而不是指mysqlimport可以并发的导入一个文件，这是有明显区别的。如果有兴趣，可以试一下在load data的同时，连接mysql然后在命令行中show full processlist;这个时候，就可以看到mysqlimport实际上是同时执行多句load data infile的命令。
