How-to: Get started with MariaDB

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 mysql mysql-5.0.77-3.el5 mysql-server-5.0.77-3.el5 php-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 = MariaDB baseurl = http://yum.mariadb.org/10.0/centos6-amd64 gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck = 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! */

The secret can be anything, preferably a random string. You will not need to use this secret anywhere else; it’s an internal requirement. Save the file with Ctrl-O and exit with Ctrl-X.

Now we can log in to phpMyAdmin by pointing a Web browser at the server URL:

http://<server IP address>/phpmyadmin

Connecting to phpMyAdmin

When you connect to phpMyAdmin, you will be presented with a login dialog. By default, phpMyAdmin will connect to the localhost MariaDB instance, not a remote instance, so this username and password must be either the root user or a user with sufficient privileges as noted above.

Once you’ve logged in, you should see a list of databases on the left, along with configuration and management options on the right. Clicking on a database will allow you to browse through the database contents, add and delete tables and data, import and export data and database schemas, and perform various other administrative tasks. Be careful that you don’t perform actions that you are unsure about, such as dropping (deleting) a database.

There are a few tips you should know about phpMyAdmin to get started. First is a method of backing up or exporting a database to a file on your local system. To do this, click the database name in the list on the left, then click the Export tab at the top. In most cases you won’t need to change any of the options, but simply click the box next to the “Save as file” option near the bottom, select a compression type if desired, then click Go. This will cause the entire database to be exported, possibly compressed, and downloaded to your computer through the browser.

Another handy tip is the converse operation, which will import data from a backup into a new database. First, create the new database by clicking on the Home icon at the top left, then click Databases. Enter a name for the new database in the text field, select a collation if you know it, and click Create. The new database will appear in the list on the left.

Now, click the name of the new database, and click Import at the top. Click to browse for the backup file and select it, then click Go. This will automatically import the backup into the new database.

You can also create and manage database users with phpMyAdmin. Click the Home icon in the upper left and select Privileges. You can add a user here by filling out the form and specifying the username and password. If you want the user to have global privileges, you can select them here, but if this is a user for one specific database, leave those check boxes blank. Once the user has been created, click Privileges again, and click the Edit icon to the right of that username.

Next, select a database under “Database-specific privileges” and grant that user whatever rights are necessary. You can use the Check All link to allow that user all privileges on the database, or you can get more granular if necessary.

PhpMyAdmin also offers views of the server status. Click the Home icon in the upper left, click Processes to see active MariaDB connections and processes, and click Status to see an exhaustive list of server traffic, query, and variable statistics. PhpMyAdmin will flag certain statistics in red if they’re potentially problematic, such as if the number of open tables is too large.

Next to each variable is a brief description of the variable and sometimes a suggestion of what might be causing a problem, such as the possibility that the table cache value is too small. This is a great way to learn about the MariaDB server settings and what they mean. All adjustments to these variables should be done in the my.cnf or server.cnf file, though some variables can be set while the server is running.

There are many more aspects to proper MariaDB operation, tuning, management, and administration, but this guide should succeed in taking you from a stock OS installation to a fully functional MariaDB server with an administrative Web-based GUI. You will likely want to peruse the MariaDB reference manuals for more information on the care and feeding of your MariaDB database server.