1
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?

2
  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
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.