Home / server / Questions / 381287
Accepted
Goro
Asked: 2012-04-20 12:03:14 +0800 CST

How to load large SQL file into mysql

  • 772

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.

mysql database windows-7 database-administration

4 Answers

  1. Best Answer

    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

    • 3

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

    • 2

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

    • 1

  4. 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
    • 1