I want to make backups every day automatically. It was fine when database was 700-800Mb, but now database (dumped sql file) is 1.5G and when using mysqldump it errors on random table with 2013 error.

When I comment out my setting of innodb_buffer_pool_size, mysqldump works perfectly.

Is there nice way to set to defaults innodb_buffer_pool_size before mysqldump and then back to 1600M?

p.s. I'm using mysqldump as following

mysqldump --max_allowed_packet=500M --net_buffer_length=548576 --single-transaction?
  • Please show the text of the 2013 error. – Rick James Apr 23 '17 at 19:07
  • How much RAM do you have? If less than 4GB, 1600M is too big! – Rick James Apr 23 '17 at 19:09
  • 2GB. In some article was saying you should place 80-90% to it – jsHate Apr 25 '17 at 7:02

Setting innodb_buffer_pool_size is dynamic in MySQL 5.7.

The default value for innodb_buffer_pool_size is 128M (134217728).

All you need to do is the following:

mysql -uroot -p... -ANe"SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 128;"
mysqldump ...
mysql -uroot -p... -ANe"SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1600;"

There may be bit of a pause depending on the number of dirty pages in the buffer pool and the active transactions using them.

| improve this answer | |

90% is dangerously high for any server. For a "tiny" 2G, a much lower 500M should be safe. Swapping is terrible for performance. But, things are worse -- --max_allowed_packet=500M is taking up a terribly large amount of RAM. Try 50M. If that gives an error, then raise it and lower the buffer_pool.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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