/ mozey.co / blog

change mysql data dir

April 1, 2015

How to change MySQL data directory?

🔗 Change MySQL data directory to a new location

Get current data dir

select @@datadir;

Stop the MySQL service

sudo service mysql stop

Copy data files to the new location (preserve permissions)

sudo su 
cp -R -p /var/lib/mysql/* /new/path 
exit

If something tries to reference the old location make it fail

sudo mv /var/lib/mysql /var/lib/mysql.old

Change MySQL config to point to new location

sudo vi /etc/mysql/my.cnf
# [mysqld]
# datadir = /new/path

Rewrite App Armor paths for MySQL data files

sudo vi /etc/apparmor.d/tunables/alias

alias /var/lib/mysql/ -> /new/path,

Reload App Armor and MySQL services

sudo service apparmor reload

sudo service mysql start

View logs

systemctl status mysql.service

journalctl -xe

Debugging, first check /new/path is owned by mysql user

sudo /usr/bin/mysqld_safe --datadir=/new/path --skip-grant-tables &

mysql -h localhost

🔗 Move MySQL log files to a new location

Stop the MySQL service

sudo service mysql stop

Copy data files to the new location (preserve permissions)

sudo su
cp -R -p /var/log/mysql /new/path
exit

Change log file location in MySQL config

sudo vi /etc/mysql/my.cnf

log_bin = /new/path/mysql-bin.log

Start MySQL service

sudo service mysql start