Virtual host MySQL database backup and restore methods
- 2020-05-06 11:46:33
- OfStack
Previously introduced a small MYSQL backup PHP script, but that is suitable for the virtual host users without MySQL Shell permission, here want to talk about a more general backup and restore method, suitable for the friends who have their own host.
1. About backup: we generally use MyISAM type tables in MySQL, which are saved as files in the file system. While tables of type HEAP are kept in memory, the following method is for MyISAM tables.
1.1 directly copy database files. This approach is not recommended in the first place because the copied tables are specific to a particular operating system (hardware environment) and moving to another system may cause problems. Second, stop the MYSQL server or lock the corresponding table during the backup. Otherwise, if the database is inserted/overwritten during the copy process, it will fail. The biggest advantage of this method is that it is convenient and fast, when backup, just copy all the files in the corresponding database folder of MYSQL data directory, where *.frm describes the structure of the table, *.MYD saves the data record of the table, *.MYI is the index of the table.
1.2. MySQLDump tool: this program can be used to export the database into a text file in the form of SQL statements, which is convenient for moving to different systems. When restoring, SQL statements in the file will be executed one by one to rebuild the database. In practice, type
in CMD under WINDOWS or LinuxShell under
mysqldump -u user_name -p database_name --option > path: / xxx. sql; Enter
; user_name is the username
; database_name is the database name
; path:/ xxx.sql is the path to save the backup file and the file name
Enter the password and you will export the database to the.sql file under PATH. Make sure the user has sufficient access to the appropriate database. --option is a number of parameters that can be specified when backup, you can refer to the official MYSQL documentation, common options:
--opt -- a set of commonly used default options, Es58en-drop-table add-options -- add-drop-table -- DROP old tables are created before DROP old tables, suitable for overwriting database
--where -- back up part of the record conditionally, e.g. --where="id<" 1000'"
The mysqldump tool has a lot of parameter functions, so we still need to study ~
2. About restoring
2.1 for the method of directly copying database files, the corresponding files can be overwritten back to the data directory to restore.
2.2 for the.SQL file produced by DUMP, you can type:
on the MYSQL command line use target_database; Select the database you want to restore and preferably create an empty
\. path: / xxx. sql; Restores the specified backup file to the current database
1. About backup: we generally use MyISAM type tables in MySQL, which are saved as files in the file system. While tables of type HEAP are kept in memory, the following method is for MyISAM tables.
1.1 directly copy database files. This approach is not recommended in the first place because the copied tables are specific to a particular operating system (hardware environment) and moving to another system may cause problems. Second, stop the MYSQL server or lock the corresponding table during the backup. Otherwise, if the database is inserted/overwritten during the copy process, it will fail. The biggest advantage of this method is that it is convenient and fast, when backup, just copy all the files in the corresponding database folder of MYSQL data directory, where *.frm describes the structure of the table, *.MYD saves the data record of the table, *.MYI is the index of the table.
1.2. MySQLDump tool: this program can be used to export the database into a text file in the form of SQL statements, which is convenient for moving to different systems. When restoring, SQL statements in the file will be executed one by one to rebuild the database. In practice, type
in CMD under WINDOWS or LinuxShell under
mysqldump -u user_name -p database_name --option > path: / xxx. sql; Enter
; user_name is the username
; database_name is the database name
; path:/ xxx.sql is the path to save the backup file and the file name
Enter the password and you will export the database to the.sql file under PATH. Make sure the user has sufficient access to the appropriate database. --option is a number of parameters that can be specified when backup, you can refer to the official MYSQL documentation, common options:
--opt -- a set of commonly used default options, Es58en-drop-table add-options -- add-drop-table -- DROP old tables are created before DROP old tables, suitable for overwriting database
--where -- back up part of the record conditionally, e.g. --where="id<" 1000'"
The mysqldump tool has a lot of parameter functions, so we still need to study ~
2. About restoring
2.1 for the method of directly copying database files, the corresponding files can be overwritten back to the data directory to restore.
2.2 for the.SQL file produced by DUMP, you can type:
on the MYSQL command line use target_database; Select the database you want to restore and preferably create an empty
\. path: / xxx. sql; Restores the specified backup file to the current database