Convert MySQL 5.7 to MariaDB 10.1

I recently provisioned a Digital Ocean droplet via Laravel Forge prior to Forge offering the option to provision with MariaDB 10.1. The new droplets provisioned by Forge come with MySQL 5.7 which should be a good thing given a promised, but contested, 3x perofrmance boost over previous versions of MySQL. I turned performance_schema off and happily went about my business until I noticed the server was using an unusually large amount of memory for what it does. Checking top -u mysql, I noted that MySQL was sitting at 26.3% which roughly translates into 526MB for a database that was idle (no connections) at the time. In a previous post, I had noted how disabling performance schema could be a quick fix for users on a low memory virtual private server. Performance schema wasn’t enabled by default until MySQL 5.6 so it was likely that only power users would actually need it. On this 5.7.11 branch, it seemed to have no effect.

After a bunch of Googling and pouring over the MySQL manual, there wasn’t an obvious reason for this level of memory usage. I’m not a MySQL tuning expert and I don’t have the time to fully get to the root cause of the issue. I was seriously considering downgrading to 5.6 when it hit me—this would be a great excuse to try MariaDB. MariaDB is a binary drop-in replacement for MySQL. This means that, in theory, you should just be able to add the keys, package, install it and, voila, no more MySQL. That’s not exactly the case—especially with a server you have provisioned with Forge. The good news is you found this post and, hopefully, it will save you some research. Here are the steps I took.

  1. Backup your MySQL database(s) if needed
  2. Remove your current MySQL installation
    sudo apt-get remove --purge mysql-server mysql-client mysql-common
    sudo apt-get autoremove
    sudo apt-get autoclean
    
  3. Remove everything from /var/lib/mysql EXCEPT the keys:
    • ca-key.pem, ca.pem, client-cert.pem, client-key.pem, private_key.pem, public_key.pem, server-cert.pem, server-key.pem
  4. Backup your my.cnf (just in case—you probably won’t need it unless you have made a number of changes and you want to refer to it post-installation)
    sudo mv /etc/mysql/my.cnf /etc/mysql/my.cnf.old
    
  5. Install MariaDB
    // Import the Key and Repository for MariaDB on Ubuntu 14.04
    sudo apt-get update
    sudo apt-get install software-properties-common
    sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
    sudo add-apt-repository 'deb [arch=amd64,i386] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu trusty main'
    // Install MariaDB
    // Note: During the installation process you will be prompted for a new password for root. Generate a strong password, enter and confirm it.
    sudo apt-get install mariadb-server
    
  6. Login to MySQL to create your forge user and your database(s)
    mysql -uroot -p
    mysql> create user 'forge' identified by 'yourforgedatabasepassword';
    mysql> grant all privileges on *.* to 'forge';
    // Note: I kept my database password rather than changing it. Do whatever makes you most comfortable.
    

After installing, I ran artisan migrate to confirm all was well, but this was unnecessary as I use the excellent Laravel Driver for the Database Backup Manager. After the migration completed, I ran an artisan db:restore and was back up and running. If you connect remotely over SSH, your client should connect just as it did before.

Last, but not least, I edited the new /etc/mysql/my.cnf to disable performance_schema. However, it turns out that MariaDB disables performance schema by default as did versions of MySQL prior to 5.6. In any case, memory usage has been pegged at 7.3% after nearly 24 hours. Looks like it’s ready for production.

Have fun, but proceed at your own risk. These procedures worked perfectly for my specific scenario, but may not work as well for you.

2 Comments

  • mysql -uroot -p
    Enter password: ¿?

    • sirobe: when you install Maria, you will be prompted to create a root password. Use that password to login.

Leave a Comment

Your email address will not be published. Required fields are marked *