Backing Up and Moving a MySQL Database

http://www.devshed.com/c/a/MySQL/

Backing-up-and-restoring-your-MySQL-Database/1/

Some good information there about MySQL database, moving them, compressing, etc. The most challenging thing about the information available online is understanding whether people are talking about username/passwords that are specifc to the database or the server. I will attempt to be clear on this subject.

Here’s the link that helped me the most: http://www.webhostingtalk.com/archive/thread/520657-1.html

Backing up the database on the local server. The local server being the one you are transferring the database from and the remote server being the one you want to transfer and restore the database to.

mysqldump -u user_mysqluser -p mysqlpassword –databases database_name > backup.sql

user_mysqluser and mysqlpassword are the username and password specific to the local server’s MySQL datbase … and not the server’s login details.

One error message I received was “stdin: is not a tty error message” - turns out to just be a warning and the backup to the local server worked fine.

Now I want to copy the backup.sql to the remote server. The examples from the first link above didn’t do it for me. This link was the most helpful for copying to the remote machine, http://www.webhostingtalk.com/archive/thread/520657-1.html .

You want to use the scp command - a secure version of the RCP command (”Remote Copy”). The sytax from your local server to your remote server should looks like this:

scp -C /root/backup.sql root@111.111.111.111:/root

This assumes the remote server’s I.P. address is 111.111.111.111 - replace with your remote server’s I.P.

Now login into the shell of the remote server (which means this is no longer the remote server - but we will still refer to it that way). Now remember I mentioned that understanding which username / password to use is tricky. Pay attention.

Here’s the command to restore - or create for that matter - the same database that was on the local server. Important, the username and password to use are not your MySQL username / password. You want to use the server login username / password.