BSD

Replacing MySQL with MariaDB in FreeBSD

(20170113 — The steps in this post were amended to address changes in recent versions of software. Minor editorial corrections were also made — iceflatline)

In my post on how to install and configure Apache, MySQL, PHP and phpMyAdmin on FreeBSD for basic local web development activities, one of the components is the MySQL database server. But what if you prefer to use MariaDB? MariaDB is an open source alternative to MySQL, and available under the terms of the GNU GPL v2 license. It is developed by the MariaDB community with oversight by the MariaDB Foundation.

This post will describe how to install and configure the MariaDB 10.1 server, as well as how to configure it as a replacement for a MySQL 5.7 server. I strongly encourage you to test these steps first before using them on your development or production environment.

The versions of software discussed in this post are as follows:

  • FreeBSD 11.0-RELEASE
  • mysql57-server-5.7.17
  • mariadb101-server-10.1.20_1
  • The following steps discussed in this post assume you have the FreeBSD Ports Collection installed. If not, you can install it using the following command:

    If the Ports Collection is already installed, make sure to update it:

    Okay, let’s get started. All commands are issued as the user root. While building the various ports you should accept all default configuration options unless otherwise instructed.

    Install the MariaDB server

    If you’re installing the MariaDB server for the first time on a FreeBSD system that does not already contain a version of MySQL server use the following steps.

    Navigate to the MariaDB server port and build it:

    Then use the sysrc command to add the following line to /etc/rc.conf:

    Start the MariaDB server:

    And create a password for the MariaDB server root user:

    That’s it. Now you should be able to use the MariaDB server in the same way you would a MySQL server.

    Replacing MySQL server with MariaDB server

    If you’ve previously installed a MySQL server then you can replace it with a MariaDB server. First, make sure to backup any existing database(s). This is critical. MariaDB 10.1 is not a drop-in replacement for MySQL 5.7. Installing MariaDB requires you to destroy your existing databases and restore them after MariaDB is installed.

    Stop the MySQL server:

    Uninstall the MySQL server and client:

    Delete everything in the MySQL server data directory:

    Then navigate to the MariaDB server port and build it:

    Start the MariaDB server:

    Create a password for the MariaDB server root user:

    Recreate your database(s) in the MariaDB server and restore their files from your backups. Then run the command mysql_upgrade. This command does two things: it ensures that your mysql privilege and event tables are updated with the new fields MariaDB uses; and it performs a check of all tables and marks them as compatible with MariaDB server. In most cases this should be a fast operation (depending on the number of database tables):

    Conclusion

    That’s it. A few minutes of your time with the FreeBSD Ports Collection and you can quickly install a MariaDB server from scratch or replace an existing MySQL server with it.

    References

    https://mariadb.com/kb/en/
    https://mariadb.com/kb/en/mariadb/what-is-mariadb-101/

    One comment Replacing MySQL with MariaDB in FreeBSD

    Leave a Reply

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