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:
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
SHOW TABLES FROM mysql;
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.
You apparently have a missing drupal_install_test
in your database schema. As the name says, it's a test table that is created at installation time to test the performance of the database and of the database user you provided for your site. At the end of the installation process, it gets dropped from the database.
The problem here is that the table has been dropped but mysql still thinks it exists. The reason why that table has remained in information_schema.tables
should definitely be investigated and may reflect some issues in the database or in your server's configuration.
That said, if drupal_install_test
is the only corrupted table in your database, you can try and drop it and proceed with your backup. I recommend that you make a full backup of the mysql data directory (in a typical linux installation /var/lib/mysql
) and issue a DROP TABLE drupal_install_test
command. Mysql should be able to restore the integrity of the schema tables and you should be able to proceed with your backup.
Best Answer
Can you try grant permission on whole database like,