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 @@SESSION.SQL_LOG_BIN= 0;
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
- How can I export the privileges from MySQL and then import to a new server?
- Export all MySQL users
- How to Export & Import Existing User (with its Privileges!)