Backup and restore “mysql” database

Question

I use the following command to backup some normal databases:

mysqldump --host=host --user=user --password=passwd --result-file=result.sql -R --single-transaction database_name

My question is : do I need to also dump the "mysql" database ? Note that I will create new users for each database after "restoring" them. So I don't need the "mysql" database for user information.

Thanks

Answer 1

Most people that perform mysqldumps just use the --all-databases option. That will include the mysql database. There are two schools of thought as to whether one should include the mysql database.

Why not to include mysql ???

When you mysqldump the mysql schema, you should make yourself aware of differences in MySQL versions, particularly the mysql.user table.

• In MySQL 5.0, mysql.user has 37 columns
• In MySQL 5.1, mysql.user has 39 columns
• In MySQL 5.5, mysql.user has 42 columns

Restoring a mysqldump from one version can result in certain privileges disappearing when restored :

Make sure you handle dumping user grants as a special script. There are two methods for this:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

I have discussed this before

• Apr 09, 2012 : Mysql users deleted
• Apr 12, 2012 : Cannot GRANT privileges as root
• Jun 13, 2012 : Fastest way to move a database from one server to another

Why include mysql ???

The only case for which you can mysqldump the mysql schema is to restore it to the same version of mysql

Answer 2

If you are creating new users and the like on the new system, then their is no need to backup the MySQL database.

Wish I had some documentation to show you, but it's quite difficult to Google MySQL database, as all the results are purely to do with the MySQL system, and not the database created in itself.

Answer 3

No need for MYSQL or any table other than your database.

Because, mysql db contains information about users, help, host, servers, time-zone, etc.. which is totally not needed as you are gonna make new users.

FYI : there are more two tables, that you might not wanting, and which you should not back up. performance_schema and information_schema

Answer 4

You could skip the mysql database on your backup process if you don't care about user accounts but on target instance you should have a mysql db to start MySQL Server...

Max