MySQL database backup and restore

Database backup:

Open cmd to execute the following statement:

The code is as follows	Copy Code
Mysqldump-h localhost-u root-proot databasename >d:databasename.sql

Database restore:

Open cmd to execute the following statement

The code is as follows	Copy Code
Mysql-uroot-proot DatabaseName < D:databasename.sql

When database backup and restore operations are not on the same database server, there may be coding problems with MySQL software installation for two servers

For example, when you restore a database, you will encounter the "Unknown command". "This error hint

You can use the

The code is as follows	Copy Code
Mysql-uroot-proot--default-character-set=gb2312 DatabaseName < D:databasename.sql Mysqlhotcopy

2.1 Backup
Mysqlhotcopy is a PERL program that was originally written by Tim Bunce. It uses LOCK tables, FLUSH tables, and CP or SCP to quickly back up databases. It is the quickest way to back up a database or a single table, but it can only run on the same machine as the database file (including the datasheet definition file, the data file, the index file). Mysqlhotcopy can only be used for backup MyISAM and can only be run on Unix-like and NetWare systems.

Mysqlhotcopy supports the one-time copying of multiple databases while also supporting regular expressions. Here are a few examples:

The code is as follows	Copy Code
Root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr db_name/tmp (Copy the database directory db_name to/tmp) Root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr db_name_1 ... db_name_n/tmp ROOT#/USR/LOCAL/MYSQL/BIN/MYSQLHOTCOPY-H=LOCALHOST-U=YEJR-P=YEJR db_name./regex//tmp

For more detailed instructions, check the manual, or call the following command to view Mysqlhotcopy's help:

The code is as follows	Copy Code
Perldoc/usr/local/mysql/bin/mysqlhotcopy

Note that to use mysqlhotcopy, you must have SELECT, RELOAD (to perform FLUSH TABLES) permissions, and you must also have permission to read the Datadir/db_name directory.

2.2 Restore

Mysqlhotcopy Backup is the entire database directory, use can be directly copied to the mysqld specified DataDir (here is/usr/local/mysql/data/) directory, but also attention to the issue of permissions, the following example:

The code is as follows	Copy Code
ROOT#CP-RF db_name/usr/local/mysql/data/ Root#chown-r nobody:nobody/usr/local/mysql/data/

(Change the owner of the Db_name directory to mysqld run user)