Install MariaDB 10.0 on CentOS 6

MariaDB is a community-developed fork of MySQL and aims to be an enhanced, drop-in replacement for it. It is GNU GPL licensed, and led by the original developers of MySQL like Monty Widenius. MariaDB seems active for now, but they do have much less market share than MySQL. Because of the big momentum MySQL has had, there is still a lot of community around it but there is a clear trend that most new activities in the open source world revolve around MariaDB and most Linux distributions ship with MariaDB as the MySQL compatible database now. The following tutorial will guide you through the steps to install MariaDB 10.0 on CentOS 6. CentOS 6 is too old? Yes, that true, but CentOS 6 is still supported until 2020 with security updates and there are still many servers out there that use it but might need a newer MySQL compatible database, e.g. to host the latest CMS systems.

Prerequisites

These instructions are intended for installing MariaDB 10.0 on a single CentOS 6 node (without MySQL already installed).
As the default MariaDB installation uses the /var/lib/mysql directory to store your databases, keep in mind that the partition or logical volume associated with /var needs adequate space.
Configuration File Backup (Replacement Only): Likewise, it is recommended that you make a copy of your current MySQL/MariaDB configuration file if you are replacing a current installation; this file can be found at /etc/mysql/my.cnf on Debian/Ubuntu systems and /etc/my.cnf on CentOS systems. On CentOS, the MariaDB package will remove the configuration file when uninstalled, so this step is especially important on these systems.
The rest of this tutorial will assume you are connected to your server with the root user account or a user account with sudo privileges. To enter the root shell from another account, run:

sudo su

Step 1. Setting up the MariaDB Repository

You should ensure that the available packages are up to date. For that, simply run the following command in terminal:

yum -y update

Loaded plugins: fastestmirror
base | 3.6 kB 00:00
extras | 3.4 kB 00:00
updates | 3.4 kB 00:00
(1/4): base/7/x86_64/group_gz | 155 kB 00:00
(2/4): extras/7/x86_64/primary_db | 101 kB 00:00
(3/4): updates/7/x86_64/primary_db | 3.1 MB 00:00
(4/4): base/7/x86_64/primary_db | 5.3 MB 00:04
Determining fastest mirrors
 * base: mirror.net.cen.ct.gov
 * extras: mirror.es.its.nyu.edu
 * updates: mirror.atlanticmetro.net
Resolving Dependencies
--> Running transaction check
---> Package openssl.x86_64 1:1.0.1e-51.el7_2.2 will be updated
---> Package openssl.x86_64 1:1.0.1e-51.el7_2.4 will be an update
---> Package openssl-libs.x86_64 1:1.0.1e-51.el7_2.2 will be updated
---> Package openssl-libs.x86_64 1:1.0.1e-51.el7_2.4 will be an update
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
 Package Arch Version Repository Size
================================================================================
Updating:
 openssl x86_64 1:1.0.1e-51.el7_2.4 updates 711 k
 openssl-libs x86_64 1:1.0.1e-51.el7_2.4 updates 951 k
Transaction Summary
================================================================================
Upgrade 2 Packages
Total download size: 1.6 M
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
(1/2): openssl-1.0.1e-51.el7_2.4.x86_64.rpm | 711 kB 00:00
(2/2): openssl-libs-1.0.1e-51.el7_2.4.x86_64.rpm | 951 kB 00:00
--------------------------------------------------------------------------------
Total 2.3 MB/s | 1.6 MB 00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Updating : 1:openssl-libs-1.0.1e-51.el7_2.4.x86_64 1/4
 Updating : 1:openssl-1.0.1e-51.el7_2.4.x86_64 2/4
 Cleanup : 1:openssl-1.0.1e-51.el7_2.2.x86_64 3/4
 Cleanup : 1:openssl-libs-1.0.1e-51.el7_2.2.x86_64 4/4
 Verifying : 1:openssl-libs-1.0.1e-51.el7_2.4.x86_64 1/4
 Verifying : 1:openssl-1.0.1e-51.el7_2.4.x86_64 2/4
 Verifying : 1:openssl-1.0.1e-51.el7_2.2.x86_64 3/4
 Verifying : 1:openssl-libs-1.0.1e-51.el7_2.2.x86_64 4/4
Updated:
 openssl.x86_64 1:1.0.1e-51.el7_2.4 openssl-libs.x86_64 1:1.0.1e-51.el7_2.4
Complete!

Now, add the MariaDB CentOS repository.

Create a new MariaDB repository file /etc/yum.repos.d/mariadb.repo with nano:

nano /etc/yum.repos.d/MariaDB.repo

And paste the following text for CentOS-6 32 Bit:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Or paste this text for CentOS-6 64 Bit:

name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Then exit and save the file by pressing Ctrl+X, then Y.

As you see in the above example, it includes a gpgkey line to automatically fetch the GPG key that MariaDB uses to sign the repositories. This key is needed to verify the integrity of the package downloads.

Note: To find which repo you should use with the MariaDB repository generator.

Step 2 – Installing MariaDB 10 with Yum

Removing MySQL (only when it is installed)

I recommend to completely remove any old MySQL server versions that might be installed on the system and then upgrade to MariaDB without conflicts.

To check whether MySQL is already installed, execute the command:

rpm -qa 'mysql*'

If necessary, you can remove found MySQL packages before installing MariaDB by using the following commands:

yum list installed | grep mysql

yum remove mysql-client mysql-server mysql-common mysql-devel

And remove the MySQL databases:

rm -Rf /var/lib/mysql

Install MariaDB 10

For a standard server installation, you will need to download and install at least the client, shared, and server RPM files.

yum install MariaDB-server MariaDB-client -y

You should see output such as the following:

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.net.cen.ct.gov
 * extras: mirror.es.its.nyu.edu
 * updates: mirror.atlanticmetro.net
Resolving Dependencies
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.44-2.el7.centos will be installed
--> Processing Dependency: perl(Sys::Hostname) for package: 1:mariadb-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(IPC::Open3) for package: 1:mariadb-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(Getopt::Long) for package: 1:mariadb-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(File::Temp) for package: 1:mariadb-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(Fcntl) for package: 1:mariadb-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(Exporter) for package: 1:mariadb-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: /usr/bin/perl for package: 1:mariadb-5.5.44-2.el7.centos.x86_64
---> Package mariadb-server.x86_64 1:5.5.44-2.el7.centos will be installed
--> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(File::Path) for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.4)(64bit) for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.1)(64bit) for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
--> Processing Dependency: libaio.so.1()(64bit) for package: 1:mariadb-server-5.5.44-2.el7.centos.x86_64
[...]
Installed:
 mariadb.x86_64 1:5.5.44-2.el7.centos mariadb-server.x86_64 1:5.5.44-2.el7.centos
Dependency Installed:
 libaio.x86_64 0:0.3.109-13.el7 perl.x86_64 4:5.16.3-286.el7
 perl-Carp.noarch 0:1.26-244.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7
 perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7
 perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7
 perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7
 perl-Filter.x86_64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-2.el7
 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7
 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PathTools.x86_64 0:3.40-5.el7
 perl-PlRPC.noarch 0:0.2020-14.el7 perl-Pod-Escapes.noarch 1:1.04-286.el7
 perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7
 perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7
 perl-Socket.x86_64 0:2.010-3.el7 perl-Storable.x86_64 0:2.45-3.el7
 perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7
 perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7
 perl-libs.x86_64 4:5.16.3-286.el7 perl-macros.x86_64 4:5.16.3-286.el7
 perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7
 perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7
Complete!

When the installation process is completed, you can start MariaDB. Please note that the MariaDB service sis named "mysql", so the command below is not a typo.

service mysql start

Starting MySQL. SUCCESS!

Run this command to start MariaDB on every boot.

chkconfig mysql on

You can check the version of the MariaDB installation with the following command:

mysql -V

mysql Ver 15.1 Distrib 10.0.24-MariaDB, for Linux (x86_64) using readline 5.1

So far so good!

Step 3 – Securing MariaDB

By default, MariaDB is not hardened. You can secure MariaDB using the mysql_secure_installation script. You should read the steps below carefully, they will set the root password, remove anonymous users, disallow remote root login, and remove the test database and access to secure MariaDB. It is recommended that all administrators run this script to make sure your MySQL installation is secure. Unfortunately, the script is interactive and prompts for the root password, so it can't easily be included in the build process. It is recommended that you answer yes to these options. You can read more about the script in the MariaDB Knowledge Base.

Invoke mysql_secure_installation without arguments:

mysql_secure_installation

This will start a procedure that works on the question-answer method and you will be able to apply some customizations on your MariaDB installation. Below is an example of what will happen and some of the recommended options you should use.

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
 SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, then the default password will be blank, so you should just press enter here.

Enter current password for root (enter for none):  
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.

Set root password? [Y/n] y
New password: ENTER YOUR PASSWORD
Re-enter new password: REPEAT YOUR PASSWORD
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove the anonymous login before moving into a production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password over the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended for testing only and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB installation should now be secure.
Thanks for using MariaDB!

Now, you have to restart MariaDB.

service mysql restart

Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

That’s it. Login to the MariaDB server and check for the old databases and tables (if you upgraded from MySQL).

To connect to MariaDB, run the given below command.

mysql -u root -p

In this case, I’ve specified the user root with the -u flag, and then used the -p flag so MySQL prompts for a password.

When asked, you just enter the password you assigned with the mysql_secure_installation script.

You’ll then be presented with a welcome header and the MariaDB prompt.

For a list of MariaDB commands, type help or \h at the promt:

General information about MariaDB can be found at
http://mariadb.org
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'

To view a list of the current databases that you have created, use the following command:

SHOW DATABASES;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)

The "information_schema", "performance_schema", and "mysql" databases are set up by default and contain system data of the MariaDB server, these tables should be left alone unless you know what you are doing.

Check the status of the of database server:

status;

mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 14
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.44-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 47 min 9 sec
Threads: 1 Questions: 32 Slow queries: 0 Opens: 4 Flush tables: 2 Open tables: 30 Queries per second avg: 0.011
--------------
MariaDB [(none)]>

Creating a database does not select it for use; you must do that explicitly. To make howtoforge the current database, use this statement:

USE howtoforge

Your database needs to be created only once, but you must select it for use each time you begin a MySQL session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke MySQL. Just specify its name after any connection parameters that you might need to provide. For example:

mysql -h host -u user -p howtoforge

NOTE: All the database names, table names, table fields name are case sensitive. So you would have to use proper names while giving any SQL command.

By default, all MySQL operations run via the command line are performed on the currently selected database. Which database is currently selected? To find out issue the following command:

SELECT database();

Your result may be similar to this:

+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
MariaDB [(none)]>

The result is null, meaning a database is not currently selected.

More information to follow

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.