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
  • 19,757
  • 8
  • 52
  • 79
HelloSam
  • 171
  • 1
  • 6

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,479
  • 1
  • 25
  • 22
  • I want to restore mysql table though - specifically the users and roles. I was trying to initialize the a slave server. – HelloSam Sep 05 '18 at 07:24
  • @HelloSam is it viable to script TWO dumps, one with `add-drop-database` and one without, then restore them one after the another? – Daniele Santi Sep 05 '18 at 07:27
  • that's kinda works - except I cannot use --all-database and have to find out what database in my master besides the `mysql` and list them out in the one with --add-drop's. – HelloSam Sep 05 '18 at 07:56