Below is short guide for installing MariaDB / MySQL server on a several linux flavours (okay, only Arch based and Debian based distros), as well as covering a few things I often need to do.
Installation should be pretty straight-forward using your package manager.
Arch-based (Arch, Manjaro, ....)
MariaDB (which is the preferred DB server on Arch based distros) MariaDB was already installed on my Manjaro-i3 setup (although not enabled or started). To install, if it's no already installed you can simply do:
According to the ArchWiki article you should run the following before starting the
Once done, you can go ahead and enable (if you want
mariadb to start on system startup) and start the service:
Debian-based (Ubuntu, ...)
For my Ubuntu server, you can install mysql-server with:
Let's update our CentOS 7 install first
Now, we can install mariadb
Securing MariaDB / MySQL after install
You are strongly encouraged to run the following which will interactively run you through implementing a basic secure configuration for MariaDB / MySQL:
Creating a database
Many applications require you to create a basic database for it before deployment (e.g. confluence, jira, etc..).
To do so, we'll first need to login using (say) root user
-p flag signifies login with a password (the password that you set in the previous step when you executed
To create a new user and a standard database, we're going to run the following after logging into MySQL
<...> with appropriate inputs.
Privileges and remote login
Now, it may be required to access your MySQL database remotely.
I'm going to assume that you know the risks involved and that you are going to securely provide access to your db remotely for only specific IP sources (for example by firewalling all access apart from only those addresses required to access said db).
To achieve this you'll first need to provide access privileges for a user. For example, to grant access to user
<user> for all databases, you would do:
Now, you might also need to disable the
bind.address argument in the
my.cnf) file. For my Ubuntu 16.04 server, I needed to comment out
Finally, you'll need to restart MySQL
Dropping a database
To drop (delete) a database from MySQL, you'll need to login to MySQL (see above) and run
You can show all databases by
Splitting dump files
You may, on occasion, be required to split a single MySQL/MariaDB dump file. If you used a similar approach for MySQL backups as outlined in Implementing rsnapshot backups on your server, then you will be dumping a the full database (including the non-user databases that the MySQL server uses). Restoring these non-user databases to another server (or restoring data from a MySQL server to a MariaDB server) can bork an sql server. However, given that we have a single .dump (backup) file we'll need to extract only the specific database schema (and data) that we might want to restore.
Enter mysqldumpsplitter. mysqldumpsplitter is an awesome shell script that provides functionality to split a single dump file into multiple dump files separated by DB schema, tables, etc. Once cloned (or downloaded), and set as executable (
chmod +x mysqldumpsplitter) you can extract a database specific dump by executing:
The above will create a compressed (gz) file in an
'out' directory. The resultant file can then be restored to a MySQL/MariaDB server safely.