Restoring mySQL database to mariadb

Emoncms
,
1

Does anyone know for sure what the situation is if I create a new emoncms server on Debian9(Stretch) which uses PHP7.0 and mariadb by default and “transport” a mySQL created emoncms database to mariadb?

I can find loadsa info on upgrading Debian8(Jessie) to Debian9(Stretch) which includes details on the mysql/mariadb position which I believe is that no existing database will be converted from mysql to mariadb automatically.

And I can find info on specifically updating a database from mysql to mariadb,

However the general consensus appears to be that starting a fresh Debian9 image with mariadb as the default database is better than upgrading from Debian8 and mysql. Alas I do not seem to be able to find any info on what to do with my old mysql database once I have created my fresh Debian9/mariadb install, I have found several posts raising concerns about using mysqldump with mariadb though, hence my hesitation to go that route.

Given that upgrading the OS doesn’t apparently “convert” existing databases, I am also hesitant to just copy the whole database into place as I would normally do when moving emoncms between servers both using mysql.

Anyone any thoughts or experience in this area?

(See the Raspbian Stretch thread too. )

[EDIT1]
Adding some links and quotes of interest

Incompatibilities between MariaDB 10.0 and MariaDB 5.5 / MySQL 5.5 appear minimal in this list

This passage from the “What’s new in Debian 9” page on the debian site suggests they are directly interchangeable

For example, installing the metapackage default-mysql-server will install mariadb-server-10.1. Users who had mysql-server-5.5 or mysql-server-5.6 will have it removed and replaced by the MariaDB equivalent.

Clearly the DB formats are different however as this warning (from the same page on Debbian site) states you cannot go back from mariadb to mysql

“Note that the database binary data file formats are not backwards compatible, so once you have upgraded to MariaDB 10.1 you will not be able to switch back to any previous version of MariaDB or MySQL unless you have a proper database dump. Therefore, before upgrading, please make backups of all important databases with an appropriate tool such as mysqldump.”

[EDIT2]

Another subtle clue is the use of “in practice” in this statement
For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 → MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 is compatible with MariaDB 5.5 and also in practice with MariaDB 10.0).

From that same page, this appears to be the answer
This means that for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. (No need to convert any datafiles if you use same main version, like 5.1). You must however still run mysql_upgrade to finish the upgrade. This is needed to ensure that your mysql privilege and event tables are updated with the new fields MariaDB uses.

The most important bit being “You must however still run mysql_upgrade to finish the upgrade.”

For more on the mysql_upgrade command see

2

Are you currently using MYSQL v5.5 in Jessie?
From the Stretch sources you will get Mariadb v10.1.23 (last time I checked) and compatibility is discussed between those versions at https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/

From my experience if it helps, I exported the MYSQL tables from MYSQL (Jessie), and simply imported them straight into Mariadb (Stretch), with no problems, and I did not run the mysql_upgrade command either.

Paul

3

Thanks Paul, just for clarity, when you say you exported and imported, are you referring to the mysql import functions or are you just saying you moved the backed up files into place and just ran mariadb.

I have done pretty much the same thing I’ve simply transfered a copy of my live emoncms database from my live server running mySQL 5.5.58 to this new server running mariadb 10.1.26, however I did run the mysql_upgrade command, I too (thus far) have not seen any problems but I haven’t set up my vhosts yet so there is no live data coming in yet.

I have to say running the mysql_upgrade command is quite reassuring when you see all the lines end in “OK”

and when you run it a second time it just basically says there’s no need unless you add the --force option to override.

I’ve been using

sudo mysql_upgrade --verbose --verbose --force

while experimenting

4

I actually used phpMyAdmin to export the database as a SQL file, and then imported the database again using phpMyAdmin, but of course that’s just the same as using the command prompt usual commands.

My understanding (right or wrong!) was that most of the MYSQL/Mariadb incompatibilities occurred in the more advanced features of MYSQL, which are not needed or used by emoncms.

Paul

5

Oh okay. I had read some minor warnings about the use of mysqldump with mariadb somewhere and the mysqldump page on the mariadb site was just as cryptic as most of the info I’m finding on this topic. So without knowing it was ok to do and no experience of having done it that way previously I decided it wasn’t for me.

I think you are right that most of the differences are more advanced stuff and horizontal moves from mysql to mariadb are very straight forward, but the fact we are going from 5.5 to 10.1 seems to infer some sort of in situ change to the db files or structure as it is (again, apparently) not possible to undo the changes made to the db during that move. But I’m starting to think it’s all just hype.