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
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.
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
sudo cp -Rp /var/lib/mysql /mnt/data/mysql
-p flag uses the right permissions, so you should not have to use
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
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 ...
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* ~/