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!
  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:
    42,734
    Likes Received:
    1,706
    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:
    933
    Likes Received:
    32
    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:
    933
    Likes Received:
    32
    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,066
    Likes Received:
    348
    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:
    16,066
    Likes Received:
    348
    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:
    16,066
    Likes Received:
    348
    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. Oluwatosin

    Copy home directory and database to another server?

    Oluwatosin, Apr 19, 2018 at 2:57 AM, in forum: Data Protection
    Replies:
    1
    Views:
    36
    cPanelMichael
    Apr 19, 2018 at 11:00 AM
  2. webmasteryoda

    High CPU when copying large files

    webmasteryoda, Mar 31, 2018, in forum: General Discussion
    Replies:
    6
    Views:
    76
    cPanelMichael
    Apr 16, 2018 at 8:40 AM
  3. zaenkhilji

    cPanel server copy another cPanel

    zaenkhilji, Mar 20, 2018, in forum: Bind / DNS / Nameserver Issues
    Replies:
    1
    Views:
    108
    cPanelMichael
    Mar 20, 2018
  4. pbixdesign

    Copy a database from one account to another account by ssh

    pbixdesign, Jan 14, 2018, in forum: Database Discussions
    Replies:
    4
    Views:
    235
    cPWilliamL
    Jan 15, 2018
  5. daemoncesar

    Database copy creating prefixes error

    daemoncesar, Dec 13, 2017, in forum: Database Discussions
    Replies:
    4
    Views:
    191
    daemoncesar
    Dec 13, 2017

Share This Page