In this tutorial, user iceflatline shows us how to replace MySQL with MariaDB in FreeBSD.

Original: https://www.iceflatline.com/2015/07/replacing-mysql-with-mariadb-in-freebsd/

MariaDB

In my post on how to install and configure Apache, MySQL, PHP and phpMyAdmin on FreeBSD (FAMP) 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 GPL v2 license. It is developed by the MariaDB community with oversight by the MariaDB Foundation. For all practical purposes MariaDB is a drop-in replacement for the same MySQL version. All commands, interfaces, libraries and APIs that exist in MySQL also exist in MariaDB. For example, MySQL 5.1 and MariaDB 5.1 are compatible, as are MySQL 5.5 and MariaDB 5.5. MariaDB 10.0 is the drop-in replacement for MySQL 5.6, and can also replace MySQL 5.5.

This post will use the aforementioned post on how to install and configure Apache, MySQL, PHP and phpMyAdmin on FreeBSD as an example implementation and demonstrate how to install and configure MariaDB as a replacement for MySQL. I strongly encourage you to test these steps first before using them on your development or production environment. At the very least you should backup your database(s).

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

  • FreeBSD 10.1-RELEASE (running as a guest OS under VMWare ESXi hypervisor 6.0.0
  • apache24 2.4.12
  • mysql56-server 5.6.24
  • mariadb55-server 5.5.43
  • mariadb100-server 10.0.17
  • mod_php56 5.6.11
  • php56 5.6.10
  • php56-extensions 1.0

 

The following steps discussed in this post assume you have the FreeBSD Ports Collection installed. If not, you can install it using the following commands:

portsnap fetch
portsnap extract

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

portsnap fetch update

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.

Building FAMP with MariaDB

To add MariaDB 10.0 when building a new FAMP implementation, use the following commands:

cd /usr/ports/databases/mariadb100-server
make install clean

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

echo 'mysql_enable="YES"' >> /etc/rc.conf

Start the MariaDB server:

service mysql-server start

Then create a password for the MySQL root user:

/usr/local/bin/mysqladmin -u root password 'your-password'

If you’d like to add MariaDB 5.5 instead of MariaDB 10.0 when building a new FAMP implementation, use the following commands:

cd /usr/ports/databases/mariadb55-server
make install clean

Then use the commands described previously to add MariaDB to /etc/rc.conf, start the server, and create the root password.

Replacing MySQL with MariaDB in an existing FAMP implementation

If you’ve previously built FAMP using the MySQL 5.6 server, then you can replace it with MariaDB 10.0. First, make sure to backup your database(s), then uninstall the MySQL 5.6 server and client:

cd /usr/ports/databases/mysql56-server
make deinstall clean
cd /usr/ports/databases/mysql56-client
make deinstall clean

Install MariaDB 10.0:

cd /usr/ports/databases/mariadb100-server
make install clean

Then finish by running 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 10.0. In most cases this should be a fast operation (depending on the number of database tables):

mysql_upgrade -u root -p

If you’re currently using MySQL 5.5, then you can replace it with either MariaDB 5.5 or MariaDB 10.0. To replace it with MariaDB 5.5:

cd /usr/ports/databases/mysql55-server
make deinstall clean
cd /usr/ports/databases/mysql55-client
make deinstall clean
cd /usr/ports/databases/mariadb55-server
make install clean

While not strictly necessary in this case, you should also run mysql_upgrade. If calling mysql_upgrade was not necessary, it does nothing.

Conclusion

Well, that’s it. A few minutes of your time with the FreeBSD Ports Collection and you can quickly replace MySQL with the open source alternative MariaDB on your FAMP implementation.

References

https://mariadb.com/kb/en/
https://mariadb.com/kb/en/mariadb/what-is-mariadb-55/
https://mariadb.com/kb/en/mariadb/what-is-mariadb-100/