MariaDB is a fork of the wildly popular open source database MySQL. Although MariaDB is very similar to MySQL, it is not necessarily the same. One of the primary goals behind the MariaDB project is to serve as a drop-in replacement for MySQL, but MariaDB also offers features beyond those available in MySQL.
For example, MariaDB 5.5 is intended to be a replacement for MySQL 5.5. However, MariaDB 10 is a departure from the version 5.x tree and represents a new direction for MariaDB. If you are replacing MySQL, you're probably better off using a 5.x version. Otherwise, start fresh with version 10.x.
To those uninitiated in how databases work, setting up MariaDB for the first time can be somewhat daunting. Nevertheless, with a few pointers, you can quickly get a new MariaDB instance up and running, ready to work with your application.
For the purposes of this guide, we’ll assume that the reader has little or no experience with MariaDB or MySQL on Linux, and we’ll concentrate on getting MariaDB installed and configured to the point where an application can be connected to the database and begin operation. More advanced elements of MariaDB, such as database programming and use of the SQL language itself, are beyond the scope of this article.
Installing MariaDB
First things first, we need to get MariaDB installed on our system. Assuming that we have a clean installation of Ubuntu Server, Fedora, or CentOS, we simply use the package management tools to pull down the required packages and install them.
Note that we may need extra packages aside from the main MariaDB code in order to make our application function. For instance, if we’re going to use a PHP-based application with MariaDB, we’ll need to install the PHP MySQL packages that allow PHP to talk to MariaDB servers.
First, however, we should check to see if MySQL was installed during the OS installation. On Fedora and CentOS we would run this command as root:
# rpm –qa | grep mysql
On Ubuntu, we would run the following:
$ sudo dpkg –l | grep mysql
If the mysql-server package is listed, then it’s already installed. If that's the case, we will need to remove it before we can install MariaDB; otherwise, expect conflicts between the two. If you are absolutely certain that the MySQL installation is not being used on this system, you can remove it using the following commands on CentOS or Fedora:
# yum remove mysql-server mysql
If you get errors about dependencies, you may have other packages installed that rely on these packages. Pull up a list of those packages and add them to the command line:
# rpm –qa | grep mysqlmysql-5.0.77-3.el5mysql-server-5.0.77-3.el5php-mysql-5.1.6-20.el5_2.1
# yum remove mysql-server mysql php-mysql
On Ubuntu, you can check if MySQL is installed with the dpkg command:
$ sudo dpkg --list | grep mysql
ii libdbd-mysql-perl 4.020-1build2 Perl5 database interface to the MySQL database
ii libmysqlclient18 5.5.24-0ubuntu0.12.04.1 MySQL database client library
ii mysql-client-5.5 5.5.24-0ubuntu0.12.04.1 MySQL database client binaries
ii mysql-client-core-5.5 5.5.24-0ubuntu0.12.04.1 MySQL database core client binaries
ii mysql-common 5.5.24-0ubuntu0.12.04.1 MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.5.24-0ubuntu0.12.04.1 MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.5 5.5.24-0ubuntu0.12.04.1 MySQL database server binaries and system database setup
ii mysql-server-core-5.5 5.5.24-0ubuntu0.12.04.1 MySQL database server binaries
ii php5-mysql 5.3.10-1ubuntu3.2 MySQL module for php5
If you see output similar to the above, you will need to uninstall all MySQL packages. You can do that with apt-get:
# sudo apt-get remove --purge mysql-*
Note that this command may uninstall additional packages, so you may want to specify packages individually rather than use the wild card:
# sudo apt-get install mysql-server mysql-client mysql-common mysql-client-core-5.5 mysql-server-core-5.5
Now that we’re sure we have a system free of MySQL, we can install MariaDB. To do this, we may need to specify a new repository -- most Linux distributions do not yet include MariaDB by default. To do this, we can use the MariaDB repository generator.
Install MariaDB on Fedora or CentOS
If you’re using Fedora 20 or CentOS 7, then MariaDB 5.5 can be installed without adding new repositories. You merely need to run the following:
# yum install mariadb-server mariadb php-mysql
For older releases or if you want to run MariaDB 10, you must use the MariaDB repository creator at the URL above. At that page, select your distribution, your distribution release, then MariaDB 5.5 or MariaDB 10. If this is a clean test environment, you should select MariaDB 10. This will generate a Yum source for Fedora or CentOS like the one below. (Do not use this sample, as it may have changed. Generate a new one from the MariaDB website based on the version and build of your distro.)
# MariaDB 10.0 CentOS repository list - created 2014-06-10 19:36 UTC# http://mariadb.org/mariadb/repositories/[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.0/centos6-amd64gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck = 1
Open a new file as root, paste that data into the file, and save the file:
# nano /etc/yum.repos.d/mariadb.repo
On Fedora and CentOS, we’ll use Yum to install the database as the root user:
# yum install MariaDB-server MariaDB-client MariaDB-compat MariaDB-shared php-mysql
This will install the MariaDB server, client, and required packages and libraries, as well as the PHP MySQL (MariaDB) extensions.
Install MariaDB on Ubuntu
Use the MariaDB repository generator to generate a set of instructions and a source string. The instructions will look something like those below, but will be based on your choice of release and MariaDB version. (Do not use this sample, as it may have changed. Generate a new one from the MariaDB website based on the version and build of your Linux distribution.)
$ sudo apt-get install python-software-properties$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db$ sudo add-apt-repository 'deb http://ftp.osuosl.org/pub/mariadb/repo/10.0/ubuntu precise main'
You can then install MariaDB:
$ sudo apt-get install mariadb-server php5-mysql
This will install the MariaDB server and the PHP MySQL (MariaDB) extensions. In addition, the installer will prompt for the MariaDB root user password and start the server before completing the install.
Starting the MariaDB server
For CentOS and Fedora, we now need to start the server and set the MariaDB root user password like so:
$ su –# service mysql start# mysqladmin password newpassword
Note that newpassword should be replaced with your actual password. Note that MariaDB defaults to using mysql as the service name, rather than mysqld, which is used by the standard MySQL packages.
At this point, we should have a functional MariaDB server instance. We can test that by logging into the server with the mysql client:
$ mysql –u root –p
This will result in a prompt for the MariaDB root user password you previously configured. Enter the password, and you will be presented with a MariaDB [(none)]> prompt. At this point, we’re logged into our new instance and ready to configure it. You can log out of the MariaDB server by typing quit at the MariaDB [(none)]> prompt.
Baseline MariaDB configuration
For most small applications, MariaDB won’t require many configuration changes. Depending on the distribution, your default configuration file for the server may be in one of a few different locations. On Fedora and CentOS, you’ll find the file /etc/my.cnf and a directory named /etc/my.cnf.d/ that contains included configuration files such as server.cnf.
On Ubuntu, you’ll find the config file /etc/mysql/conf.d/mariadb.cnf.
Parameters in the config files can be adjusted as needed, but these will be dictated by the requirements of the application using MariaDB. If alterations are needed, they should be listed in the application’s installation manual. However, many applications require a database to be created manually, as well as a username and password to be assigned to the database, before the application can be installed. We accomplish that from the Ubuntu, Fedora, or CentOS command line as follows:
$ mysql –u root –p
We then enter the root password and log in to the instance.
It’s important to note that MariaDB has a client called mysql and uses the name mysql in a number of other areas. This helps maintain compatibility with applications and frameworks that have been developed for MySQL and therefore have dependencies on files and executables with those names. From here on out, though you may be typing mysql commands, you’re using MariaDB.
Now we need to create the database.
mysql> CREATE DATABASE `mydatabase`;
Note that those marks around mydatabase are backticks, not quote marks. This command will create the database called mydatabase.
Now, we need to assign privileges. We can assign a restricted set of privileges to a user, but most applications will need full access, so we can start by adding a username with full privileges.
mysql> GRANT ALL PRIVILEGES on mydatabase.* TO myuser@localhost IDENTIFIED BY ‘mypassword’;
Note that mypassword is surrounded by single-quote marks.
The above command will grant all privileges to the user named myuser, with the password mypassword, but will only allow connections with that username/password if they originate from the local system. If we have another server that needs to communicate with this MariaDB instance, then we must specify the IP address or hostname, like so:
mysql> GRANT ALL PRIVILEGES on mydatabase.* TO myuser@192.168.100.5 IDENTIFIED BY ‘mypassword’;
This command will allow a system with the IP address 192.168.100.5 to connect with the username myuser and password mypassword.
We can test this new user and connection with the MariaDB client. If we’re testing from the server that MariaDB is running on, we’d use this command line:
$ mysql –u myuser –p
If we’re testing from another system, we need to specify the server to connect to:
$ mysql –u myuser –h <server IP address or name> -p
Insert the actual IP address or hostname of the MariaDB server after the –h flag in that command line.
If all is well, we've now logged right in to the instance and we’re ready to install our application.
Many LAMP applications have specific MariaDB installers and instructions. Some of them have a small script that needs to be run from the command line to create the database schema prior to launching the application, while others tackle this through a Web-based installer. Read the installation instructions for your application to determine how to do the initial MariaDB setup prior to use.
Managing MariaDB with phpMyAdmin
Once the application is installed and tested, you will want to keep tabs on the database and perhaps move into more advanced database management. For many implementations, the use of Web-based tools can simplify the management of the database for those not well versed in MariaDB administration.
One of these tools is phpMyAdmin, for PHP-based MariaDB administration. It provides a no-fuss Web GUI for performing nearly all MariaDB administrative tasks. Installing it will make everyone’s life easier.
To do this on Fedora and CentOS, we run this command as root:
# yum install phpmyadmin
On Ubuntu, we run the following:
$ sudo apt-get install phpmyadmin
Ubuntu will prompt for the Web server in use and perform the configuration automatically. In most cases you’ll select Apache, unless you are using lighttpd or another Web server. In addition, Ubuntu will prompt you to choose manual or automatic configuration of phpMyAdmin. Unless you’re comfortable doing this manually, you can safely choose the automatic configuration, which will ask for the MariaDB root password. Following this, Ubuntu will configure everything and restart the servers.
However, depending on how the server was initially built, you may also need to install the Apache PHP module. If that's the case, then:
$ sudo apt-get install libapache2-mod-php5
On Fedora and CentOS, we need to make a few minor changes to access phpMyAdmin from remote systems. We’ll use Nano to edit the phpMyAdmin configuration file:
# nano /etc/httpd/conf.d/phpmyadmin.conf
When the file opens, add a line below “Allow from 127.0.0.1” to include the IP addresses or ranges to which you want to allow access. For example, “Allow from 192.168.1” will permit everything in the 192.168.1.0/24 subnet to connect. You can limit connections to a specific IP address, or you can add multiple lines to allow multiple IPs or subnets.
Once you’ve added those lines, save the file with Ctrl-O and exit with Ctrl-X. Now restart Apache:
# service httpd restart
On Fedora and CentOS, we also need to insert a Blowfish secret into the phpMyAdmin configuration. We can do that with Nano too:
# nano /usr/share/phpMyAdmin/config.inc.php
(Note: You may find only a config.sample.inc.php file in the phpMyAdmin directory. If so, make a copy named config.inc.php. Also, the path may be /usr/share/phpmyadmin on some versions.)
Locate the following line:
$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
Enter a secret between the quotes, like this:
$cfg['blowfish_secret'] = 'SDJkjshdkfjhsdf9**&^%^&%'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
