This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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 |
|
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 |
|
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/, |
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/ | +-------------------------+ |
