Move MySQL datadir

We are going to see how to move MySQL datadir without loosing data. This is usefull when you meet a disk space problem or when you want to change your database server.

Operation is very simple, we just have to create a new directory in which we are going to copy the old datadir content. MySQL must be stopped before the copy to be sure that nothing will be modified during the data copy. Then we have to edit the mysql config file to define the new datadir. Finally, restart MySQL server and check that everything is ok. old mysql datadir: /var/lib/mysql new mysql datadir: /opt/mysql/datadir Here is the detail of commands:

# create directory, chown, chmod it
mkdir /opt/mysql/datadir
chown mysql:mysql /opt/mysql/datadir
chmod 0755 /opt/mysql/datadir

# save my.cnf
cp /etc/my.cnf /etc/my.cnf.bak

# stop mysql
/etc/init.d/mysql.server stop

# change datadir in my.cnf
vim /etc/my.cnf

:%s?/var/lib/mysql?/opt/mysql/datadir?g

:wq

# copy old datadir to new datadir
cp -rp /var/lib/mysql/* /opt/mysql/datadir/

# start mysql
/etc/init.d/mysql.server start

Now it's important to check that everything working fine, if all is ok we can remove the old datadir:

rm -rf /var/lib/mysql

Otherwise, we go back like this:

/etc/init.d/mysql.server stop
mv /etc/my.cnf /etc/my.cnf2 && mv /etc/my.cnf.bak /etc/my.cnf
/etc/init.d/mysql.server start