Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Programming Tutorials : Move a MySQL Database from one host to another via SSH

[linux-tech]

It's actually rather easy to do through ssh:

Login to old host:
Commands:
First, dump the database to a file.
mysqldump database -uusername -ppassword > database.sql

then open an FTP session to your new host:

ncftp -uuser new-host

then put the file dump on the new server. Navigate to any appropriate directory first:

put database.sql

Exit twice, first to close the FTP session, and the SSH session:
exit
exit

ssh to new host (make sure the database actually exists!)

mysql database -uusername -p < database.sql
or simply mysql database -uusername -p
enter password
source database.sql

[Akash]

I know - I was hoping a generous community member like yourself would be willing to post a How-To to get us started.

I split your thread - if you could please go back and clean it up a bit (removing, my quote, your sig, and making it a bit more newbie friendly).

All your guys' efforts will be greatly appreciated with this new addition.

edit: congrats on hitting 1000+ posts

[linux-tech]

Was wondering where that went
Unfortunately I can't remove the sig this late in the game (60 minute limit), oddly enough it didn't notify me about the post in here, otherwise I'd have been on it sooner, sorry, I didn't expect the thread to be split like it was, was just following up on a request


Feel free to edit to remove those if you want.

[Akash]

How's that look to you

[linux-tech]

looks good See, now that I subscribed to the thread I got notified.. Methinx there's a buggy of some kind in splitting threads

[loopforever]

An additional method, if you have root access, is to simply:

tar -cf backupName.tar /var/lib/mysql/database_name

And then FTP. I prefer this method, since there's a few less steps to go through.

It also isn't a bad idea to repair tables before backing them up:

cd /var/lib/mysql/databaseName
myisamchk -r *.MYI

Just my input .

[linux-tech]

lol, a few months later I finally run across this:

tar -cf backupName.tar /var/lib/mysql/database_name

This is actually not the best way to do this. In fact, I encourage people to use the sqldump method, as this method will complicate things:

If you do use this method:
on the second server, don't forget you have to chown -R mysql:mysql /var/lib/mysql/database_name after decompressing it
In addition, you HAVE to restart mysql to use this
service mysql stop
service mysql start

To me, mysqldump is 10x more efficient and less intrusive on other users on the server.

[herrvinny]

Thanks for this. Before I saw this, I was just going to use phpMyAdmin to export the DB and then use the restore feature of phpMyAdmin on the other host.

Thanks again!

[wscreate]

Originally posted by wolfstream
lol, a few months later I finally run across this:

This is actually not the best way to do this. In fact, I encourage people to use the sqldump method, as this method will complicate things:

If you do use this method:
on the second server, don't forget you have to chown -R mysql:mysql /var/lib/mysql/database_name after decompressing it
In addition, you HAVE to restart mysql to use this
service mysql stop
service mysql start

To me, mysqldump is 10x more efficient and less intrusive on other users on the server.

I have always used the tar method to backup the database and then move it. I just untar it in the new mysql directory on the new server and then grant permissions to the user/pass/host and all is well. Quite simple.

1. I have never had to restart mysql after granting permissions.
2. tar -cf preserves mysql.mysql ownership and it is not necessary to chown anything.

[openXS]

Its not quite easy for a newbie to use. Can anyone please revise this and put it step-by-step. I want to transfer about 900MB of MySQL Data using this method, but its difficult for me to understand

Thanks

[openXS]

I'm trying to make it easier, lemme know how it looks.

I suppose the DB Username is "searchxs", Database name is "searchxsdb" db password is "searchxspass" and cpanel account name is "searchxsacct" domain is "searchxs.com" and mysql bacup file is "searchxsbackup.sql"


Login to Old host via SSH:

# mysqldump --opt -usearchxs -psearchxspass dbsearchxsdb > /home/searchxsacct/searchxsbackup.sql

Now, Login to the New Server via SSH:

# wget http://www.searchxs.com/searchxsbackup.sql

Done, now your DB has been transferred from yoru Old Server to your new server. You can import it via phpMyAdmin now.

[Someone please correct me if I'm wrong]

Hope this helps

[openXS]

Wolfstream,

I get an error:

server1# mysqldump searchxs_dbname -usearchxs_dbuser -ppass > /home/sear
chxs/database.sql
mysqldump: Got error: 1030: Got error 127 from table handler when retrieving data from server

Whats with it?

[jasonr33]

instead of SSH, if your server has a shared SSL license, you could use that to transfer your info via HTTP/SSL, using phpMyAdmin... eh?

[ledjon]

Come on folks, we can do better then these methods!

Here are 2 new ways:

1) Load directly from dump. This assumes that you can access one of the server's mysql from the other server (which is rare, granted):

mysqldump -ux -px database | mysql -h newhost.com -ux -px database
... or the other way around
mysqldump -h oldhost.com -ux -px database | mysql -ux -px

2) The other way, which is even cooler, and more secure, assuming you have ssh access on both machines (this, again, is a direct dump-to-import):

mysqldump -ux -px database | ssh me@newhost "mysql -ux -px database"

This will dump the data to stdout, pipe it to ssh @ the newhost, and into mysql. Of course, replace all x's with username's and password.

Both these methods save you from having to store the databases on-disk for the move. I would recommend the 2nd method, as the first is not likely to work (not on any host that has secure db policies), plus it has the added security of the data being encrypted as it travels over the wire. You can also tell ssh to use compression to make it use even less bandwidth (and be faster); see ssh's man page for detail on that.

[jasonr33]

Ledjon,

YOU are sooooo right! I've used method 2 once... and only once.. before, and it works GREAT!

I think I had a brain freeze during my last reply, but just seeing your command line jogged my memory... a repressed memory at that.