Hello, I need to replace a simple MariaDB 10.11.6 server (no replication or anything, few InnoDB dbs only) with Percona. If I understood correctly, MariaDB 10.11.6 is based off Mysql 8.x.
Is still possible to do an in-place replace with this version? Which version of Percona should I use? Can I just run a “percona-release setup ps80” followed by “apt install percona-server-server”? Anything I should know of, except from saving the /var/lib/folder in case of problems?
PS: The server is running Plesk, the setup should not change the mysql admin password.
Hello @xirtam, unfortunately Percona Server is not a drop-in replacement for MariaDB. Percona Server is based off Community MySQL and is therefor a drop-in. MariaDB on the other hand is a fork of MySQL Community going back to 5.5. There is no guarantee that MariaDB has not made any changes to the on-disk data structures which would prevent in-place upgrade. The best action is to create a new Percona MySQL 8.0 as a replica of this MariaDB server (this is supported). Then cut-over to the Percona MySQL when you are ready. You can use mysqldump or mydumper to accomplish this fairly easily.
Understood. Maybe someone else who already did it can tell me if (even unofficially) he was able to just drop-in on this version.
I remember doing it some time ago without issues with MariaDB based on MySQL 5.7 even if technically the drop-in is only guaranteed on 5.5 (when MariaDB was still kinda stuck to the same codebase as MySQL).
So my hope is that someone already tried it on a simple mariadb setup with the same version and can report his success\failure.
I will ofc use the backup\restore solution in case it’s not possible but, since it requires longer downtimes that just shut down mariadb, apt some package and restart it, I hope to avoid it.
Just one more question: If I copy my /var/lib/MySQL folder elsewhere and try a drop-in replace with Percona on MariaDB, in case anything does wrong can I technically just restore the MariaDB apt package, put the old lib folder back and just restart it? Will that work as a backup? Just because it’s way faster than mysqldump everything manually.
Also in this case, will the apt install percona-server-server keep all my existing user intact? especially the mysql admin ones which are used by Plesk.
Thanks again
The procedure I described above (ie: creating a replica) is a “zero downtime” migration. An “in-place upgrade” requires downtime.
Yes. The OS packages do not modify any data contained inside the database.
Hi xirtam,
Newer MariaDB versions are diverging and have more differences , incompatibilities and possible problems when migrating from MariaDB to MySQL/Percona Server.
If you can allow for downtime then Matthew’s suggestion of taking a logical backup should be the way to go. More info: Migrating From MariaDB to Percona Server for MySQL 8: The Security Witchcraft
If you were using older versions then the trick of using 5.7 will do: Want to Migrate From MariaDB 10.4 to MySQL 8.0 but Facing Hurdles? MySQL 5.7 to the Rescue!
Replicating directly from newer MariaDB versions to MySQL won’t likely work unless very special (and basic) cases
Regards
How is it a zero downtime? First of all I have to lock all tables from writing, meaning set all the websites in maintenance mode (they are ecommerces). Then I have to wait for the dump to be done (quite some time), move on the other server with Percona installed, import the dump (again some time), check if everything is correct then install Percona on the old server, export the dump again from the cloned Percona and reimport again on the old one.
All this while the website will be closed for maintenance.
Or I’m doing it wrong?
PS: if everything is correct, can I just copy the /var/lib/MySQL folder back to the old server instead of doing again the dump? It’s way faster for me to just scp files between servers.
This is done using a transaction which requires no locking. Transactions are isolated snapshots of the tables when can be exported without any locking. We highly recommend GitHub - mydumper/mydumper: Official MyDumper Project which will execute FLUSH TABLES WITH READ LOCK which locks everything for about just a few seconds to coordinate with the binary logs. A few seconds is not considered an “outage” since the application will simply see a pause, then continue as normal, not an error.
It doesn’t matter how long it takes, as there is no blocking/locking/downtime, right? The time depends on the size of your database.
Why would you need ot do that? You dump from MariaDB, import into a Percona Server. Configure replication from MariaDB (source) → Percona (replica) to keep changes in sync. Then, whenever you are ready, simply reconfigure the website/app to point to Percona server. Migration is complete. Zero downtime (aside from the app restart period, if needed. If a PHP web app, no restart needed at all, just a config change).
What you described is incorrect. See my previous comment about setting up a replica.
It is, but as both myself and Carlos have pointed out, MariaDB is quite diverged from MySQL and there is no guarantees MySQL will be able to read the MariaDB data files.
Yes, but it’s not about the locking. Is about avoiding new writings (aka orders on the ecommerce) on the old MariaDB instance even after it’s copied. Until Percona replaces it.
I think the misunderstood here is thinking that I have a separate server for Mysql. MariaDB runs on the same server as the webserver. I can’t just replace an IP in the website configs. They all point to localhost.
No, I meant copying back the /var/lib/folder after I use mydumper. From the imported Percona instance to the old MariaDB server (which in the meantime has become a Percona server by removing mariadb pkg and installing percona-server).
Anyway I tried the mydumper/myloader migration on a test server and it went all well except for the user accounts which were without password apparently. I followed the guide linked before (Migrating From MariaDB to Percona Server for MySQL 8: The Security Witchcraft) that uses pt-show-grants to migrate users. But only step1.sql and step3.sql had data, step2/4 were empty. Not sure if that’s normal behaviour.
I’ll retry tomorrow
This is why you configure replication as I’ve described above. The data stays in sync between the two. Any writes that occur on MariaDB are replicated to Percona.
Sure you can. Just replace “localhost” with the IP of the new Percona MySQL. Having them both on the same machine is considered bad practice anyways.
Ahh, I understand now. Yes, you could do that, but that would incur downtime.
Hmm. Can you provide the command you used? Did you skip the mysql database on the export? It’s also possible that MariaDB’s user table is different from MySQL’s and the import may have failed.
That’s also an old blog. The Percona Toolkit has had updates since 2023 and there should be more direct support for MariaDB users.
Yes, i skipped mysql, sys, information_schema and performance_schema tables (not sure I should skip the schemas ones?)
I used this slightly modified version of what it’s written on that guide:
mysql -Bsne "select concat('CREATE USER IF NOT EXISTS ',user,'@''',host,'''',' IDENTIFIED WITH mysql_native_password AS ''',authentication_string,''';') from mysql.user where is_role='N' and user <> 'mariadb.sys' union all select concat('CREATE ROLE IF NOT EXISTS ',user,';') from mysql.user where is_role='Y' order by 1 ;" 2>/dev/null > step1.sql
mysql -Bsne "select concat('ALTER USER IF EXISTS ',user,'@''',host,'''',' IDENTIFIED WITH mysql_native_password AS ''',authentication_string,''';') from mysql.user where is_role='N' and user <> 'mariadb.sys'" 2>/dev/null > step2.sql
pt-show-grants | egrep -v "Grants|IDENTIFIED|mariadb.sys|PROXY" > step3.sql
mysql -Bsne "select concat('SHOW GRANTS FOR ',user,';') from mysql.user where is_role='Y' order by 1 ;" 2>/dev/null| grep -vi PUBLIC | sed 's/$/;/g' > step4.sql
AND AFTER REPLACING MARIADB WITH PERCONA
for NUM in {1..4}; do mysql -f -vve "source step$NUM.sql"; done
I did a second try just now. Instead of importing them with the last command, i just copypasted every stepX.sqls inside a mysql root shell. It worked apparently. The test server is running a website correctly. I’ll do some more tests tomorrow before trying it on the live server.
About this solution instead, do you have a guide on how to setup the replication? This way I don’t need mydumper/myloader since the Percona istance will eventually catch up with the MairDB one and the writes after that moment are simultaneous, correct?
I just setup the replica, then when it’s synced I just replace MaridDB pkg with Percona ones and replace the /var/lib/mysql from the replicated instance.
I assume the replication automatically takes cares of “translating” what is incompatible between the two with the correct data. Also I don’t need to skip any table, like the mysql one etc if I want an exact replica. Is that correct?
This way my only downtime would be the time of the apt install and the /var/lib/mysql transfer If I get correctly.
Thanks
You still need to have a backup as starting point for the replica, but after that it should just sync (it appears mariadb source to percona replica is a thing since it was suggested).
When they are in sync you could plan ahead and set a downtime for you to switch plesk config to point to the replica and then promote replica to master.
I dont have a step by step for you, but its fairly easy to accomplish, maybe someone Else has a link.
Edit: And to add, i think you will need to recreate any users in the database as the mysql database is different on mysql/percona and mariadb.
Needless to say you need to test this on a backup and figurer out what works and doesnt. But it is possible…
There are several guides on our documentation pages.
Here is the process, high-level outline:
- Create a new VM/instance
- Install Percona Server on this instance
- Use mydumper on mariadb server to make a consistent logical backup. This backup will include the binary log filename, and position needed to configure replication.
- Use myloader on Percona server to import the backup.
- Configure replication using the details in the mydumper metadata file. Use some of our guides for more details on SQL and user permissions needed.
- Wait for replication to catch up.
- Once replication is done, stop the app server. Shut down mariadb. Change app config to point to Percona server IP address.
- Start apps. Verify app behavior.
No, on the new replica start with Percona. Don’t start with MariaDB. See steps above.
No. If there is a MariaDB specific feature you are using replication will not translate it.
Don’t skip any tables.
Your downtime is the amount of time it takes you to ‘stop app, update app config, start app’. Everything else you do, is async with regards to the app. The app has no idea you are creating a replica.
You are assuming the DB instance is separate from the app. This is not the case.
The SQL servers runs on the same dedicated server (no vm involved here).
So I can only run one of MySQL or Percona at a time (unless configuring different ports and data path which is a lot of work).
I can’t just “change the app config” to switch the sql server. The app instead won’t be touched at all, because it will always need to be localhost:3306.
That’s why the downtime is way bigger than expected.
Anyway I understood all the processes involved, and will do it when a major maintenance downtime is planned.
Correct. It looks like your only option is the following:
- stop app (thereby blocking writes)
- mydumper mariadb
- shutdown mariadb
- make a backup (using tar or zstd) of mariadb $datadir
- remove all mariadb packages
- install percona packages
- start mysql.
- myloader into mysql.
- start app.
I highly recommend that you test/practice the above steps on a test server first.