Change MySQL Database Location
So considering that i am using a 8GB USB Stick as my OS HDD it tends to get full easily and i need to clean stuff up or move things around. This happened recently with my fairly large databases.
Also because of read/write cycles are high i couldn’t imagine this would be good for my flash drive HDD and therefore decided to move it!
Stop MySQL
|
1 |
/etc/init.d/mysql stop |
Move existing data directory (which is located in /var/lib/mysql) to new dir /usr/new_datadir
|
1 |
mv /var/lib/mysql /usr/new_datadir |
Create symlink from new dir to old one
|
1 |
ln -s /usr/new_datadir /var/lib/mysql |
Don’t change /etc/mysql/my.cnf
Ubuntu uses some security software called AppArmor that specifies the areas of your filesystem applications are allowed to access. Unless you modify the AppArmor profile for MySQL, you’ll never be able to restart MySQL with the new datadir location.
In the terminal, enter the command
|
1 |
vim /etc/apparmor.d/usr.sbin.mysqld |
Duplicate the lines beginning with /var/lib/mysql and replace duplicated strings with /usr/new_datadir
In my case it was:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
.........
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
#
/usr/new_datadir r,
/usr/new_datadir** rwk,
........ |
Restart the AppArmor profiles
|
1 |
/etc/init.d/apparmor restart |
Restart MySQL
|
1 |
/etc/init.d/mysql restart |
MySQL should now start without any errors, have fun!