move mysql db between servers - Printable Version

move mysql db between servers - Printable Version

+- Linux-Noob Forums (https://www.linux-noob.com/forums)
+-- Forum: Linux Server Administration (https://www.linux-noob.com/forums/forum-8.html)
+--- Forum: LAMP (https://www.linux-noob.com/forums/forum-83.html)
+--- Thread: move mysql db between servers (/thread-2638.html)

move mysql db between servers - znx - 2005-05-21

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)

move mysql db between servers - anyweb - 2005-05-21

thanks !

moved to the correct area and pinned :)

cheers

anyweb

move mysql db between servers - xDamox - 2005-05-22

Good tutorail :)very usful

move mysql db between servers - znx - 2005-05-23

Quote:moved to the correct area and pinned :)

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

Quote:Good tutorail :)very usful

cheers :)

move mysql db between servers - znx - 2005-08-25

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