備份
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)