Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a database backup of a MySQL database that is about 250MB. I made it with mysqldump

I was trying to load it on another server like so:

mysql -u xxxx -pxxxx data_mirror < dbdump.sql

I was not satisfied with this process because

  1. It did not give me any feedback on the (lengthy) progress
  2. It failed with a "Server went away" message

What method would you recommend for backing up and restoring large MySQL database?

I am doing this on Windows 7 - based servers.

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted

The reason you got a "Server went away" is because your terminal session timed out. Use nohup to prevent the process from being interrupted, like so:

nohup mysql -u xxxx -pxxxx data_mirror < dbdump.sql &

Note: the ampersand means the process runs in the background. To track the state of the running process, simply tail the nohup.out file that gets created:

tail -f nohup.out

share|improve this answer
    
Alas, I am on Windows! :( –  Goro Apr 19 '12 at 20:23
2  
open a powershell window and use start-job comdlet in windows 7, the effect is similar –  johnshen64 Apr 19 '12 at 20:43
add comment

use LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. this should be much faster as there is no sql parsing involved.

share|improve this answer
add comment

Using Percona Xtrabackup for quite a long time on different flawors of Linux. Unfortunately, it's in alpha for Windows.

share|improve this answer
    
Are you seriously suggesting using an alpha version (of anything) for production? –  John Gardeniers Apr 19 '12 at 23:28
1  
Surely not. I want other people to know about this software which works in production for *NIX so in case there would not be restriction on windows people still could use advices from this thread. –  jollyroger Apr 20 '12 at 8:03
add comment

Large backup ? I've restored data from 10+ GB compressed SQL dumps ...

There is no way to have a progress indicator, by the very (non-linear) nature of the SQL dump. And there is almost no correlation between the dump size and the actual disk utilization by the MySQL server.

The 'server went away' seems to indicate that something is dropping the connection between your mysql client and server. Either the MySQL server crashed (or ran into unbereable slowness), or check your network if yor server is remote (some firewalls are painfull with long lived TCP connections).

Note that using compressed SQL dumps is faster, since it requires less I/O for reading the actual dump. Eg:

mysqldump the_db | gzip > the_db.sql.gz  # backup
zcat the_db.sql.gz | mysql the_db        # restore
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.