如何用所有用户以及权限和密码备份整个MySQL数据库? [英] How to backup whole MySQL database with all users and permissions and passwords?

问题描述

我需要使用所有用户及其权限和密码的信息来备份整个MySQL数据库.

I need to backup the whole of a MySQL database with the information about all users and their permissions and passwords.

我在 http://上看到了这些选项www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/， 但是用所有用户，密码和权限以及所有数据库数据备份所有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?

只是MySQL的完整备份，所以我以后可以在另一台计算机上导入.

Just a full backup of MySQL so I can import later on another machine.

推荐答案

最基本的是，您可以使用的mysqldump命令是:

At it's most basic, the mysqldump command you can use is:

mysqldump -u$user -p$pass -S $socket --all-databases > db_backup.sql

这将包括mysql数据库，该数据库将具有所有的users/privs表.

That will include the mysql database, which will have all the users/privs tables.

在生产系统上运行此操作存在一些缺点，因为它可能导致锁定.如果您的表足够小，则可能不会产生重大影响.您将需要先对其进行测试.

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.

但是，如果您运行的是纯InnoDB环境，则可以使用--single-transaction标志，该标志将在单个事务中创建转储(获取转储)，从而防止锁定数据库.注意，在某些特殊情况下，转储运行的初始FLUSH TABLES命令可以锁定表.如果是这种情况，请杀死转储并重新启动它.我还建议，如果将其用于备份目的，也请使用--master-data标志从转储处获取二进制日志坐标.这样，如果需要还原，则可以导入转储文件，然后使用mysqlbinlog命令从进行此转储的位置重播二进制日志文件.

However, if you are running a pure InnoDB environment, you can use the --single-transaction flag 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 TABLES command 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-data flag 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 mysqlbinlog command to replay the binary log files from the position where this dump was taken.

这篇关于如何用所有用户以及权限和密码备份整个MySQL数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！