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
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
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.
