Several ways to backup and restore MySQL data

mysqldump Command mode

Backup IP to 192.168.1.100 a library:

The code is as follows	Copy Code
D:/apm/apmserv5.2.6/mysql5.1/bin> Mysqldump-h 192.168.1.100-u root-p Ucenter >d:/sql/uc_20130306.sql Enter Password: * * *

To back up a table under a library:

The code is as follows	Copy Code
Mysqldump-u root-p Password dbname tablename>d:/test.sql

Back up the entire library:

The code is as follows	Copy Code
Mysqldump-u root-p Password –all-databases >d:/test.sql

To back up the structure of the dbname database:
Mysqldump-u root-p-d–add-drop-table dbname >d:/sql/a.sql
Note:-D No data –add-drop-table add a drop table before each CREATE statement
To import a database:

The code is as follows	Copy Code
D:/apm/apmserv5.2.6/mysql5.1/bin> Mysql–u root–p [dbname] < Backup.sql

Parameter description:

The dbname parameter represents the database name. This parameter is optional, you can specify a database name, or you may not specify it. Specifies that the table under the database is restored when the database name is specified.
When you do not specify a database name, you restore a specific database

Mysqlhotcopy

It copies files directly, so it's faster than mysqldump.

Data locking can be done automatically without shutting down the server during backup.

The ability to refresh the log so that checkpoints for backup files and log files remain synchronized.

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

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=root-p=root Testdb/tmp (copy of database directory TestDB to/tmp) /usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=root-p=root Testdb_1 testdb_2 testdb_n/tmp /usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=root-p=root testdb./regex//tmp

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 can be, while attention to the issue of permissions, the following example:

The code is as follows	Copy Code
CP-RF testdb/usr/local/mysql/data/ Chown-r nobody:nobody/usr/local/mysql/data/(Convert testdb directory owner to Mysqld run user)

Direct copy

Windows System: Direct replication is simple we stopped the MySQL database and then copied it.

Linux system:

① find the database file storage location, the MySQL file suffix has three kinds of forms *. MyD, *. Myi, *.frm;

The code is as follows	Copy Code
Find/-name *. MyD

② into the database storage file directory;

The code is as follows	Copy Code
cd/opt/lanmp/mysql-5.1.63/win/data/

③ Use the Copy command to copy all the data out;

The code is as follows	Copy Code
Copy Mysql/home

④ at this point just to convert these database files to SQL files, this is also the focus of this article I want to talk about, if necessary, then the SQL file with the command to import it;

⑤ Create a new database named Mysqlbak, copy the folder just copied into the new Database Data folder;

The code is as follows	Copy Code
Find/-name Mysqlbak Cd/home/mysql Copy */www/wdlinux/mysql-5.1.63/var/mysqlbak/

⑥ at this time already can see the new database Mysqlbak in the data, with the original database MySQL data is the same;

⑦ use the command to export the database file.

The code is as follows	Copy Code
Mysqldump-uroot-p Mysqlbak >/home/mysql.sql

⑧ the file Mysql.sql file under this home folder is an easy to import database file