Raspberry Pi 2 - Move MySQL database to USB stick

[trlaing]

Hello!
I am having a mess around with installing different things on my Raspberry Pi 2. My latest project is installing a MySQL server on it. I have this working fine, but once I implement the MySQL server for the purpose I will use it for, the database will be rather large. Therefore, I would like to move the MySQL data files onto my Sandisk Cruzer Blade 64GB USB stick formatted to ext4. I have followed the following guide to do it:
http://gagsap37.blogspot.co.uk/2014/11/ ... -data.html
However, after copying the data folder to the USB drive and changing the datadir in the /etc/mysql/my.cnf file, I get the following message when trying to restart the MySQL server:

Code: Select all

[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

I have changed the permissions to pretty relaxed ones (and probably unsafe), so I don't think it has to do with permissions:

Code: Select all

pi@raspberrypi /mnt/disk1/mysql $ ls -lrt
total 28692
-rw-rw-rw- 1 root root        0 Jun 16 23:27 debian-5.5.flag
-rw-rw-rw- 1 root root 18874368 Jun 16 23:27 ibdata1
-rw-rw-rw- 1 root root  5242880 Jun 16 23:27 ib_logfile1
-rw-rw-rw- 1 root root        6 Jun 16 23:27 mysql_upgrade_info
drwxrwxrwx 2 root root     4096 Jun 16 23:27 mysql
drwxrwxrwx 2 root root     4096 Jun 16 23:27 performance_schema
drwxrwxrwx 2 root root     4096 Jun 16 23:27 testdb
drwxrwxrwx 2 root root     4096 Jun 16 23:27 phpmyadmin
-rw-rw-rw- 1 root root  5242880 Jun 17 12:48 ib_logfile0

When I change the datadir back to the default, the MySQL server starts fine.
Any help would be greatly appreciated!

[trlaing]

I think I have resolved this myself. I stopped the server and ran the following command:

Code: Select all

chown -R mysql:mysql /newlocation/mysql

Then it started fine

[trlaing]

Okay, so here is my attempt at an idiot's guide to moving your MySQL data folder to a USB drive. I have presumed a couple of things:
1. There is only one USB drive plugged into the Raspberry Pi. When plugged in, it is found at /dev/sda1 (mine always has been).
2. You want to format the USB drive to ext4 file system.

First of all, plug in the USB drive. We want to format it to ext4 file system:

Code: Select all

sudo mkfs.ext4 /dev/sda1 -L usbdrive

Now we want to auto-mount the USB drive. We need to edit the /etc/fstab file:

Code: Select all

sudo nano /etc/fstab

Add the following line to the bottom:

Code: Select all

/dev/sda1       /mnt/disk1      ext4    defaults        0       0

Now we need to test that the auto-mount works:

Code: Select all

sudo mount -a

Now go to the folder we have mounted it to see if it has worked:

Code: Select all

mount

And you should see the line at the bottom saying something like:

Code: Select all

/dev/sda1 on /mnt/disk1 type ext4 (rw,relatime,data=ordered)

Now to move the MySQL data folder to the USB drive. First we need to stop the MySQL server:

Code: Select all

sudo service mysql stop

Then navigate to the folder where we would like to put the MySQL data folder:

Code: Select all

cd /mnt/disk1

Create the folder for the data files:

Code: Select all

sudo mkdir mysql

Now to copy the data files. We need to do this as a superuser to access the data folder:

Code: Select all

sudo su

Now the prompt will look different:

Code: Select all

root@raspberrypi:/mnt/disk1#

Enter the following to copy the files:

Code: Select all

cp –Rv /var/lib/mysql/* /mnt/disk1/mysql/

Change the ownership of the folder so MySQL can access it:

Code: Select all

chown -R mysql:mysql /mnt/disk1/mysql

And exit superuser mode:

Code: Select all

exit

Now we're back to the usual prompt:

Code: Select all

pi@raspberrypi /mnt/disk1 $ 

Now we must edit the MySQL configuration to let it know where the new data folder is:

Code: Select all

sudo nano /etc/mysql/my.cnf

Change the line:

Code: Select all

datadir                = /var/lib/mysql

to:

Code: Select all

datadir                = /mnt/disk1/mysql

Now restart the MySQL server:

Code: Select all

sudo service mysql start

Hopefully you will see a message like the following:

Code: Select all

[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..

If not, then my first attempt at an idiot's guide isn't very good!

[dharashah]

Hi trlaing,

Thanks for a very descriptive guide.

I am getting some issues

I have used home

Code: Select all

cp –Rv /var/lib/mysql/* /mnt/disk1/mysql/
cp: cannot stat ‘–Rv’: No such file or directory
cp: omitting directory ‘/var/lib/mysql/embroidery’
cp: omitting directory ‘/var/lib/mysql/mysql’
cp: omitting directory ‘/var/lib/mysql/performance_schema’
cp: omitting directory ‘/var/lib/mysql/phpmyadmin’

embroidery is the database i need. And somehow these folders are getting ommitted

When I tried starting MYSQL, I got error

Code: Select all

 sudo service mysql start
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

I will appreciate any help

[trlaing]

I have no idea why you get the error

Code: Select all

cp: cannot stat ‘–Rv’: No such file or directory

I tried the command myself and it does not give the same error. If you are copy and pasting it might be picking up some unwanted characters. If you type in the code to make sure there are no extra characters...

[dharashah]

Ok. I changed the command

Code: Select all

cp –Rv /var/lib/mysql/* /mnt/disk1/mysql/

to

Code: Select all

cp –r /var/lib/mysql/* /mnt/disk1/mysql/

This removed the error

Code: Select all

cp: cannot stat ‘–Rv’: No such file or directory

And also solved the problem on ommission of folders

And then everything started working!!!!

[trlaing]

Excellent!

[danman800]

Hi wouldnt it be better to move the folder and then create a symbolic link to it in its original location? Then you wont need to configure any changes in the config files...
Am i wrong?

[trlaing]

Your could try it and see if it works. Please post your result here so others can benefit from it.

[mfa298]

A couple of comments that might improve the tutorial:

Rather than 'sudo su' in the middle you could run 'sudo -i' (which does the equivalent thing) at the start and save the need for sudo all the time. Alternately you could just sudo for the couple of commands between the sudo su and exit.

Having labelled the ext4 partition when you formatted it (the -L usbdrive option to mkfs.ext4) you can use that label when mounting it by using LABEL="usbdrive" in /etc/fstab instead of the device (/dev/sda1) this means that if another drive is plugged in the mount will still work correctly. There's also a UUID option that uses the partitions uuid to mount.

if you added the -p flag to cp it should preserve the permissions so you don't need to chown/chmod everything afterwards.

Code: Select all

chown mysql: /mnt/disk1/mysql
cp –rvp /var/lib/mysql/* /mnt/disk1/mysql/

You'll still need to chown the root mysql folder but that can be done after creating it (before the copy) it and doesn't need to be recursive.

After getting the new location working I'd move the old DB out from /var/lib/mysql otherwise you might get confused data if the config reverts for some reason.

Code: Select all

sudo mv /var/lib/mysql{,.old}

(the {,.old} is a shell expansion so get's expanded to: '/var/lib/mysql /var/lib/mysql.old')

As someone else suggested rather than changing the datadir option it my.cnf you could use a symlink

Code: Select all

sudo ln -s /mnt/disk1/mysql /var/lib/mysql

(NB: this assumes you did the mv above)

A better option might be a bind mount (manual method)

Code: Select all

sudo mkdir /var/lib/mysql
sudo chown mysql: /var/lib/mysql
sudo mount --bind /mnt/disk1/mysql /var/lib/mysql

(NB: this assumes you did the mv above)

To make this automatic enter the following into /etc/fstab and use mount -a (you'll still need to have done the mv, mkdir and chown steps above before it can mount safely the first time)

Code: Select all

/mnt/disk1/mysql    /var/lib/mysql                 none    bind    0 0

(I've used the bind mount setup on some big production DB's in the past without issue)

[samarkh]

I too had this problem, and its due to the

Code: Select all

-

being changed to

Code: Select all

–

a dash being changed to an endash or emdash
I found it on this site http://askubuntu.com/questions/578337/cp-cannot-stat-r

Ok. I changed the command

Code: Select all

cp –Rv /var/lib/mysql/* /mnt/disk1/mysql/

to

Code: Select all

cp –r /var/lib/mysql/* /mnt/disk1/mysql/

This removed the error

Code: Select all

cp: cannot stat ‘–Rv’: No such file or directory

And also solved the problem on ommission of folders

And then everything started working!!!!