As part of the migration of my MacOS Server to Linux the first service to migrate is my MySQL 5.7 engine. Although MySQL is not part of MacOS Server anymore for a long time (and I had installed it separately), I will cover the migration here as 1) I still had it running on my MacOS Server and 2) the migration wasn’t smooth so decided to share my learnings here.
The first complication I encountered was that on Debian 9 (Stretch), MySQL (now an Oracle product) is no longer available in the Debian package repository. It has been replaced, by MariaDB, a spin-off by a part of the original MySQL development team at the moment of the acquisition of Sun by Oracle. Although it is supposed (and also looks like) a drop-in replacement, I wasn’t sure how it would handle the migration from MySQL 5.7 to MariaDB 10.1. And it turned out not to be a smooth ride as there were incompatible differences between these two products/versions that could not be handled in a fully automated way.
Failed Attempt 1: mysqldump -A and importing the dump
The first (and usual) approach to migrate MySQL databases is to export it on the old server and import it on the new server. This is how I have done this already many times before so expected this to work. I issued the following command:
sudo /usr/local/mysql/bin/mysqldump -p --all-databases | gzip > hostname.mysql.20180331.dmp.gzon my MacOS Server (it prompted me for the root password as that is the way I set it up), transferred the file to the new server and ran the command:
zcat hostname.mysql.20180331.dmp.gz | sudo mysqlBy default, on MariaDB on Debian allows root to connect and login in using a unix socket so no password is needed for this operations. After the import, the data seemed OK though I was unable to add any users or change rights and after a restart, MariaDB no longer wanted to start and in the logs I got the following error message:
[ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.I tried running the mysql_upgrade command, but that was unable to fix the problem so it turned out that the import would not work as something appears to have changed in the mysql schema related to how the user (rights) are setup.
Failed attempt 2: mysqldump -A --events --routines, import and run mysql_upgrade
After a bit of searching online found an article from the MariaDB team titled MySQL to MariaDB migration: handling privilege table differences when using mysqldump that described the steps to take for a migration like this:
This states that for a successful migration one must:
- use
--events --routineswhen usingmysqldump - use the
--forceoption of themysqlclient when loadingmysqldumpoutput - run
mysql_upgradeafter loading an SQL dump
I followed these instructions to alter the commands, ran them again and then started the mysql_upgrade command as specified. This time the migration did seem to work (no error but an odd message at the end) but it did not resolve the problem. It was still not possible to add users or grant rights and after a restart the MariaDB again no longer started.
Successful attempt 3: mysqldump --databases, import and manually add users and rights
Further searching online did not result in any other hints on how to migrate the databases through an export/import. Given that I had to finish the migration today and the number of databases was manageable, I decided to skip the migration of the metadata and users/rights and only export the data from the databases themselves. First I got the list of databases with
SHOW DATABASES;Next I executed the following command to dump all databases except the mysql system databases mysql and information_schema:
sudo /usr/local/mysql/bin/mysqldump -p --databases db1 db2 ... | gzip > hostname.mysql_data.20180331.dmp.gzon my MacOS Server (replacing db1 db2 ... with the actual database names) to dump the data, transferred the file again to the new server and ran the command:
zcat hostname.mysql_data.20180331.dmp.gz | sudo mysqlto import the data, which worked without any issues. Also a restart did not uncover any problems. The only drawback of this approach is that the users and the rights were not transferred. As in my case there were only about 10 databases, each with 1 – 2 user/host combinations, it was not really much work to re-create these users. First I obtained a list of user/host combinations on the MacOS Server that I still needed with:
SELECT CONCAT(user, '@', host) AS user FROM user;Fortunately all were application users so I was able to retrieve the passwords from the application settings. For each of the user/host combination I created an entry manually on the MariaDB server with:
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password'(substituting username, hostname and password for the actual values). Next I ran the following command for each user/host combination on the MacOS Server to retrieve the access rights that user had on MySQL:
SHOW GRANTS FOR 'username'@'hostname'(again substituting username and hostname for the actual values) and executed the GRANT commands this returned on the MariaDB side to configure the access right for each user.
Conclusion
Migration was successful though part of it had to be done manually. I have probably lost more time on trying to do this fully-automated then the time it took to re-create the users but alas, I only found out that the automatic migration was not working after I tried it. I am not quite sure why the migration was not successful, the hardware architecture etc. is all the same (two VMs running on the same Mac Mini running ESXi 6.5), so I can only conclude that although MariaDB is a drop-in replacement for MySQL (after migration I have not noticed any other problems – apart from my MySQL WorkBench complaining about an unsupported DB engine) the versions clearly weren’t and the migration got stuck on changes in the user/privileges schemas between the two product versions. Nevertheless a successful migration and one that can be replicated easily.
This post is part of a series on moving functionality removed in Fall 2018 from MacOS Server: