MySQL Backup and recovery

The first step is to prepare the work:

to avoid inconsistencies in data, updates to data in the database are not allowed during backup of the database file. The simplest way to achieve this is to stop the MySQL service. Then, copy the backup files to another storage space.

Alternatively, instead of stopping the MySQL service, use the mysql command "flush tables with read lock" to flush the data in the server's memory into the database file. All tables are locked at the same time to ensure that no new data is written during the backup, thereby avoiding inconsistencies in the data. After the backup file is copied to another storage space , the mysql command "unlock tables" is used to lock the file, and theMySQL service instance can re-provide the data update service to perform the result operation.

Note: TheMySQL command "flush tables with read lock" prohibits update operations for all database tables, but cannot prohibit query operations on database tables.

Step Two:

The data in the MySQL database eventually exists as a file. After the backup is ready, do you need to copy those files from the database to disk?

If the database is all MyISAM storage engine tables, the simplest method of database backup is to "back up" the entire database directory (for example, the choose that corresponds to the choose Database Copy the directory to disk).

If a table for the InnoDB storage engine exists in a database, you need not only to "back up" the entire database directory, but also to back up the ibdata1 tablespace file and redo the log file Ib_logfile0 and ib_logfile1.

when backing up a database, it is recommended that you back up the MySQL configuration file together.

Step Three:

Stop the MySQL service First, then the entire database directory, themysql configuration file, theibdata1 tablespace file, and the redo log file Ib_logfile0 and The ib_logfile1 is copied to the new MySQL Server directory so that the data in the database can be recovered.

If the new MySQL server differs from the database root of the old MySQL server, you also need to modify the "mysqld" in the my.ini configuration file The datadir reference information in the option group.