MySQL: Move Data Directory to a New Location on Ubuntu 16.04

In this post, I am sharing required steps to move MySQL Data Directory from old location to new location.

Check the current data directory of MySQL:

1 2 3 4 5 6

mysql> select @@datadir; +-----------------+ | @@datadir       | +-----------------+ | /var/lib/mysql/ | +-----------------+

Stop the MySQL Service:

1	sudo systemctl stop mysql

Confirm the inactive status of MySQL Service:

1	sudo systemctl status mysql

Output:

1 2 3 4 5 6 7 8 9

● mysql.service - MySQL Community Server    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)    Active: inactive (dead) since Wed 2017-07-12 19:45:48 UTC; 11s ago Main PID: 8841 (code=exited, status=0/SUCCESS)   Jul 12 19:11:30 ip-172-31-27-19 systemd[1]: Starting MySQL Community Server... Jul 12 19:11:31 ip-172-31-27-19 systemd[1]: Started MySQL Community Server. Jul 12 19:45:46 ip-172-31-27-19 systemd[1]: Stopping MySQL Community Server... Jul 12 19:45:48 ip-172-31-27-19 systemd[1]: Stopped MySQL Community Server.

Use rsync with -av flag to copy the existing database directory to the new location:

1	sudo rsync -av /var/lib/mysql /mnt/dbrnd_mysql

Rename old MySQL Data Directory:

1	sudo mv /var/lib/mysql /var/lib/mysql.backup

Change (datadir=) parameter in MySQL Config file (mysqld.cnf) for pointing to the New Data Location:

1	sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

My Sample mysqld.cnf:

1 2 3 4 5 6 7 8 9 10 11 12 13

[mysqld] # # * Basic Settings # user            = mysql pid-file        = /var/run/mysqld/mysqld.pid socket          = /var/run/mysqld/mysqld.sock port            = 3306 basedir         = /usr datadir         = /mnt/dbrnd_mysql/mysql tmpdir          = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking

Now, Check below sample error:

1	Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

To avoid above error, configure AppArmor Access Control Rules:

AppArmor: AppArmor (“Application”) is a Linux kernel security module that allows the system administrator to restrict programs’ capabilities with per-program profiles. Profiles can allow capabilities like network access, raw socket access, and the permission to read, write, or execute files on matching paths.

Open AppArmor for edit:

1	sudo nano /etc/apparmor.d/tunables/alias

Add new entry of new data location path:

1	alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/,

Please check below sample output of my AppArmor:
Because don’t add # in new entry and also make sure that old path is there with #.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

# ------------------------------------------------------------------ # #    Copyright (C) 2010 Canonical Ltd. # #    This program is free software; you can redistribute it and/or #    modify it under the terms of version 2 of the GNU General Public #    License published by the Free Software Foundation. # # ------------------------------------------------------------------   # Alias rules can be used to rewrite paths and are done after variable # resolution. For example, if '/usr' is on removable media: # alias /usr/ -> /mnt/usr/, # # Or if mysql databases are stored in /home: # alias /var/lib/mysql/ -> /var/lib/mysql/, alias /var/lib/mysql/ -> /mnt/dbrnd_mysql/mysql/,

After change, restart the AppArmor:

1	sudo systemctl restart apparmor

Create the minimal directory structure:

1	sudo mkdir /var/lib/mysql/mysql -p

Now, Start the MySQL Service:

1	sudo systemctl start mysql

Check the status of MySQL Service:

1	sudo systemctl status mysql

Now, connect MySQL and check the new datadir location:

1 2 3 4 5 6

mysql> select @@datadir; +-------------------------+ | @@datadir               | +-------------------------+ | /mnt/dbrnd_mysql/mysql/ | +-------------------------+