# What would be the best way to migrate MariaDB 10.8 to Percona Server?

**URL:** <https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972>\
**Category:** Percona Server for MySQL 8.0\
**Tags:** mysql, percona\
**Created:** [May 10, 2023, 3:37am UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972 "2023-05-10T03:37:57Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![nunop](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@nunop](https://forums.percona.com/u/nunop)\
**Post date:** [May 10, 2023, 3:37am UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/1 "2023-05-10T03:37:58Z")

</div>

Hey!  
I’m currently on MariaDB 10.8, and even though I like it very much, I’ve been getting disappointed with the lack of crucial features missing there.

For example, LATERAL not being implemented, while it’s been available in MySQL 8 since very long ago. There are also a lot of very nice tools that Percona provides that I cannot guarantee they will work with MariaDB, so I avoid using them.

And there have been a few releases that could corrupt the database, and I was very affected by the first few releases of MariaDB 10.8, which could hurt very much my business.

Therefore, I’ve been seriously considering migrating to Percona Server, but I’m quite unsure what’s the best way to do it, ensuring that everything will go smoothly.

I don’t use very specific features of MariaDB, and no replication.  
Just “basic” SQL, users & permissions (I know MariaDB has made changes in the way users/permissions work), column-specific permissions, indexed VIRTUAL stored columns. One or two CTEs. All FKs should be “RESTRICT / NO ACTION”, but there could be one or two that CASCADE.  
All InnoDB, Barracuda, file per table.

**What would be the best way to migrate to Percona Server?**

It’s a quite big database, so mysqldump would likely take a very long time.  
I doubt xtrabackup/mariabackup would work.

**Would replication be possible?**  
If so, what would be the steps to setup replication in a compatible way, and then do the switch?

And if I need to rollback, is it possible/easy to do the other way around to go back to MariaDB in case of an emergency, without losing the changes since the first migration?

–

I understand there will be some queries that need to be re-optimized, and some my.cnf changes too, which is ok, as I should be able to test on a Test server first.

But I’m a big newbie with replication, as it’s something I’ve never done before. 🙂

–

Here are a few my.cnf configs that I’d like to point out, in case it could matter:

```auto
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY"
#old_mode = "" # default: "UTF8_IS_UTF8MB3"

innodb_stats_on_metadata = 0

innodb_buffer_pool_size = 95G
innodb_log_file_size = 24G

innodb_page_size = 16384
innodb_doublewrite = 1
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_io_capacity = 1000
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 15

optimizer_use_condition_selectivity = 1

```

Thank you very much!!!

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [May 11, 2023, 1:21am UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/2 "2023-05-11T01:21:08Z")

</div>

> [@nunop](#):
>
> I doubt xtrabackup/mariabackup would work.

Xtrabackup should work just fine.

> [@nunop](#):
>
> If so, what would be the steps to setup replication in a compatible way, and then do the switch?

Use Xtrabackup to take a backup of your MariaDB and create a replica with Percona MySQL 8. MariaDB uses an incompatible GTID sequence generation, so you will need to use traditional binlog filename/position when configuring replication. There are countless guides on the internet for how to set up a MySQL replica.

Rollback is hard because once your write data to MySQL, your MariaDB is now out of sync. Your best bet is to configure source/source replication where Percona MySQL is a replica of MariaDB and simultaneously MariaDB is a replica of Percona MySQL. Yes, this can be done and it is quite typical. Again, plenty of guides on our website and google for this.

You would need to remove `optimizer_use_condition_selectivity` from my.cnf as that is a MariaDB-only variable. The others are all fine as-is.

---

<div class="post-metadata">

**Author:** ![nunop](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@nunop](https://forums.percona.com/u/nunop)\
**Post date:** [July 23, 2023, 1:56pm UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/3 "2023-07-23T13:56:23Z")

</div>

Hey @matthewb - thank you so much for your reply.

Just back to this again, as I was very busy with other things in the past months.

> Xtrabackup should work just fine.

I appreciate this advice.  
But I’m having trouble getting this to work.

with “xtrabackup 8.0”:

> 2023-07-23T13:36:35.779551-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --parallel=2 --target-dir=/backup/ --rsync=1 --host=mysqlsnap --user=root --password=\* --no-server-version-check=1  
> xtrabackup version 8.0.32-25 based on MySQL server 8.0.32 Linux (x86\_64) (revision id: 14f007fb)  
> 2023-07-23T13:36:35.781031-00:00 0 [Note] [MY-011825] [Xtrabackup] perl binary not found. Skipping the version check  
> 2023-07-23T13:36:35.781049-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: mysqlsnap, user: root, password: set, port: not set, socket: not set  
> 2023-07-23T13:36:35.788370-00:00 0 [ERROR] [MY-011825] [Xtrabackup] failed to execute query ‘SELECT \* FROM performance\_schema.replication\_group\_members’ : 1146 (42S02) Table ‘performance\_schema.replication\_group\_members’ doesn’t exist  
> 2023-07-23T13:36:35.790724-00:00 0 [ERROR] [MY-011825] [Xtrabackup] Unsupported server version: ‘10.8.8-MariaDB-1:10.8.8+maria~ubu2204’  
> 2023-07-23T13:36:35.790743-00:00 0 [ERROR] [MY-011825] [Xtrabackup] This version of Percona XtraBackup can only perform backups and restores against MySQL 8.0 and Percona Server 8.0  
> 2023-07-23T13:36:35.790748-00:00 0 [Note] [MY-011825] [Xtrabackup] Please use Percona XtraBackup 2.4 for this database.

with “xtrabackup 2.4”:

> xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/var/mysqltmp --server-id=1 --open\_files\_limit=10000 --innodb\_file\_per\_table=1 --innodb\_open\_files=6000 --innodb\_read\_io\_threads=12 --innodb\_write\_io\_threads=2 --innodb\_buffer\_pool\_size=20G --innodb\_log\_file\_size=512M --innodb\_page\_size=16384 --innodb\_doublewrite=1 --innodb\_flush\_method=O\_DIRECT --innodb\_io\_capacity=200 --innodb\_flush\_log\_at\_trx\_commit=2 --parallel=2  
> xtrabackup: recognized client arguments: --backup=1 --target-dir=/backup/ --rsync=1 --host=mysqlsnap --user=root --password=\*  
> xtrabackup: perl binary not found. Skipping the version check  
> 230723 13:53:12 Connecting to MySQL server host: mysqlsnap, user: root, password: set, port: not set, socket: not set  
> Using server version 10.8.8-MariaDB-1:10.8.8+maria~ubu2204  
> xtrabackup version 2.4.28 based on MySQL server 5.7.40 Linux (x86\_64) (revision id: 44a8f7b)  
> xtrabackup: uses posix\_fadvise().  
> xtrabackup: cd to /var/lib/mysql  
> xtrabackup: open files limit requested 10000, set to 1048576  
> xtrabackup: using the following InnoDB configuration:  
> xtrabackup: innodb\_data\_home\_dir = .  
> xtrabackup: innodb\_data\_file\_path = ibdata1:12M:autoextend  
> xtrabackup: innodb\_log\_group\_home\_dir = ./  
> xtrabackup: innodb\_log\_files\_in\_group = 2  
> xtrabackup: innodb\_log\_file\_size = 536870912  
> xtrabackup: using O\_DIRECT  
> InnoDB: Number of pools: 1  
> Warning: “sys.sys\_config” uses engine “Aria” and will not be backed up.  
> InnoDB: Operating system error number 2 in a file operation.  
> InnoDB: The error means the system cannot find the path specified.  
> InnoDB: File ./ib\_logfile1: ‘open’ returned OS error 71. Cannot continue operation  
> InnoDB: Cannot continue operation.

I’m running these on Docker:

```auto
services:
  mysqlsnap:
    container_name: mysqlsnap
    image: mariadb:10.8
    env_file:
      - ./db.env
    volumes:
      - /var/lib/mysql-docker:/var/lib/mysql:rw
      - mysqltmp:/var/mysqltmp
      - ./conf:/etc/mysql/conf.d:ro
      - /opt/certs/:/etc/my.cnf.d/certs/:ro
    networks:
      - mysqlsnap
  mysqlsnap-backup:
    container_name: mysqlsnap-backup
    image: percona/percona-xtrabackup:2.4
    restart: no
    profiles: ["backup-only"]
    user: root
    volumes:
      - /var/lib/mysql-docker:/var/lib/mysql:rw
      - /var/lib/mysql-docker-xtra/:/backup
      - mysqltmp:/var/mysqltmp
      - ./conf/my.cnf:/etc/my.cnf:ro
      - /opt/certs/:/etc/my.cnf.d/certs/:ro
    cap_add:
      # mbind: Operation not permitted
      - SYS_NICE
    networks:
      - mysqlsnap

```

The command:

`docker-compose run --rm mysqlsnap-backup xtrabackup --defaults-file=/etc/my.cnf --backup --parallel=2 --target-dir=/backup/ --rsync --host=mysqlsnap --user=root --password= *********** --no-server-version-check`

Any ideas?

Thank you so much!

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [July 23, 2023, 9:39pm UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/4 "2023-07-23T21:39:28Z")

</div>

Hello @nunop,  
Sorry for my previous assumption. You’re attempting to use MySQL-based tools against a MariaDB database. That’s not going to work due to the way MariaDB has changed themselves away from MySQL.

You’re going to have to use [mydumper/myloader](https://github.com/mydumper/mydumper) to perform a logical dump and restore to migrate from MariaDB to Percona Server for MySQL. mysqldump is too slow and single threaded. Use mydumper for parallel logical dumps. It supports MariaDB just fine.

---

<div class="post-metadata">

**Author:** ![nunop](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@nunop](https://forums.percona.com/u/nunop)\
**Post date:** [July 24, 2023, 2:07am UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/5 "2023-07-24T02:07:05Z")

</div>

Thank you @matthewb

I’m testing that now.

`mydumper` worked fine, and took 7 minutes to complete, with 8 threads (on a Snapshot that is restored every day using `mariabackup`, which has no traffic)

However, when I try to run `myloader` against a new `percona:8.0` container, I seem to be getting loads of critical errors due to syntax.

`docker run --rm -v /var/lib/mysql-docker-xtra/:/backup --network mysqlsnap mydumper/mydumper myloader --threads=8 --verbose 3 --overwrite-tables --user=root --password= ****** --host=mysqlsnap-percona --port=3306 --directory=/backup`

> \*\* Message: 01:55:34.145: Server version reported as: 8.0.33-25  
> \*\* Message: 01:55:34.146: Initializing initialize\_worker\_schema  
> \*\* Message: 01:55:34.288: S-Thread 1: Starting import  
> \*\* Message: 01:55:34.289: S-Thread 3: Starting import  
> \*\* Message: 01:55:34.293: S-Thread 2: Starting import  
> \*\* Message: 01:55:34.320: S-Thread 4: Starting import  
> \*\* Message: 01:55:34.321: Intermediate queue: Sending END job  
> \*\* Message: 01:55:34.329: Thread 1: restoring create database on `mysql` from mysql-schema-create.sql. Tables 0 of 60 completed  
> \*\* Message: 01:55:34.329: Filename to open: //backup/mysql-schema-create.sql  
> \*\* Message: 01:55:34.333: Thread 1: restoring table `mysql`.`roles_mapping` from //backup/mysql.roles\_mapping-schema.sql  
> \*\* Message: 01:55:34.333: Dropping table or view (if exists) `mysql`.`roles_mapping`  
> \*\* Message: 01:55:34.334: Thread 2: restoring table `mysql`.`time_zone_transition_type` from //backup/mysql.time\_zone\_transition\_type-schema.sql  
> \*\* Message: 01:55:34.334: Dropping table or view (if exists) `mysql`.`time_zone_transition_type`  
> \*\* Message: 01:55:34.335: Thread 1: Creating table `mysql`.`roles_mapping` from content in //backup/mysql.roles\_mapping-schema.sql.
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.335: Thread 1: Error restoring 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Granted roles’’ at line 7
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.336: Thread 1: Retrying last failed executed statement
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.336: Thread 1: Error restoring: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Granted roles’’ at line 7
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.336: Thread 1: issue restoring //backup/mysql.roles\_mapping-schema.sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Granted roles’’ at line 7  
> \*\* Message: 01:55:34.337: Thread 2: Creating table `mysql`.`time_zone_transition_type` from content in //backup/mysql.time\_zone\_transition\_type-schema.sql.
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.338: Thread 2: Error restoring 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Time zone transition types’’ at line 8  
> \*\* Message: 01:55:34.338: Thread 4: restoring table `mysql`.`column_stats` from //backup/mysql.column\_stats-schema.sql  
> \*\* Message: 01:55:34.338: Dropping table or view (if exists) `mysql`.`column_stats`
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.339: Thread 2: Retrying last failed executed statement
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.339: Thread 2: Error restoring: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Time zone transition types’’ at line 8
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.339: Thread 2: issue restoring //backup/mysql.time\_zone\_transition\_type-schema.sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Time zone transition types’’ at line 8  
> \*\* Message: 01:55:34.340: Thread 4: Creating table `mysql`.`column_stats` from content in //backup/mysql.column\_stats-schema.sql.  
> \*\* Message: 01:55:34.340: Thread 3: restoring create database on `db_mydb_archive` from db\_mydb\_archive-schema-create.sql. Tables 0 of 136 completed  
> \*\* Message: 01:55:34.340: Filename to open: //backup/db\_mydb\_archive-schema-create.sql
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.340: Thread 4: Error restoring 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=0 COMMENT=‘Statistics on Columns’’ at line 14
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.341: Thread 4: Retrying last failed executed statement
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.341: Thread 4: Error restoring: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=0 COMMENT=‘Statistics on Columns’’ at line 14  
> \*\* Message: 01:55:34.341: Thread 1: restoring table `mysql`.`time_zone_name` from //backup/mysql.time\_zone\_name-schema.sql  
> \*\* Message: 01:55:34.341: Dropping table or view (if exists) `mysql`.`time_zone_name`
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.341: Thread 4: issue restoring //backup/mysql.column\_stats-schema.sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=0 COMMENT=‘Statistics on Columns’’ at line 14  
> \*\* Message: 01:55:34.341: Thread 3: restoring table `db_mydb_archive`.`table1` from //backup/db\_mydb\_archive.table1-schema.sql  
> \*\* Message: 01:55:34.341: Thread 4: restoring table `db_mydb_archive`.`table2` from //backup/db\_mydb\_archive.table2-schema.sql  
> \*\* Message: 01:55:34.341: Dropping table or view (if exists) `db_mydb_archive`.`table1`  
> \*\* Message: 01:55:34.341: Dropping table or view (if exists) `db_mydb_archive`.`table2`  
> \*\* Message: 01:55:34.341: Thread 2: restoring table `db_mydb_archive`.`table3` from //backup/db\_mydb\_archive.table3-schema.sql  
> \*\* Message: 01:55:34.342: Dropping table or view (if exists) `db_mydb_archive`.`table3`  
> \*\* Message: 01:55:34.342: Thread 4: Creating table `db_mydb_archive`.`table2` from content in //backup/db\_mydb\_archive.table2-schema.sql.  
> \*\* Message: 01:55:34.344: Thread 2: Creating table `db_mydb_archive`.`table3` from content in //backup/db\_mydb\_archive.table3-schema.sql.  
> \*\* Message: 01:55:34.344: Thread 1: Creating table `mysql`.`time_zone_name` from content in //backup/mysql.time\_zone\_name-schema.sql.  
> \*\* Message: 01:55:34.344: Thread 3: Creating table `db_mydb_archive`.`table1` from content in //backup/db\_mydb\_archive.table1-schema.sql.
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.344: Thread 1: Error restoring 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Time zone names’’ at line 5
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.345: Thread 1: Retrying last failed executed statement
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.345: Thread 1: Error restoring: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Time zone names’’ at line 5
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.345: Thread 1: issue restoring //backup/mysql.time\_zone\_name-schema.sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=1 COMMENT=‘Time zone names’’ at line 5  
> \*\* Message: 01:55:34.345: Thread 1: restoring table `db_mydb_archive`.`table4` from //backup/db\_mydb\_archive.table4-schema.sql  
> \*\* Message: 01:55:34.345: Dropping table or view (if exists) `db_mydb_archive`.`table4`  
> \*\* Message: 01:55:34.346: Thread 1: Creating table `db_mydb_archive`.`table4` from content in //backup/db\_mydb\_archive.table4-schema.sql.  
> \*\* Message: 01:55:34.350: Thread 4: Table `db_mydb_archive`.`table2` created. Tables 0 of 217 completed  
> \*\* Message: 01:55:34.350: Thread 4: restoring table `db_mydb_archive`.`table5` from //backup/db\_mydb\_archive.table5-schema.sql  
> \*\* Message: 01:55:34.350: Dropping table or view (if exists) `db_mydb_archive`.`table5`  
> \*\* Message: 01:55:34.350: Thread 2: Table `db_mydb_archive`.`table3` created. Tables 0 of 221 completed  
> \*\* Message: 01:55:34.350: Thread 2: restoring table `db_mydb_archive`.`table6` from //backup/db\_mydb\_archive.table6-schema.sql  
> \*\* Message: 01:55:34.350: Dropping table or view (if exists) `db_mydb_archive`.`table6`  
> \*\* Message: 01:55:34.351: Thread 4: Creating table `db_mydb_archive`.`table5` from content in //backup/db\_mydb\_archive.table5-schema.sql.  
> \*\* Message: 01:55:34.351: Thread 2: Creating table `db_mydb_archive`.`table6` from content in //backup/db\_mydb\_archive.table6-schema.sql.  
> \*\* Message: 01:55:34.354: Thread 1: Table `db_mydb_archive`.`table4` created. Tables 0 of 236 completed  
> \*\* Message: 01:55:34.354: Thread 1: restoring table `mysql`.`help_keyword` from //backup/mysql.help\_keyword-schema.sql  
> \*\* Message: 01:55:34.354: Dropping table or view (if exists) `mysql`.`help_keyword`  
> \*\* Message: 01:55:34.356: Thread 1: Creating table `mysql`.`help_keyword` from content in //backup/mysql.help\_keyword-schema.sql.  
> \*\* Message: 01:55:34.356: Thread 1: restoring `mysql`.`time_zone_transition_type` part 1 of 1 from mysql.time\_zone\_transition\_type.00000.sql. Progress 1 of 129. Tables 0 of 259 completed  
> \*\* Message: 01:55:34.356: Filename to open: //backup/mysql.time\_zone\_transition\_type.00000.sql
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.357: Thread 1: Error restoring 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=0 COMMENT=‘help keywords’’ at line 6
> 
> \*\* (myloader:1): WARNING \*\*: 01:55:34.358: Thread 1: Retrying last failed executed statement
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.358: Thread 1: Error restoring: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=0 COMMENT=‘help keywords’’ at line 6
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.358: Thread 1: issue restoring //backup/mysql.help\_keyword-schema.sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PAGE\_CHECKSUM=1 TRANSACTIONAL=0 COMMENT=‘help keywords’’ at line 6  
> \*\* Message: 01:55:34.358: Thread 1: restoring create database on `sys` from sys-schema-create.sql. Tables 0 of 263 completed  
> \*\* Message: 01:55:34.358: Filename to open: //backup/sys-schema-create.sql  
> \*\* Message: 01:55:34.358: Thread 1: restoring table `mysql`.`slave_worker_info` from //backup/mysql.slave\_worker\_info-schema.sql  
> \*\* Message: 01:55:34.358: Dropping table or view (if exists) `mysql`.`slave_worker_info`  
> \*\* Message: 01:55:34.358: Thread 4: Table `db_mydb_archive`.`table5` created. Tables 0 of 264 completed  
> \*\* Message: 01:55:34.358: Thread 3: Table `db_mydb_archive`.`table1` created. Tables 0 of 264 completed  
> \*\* Message: 01:55:34.358: Thread 4: restoring table `mysql`.`slave_master_info` from //backup/mysql.slave\_master\_info-schema.sql  
> \*\* Message: 01:55:34.358: Dropping table or view (if exists) `mysql`.`slave_master_info`  
> \*\* Message: 01:55:34.358: Thread 3: restoring table `mysql`.`table_stats` from //backup/mysql.table\_stats-schema.sql  
> \*\* Message: 01:55:34.358: Dropping table or view (if exists) `mysql`.`table_stats`
> 
> \*\* (myloader:1): CRITICAL \*\*: 01:55:34.358: Drop table failed: Access to data dictionary table ‘mysql.table\_stats’ is rejected.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [July 24, 2023, 2:14am UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/6 "2023-07-24T02:14:28Z")

</div>

For starters, I would have skipped dumping the `mysql` database and `sys` database, as those will contain MariaDB-specific tables that won’t restore properly into MySQL.

Secondly, what engine is this table `mysql.time_zone_name` using? These may be using an engine not supported by MySQL.

Yea, it looks like most, if not all, of you issues above are with the `mysql` database. I would take another mydumper and skip the `mysql` database. You can do so using an inverse regex:

```
 mydumper --regex '^(?!(mysql\.|test\.))'

```

That will skip any tables in the `mysql` and `test` databases.

---

<div class="post-metadata">

**Author:** ![nunop](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@nunop](https://forums.percona.com/u/nunop)\
**Post date:** [July 24, 2023, 2:43am UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/7 "2023-07-24T02:43:31Z")

</div>

Thank you again @matthewb - I really appreciate your help.

`mysql.time_zone_name` uses Aria, which I believe is MariaDB-specific.

I filtered out “mysql” and “sys” as suggested, and that ran MUUUCH better.

I’m still getting a few errors, such as:

> \*\* (myloader:1): CRITICAL \*\*: 02:34:31.065: Thread 3: Error restoring: Character set ‘utf8mb4\_unicode\_ci’ cannot be used in conjunction with ‘binary’ in call to regexp\_replace.

> \*\* (myloader:1): CRITICAL \*\*: 02:34:31.065: Thread 3: issue restoring //backup/db\_mydb.table1-schema.sql: Character set ‘utf8mb4\_unicode\_ci’ cannot be used in conjunction with ‘binary’ in call to regexp\_replace.

> \*\* (myloader:1): CRITICAL \*\*: 02:34:26.619: Thread 4: Error restoring: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘`PAGE_COMPRESSED`=‘ON’’ at line 4

(and the loader enters in an infinite loop because of some of these CRITICAL errors, which didn’t create the table)

But I’ll see if I can resolve these by myself, either by doing a `sed` replace, or by actually altering the database before the dump (if they’re small tables).

Thank you again.

---

<div class="post-metadata">

**Author:** ![nunop](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@nunop](https://forums.percona.com/u/nunop)\
**Post date:** [September 17, 2023, 11:19am UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/8 "2023-09-17T11:19:45Z")

</div>

@matthewb - I’m delighted to let you know that I’ve finally done the migration. I had to create a very robust script that does “all for me”, by running mydumper, fix the schemas/scripts, run myloader, compare all COUNT(\*), tables, databases, etc… Tested very well, and ran today in production. All successful!

However… I’m having serious Performance problems, mostly because the way Percona is using indexes seem to be quite different than MariaDB, and in some queries, I don’t seem to be able to FORCE INDEX usage to the way it was doing in MariaDB.

In another query, it seems to be using the same/expected indexes and rows examined, but while in MariaDB all components of the EXPLAIN are “Using where”, in Percona one of them is doing “Using filesort”, which is causing the performance issue, I think.

I tried to set the “optimized\_switch” to the same as MariaDB (well, at least those that exist on Percona too), but it didn’t seem to resolve the problem.

Thank you!

---

<div class="post-metadata">

**Author:** ![nunop](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@nunop](https://forums.percona.com/u/nunop)\
**Post date:** [September 17, 2023, 12:46pm UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/9 "2023-09-17T12:46:38Z")

</div>

@matthewb - I’ve been slowly optimizing the queries for Percona, and I’m at a much better situation now.

Still getting one or two slow queries (from those improved), but I’ll continue to see what I can do.

However, for some reason I’m getting intermittent logs of:

> mysqli\_connect(): Error while reading greeting packet.

Percona is running on Docker, in the same host where PHP is running (not on Docker).

MariaDB was running on the Host itself (not on Docker).

It’s still `localhost`, but different port (shouldn’t make any difference). I understand there may be a slight impact for being on Docker, but I wonder if there’s anything I can do to fully eliminate this intermittent connection failures.

Thank you very much!

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [September 17, 2023, 1:57pm UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/10 "2023-09-17T13:57:10Z")

</div>

I would skip connecting over TCP and use the socket connection, since both PHP and MySQL live on the same machine. I’m assuming that you have mounted a path on the host into the MySQL docker for storing data. If so, then you should be able to find the socket path. If you didn’t, then you’ll need to mount a new directory into the container and configure mysql to put the socket into this path. Then you can access it from the host PHP code.

> <https://superuser.com/questions/1411402/how-to-expose-linux-socket-file-from-docker-container-mysql-mariadb-etc-to/1411424#1411424>

---

<div class="post-metadata">

**Author:** ![nunop](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@nunop](https://forums.percona.com/u/nunop)\
**Post date:** [September 17, 2023, 3:54pm UTC](https://forums.percona.com/t/what-would-be-the-best-way-to-migrate-mariadb-10-8-to-percona-server/21972/11 "2023-09-17T15:54:02Z")

</div>

Thank you very much @matthewb !

This was a very good idea. 👍🏼

There’s indeed a mounted volume for the files, but I had to put the “mysql.sock” file in a location that PHP can access (as the Docker mounted volumes are all well protected in terms of permissions!)

I’ve then made PHP connect through the socket, and so far I haven’t got those errors anymore!

**Big Thank You, again, and I also appreciate your quick response, on a Sunday!**

It’s been a long day, and still have to fix the performance of some other queries!! 🥱😪😴⭐  
Having good progress so far, at least.