pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

moving mysql database to a usb drive

Sat May 12, 2018 2:05 pm

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?

User avatar
DougieLawson
Posts: 33620
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: moving mysql database to a usb drive

Sat May 12, 2018 3:03 pm

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.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

Heater
Posts: 9707
Joined: Tue Jul 17, 2012 3:02 pm

Re: moving mysql database to a usb drive

Sat May 12, 2018 3:21 pm

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.

User avatar
DougieLawson
Posts: 33620
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: moving mysql database to a usb drive

Sat May 12, 2018 3:46 pm

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
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

Heater
Posts: 9707
Joined: Tue Jul 17, 2012 3:02 pm

Re: moving mysql database to a usb drive

Sat May 12, 2018 6:04 pm

Excellent Dougie. Can we have that beer now?

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

Sat May 12, 2018 6:35 pm

lol. ok i'll try that before anyone grabs a beer

User avatar
DougieLawson
Posts: 33620
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: moving mysql database to a usb drive

Sat May 12, 2018 6:39 pm

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.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

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.

User avatar
DougieLawson
Posts: 33620
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: moving mysql database to a usb drive

Sat May 12, 2018 8:54 pm

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.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

Tue May 15, 2018 2:53 am

I just tried it. It works
(Y)(Y)(Y) <- 3 thumbs up

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

Wed May 16, 2018 2:59 am

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.

User avatar
DougieLawson
Posts: 33620
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: moving mysql database to a usb drive

Wed May 16, 2018 6:03 am

Reformat the stick as ext4 or mount it with uid=mysql to allow the mysql task to access files on your vfat filesystem.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

Fri May 18, 2018 1:03 am

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.

User avatar
rpdom
Posts: 12745
Joined: Sun May 06, 2012 5:17 am
Location: Ankh-Morpork

Re: moving mysql database to a usb drive

Fri May 18, 2018 5:00 am

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

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

Sat May 19, 2018 1:46 am

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

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

Sun Jun 03, 2018 9:37 pm

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.

grahamed
Posts: 272
Joined: Mon Jan 30, 2012 7:01 pm

Re: moving mysql database to a usb drive

Sun Jun 03, 2018 10:41 pm

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?

pi9
Posts: 44
Joined: Wed Jun 10, 2015 3:32 pm

Re: moving mysql database to a usb drive

Mon Jun 04, 2018 1:14 am

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.

Return to “Other programming languages”

Who is online

Users browsing this forum: No registered users and 2 guests