move mysql db between servers

[znx]

So if alter hosting how do you transfer all those megs of MySQL databases...

 

First off you need to make a backup of the database(s). This can be done with various web clients however the command line is just as easy to use. So get into a shell on the hosting company (most its done via ssh).

 

Note:

HOST = MySQL server name probably 'localhost'

USER = your MySQL username

DATABASE = the name of the database that you are backing up.

 

Code:

# mysqldump -h HOST -u USER -p DATABASE > database.sql
.. password request

 

Thats it. You now have a backup of your database. The file can be compressed if you want (sometimes this is a good idea if the connection is slow and the database large). If all you are doing is a backup, then you can store that .sql file somewhere safe.

 

Now to transfer it you can use ftp/sftp or scp, hey a CD if you really want ;) In this example I'll use ftp.

 

Code:

# ftp ftp.newwebhost.com
.. user/pass request
ftp> put database.sql
ftp> close
ftp> quit

 

Thats the database file over to the new host now. So logout of the old host and log into the new host, again ssh is the most likely.

 

SO, how you import into the new server (or restore after a dead). Remember the new host's user/pass etc should be used here. It is also important that the DATABASE exists, it doesn't need to be the same name as the previous but it must exist.

 

Code:

mysql -h HOST -u USER -p DATABASE < database.sql
... password request

 

The database file will be imported into the new database (or restored to the old one).

 

DONE! yay B)

[anyweb]

thanks !

 

moved to the correct area and pinned :)

 

cheers

 

anyweb

[xDamox]

Good tutorail :)very usful

[znx]

moved to the correct area and pinned :)

 

ty anyweb... missed the obvious [img]<___base_url___>/uploads/emoticons/default_rolleyes.gif[/img]

 

Good tutorail :)very usful

 

cheers :)

[znx]

need to cross link to this ---> neat script by Strabo which i missed when creating this .. ;)