Move MySQL Directory to Another Location

Lets say your MySQL data has fully utilized the disk space and you have another new hard disk specifically mounted for this purpose, you will need to migrate the data over to this new location. By default, MySQL will occupied following locations in the server:

Data -/var/lib/mysql
Config – /etc
Log -/var/log

We will not migrate data in /etc or /log because it usually not required much space. I will use following variables:

Server OS: CentOS 5.6 64bit
Old MySQL data: /var/lib/mysql
New MySQL data: /mnt/disk1/mysql

Warning: You MUST able to afford downtime on MySQL service to do this!

1. First of all, we need to make sure the service is stopped (if you are running cPanel, please disable MySQL monitoring under WHM > Service Manager > Untick ‘monitor’ for MySQL):

[[email protected] ~]# service mysql stop

2. Make sure there is no MySQL services running on the server:

[[email protected] ~]# ps aux | grep mysql | egrep -v 'grep'

3. Lets copy over the data directory. We are using rsync command so we can see the copy progress with estimate time remaining (this is really good for monitoring), and also preserve the permission and ownership of the MySQL directory:

[[email protected] ~]# rsync -avpP /var/lib/mysql /mnt/disk1/mysql

4. Now we need to tell MySQL to look at new data directory. Open /etc/my.cnf via text editor and change following server options:

[mysqld]
......
datadir=/mnt/disk1/mysql
socket=/mnt/disk1/mysql/mysql.sock
......

5. Now lets start back the MySQL with this new settings:

[[email protected] ~]# service mysql start

If everything are running back as expected, you can then delete the old /var/lib/mysql directory so you can save more free space.

MySQL Daily Backup and Transfer to other Server

When you have 2 MySQL server which is not running in replication or cluster, is recommended to have MySQL backup running daily. This will help you on fast restoration, reliable data backup and disaster recovery.

I have created a bash script to run daily and make sure the data is save into local disk and another copy being transferred to another server via rsync. You can use following script and change the value to suit your environment. I am using following variables:

OS: CentOS 5.6 64bit
Server1: 192.168.1.2
Server2: 192.168.1.3
Backup user: mysql_backup
Backup user password: l3tsb4ckup
Backup path: /backup

Before we use the script, is good to have a specific user to run the backup scripts. Now lets start configuring in Server1:

1. Create specific user to run the backup:

[root@centos ~] useradd mysql_backup
[root@centos ~] passwd mysql_backup

(notes: enter the password 2 times as above)

2. Assign that user to a specific location inside your server:

[root@centos ~] usermod -d /backup mysql_backup

3. Test the user environment by using su and create ssh-key so this user will have password-less connection to another server:

[root@centos ~] su - mysql_backup
-bash-3.2$ mkdir ~/.ssh/
-bash-3.2$ ssh-keygen -t dsa

(notes: just press enter for all prompts)

4. Now login to Server2 via root and repeat step 1 to step 3. Once done, continue below step in both servers to transfer the SSH key for mysql_backup user. This will allow password-less connection between each of them:

-bash-3.2$ cat ~/.ssh/id_dsa.pub | ssh mysql_backup@192.168.1.2 "cat -- >> ~/.ssh/authorized_keys"

(notes: enter mysql_backup’s password which is l3tsb4ckup)

Continue reading “MySQL Daily Backup and Transfer to other Server” »