How to change MySQL data directory to a new drive SSD in this case with minimal downtime?

Install a new drive, format, partition and mount it, say as ‘/ssd’.

1. Change directory to new partition/location

# cd /ssd

2. Create new MySQL directory.

mkdir mysql

3. Assign the MySQL group and owner for the new data directory.

# chown -R mysql:mysql /ssd/mysql

4. Sync all the databases from old data dir to new data dir.

# nohup rsync -vrplogDtH /var/lib/mysql/ /ssd/mysql &

(nohup insures that the copy command runs even if the shell hangs and the sync status will be available in nohup.out)

5. Schedule a MySQL server shutdown during off-peak hours.

# /etc/init.d/mysql(d) stop

(In Cpanel servers, stop chkservd too, else MySQL will be restarted, if monitoring is enabled).

6. Sync the data again (will take less time to sync this time).

# nohup rsync -vrplogDtH /var/lib/mysql/ /ssd/mysql &

7. Once you have verified the data, delete the “/var/lib/mysql” folder.

# rm -rf /var/lib/mysql

8. Create a symbolic link from the new data directory to the old one.

# ln -s /ssd/mysql /var/lib/mysql

Note: Create a symlink to avoid changing the MySQL socket and data path in MySQL, PHP and other applications that need it, or adjust the mysql config to see the new path

kbadmin has written 146 articles

Loading Facebook Comments ...

This site uses Akismet to reduce spam. Learn how your comment data is processed.