How to move MySQL datadir to another drive

Your MySQL database takes a huge space and your current drive is almost full?
One of the available options is to move MySQL database to another drive with minimal downtime, here is how…
On some machines this can also increase MySQL performance, especially one with fast drive — such as Raptor or SCSI drive.
I’m assuming:
– the second drive mounted as “/home2”
– current MySQL datadir is “/var/lib/mysql”
– the content of “/var/lib/mysql” is the following:
[root@server ~]# ls -o /var/lib/mysql
-rw-rw—- 1 mysql 10485760 Jul 9 19:04 ibdata1
-rw-rw—- 1 mysql 5242880 Jul 9 19:04 ib_logfile0
-rw-rw—- 1 mysql 5242880 Jul 9 07:13 ib_logfile1
drwx—— 2 mysql 4096 Jul 15 07:40 joomla
drwx—— 2 mysql 4096 Jul 9 06:03 mysql
srwxrwxrwx 1 mysql 0 Aug 13 09:42 mysql.sock
drwx—— 2 mysql 4096 Jul 9 11:31 users
drwx—— 2 mysql 4096 Jul 9 08:22 wordpress
[root@server ~]#
Steps:
We need to shutdown MySQL to get all data copied properly.
/etc/init.d/mysql stop

or
/etc/rc.d/init.d/mysql stop

if those doesn’t work for you try:
/etc/init.d/mysqld stop

or
/etc/rc.d/init.d/mysqld stop

For the sake of simplicity, let assume the correct one is “/etc/init.d/mysql stop”.
Optional, if your are using cron jobs and afraid to break things, stop it.
/etc/init.d/crond stop

Create a directory on “/home2” to hold MySQL data
mkdir /home2/mysql

Copy only databases from current MySQL datadir to the new directory recursively. Remember to copy database only, do not copy ibdata*, ib_logfile*, mysqld-log-bin or mysqld-log-bin.* files! In this case, I have 4 databases: joomla, mysql, users & wordpress.
cp -R /var/lib/mysql/joomla /home2/mysql
cp -R /var/lib/mysql/mysql /home2/mysql
cp -R /var/lib/mysql/users /home2/mysql
cp -R /var/lib/mysql/wordpress /home2/mysql

Backup MySQL config file in case something goes wild.
cp /etc/my.cnf /root/my.cnf.backup

Update MySQL config file.
nano /etc/my.cnf

Adjust “datadir” value and if needed also “socket” to match the following:
datadir=/home2/mysql
socket=/home2/mysql/mysql.sock

Save
Ctrl + O
Enter

Update new directory permission to mysql
chown -R mysql:mysql /home2/mysql

Rename old directory
mv /var/lib/mysql /var/lib/mysql-old

Create a symlink on old location in case there is unknown programs that depend on it
ln -s /home2/mysql /var/lib/mysql

Backup MySQL init script
cp /etc/init.d/mysql /root/mysql.backup

Update MySQL init script
nano /etc/init.d/mysql

Find “get_mysql_option” line.
Adjust the value to reflect the new directory
get_mysql_option mysqld datadir “/home2/mysql”

Optional, if you are using Webmin, you need to update Webmin to reflect the new config.
Login to Webmin
Go to “Servers” >> “MySQL Database Server” >> Click “Module Config” link at the top.
Update “Path to MySQL databases directory” value to:
/home2/mysql

We are done 🙂
Start MySQL and cron:
/etc/init.d/mysql start
/etc/init.d/crond start
Test the new location by browsing around your site. Once you are satisfied, you can completely remove the old MySQL datadir:
rm -fr /var/lib/mysql-old

I advised you do not remove the old directory for a few days, as precautions, until you really sure everything is going fine.
If something goes wild, restore them all:
/etc/init.d/mysql stop
/etc/init.d/crond stop
cp -f /root/my.cnf.backup /etc/my.cnf
cp -f /root/mysql.backup /etc/init.d/mysql
mv /var/lib/mysql-old /var/lib/mysql
/etc/init.d/mysql start
/etc/init.d/crond start

Advertisements

Share your thoughts with us!!!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s