The simplest MySQL data backup and restore tutorial in history (Part 1) (Part 35)

The first article on data backup and restoration is shared with you. The specific content is as follows

Basic concepts:

Backup, save another copy of the current data or records;

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

To back up the data table, we do not need to use SQL. We can directly enter the database folder to copy the corresponding table structure and data. When we need to restore the data, we can simply put the backup (copy) content back.

However, there are prerequisites for backing up data tables because different storage engines are different.

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

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

Myisam: tables, data and indexes are all stored separately;
InnoDB: Only the table structure is present, and all data is stored in ibd files.

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

-- Create a 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 the figure above, we created a data table named my_myisam with the storage engine being Myisam. 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 will generate three storage files, namely:

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

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

Execute the following SQL statement to test:

-- Switch database use testoo;

-- View the tables in the testoo database show tables;

-- View table my_myisam
select * from my_myisam;

As shown in the figure above, we have obviously completed the backup of the data table by copying the file.

Here, there is one thing we need to pay attention to, that is: we can copy the .frm and .idb files generated by the InnoDB storage engine to another database, and we can also view the copied table names through the show tables command, but we cannot obtain the data.

Execute the following SQL statement to test:

-- View the tables in the testoo database show tables;

-- View table my_class
select * from my_class;

Through the above tests, it is obvious that the data table backup 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.

Tips: The content enclosed by the symbol [] indicates optional items; the symbol + means connection.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.