Move mysql data directory to new location

In theory, it’s very easy:
In linux distributions, you first stop mysql:

service mysqld stop

Then, copy the entire data to the new location. Use -rv to make sure permissions etc. are copied as well:

cp -rv /var/lib/mysql /home

Next, make user mysql the owner:

chown -R mysql:mysql /home/mysql

Next, open the config file /etc/my.cnf
and change the datadir accordingly:

datadir=/home
#datadir=/var/lib/mysql

Last but not least, start mysql:

service mysqld start

In practice, this can go wrong easily, and there might be various reasons for this, such as apparmor. However, before starting lengthy researches, try the following instead:

mkdir /home/db
cd /home/db
mysql_install_db
service mysqld stop
cp -rv /var/lib/mysql /home/db
chown -R mysql:mysql /home/db/mysql
vi /etc/my.cnf
[mysqld]
datadir=/home/db/mysql
#datadir=/var/lib/mysql
service mysqld start

Check your website.

In my case, this solved the issue of error messages such as “Can’t open the mysql.plugin table. Please run mysql_upgrade to create it” (which wasn’t possible since mysql_upgrade only runs when the new datadir is ok).

Post a Comment

You must be logged in to post a comment.