Page 1 of 1

moving mysql database to a usb drive

Posted: Sat May 12, 2018 2:05 pm
by pi9
Hi

I need to move one database to a USB drive rather than running it off the main sd card and shortening its life. There are many articles on other forums on how to move all the databases using the my.cnf file, but I just need certain databases. Is that possible?

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 3:03 pm
by DougieLawson
Close MySQL, move files from /var/lib/mysql to /new/directory, update /etc/mysql/my.cnf change all instances of /var/lib/mysql to /new/directory, save it. Restart MySQL, grab a beer, celebrate.

Simples.

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 3:21 pm
by Heater
Except, Dougie seems have drunk his beer before posting and hence gives exactly the answer you did not want. That is, how to move all the mysql databases.

I suspect that it is not possible to move individual databases from what I have read. For example: http://etutorials.org/SQL/MySQL/Part+II ... Directory/

However, all is not lost. You could run more than one instance of MySql, each configured with it's own database location. Which can of course be different.

You will have to change the configuration of the second or third... instances to use different port number to listen on.

Or, perhaps, you could move the file(s) for a database out of the normal data directory to somewhere else and then create symbolic links in the data directory pointing to the new database files locations.

Do back up your data before messing with any of this, if it's important.

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 3:46 pm
by DougieLawson
Looks like you can move just one database with
sudo mv /var/lib/mysql/DATABASENAME /full/path/to/new/db/.
sudo ln -s /full/path/to/new/db/DATABASENAME /var/lib/mysql/DATABASENAME

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 6:04 pm
by Heater
Excellent Dougie. Can we have that beer now?

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 6:35 pm
by pi9
lol. ok i'll try that before anyone grabs a beer

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 6:39 pm
by DougieLawson
I grabbed a beer, created a new database, inserted a row then moved it to /tmp.
The interesting thing was the folder & files in /tmp got deleted when I dropped the database after my experiment.

I'll be on the Yeni-Rakı when the Eurobore Song Contest starts.

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 7:19 pm
by pi9
Isn't it supposed to be like that? The database no longer exists. right?
I am not sure what you were trying to say.

Re: moving mysql database to a usb drive

Posted: Sat May 12, 2018 8:54 pm
by DougieLawson
pi9 wrote:
Sat May 12, 2018 7:19 pm
Isn't it supposed to be like that? The database no longer exists. right?
I am not sure what you were trying to say.
I didn't expect it to follow the symbolic link and delete the underlying database in /tmp.

Re: moving mysql database to a usb drive

Posted: Tue May 15, 2018 2:53 am
by pi9
I just tried it. It works
(Y)(Y)(Y) <- 3 thumbs up

Re: moving mysql database to a usb drive

Posted: Wed May 16, 2018 2:59 am
by pi9
Following up on my question earlier. I mounted the USB stick using:

sudo mount -t vfat /dev/sda1 /mnt/usb

and created the symbolic link. All is well except that when I try to create a table I get ERROR1005... can't create table (errno:13). I changed the permissions on database folder chmod +777 /mnt/usb/mydb but i still get the same thing. When I try to create the table on the original database I don't have any issues.

Re: moving mysql database to a usb drive

Posted: Wed May 16, 2018 6:03 am
by DougieLawson
Reformat the stick as ext4 or mount it with uid=mysql to allow the mysql task to access files on your vfat filesystem.

Re: moving mysql database to a usb drive

Posted: Fri May 18, 2018 1:03 am
by pi9
I tried different variations for the mount command, but I couldn't figure it out.
i tried uid=mysql and tried UUID=mysql and -u mysql, but it doesn't work.

Should I substitute mysql with some user name? I run mysql as root when I am creating the tables, but my applications have their own users to login. I am leaning towards running keeping the USB with fat in case I decide to plug it in windows later down the line. I don't want to format it by mistake.

Re: moving mysql database to a usb drive

Posted: Fri May 18, 2018 5:00 am
by rpdom
pi9 wrote:
Fri May 18, 2018 1:03 am
I tried different variations for the mount command, but I couldn't figure it out.

Code: Select all

sudo mount -t vfat -o uid=mysql /dev/sda1 /mnt/usb

Re: moving mysql database to a usb drive

Posted: Sat May 19, 2018 1:46 am
by pi9
Thanks. That resolved my issue. I am now able to create tables.
For other people's reference, this is what I put in /etc/fstab:

Code: Select all

/dev/sda1  /mnt/usb  vfat  rw,users,uid=mysql  0 2

Re: moving mysql database to a usb drive

Posted: Sun Jun 03, 2018 9:37 pm
by pi9
Revisiting this issue after a disaster where I got the OS card corrupted, I think even though the database appears to be on the memory stick, the data might have been on the main card. I mounted the card on the pi after getting a brand new installation, I was able to mount the database and see a listing of the tables, but not the structure or the data. I get a "table does not exist" error.

Re: moving mysql database to a usb drive

Posted: Sun Jun 03, 2018 10:41 pm
by grahamed
Last week I made a new Pi image c/w MySQL (actually MariaDB but hey). I created the mount points, I edited fstab, I edited my.conf.

I rebooted. I checked datadir variable. Shows it is using the USBstick. I imported the data using MySQL Workbench. I read, wrote, etc the data no problem. Left the thing running for a couple of days, data going in, web pages changing all OK. Power off and on. No data on the USBstick at all - nothing - no files, folders, nothing.

I guess that Linux was happily caching the data and never wrote anything to the USBstick at all - though ls and WinSCP showed files to be there, dates, sizes, permissions, etc all correct.

As a test I created a txt file on the stick, edited it, cat..ed it, etc.

Power cycle and it is not there.

Wrote a file using windows, no problem. Reformat, etc, no problem.

So why does Linux do this?

Re: moving mysql database to a usb drive

Posted: Mon Jun 04, 2018 1:14 am
by pi9
In my case, the structure was there when I rebooted. It is just that when I corrupted the OS and had to use a new one that I found out that my database was not really there. I guess it is probably best to use it from where it is supposed to be.