The simplest MySQL data backup and restore tutorial in history

This article mainly introduces in detail the first tutorial on the simplest MySQL data backup and restoration in history. It has certain reference value. Interested friends can refer to it. I hope it can help you.

The first article of data backup and restoration is shared with everyone. The specific content is as follows

Basic concepts:

Backup, the current existing data or Save another copy of the record;

Restore, restore the data to the state at the time of backup.

Why do we need to back up and restore data?

Prevent data loss;
Protect data records.

There are many ways to back up and restore data, which can be divided into: data table backup, single table data backup, SQL backup and incremental backup.

Data table backup

Data table backup does not need to be backed up through SQL. We can directly enter the database folder to copy the corresponding table structure and data; when needed When restoring data, just put back the backed up (copied) content.

However, there are prerequisites for data table backup, because there are differences between different storage engines.

For storage engines, MySQL mainly uses two types: InnoDB and Myisam, both of which are free. Here, we can popularize the knowledge of storage engines by the way:

Among them, the data storage methods of Myisam and InnoDB are also different:

Myisam: table , data and indexes are all stored separately;
InnoDB: only the table structure, all data is stored in the ibd file.

Execute the following SQL statement to test the data storage method of Myisam:

-- 创建 Myisam 表
create table my_myisam(
 id int
)charset utf8 engine = myisam;

-- 显示表结构
show create table my_myisam;

-- 插入数据
insert into my_myisam values(The simplest MySQL data backup and restore tutorial in history),(2),(3);

-- 显示数据
select * from my_myisam;

As shown in the figure above, we created a storage named my_myisam The engine is Myisam's datasheet. In order to verify the storage characteristics of Myisam, we can go to the data folder to view the specific data storage situation:

As shown in the figure above, we only created a table my_myisam, but Myisam Three storage files will be generated, namely:

my_myisam.frm: the structure of the storage table;
my_myisam.MYD: the data of the storage table;
my_myisam.MYI: the index of the storage table.

Now, we copy these three files to the testoo database (as for how to find the storage location of the MySQL data file, you can refer to the detailed method of viewing the storage location of the MySQL data file):

Execute the following SQL statement to test:

-- 切换数据库
use testoo;

-- 查看 testoo 数据库中的表
show tables;

-- 查看表 my_myisam
select * from my_myisam;

As shown in the picture above, obviously we have completed the process by copying the file Performed data table backup work.

Here, one thing we need to pay attention to is: we can copy the .frm and .idb files generated by the InnoDB storage engine to another database, or we can view the copied tables through the show tables command. name, but the data cannot be obtained.

Execute the following SQL statement to test:

-- 查看 testoo 数据库中的表
show tables;

-- 查看表 my_class
select * from my_class;

Through the above test, it is obvious that the data table backup is this kind of backup This method is more suitable for the Myisam storage engine, and the backup method is also very simple. Just copy the three storage files .frm, .MYD and .MYI generated by the Myisam storage engine to the new database.

Warm reminder: The content enclosed by the symbol [] indicates optional options; the symbol + indicates connection.

Related recommendations:

Mysql data backup logical backup recovery method

Mysql data backup logical backup

MySQL data backup and recovery related operation commands

The above is the detailed content of The simplest MySQL data backup and restore tutorial in history. For more information, please follow other related articles on the PHP Chinese website!