After moving MYSQL Databases Directory backup is failing adn cannot manage databases from Virtualmin

Hi

Unfortunately when I loaded this server I partitioned it in such a way that the root partition ran out of space so I moved the MYSQL database Directory to another partition.

MYSQL starts and runs fine but Virtualmin fails when backing up and when I try to manage the database from the virtual server. All websites can access the databases and other programs can connect to manage also.

The backup gives this error "Error: Couldn't read status information for table webid_bawtpayments () mysqldump: Couldn't execute 'show create table webid_bawtpayments': Table '455074_bawt.webid_bawtpayments' doesn't exist (1146)"

manage databses gives this error "SQL show index from webid_bawtpayments failed : Table '455074_bawt.webid_bawtpayments' doesn't exist"

I have changed 2 settings in Webmin -> Servers -> MySQL Database Server -> Module Config -> Path to MySQL databases directory and Webmin -> Servers -> MySQL Database Server -> MySQL Server Configuration -> Databases files directory

Is there another place to change it for Virtualmin?

I moved only the database folders and left the other files in the old location.

Webmin version 1.570 Virtualmin version 3.88 Pro Operating system Debian Linux 5.0 Perl version 5.010000 Path to Perl /usr/bin/perl BIND version 9.6 Postfix version 2.5.5 Mail injection command /usr/lib/sendmail -t Apache version 2.2.9 PHP version 5.2.6 Webalizer version 2.01-10 Logrotate version 3.7.1 MySQL version 5.0.51 ProFTPd version 1.31 SpamAssasssin version 3.2.5 ClamAV version 0.97.3

Regards Greg

Status: 
Closed (fixed)

Comments

Did you restart MySQL after making this change and moving the directory?

Yes I have restarted MYSQL several times, it restarts with no errors.

Well, Virtualmin communicates with the MySQL service in order to access those databases... it never needs to directly view the directories.

So, the only reason you'd get those errors is if they're being returned by MySQL, as the MySQL service attempts to find those databases and tables. It appears that MySQL may be a little confused at the moment.

If you run this command from the command line, what output do you receive:

grep datadir /etc/mysql/my.cnf

Hi

I get datadir = /home/mysql_databases

Another way to change the MySQL database location, rather than changing the datadir in MySQL, would be to move the databases elsewhere, then make /var/lib/mysql a symlink to your new location.

With that -- MySQL would still be looking at /var/lib/mysql, but the symlink would redirect it to the location you've moved them to.

That is a great idea, never thought of that.

I will change that over tonight when I can shut down MySQL for a few minutes and let you know how I go.

Appreciate your assistance, as usual always very helpful.

Great, let us know how that works for you!

+1 to the symlink idea .. that's what I've done in the past.

Thanks for all your help, it work perfectly.

Great, I'm glad it's working, thanks for letting us know!

for mysql better use bind instead of symbolic links, something like:

/etc/fstab /home/mysql_databases /var/lib/mysql none defaults,bind 0 0

you also can set UID and GUI :) to get UID and GID use command "id mysql"

for example:

/home/mysql_databases /var/lib/mysql none defaults,bind,uid=27,gid=27 0 0

Automatically closed -- issue fixed for 2 weeks with no activity.