The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Move MySQL Databases

Discussion in 'Database Discussions' started by patchwork, May 7, 2002.

  1. patchwork

    patchwork Well-Known Member

    Joined:
    Nov 2, 2001
    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    16
    I want to move the MySQL databases to a different location, is this the correct procedure? Do I need to do anything else or modify any other files?


    # stop services
    /etc/rc.d/init.d/chkservd stop
    /etc/rc.d/init.d/mysql stop


    # double check mysql has stopped
    ps aux|grep mysql

    # copy databases to a new location
    cp -r /var/lib/mysql /newpath/mysql


    alter /etc/my.cnf

    change
    datadir=/var/lib/mysql
    to
    datadir=/newpath/mysql


    # restart services
    /etc/rc.d/init.d/mysql start
    /etc/rc.d/init.d/chkservd start


    Thanks
    Pete
     
  2. Brownie

    Brownie Well-Known Member

    Joined:
    Aug 10, 2001
    Messages:
    145
    Likes Received:
    0
    Trophy Points:
    16
    What i did, was:

    stopped mysql,

    moved all the databases to /home/mysql

    then went back to /var/lib - and created a symlink - much easier :p
     
  3. wwsphil

    wwsphil Registered

    Joined:
    Nov 11, 2005
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    solution to move mysql db away from /var

    Hi all
    I had the same problem. The/var directory got full and I had to move the mysql databases.

    I requested support from cpanel. Answer:

    http://www.eth0.us/mysql-dir


    Regards,
    Phil

    copy and pasted text from http://www.eth0.us/mysql-dir:

    Change the mysql directory
    By eth00
    Created 2005-12-16 10:21

    While not all datacenters do there are some that by default partition the system in a less then ideal manner. I have had to deal with quiet a few clients who have filled up their /var partition due to mysql being stored by default on the partition. This is a short little guide to show you how to move mysql to another parition on the system. I have another guide that shows you how to parition a drive for mysql but many times it is not possible to repartition a drive and it is a lot easier to simply move it to another live parition. I would make sure and leave mysql in the old place, or at least a backup copy, for a few days before deleting it to make sure all of the data was transfered fine. If you want to move to a partition other then /backup/ simply change backup to home or the partition name anyplace you see it. In addition to the space considerations moving to a different drive can also have a profound positive influence on the system performance.

    First edit the my.cnf:

    pico -w /etc/my.cnf

    Now in the mysqld section add the following:

    pid-file = /backup/mysql/mysqld.pid
    socket = /var/lib/mysql/mysql.sock

    datadir = /backup/mysql
    basedir = /backup/mysql



    Now we are going to copy all of the data to the new partition. Notice that we do the copy TWICE, that is because moving 5gb of data can take some time and the tables may have changed. When we run it the second time we hopefully get it so that when the switch over happens there is very little, if any, lost data. If you can afford the downtime simply shut down mysql before running this command. If you cannot though running it twice then quickly copy/pasting the other commands is a valid substitute.


    rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/
    rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/


    Now we need to setup the mysql.sock so that it operates correctly:
    ln -s /backup/mysql/mysql.sock /var/lib/mysql/mysql.sock
    rm -rf /tmp/mysql.sock
    ln -s /backup/mysql/mysql.sock /tmp/mysql.sock

    Restart mysql so it is on the new parition:
    killall -9 mysqld
    service mysql start



    *Note* I do not show you deleting the /var/lib/mysql directory, go ahead and do that a few days after the move if you do not have good backups incase something went wrong. Make sure when you delete the /var/lib/mysql directory you recreate it so that the mysql.sock file can be created in the directory. Do the following to remove the old data and get the mysql.sock correctly set back up.

    rm -rf /var/lib/mysql
    mkdir /var/lib/mysql
    chown mysql /var/lib/mysql
    service mysql restart
    ln -s /backup/mysql/mysql.sock /var/lib/mysql/mysql.sock
    rm -rf /tmp/mysql.sock
    ln -s /backup/mysql/mysql.sock /tmp/mysql.sock

    Thats it, you are all done with moving mysql!
     
  4. Crooner

    Crooner Member

    Joined:
    Oct 22, 2002
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Is there any reason this should not work? It seems so much simpler than the other solutions in this thread.

    Thanks,
    Dean
     
  5. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    It works just fine.
     
  6. Crooner

    Crooner Member

    Joined:
    Oct 22, 2002
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Thanks - I've been running tests all day and haven't seen any problems but assurance from an expert seals the deal!

    BTW - take a look at ASSP. I've had you install Mailscanner/Spamassasin on all my other servers but this looks like a great spam solution.
     
  7. forlinuxsupport

    forlinuxsupport Well-Known Member
    PartnerNOC

    Joined:
    Dec 22, 2004
    Messages:
    386
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    i used the symlinking and cpanel did NOT include mysql in the backup.

    Please check and make sure your mysql is still being backed up.
     
  8. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    If you edit /etc/my.cnf and under [mysqld] specify the new path to the mysql directory:

    datadir = /home/mysql/

    Then restart mysql (and check it's working as expected).

    The cPanel backup procedure will then pick up the new path and back it up correctly :)
     
  9. Bravo

    Bravo Well-Known Member

    Joined:
    Oct 30, 2001
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    I'm trying to move /var/lib/mysql to /backup/mysql/ doing the following:

    stop mysql:
    /etc/rc.d/init.d/chkservd stop
    /etc/rc.d/init.d/mysql stop

    copy to /backup:
    rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/

    rename /var/lib/mysql/ to /var/lib/mysql2/

    Up to this point works fine. Then I try to create a symlink and it fails:

    ln -s /backup/mysql/ /var/lib/mysql/
    ln: creating symbolic link `/var/lib/mysql/' to `/backup/mysql/': No such file or directory

    I double checked and the directory /backup/mysql/ exists. Why am I getting this error?
     
  10. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sticky On Internet
    you've already renamed the /var/lib/mysql/ how would it link ????


    mohit
     
  11. Bravo

    Bravo Well-Known Member

    Joined:
    Oct 30, 2001
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    does /var/lib /mysql/ have to exist in order to make it a symlink to /backup/mysql/?
    I thought if it existed ln -s would not be able to create it as a symlink
     
  12. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    Drop the trailing /

    Code:
    root@mundane [~]# ln -s /root/tmp /tmp/tmp
    root@mundane [~]# ln -s /root/tmp /tmp/tmp2/
    ln: creating symbolic link `/tmp/tmp2/' to `/root/tmp': No such file or directory
    The first command succeeds, the second fails due to the trailing /

    Code:
    root@mundane [~]# ls -l /tmp
    total 296
    drwxrwxrwt   8 root     root       4096 Aug 28 09:58 ./
    drwxr-xr-x  28 root     root       4096 Aug 21 07:54 ../
    drwxr-xr-x   2 root     root       4096 Aug 28 09:49 cpbandwidth/
    -rw-r--r--   1 tramel   abcdee       11 Aug 28 00:00 cptest
    drwxrwxrwt   2 root     root       4096 Aug 21 07:55 .font-unix/
    drwxr-xr-x   2 tomcat   nobody     4096 Aug 21 07:55 hsperfdata_tomcat/
    drwxrwxrwt   2 root     root       4096 Aug 21 07:54 .ICE-unix/
    drwx------   2 root     root      16384 Sep 13  2006 lost+found/
    srwx------   1 nobody   nobody        0 Aug 27 09:20 mod_mono_server_global=
    -rw-------   1 nobody   nobody        0 Aug 27 09:20 mod_mono_server_global_1651471482
    lrwxrwxrwx   1 root     root         25 Jun 18 10:55 mysql.sock -> /var/lib/mysql/mysql.sock=
    -rw-r--r--   1 root     root     196597 Aug 23 13:00 packages.log
    -rw-------   1 nobody   nobody    18216 Aug 23 14:18 sess_8d721bfacfef2ac4f67e8e1803298fd1
    -rw-------   1 nobody   nobody    16665 Aug 23 14:17 sess_ff7bb60943210e0ff091b6cc4d343619
    srwxrwxrwx   1 postgres postgres      0 Aug 28 09:55 .s.PGSQL.5432=
    -rw-------   1 postgres postgres     25 Aug 28 09:55 .s.PGSQL.5432.lock
    lrwxrwxrwx   1 root     root          9 Aug 28 09:58 tmp -> /root/tmp/
    drwx------   2 nobody   nobody     4096 Aug 27 09:20 .wapi/
    
     
  13. Bravo

    Bravo Well-Known Member

    Joined:
    Oct 30, 2001
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
     
  14. activa

    activa Well-Known Member

    Joined:
    May 23, 2006
    Messages:
    204
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Morocco
    cPanel Access Level:
    Root Administrator
    i have made all the step^ , but in the end i get error when try to starting mysql i have found this error in the log file

    Code:
    071218 01:46:59  mysqld started
    071218  1:46:59 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
    071218 01:46:59  mysqld ended
    note that i have made the move to directory /home/mysql

    Code:
    cp -r /var/lib/mysql /home/mysql
    thanks
     
  15. pcsousa

    pcsousa Well-Known Member

    Joined:
    May 28, 2004
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    There are many solutions here.

    I made a script to move databases to a db directory inside user space (located at /home or /home2, script checks) and then create symlink at /var/lib/mysql/

    Time to time I check major databases and move them using "./move.db.to.user.area cpanel_user db". I made this because I do not know how cPanel consider mysql space used to global user quota, so I think to put it on user space. This way user also have access to it's mysql files if he wants to move or make file backup.

    The script:
    This works fine but I made my var really small at the beginning. For a 2*750Gb space available I made /var only 5Gb spaces and this has been a major problem.

    Probably I'll move all mysql db directory too.

    Do you know how mysql space are considered to user quota? Db files are mysql.mysql and not user.user owned. This way system is not considerinf them to user system quota, or am I worng?

    Thank you.
     
  16. sharmaine001

    sharmaine001 Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    143
    Likes Received:
    0
    Trophy Points:
    16
    I followed this and this does not work

    I am getting error :

    Starting MySQLCouldn't find MySQL manager (/home/mysqlvarli[FAILED]/bin/mysqlmanager) or server (/home/mysqlvarlib/mysql//bin/mysqld_safe)

    how do i resolve this??

     
  17. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    What are the following two directives set to in your MySQL configuration?
    basedir
    datadir

    For reference, the MySQL configuration file may be found at the following path:
    Code:
    /etc/my.cnf
    Here is an example of what they could be set to (before moving anything):
    Code:
    [mysqld]
    basedir                         = /usr
    datadir                         = /var/lib/mysql
     
  18. sharmaine001

    sharmaine001 Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    143
    Likes Received:
    0
    Trophy Points:
    16
    I followed the instructions above:

    datadir = /home/mysqlvarlib/mysql
    basedir = /home/mysqlvarlib/mysql
     
  19. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    The specific instructions referenced are from the third-party and may or may not be safe if followed verbatim; please be cautious when following any guides including those from third-party sources. I recommend referring to official vendor documentation when possible, such as that by the official MySQL vendor or official MySQL documentation resources.

    Assuming the MySQL binary installation is intact and located in the standard path I would change the MySQL configuration directive "basedir" to the following:
    Code:
    basedir = /usr
     
  20. sharmaine001

    sharmaine001 Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    143
    Likes Received:
    0
    Trophy Points:
    16
    I will try that but the link that your staff gave me is the same with that post. I logged a ticket and i was directed to Change the mysql directory | eth0.us - Server admin info for cPanel, plesk, ensim and linux! by your staff
     
Loading...

Share This Page