Setup replication using mysqldump. Restore getting "Access to system schema 'mysql' is rejected."

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi,
I was trying to sync a standby database using mysqldump.
Dumped using “mysqldump --all-databases --master-data”

Running the restore I hit “ERROR 3552 (HY000) at line 48031: Access to system schema ‘mysql’ is rejected.”

Can i simply remove the “/!40000 DROP DATABASE IF EXISTS mysql/;” ?

Concerned that replication might not work if I remove the drop.

thanks

2

Yes , this removing this line , can be workaround

3

Hi @ipcmlr ,

Even though this question is solved by commenting a “drop if exists” but have this consideration.

  • You were trying to trying to sync a standby database using mysqldump from source.
  • So you executed mysqldump --all-databases --master-data.
  • Then you use the sql file thus generated on replica giving you error Access to system schema ‘mysql’ is rejected. ERROR 3552 (HY000) is a specific error code in MySQL that typically indicates an access restriction or permission issue related to system schemas.
  • This hints that the SQL being executed are using user with not-enough-privileges?! Are you using user with limited privileges?
    Thanks,
    K
4

Hello @ipcmlr

Please check the mysqldump option add-drop-database

mysqldump --help | grep add-drop-database
  --add-drop-database Add a DROP DATABASE before each create.
add-drop-database                           FALSE

If the option is enabled, mysqldump will add query:

    snprintf(qbuf, sizeof(qbuf), "SHOW CREATE DATABASE IF NOT EXISTS %s",
             qdatabase);

    if (mysql_query(mysql, qbuf) || !(dbinfo = mysql_store_result(mysql))) {
      /* Old server version, dump generic CREATE DATABASE */
      if (opt_drop_database)
        fprintf(md_result_file, "\n/*!40000 DROP DATABASE IF EXISTS %s*/;\n",
                qdatabase);

The issue can be reproduced if the option is enabled.

shell> mysqldump --all-databases  --master-data --add-drop-database=1 > all.sql
shell> mysql < all.sql
ERROR 3552 (HY000) at line 39: Access to system schema 'mysql' is rejected.
1 Like