Frozen Geek Technology Blog

Database migration from MySQL 5.1 to MariaDB 10 on OpenBSD

Michal

Recently I have been on somewhat of a quest to bring as many of my Virtual Machines and servers to a current version of the Operating System as possible, and one of those servers is a small OpenBSD Virtual Machine used as a Database server for various web services. The challenge I faced was how to migrate data from the fairly old version of MySQL (5.1) to the current release of MariaDB (10), in as simple a way as I can.

A brief background

As some of you may know, I have spent 15 months including the whole of 2014 at Halley Research Station in Antarctica, working for British Antarctic Survey. During that time, I was also running my own servers, one of which hosts my blog from my time over there. All that setup was put in place a few years before I went South, and so several of the VMs there were running old version of the OS. When I got to Halley, I realised that the network connection we had there was not enough to safely perform the OS upgrade, and so I left everything as it was.

Now I’m back to the Real World, getting ready for another stint. And while I’m here, I thought I really should upgrade my VMs to current versions of the OS.

I’ve been ploughing away with that for the last few weeks – some of that work has been described in my other article on setting up a mail server on OpenBSD.

The database server had slightly lower priority, as it’s not exposed to the Internet directly at all. It has been running OpenBSD 4.9 with MySQL 5.1 that came in packages. And now I wanted to bring it to a current release, 5.7.

I’m sure you know this already, but the sanctioned way of upgrading a system is to go from one release to the next without skipping. This means I would have to go 4.9 -> 5.0 -> 5.1 -> 5.2 -> 5.3 -> 5.4 -> 5.5 -> 5.6 -> 5.7. Eight distinct upgrade operations. In between, there have been quite a few changes, including a very significant one between 5.4 and 5.5 which dealt with Y2038 by changing the timestamp counter to a 64-bit time type, which resulted in the old kernel not being compatible with new binaries, and the old binaries not compatible with new kernel.

And while the good folk at OpenBSD did provide a migration process, it looked to be fairly involved, and required uninstalling all packages anyway.

Considering all the above, I’ve decided to try another way, which is to simply deploy a new VM and migrate my configs and the data across from the old one. This also gave me a bit of a safety net, in that if the migration didn’t work out as planned, I could always simply get back to using the old VM and think of another approach.

One other small concern I’ve had was that while doing the research on how easy would it be to migrate the MySQL data to MariaDB, pretty much all of the articles I found recommended going through the interim stage of using MySQL 5.5. The idea was to:

I really didn’t fancy going through all of this and I thought I’d try the direct migration from MySQL 5.1 to MariaDB 10, without the interim stage.

Note that my case was slightly different than the ones described in the articles I found, in that I wasn’t just upgrading the database engine on my server. I was effectively migrating the data only, from an old server running old software, to a new server, running current software.

The migration

A word of caution: Please note, the steps below did work for me, however they may not work for everybody. I’m no database expert, however I do think that the database I have is a fairly simple one. If your database is more complex, you may come across issues which make going through the interim step of MySQL 5.5 necessary, otherwise some or all of your data might get corrupted. So, use common sense, make backups, ensure you can restore them, ideally practice and test on a non-production server and hope for the best!

New server installation

I’m lucky enough that I have my own VMware server in a friendly data centre, and have no shortage of IP address space. Therefore, it was easy enough for me to spin up a new small VM and install OpenBSD 5.7 on it, while keeping the old database server still running and with the web server running the blog still using that old database server.

For the new server, I simply assigned it a different IP address for the installation and then the configuration stage, and I’ve also ran some tests to ensure things are working before I switched the new and the old database servers around.

I’ve gone through the usual for me OpenBSD installation, where I didn’t install any of the X11 packages nor games. I’ve included all other base system packages during the installation.

Once OpenBSD was installed, I’ve rebooted the server and installed the following extra packages from the OpenBSD mirror:

Out of the list above, MariaDB client and server packages are the only real requirements. I’ve been using bash as my shell for donkeys, and old habits die hard, so there you go. Same goes for vim.

Next task was to patch the server to include all bug fixes since the 5.7 was released. For a good guide on that I refer you to the OpenBSD errata page. As I had all patched binaries already pre-built on another server, it was simply a case of running make install for me, without having to go through the source patching and compilation.

Server configuration

Next goes the server configuration. I’ve pretty much moved the /etc/pf.conf file, replicated the interface configuration, taking into consideration the fact that now my interface names have changed from vic0 to vmx0 (and vic1 to vmx1). I’ve also made sure my NTP and DNS settings are the same, and that my new server has required SSH public keys added to the authorized_keys files.

The next step was to initialise and configure MariaDB. To do that, I ran the following commands (note, all output was removed):


db1:~$ sudo mysql_install_db # to initialise the new database
db1:~$ sudo rcctl enable mysqld # to make MariaDB start at boot
db1:~$ sudo rcctl start mysqld # to start MariaDB now
db1:~$ sudo mysql_secure_installation # to harden the default settings a bit by setting a new MySQL root user password, removing the guest user and example database

One more thing that you may need to do is to edit the /etc/my.cnf and configure MariaDB to bind to a network interface, which by default it doesn’t. To do that, in /etc/my.cnf add a line in the [mysqld] section like below, and restart MariaDB with sudo rcctl restart mysqld:


# [...]
# Here follows entries for some specific programs
 
# The MariaDB server
[mysqld]
bind-address = 0.0.0.0
port = 3306
socket = /var/run/mysql/mysql.sock
skip-external-locking
# [...]

Export the data on the old database server

Before I could start with this step, I wanted to make sure my database will not change during the migration. As I knew I only had a single web server accessing the database, I simply scheduled a down time for the web server application on that box. This is important to do before you export any data, as otherwise you might miss database updates between now and when the server comes back up. Again, I was lucky in that I could afford the down time. Otherwise you’d need to figure out how to switch MySQL to read-only mode or similar.

The following step is to export the whole database on the old server. Having read a helpful article about a similar thing on the MariaDB web site, which describes how to handle differences between MySQL and MariaDB during the data migration, I’ve ran the following command on the old server:

db1-old:~$ sudo mysqldump -u root -p -A --events --routines > mysqldump.sql

This took a short couple of seconds, after which I was able to scp the dump file across to the new server.

Import the data on the new server

The last step is to import the data into MariaDB on the new server. Again, referring to the article on the MariaDB site above, I ran the following:

db1:~$ cat mysqldump.sql | mysql -u root -p --force
db1:~$ sudo mysql_upgrade -u root -p

You’ll notice I’ve called mysql_upgrade with sudo – this is so I could avoid an error that gets thrown at the end of the mysql_upgrade process if it runs with a regular user privileges:

Could not create the upgrade info file '/var/mysql/mysql_upgrade_info' in the MySQL Servers datadir, errno: 13

and which is caused by lack of permissions to write to /var/mysql. If you’re happy not getting this file, or if you execute mysql_upgrade as user _mysql, things should work fine.

Final word

Now is the time to test and make sure your data migrated happily and fully, which I’ll leave up to you to do, as the tests would have to depend on what and how much of data you have in the database.

When I was happy everything is working as planned, I then shut down my old database server and restarted the new one, so it came up with new IP and IPv6 address settings, and so I could verify that the database came up as well.

I then brought my web server application back up and checked the content of my website, which uses data from the database, was as expected. Finally I’ve made a small modification to the web site which I knew would be propagated to the database if things were running correctly, and verified that it indeed was the case.

And this concludes the migration, which all in took no more than about and hour and a half, including the OS installation!

Exit mobile version