moving mysql databases to create space on var

[vashti]

My Server Admin has moved the MysQL databases from the default place in /var/

to

/usr/lib/mysql

However, an error has occured like this:

Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/username/public_html/new/includes/functions/database.php on line 19
Unable to connect to database server!

I've done locate mysql.sock in SSH as r00t, and it tells me it is in var/lib/mysql

But if I CD into var/lib/mysql and do ls -l it doesn't show ??

Can anyone advise on what to do to get MySQL up and running properly please. The MySQL daemon is up and running according to the servsup script.

Many thanks.

cPanel.net Support Ticket Number:

[vashti]

btw, have tried a graceful reboot - didn't work. Also a hard reboot, again didn't work.

Have tried shutting down MySQL and restarting via WHM, and that also didn't work :

mysql has failed, please contact the sysadmin. Jun 30 22:45:55 server mysql: No mysqld pid file found.

Looked for /var/lib/mysql/server.mydomain.com.pid. Jun 30 22:45:55 server rc: Stopping mysql: succeeded

cPanel.net Support Ticket Number:

[sfield]

Can anyone recommend a safe way to move MySQL database files from /var to /home?

It is essential that cpanel will still create database backups.

Thanks for any help

[fishfreek]

Anyone have more info on this? I am in the need to do this as well.

I figured I could just shut down myql, move the folder from /var/lib/mysql to something like /backup/mysql or /home/mysql (but if i do /home/mysql i dont want it to be found by going to http://servername/~mysql/) And then changing the my.cnf to reflect the new location for the database files and then starting mysql up again.

I dont want to try this with out knowing it will work right as the box I need to do it on is fairly heavily used for database's.

If I do edit the my.cnf to reflect the new location what would be the recommended syntax?

[PWSowner]

I leave the /var/lib/mysql directory where it is and just move the bigger user directories from inside, then symlink to them. Never had a problem with using it, backups, or anything else.

Make a mysql directory in the home directory, then move the user directories to the new place and link to them.

[dianaward]

I leave the /var/lib/mysql directory where it is and just move the bigger user directories from inside, then symlink to them. Never had a problem with using it, backups, or anything else.

Make a mysql directory in the home directory, then move the user directories to the new place and link to them.

Will this actually work this simply? I Have to move the user's databases from the var, there are a Lot of good sized databases on this box, and an email queue or logs of any size overload the partition and then the server. But the dc techs say I need to pay them to redo the OS in order to enlarge the partition. Moving the databases to the second backup drive or to home is a much easier and cheaper alternative.

Can someone tell me exactly what to do on a box with:
WHM 10.0.0 cPanel 10.0.0-C52
Fedora i686 - WHM X v3.1.0

[DigitalN]

To move the MySQL databases to another partition (/home/mysql in this instance)

# /etc/init.d/chkservd stop

# /etc/init.d/mysql stop

# rsync -avz /var/lib/mysql /home/

# mv /var/lib/mysql /var/lib/mysql.backup

# ln -s /home/mysql /var/lib/mysql

# /etc/init.d/mysql start

# /etc/init.d/chkservd start

Voila!

Remove /var/lib/mysql.backup when you are sure that all is well

# rm -rf /var/lib/mysql.backup

How large is /var/log ? It may be better to move /var/log than mysql, as it usually holds more data. Whats so large in /var/lib/mysql ? Do you have a lot of *.bin files?
You can take steps to not create the *.bin files in /etc/my.cnf - This can be a big space saver too.

[dianaward]

have tweaked exim to clear the mail queue must more quickly that the default. Although there aren't that many sites on this server, they are nearly all businesses with large mailing lists, plus run affilate programs and such. The average number of databases per account is about 6. Var has 3.1 GB and of that the mysql directory is over 2 GB. I am very nervous about messing with the databases since these customers are dependent on them for their businesses. But they are dependent on the proper running of the server also, and lately the server load has gone up to 30% and more when someone did a large mailing and the queue filled up the rest of /var.