如何使用 mysqldump 命令行实用程序对 mysql 数据库进行完整备份

How to take complete backup of mysql database using mysqldump command line utility

提问by MySQL DBA

How can I make a complete backup of mysql database using mysqldump? When I am making a backup, my tables from specified database are only getting backed up. The procedures and functions are not.

如何使用 mysqldump 对 mysql 数据库进行完整备份？当我进行备份时，来自指定数据库的表只会被备份。程序和功能不是。

Here's the backup command I am using :
(Operating system is Windows Vista.)

这是我正在使用的备份命令：（
操作系统是 Windows Vista。）

mysqldump -u username -p db1 > backup.sql

采纳答案by Knut Haugen

It depends a bit on your version. Before 5.0.13 this is not possible with mysqldump.

这有点取决于您的版本。在 5.0.13 之前，mysqldump 无法做到这一点。

From the mysqldump man page (v 5.1.30)

从 mysqldump 手册页 (v 5.1.30)

 --routines, -R

      Dump stored routines (functions and procedures) from the dumped
      databases. Use of this option requires the SELECT privilege for the
      mysql.proc table. The output generated by using --routines contains
      CREATE PROCEDURE and CREATE FUNCTION statements to re-create the
      routines. However, these statements do not include attributes such
      as the routine creation and modification timestamps. This means that
      when the routines are reloaded, they will be created with the
      timestamps equal to the reload time.
      ...

      This option was added in MySQL 5.0.13. Before that, stored routines
      are not dumped. Routine DEFINER values are not dumped until MySQL
      5.0.20. This means that before 5.0.20, when routines are reloaded,
      they will be created with the definer set to the reloading user. If
      you require routines to be re-created with their original definer,
      dump and load the contents of the mysql.proc table directly as
      described earlier.

回答by NarasimhaTejaJ

If you want to take a full backup i.e., all databases, procedures, routines, and events without interrupting any connections:

如果您想在不中断任何连接的情况下进行完整备份，即所有数据库、过程、例程和事件：

mysqldump -u [username] -p -A -R -E --triggers --single-transaction > full_backup.sql

1. -AFor all databases (you can also use --all-databases)
2. -RFor all routines (stored procedures & triggers)
3. -EFor all events
4. --single-transactionWithout locking the tables i.e., without interrupting any connection (R/W).

1. -A对于所有数据库（您也可以使用--all-databases）
2. -R对于所有例程（存储过程和触发器）
3. -E对于所有事件
4. --single-transaction不锁定表，即不中断任何连接（读/写）。

If you want to take a backup of only specified database(s):

如果您只想备份指定的数据库：

mysqldump -u [username] -p [database_name] [other_database_name] -R -e --triggers --single-transaction > database_backup.sql

If you want to take a backup of only a specific table in a database:

如果您只想备份数据库中的特定表：

mysqldump -u [username] -p [database_name] [table_name] > table_backup.sql

If you want to take a backup of the database structure only just add --no-datato the previous commands:

如果您只想备份数据库结构，只需添加--no-data到前面的命令：

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

mysqldumphas many more options, which are all documented in the mysqldumpdocumentationor by running man mysqldumpat the command line.

mysqldump有更多选项，这些选项都记录在mysqldump文档中或通过man mysqldump在命令行运行。

回答by ajay singh mittal

Use these commands :-

使用这些命令：-

mysqldump <other mysqldump options> --routines > outputfile.sql

If we want to backup ONLY the stored procedures and triggers and not the mysql tables and data then we should run something like:

如果我们只想备份存储过程和触发器而不是 mysql 表和数据，那么我们应该运行如下：

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

If you need to import them to another db/server you will have to run something like:

如果您需要将它们导入到另一个数据库/服务器，您将必须运行如下命令：

mysql <database> < outputfile.sql

回答by jonfm

In addition to the --routines flag you will need to grant the backup user permissions to read the stored procedures:

除了 --routines 标志外，您还需要授予备份用户读取存储过程的权限：

GRANT SELECT ON `mysql`.`proc` TO <backup user>@<backup host>;

My minimal set of GRANT privileges for the backup user are:

我为备份用户设置的最小 GRANT 权限是：

GRANT USAGE ON *.* TO ...
GRANT SELECT, LOCK TABLES ON <target_db>.* TO ...
GRANT SELECT ON `mysql`.`proc` TO ...

回答by Vitaly Kushner

Use '-R' to backup stored procedures, but also keep in mind that if you want a consistent dump of your database while its being modified you need to use --single-transaction(if you only backup innodb) or --lock-all-tables(if you also need myisam tables)

使用“-R”来备份存储过程，但请记住，如果您希望在修改数据库时对数据库进行一致的转储，则需要使用--single-transaction（如果您只备份 innodb）或--lock-all-tables（如果您还需要 myisam 表）

回答by sunil

I am using MySQL 5.5.40. This version has the option --all-databases

我正在使用 MySQL 5.5.40。这个版本有选项--all-databases

mysqldump -u<username> -p<password> --all-databases --events > /tmp/all_databases__`date +%d_%b_%Y_%H_%M_%S`.sql

This command will create a complete backup of all databases in MySQL server to file named to current date-time.

此命令将创建 MySQL 服务器中所有数据库的完整备份到命名为当前日期时间的文件。

回答by M. Hamza Rajput

On MySQL 5.7 its work for me, I'm using CentOS7.

在 MySQL 5.7 上它对我有用，我使用的是 CentOS7。

For taking Dump.

对于采取转储。

Command :

命令 ：

mysqldump -u user_name -p database_name -R -E > file_name.sql

Exemple :

例子：

mysqldump -u root -p mr_sbc_clean -R -E > mr_sbc_clean_dump.sql

For deploying Dump.

用于部署 Dump。

Command :

命令 ：

mysql -u user_name -p database_name < file_name.sql

Exemple :

例子：

mysql -u root -p mr_sbc_clean_new < mr_sbc_clean_dump.sql

回答by Rahul Maurya

To create dump follow below steps:

要创建转储，请按照以下步骤操作：

1. Open CMD and go to bin folder where you have installed your MySQL
   ex:C:\Program Files\MySQL\MySQL Server 8.0\bin. If you see in this
   folder mysqldump.exe will be there. Or you have setup above folder in your Path variable of Environment Variable.

2. Now if you hit mysqldump in CMD you can see CMD is able to identify dump command.

3. Now run "mysqldump -h [host] -P [port] -u [username] -p --skip-triggers --no-create-info --single-transaction --quick --lock-tables=false ABC_databse > c:\xyz.sql"
4. Above command will prompt for password then it will start processing.

1. 打开 CMD 并转到安装 MySQL 的 bin 文件夹，
   例如：C:\Program Files\MySQL\MySQL Server 8.0\bin。如果您在此
   文件夹中看到mysqldump.exe 将在那里。或者您在环境变量的 Path 变量中设置了上述文件夹。

2. 现在，如果您在 CMD 中点击 mysqldump，您可以看到 CMD 能够识别转储命令。

3. 现在运行“ mysqldump -h [host] -P [port] -u [username] -p --skip-triggers --no-create-info --single-transaction --quick --lock-tables=false ABC_databse > c:\xyz.sql"
4. 上面的命令将提示输入密码，然后它将开始处理。