Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.


share|improve this question

migrated from Dec 24 '12 at 5:15

This question came from our site for professional and enthusiast programmers.

4 Answers 4

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.

share|improve this answer

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

share|improve this answer
get it. Thanks very much – Eagle Dec 24 '12 at 8:26

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


share|improve this answer

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

Why include mysql ???

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

UPDATE 2014-12-30 17:42 EST

My original answer covered just the MySQL Grants since this particular aspect of the mysql schema is the easiest to break and overlook.

There are other reasons not to just backup the mysql schema unless it is necessary

  • If you want to retain the timestamps for all Stored Procedures, you can mysqldump the mysql.proc table by itself and load it. You can also just copy the proc.frm, proc.MYD, and proc.MYI then reload it by copying it back into the mysql subsfolder and runninf FLUSH TABLES;
  • If you use table based logging for the general log (mysql.general_log) and slow log (mysql.slow_log), you do not want to backup these logs and move them to another server where the logs do not semantically apply. It is your choice if you want to restore them.
  • If you set any timezone info in the mysql, be very careful if you do not want to restore the mysql schema in a data center that is in another timezone.

If you want to be careful about what you want to backup and restore in the mysql schema, you should mysqldump the mysql schema in a separate file.

The number of tables in the mysql schema, as well as the number of columns, will vary from version to version. So, you should login to mysql and run


The list of tables may not include events (like MySQL 5.0), may not have InnoDB system tables (like MySQL 5.6 has), may not include tablespace grants (like MySQL 5.6), and so forth. Sp, if you feel you must backup the mysql schema, you be very conservative (ok very picky and careful) about what tables you truly need to backup from the mysql schema.

share|improve this answer
Hi Rolando. I'm not familiar with what info is in the mysql database--do method #1 and method #2 include all needed information from that db? Or are there other things that might be getting left behind when using those commands? – Mark Doliner Dec 30 '14 at 21:01
@MarkDoliner Methods 1 and 2 are for porting user grants as version-neutral SQL GRANT commands. The mysql schema also contains Stored Procedures. I have my old post… to show how to dump Stored Procedures in a version-neutral way. The same principles and methodology can used to dump MySQL events (See my post…) – RolandoMySQLDBA Dec 30 '14 at 21:46
Thanks for the response. Since the poster's original question asks whether it's necessary to back up the mysql database (not just user information), would it make sense to edit your answer to mention Stored Procedures and events? – Mark Doliner Dec 30 '14 at 22:14
@MarkDoliner my answer addresses the one thing that blindly backing up and restoring the mysql database often breaks and goes unnoticed until certain grants disappear. Including the options --routines, --triggers, --events is more than enough for mysqldump. It's the MySQL Grants that prove to be the lone wolf that mysqldump does not cover and still doesn't cover today. It would be nice if my emulation code was added to mysqldump. Then, all bases would be covered. Notwithstanding, I just thought of other reasons. So, I will update my answer. – RolandoMySQLDBA Dec 30 '14 at 22:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.