search
HomeDatabaseMysql TutorialTeach you how to restore a single table in MySQL through physical methods

This article brings you relevant knowledge about MySQL, which mainly introduces how to use physical methods to quickly restore a single table in MySQL, and teaches you step by step! Let’s take a look at it together, I hope it will be helpful to everyone.

Teach you how to restore a single table in MySQL through physical methods

Usage method

1. First create a test table test1 and insert several pieces of data:

mysql> create table test1 (id int auto_increment primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test1 (name) values ('张三'),('李四'),('王二');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

2. Create the target table test2:

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.10 sec)
查看数据目录里面的ibd文件(test2.ibd、test1.ibd):
-rw-r-----. 1 * * 114688 Nov  2 16:20 test1.ibd
-rw-r-----. 1 * * 114688 Nov  2 16:23 test2.ibd

3. Discard the idb file of table test2 through the alter table discard method (to prepare for the next step of copying the data of test1):

mysql> alter table test2 discard tablespace;   
Query OK, 0 rows affected (0.02 sec)

查看ibd文件情况,发现test2的ibd文件已经被删除

-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

4. Execute the following command, Generate a cfg file of test1, as follows:

mysql> flush table test1 for export; 
Query OK, 0 rows affected (0.00 sec)

生成了一个test1.cfg的cfg文件

-rw-r----- 1 * *    655 Nov  2 16:25 test1.cfg
-rw-r----- 1 * * 114688 Nov  2 16:20 test1.ibd

5. Copy the cfg file and ibd file of the source table test1 to the target table test2, and modify the file permissions:

cp test1.cfg test2.cfg
cp test1.ibd test2.ibd
chown -R mysql.mysql test2.*

6. After the copy is completed , execute the select command and find the following error:

mysql> select * from test2;
ERROR 1100 (HY000): Table 'test2' was not locked with LOCK TABLES

7. Execute unlock tables, release the test1.cfg file of the source table, and then import the ibd file:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

并用alter table的方法为目标表test2导入这个ibd文件:

mysql> alter table test2 import tablespace; 
Query OK, 0 rows affected (0.03 sec)
1 row in set (0.00 sec)

8. Execute select again and find that The data has been imported:

mysql> select * from test2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
3 rows in set (0.00 sec)

Introduction to physical copy method

The core of the above single table physical copy method lies in the cp command. Because it is a physical copy, if the copied table is very large, then through Physical copy will be much faster than logical SQL writing, such as insert into select statement.

简单总结一下上述物理复制过程:
  • 1. Create table like syntax creates an empty target table with the same table structure
  • 2. The target table executes alter table discard and discards the ibd file
  • 3. Execute the alter table for export syntax in the source table to generate the .cfg file and lock the table
  • 4. Use the cp command to copy the source table cfg file and ibd file to the target table
  • 5. Unlock tables releases the cfg file and lock of the source table
  • 6. The alter table import command imports the ibd data file of the target table.

alter table for export syntax introduction:

  • 1. This command is to refresh the data about this table in the memory to the disk to ensure that the data can be binlogged Recorded;
  • 2. This operation requires flush table or reload permission;
  • 3. This operation will hold the shared MDL lock of the current table, preventing other sessions from modifying the table structure. In FOR EXPORT The previously acquired MDL lock will not be released when the operation is completed and needs to be released manually
  • 4. InnoDB will generate a file named table_name.cfg in the same database directory as the table
  • 5 After processing the table copy, you need to use UNLOCK tables to release the MDL lock of the source table or disconnect the connection.

Note:

Because alter table for export locks the table, this method is more suitable to stop the replication relationship on the slave database, and then perform the table replication operation. If there is a business operation on the current source table, careful consideration is required.

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of Teach you how to restore a single table in MySQL through physical methods. For more information, please follow other related articles on the PHP Chinese website!

Statement
This article is reproduced at:cnblogs. If there is any infringement, please contact admin@php.cn delete
What are some tools you can use to monitor MySQL performance?What are some tools you can use to monitor MySQL performance?Apr 23, 2025 am 12:21 AM

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

How does MySQL differ from SQL Server?How does MySQL differ from SQL Server?Apr 23, 2025 am 12:20 AM

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In what scenarios might you choose SQL Server over MySQL?In what scenarios might you choose SQL Server over MySQL?Apr 23, 2025 am 12:20 AM

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.

How does MySQL handle character sets and collations?How does MySQL handle character sets and collations?Apr 23, 2025 am 12:19 AM

MySQLmanagescharactersetsandcollationsbyusingUTF-8asthedefault,allowingconfigurationatdatabase,table,andcolumnlevels,andrequiringcarefulalignmenttoavoidmismatches.1)Setdefaultcharactersetandcollationforadatabase.2)Configurecharactersetandcollationfor

What are triggers in MySQL?What are triggers in MySQL?Apr 23, 2025 am 12:11 AM

A MySQL trigger is an automatically executed stored procedure associated with a table that is used to perform a series of operations when a specific data operation is performed. 1) Trigger definition and function: used for data verification, logging, etc. 2) Working principle: It is divided into BEFORE and AFTER, and supports row-level triggering. 3) Example of use: Can be used to record salary changes or update inventory. 4) Debugging skills: Use SHOWTRIGGERS and SHOWCREATETRIGGER commands. 5) Performance optimization: Avoid complex operations, use indexes, and manage transactions.

How do you create and manage user accounts in MySQL?How do you create and manage user accounts in MySQL?Apr 22, 2025 pm 06:05 PM

The steps to create and manage user accounts in MySQL are as follows: 1. Create a user: Use CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password'; 2. Assign permissions: Use GRANTSELECT, INSERT, UPDATEONmydatabase.TO'newuser'@'localhost'; 3. Fix permission error: Use REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost'; then reassign permissions; 4. Optimization permissions: Use SHOWGRA

How does MySQL differ from Oracle?How does MySQL differ from Oracle?Apr 22, 2025 pm 05:57 PM

MySQL is suitable for rapid development and small and medium-sized applications, while Oracle is suitable for large enterprises and high availability needs. 1) MySQL is open source and easy to use, suitable for web applications and small and medium-sized enterprises. 2) Oracle is powerful and suitable for large enterprises and government agencies. 3) MySQL supports a variety of storage engines, and Oracle provides rich enterprise-level functions.

What are the disadvantages of using MySQL compared to other relational databases?What are the disadvantages of using MySQL compared to other relational databases?Apr 22, 2025 pm 05:49 PM

The disadvantages of MySQL compared to other relational databases include: 1. Performance issues: You may encounter bottlenecks when processing large-scale data, and PostgreSQL performs better in complex queries and big data processing. 2. Scalability: The horizontal scaling ability is not as good as Google Spanner and Amazon Aurora. 3. Functional limitations: Not as good as PostgreSQL and Oracle in advanced functions, some functions require more custom code and maintenance.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)