Saturday, November 22, 2014

Raspberry Pi - MySQL use USB as data directory

Here's how to change the data location of MySQL running on Raspbian. By default data location is on /var/lib/mysql. Nothing wrong with that, but since I do not want to overwork my SDCard and need more storage space I decided to change the data location to my USB Flash Drive.

Prerequisites:
* The flash drive should be auto mounted Click here for guide

Steps:
1. Stop MySQL service
sudo service mysql stop

2. create a directory on your mounted Flash Drive  where we will put the content of the /var/lib/mysql directory

sudo mkdir datadir

my mount point is flash and I created the datadir directory inside of it.

pi@raspberrypi/mnt/flash $ ls -lrt
total 4
drwxrwxrwx 4 root root 4096 Nov 22 09:33 datadir


3. Copy the content of /var/lib/mysql to the new data location (/mnt/flash/datadir in this example)

I copy this as root (sudo su -)

 cp -Rv /var/lib/mysql/* /mnt/flash/datadir/

Check if the copy is successful. The content of datadir should be somewhat similar to this.

pi@raspberrypi02 /mnt/flash/datadir $ pwd
/mnt/flash/datadir
pi@raspberrypi02 /mnt/flash/datadir $ ls -lrt
total 28688
-rw-rw-rw- 1 root root        0 Nov 22 09:19 debian-5.5.flag
drwxrwxrwx 2 root root     4096 Nov 22 09:19 performance_schema
-rw-rw-rw- 1 root root        6 Nov 22 09:19 mysql_upgrade_info
drwxrwxrwx 2 root root     8192 Nov 22 09:19 mysql
-rw-rw-rw- 1 root root  5242880 Nov 22 09:19 ib_logfile1
-rw-rw-rw- 1 root root 18874368 Nov 22 09:33 ibdata1
-rw-rw-rw- 1 root root  5242880 Nov 22 09:33 ib_logfile0


4. Edit the /etc/mysql/my.cnf file
Change the line
datadir                = /var/lib/mysql

to
datadir         = /mnt/flash/datadir

then save.

5. start the MySQL service then check if everything is working fine.
sudo service mysql start

Done.




1 comment:

  1. I found that the service would not start without changing the ownership of the directory and everything in it

    sudo chown -R mysql:mysql /mnt/flash/datadir

    ReplyDelete

Multi Tab Cygwin using ConsoleZ

I have been developing  a lot of bash scripts lately. I usually test on my desktop computer using cygwin then I transfer the script to the i...