本篇文章旨在介绍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)
插入数据后,内容如下:
mysql> select * from t1;
+----+------+
| xh | xm |
+----+------+
| 1 | zs |
| 2 | ls |
+----+------+
2 rows in set (0.00 sec)
2.实验开始
a-备份
mysqldump命令的格式:
mysqldump -h 127.0.0.1 -u root -p666 xxx > f:\数据库备份练习\xxx.sql
#127.0.0.1为ip地址
#root为用户名
#666为密码
#xxx为存储的数据库名
#>后为要存储的位置以及文件名
注意是在操作系统命令行输入,不是在mysql里。
[root@VM-4-9-centos ~]# mysqldump -u root -p d1 > d1.bak
Enter password:
[root@VM-4-9-centos ~]# ls
d1.bak
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:
查看还原效果:
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)