Kaliphonia.com

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

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!