Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

How to copy / migrate a large mysql database between accounts

Discussion in 'Database Discussion' started by markuswest, Dec 8, 2014.

  1. markuswest

    markuswest Active Member

    Joined:
    Oct 21, 2013
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    I hope its OK to post this question on this forum, in my situation I have one dedicated server with 3 accounts, of which I will only work on 2 for this question, the main account is website.com with a mysql database of about 1.3 Gb now I want to copy the database to another account old.website.com but I am not sure how to proceed with this task, I figure I should use Secure Shell to do this as its a large database. Can someone please provide me with steps I can take to fulfill this task?

    Best Regards,
    Lloyd
     
    #1 markuswest, Dec 8, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,367
    Likes Received:
    1,855
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Re: How to copy / migrate a large mysql database between accounts on a dedicated server

    Hello :)

    You can use the "mysqldump" utility to backup a database with a command such as:

    Code:
    mysqldump database_name > /path/to/database_name.sql
    You can then restore the database dump to the new database via:

    Code:
    mysql new_database_name < /path/to/database_name.sql
    Note: The above commands assume you are logged in as "root".

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #2 cPanelMichael, Dec 8, 2014
  3. markuswest

    markuswest Active Member

    Joined:
    Oct 21, 2013
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Re: How to copy / migrate a large mysql database between accounts on a dedicated server

    Hi Michael,

    Apologies for sounding naive, but how do you find out what is the path to the database? can you use the File Manager in cPanel to see the path to database, and I presume to restore the dump into the database you first have to create the database in cPanel?

    Best Regards,
    Lloyd
     
    #3 markuswest, Dec 9, 2014
  4. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    995
    Likes Received:
    41
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    Re: How to copy / migrate a large mysql database between accounts on a dedicated server

    I think Michael is suggesting that you have root access and SSH

    If you only have Cpanel, then I'd use MyPHP admin.
    There are limitations on file size with MyPHP, so you'll have to export in bite sized chunks and try to zip as well.
    I guess there are tidier ways of doing it, but this has worked for me on numerous occasions.
     
    #4 keat63, Dec 9, 2014
  5. markuswest

    markuswest Active Member

    Joined:
    Oct 21, 2013
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Re: How to copy / migrate a large mysql database between accounts on a dedicated server

    Hi @keat63,

    I presume Michael is talking about root access and SSH, I just don't know SSH so well.

    About PHPmyadmin I didnt know you you can export a database in chunks, I will have to check it out.
     
    #5 markuswest, Dec 9, 2014
  6. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    995
    Likes Received:
    41
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    Re: How to copy / migrate a large mysql database between accounts on a dedicated server

    If you highlight the database in the left hand column, and then choose which tables you wish to export.
    Scroll down to the bottom of the page and use this export button, (and not the one at the top) it will only then export the chosen tables, which you can import in to your new database.

    It's messy, so like i said, there are probably tidier and easier ways to do it, i just havn't learnt how.
     
    #6 keat63, Dec 9, 2014
  7. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,309
    Likes Received:
    393
    Trophy Points:
    583
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Re: How to copy / migrate a large mysql database between accounts on a dedicated server

    Download a MySQL Database Backup is a tool in your cPanel for quickly downloading a backup of a database. The backup is compressed. Working within phpMyAdmin, if you're not sure exactly what you're doing, can get messy.

    You should be able to download the entire database easy enough from there for moving to another server, or for backing up purposes.

    What cPanelMichael provided is the best way to backup the database, and restore it on the same server. The path he mentions, can be any path you like. You need a place to dump it to first, before you can restore it somewhere else on the same server. If you've got your server partitioned and lacking space in one area, you might change/that/path/to/database_name.sql to some other partition where you do have enough space to safely store it for the task at hand.

    When restoring the database from command line, you will be asked for the NEW database name, and password to restore this database into, so yes, create one first as you normally would from your cPanel.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #7 Infopro, Dec 9, 2014
  8. markuswest

    markuswest Active Member

    Joined:
    Oct 21, 2013
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    So to make sure the "/path/to/database_name.sql" can be like a /tmp/ folder? sorry for asking but I really don't know much about SSH and don't want to break the server...

    Best regards,
    Lloyd
     
    #8 markuswest, Dec 10, 2014
  9. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,309
    Likes Received:
    393
    Trophy Points:
    583
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Yes, it could be.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #9 Infopro, Dec 10, 2014
  10. markuswest

    markuswest Active Member

    Joined:
    Oct 21, 2013
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi @Michael, Infopro & keat63

    With your advice [Michael] I have successfully migrated / copied the large mysql database via SSH, and then completed the migration on the old.website.com.

    Thank you for the help, I couldn't do it without you guys.

    Best Regards,
    Lloyd
     
    #10 markuswest, Dec 10, 2014
  11. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,309
    Likes Received:
    393
    Trophy Points:
    583
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Good news then! Thanks for posting back. :)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #11 Infopro, Dec 10, 2014
(You must log in or sign up to post here.)
Loading...
Similar Threads - copy migrate large
  1. Tweetytoon

    Copy incoming and outgoing emails?

    Tweetytoon, Aug 8, 2018, in forum: E-mail Discussion
    Replies:
    3
    Views:
    69
    cPanelMichael
    Aug 9, 2018
  2. Rakaris Bakaris

    SOLVED Hide copyright message from php cli output

    Rakaris Bakaris, Jun 5, 2018, in forum: cPanel Developers
    Replies:
    3
    Views:
    113
    cPanelMichael
    Jun 6, 2018
  3. rpvw

    SOLVED Copy Paste in FileManager Editor Issue

    rpvw, May 16, 2018, in forum: User Experience
    Replies:
    9
    Views:
    210
    cPanelMichael
    May 21, 2018
  4. alias harun

    SOLVED [CPANEL-20499] Transfers fail when "Copy Home Directory" is deselected

    alias harun, May 16, 2018, in forum: Data Protection
    Replies:
    5
    Views:
    468
    cPanelMichael
    May 23, 2018
  5. Srinath S M

    Copying the Account level filter over to Global filter

    Srinath S M, May 2, 2018, in forum: E-mail Discussion
    Replies:
    3
    Views:
    152
    cPanelLauren
    May 3, 2018

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice