1

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?

1
  • @ErgestBasha you should post your comment as an answer.
    – mustaccio
    Commented Mar 27, 2023 at 16:47

1 Answer 1

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!)
1
  • There is a basic issue which I don't grasp. I have checked the output of "mysqldump --all-databases" and it starts with exactly the same lines as the output of just "mysqldump mysql", which gave the error above. This means that if I produce a dump with "--all-databases", which is supposed to be a full backup, then if I try to restore, I will encounter that same problem. I have not really tried, but it seems logical to assume so. Thus backup/restore with mysqldump is now broken? Commented Mar 30, 2023 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.