How To Install MySql Using MariaDb On Linux distributions, Debian, CentOS and SuSE
It’s been almost 31 years now, since Linus Torvalds announced "I m doing a (free) operating system (just a hobby, won’t be big and professional)".
Not only has his “hobby” became both big and professional, it gave birth to hundreds, if not thousands of different Linux operating system distributions, created for various purposes, from ones intended for simple storage, to those created for penetration testing and ethical hacking. By giving it a functional, usable and free operating system, Linux fueled the open source community, and with its help many applications grew into pivotal products of the software industry used on thousands of servers worldwide today.
Among them, not many are better known than MySQL, an open-source relational database management system created in 1994 by a Swedish company MySQL AB.
MySQL went through a lot of changes in these 25 years. After being bought by Sun Microsystems in 2008, MySQL changed hands again in 2010. When the company was acquired by the Oracle Corporation. Being owned by giant from California (which already had a competitive product) didn’t bring any major changes, community even argued that the development was deliberately halted, so in the best spirit of open source a couple of developers created a totally compatible fork of MySQL called MariaDB, maintained and further developed by the community to this day.
For this and other reasons, most Linux distributions today include MariaDB instead of MySQL in their software repositories, as can be seen when you try to install MySQL from the command line on Debian based distributions:
milosh@box:~/Desktop$ sudo apt-get install mysql-server
[sudo] password for milosh:
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package mysql-server is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or is only available from another source
MariaDB is a fully compatible drop-in replacement for MySQL, and will be used everywhere instead, so we will use that in our tutorial.
We will now go through it’s installation on major Linux distributions, Debian, CentOS and SuSE Linux.
Debian (and it’s derivatives like Ubuntu)
Installing MariaDB with Debian’s apt is as straightforward as any other application. First, make sure you keep your system updated and issue:
sudo apt-get update
After that, simply install MariaDB with:
sudo apt-get install mariadb-server
After that, we will enable automatic startup of the database server with the system:
systemctl enable mariadb
And to start it straight away, we will use:
systemctl start mariadb
Once installed, enabled and started up, we need to secure it, which we can do by running:
mysql_secure_installation
This will run us through several questions, and while you can probably just quickly go through the list and accept the suggested answers, it wouldn’t hurt to dedicate a few minutes and if needed, adjust answers according to your scenario - CentOS (Fedora etc)
In order to install MariaDB on CentOS, we will use its yum package manager.
As always, first make sure your system is updated, and issue:
sudo yum update
After that, just run:
sudo yum install mariadb-server
From there on, you can follow the same logic as with previous, Debian’s install. Proceed with:
systemctl enable mariadb
systemctl start mariadb
and
mysql_secure_installation
SuSE Enterprise Linux
In order to install MariaDB on SuSE, we will use it’s zypper package manager.
As always, first check whether your system is updated, and issue:
sudo zypper update
After that, just run:
sudo zypper install mariadb-server
Once done, start MariaDB by typing:
sudo rcmysql start
This will start MariaDB from where we can continue running:
mysql_secure_installation
There we will be able to set up the root password and tighten up the security a little bit.
Creating a database and a user
Now when we’re done with installation and configuration, it is time to check whether everything is up and running, by logging in to our MySQL server. Also checkout how to enable autosuggest and autocomplete for mysql
mysql -u root -p
If everything is alright, we should be presented with:
milosh@box:~$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.3.20-MariaDB-1 Debian buildd-unstable
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
You can
Most content management systems (like WordPress for example), require one database and one user with aprropriate rights. While still logged in into our MariaDB, proceed and type this in, replacing highlighted OUR_DATABASE with the name we’d like to use:
CREATE DATABASE OUR_DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;
Next step would be creating a database user, and granting him privileges on our database (replace highlighted with desired values):
GRANT ALL ON OUR_DATABASE.* TO 'OUR_USER'@'localhost' IDENTIFIED BY 'OUR_PASSWORD' WITH GRANT OPTION;
While still logged in, we will flush the privileges:
FLUSH PRIVILEGES;
And that’s it, we created our first database and it’s first user, so we can now freely exit by typing so:
exit
This will close our session.
Creating backups with mysqldump
Being able to create backups of our databases is a must for every administrator. For this task, we have an excellent little utility called mysqldump. With its help, creating a quick backup is a one line thing in our command line.
A simple:
mysqldump -u root -p -x -A > /home/user/backups/databases.sql
will create a dump of all our databases, in a single .sql file.
We can also create a dump of a single database that we created, by using:
mysqldump --user=root --password --lock-tables --databases OUR_DATABASE > /home/user/backup/OUR_DATABASE.sql
This will create a single database dump, locking tables first to avoid any corruption.
If you want to automate tasks (and you will at some point), you can also use cron jobs to create dumps, but in that case as extra security measure it might be vise to first create an administrative database user, whose credentials you can write down in your executable scripts.
That’s all folks.
Related Topics:
MySQL Create User and Password Commands
How to fix error Error loading MySQLdb module