How to change the Mysql directory on a new bigger hard-drive that Stacks can access on Ubuntu 14.04 (trusty)
Paul Labadie
Ubuntu 14.04: How I changed the Mysql directory on a new bigger hard-drive that Stacks could access
First, identify your new drive. In order to have the same permissions of the OS drive, the new drive needs to have a partition table formatted as “EXT4”. This can be done in Ubuntu by using the “disks” utility. The reason is that new drives usually come formatted as NTFS and the permissions settings are different than the EXT4 partitions.
Which files will be affected:
/var/lib/mysql default data directory for mysql
/media/…. Where drives mounting points are located
/etc/mysql/my.cnf Where the mysql configuration file is located
/etc/apparmor.d/usr.sbin.mysqld Where the apparmor service keeps the mysql server settings
/etc/apparmor.d/tunables/alias File used by apparmor for aliases
1. Check that the partition table of the drive is EXT4.
2. Open a terminal window and become super-user
$ sudo su
3. Stop Mysql server
# service mysql stop
4. Copy the original mysql data directory into the new drive
# cp -R -p /var/lib/mysql /media/user/dr2
5. Backup the Mysql configuration file ~my.cnf as ~my.cnf.bkp
# /etc/mysql/my.cnf ./my.cnf.bkp
6. Edit the file my.cnf and look for the datadir entry. Change the path (which should be /var/lib/mysql) to the new path and save:
....
[mysqld]
local-infile=1
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
#datadir = /var/lib/mysql <<<<< the symbol “#” is used to comment command lines
datadir = /media/newpath…../mysql <<<<<<< change to this new line
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
....
7.
Then
open the file /etc/apparmor.d/usr.sbin.mysqld. Look for the following lines
beginning with /var/lib/mysql. Change these lines with new lines for the new
path and save the file.
...
......
/etc/mysql/*.pem r,
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/* r,
/etc/mysql/*.cnf r,
/usr/lib/mysql/plugin/ r,
/usr/lib/mysql/plugin/*.so* mr,
/usr/sbin/mysqld mr,
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/var/lib/mysql/ r, <<< change to “/media/..path../mysql/ r,”
/var/lib/mysql/** rwk, <<< change to “/media/..path../mysql/** rwk,”
/var/log/mysql/ r,
/var/log/mysql/* rw,
/var/run/mysqld/mysqld.pid rw,
/var/run/mysqld/mysqld.sock w,
/run/mysqld/mysqld.pid rw,
/run/mysqld/mysqld.sock w,
......
8. Now open the file /etc/apparmor.d/tunables/alias and replace the last line /home/mysql with the new path and remove the “#” commenting mark to make the line readable. Save the file
........
# ------------------------------------------------------------------
# Alias rules can be used to rewrite paths and are done after variable
# resolution. For example, if '/usr' is on removable media:
# alias /usr/ -> /mnt/usr/,
#
# Or if mysql databases are stored in /home:
alias /var/lib/mysql/ -> /home/mysql/, <<<< change with /media/…newpath../mysql remove the symbol “#” if present.
.......
9. Reload the apparmor service:
# /etc/init.d/apparmor reload
10. It is necessary to provide the correct permissions to the new mysql directories. Under most systems the external drives are mounted with a mounting point found under the /media directory (/media/user/drive2 for example where user can be any name and drive2 any mounting point name). The owner, group and relative permissions should be all checked before restarting mysql. Use bash functions chown, chgrp and chmod to change these parameters. Follow this scheme to set those parameters for each directory:
Owner Group permissions
/media root root 755
/media/user root root 777
/media/user/drive2 mysql mysql 777
/media/user/drive2/mysql mysql mysql 700
Anything under the /mysql directory must have owner=mysql, group=mysql and various chmod permissions that were inherited from the original directory under /var/lib/mysql
11. Rename the original /var/lib/mysql directory as /var/lib/mysql-old
12. Restart mysql:
# service mysql restart
To see if mysql is running use:
$ sudo netstat –tap | grep mysql
At this point stacks should be able to see the databases.Paul Labadie
Paul
claudius
Only after putting the apparmor profile for mysqld in complain mode, can I successfully start mysql with a non-default data directory:
$ sudo aa_complain mysqld
claudius
...