How to back up and restore the MySQL database of the VM

I have previously introduced a small MYSQL backup PHP script program, but it is suitable for virtual host users who do not have the MySQL Shell Permission. Here I would like to introduce more general backup and restoration methods, suitable for friends who have their own hosts.

1. About backup: We generally use MyISAM tables in MySQL. These tables are stored in the file system as files, while HEAP tables are stored in the memory, the following method is for the MyISAM table.

1.1 directly copy database files. This method is not recommended first, because the copied data table is for a specific operating system (hardware environment) and moving to another system may cause problems. Next, you need to stop the MYSQL server or lock the corresponding table when backing up the database. Otherwise, if the database is inserted or rewritten during the copy process, it is not suitable for servers that require continuous backup. The biggest advantage of this method is that it is convenient and convenient. During backup, you only need to copy all the files in the corresponding database folder of the mysql data directory, where *. frm describes the table structure ,*. MYD Stores Table data records ,*. MYI is the index of the table.

1.2 use MySQLDump: This program can be used to export a database as a text file in the form of SQL statements, so that you can easily move it to different systems. When restoring the database, the SQL statements in the execution file can be used to recreate the database. In windows cmd or LinuxShell, enter the following command:
Mysqldump-u user_name-p database_name -- option> path:/xxx. SQL; press ENTER
; User_name is the user name
; Database_name is the database name
; Path:/xxx. SQL is the path and file name for saving the backup file

Then, enter the password to export the database to the. SQL file in the specified PATH. Make sure that you have sufficient permissions to access the corresponding database. -- Option is a parameter that can be specified during backup. For details, refer to the MYSQL official documentation. Several common options are as follows:
-- Opt -- a group of common default options, including -- add-drop-table -- add-locks -- create-options -- disable-keys -- extended-insert -- lock-tables -- quick -- set-charset
-- Add-drop-table -- DROP the old table before creating the table, which is suitable for overwriting the database.
-- Where -- partial records are backed up according to conditions, for example: -- where = "id <1000 '"
The mysqldump tool has many parameter functions and needs to be carefully studied ~

2. About Restoration
2.1 if you copy database files directly, overwrite the corresponding files back to the data directory to restore them.
2.2 For DUMP. SQL files, enter the following in the MYSQL command line:
Use target_database; select the database to restore. It is best to create an empty
\. Path:/xxx. SQL; restore the specified backup file to the current database