Mysqldump Backup Big Data error

Mysqldump Backup Big Data error Technology

Maybe yes posted on 2015-01-26 22:58

Original link: http://blog.lmlphp.com/archives/72 from: lmlphp backyard

The data of the website is backed up regularly, now the data is big, the Mysqldump method is estimated to be out of the way, and fails to start the backup after the last location. Error content: Mysqldump:error 2013:lost connection to MySQL server during query when Dumping table ' table name ' at row:23699.

Lost connection to MySQL server, when using mysqldump (especially when backing up to NFS), many people are "mysqldump:got error:2013:lost connection to MYSQ L server during query when dumping table "is a problem, there are some simple explanations for this problem in manual.
When backing up to NFS, the flow of data is as follows: The MySQL Server retrieves data from the data file, and then returns the data to the Mysqldump client in batches, and then Mysqldump writes the data to NFS. Generally, the speed of writing data to NFS is much slower than server-side retrieval of data, which causes mysqldump to not accept data sent by the server in a timely manner, the server side of the data will be backlog in memory waiting to be sent, this wait is not indefinite, when The Server waits longer than Net_write_timeout (default is 60 seconds) when it loses patience, mysqldump connection is disconnected, and error Got Error:2013:lost connection is thrown.
Adding net_write_timeout can solve the above problems. In practice, it is found that the server side consumes more memory after increasing net_write_timeout, and sometimes even the use of swap (not sure if it is modified net_write_timeout). It is recommended that you modify net_write_timeout to a larger value (such as 1800) before mysqldump, and at the end of the mysqldump, change this value to the default of 60.
Lost connection to MySQL server during query error
There are many reasons for this error
Personal experience thinks that first try these two parameters, most of them are caused by this reason:
Bind-address = 127.0.0.1
Skip-name-resolve
Either of the two parameters is OK.
In other words, you can reconnect MySQL if you encounter a 2006,2013 error.
MySQL level, you need to configure some parameters my.cnf
Wait_timeout = x Time Out
Max_allowed_packet = y Maximum allowable data volume
Generally this is not the case for all the sentences but a single table, please fix the table can generally solve such problems
Backups do not take place at a time when the database is under pressure, and the early morning backup is more appropriate
If it is a transactional engine (InnoDB), it is recommended to use the--single-transaction parameter, which can make the lock table time very short.

The above is the practice estimate is not feasible, the site on the virtual host, modify the MySQL configuration is not possible. The MySQL website also has a similar report http://bugs.mysql.com/bug.php?id=47702. For the time being, you can only use the--ignore-table=<database>.<table> option to ignore larger tables without backing it up.

Read (69) reviews (0) View comments

Mysqldump Backup Big Data error