Mysql – Moving the MySQL data directory Ubuntu 16.04

mysql-5.6Ubuntu

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?

Best Answer

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
```
Stop MySQL:
```
sudo systemctl stop mysql
```
Verify that MySQL is stopped:
```
sudo systemctl status mysql
```
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)

Create a backup of the existing data volume

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

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
```
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
```
Create the minimal directory structure, to pass the mysql-systemd-start checks:
```
sudo mkdir /var/lib/mysql/mysql -p
```
Restart MySQL
```
sudo systemctl start mysql
```
Check status:
```
sudo systemctl status mysql
```
Verify that the new data directory is active:
```
mysql -u root -p
```
At the MySQL prompt, enter:
```
SELECT @@datadir;
```
Remove backup, once everything is verified to work:
```
sudo rm -Rf /var/lib/mysql.bak
```
Restart MySQL
```
sudo systemctl start mysql
```
Check status:
```
sudo systemctl status mysql
```

Related Solutions

PostgreSQL – How to Move Data Directory on Ubuntu

First make sure the file exists really in that location. Run ls $PGDATA if there is a mistake in the environment variable you will either see the wrong files or get an error because the path does not exist.

Then make sure the owner of the parent directory /home/destination_data_directory and all directories and files under match the user you start pg_ctl under. If they are not either use

su postgres_user

to switch to the right user before running pg_ctl or use

chown -R postgres_user /home/destination_data_directory

to change the owner of the directory and everything under it.

MySQL 5.6 – No Longer Defaulting to /tmp/mysql.sock

The MySQL documentation states that the default path to mysql.sock is /var/lib/mysql/ on RPM distributions. The path is set at compile-time depending on the target OS and can be overridden by configuration or command line, so for CentOS the behavior you are seeing is correct.

This means that the socket path must have originally been set to /tmp by some other mechanism. There are several places this could have been done; likely in the init script /etc/init.d/mysql.server (to set socket=/tmp/mysql.sock on the command line - this can be done in an init script even though it is unsupported) or a local options file. See this page in the documentation for MySQL's precedence order when processing startup options. Entries listed later in the table will override the preceding ones.

It probably isn't possible to trace the sequence of events leading up to the socket path change, but fortunately you can change it via ~/.my.cnf, the user-specific option file.

Best Answer

Related Solutions

PostgreSQL – How to Move Data Directory on Ubuntu

MySQL 5.6 – No Longer Defaulting to /tmp/mysql.sock

Related Question