Mariadb Ubuntu

I had some troubles re-installing mariadb - files were missing and old installation files were used. Here are the steps how I optimised the configuration and changed the data-dir.

Cleanup: Remove all MySQL/Mariadb files

First kill all running mysql processes by calling the following command within your terminal. But attention, this can lead to data-disruption, so only call it if you want to remove all files and re-install mariadb:

sudo service mysql stop
killall mysqld

Now you can check if it was successful by running ps aux | grep mysql. In my case it returns just one process - the grep-command itself.

As second step we can remove all mysql-related packages. I used the follwing commands:

sudo apt-get purge mariadb-server
sudo apt-get purge mysql-*
sudo apt-get autoremove
sudo apt-get autoclean

You can check if further packages are using mysql-config by running dpkg -S etc/mysql.

Now we will delete all mysql related files, this includes tables, databases, logs and so on. The following command will show you all the files containing the name mysql:

find / -iname mysql 2> /dev/null

In my case I removed the all except the bash-completion:

sudo rm -rf /var/log/mysql /var/lib/mysql /etc/apparmor.d/abstractions/mysql /etc/init.d/mysql /etc/mysql

Installation

Now we are ready to install mariadb-server:

sudo apt-get install -f mariadb-server

Voila! - now you have a fresh and clean MariaDB installation.

Change datadir

Before we start to reconfifure it stop the service:

sudo service mysql stop

Now we can copy files to the new location (in my case /mnt/data/mysql).

sudo cp -Rp /var/lib/mysql /mnt/data/mysql

The -p flag uses the right permissions, so you should not have to use chmod or chown. To check it you can run ls -allh /mnt/data/mysql, which will show you the files, their permissions and the owner/group combination. In some cases this can be root:root, but most folders should belong to mysql:mysql.

Now you can change the datadir-setting in your mysql-configuration. If you want to use a "better" configuration, you can skip this step and use the config generated in the next section.

sudo vim /etc/mysql/my.cnf

datadir = /mnt/data/mysql #/var/lib/mysql

sudo service mysql start

In case of MariaDB hosted on a Amazon ec2 Ubuntu instance it works even without appamor-configuration etc.

ubuntu@ip-172-31-19-254:/var/lib$ mysqld --version
mysqld  Ver 5.5.47-MariaDB-1ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))
160422  6:15:01 [Note] mysqld (mysqld 5.5.47-MariaDB-1ubuntu0.14.04.1) starting as process 13481 ...

Optimize Configuration

On Percona you find a nice wizard to generate a performant configuration.

Stop mysql and backup your old config

sudo service mysql stop
sudo mv /etc/mysql/my.cnf /etc/mysql/my.old.cnf

Now create a new /etc/mysql/my.cnf and paste the content suggested by the percona wizzard:

sudo service mysql start

If you get an error like this:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 536870912 bytes!
InnoDB: Possible causes for this error:
 (a) Incorrect log file is used or log file size is changed
 (b) In case default size is used this log file is from 10.0
 (c) Log file is corrupted or there was not enough disk space
 In case (b) you need to set innodb_log_file_size = 48M
160422  7:49:09 [ERROR] Plugin 'InnoDB' init function returned error.
160422  7:49:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160422  7:49:09 [ERROR] Failed to initialize plugins.
160422  7:49:09 [ERROR] Aborting

You can just move your logfiles to your home-dir and mysql will create new one:

sudo mv /mnt/data/mysql/ib_logfile* ~/

Links