TurnKey Linux Virtual Appliance Library

Change default MySQL database location

Hello,

I am using the Fileserver, TurnKey Linux 12.0 / Debian 6.0.5 Squeeze version.

I have installed mysql-server on this appliance which runs happily.

Due to space constraints on the server, I would like to change the default location of mysql databases (currently under /var/lib/mysql) to another directory, for example, to /srv/storage/mysqldb.

I have followed a number of tutorials to make this change, but every single time mysql fails to start with the changed configuration. If I go back to the default config, mysql fires up easily.

Is there a trick I should know about to do this? For example, Ubuntu has apparmor installed which prevents changes to mysql data location. Wondering if there is an equivalent of apparmor in Fileserver, such as SELinux (I could not find anything installed though)?

On the Fileserver appliance, try as I might I cannot make mysql start if I change the default location of its database.

Any help gratefully accepted!

Thanks,

doxford

Jeremy's picture

Have you checked the MySQL logs?

Perhaps the logs (when it doesn't start) may give a hint on why it is not working? They should be found in /var/log

My guess would be file/folder permissions although that it a complete guess (based on nothing more than a hunch).

Another way to go would be to mount --bind the additional space to the default location. Even if it doesn't work, it will assist in troubleshooting the issue. The acheive your ends the usage would be 'mount --bind /extra/space /default/location'. Assuming that it works, then to get that to happen on reboot you will need to add an entry to /etc/fstab OTTOMH I don't recall the exact line, but I'm pretty sure I posted it the other day somewhere on here... (I definately looked up via google so worst case...)

Also if you are using TKLBAM I would double check that your new data location is being included in your backups. IIRC as long as it exists somewhere like /srv then it should, but always good to check these things.

Logs have not helped

Hi Jeremy,

Thanks for your quick response.

I have tried troubleshooting using logs, but still no joy. Here are the basic steps that I have tried when changing the default location:

- stop mysql

- copy mysql data to new folder (cp -R /var/lib/mysql/* /srv/storage/mysqldb)

- change permissions on new location (chown -R mysql:mysql /srv/storage/mysqldb)

- rename existing location (mv /var/lib/mysql /var/lib/mysql_original)

- edit /etc/mysql/my.cnf (datadir and socket locations pointing to new directory)

- create symlink (ln -s /srv/storage/mysqldb /var/lib/mysql)

- change symlink ownership (chown -h mysql:mysql /var/lib/mysql)

I have tried this many times without success so far - mysql fails to start with changes. BTW, I am running this on a local machine and don't use TKLBAM.


Solved: Change location of MySQL databases

NOTE: The following methods work for both Ubuntu Lucid LTS and Debian Squeeze versions of TurnKey FileServer. AppArmor seems disabled by default in the TKL version of Ubuntu, so no changes to AppArmor are needed


To change default location (/var/lib/mysql) of mysql databases using datadir in my.cnf (preferred method):
- /etc/init.d/mysql stop
- cd /var/lib/mysql
- ls -l (to check which databases are present in folder)
- mkdir /srv/storage/mysqldata
- chown -R mysql:mysql /srv/storage/mysqldata
- IMPORTANT: *Only* the databases need to be copied over to new location (i.e. do not copy over ib* files etc); therefore if we have 2 databases in /var/lib/mysql called 'mysql' and 'phpmyadmin' then we do:
- 'cp -r mysql /srv/storage/mysqldata/' and 'cp -r phpmyadmin /srv/storage/mysqldata/'
- chown -R mysql:mysql /srv/storage/mysqldata/*
- in /etc/mysql/my.cnf, *only* the 'datadir' line needs to be changed to: datadir = /srv/storage/mysqldata
- /etc/mysql/debian.cnf file should not be touched
- /etc/init.d/mysql start

To change default location (/var/lib/mysql) of mysql databases using mount-bind (alternative method):
- /etc/init.d/mysql stop
- mv /var/lib/mysql /var/lib/mysql_original (backup first for safety)
- mkdir /srv/storage/mysqldata
- cp -R /var/lib/mysql_original/* /srv/storage/mysqldata/
- mkdir /var/lib/mysql
- mount -o bind /srv/storage/mysqldata /var/lib/mysql
- chown -R mysql:mysql /var/lib/mysql
- /etc/init.d/mysql start


how to change mysql error log path to /mnt

Hi,

Can you please let me know, where do I change mysql log file path?

I tried in my.cnf file but not working. Please suggest

Jeremy's picture

Did you restart MySQL?

Also have you considered permissions? Generally /mnt is a temporary mount point which by default I'm not sure if MySQL would have permissions to write to (although I could be wrong...)

Besides using a DB from a temporary mount point doesn't sound like a particularly good idea to me...

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong> <font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <strike> <caption>

More information about formatting options

Leave this field empty. It's part of a security mechanism.
(Dear spammers: moderators are notified of all new posts. Spam is deleted immediately)