Moving mysql database

[lilcam]

What's the easiest way to move a mysql database between hosts.

[Jonathan]

I let a customer have a subdomain once;
when came time I received his payment (he was minior, had to mail $$)
I set up his domain; I used cPanel to backup (NOT phpMyAdmin !)
and when I tried to use it to restore on the domain (via cPanel)
it seemed to freeze up, and after submitting a trouble ticket Andrew
said it was an "invalid" cPanel backup.

So -- my Q is "How could cPanel make a backup thats an INVALID cPanel backup??"

I've yet to think (or find) a good answer, so I usually save
it "uncompressed" as a .SQL file; so far no need to attempt
to restore a backup, so I do not know if it'll work,
but so far it seems to be the best route....

[-Oz-]

Use phpmyadmin...

Then with the exported file you can either reupload it if its small enough or you can use SSH to add all the data.

[Buddha]

So -- my Q is "How could cPanel make a backup thats an INVALID cPanel backup??"

Has cPanel backup ever worked? I can remember someone (Andrew I think) warning, over a year ago, it didn't work. cPanel needs to stop adding features and fix what's broken.

[sdjl]

If the database is quite large, PHPMyAdmin can freeze up or not transfer all of the database over. Here's how i transfer large databases:

First step is to make a copy of the database to a .sql file. This is all done via SSH command line:

Login to SSH, then navigate to the folder where you want the backup to be placed
e.g. cd.. /public_html/mysql_backup/

Once there, run the following command to backup the database. Change the options that are in CAPS:

mysql -u USERNAME -p -D DATABASENAME > databasedump.sql

This will ask you to enter your MySQL database username password. Do so.
The file should then be located in the folder you specified in the first command.

With the database now downloaded as a .sql file, you can then either ftp down to your local computer or ftp from the server to the next server.
I'll assume you're going to download the file, if you want to know how to use SSH to FTP the file to another server, just ask.

Once you have uploaded the file to your new host, you'll need to login via SSH again and run the following command to import the database:

mysql -u USERNAME -p -D DATABASENAME < databasedump.sql

That should work nice and quickly. Any problem, just yell

David

[reviewum.com]

Has cPanel backup ever worked? I can remember someone (Andrew I think) warning, over a year ago, it didn't work. cPanel needs to stop adding features and fix what's broken.

Guess what... about a year ago I did a Cpanel backup / restore and it worked perfectly... but I haven't done it since. I'm not sure if it was here (Dathorn) that I did it, or at my previous host, but I remember being surprised that it worked as well as it did... especially for CPanel! :-)

Like I said, it was a while ago, but I think I had to create the DB in Cpanel and THEN restore the db file... it was something odd like that.

Good luck! I'm interested in what the final verdict is. I don't have enough MySQL db background to do some serious testing myself!

[avenuex]

Hiya,

Actually, I just finished moving over a dozen clients from my older server to dathorn. I used CPanel backup for nearly all the sites. It worked rather flawlessly (rather, since there was one minutae problem)

Cpanel can backup/restore databases fine. But it doesn't restore the DATABASE USER OR PASSWORD... so I had to do some digging into each nuke config and program to find the username password and restore them after i've setup the database restore.

if there's a easier way, i'd be happy to use it

[Telfie]

Another method is to use something like mysqlFront.

You will need to log into SSH and then set up mySQLFront to access both your Hosting accounts. Then transfer the info from x to y.

As mentioned earlier, you will need to create the database and the user ID/ pass before doing this with the added requirement of either adding your IP address or wildcard to phpmyAdmin on both host accounts.

Once this is completed, you can transfer all the tables across to the new location.

It is also good for taking backups of your data and placing it locally when you are doing development work so you can see what is live on the site and mimic any issues with the same data.

[Jonathan]

First step is to make a copy of the database to a .sql file. This is all done via SSH command line:

Login to SSH, then navigate to the folder where you want the backup to be placed
e.g. cd.. /public_html/mysql_backup/

n00blet Q = How do I navigate to a folder in SSH?
I.e., /home/user/public_html/

When I just typed that in, it said "(stuff I typed) is a folder"

Eh; evility....

[KyleC]

I am sure you can find a guide to commands somewhere on google.

but its somewhat like DOS

DIR is directory listing

bash-2.05a$ cd public_html/ goes to ur public_html DIR.
bash-2.05a$ cd goes back to user/