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.

How to copy / migrate a large mysql database between accounts

Discussion in 'Database Discussions' 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:
    37,064
    Likes Received:
    1,287
    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.
     
    #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:
    875
    Likes Received:
    25
    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:
    875
    Likes Received:
    25
    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:
    15,617
    Likes Received:
    296
    Trophy Points:
    433
    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.
     
    #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:
    15,617
    Likes Received:
    296
    Trophy Points:
    433
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Yes, it could be.
     
    #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:
    15,617
    Likes Received:
    296
    Trophy Points:
    433
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Good news then! Thanks for posting back. :)
     
    #11 Infopro, Dec 10, 2014
(You must log in or sign up to post here.)
Loading...
Similar Threads - copy migrate large
  1. mindnetcombr

    Legacy restore, why copy twice?

    mindnetcombr, Aug 15, 2017 at 9:11 PM, in forum: Data Protection
    Replies:
    11
    Views:
    63
    cPanelMichael
    Aug 18, 2017 at 1:44 PM
  2. RafaelEbarola

    Block Gmail carbon copy on cPanel

    RafaelEbarola, Jul 27, 2017, in forum: E-mail Discussions
    Replies:
    5
    Views:
    93
    cPanelMichael
    Aug 1, 2017
  3. jhkoning

    Forward email without keeping a copy?

    jhkoning, Jul 13, 2017, in forum: E-mail Discussions
    Replies:
    5
    Views:
    97
    Infopro
    Jul 14, 2017
  4. George23

    How to configure a filter for blind copy from one domain user to another one?

    George23, Jun 28, 2017, in forum: E-mail Discussions
    Replies:
    3
    Views:
    112
    cPanelMichael
    Jun 30, 2017
  5. AndyB78

    Copyright protecting a plugin

    AndyB78, Jun 10, 2017, in forum: cPanel Developers
    Replies:
    3
    Views:
    126
    Infopro
    Jun 10, 2017

Share This Page