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:
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!!!