lunatechian (lunatech-ian)

one relating to, belonging to, or resembling lunatech

Moving the MySQL's datadir directory.

By default, MySQL's datadir is placed in the /var/lib/mysql directory. However, if you are planning on using MySQL tables to store a lot of data and your /var partition is small, it might cause you problem at a later stage. In such a scenario, it is better to move the MySQL's datadir to another partition (like /home.

The steps are

  1. Stop your mysql server before starting this operation
  2. Create the directories that will be new datadir
  3. chown the directory to the mysql:mysql user
  4. copy the files from the old datadir to the new location. However, make sure that the files named ib_arch_log_0000000000, ib_logfile0 etc. are not copied to the newer location.
  5. Make sure that the files and directories are owned by mysql user
  6. Make changes in the my.cnf to point the new datadir.
  7. Restart the MySQL database
  8. You might need to do varying degree of troubleshooting to get the server working if there is some problem
  9. Create a new database and verify that the files for this database are getting created in the new datadir
  10. After the server is running for a few days properly, get rid of the old data.
  11. Sleep
I tested these steps on MySQL 4.0.24 and all my tables were MyISAM type.

Defined tags for this entry:
Related entries by tags:

Trackbacks

Trackback specific URI for this entryTrackback URL

Comments

    • Posted byCHAd
    • on
    9. After the server is running for a few days properly, get rid of the old data.
    10. Sleep
    You mean if I follow these steps I don't get to sleep for a "few days"? ;-)
    Reply
    • Posted byCHAd
    • on
    Something's wrong I put in 3 > (angle brackets) followed by the text - "Meaning I don't get to sleep for days." But it didn't show up. Why?
    Reply
  3. I think the blog was stripping out the greater than angle brackets before displaying.
    Reply
  4. hehe -- I think you can do both of them concurrently
    Reply
    • Posted byID
    • on
    I think you should stop your server first!
    ...before step 3
    Reply
  6. ID - you stop mysql server before doing any of these steps.
    Reply
    • Posted byEric
    • on
    DELETING THE IB_* files when doing the move was BAD ADVICE in my case FYI !!! I couldn't figure out forever why I couldn't get everything working again, kept getting the error below. But when I finally copied the original ib_* files back into the data dir, it all worked again GREAT. Here's the error I got without them:

    070516 13:13:52 InnoDB error:
    Cannot find table / from the internal data dictionary
    of InnoDB though the .frm file for the table exists. Maybe you
    have deleted and recreated InnoDB data files but have forgotten
    to delete the corresponding .frm files of InnoDB tables, or you
    have moved .frm files to another database?
    Look from section 15.1 of http://www.innodb.com/ibman.html
    how you can resolve the problem.
    070516 13:13:52 [ERROR] Slave: Error 'Got error 1 from storage engine' on query. Default database: ''. Query: 'replace into (groupid, ts, state) values (9,'2007-05-15 11:14:06',0)', Error_code: 1030
    Reply
    • Posted byPeter Laman
    • on
    All my databases are INNODB and I keep getting "#2003 - Can't connect to MySQL server on 'localhost' (10061) " after following this procedure...
    Reply
  9. Check the mysqld.err log to see what the problem is. From my guess, it would be permission problem
    Reply
    • Posted byMorgan
    • on
    I found this question on Yahoo answers, but the question has since been deleted and as far as I know it it was not answered. I figured out an answer for it which solved the same problem I was having.



    How to change MySQL datadir

    Okay, so I want to change the datadir of my MySQL databases... but something doesn't seem to be working correctly for me.

    ***FYI: Operating System = Ubuntu 8.04***

    What I have done:

    created arbitrary directory /var/lib/mysql2 (as opposed to /var/lib/mysql .. I am just trying to test the method to change datadir)

    sudo chown -R mysql:mysql /var/lib/mysql2

    sudo /etc/init.d/mysql stop

    sudo cp -r /var/lib/mysql /var/lib/mysql2

    cd /var/lib/mysql2

    sudo rm ibdata1
    sudo rm ib_logfile0
    sudo rm ib_logfile1

    vi /etc/mysql/my.cnf
    - corrected line to : datadir =/var/lib/mysql2

    vi /etc/apparmor.d/usr.sbin/mysqld
    - added lines: /var/lib/mysql2/ r, and /var/lib/mysql2/** rwk,

    Now, when I try:

    sudo /etc/init.d/mysql start

    it fails to start, If I edit the /etc/mysql/my.cnf file and change the datadir back to /var/lib/mysql, the service starts up fine.


    The solution is to restart apparmor with:

    sudo /etc/init.d/apparmor restart


    then start mysql:

    sudo /etc/init.d/mysql start
    Reply
  11. Morgan,

    Do not delete the ibdata file, as that is where the innodb tables are stored.
    Reply
  12. Thanks -- at last somebody actually did this before me in hardy herron
    Reply
    • Posted bystrobelix
    • on
    worked great, just like that on debian 5 with MySQL 5. I did it right after I had installed the MySQL server with apt-get install.

    Once done I did a 'mysqlcheck -A -u root -p'

    and everything was ok. GREAT & SIMPLE ;-)
    Reply
    • Posted byAnonymous
    • on
    When moving datadir on mysql 5.5.21 by setting

    datadir=/var/lib/mysql/databases

    in /etc/my.cnf

    it turned out to be necessary to move the ibdata1 ib_logfile0 ib_logfile1 files into the new directory
    Reply
    • Posted byJohn
    • on
    Hi,

    It worked for me. At first i tried only changing the data dir path
    datadir = /mnt/sdb/mysql
    After then, mysql server was not starting. Then i made a quick search on google and found your site and followed your guide.

    Again mysql was started and worked fine.

    Thank you very much :-)
    Reply

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.