Thursday, September 12, 2013

How to Move MySQL Datadir to an alternate location



 

mysqldump - -add-drop-table - -all-databases | gzip > /home/alldatabases.sql.gz

I have put together a guide on how to correctly move MySQL datadir to free up space on the /var partition. I do not take any responsibility for this article if you do not pay attention and you crash MySQL I am not responsible. If you are truly unsure of how to do this please get with a upper tier admin or myself to assist you.

First check the free space on /var and /home partitions:

root@localhost [/var/lib/mysql]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda8 198G 29G 159G 16% /home
/dev/sda2 9.7G 9.2G 0 100% /var

Make sure the Disk Space is being used by MySQL:

root@localhost [/var/lib/mysql]# du -h --max-depth=1
4.9G .

You need to stop MySQL before copying the files this way the InnoDB database does not become corrupt. We also need to make sure that MySQL does not start back up while we are copying the files into the new location. So we will add a syntax error to the /etc/my.cnf:

root@localhost [/var/lib]# vi /etc/my.cnf
add DIE to the top

root@localhost [/var/lib]# grep die /etc/my.cnf
die

Now Stop MySQL:

root@localhost [/var/lib]# /etc/init.d/mysql stop
Shutting down MySQL.. [ OK ]

Make sure that MySQL is not running:

root@localhost [/var/lib]# ps aufx |grep mysql
root@localhost [/var/lib]#

Attempt to start MySQL, it should error out if the syntax error is working correctly:

root@localhost [/var/lib]# /etc/init.d/mysql start
error: Found option without preceding group in config file: /etc/my.cnf at line: 1
Fatal error in defaults handling. Program aborted
error: Found option without preceding group in config file: /etc/my.cnf at line: 1
Fatal error in defaults handling. Program aborted
Starting MySQL.Manager of pid-file quit without updating fi[FAILED]
root@localhost [/var/lib]#

Make sure MySQL is not running:

root@localhost [/var/lib]# ps aufx |grep mysql
root@localhost [/var/lib]#

Let's rsync the data to the new location /home/mysql:

root@localhost [/var/lib]# rsync -avz --progress mysql /home/
building file list ...
6127 files to consider
mysql/

sent 2199787710 bytes received 134014 bytes 2565506.38 bytes/sec
total size is 5063821822 speedup is 2.30

When the rsync completes let's make sure MySQL did not start:

root@localhost [/var/lib]# ps aufx |grep mysql
root@localhost [/var/lib]#

If MySQL did not start and it's still not running let's go ahead and make sure that the data in both folders are the exact same by running the rsync again:

root@localhost [/var/lib]# rsync -avz --progress mysql /home/
building file list ...
6127 files to consider
mysql/hiphopishere.hiphopishere.com.err
18030 100% 0.00kB/s 0:00:00 (xfer#1, to-check=6123/6127)

sent 150883 bytes received 42 bytes 100616.67 bytes/sec
total size is 5063822606 speedup is 33551.91

Now if everything was the same let's go ahead and remove the syntax error from /etc/my.cnf and update the datadir location:

root@localhost [/var/lib]# vi /etc/my.cnf

Remove die syntax and change:

datadir=/var/lib/mysql

TO:

datadir=/home/mysql

Now that we have verified the data let's move the MySQL datadir to mysql.bk:

root@localhost [/var/lib]# mv /var/lib/mysql /var/lib/mysql.bk

Create a symlink to the new MySQL datadir:

root@localhost [/var/lib]# ln -s /home/mysql /var/lib/mysql

Verify the symlink is pointed to the correct location:

root@localhost [/var/lib]# ll |grep mysql
lrwxrwxrwx 1 root root 11 Dec 31 00:39 mysql -> /home/mysql/
drwxr-x--x 51 mysql mysql 4096 Dec 31 00:15 mysql.bk/

Start MySQL:

root@localhost [/var/lib]# /etc/init.d/mysql start
Starting MySQL. [ OK ]

Check the status of MySQL:

root@localhost [/var/lib]# /etc/init.d/mysql status
MySQL running (8283) [ OK ]

root@hiphopishere [/var/lib]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.85-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.85, for pc-linux-gnu (i686) using readline 5.1

Go ahead and Check/Repair all databases:

root@localhost [/var/lib]# for i in `mysql -e "show databases"`; do mysqlcheck -r $i; done;

Once it's completed if everything seems good then you can go ahead and remove the old MySQL datadir which is now: /var/lib/mysql.bk:

root@localhost [/var/lib]# rm -rfv /var/lib/mysql.bk/
root@localhost [/var/lib]#

Check free space:

root@localhost [/var/lib]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda8 198G 34G 154G 18% /home
/dev/sda2 9.7G 4.4G 4.9G 48% /var

=========================================

In this tutorial we show you How to change MySQL database data directory to another location. By default MySQL on linux configure at following location
/var/lib/mysql

What will happen if /var goes low on space? Obviously MySQL start failing time to time and new data will be discarded. Then what is the solution?

The only solution is to change MySQL data directory from /var/lib to some other partition where we have a lot of free space. Use the following method to change the MySQL data location.

SSH the server using Terminal (linux), PuTTy (Windows). Login as root. Now let’s create whole database server backup in single database to secure ourselves.
mysqldump - -add-drop-table - -all-databases | gzip > /home/alldatabases.sql.gz

Stop the MySQL server
/etc/init.d/mysql stop

Let’s move the data directory
cd /var/lib
mv mysql /home/mysql
ln -s /home/mysql mysql
chown -R mysql:mysql /home/mysql

Start MySQL server
/etc/init.d/mysql start

Verify MySQL running fine
ps aux | grep mysql

 


No comments:

Post a Comment