The method of backup and restore of Mysql database of virtual host _mysql

Previously introduced a small MySQL backup php script program, but it is not for MySQL shell permissions of the virtual host users, here want to talk about more common backup and restore methods, suitable for their own host friends.

1, about Backup: We use MySQL in the general use of MyISAM types of tables, which are saved as a file in the file system, and the heap type of table is stored in memory, the following method is for the MyISAM table.

1.1, directly copy the database files. This method is not recommended first, because a copied datasheet is intended for a particular operating system (hardware environment) and may cause problems in moving to other systems. Second backup to stop the MySQL server or lock the corresponding table, otherwise, if the copy process in the Insert/overwrite database is hung, not suitable for the need for uninterrupted server. The most benefit of this method is convenient and quick, backup time just copy MySQL data directory in the corresponding database folder of all the files, which *.frm is described in the structure of the table, *. MyD saves the data records of the table, *. Myi is the index of the table.

1.2, the use of mysqldump tools: The use of this program can be exported to the database into the form of SQL statements text files, easy to move to different systems, restore time will be the execution of the SQL statements in the file can be rebuilt database. When actually in use, in Windows cmd or Linuxshell enter the command:
Mysqldump-u user_name-p database_name--option > path:/xxx.sql; carriage return
; user_name is the user name
;d Atabase_name is the database name
;p Ath:/xxx.sql is the path and filename to save the backup file

Then enter the password to export the database to the. sql file under the specified path, ensuring that the user has sufficient access to the appropriate database. --option is a backup can be specified some of the parameters, you can refer to the MySQL official documentation, a number of common options:
--opt--a set of common default options, including--add-drop-table--add-locks--create-options--disable-keys--extended-insert--lock-tables- Quick--set-charset
--add-drop-table--the old table before setting up the table, suitable for overwriting the database
--where--a partial record of a conditional backup, for example:--where= "id<1000"
The Mysqldump tool's parameter function is quite many, but also needs to study well the next ~

2, about restore
2.1, for direct copy of the database file method, the corresponding file cover back to the data directory can be restored.
2.2, for dump out of the. sql file, you can enter in the MySQL command line:
Use target_database; Select the database you want to restore, preferably a new empty
\. Path:/xxx.sql to restore the specified backup file to the current database