MySQL restore of "mysql" database fails with error 3723

Question

I have a backup process that once a day dumps all the mysql databases on one server (with mysqldump), rsyncs them to another, and then they are imported there (one at a time). I did it this way to follow the traditional rule of never touching the database files, always dump and restore with the provided tools.

Thus all my databases are copied this way, including the "mysql" database (because I need to keep in sync also users and permissions).

In the past, I had no problems. Now after upgrading to MySql 8.0 (via Ubuntu 20), the restore of the "mysql" database fails with this error:

ERROR 3723 (HY000) at line 25: The table 'columns_priv' may not be created in the reserved tablespace 'mysql'.

Only the "mysql" database has this problem, not the others. For the record, the sql dump file is rather small (less than 2 MB). The dump is freshly created, and restored on an identical MySQL server (same version: Mysql 8.0.32-0ubuntu0.20.04.2).

So far I have found no solution. Any idea?

Answer 1

In the past, I had no problems. Now after upgrading to MySql 8.0 (via Ubuntu 20), the restore of the "mysql" database fails with this error:

ERROR 3723 (HY000) at line 25: The table 'columns_priv' may not be created in the reserved tablespace 'mysql'

From Chapter 2 Server Error Message Reference

Error number: 3723; Symbol: ER_RESERVED_TABLESPACE_NAME; SQLSTATE: HY000

Message: The table '%s' may not be created in the reserved tablespace '%s'.

ER_RESERVED_TABLESPACE_NAME was added in 8.0.11.

---

including the "mysql" database (because I need to keep in sync also users and permissions)

You shouldn't backup MySQL schema. The MySQL schema is the system schema. It contains tables that store information required by the MySQL server as it runs. A broad categorization is that the mysql schema contains data dictionary tables that store database object metadata, and system tables used for other operational purposes

One of the easiest ways of exporting users with privileges/grants is using mysqlpump

Tested on Windows .

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlpump -uroot -p --exclude-databases=% --users > C:\Users\erges\OneDrive\Desktop\users\user_grants.sql
Enter password: *********
Dump completed in 112

C:\Program Files\MySQL\MySQL Server 8.0\bin>

The user_grants.sql will contain something like

- Dump created by MySQL pump utility, version: 8.0.29, Win64 (x86_64)
-- Dump start time: Tue Mar 28 18:52:45 2023
-- Server version: 8.0.29

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE USER `wp_gmvgw`@`%` IDENTIFIED WITH 'mysql_native_password' AS '*936C28CE589C8237B2839F7BEBAD2441ECF3F2ED' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT SELECT ON *.* TO `wp_gmvgw`@`%`;

There are other ways which can be found on the following answers

1. How can I export the privileges from MySQL and then import to a new server?
2. Export all MySQL users
3. How to Export & Import Existing User (with its Privileges!)