How to Move MySQL Datadir to an alternate location
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
Leave a Reply
You must be logged in to post a comment.