How to load large SQL file into mysql

Question

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

It did not give me any feedback on the (lengthy) progress
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.

score 4 · Accepted Answer · answered Apr 19 '12 at 20:19

4

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

answered Apr 19 '12 at 20:19

wrangler

3,080
5
24
20

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
Is it generally OK to ctrl-c the command after executing it? I have a flashing cursor right under `[user@box] nohup: appending output to ‘nohup.out’` – a coder Jun 10 '20 at 22:30
Just answering my own newb question - it is absolutely OK to cancel out of that prompt. The process continued running as described in wrangler's answer. – a coder Jun 10 '20 at 22:44

score 2 · Answer 2 · answered Apr 19 '12 at 20:14

2

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

answered Apr 19 '12 at 20:14

johnshen64

5,865
24
17

score 1 · Answer 3 · answered Apr 19 '12 at 21:32

1

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

answered Apr 19 '12 at 21:32

jollyroger

1,650
11
19

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 08:03

score 1 · Answer 4 · answered Apr 20 '12 at 14:48

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

How to load large SQL file into mysql

4 Answers4