1

Problem statement

mysqldump --add-drop-database --all-database creates a dump that contains

....
/*!40000 DROP DATABASE IF EXISTS `mysql`*/;
....

This is expected and I believe is a correct behavior.

Yet, if cat'ing this dump into a mysql server, i.e. cat dump | mysql -u root -pxxxx (The account has SUPER), it complains ERROR 3552 (HY000) at line 30: Access to system schema 'mysql' is rejected.

Question

Is there any way to make it works, without manually editing the dump. Or sed/grep to remove the offending line is my only choice?

This used to be working in MySQL 5.6 (i.e. it accept dropping the mysql database)

The root account has the follow privileges SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, BACKUP_ADMIN, RESOURCE_GROUP_ADMIN, XA_RECOVER_ADMIN

Server and Client: MySQL 8.0.12

Gerald Schneider
  • 26,582
  • 8
  • 65
  • 97
HelloSam
  • 171
  • 1
  • 7

1 Answers1

0

I think there's no need to dump/restore the mysql database schema.

If you want an automated method which skips the mysql database (or any other database you don't want to dump) without specifying every single database, check the answer to this question for a simple script to autmatically get all databases except those you want to ignore.

Daniele Santi
  • 2,579