MySQL Database backup command

Dump command Backup

1. Back up a database:

Mysqldump-uroot (Root is User name)-P-Database (library name) table (table name) >d:\backup.sql (where to back up, file name)

2. Back up multiple databases: (plus the--databases option, followed by multiple databases)

Mysqldump-uroot-p--databases dbname1 dbname2 >d:\backup.sql

3. Back up all databases:

The syntax of the mysqldump command to back up all databases is as follows:

Mysqldump-uroot-p-all-databases >d:\backup.sql

directly replicate the entire database directory
MySQL has a very simple backup method, is the MySQL database files directly copied out. This is the simplest and fastest way.
before you do this, however, you should stop the server before you can guarantee that the data for the database will not change during replication. Data inconsistencies can occur if there are data writes in the process of replicating the database.
This is possible in the development environment, but it is difficult to allow backup servers in a production environment.
Note: This method does not apply to INNODB storage engine tables, but is convenient for MyISAM storage engine tables. At the same time, the version of MySQL is best when restoring.

You can also use the Mysqlhotcopy tool to quickly back up

A hot backup is known as a name. Therefore, mysqlhotcopy support does not stop MySQL server backups. Also, mysqlhotcopy backup is faster than mysqldump. Mysqlhotcopy is a Perl script that is used primarily on Linux systems. It uses lock tables, FLUSH tables, and CP for quick backups.

Rationale: First the database to be backed up with a read lock, and then use flush tables to write the data in memory back to the database on the hard drive, and finally, copy the database files that need to be backed up to the target directory

mysqlhotcopy [option] dbname1 dbname2 backupdir/

dbname: Database name;

Backupdir: under which folder to back up;

Common options:

--help: View Mysqlhotcopy help;

--allowold: If the backup directory has the same backup file, the old backup file plus _old;

--keepold: If the backup directory has the same backup files, do not delete the old backup files, but the old files renamed;

--flushlog: After this seniority, updates to the database are recorded in the log;

--noindices: Only backup data files, do not back up index files;

--user= Username: Used to specify user name, can be replaced with-u;

--password= Password: Used to specify the password, can be replaced with-P. When using-p, there is no space between the password and-p;

--port= port number: Used to specify the access port, can be replaced with-p;

--socket=socket file: Used to specify the socket file, can be replaced with-s;

Mysqlhotcopy is not a mysql-led, need to install Perl database interface package; Download Address: http://dev.mysql.com/downloads/dbi.html

Database restore

The syntax for restoring a database backed up using the mysqldump command is as follows:

Mysql-u root-p [dbname] < BACKUP.SQ

url:http://blog.csdn.net/lovely_1014/article/details/53938994