Shulou(Shulou.com)06/01 Report--

This article mainly introduces MySQL how to achieve data backup and restore simple methods, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively large, the following with the author to understand MySQL how to achieve data backup and restore simple method it.

Basic concepts:

Backup, save a copy of existing data or records;

Restore, which restores data to the state it was in at the time of backup.

Why backup and restore data?

Prevention of data loss;

Protection of data records.

There are many ways to backup 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, do not need to backup through SQL, we can directly enter the database folder copy the corresponding table structure and data; when you need to restore data, directly put the backup (copy) content back.

However, there are prerequisites for wanting to backup data tables, because there are differences between different storage engines.

MySQL uses two main storage engines: InnoDB and Myisam, both of which are free. Here, we can take a look at the knowledge of the storage engine:

Myisam and InnoDB also differ in their data storage methods:

Myisam: tables, data and indexes are stored separately;

InnoDB: Only table structure, all data stored in ibd file.

Execute the following SQL statement to test how Myisam stores data:

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

As shown above, we created a data table named my_myisam with Myisam as the storage engine. To verify Myisam's storage features, we can go to the data folder to see the specific data storage situation:

As shown above, we only created a table my_myisam, but Myisam generates three storage files for:

my_myisam.frm: stores the structure of the table;

my_myisam.MYD: stores the table's data;

myisam.MYI: Store the index of the table.

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

Execute the following SQL statement to test:

--switch database use testoo;--view tables in testoo database show tables;--view tables my_myisamselect * from my_myisam;

As shown above, it is clear that we have completed the backup of the data table by copying files.

One thing to note here is that we can copy the.frm and.idb files generated by the InnoDB storage engine to another database, or we can view the copied table names with the show tables command, but we can't get the data.

Execute the following SQL statement to test:

--View table show tables in testoo database;--View table my_classelect * from my_class;

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

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

After reading the above simple methods on how MySQL achieves data backup and restore, many readers must have some understanding, if you need to obtain more industry knowledge information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.