How to backup whole MySQL database with all users and permissions and passwords?

Question

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

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?

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

Answer 1

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

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.

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-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.