备份
mysqldump
是
MySQL
自带的逻辑备份工具。它的备份原理是通过协议连接到
MySQL
数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的
insert
语句，当需要还原这些数据时，只要执行这些
insert
语句，即可将对应的数据还原。
进入
MySQL
:
[root@MiWiFi-R1CM-srv ~]# mysql -uroot -p -h127.0.0.1 -P3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 查询
MySQL
版本：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec) 备份
kaven
数据库。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kaven |
| mysql |
| performance_schema |
| sys |
| user |
+--------------------+
6 rows in set (0.00 sec)
mysql> use kaven;
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> show tables;
+-----------------+
| Tables_in_kaven |
+-----------------+
| category |
| product |
+-----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from category;
+----------+
| count(*) |
+----------+
| 163 |
+----------+
1 row in set (0.11 sec)
mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
| 497235 |
+----------+
1 row in set (1.62 sec) 这是一个商品数据库（先不纠结数据库名称是否规范）。
mysql> select detail, price, image_url from product limit 1;
+---------------------------------------------------------------------------------------------------------+---------+----------------------------------------------------------------------------------------------------+
| detail | price | image_url |
+---------------------------------------------------------------------------------------------------------+---------+----------------------------------------------------------------------------------------------------+
| 八核处理器，5000mAh超大电池，配备22.5W超级快充~【play5T活力版，正在热销中】 | 1199.00 | https://xxx.com/z7/jfs/q1/904751/09/16896/87334/320ba745E85c55a96/z56ui9r0zzu8opl0.jpg |
+---------------------------------------------------------------------------------------------------------+---------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) - 导出表结构不导出数据：
mysqldump -d kaven -uroot -p > kaven.sql
- 导出数据不导出表结构：
mysqldump -t kaven -uroot -p > kaven.sql
- 导出数据和表结构：
mysqldump kaven -uroot -p > kaven.sql
其他选项和参数就不介绍了，用到再去查即可。
导入备份
创建一个新的数据库。
[root@MiWiFi-R1CM-srv ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database goods;
Query OK, 1 row affected (0.21 sec)
mysql> exit
Bye [root@MiWiFi-R1CM-srv ~]# mysql -uroot -p goods < kaven.sql
Enter password:
[root@MiWiFi-R1CM-srv ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| goods |
| information_schema |
| kaven |
| mysql |
| performance_schema |
| sys |
| user |
+--------------------+
7 rows in set (0.09 sec)
mysql> use goods;
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> show tables;
+-----------------+
| Tables_in_goods |
+-----------------+
| category |
| product |
+-----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from category;
+----------+
| count(*) |
+----------+
| 163 |
+----------+
1 row in set (0.05 sec)
mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
| 497235 |
+----------+
1 row in set (1.61 sec)