0

So, this page is what I'm using as a manual:

Different locations for MySQL databases?

I'm trying to store my database on USB (whether a good idea or not, please tell me how to do this if there is a way). mysql works fine with a soft link that is linked to another folder on the computer. Of course that folder's ownership changed with chown -R mysql:mysql /file.

The first problem I encountered when doing this with USB is that, I couldn't use chown when the current directory is in the usb. My usb's file system is vfat. What I learnt from my research is that "vfat" or "fat32" file system has no support for a security. And that the files in usb could have "immutable" attribute. I couldn't use lsattr nor chattr to check if they are immutable or to eliminate the immutable tag.

I manually mounted the USB so that its "uid" and "gid" are "mysql". I run id -u mysql and id -g mysql, and those turned out to be 116 and 125. Then I used sudo mount -t vfat /dev/sdb1 /media/external -o uid=116,gid=125,utf8,dmask=007,fmask=117 to mount my usb. When I lsblk, it is mounted. I was little uncomfortable with the fact that EVERYTHING in my USB will be mysql:mysql, but I wasn't able to find how to set different owner for folders in the same USB.

Used https://help.ubuntu.com/community/Mount/USB this as a manual.

So, after I've done this, when I ls -l in USB, it gives mysql:mysql.

Before doing this, when performing show databases, I think the soft link I made under /var/lib/mysql/ would not appear, but it appears now.

Now, the problem is that when I perform show tables or create tables after choosing the database (soft link) it gives:

ERROR 1018 (HY000): Can't read dir of './Muniverse/' (errno: 13)

I don't know what to do from here.

When I search for "mysql errno: 13", the solutions all suggest chown -R mysql:mysql (or mysql.mysql), which I think it is already done since when I ls -l in USB, it APPEARS that the the owner and group IS "mysql".

Maybe it only APPEARS as it is? Or is this not working because the files are "immutable"?

However, referring to man manual, there exists "sys_immutable" option that is not set by default.

What am I doing or what have I done wrong?

NOTE: So, basically, I can make the soft link for folders on the computer to work in mysql but not on USB.

===========================

FYI, for those who are as noob as I am in Linux, I was able to format USB to ext4 on LINUX. My windows 10 computer didn't have option for "ext4". And the proof that I think is useful is that when I do lsattr, it now prints attributes and stuff.

link: formatting USB Flash memory for ubuntu ext4

Community
  • 1
user3290525
  • 103
  • You should use chown -R mysql:mysql /path to change owner, chmod only changes modification aka user rights. Not an answer to your question, but basics. – Kev Inski Jan 13 '16 at 08:42
  • @KevInski oh, that's right. Will edit my question. I did that, but I messed up that in questions. Thank you – user3290525 Jan 14 '16 at 00:01

1 Answers1

0

Use EXT on the USB. You are not going to be using the MySQL database outside of Linux anyways so use a native filesystem. That will fix all problems you have with permissions. The errno 13 is a permissions error and more than likely related to your filesystem.

"ln -s" will also work when using EXT. It is a Linux command so it does not work with Microsoft filesystems.

Using an USB for a database is a very bad idea though. Wear leveling is going to be a serious issue. You would be far better off putting the database on a separate partition on your system and copying that partition to a USB and restore the data on another machine where you can use the new database.

Rinzwind
  • 299,756
  • I'll try formatting USB to EXT file system and thanks for the caveat. I want to use the MySQL database just like any files I'd store in USB to "work on any PCs"; store a project on USB from home, continue at the work, or at friend's house, or something like that. So, I want to do the same with the MySQL database. Use the database on the work-laptop (linux), then use it on my home-laptop (linux as well). – user3290525 Jan 14 '16 at 00:41
  • So, like you said, it seems like a good idea to use EXT on the USB because I'll be using the USB storing MySQL only on Linux computers. But how bad is it to use MySQL database on USB? How severe is it from using, say, Microsoft Words on USB? I'd have to do research on why it'd be problem with MySQL database, but is it related with how MySQL handles editting, deleting, and saving the data? (e.g., even MySQL makes temporary tables on computers, instead of on USB, MySQL does it so often that USB will eventually get old faster than, say, using Microsoft Words?) – user3290525 Jan 14 '16 at 00:41
  • You should use USB for transport only. Use your systems harddisk and backup/restore from/to the USB to update the other systems. The amount of writes from MySQL to the USB will be a problem at some point (and if the USB is the ONLY medium with the database on it you have a single point of failure). Better to use your harddisks so when 1 fails you still have the other computers as fallback. Oh and you can not compare a database system with a word processor. Totally different ballgames ;) (a database does a lot of writes to disk). – Rinzwind Jan 14 '16 at 06:53
  • successfully formatted USB using this link: http://askubuntu.com/questions/149984/formatting-usb-flash-memory-for-ubuntu-ext4 – user3290525 Jan 16 '16 at 02:00
  • but still wasn't able to accomplish what I was going to do. Tried changing chown of the soft link, of the actual directory and its children using chown -R mysql:mysql, tried copying ibdata1, ib_logfile0, ib_logfile1 in the same folder as all the other tables are and in the same directory as the "database" folder. But still gives me errno: 13. But when I bring the folder back into /var/lib/mysql, it works perfectly even without having to restart the service (I didn't know I don't need to restart). – user3290525 Jan 16 '16 at 02:03