מאגר מידע

I have put together a guide on how to correctly move MySQL datadir to free up space on the /var partition.  I do not take any responsibility for this article if you do not pay attention and you crash MySQL I am not responsible.  If you are truly unsure of how to do this please get with a upper tier admin or myself to assist you.

First check the free space on /var and /home partitions:

root@localhost [/var/lib/mysql]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda8             198G   29G  159G  16% /home
/dev/sda2             9.7G  9.2G     0 100% /var

Make sure the Disk Space is being used by MySQL:

root@localhost [/var/lib/mysql]# du -h --max-depth=1
4.9G    .

You need to stop MySQL before copying the files this way the InnoDB database does not become corrupt.  We also need to make sure that MySQL does not start back up while we are copying the files into the new location.  So we will add a syntax error to the /etc/my.cnf:

root@localhost [/var/lib]# vi /etc/my.cnf
add DIE to the top

root@localhost [/var/lib]# grep die /etc/my.cnf
die

Now Stop MySQL:

root@localhost [/var/lib]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]

Make sure that MySQL is not running:

root@localhost [/var/lib]# ps aufx |grep mysql
root@localhost [/var/lib]#

Attempt to start MySQL, it should error out if the syntax error is working correctly:

root@localhost [/var/lib]# /etc/init.d/mysql start
error: Found option without preceding group in config file: /etc/my.cnf at line: 1
Fatal error in defaults handling. Program aborted
error: Found option without preceding group in config file: /etc/my.cnf at line: 1
Fatal error in defaults handling. Program aborted
Starting MySQL.Manager of pid-file quit without updating fi[FAILED]
root@localhost [/var/lib]#

Make sure MySQL is not running:

root@localhost [/var/lib]# ps aufx |grep mysql
root@localhost [/var/lib]#

Let's rsync the data to the new location /home/mysql:

root@localhost [/var/lib]# rsync -avz --progress mysql /home/
building file list ...
6127 files to consider
mysql/

sent 2199787710 bytes  received 134014 bytes  2565506.38 bytes/sec
total size is 5063821822  speedup is 2.30

When the rsync completes let's make sure MySQL did not start:

root@localhost [/var/lib]# ps aufx |grep mysql
root@localhost [/var/lib]#

If MySQL did not start and it's still not running let's go ahead and make sure that the data in both folders are the exact same by running the rsync again:

root@localhost [/var/lib]# rsync -avz --progress mysql /home/
building file list ...
6127 files to consider
mysql/hiphopishere.hiphopishere.com.err
18030 100%    0.00kB/s    0:00:00 (xfer#1, to-check=6123/6127)

sent 150883 bytes  received 42 bytes  100616.67 bytes/sec
total size is 5063822606  speedup is 33551.91

Now if everything was the same let's go ahead and remove the syntax error from /etc/my.cnf and update the datadir location:

root@localhost [/var/lib]# vi /etc/my.cnf

Remove die syntax and change:

datadir=/var/lib/mysql

TO:

datadir=/home/mysql

Now that we have verified the data let's move the MySQL datadir to mysql.bk:

root@localhost [/var/lib]# mv /var/lib/mysql /var/lib/mysql.bk

Create a symlink to the new MySQL datadir:

root@localhost [/var/lib]# ln -s /home/mysql /var/lib/mysql

Verify the symlink is pointed to the correct location:

root@localhost [/var/lib]# ll |grep mysql
lrwxrwxrwx  1 root    root      11 Dec 31 00:39 mysql -> /home/mysql/
drwxr-x--x 51 mysql   mysql   4096 Dec 31 00:15 mysql.bk/

Start MySQL:

root@localhost [/var/lib]# /etc/init.d/mysql start
Starting MySQL.                                            [  OK  ]

Check the status of MySQL:

root@localhost [/var/lib]# /etc/init.d/mysql status
MySQL running (8283)                                       [  OK  ]

root@hiphopishere [/var/lib]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.85-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.85, for pc-linux-gnu (i686) using readline 5.1

Go ahead and Check/Repair all databases:

root@localhost [/var/lib]# for i in `mysql -e "show databases"`; do mysqlcheck -r $i; done;

Once it's completed if everything seems good then you can go ahead and remove the old MySQL datadir which is now: /var/lib/mysql.bk:

root@localhost [/var/lib]# rm -rfv /var/lib/mysql.bk/
root@localhost [/var/lib]#

Check free space:

root@localhost [/var/lib]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda8             198G   34G  154G  18% /home
/dev/sda2             9.7G  4.4G  4.9G  48% /var