(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:
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:
1 |
portsnap fetch extract |
If the Ports Collection is already installed, make sure to update it:
1 |
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.
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:
1 2 |
cd /usr/ports/databases/mariadb101-server make config-recursive install distclean |
Then use the sysrc command to add the following line to /etc/rc.conf:
1 |
sysrc mysql_enable="YES" |
Start the MariaDB server:
1 |
service mysql-server start |
And create a password for the MariaDB server root user:
1 |
/usr/local/bin/mysqladmin -u root password 'your-password' |
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:
1 |
service mysql-server stop |
Uninstall the MySQL server and client:
1 2 3 4 5 |
cd /usr/ports/databases/mysql57-server make deinstall cd /usr/ports/databases/mysql57-client make deinstall |
Delete everything in the MySQL server data directory:
1 |
rm -r /var/db/mysql/* |
Then navigate to the MariaDB server port and build it:
1 2 |
cd /usr/ports/databases/mariadb101-server make conig-recursive install distclean |
Start the MariaDB server:
1 |
service mysql-server start |
Create a password for the MariaDB server root user:
1 |
mysqladmin -u root password 'your-password' |
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):
1 |
mysql_upgrade -u root -p |
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/
[…] Original: https://iceflatline.com/2015/07/replacing-mysql-with-mariadb-in-freebsd/ […]