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. )
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.”
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