Error restore dump MySQL8 - ERROR 3552 (HY000) at line 19044: Access to system schema 'mysql' is rejected

Question

We have a cron that does a full dump of the MySQL5 server, and in tests of restore on a empty instance, it restores all bases, including mysql with mysql.user carrying users and permissions together.

In MySQL8 because mysql base is system, the --add-drop-database and --all-databases attributes conflict giving an error in the restore "ERROR 3552 (HY000) at line 19044: Access to system schema 'mysql' is rejected.", as it is not allowed to drop the mysql base.

Has anyone managed to get around this situation and bring users and privileges together in MySQL8 in same dumpfile?

This is the command i run to dump:

mysqldump --add-drop-database --flush-logs --single-transaction --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats --quick  --all-databases --triggers --routines --events -u root --password='senha' -P 3306 -h 1.1.1.1 | bzip2 > /tmp/backup.sql.bz2

Answer 1

The problematic SQL:

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

The best way to walk around this, just open the dump SQL file, delete this SQL,

if the file is too big, use sed.

Answer 2

I ran into this same scenario. I dumped a broken instance with all databases and using add-drop-statement to try and save the data, but when I went to restore it I was blocked. You can no longer drop the mysql system database.

My database backup was something like 150gb, and opening it manually was not an option (a shame as i could tell by doing head -n 50 backup.sql that the problematic statement was within the first few lines).

the statement to remove was

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

and the sed command for me was:

sed -i 's/\/\*!40000 DROP DATABASE IF EXISTS `mysql`\*\/;/ /g' backup.sql

I would paste the statement into an empty text file first, and run the command to confirm that it actually works. This way you don't waste a ton of time on the execution of a very large backup file -- as there's a chance with your version of sed, or OS, that it might resolve the regular expression differently.