mysql> select * from fruits; 【查看fruits表中的数据】
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
8 rows in set (0.00 sec)
mysql>
步骤1：执行备份操作
C:\Users\Administrator>mysqldump -uroot -p test_db fruits > c:\fruits.sql 【执行备份操作】
Enter password: *********
C:\Users\Administrator>
备份完成之后可以查看备份的文件内容信息如下所示
| -- MySQL dump 10.13 Distrib 8.0.25, for Win64 (x86_64) -- -- Host: localhost Database: test_db -- ------------------------------------------------------ -- Server version 8.0.25 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `fruits` -- DROP TABLE IF EXISTS `fruits`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `fruits` ( `f_id` char(10) NOT NULL, `s_id` int NOT NULL, `f_name` char(255) NOT NULL, `f_price` decimal(8,2) NOT NULL, PRIMARY KEY (`f_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `fruits` -- LOCK TABLES `fruits` WRITE; /*!40000 ALTER TABLE `fruits` DISABLE KEYS */; INSERT INTO `fruits` VALUES ('a1',101,'apple',5.20),('b1',101,'blackberry',10.20),('bs1',102,'orange',11.20),('bs2',105,'melon',8.20),('c0',101,'cherry',3.20),('o2',103,'coconut',9.20),('t1',102,'banana',10.30),('t2',102,'grape',5.30); /*!40000 ALTER TABLE `fruits` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2021-10-01 14:57:15 |
步骤2：执行数据恢复操作
mysql> delete from fruits; 【清空源表数据】
Query OK, 8 rows affected (0.00 sec)
mysql>
接着执行恢复操作
| mysql> source c:\fruits.sql; ERROR: Unknown command '\f'. Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> select * from fruits; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | b1 | 101 | blackberry | 10.20 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | +------+------+------------+---------+ 8 rows in set (0.00 sec) mysql> |
但是在恢复过程中发现有出现了个警告信息
警告查阅相关资料反馈mysql默认是以gbk编码连接数据库的，之前导出备份的文件是UTF8，
编码不一致导致报的警告异常信息，处理方法为连接mysql时指定字符集为utf8即可
mysql> select @@character_set_connection; 【查看客户端连接mysql使用的字符集】
+----------------------------+
| @@character_set_connection |
+----------------------------+
| gbk |
+----------------------------+
1 row in set (0.00 sec)
mysql> select @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| gbk |
+------------------------+
1 row in set (0.00 sec)
mysql> select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| gbk |
+-------------------------+
1 row in set (0.00 sec)
mysql>
C:\Users\Administrator>mysql -uroot -p --default-character-set=utf8 【连接mysql指定连接使用的字符集】
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 8.0.25 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> select @@character_set_connection; 【查看连接使用的字符集】
+----------------------------+
| @@character_set_connection |
+----------------------------+
| utf8mb3 |
+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| utf8mb3 |
+------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| utf8mb3 |
+-------------------------+
1 row in set (0.00 sec)
mysql>
mysql> use test_db;
Database changed
mysql> delete from fruits; 【清空表中数据】
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fruits; 【验证数据是否清空完毕】
Empty set (0.00 sec)
mysql> source c:\fruits.sql 【重新做恢复，此时可以看到已经没有出现之前的告警异常信息了】
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.32 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>