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, [b]do not copy[/b] 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
Good luck!