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