Mysql database backup and Restoration

MySQL supports the following free backup tools: mysqldump and mysqlhotcopy. You can also use SQL syntax for backup: BACKUPTABLE or SELECTINTOOUTFILE, or binlog backup ), you can also directly copy data files and related configuration files.

MySQL supports the following free BACKUP tools: mysqldump and mysqlhotcopy. You can also use SQL syntax to back up: BACKUP TABLE or SELECT INTO OUTFILE, or back up binary logs (binlog ), you can also directly copy data files and related configuration files.

Database Backup:

Open cmd and execute the following statement:

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

Database Restoration:

Open cmd and execute the following statement:

The Code is as follows:
Mysql-uroot-proot databasename <d: databasename. SQL

When the database backup and restoration operations are not performed on the same database server, the encoding of the mysql software installed on the two servers may occur.

For example, an error message such as "Unknown command ''' will be displayed during database restoration ."

You can use

The Code is as follows:
Mysql-uroot-proot -- default-character-set = gb2312 databasename <d: databasename. SQL Mysqlhotcopy

2.1 backup
Mysqlhotcopy is a PERL program originally written by Tim Bunce. It uses lock tables, flush tables, and cp or scp to quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database file (including the data table definition file, data file, and index file) is located. Mysqlhotcopy can only be used to back up MyISAM and can only run on Unix-like and NetWare systems.

Mysqlhotcopy supports copying multiple databases at a time and regular expression. The following are examples:

The Code is as follows:
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 usage, refer to the manual or call the following command to view the help of mysqlhotcopy:

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

Note: To use mysqlhotcopy, you must have the SELECT and RELOAD permissions (to execute flush tables) and be able to read the datadir/db_name directory.

2.2 restore

Mysqlhotcopy backs up the entire database Directory, which can be copied directly to the datadir (/usr/local/mysql/data/) directory specified by mysqld during use, pay attention to permission issues as follows:

The Code is as follows:
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 the mysqld running user)