Results 1 to 22 of 22
  1. #1
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,135
    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
    Last edited by Akash; 01-18-2004 at 12:55 AM.

  2. #2
    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

  3. #3
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,135
    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.

  4. #4
    Join Date
    Jan 2001
    Location
    Illinois, USA
    Posts
    7,175
    How's that look to you

  5. #5
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,135
    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

  6. #6
    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 .

  7. #7
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,135
    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.
    Tom Whiting, WHMCS Guru extraordinaire
    Linux problems? WHMCS Problems? Give me a shout
    Check out my WHMCS Addons

  8. #8
    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!

  9. #9
    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.

  10. #10
    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

  11. #11
    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

  12. #12
    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 server
    Whats with it?
    When everything seems under control, you're just not going fast enough — Mario Andretti, Racer

  13. #13
    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

  14. #14
    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.

  15. #15
    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

  16. #16
    Join Date
    Mar 2004
    Posts
    1,016
    Code:
    mysqldump -ux -px database | ssh me@newhost "mysql -ux -px database"
    Is that the correct command ? (fcourse I've to pu my info in there)

    Code:
    me@newhost
    I'm not sure what does this mean?

    Thanks Ledjon!
    When everything seems under control, you're just not going fast enough — Mario Andretti, Racer

  17. #17
    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)

  18. #18
    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

  19. #19
    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.

  20. #20
    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/
    that you call with a cron job every 10 minutes/1 hour/1day/...

    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

  21. #21
    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

  22. #22
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •