Goal:
Export the table_11 data table of the migration_one database in the MySQL service with username=root, password=root, port=3307 on the local host to the local D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11.bak Note: Currently, the file table_11.bak does not exist in the folder D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL.
Let’s first take a look at the data in the data table:
SELECT * FROM migration_one.`table_11`;
result:
id name -------------- 12321 hehe
It seems that there is only one line
1. Export data:
1. Export the .bak file of backup data:
Use my mysql8’s mysqldump tool, but currently I need to find where my mysql8 is:
SHOW VARIABLES LIKE "character_sets_dir";
got the answer:
Variable_name Value --------------------------------------------------- ---------------------------- character_sets_dir D:\start_java\lwsmysql\mysql-8.0.17-winx64\share\charsets\
So I went to the D:\start_java\lwsmysql\mysql-8.0.17-winx64\bin folder and saw a mysqldump.exe inside.
Then you can execute the command to back up the file, but you need to delete the original table_11.bak.
The administrator opens the terminal and executes:
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
Get execution results:
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.
Then open the table_11.bak file:
--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
Happy success! This is considered a normal backup file.
2. Export data and table structure – return all data, table structure and data in a specific table of a specific database
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
The administrator opens cmd and enters the D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL folder to execute the above command.
Results of the:
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.
Found that there is an extra table_11_t_d.sql file in the D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL folder, which is:
--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
Successful
3. Export the table structure but not the table data – only return the table structure of a specific table in a specific database, without returning data, add the “-d” command parameter
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
Results of the:
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.
View 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
3. Export data but not table structure – only return the data of a specific table in a specific database, not the table structure, add the “-t” command parameter
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
Results of the:
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.
View 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
It’s really just inserting data.
4. Export the table structure and data of all tables in a specific database and add the “–databases” command parameter
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
Results of the:
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.
View 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
5. mysql exports table data with specific conditions (--where="text")
mysql exports table data based on conditions (--where="text")-CSDN Blog
2. Import (restore) data
To import (recover) data, let’s experiment with the library named demo in my local MySQL 5.7.19.
The above export (backup) data was performed in mysql8, but the local machine was not satisfied with the installation of the second MySQL8, so the previous MySQL5.7.19 was used to import the data.
Start MySQL5.7.19 first:
D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL>net start MYSQL MySQL service is starting. The MySQL service has been started successfully.
Enter the login command. I will use the sqlyog connection tool to understand it. The account password also needs to be entered:
SELECT VERSION();
result:
version() ---------- 5.7.19
It is indeed version 5.7.19
1. Restore the entire database
First check whether the library migration_one currently exists:
SELECT 1 FROM information_schema.schemata WHERE schema_name='migration_one';
If it does not exist, restore it. Before restoring, find the mysql.exe of mysql-5.7.19:
SHOW VARIABLES LIKE "character_sets_dir";
got the answer:
Variable_name Value --------------------------------------------------- ---------------------------- character_sets_dir D:\start_java\lwsmysql\mysql-5.7.19-winx64\share\charsets\
So the mysql.exe of mysql-5.7.19 is in D:\start_java\lwsmysql\mysql-5.7.19-winx64\bin\mysql.exe
The administrator opens the terminal and enters:
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
Results of the:
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.
Executing a query in the database connection tool sqlyog can verify that the recovery was successful:
SELECT 1 FROM information_schema.schemata WHERE schema_name='migration_one';
get:
-- 1 result:
1
--------
1
-- 3 messages:
1 row returned
Execution time: 0 sec
Transmission time: 0 sec
Total time taken: 0 sec
SELECT * FROM migration_one.table_11;
-- 1 result: id name -------------- 12321 hehe -- 3 messages: 1 row returned Execution time: 0 sec Transmission time: 0 sec Total time taken: 0 sec
Nice seems to have been restored successfully in mysql-5.7.19.
2. Restore data table results and data:
This time we are going to execute D:\start_java\XinQiUtilsOrDemo\testMigrationMySQL\table_11_t_d.sql to restore migration_one.table_11This table and its data.
Before restoring, check whether demo exists in the table_11 table:
SELECT * FROM information_schema.tables WHERE table_schema='demo';
result:
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)
It is found that table_11 does not exist, so you can rest assured to restore the table_11 table in the demo database:
The administrator opens the terminal and executes
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
execution succeed:
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.
Verify whether the table_11_t_d table and its data have been restored in demo:
SELECT * FROM information_schema.tables WHERE table_schema='demo';
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)
Verify again:
SELECT * FROM demo.table_11;
result:
id name -------------- 12321 hehe
Data recovery successful!
Here is the recovery of the data table structure and its data. Take a look at table_11_t_d.sql and you will find that the logic inside is [determine whether the table exists, delete the table if it exists -> create the table -> lock - >Insert all data->Release lock】
It's easy to understand. You can see its application scenarios by looking at SQL.
3. Only restore table data
Here is the application scenario of the previously exported table_11_d.sql. Looking at the logic inside: [Lock->Insert all data->Release lock], you will know that there must first be a file named table_11's table.
First get rid of the data of demo.table_11:
TRUNCATE demo.table_11; SELECT * FROM demo.table_11;
Next, the administrator opens the terminal and executes:
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
Results of the:
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.
Then use the MySQL command to execute:
SELECT * FROM demo.table_11;
You can see the table data restored.
4. Only restore the table structure
Here is the application scenario of the previously exported table_11_t.sql. Look at the logic inside: [Judge whether the table exists, delete the table if it exists -> Create the table]
Just know that first delete the table named table_11 that exists in the demo library:
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';
It is known from the results that demo.table_11 has been sacrificed to heaven.
Next, the administrator opens the terminal and executes:
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
Results of the:
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.
The following statement shows that the verification of the restored table structure is successful:
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;
nice!
Reference
MySql database backup and recovery - summary of import and export methods using mysqldump_mysql backup-CSDN blog
Import and export (backup and restore) of MySQL database (table) mysql updates one table based on data in another table - Tencent Cloud Developer Community - Tencent Cloud (tencent.com)
MySQL determines whether a library, table, or column exists - Zhong Xiaohe - Blog Park (cnblogs.com)
Other references that may be useful or can be learned from:
mysql mysqldump command exports the data of the specified table - Ruthless - Blog Park (cnblogs.com)