Recently, my server's only hard disk was almost full. I bought a new hard disk with bigger size and I decided to just add it as a second hard disk. Since I need to move it to the 2nd hard disk, I need to find a proper way to move the db with minimum downtime. So I googled around and found a solution.
First, I needed to format the 2nd hard disk and I chose xfs as the filesystem. I created 2 partitions using Linux's fdisk for this task. First partition is 10 GB and 2nd one is around 900 GB. That's approximately added up to 1 TB. Then I mounted the 2nd partition in current partition eg /media/hd2 as follows:
mount -t xfs /dev/sdb5 /media/hd2
where /dev/sdb5 is the partition and /media/hd2 is the mounting dir.
Stop mysql db before doing anything:
service mysql stop
Afterthat, I copied the entire db to newly mounted hard disk:
cp -rv /var/lib/mysql /media/hd2
It will take a while if you have huge databases.
Then, change the ownership of the dir to user and group of mysql:
chown -R mysql:mysql /media/hd2/mysql
You need to change the mysql config file in /etc/my.cnf to point to the dir:
[mysqld]
user = mysql
datadir = /media/hd2/mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
Now you can restart mysql db:
service mysql start
If there are no errors on startup, you can test by login to your mysql db and do sql query.
You can leave other settings as it is. If this doesn't work and if you use innodb, you may want to change these lines:
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
Make sure you test your dbs before deleting the old ones. Have fun!
