Source:Troubleshooting MySQL

MySQL Backup

MySQL backup fails with mysqldump: Error 2013: Lost connection to MySQL server during query

Problem

• MySQL backup fails with

mysqldump: Error 2013: 
Lost connection to MySQL server during query

Possible cause

• This error typically occurs when backing up MySQL databases to tape devices. The reason for this is the MySQL setting net_write_timeout, which defaults to a value of 60 seconds. It can take longer than 60 seconds to open the tape, but after 60 seconds MySQL database resets the connection while the backup is still in progress.

⇒ Solution

• Open the option file /etc/my.cnf and set a higher value for the variable net_write_timeout in section [mysqld] as follows:
[mysqld] ... other options .. net_write_timeout = 180
• Close the file and restart your mysql daemon for changes to take effect. If a restart is not an option, the setting can also be applied online, via the mysql command:
# mysql -u root -p -e "set global net_write_timeout=180;" Keep in mind that this setting will reset to its original value during restart if it is not saved in the configuration file.
• If you have raised the value to 180 seconds, but backups are still failing with the same error message, try to use a higher timeout value. The timeout can get exceeded, for example, if the tape gets swapped during the MySQL backup. Note that some tape libraries require more than 180 seconds to handle the tapes.
The current active value can be determined as follows:

# mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'net_write_timeout'"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_write_timeout | 60    |
+-------------------+-------+

Problems during MySQL backup if user's password contains special characters – use configuration file to store the password

Problem

• There may be problems during backup if the password of the user, assigned for executing the backups, contains special characters. In general, the use of apostrophes as well as mutated vowels (umlauts) is suppressed in the GUI.

⇒ Solution

• Store the password (recommended for all MySQL backups) in a special configuration file rather than a task. If a password is stored separately in the configuration file, it will not appear in SEP sesam logging. See 4.2.3.3. Using Option Files for a complete overview about MySQL configuration files on any supported platform. The following example is suitable for Unix and Linux:

• Create a configuration file named my.cnf. In case SEP sesam runs as user root and to ensure that the SEP sesam Client can read it correctly, store this file in
/root/.my.cnf
• Enter the following parameters into the file (the entry user is optional):
[client] user=root password=mysqlpw
• The backup tasks options must include the user name if not set:
-a user=username The password will now be read from the configuration file during the backup.

Shell login of user root is permitted (i.e. on Ubuntu systems), but the variable $HOME does not exist

Problem

• If the variable $HOME does not exist, then the file my.cnf cannot be found.

⇒ Solution

• Edit the file <sesam-root>/var/ini/sm.ini and add the following lines at the bottom of the file:
[ENVIRONMENT] HOME=/root Once you added the required variable, restart SEP sesam service for changes to take effect.

See also

MySQL