The simplest MySQL Data Backup and restoration tutorial (I) (35) in history, mysql Data Backup

The simplest MySQL Data Backup and restoration tutorial (I) (35) in history, mysql Data Backup

The first article on data backup and restoration is as follows:

Basic concepts:

Backup, save the existing data or records in another copy;

Restores the data to the backup status.

Why do we need to back up and restore Data?

Prevent data loss;
Protect data records.

There are many data backup and restoration methods, including data table backup, single table data backup, SQL backup, and Incremental backup.

Data Table backup

You do not need to use SQL to back up data tables. You can directly copy the corresponding table structure and data to the database folder. When you need to restore data, you can directly copy the backup (copy) content.

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

For the storage engine, MySQL mainly uses two types: InnoDB and Myisam, both of which are free of charge. Here, let's take a look at the knowledge of the storage engine:

The Data Storage Methods of Myisam and InnoDB are also different:

Myisam: tables, data, and indexes are stored separately;
InnoDB: only table structures are supported. All data is stored in ibd files.

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

-- Create Myisam table create table my_myisam (id int) charset utf8 engine = myisam; -- display table structure show create table my_myisam; -- insert data insert into my_myisam values (1), (2 ), (3); -- display data select * from my_myisam;

As shown in, we have created a data table named my_myisam and the storage engine is Myisam. To verify the storage features of Myisam, you can view the specific data storage in the data folder:

As shown in, we only created a table my_myisam. However, Myisam generates three storage files:

My_myisam.frm: stores the table structure;
My_myisam.MYD: Stores Table data;
My_myisam.MYI: stores the table index.

Now, copy these three files to the testoo database (for how to locate the storage location of MySQL Data Files, refer to the detailed method to view the storage location of MySQL Data Files ):

Run the following SQL statement for testing:

-- Switch to the database use testoo; -- view the table show tables in the testoo database; -- view the table my_myisamselect * from my_myisam;

As shown in, we have already backed up data tables by copying files.

Here, we need to note that we can use the InnoDB Storage engine. frm and. copy the idb file to another database. You can also run the show tables command to view the copied table name, but you cannot obtain the data.

Run the following SQL statement for testing:

-- View the table show tables in the testoo database; -- view the table my_classselect * from my_class;

Through the above tests, it is clear that the data table backup method is more suitable for the Myisam storage engine, and the backup method is also very simple, directly copying the data generated by the Myisam storage engine. frm ,. MYD and. the MYI three files can be stored in the new database.

Tip: The content enclosed by the symbol [] indicates the option; the symbol + indicates the meaning of the connection.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.