先试一下基本的逻辑备份和恢复过程,t1表模拟了些数据,查看一下当前的时间
mysql> select count(*) from l5m.t1;
+----------+
| count(*) |
+----------+
| 131072 |
+----------+
1 row in set (0.05 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2018-03-21 08:49:20 |
+---------------------+
1 row in set (0.00 sec)
将l5m数据库下面的表都备份一下
[root@qht131 ~]# mysqldump -u root -p l5m > /u01/backup/db_l5m.log
看了下备份文件的内容,主要就是drop,create和insert语句,在insert之前会对表LOCK TABLES `table_name` WRITE,insert之后会UNLOCK TABLES。
下面直接运行db_l5m.log进行恢复
[root@qht131 ~]# mysql -u root -p l5m < /u01/backup/db_l5m.log
查看数据库中表的数据及建立时间,t1和t2表都被drop后重新建立了起来,也就是说恢复的时候删除了表然后重新建立表后导入数据。
mysql> select count(*) from l5m.t1;
+----------+
| count(*) |
+----------+
| 131072 |
+----------+
1 row in set (0.06 sec)
mysql> select table_schema,table_name,table_type,engine,create_time from tables where table_schema='L5M';
+--------------+------------+------------+--------+---------------------+
| table_schema | table_name | table_type | engine | create_time |
+--------------+------------+------------+--------+---------------------+
| l5m | t1 | BASE TABLE | InnoDB | 2018-03-21 08:50:00 |
| l5m | t2 | BASE TABLE | InnoDB | 2018-03-21 08:50:06 |
+--------------+------------+------------+--------+---------------------+
2 rows in set (0.00 sec)
导入sql语句除了用myql工具以外,还可以用source直接运行脚本
mysql> use l5m;
Database changed
mysql> source /u01/backup/db_l5m.log
如果导入的不是sql语句,而是txt文档,可以用mysqlimport工具和load data的方式:
首先生成一个txt文档的备份:
[root@qht131 backup]# mysqldump -u root -p l5m -T /u01/backup
Enter password:
[root@qht131 backup]# ls
t1.sql t1.txt t2.sql t2.txt
先用mysqlimport导入一次:
[root@qht131 backup]# mysqlimport -u root -p l5m /u01/backup/t1.txt
Enter password:
l5m.t1: Records: 131072 Deleted: 0 Skipped: 0 Warnings: 0
mysql> use l5m;
Database changed
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 262144 |--发现数据翻倍了,因为txt里面没有重建表的sql,只有数据
+----------+
1 row in set (0.10 sec)
再用load data导入一次
mysql> load data infile '/u01/backup/t1.txt' into table t1;
Query OK, 131072 rows affected (1.16 sec)
Records: 131072 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 393216 |--数据又增长了一次
+----------+
1 row in set (0.15 sec)
上面测试了mysqlimport导入txt数据,那mysqlimport能不能导入sql语句呢?
答案是否定的
[root@qht131 backup]# mysqlimport -u root -p l5m /u01/backup/t1.sql
Enter password:
mysqlimport: Error: 1366, Incorrect integer value: '-- MySQL dump 10.13 Distrib 5.7.21, for linux-glibc2.12 (x86_64)' for column 'c1' at row 1, when using table: t1