XF 2.2 Can MariaDB Backups work on MySQL?

xml

Active member
I am moving my forums to a new VPS webhost so I am running tests on the new hosting, restored my user account backup on the new host. My old host DB was MariaDB 10.3 and the new host DB is MySQL 5.7 and the test forums running normally. Is that possible? can XF forums be restored in MySQL although its originally backedup from MariaDB?
 

briansol

Well-known member
XF doesn't really use advanced things. it's basic statements and DDL/DML. So, yes, there are differences, new engines, etc, but the board won't use it most likely.

Maria is more advanced and i would recommend upgrading to it again if you can, but youll be just fine on mysql.
 

MySiteGuy

Well-known member
The incompatibilities between the two tend to not affect the vast majority of software applications. Both try very hard to avoid changing core SQL functionality for this very reason - not only would they break cross compatibility, but they would also break backwards compatibility with their own product.

The only issue I've run into with Xenforo is when doing a mysqldump from MySQL 8+ and restoring it to MariaDB. The UTF8MB4 character set collation in MySQL 8+ is slightly different for MySQL 5.x and MariaDB. I can quickly resolve it with a find/replace in the dump file or slight change to the dump options.

This type of situation is very unlikely to affect anyone here, unless they were moving to a new server and going from MySQL 8+ to MariaDB in the process.
 

MySiteGuy

Well-known member
On my own servers, I've been running MariaDB 10.6 since it came out, with Xenforo 1.x and 2.x - zero problems.

Edit - 10.6 also works fine with WordPress and every other software package I've used that support MySQL.
 

sbj

Well-known member
@MySiteGuy

Well, the question was not if MariaDB 10.4+ runs smoothly with XF. It surely does, but as admins who are not good in server management, we have to take everything in consideration. So, since MariaDB 10 came out I didn't have to think twice about running it or updating it to the newest branch. But since 10.4+, now it is not compatible with MySQL anymore, so one is committed from then on. Not that I will ever go to MySQL, but being prepared is always good.

Also, I can't give you details, but if you read eva's posts on his forum, he mentions that there are also some speed problems with the new MariaDB 10.4x branches. So, XF won't have problems running it, but from server-side it seems it is not optimized yet. That is one other reason I stay away from it for now.

1641425382695.png
 

eva2000

Well-known member
My old host DB was MariaDB 10.3 and the new host DB is MySQL 5.7 and the test forums running normally. Is that possible? can XF forums be restored in MySQL although its originally backedup from MariaDB?

Pretty lucky as MariaDB 10.3 is the last version is drop-in replaceable and works fine with MySQL 5.7. As @sbj linked to and @MySiteGuy has mentioned MariaDB 10.4+ and MySQL 8.0 on the other can run into gotchas if you're switching between them depending on how you're using MySQL. If you're switching between MariaDB 10.4 and MySQL 8.0, I'd be doing a test on a separate server or MySQL instance just to be sure everything is working. You don't want your MariaDB 10.4 server to be the only instance (without backups) available if you run into issues during the switch.
 

Masetrix

Well-known member
The only reason why we switched from Oracle-MySQL to MariaDB is that more and more large Linux OS providers (Ubuntu, SuSE, Red Hat ...) have switched from MySQL to MariaDB as the standard. ACPs like Plesk also support, e.g. for
OS upgrades (Ubuntu 18.4 -> 20.4 ...) only MariaDB therefore ...

MariaDB is used at ServiceNow,[35] DBS Bank,[36] Google,[37] Mozilla,[38] and, since 2013, the Wikimedia Foundation.[39]

Several Linux distributions and BSD operating systems include MariaDB.[40] Some default to MariaDB, such as Arch Linux,[41] Manjaro,[42] Debian (from Debian 9),[43] Fedora (from Fedora 19),[44][45] Red Hat Enterprise Linux (from RHEL 7 in June 2014),[46][47] CentOS (from CentOS 7),[48] Mageia (from Mageia 2),[49] openSUSE (from openSUSE 12.3 Dartmouth),[50] SUSE Linux Enterprise Server (from SLES 12),[51] OpenBSD (from 5.7),[52][53][54] and FreeBSD.[55]
 

MySiteGuy

Well-known member
@MySiteGuy

Well, the question was not if MariaDB 10.4+ runs smoothly with XF. It surely does, but as admins who are not good in server management, we have to take everything in consideration. So, since MariaDB 10 came out I didn't have to think twice about running it or updating it to the newest branch. But since 10.4+, now it is not compatible with MySQL anymore, so one is committed from then on. Not that I will ever go to MySQL, but being prepared is always good.
Moving to 10.4+ doesn't commit you. I do dumps and restores between MySQL 5.6/5.7, 8 and MariaDB 10.0-10.6 daily and have yet to run into anything beyond changing the character set when dumping from 8 and restoring to 10.6.

Also, I can't give you details, but if you read eva's posts on his forum, he mentions that there are also some speed problems with the new MariaDB 10.4x branches. So, XF won't have problems running it, but from server-side it seems it is not optimized yet. That is one other reason I stay away from it for now.
MariaDB 10.6+ not only works very well with Xenforo, I've found it to be quicker than MySQL 8 under heavy multi-threaded loads.

The reluctance you're stating is much worse than what I've encountered. It's the more advanced system admins who are doing more advanced things like in place switching between products in place without doing individual point releases, and master/slave replication that are likely to encounter any issues, and those people generally know how to handle it.

The average non-technical system user is going to be using backup and restore tools that use mysqldump style backups, and these work fine between products.
 

sbj

Well-known member
Moving to 10.4+ doesn't commit you. I do dumps and restores between MySQL 5.6/5.7, 8 and MariaDB 10.0-10.6 daily and have yet to run into anything beyond changing the character set when dumping from 8 and restoring to 10.6.
Good to know. My knowledge is limited and I shy away from these stuff. I only rely on people who know what they are talking about (like eva) and take their words as granted. And getting knowledge from you is also nice, which takes away my concerns.

MariaDB 10.6+ not only works very well with Xenforo, I've found it to be quicker than MySQL 8 under heavy multi-threaded loads.
My statement was in comparison to MariabDB 10.3. From what I've read from eva, he states that the 10.4+ releases are not as well optimized as the 10.3 release. Of course we are taking about milliseconds here (I think), so nobody sane will ever notice it. But nevertheless waiting for a product to mature always worked out for me. So, that is why I always hesitate. Because unlike you I am not one of those who know how to handle it.

For example the latest vulnerability in ES forced me to update my ES installation on my server. I had the ES service running for 2 straight years without needing to reboot it once. And because how lucky I am, once I updated my ES, suddenly the service didn't want to start anymore, was not working. For me that means "panic mode". Now I have to figure out why the hell it does not work anymore. I hate this. This is why I hesitate on updating stuff, always something breaks without you understanding why it happens. Well, after searching and searching, I found out the reason (you can read it here). Who would of thought of that? ES decided to not support some jvm.options anymore (which were there by default), which I can't foresee. So, the conclusion is, what if I update my MariaDB 10.3 to a newer branch and it breaks, too? Out of nowhere these stuff happen. So, I have to make sure and be on the safer side. Just like knowing that the conversion between MariaDB and MySQL is not taken as granted anymore. But you state that it doesn't affect the general admin, so good to know, thanks.
 

MySiteGuy

Well-known member
I need to step in and say I'm not disagreeing with Eva one bit. His warnings are 100% valid.

There's a lot to digest in his notes, and the things listed are the things advanced admins and developers are likely to encounter. Average hobbyist and part time admins, don't worry a great deal.. and if you do encounter something 15 minutes here or on stack exchange and you'll find your answer.

I would not, for instance, recommend upgrading between MySQL 8 and MariaDB 10.4 "in place" on the same box because of the binary file differences. You'd want to use an older version as a intermediary and upgrade to the newer version. And as Eva said, have backups (do that even copying between same versions!).

And another example, are subtle differences in JSON between the two products, but programmers, like those at Xenforo, are going to stick with things that work between both. If it were a major corporation where all the internal systems were one SQL product line, and you have internal-only apps then it's more likely I'd expect to see a dependency with one product rear it's ugly head.
 

eva2000

Well-known member
Good to know. My knowledge is limited and I shy away from these stuff. I only rely on people who know what they are talking about (like eva) and take their words as granted. And getting knowledge from you is also nice, which takes away my concerns.

For MariaDB 10.4+ at least this is the specific thread that I mentioned in your linked thread of mine regarding MariaDB 10.4 moving away from drop in replacement to Oracle MySQL https://community.centminmod.com/threads/interesting-look-at-whats-new-in-mariadb-10-4.17676/

one such is
MariaDB 10.4 will no longer merge InnoDB features from Oracle MySQL upstream.
Quite recently we saw a blog post explaining MariaDB stance on InnoDB changes and compatibility. The gist is that MariaDB will no longer merge InnoDB features from MySQL, the focus will be on the stability and performance improvement done by MariaDB. This basically means that MariaDB will become incompatible with MySQL. Even if you could do the binary upgrade in the past, this will not be possible in the future. Even right now it can be tricky to execute. This increases the importance of tools like mydumper/myloader as logical backup will be the only way for migration. What is good, MariaDB will be able to own the stability of their fork of InnoDB - they will not have to deal with issues introduced by upstream developers therefore we can expect less bugs being introduced.
For us users, all of this means that MariaDB 10.4 should be more stable than the previous releases. It also means that eventually we will have to learn internals of two different storage engines - especially if we care about the performance. This is far from ideal but that’s the way it is. Tools will have to be designed to work with one or the other version of InnoDB (or additional work will have to be added to support both MySQL and MariaDB). We will keep an eye on how this will progress. When you think of it, it is not such a surprising move - MariaDB always had to take its time to integrate with more recent InnoDB version. With more and more incompatible features being added to MariaDB and huge changes introduced in MySQL 8.0, it makes sense to focus on developing new functionality rather than on porting incompatible InnoDB from upstream MySQL.

As @MySiteGuy stated, logical backups via mysqldump/mydumper etc is the only way. But it's more reference to feature set 1 for 1 compatibility which is diverging between MariaDB 10.4+ and Oracle MySQL. So in-place upgrades/swaps between MariaDB and Oracle MySQL are not advised.

So for Xenforo MySQL usage probably not that big of a deal if you're doing mysqldump/restore type switches and aren't relying on MariaDB/Oracle specific InnoDB features that are unique to their respective InnoDB engines. But my comments on my forums are there as a notice as I do not know how Centmin Mod users will be using their MariaDB MySQL servers and what InnoDB/features they'd utilise. I've seen some Centmin Mod users swap out MariaDB for Oracle or Percona MySQL 8 themselves successfully and not so successfully.
 
Last edited:
Top