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
Best Answer
Most people that perform mysqldumps just use the
--all-databases
option. That will include themysql
database. There are two schools of thought as to whether one should include themysql
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.
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.
METHOD #2 : Emulate pt-show-grants
I made my own technique for pt-show-grants
I have discussed this before
Apr 09, 2012
: Mysql users deletedApr 12, 2012
: Cannot GRANT privileges as rootJun 13, 2012
: Fastest way to move a database from one server to anotherWhy include mysql ???
The only case for which you can mysqldump the
mysql
schema is to restore it to the same version ofmysql
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
proc.frm
,proc.MYD
, andproc.MYI
then reload it by copying it back into the mysql subsfolder and runninfFLUSH TABLES;
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.