MySQL DB not compatible with Maria DB

Virtualmin
1
SYSTEM INFORMATION
OS type and version Ubuntu Linux 22.04.1
Webmin version 2.001
Virtualmin version 7.3-1
Related packages SUGGESTED

I installed Virtualmin 7 on Ubuntu 22.04 and for some reason it comes with MariaDB. The website that I want to install has mysql db, version 8.0.31-0. I tried to upload mysql db to MariaDB and got errors. It looks like I need to use mysqldump. Please I do have some questions.
Q1: Does Virtualmin give the option to use mysqldump to move my sqldb to Mariadb?
Q2: If the answer is no on Q1, what command could I use to do that via ssh (on Ubuntu 20.04 installation)?
Thanks a lot!

2

And how exactly did you try to do that?

Did you back up the original MySQL database and then try to import it into Virtualmin via the import database tool in Virtualmin?

Did you use phpMyAdmin?

What exactly did you do?

3

Correct, that’s what I did.

4

No, I dont have phpMyAdmin installed. I’m using the default tools of Virtualmin (Run SQL from file) to import the database backup file.

5

A Virtualmin backup only backs up databases by performing a mysqldump. That is the only way to safely backup a database…the binary files are not guaranteed to work from one version to another, and definitely won’t work going from MySQL to Mariadb.

If a restore fails because of the database, you’ll either need to munge the data manually or stick with MySQL. We’re not experts on migrations from MySQL to MariaDB. Mostly, it Just Works, as the two are very nearly compatible. But, they have begun to diverge (we should have switched Ubuntu over sooner…MySQL is seemingly intentionally introducing incompatibilities now, and has been for some time).

This has been discussed quite a bit in the forums. I recommend you do a search of posts about the change in Virtualmin 7.

With Virtualmin 7, we default to mariadb on all distributions. We switched to MariaDB on all but Ubuntu during Virtualmin 6, now they’re all MariaDB, as the Ubuntu community seems to have caught up and prefers MariaDB, as well.

If you are coming from a Virtualmin 6 system on Ubuntu, and you don’t have the experience to migrate a database from MySQL to MariaDB (dumps are not always compatible without a little data munging…the incompatibilities are minor, but still probably need some reading and experimentation to sort out), and a dump/restore has errors, you should probably stick with MySQL. I’ve discussed how to switch to MySQL on a fresh installation, but to review:

  1. Install Virtualmin. Do not run the postinstall wizard or even login to Webmin.
  2. Install MySQL, replacing MariaDB, client, server, and libs.
  3. Make sure MySQL is now installed and running (systemctl status mysql). Login to Webmin and go through the postinstall wizard. You’re now running MySQL and any databases dumped on MySQL should import without complaint.

Note that even now, most apps stick with a compatible subset of functionality between MySQL and MariaDB, so usually a dump/restore will work. But if you have custom development, or have been using any apps that are very focused on MySQL, use new database features, and don’t pay attention to compatibility with MariaDB, you may have errors. In which case, the options are to fix the errors and import the db manually, or not even try and just stick with MySQL (but, MySQL will probably keep getting less compatible over time, and you’ll be further entrenching yourself in that ecosystem, which we’re all mostly leaving behind).

6

Thank you very much. I do have two questions please.

  1. Where could I find the ssh syntax for each of the steps described above?
  2. Is it easy to convert the existing mysql database to Mariadb on my Ubuntu 20.04 server? Then I could get a back of the Mariadb version and install it on Ubuntu 22.04 server with Virtualmin 7.
    Thanks a lot!
7

“Easy” is a relative term. Depends on your databases, and depends on your skill level. It also depends on the version of the MySQL database you’re coming from…newer versions have more features that are incompatible. I have, so far, not met any situation that took more than a few minutes to fix. And, mostly my databases have migrated without change. Up until just a couple years ago, a dump/restore was pretty much all that was ever needed. But, it would be entirely possible to make a database in a current version of MySQL that is extremely difficult to convert to MariaDB…not as much the other direction, as MariaDB has been more careful about compatibility (across versions and between the MySQL and MariaDB).

If you want to do that, I would suggest you dump your databases on your production server, and try to import them into MariaDB on a development server. Make a note of problems during the restore, and google them. I can’t tell you what you have to do to convert. It may Just Work. It may require some modifications. Even if you tell me the errors I still cannot tell you how to fix it. I would have to google it. So…I’m going to just tell you to google it.

Once you have identified problems, you can probably alter the way the database is used on the old system, so your Virtualmin backups will just work when you import them, but I’d probably just not bring the databases over as part of the Virtualmin migration and import them manually once I’d fixed them on the devel server.

8

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.