How to move MySQL data directory

Create the new database directory in the second HDD

mkdir /backup/mysql

rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/

chown -R mysql:mysql /backup/mysql

Stop MySQL

service mysql stop

Now rsync again, so all data will be transferred

rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/

Rename old database directory

mv /var/lib/mysql/ /var/lib/mysql_old

Create a symbolic link from the old database directory to the new one for any programs that rely on the default location

ln -s /backup/mysql /var/lib/mysql

Set the correct owner and group on the symbolic link

chown -R mysql:mysql /var/lib/mysql

Edit /etc/my.cnf

vi /etc/my.cnf

Comment out the old settings and add a line for the new one as you can see below

[mysqld]
#datadir=/var/lib/mysql
datadir=/backup/mysql

Save my.cnf and exit text editor

Start MySQL

service mysql start

 mysql> show variables like '%socket%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| socket        | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%datadir%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| datadir       | /backup/mysql/ | 
+---------------+----------------+
1 row in set (0.00 sec)

mysql> show variables like '%basedir%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

Move copy of current database to different server

cd /var/lib/
mv mysql_old `hostname`-mysqldata

disable firewall then rsync

rsync -avz "-e ssh -p 3333" /var/lib/`hostname`-mysqldata [email protected]:/backup/

server12.hosthat.com is a remote server to which we are backing up the mysql data folder.

Add Login Alert about MySQL data folder change

Edit/ etc/motd and add following lines

vi /etc/motd

Add

MySQL in /backup/mysql/ (/dev/sdb1)

---

backup mysql after moving data dir

mysql backup

mysql