Home > MySQL > MySQL – Moving the datadir

MySQL – Moving the datadir

Problem:
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.

Solution:
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
from

datadir = /var/lib/mysql 

to

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              |
+--------------------+
So 
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
owners.

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

mysql> 

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

Source:
http://rajshekhar.net/blog/archives/90-Moving-the-MySQLs-datadir-directory..html

Advertisements
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: