How to copy / migrate a large mysql database between accounts

[markuswest]

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

 

[cPanelMichael]

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:

mysqldump database_name > /path/to/database_name.sql

You can then restore the database dump to the new database via:

mysql new_database_name < /path/to/database_name.sql

Note: The above commands assume you are logged in as "root".

Thank you.

 

[markuswest]

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

 

[keat63]

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.

 

[markuswest]

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.

 

[keat63]

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.

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.

 

[Infopro]

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.

 

[markuswest]

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

 

[Infopro]

Yes, it could be.

 

[markuswest]

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

 

[Infopro]

Good news then! Thanks for posting back.