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?

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.

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.

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.

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.

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.

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.