What do you think? Discuss, post comments, or ask questions at the end of this article [More about me]

Skip to end of metadata
Go to start of metadata

Many (most?) of the applications I run and develop use a MariaDB or MySQL backend for data persistency.

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

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:

sudo pacman -S mariadb

According to the ArchWiki article you should run the following before starting the mariadb.service:

sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

Once done, you can go ahead and enable (if you want mariadb to start on system startup) and start the service:

sudo systemctl enable mariadb
sudo systemctl start mariadb

Debian-based (Ubuntu, ...)

For my Ubuntu server, you can install mysql-server with:

sudo apt-get update
sudo apt-get install mysql-server

Centos 7

Let's update our CentOS 7 install first

sudo yum update

Now, we can install mariadb

sudo yum install mariadb-server

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:

sudo mysql_secure_installation

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

sudo mysql -uroot -p

The -p flag signifies login with a password (the password that you set in the previous step when you executed sudo mysql_secure_installation.

To create a new user and a standard database, we're going to run the following after logging into MySQL

CREATE USER '<dbuser>'@'localhost' IDENTIFIED BY '<PASSWORD>';
CREATE DATABASE <dbname> CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON <dbname>.* TO '<dbuser>'@'localhost' IDENTIFIED BY '<PASSWORD>';
FLUSH PRIVILEGES;

replacing the <...> 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:


GRANT ALL PRIVILEGES ON *.* TO '<user>'@'%' IDENTIFIED BY '<PASSWORD>' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Now, you might also need to disable the bind.address argument in the mysqld.cnf (or my.cnf) file.  For my Ubuntu 16.04 server, I needed to comment out 

/etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address            = 127.0.0.1

Finally, you'll need to restart MySQL

sudo service mysql restart

Dropping a database

To drop (delete) a database from MySQL, you'll need to login to MySQL (see above) and run

DROP DATABASE <dbname>

You can show all databases by

SHOW DATABASES

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:

./mysqldumpsplitter.sh --source <filename> --extract DB --match_str <database-name>

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.

References

  1. https://mariadb.org/
  2. https://dev.mysql.com/doc/
  3. https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html
  4. https://github.com/kedarvj/mysqldumpsplitter

Related articles