如何使用所有用户、权限和密码备份整个 MySQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7848189/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to backup whole MySQL database with all users and permissions and passwords?
提问by Dakadaka
I need to backup the whole of a MySQL database with the information about all users and their permissions and passwords.
我需要使用有关所有用户及其权限和密码的信息备份整个 MySQL 数据库。
I see the options on http://www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/, but what should be the options to backup all of the MySQL database with all users and passwords and permissions and all database data?
我在http://www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/上看到了选项，但是使用所有用户和密码备份所有 MySQL 数据库的选项应该是什么和权限以及所有数据库数据？
Just a full backup of MySQL so I can import later on another machine.
只是一个 MySQL 的完整备份，所以我可以稍后在另一台机器上导入。
回答by desertwebdesigns
At it's most basic, the mysqldump command you can use is:
最基本的，您可以使用的 mysqldump 命令是：
mysqldump -u$user -p$pass -S $socket --all-databases > db_backup.sql
mysqldump -u$user -p$pass -S $socket --all-databases > db_backup.sql
That will include the mysql database, which will have all the users/privs tables.
这将包括 mysql 数据库，它将包含所有用户/privs 表。
There are drawbacks to running this on a production system as it can cause locking. If your tables are small enough, it may not have a significant impact. You will want to test it first.
在生产系统上运行它有一些缺点，因为它会导致锁定。如果您的表足够小，则可能不会产生重大影响。您将要先对其进行测试。
However, if you are running a pure InnoDB environment, you can use the --single-transactionflag which will create the dump in a single transaction (get it) thus preventing locking on the database. Note, there are corner cases where the initial FLUSH TABLEScommand run by the dump can lock the tables. If that is the case, kill the dump and restart it. I would also recommend that if you are using this for backup purposes, use the --master-dataflag as well to get the binary log coordinates from where the dump was taken. That way, if you need to restore, you can import the dump file and then use the mysqlbinlogcommand to replay the binary log files from the position where this dump was taken.
但是，如果您正在运行纯 InnoDB 环境，则可以使用--single-transaction将在单个事务中创建转储（获取它）的标志，从而防止锁定数据库。请注意，在某些极端情况下，FLUSH TABLES转储运行的初始命令可以锁定表。如果是这种情况，请终止转储并重新启动它。我还建议，如果您将其用于备份目的，也可以使用该--master-data标志来获取转储所在位置的二进制日志坐标。这样，如果您需要恢复，您可以导入转储文件，然后使用该mysqlbinlog命令从执行此转储的位置重放二进制日志文件。
回答by AleX
If you'd like to transfer also stored procedures and triggers it's may be worth to use
如果您还想传输存储过程和触发器，则可能值得使用
mysqldump --all-databases --routines --triggers
mysqldump --all-databases --routines --triggers
if you have master/slave replication you may dump their settings
如果您有主/从复制，您可以转储它们的设置
--dump-slaveand/or --master-data
--dump-slave和/或 --master-data
