如何使用mysqldump来备份mysql数据库或数据表

Summary: in this tutorial you will learn how to use mysqldump tool back up MySQL databases.

MySQL GUI tools such as phpMyAdmin, SQLyog and etc often provide features for backup MySQL databases with ease. However if your database is big, the backup process could be very slow because the backup file need to be transferred across the network to your client PC. As the result, the backup process increases locking time therefore MySQL unavailability.

MySQL provides a very useful tool for backup or dump MySQL databases locally on server very fast. The backup file is stored in the file system in the server so you just need to download it when needed. The tool to backup MySQL databases is mysqldump. It is located in the root/bin folder of MySQL installation folder. The mysqldump is a program provided by MySQL that can be used to dump databases for backup or transfer database to another database server. The dump file contains a set of SQL statements to create and populate tables. In addition, the mysqldump can be used to generate CSV, delimited or XML files. In this tutorial, we will focus only on how to backup MySQL database by using mysqldump tool.

How to Backup a MySQL Database

To backup a MySQL database, the database first has to exist in the database server and you have access to that server as well. You can use SSH or Telnet to login to the remote server if you do not have remote desktop to it. The command to backup a MySQL database as follows:

mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

The parameter of the command above as follows:

• [username]: valid MySQL username.

• [password]: valid password for the user. Note that there is no space between –p and the password.

• [database_name]: database name you want to backup

• [dump_file.sql]: dump file you want to generate.

By executing the above command all database structure and data will be exported into a single [dump_file.sql] dump file. For example, in order to back our sample database classicmodels, we use the following command:

mysqldump -u OutOfMemory –psecret  classicmodels > c:\temp\backup001.sql

How to Backup MySQL Database Structure Only

If you only want to backup database structure only you just need to add an option –no-data to tell mysqldump that only database structure need to export as follows:

mysqldump -u [username] –p[password] –no-data [database_name] > [dump_file.sql]

For example to backup our sample database with structure only, you use the following command:

mysqldump -u OutOfMemory –psecret  -no-data classicmodels > c:\temp\backup002.sql

How to Backup MySQL Database Data Only

There is a case that you want to refresh data in staging and development system so the data in those systems are the same as production system. In this case you just need to export data only from production system and import it to staging and development system. In order to backup data only, you use option –no-create-info of mysqldump as follows:

mysqldump -u [username] –p[password] –no-create-info [database_name] > [dump_file.sql]

For example to backup our sample database with data only, you use the following command:

mysqldump –u OutOfMemory –psecret –no-create-info classicmodels > c:\temp\backup002.sql

How to Backup Multiple MySQL Databases into a Single File

If you want to backup multiple database just separate database name by command in the [database_name]. If you want to back up all databases in the database server use the option –all-database.

mysqldump -u [username] –p[password] [dbname1,dbname2,…] > [dump_file.sql] mysqldump -u [username] –p[password] –all-database > [dump_file.sql]

原文链接：http://outofmemory.cn/mysql/administration/how-to-backup-database-using-mysqldump