Results 1 to 22 of 22
-
01-17-2004, 05:38 PM #1
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.sqlLast edited by Akash; 01-18-2004 at 12:55 AM.
-
01-17-2004, 05:41 PM #2Web Hosting Master
- Join Date
- Jan 2001
- Location
- Illinois, USA
- Posts
- 7,175
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
-
01-17-2004, 09:51 PM #3
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.
-
01-18-2004, 12:55 AM #4Web Hosting Master
- Join Date
- Jan 2001
- Location
- Illinois, USA
- Posts
- 7,175
How's that look to you
-
01-18-2004, 01:02 AM #5
looks good See, now that I subscribed to the thread I got notified.. Methinx there's a buggy of some kind in splitting threads
Tom Whiting, WHMCS Guru extraordinaire
Linux problems? WHMCS Problems? Give me a shout
Check out my WHMCS Addons
-
01-18-2004, 07:55 PM #6Web Hosting Evangelist
- Join Date
- Jul 2002
- Posts
- 500
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 .
-
06-24-2004, 12:15 AM #7
lol, a few months later I finally run across this:
tar -cf backupName.tar /var/lib/mysql/database_name
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.Tom Whiting, WHMCS Guru extraordinaire
Linux problems? WHMCS Problems? Give me a shout
Check out my WHMCS Addons
-
06-25-2004, 08:35 PM #8Newbie
- Join Date
- Jun 2004
- Posts
- 11
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!
-
07-05-2004, 11:17 PM #9Disabled
- Join Date
- Apr 2001
- Posts
- 184
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.
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.
-
07-24-2004, 06:47 AM #10Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,016
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
-
07-24-2004, 07:05 AM #11Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,016
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
-
07-27-2004, 03:12 AM #12Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,016
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 serverWhen everything seems under control, you're just not going fast enough — Mario Andretti, Racer
-
07-28-2004, 09:52 PM #13Newbie
- Join Date
- Jul 2004
- Location
- Ann Arbor, MI
- Posts
- 17
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?
-------------------------------------------
http://www.jmrtechnet.com
-
08-02-2004, 02:26 PM #14Junior Guru Wannabe
- Join Date
- Jan 2002
- Posts
- 30
Better methods
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.
-
08-02-2004, 03:25 PM #15Newbie
- Join Date
- Jul 2004
- Location
- Ann Arbor, MI
- Posts
- 17
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.-------------------------------------------
http://www.jmrtechnet.com
-
08-03-2004, 03:34 AM #16Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,016
Code:mysqldump -ux -px database | ssh me@newhost "mysql -ux -px database"
Code:me@newhost
Thanks Ledjon!When everything seems under control, you're just not going fast enough — Mario Andretti, Racer
-
08-03-2004, 03:38 PM #17Junior Guru Wannabe
- Join Date
- Jan 2002
- Posts
- 30
me@newhost would be your username and the ip address or hostname of the new server you're putting it on. It may be something like
sitename@1.2.3.4
or anything. Just username@hostname (or ip address) is the format. See the ssh man page for details (run 'man ssh' from your ssh session)
-
01-24-2005, 09:39 AM #18Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
If you have SSH access on both servers, then I would recommend that you use the second method posted by ledjon (great snippet).
In addition, if you have SSH access on your new host, use scp to transfer files, as it provides a bit of extra security.
Format for scp (which stands for secure copy) is :
scp sourcefile user@hostname:/path/to/remote/file
If you want to transfer /home/myuser/secret.txt to the home directory of your account named fred on my.otherbox.com then this is the command you would want :
scp /home/myuser/secret.txt fred@my.otherbox.com:~
You can also transfer files from two different servers (assuming you have SSH access on both servers):
scp me@server1.com:~/myfile.txt me@server2.com:~
Maybe it will help someone
-
04-03-2005, 08:51 PM #19Newbie
- Join Date
- Nov 2004
- Posts
- 23
Using the parameters -e -f -q -Q -K for mysqldump a good idea and will make the insert faster and more secure.
Look them up in the docs!http://DNSDigger.com - Shows what other sites is hosted on that IP/host/range.
-
07-13-2005, 04:35 AM #20Newbie
- Join Date
- Jul 2005
- Posts
- 10
If you want to keep two databases equal, you can use rsync (if your system supports it) to do it by creating a simple script:
Code:rsync you@yourserver::share/path/to/mysql/data/* /path/on/the/other/server/
This way, whenever your database change, rsync will find the difference and would transfer ONLY the bytes that have changed.
To give you an idea, I have 3 servers with about 100 databases each that I backup using the above method - the entire process (on a daily basis) takes only 30 minutes to finish.
Glosk
-
07-13-2005, 10:01 AM #21Junior Guru Wannabe
- Join Date
- Jan 2002
- Posts
- 30
That's a good idea, except that if you copy the data file (or its differences) while mysql is actively writing to it, you can end up with a corrupted data file. If you're using all MyISAM tables, this can usually be fixed with a 'repair table', but that can take a long time per-table depending on the size of the table, and may still fail in the end as well.
Jon Coulter
-
07-13-2005, 12:51 PM #22
Rsync is not a good idea for SQL databases, for the above mentioned reason. If the server is still running, and you try to send data through, then you're bound to corrupt at least one database. Even IF it's shut down, you still run the risk of something going wrong.
mysqldump is ALWAYS the best option around for transferring sql data from A to B. Using the | method is good, but not so if you're going to deal with a lot of sql based data in most cases. A lot CAN and usually will go wrong, it's best to just throw the sql output into a file and ftp it over to the new (or other) server.Tom Whiting, WHMCS Guru extraordinaire
Linux problems? WHMCS Problems? Give me a shout
Check out my WHMCS Addons