Change default MySQL database location

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

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.

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.

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...