Home > MySQL > MySQL – Moving the datadir

MySQL – Moving the datadir

My default mysql location is /var/lib/mysql and I needed to restore a database that takes up a few GB of data. My root partition where /var is does not have enough space.

Move the location of mysql’s datadir. Follow the steps below. (I had upgraded mysql and had made some changes as noted in this earlier post)

1. Stop mysql

root@computer:/home/user# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

2. Edit my.cnf
In my case I had renamed it to some thing else. So I put the file back.

root@computer:/home/user# cd /etc/mysql
root@computer:/etc/mysql# cp my.cnf.issues my.cnf

Now edit the line

datadir = /var/lib/mysql 


datadir = /home/user/mysqldatadir

3. Copy the contents of /var/lib/mysql to the new location /home/user/mysqldatadir.
I did this a few times so I am guessing that you only need to copy the folder that has the default mysql database. The one that normally shows up when you have a fresh mysql install. As shown below.

mysq> show databases;
| Database           |
| information_schema |
| mysql              |
root@computer# cd /var/lib/mysql
root@computer:/var/lib/mysql# ls
debian-5.1.flag  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql_upgrade_info  carsdb

Now copy mysql folder to the new datadir location.
Also copy mysql_upgrade_info file.
Try and preserve the file permissions. The other files ie ibdata1 and ib_logfile* will be created. the folder carsdb is database that I do not need moved. (I intend to copy that over by first dumping the data using mysqldump.)

root@computer:/var/lib/mysql# cp -prv mysql /home/user/mysqldatadir/
root@computer:/var/lib/mysql# cp -prv mysql_upgrade_info /home/user/mysqldatadir/

4. Now change the permissions of the new datadir to belong to mysql

root@computer:/home/user# chown mysql:mysql  mysqldatadir/

5. Start up mysql

root@computer:/var/lib/mysql# cd /home/user/
root@computer:/home/user# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

root@computer:/home/user# ls -al mysqldatadir/
drwxrwxrwx  5 mysql   mysql       4096 Oct 26 16:25 .
drwxr-xr-x 81 user user     4096 Oct 26 16:24 ..
-rw-rw----  1 mysql   mysql   10485760 Oct 26 16:24 ibdata1
-rw-rw----  1 mysql   mysql    5242880 Oct 26 16:24 ib_logfile0
-rw-rw----  1 mysql   mysql    5242880 Oct 26 15:54 ib_logfile1
drwx------  2 mysql   mysql       4096 Jun 22 15:55 mysql
-rw-------  1 mysql   mysql          6 Jun 22 15:55 mysql_upgrade_info

6. Nothing else should change. So your previous users are still valid.

user@computer:~$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

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


7. If mysql fails to start have a look at the syslog. Should be near the end of the file.

root@computer:/home/user# less /var/log/syslog


Categories: MySQL Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: