How do I move the MySQL data directory?

Question

I am trying to move the data directory of my MySQL database to a second disk array that I have as a mount point /array2/.

The problem I am having is I have tried everything and after I modify the location of datadir in my.cnf mysql will not start up again.

All I get is:

start: Job failed to start

score 66 · Accepted Answer · edited Sep 21 '15 at 09:20

66

Forgot about app armour.

For anyone that is interested I did the following to move the folder.

Stop the mysql server:

stop mysql

Create the new directory:

mkdir /array2/mysql

Copy over ONLY the database folders:

cp -R /var/lib/mysql /array2/mysql
cp -R /var/lib/mysql/users /array2/mysql

Backup the my.cnf file:

cp /etc/mysql/my.cnf /root/my.cnf.backup

Edit the my.cnf file:

nano /etc/mysql/my.cnf

Change all mentions of the old datadir and socket to your new location

Mine became:

datadir=/array2/mysql

socket=/array2/mysql/mysql.sock

Update the directory permissions:

chown -R mysql:mysql /array2/mysql

Rename the old directory:

mv /var/lib/mysql /var/lib/mysql-old

Create a symlink, just in case:

ln -s /array2/mysql /var/lib/mysql

Let AppArmor know about the new datadir:

echo "alias /var/lib/mysql/ -> /your/new/datadir/," >> /etc/apparmor.d/tunables/alias

Reload the apparmor profiles

sudo /etc/init.d/apparmor reload

Then start mysql:

start mysql

edited Sep 21 '15 at 09:20

Rushi Agrawal

115

answered May 16 '12 at 16:10

Jonny Flowers

961

This is a good answer but you may need one more step to allow clients to connect to mysql. Add a new group with one line under it... [clients] socket=/array2/mysql/mysql.sock where the socket= portion points to the new location of your mysql.sock file. – Night Owl Jan 30 '13 at 05:20
2

If you don't want to be bothered to merge /etc/apparmor.d/usr.sbin.mysqld each time mysql receives an update, you can add /array2/mysql/** rwk, to /etc/apparmor.d/local/usr.sbin.mysqld instead. Mysql will still have access to the old data dir, but that probably isn't an issue. – drevicko Jul 26 '13 at 03:46
sed -i "s,datadir.*=.*,datadir=$new_dir,g" /etc/mysql/my.cnf. Why only the database folders? – int_ua Apr 26 '14 at 16:36
For anyone moving their datadir to ZFS (like I did), make sure you also add: innodb_use_native_aio=0 in the [mysqld] section of your my.cnf file, since ZFS on linux does not support AIO. https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_use_native_aio – Andrew Ensley Oct 03 '14 at 20:04

score 23 · Answer 2 · edited Aug 18 '12 at 01:29

I found that AppArmor was the culprit by examining the syslog, and was able to successfully change the mysql data location by following this process.

Please note that, in files edited below, lines starting with + were added, and lines starting with - were removed. You should not actually type/paste the + signs when adding lines to these files.

I cloned the mysql directory to the new location:

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

Then I edited the datadir line in /etc/mysql/my.cnf:

sudo vi /etc/mysql/my.cnf

-datadir     = /var/lib/mysql
+datadir     = /new_dir/mysql

Then I edited /etc/apparmor.d/usr.sbin.mysqld:

sudo vi /etc/apparmor.d/usr.sbin.mysqld

-  /var/lib/mysql/ r,
-  /var/lib/mysql/** rwk,
+  /new_dir/mysql/ r,
+  /new_dir/mysql/** rwk,

Then I restarted mysql.

score 13 · Answer 3 · edited May 02 '13 at 10:17

13

Be aware.

If you have InnoDB tables you MUST copy over the ibdata* and ib_logfile* files or you will not be able to use the the tables. You will get:

'Table 'databaseName.tableName' doesn't exist'

errors.

Run this copy command to copy over the ibdata* and ib_logfile* files.

sudo cp -p /var/lib/mysql/ib* /array2/mysql/

edited May 02 '13 at 10:17

BuZZ-dEE

14,223

answered May 02 '13 at 09:59

dave

131
1
2

this should be a comment, it is not a complete answer – Postadelmaga Dec 02 '16 at 03:57

score 9 · Answer 4 · edited Dec 02 '16 at 05:49

9

I prefer to use mount with bind option so I avoid any further changes in Apparmor and Mysql configuration.

For example:

Suppose I want to move everything in /var/www. Lets say this dir is my dev environment and it is mounted in a different partition

First we need to stop mysql:
```
sudo systemctl stop mysql.service
```
We move files (preserving permission)
```
sudo rsync -av /var/lib/mysql /var/www
```
This will generate a directory /var/www/mysql/ with all the content.
We remove everything in the old directory:
```
sudo rm -r /var/lib/mysql/*
```
We mount the new directory with bind option in the old one.
edit /etc/fstab and add this line:
```
/var/www/mysql /var/lib/mysql  none  bind 0 0
```
This will mount the /var/www/mysql in our empty dir /var/lib/mysql
The bind option here do the magic, it will populate /var/lib/mysql with the content of /var/www/mysql so for mysql and apparmor it will be the like nothing has changed.
Now we do the mount:
```
sudo mount -a
```
and restart mysql.

edited Dec 02 '16 at 05:49

muru

197,895
55
485
740

answered Dec 02 '16 at 04:36

Postadelmaga

5,689

Can't thank you enough. Supposedly I have apparmor and selinux disabled, but changing datadir was not working. This approach fixed it. I have no idea why other approaches did not work, but it is solved. YOU MADE MY DAY! – Alberto Nov 21 '19 at 14:35
This worked on my end. No more configuring apparmor on Ubuntu 18.04 – Ocastro Feb 19 '21 at 17:42
You also need to move mysql-files directory, and add it as an additional bind mount. (Ubuntu 21.10) – PabloRQ Apr 27 '22 at 11:05

score 7 · Answer 5 · answered Aug 20 '15 at 22:19

I just tried another way, that some may find useful:

copy with permissions intact:

rsync -avzh /var/lib/mysql /path/to/new/place

back up (in case something goes wrong):

mv /var/lib/mysql /var/lib/_mysql

create a new empty directory in place of old:

mkdir /var/lib/mysql

bind mount the new location to the old:

mount -B /path/to/new/place /var/lib/mysql

Hope that helps someone, because symlinking didn't work for me, and this was the simplest way

score 3 · Answer 6 · answered Oct 09 '14 at 12:36

If you move your datadir, you not only need to give the new datadir permissions, but you need to insure all parent directories have permission.

I moved my datadir to a hard drive, mounted in Ubuntu as:

/media/*user*/Data/

and my datadir was Databases.

I had to set permissions to 771 to each of the media, user and Data directories:

sudo chmod 771 *DIR*

If this does not work, another way you can get mysql to work is to change user in /etc/mysql/my.cnf to root; though there are no doubt some issues with doing that from a security perspective.

score 0 · Answer 7 · answered Oct 29 '17 at 17:16

You realize if you follow the commands verbatim from the accepted answer it will fail?

cp -R /var/lib/mysql /array2/mysql

Will copy /var/lib/mysql to /array2/mysql/mysql.

When you set your configuration file you better be putting in /array2/mysql/mysql as the directory or else your mysqld will fail to start.

The better copy commands would be:

cp -R /var/lib/mysql /array2/ cp -R /var/lib/mysql/users /array2/mysql

Just my 2 cents worth.

How do I move the MySQL data directory?

7 Answers7

Linked

Related