Moving the MySQL data directory Ubuntu 16.04

Question

OS: Ubuntu 16.04

I am trying to move the MySQL data directory to another location. Here's what I did:

mysql -u root -p

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

sudo systemctl stop mysql

mkdir /data

sudo rsync -av /var/lib/mysql /data

I then verified that a /data/mysql/mysql directory was created and all the data was moved there.

Then I modified the /etc/mysql/mysql.conf.d/mysqld.cnf file:

datadir=/data/mysql

I then modified /etc/apparmor.d/tunables/alias. At the bottom of the file, added the following alias rule: alias /var/lib/mysql/ -> /data/mysql/,

then:

sudo systemctl restart apparmor

No errors so far.

I then did:

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

followed by:

sudo systemctl start mysql

This gave me the following error message:

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

systemctl status mysql.service

gives me:

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Thu 2016-09-01 08:09:04 UTC; 10s ago
  Process: 20380 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 20377 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 20380 (code=exited, status=1/FAILURE);         : 20381 (mysql-systemd-s)
    Tasks: 2
   Memory: 336.0K
      CPU: 221ms
   CGroup: /system.slice/mysql.service
           └─control
             ├─20381 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─20430 sleep 1

Sep 01 08:09:04 db.msrv.com systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Sep 01 08:09:04 db.msrv.com systemd[1]: Stopped MySQL Community Server.
Sep 01 08:09:04 db.msrv.com systemd[1]: Starting MySQL Community Server...
Sep 01 08:09:06 db.msrv.com systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

Any ideas?

Answer 1

1. Verify the existing MySQL installtion:

   mysql -u root -p
   

   From the MySQL prompt, do:

   select @@datadir;
   

   You should see the current data directory:

   /var/lib/mysql/
   

   Exit the MySQL mode:

   exit
   

2. Stop MySQL:

   sudo systemctl stop mysql
   

3. Verify that MySQL is stopped:

   sudo systemctl status mysql
   

4. Copy the data to the new location:

   sudo rsync -av /var/lib/mysql /data/newlocation 
   

   (or whatever volume you've created to store the data)

5. Create a backup of the existing data volume

   sudo mv /var/lib/mysql /var/lib/mysql.bak
   

6. Tell MySQL about the new location. Edit the file:

   /etc/mysql/mysql.conf.d/mysqld.cnf
   

   and change the datadir directive, to reflect the new location:

   datadir=/data/newlocation
   

7. Tell AppArmor about it: edit the file /etc/apparmor.d/tunables/alias and add:

   alias /var/lib/mysql/ -> /data/newlocation/,
   

   to the bottom of that file (the comma at the end of the line is NOT a typo).

   Then do:

   sudo systemctl restart apparmor
   

8. Create the minimal directory structure, to pass the mysql-systemd-start checks:

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

9. Restart MySQL

   sudo systemctl start mysql
   

10. Check status:

    sudo systemctl status mysql
    

11. Verify that the new data directory is active:

    mysql -u root -p
    

    At the MySQL prompt, enter:

    SELECT @@datadir;
    

12. Remove backup, once everything is verified to work:

    sudo rm -Rf /var/lib/mysql.bak
    

13. Restart MySQL

    sudo systemctl start mysql
    

14. Check status:

    sudo systemctl status mysql