Source:Troubleshooting MySQL

Other languages:

Deutsch
English

MySQL

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 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.

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.