Moving mysql DB?

[Red Squirrel]

I'm trying to move mysql since the default directory is silly and is on a drive with hardly no space - I did not partition that drive to contain dynamic growing data, only OS/programs. I tried to move it, but then it wont start, I get this:

Log says:


070709 19:06:52 [ERROR] Can't start server : Bind on unix socket: Permission denied
070709 19:06:52 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql.sock ?
070709 19:06:52 [ERROR] Aborting



I do not have another instance running. I always do service mysqld stop before to be sure.



I edited my.cnf accordingly. Is there any other file I need to edit?

 

[Nothinman]

I did not partition that drive to contain dynamic growing data, only OS/programs.

Um the default directory is under /var which is defined as containing dynamic, growing data.

I haven't tried it myself but all you should have to do is change the datadir entry in /etc/mysql/my.cnf. And the error you posted has nothing to do with the database location, it can't open the local communication socket.

 

[Red Squirrel]

could it be that I can't move the socket? I tried to open that file to see what exactly it is, but it seems like an awkward file, I have a feeling it can't be moved, is this the case? If I change everything in my.cnf to point to the new location I start getting those errors.

 

[Nothinman]

Why move the socket? It's just a communication socket, it has no real size. There are a handful of places in my.cnf that you need to change if you move the socket, but there's no reason to do that.

 

[Brazen]

best thing to do would be to copy the files under /var/lib/mysqld (I think I got that right) to the new drive and then mount that drive at /var/lib/mysqld. If you want this drive to have more than just the mysql databases on it, than use the --bind option to mount just the mysql folder at /var/lib/mysqld.

 

[Nothinman]

best thing to do would be to copy the files under /var/lib/mysqld (I think I got that right) to the new drive and then mount that drive at /var/lib/mysqld. If you want this drive to have more than just the mysql databases on it, than use the --bind option to mount just the mysql folder at /var/lib/mysqld.

That's not a bad idea, I just assumed he had already partitioned and mounted all of the drives and just wanted to move the data to somewhere like /srv/mysql or wherever he already has his "dynamic growing data" partition that didn't include /var...

 

[Red Squirrel]

It is already partitioned, and repartitioning is not really an option. I rather keep the 300GB drive unpartitioned.

I tried doing a symlink though but that did not work.

 

[Red Squirrel]

Wow turns out symlink DOES work. I tried it again for fun, I might of had forgotten to point my.cnf back to original location when I first tried it.

So I got it moved from hda2 to hdc1. MUCH better. Not at 99% anymore. VMware writes temp files there and even had it turn off VMs on me at some point since it ran out. I'll keep the temp files there as it is probably better to have them on a separate drive then the VMs for performance.

Filesystem Size Used Avail Use% Mounted on
/dev/hda2 9.5G 4.5G 4.6G 50% /
/dev/hda1 145M 8.9M 128M 7% /boot
/dev/hdb1 276G 177G 85G 68% /data
/dev/hda5 100G 4.5G 90G 5% /data2
tmpfs 1014M 0 1014M 0% /dev/shm
/dev/hdc1 294G 123G 156G 45% /data3

 

[Nothinman]

VMWare writes tmp files to /tmp (not sure if it honors $TMPDIR) not anywhere in /var, you only noticed because you have both /tmp and /var on / and it's only 9G.