diff --git a/src/docs/user/configuration/configuring_backups.diviner b/src/docs/user/configuration/configuring_backups.diviner --- a/src/docs/user/configuration/configuring_backups.diviner +++ b/src/docs/user/configuration/configuring_backups.diviner @@ -50,11 +50,54 @@ Restore: MySQL ============== -To restore a MySQL dump, just pipe it to `mysql` on a clean host. (You may need -to uncompress it first, if you compressed it prior to storage.) +To restore a MySQL dump, just pipe it to `mysql` on a clean host. $ gunzip -c backup.sql.gz | mysql +If you have a large amount of data, you may need to adjust the configuration +of your MySQL server in order for this process to work. Some of the most common +issues you may need to identify and resolve are: + +**Max Allowed Packet**: The MySQL configuration option `max_allowed_packet` may +limit the maximum size of statements which can be executed. + +If the import fails because of a packet limit issue, the failure may take the +form of an error message like one of these in the client: + +``` +ERROR 2020 (HY000) at line X: Got packet bigger than 'max_allowed_packet' bytes +ERROR 2006 (HY000) at line X: MySQL server has gone away +``` + +To resolve this, increase this limit temporarily by passing +`--max-allowed-packet 1GB` to `mysql`. + +**InnoDB Log File Size**: Large import statements may fail if the data size +is large relative to the combined InnoDB log file size, governed primarily +by `innodb_log_file_size`. + +If the import fails because of a log size issue, the failure may take the form +of an error message like this in the client: + +``` +ERROR 2006 (HY000) at line X: MySQL server has gone away +``` + +The server log may have an error message which is more illuminating, like this +one: + +``` +XXXXXX XX:XX:XX InnoDB: ERROR: the age of the last checkpoint is X, +InnoDB: which exceeds the log group capacity X. +InnoDB: If you are using big BLOB or TEXT rows, you must set the +InnoDB: combined size of log files at least 10 times bigger than the +InnoDB: largest such row. +``` + +To resolve this, increase the size of `innodb_log_file_size` in your MySQL +configuration. A setting of `8GB` should allow you to import any dump, although +this is a relatively high value and may not be ideal for production workloads. + Backup: Hosted Repositories ===========================