Mysql之备份（Mysqldump）

本篇文章旨在介绍Mysql的备份，借助mysqldump命令。

1.准备数据

准备一个数据库d1，表t1
表结构如下：

mysql> desc t1;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| xh    | int        | NO   | PRI | NULL    | auto_increment |
| xm    | varchar(5) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

插入数据后，内容如下：

mysql> select * from t1;
+----+------+
| xh | xm   |
+----+------+
|  1 | zs   |
|  2 | ls   |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

2.实验开始

a-备份

mysqldump命令的格式：

mysqldump -h 127.0.0.1 -u root -p666 xxx > f:\数据库备份练习\xxx.sql
#127.0.0.1为ip地址
#root为用户名
#666为密码
#xxx为存储的数据库名
#>后为要存储的位置以及文件名
1
2
3
4
5
6

注意是在操作系统命令行输入，不是在mysql里。

[root@VM-4-9-centos ~]# mysqldump -u root -p d1 > d1.bak
Enter password:
[root@VM-4-9-centos ~]# ls
d1.bak 
1
2
3
4

b-还原

在还原前，我们需要先建立数据库。

#删除数据库
mysql> drop database d1;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

#还原
[root@VM-4-9-centos ~]# mysql -u root -p d1 < d1.bak
Enter password:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

查看还原效果：

mysql> use d1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc t1;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| xh    | int        | NO   | PRI | NULL    | auto_increment |
| xm    | varchar(5) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----+------+
| xh | xm   |
+----+------+
|  1 | zs   |
|  2 | ls   |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

c-原理