A history of the tears of a database backup

Alas, one not careful enough to cause a friend of a website MySQL data all gone. So-called a fall into your wit, so think or write an article to remind the surrounding small friends. In the spirit of saving lives, medicine to eradicate the consistent policy, a lot of decisions from the MySQL common sense to start.

Introduction to database Files

Each MySQL database corresponds to a folder with the same name as the database. In addition, the complete database file consists of the following two parts:

The first part: MySQL Database files built

Files with the suffix. frm: the frame structure that stores the data table, the file name is the same as the table name, and each table corresponds to a frm file of the same name, regardless of the operating system and storage engine, that is, regardless of the operating system on which MySQL runs, which storage engine is used.

Part Two: The database files created by the storage engine used by MySQL, and the storage engine creates separate database files. Based on the different storage engines used by MySQL (Common storage engines are MyISAM and InnoDB)

1, MyISAM database table files include (myd file: That is, my data, the table file;. Myi file: That is my index, index file;. log file: Log file)

2. InnoDB database files include (ibdata1, IBDATA2, etc.: System tablespace files, storage InnoDB system Information and user database table data and indexes, all tables are shared;. ibd Files: Single-table tablespace files, each using a tablespace file (file per table), storing user database tables data and indexes; Log files: Ib_logfile1, Ib_logfile2)

　MySQL Database storage location

1, MySQL If you use the MyISAM storage engine, the database file type includes. frm,. MYD,. MYI, the default storage location is/var/lib/mysql/data/your database name (I use Ubuntu), note: MyISAM is the default engine.

2, MySQL If you use the InnoDB storage engine, the database file type includes. frm, ibdata1,. IBD, storage location two,. frm file default location is/var/lib/mysql/data/your database name, IBDATA1,. The default storage location for IBD files is/var/lib/mysql/data.

PS: See no, MyISAM just back up a directory with the same name as the database, and InnoDB also need to back up ibdata1, IBD files. (This is my tragic point, woo-hoo ~ ~ ~ ~).

　　Back to the history of tears

Because the default engine is MyISAM, when friends let me back up, I do not think that is directly back up the MySQL data directory with the database name of the same directory, and then recovered to find the original friend is the InnoDB engine, tragedy so produced, alas!

Lessons learned

When backing up data, be sure to look ahead to what the backup database is, and then identify the database files that need to be backed up, and friends who do not know where the database is stored, which can be found in the MY.CNF configuration file in the MySQL directory. Secretly tell you a simple big move, directly to the MySQL directory of the entire backup, do not you write database backup script? Also do not have to manually backup, to the multi-backup is good! One-click Backup multiple cloud disk data never lost, need to restore a key. Save effort and worry!

A history of the tears of a database backup