-
不同MySQL服务的表以及库的数据迁移（/备份）
目标：
将本地主机上username=root,password=root,port=3307的MySQL服务中migration_one数据库的table_11数据表导出到本地的D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11.bak 注意：目前D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL该文件夹未存在table_11.bak这个文件。- 1
- 2
- 3
- 4
先看看数据表有什么数据：
SELECT * FROM migration_one.`table_11`;- 1
结果：
id name ------ -------- 12321 hehe- 1
- 2
- 3
看来就只有一行
一、导出数据：
1、导出备份数据之.bak文件：
用我的mysql8的mysqldump工具，但是目前是需要去找到我的mysql8在哪里：
SHOW VARIABLES LIKE "character_sets_dir";- 1
得到结果：
Variable_name Value ------------------ ------------------------------------------------------------ character_sets_dir D:\start_java\lwsmysql\mysql-8.0.17-winx64\share\charsets\- 1
- 2
- 3
于是来到
D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin文件夹可以看到一个mysqldump.exe在里面于是就可以执行命令来备份文件了，不过需要将原来的
table_11.bak删掉。管理员打开终端执行：
D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -h127.0.0.1 -P3307 -uroot -proot migration_one table_11 > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11.bak- 1
得到执行结果：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -h127.0.0.1 -P3307 -uroot -proot migration_one table_11 > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11.bak mysqldump: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
再打开
table_11.bak文件:-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: migration_one -- ------------------------------------------------------ -- Server version 8.0.17 /*!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 utf8 */; /*!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 `table_11` -- DROP TABLE IF EXISTS `table_11`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `table_11` ( `id` varchar(200) NOT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `table_11` -- LOCK TABLES `table_11` WRITE; /*!40000 ALTER TABLE `table_11` DISABLE KEYS */; INSERT INTO `table_11` VALUES ('12321','hehe'); /*!40000 ALTER TABLE `table_11` 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 2023-11-04 14:53:46- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
成功乐！这样才算正常的备份文件嘛。
2、导出数据和表结构——将特定数据库特定表中的数据和表格结构和数据全部返回
D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 migration_one table_11 > table_11_t_d.sql- 1
管理员打开cmd进入
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL文件夹里面执行上面的命令执行结果：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 migration_one table_11 > table_11_t_d.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
发现
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL文件夹多了一个table_11_t_d.sql文件，里面是：-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: migration_one -- ------------------------------------------------------ -- Server version 8.0.17 /*!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 utf8 */; /*!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 `table_11` -- DROP TABLE IF EXISTS `table_11`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `table_11` ( `id` varchar(200) NOT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `table_11` -- LOCK TABLES `table_11` WRITE; /*!40000 ALTER TABLE `table_11` DISABLE KEYS */; INSERT INTO `table_11` VALUES ('12321','hehe'); /*!40000 ALTER TABLE `table_11` 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 2023-11-04 14:44:55- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
成功了
3、导出表结构却不导出表数据——只返回特定数据库特定表格的表格结构，不返回数据,添加“-d”命令参数
D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 -d migration_one table_11 > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t.sql- 1
执行结果：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 -d migration_one table_11 > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
- 3
查看
table_11_t.sql：-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: migration_one -- ------------------------------------------------------ -- Server version 8.0.17 /*!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 utf8 */; /*!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 `table_11` -- DROP TABLE IF EXISTS `table_11`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `table_11` ( `id` varchar(200) NOT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 2023-11-04 15:04:43- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
3、导出数据却不导出表结构——只返回特定数据库中特定表格的数据，不返回表格结构，添加“-t”命令参数
D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 -t migration_one table_11 > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_d.sql- 1
执行结果：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 -t migration_one table_11 > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_d.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
- 3
查看
table_11_d.sql-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: migration_one -- ------------------------------------------------------ -- Server version 8.0.17 /*!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 utf8 */; /*!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 */; -- -- Dumping data for table `table_11` -- LOCK TABLES `table_11` WRITE; /*!40000 ALTER TABLE `table_11` DISABLE KEYS */; INSERT INTO `table_11` VALUES ('12321','hehe'); /*!40000 ALTER TABLE `table_11` 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 2023-11-04 15:12:00- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
确实是只插入数据。
4、导出特定数据库的所有表格的表结构及其数据，添加“–databases ”命令参数
D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 --databases migration_one > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\migration_one_database.sql- 1
执行结果：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin\mysqldump.exe -u root -h 127.0.0.1 -proot -P3307 --databases migration_one > D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\migration_one_database.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
查看
migration_one_database.sql：-- MySQL dump 10.13 Distrib 8.0.17, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: migration_one -- ------------------------------------------------------ -- Server version 8.0.17 /*!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 utf8 */; /*!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 */; -- -- Current Database: `migration_one` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `migration_one` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */; USE `migration_one`; -- -- Table structure for table `table_11` -- DROP TABLE IF EXISTS `table_11`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `table_11` ( `id` varchar(200) NOT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `table_11` -- LOCK TABLES `table_11` WRITE; /*!40000 ALTER TABLE `table_11` DISABLE KEYS */; INSERT INTO `table_11` VALUES ('12321','hehe'); /*!40000 ALTER TABLE `table_11` 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 2023-11-04 15:18:42- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
5、mysql导出具特定条件的表数据（
--where="文本"）mysql根据条件导出表数据（
--where=“文本“）-CSDN博客二、导入（恢复）数据
导入（恢复）数据就用我在本地机MySQL5.7.19中名为
demo这个库来实验吧。上面导出（备份）数据是在mysql8进行的，但是本地不满意安装第二个MySQL8所以就用之前的MySQL5.7.19来做数据的导入。
先启动MySQL5.7.19：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>net start MYSQL MySQL 服务正在启动 . MySQL 服务已经启动成功。- 1
- 2
- 3
输入登录命令，我的就用sqlyog连接工具来理解吧，账号密码也是要输入的：
SELECT VERSION();- 1
结果：
version() ----------- 5.7.19- 1
- 2
- 3
的确是5.7.19版本
1、恢复整个数据库
先查看当前是否存在库
migration_one：SELECT 1 FROM information_schema.schemata WHERE schema_name='migration_one';- 1
不存在那就恢复，恢复前先找到mysql-5.7.19的mysql.exe：
SHOW VARIABLES LIKE "character_sets_dir";- 1
得到结果：
Variable_name Value ------------------ ------------------------------------------------------------ character_sets_dir D:\start_java\lwsmysql\mysql-5.7.19-winx64\share\charsets\- 1
- 2
- 3
于是mysql-5.7.19的mysql.exe在
D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe管理员打开终端输入：
D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\migration_one_database.sql- 1
执行结果：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\migration_one_database.sql mysql: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
在数据库连接工具sqlyog执行查询可以验证确实是恢复成功了：
SELECT 1 FROM information_schema.schemata WHERE schema_name='migration_one';- 1
得到：
-- 1个结果： 1 -------- 1 -- 3条信息： 返回了 1 行 执行耗时 : 0 sec 传送时间 : 0 sec 总耗时 : 0 sec- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
SELECT * FROM migration_one.table_11;- 1
-- 1个结果： id name ------ -------- 12321 hehe -- 3条信息： 返回了 1 行 执行耗时 : 0 sec 传送时间 : 0 sec 总耗时 : 0 sec- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
nice看来是在mysql-5.7.19恢复成功了。
2、恢复数据表结果及其数据：
这次我们要将
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t_d.sql执行以在mysql-5.7.19的demo这个库中恢复migration_one.table_11这个表及其数据。恢复前先看看
demo是否存在table_11表：SELECT * FROM information_schema.tables WHERE table_schema='demo';- 1
结果：
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT ------------- ------------ ----------------- ---------- ------ ------- ---------- ---------- -------------- ----------- --------------- ------------ --------- -------------- ------------------- ----------- ---------- --------------- -------- -------------- --------------- def demo persistent_logins BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 (NULL) 2022-11-17 10:28:14 (NULL) (NULL) utf8_general_ci (NULL) def demo users BASE TABLE InnoDB 10 Dynamic 2 8192 16384 0 0 0 3 2022-11-15 19:12:49 (NULL) (NULL) utf8_general_ci (NULL)- 1
- 2
- 3
- 4
发现
table_11不存在，于是可以安心在demo这个数据库恢复table_11表了：管理员打开终端执行
D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 demo < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t_d.sql- 1
执行成功：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 demo < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t_d.sql mysql: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
验证
table_11_t_d表及其数据是否已在demo恢复：SELECT * FROM information_schema.tables WHERE table_schema='demo';- 1
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT ------------- ------------ ----------------- ---------- ------ ------- ---------- ---------- -------------- ----------- --------------- ------------ --------- -------------- ------------------- ------------------- ---------- --------------- -------- -------------- --------------- def demo persistent_logins BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 (NULL) 2022-11-17 10:28:14 (NULL) (NULL) utf8_general_ci (NULL) def demo table_11 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 (NULL) 2023-11-04 16:24:28 2023-11-04 16:24:28 (NULL) utf8_general_ci (NULL) def demo users BASE TABLE InnoDB 10 Dynamic 2 8192 16384 0 0 0 3 2022-11-15 19:12:49 (NULL) (NULL) utf8_general_ci (NULL)- 1
- 2
- 3
- 4
- 5
再验证：
SELECT * FROM demo.table_11;- 1
结果：
id name ------ -------- 12321 hehe- 1
- 2
- 3
数据恢复成功！
这里是恢复数据表结构及其数据，看一眼
table_11_t_d.sql即可发现里面的逻辑就是【判断表是否存在，存在即删除该表->创建该表->加锁->插入所有数据->释放锁】很好懂的，看sql见其应用场景。
3、仅仅恢复表数据
这里便是之前导出的
table_11_d.sql的应用场景了，看里面的逻辑：【加锁->插入所有数据->释放锁】就知道首先得存在一张名为table_11的表。先干掉
demo.table_11的数据：TRUNCATE demo.table_11; SELECT * FROM demo.table_11;- 1
- 2
接下来管理员打开终端执行：
D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 demo < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_d.sql- 1
执行结果：
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 demo < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_d.sql mysql: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
再用MySQL命令执行：
SELECT * FROM demo.table_11;- 1
即可看到表数据恢复。
4、仅仅恢复表结构
这里便是之前导出的
table_11_t.sql的应用场景了，看里面的逻辑：【判断表是否存在，存在即删除该表->创建该表】就知道首先删除
demo库里存在名为table_11的表：DROP TABLE demo.table_11; SELECT 1 FROM information_schema.tables WHERE table_schema='demo' AND table_name ='table_11'; SELECT * FROM information_schema.tables WHERE table_schema='demo';- 1
- 2
- 3
- 4
- 5
通过结果得知
demo.table_11已祭天。接下来管理员打开终端执行：
D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 demo < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t.sql- 1
执行结果:
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe -u root -h 127.0.0.1 -pxinqi -P3306 demo < D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t.sql mysql: [Warning] Using a password on the command line interface can be insecure.- 1
- 2
通过以下语句得知验证恢复表结构成功:
SELECT 1 FROM information_schema.tables WHERE table_schema='demo' AND table_name ='table_11'; SELECT * FROM information_schema.tables WHERE table_schema='demo'; SELECT * FROM demo.table_11;- 1
- 2
- 3
- 4
- 5
nice!
参考
MySql数据库备份与恢复——使用mysqldump 导入与导出方法总结_mysql备份-CSDN博客
MySQL数据库（表）的导入导出（备份和还原） mysql 根据一张表数据更新另一张表-腾讯云开发者社区-腾讯云 (tencent.com)
MySQL判断库、表、列是否存在 - 钟小嘿 - 博客园 (cnblogs.com)
其他可能有用或可以学习得参考：
-
相关阅读:
Bootstrap框架
Python中的正则表达式：常见问题与解决方案
云呐|机房监控服务平台，机房监控服务平台有哪些
实验3 7段数码管译码器动态显示
设计模式之备忘录模式
LazSerial - 二进制数据传输方式
java安全之CC1浅学(2)
构建卓越语言模型应用的利器：LangChain | 开源日报 No.39
QT在scrollArea中添加按钮，可滚动
ScrollView 源码注解
- 原文地址：https://blog.csdn.net/ws_please/article/details/134221505
