2013 lost connection to mysql server at reading initial communication packet system error 0

I’m having an issue connecting to my local MySQL database using Python’s MySQLdb library. The script has been working well previously, but I will occasionally get the MySQL error in the title. There seems to be no explanation for when the error occurs, and the script is always run from the same machine with the same arguments.

The MySQL server is running as a service on Windows XP SP3 using port 3306 (locally hosted phpMyAdmin works), and the script is run from an Ubuntu 10.04 guest operating system in Oracle VM VirtualBox.

I am currently working around this issue by opening a command prompt and executing ‘net stop MySQL’ then ‘net start MySQL’. This allows me to run the script a few times again before resulting in the error, which I’ve been fixing by restarting the MySQL service.

As I am still making changes to the script, there are occasions when the script raises an exception and doesn’t exit gracefully, though I do catch the exception and close the cursor and connection.

The code to connect to the database:

def __init__(self):
  try:
    print "Connecting to the MySQL database..."
    self.conn = MySQLdb.connect( host = "192.168.56.1",
                                 user = "guestos",
                                 passwd = "guestpw",
                                 db = "testdb")
    self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
    print "MySQL Connection OK"
  except MySQLdb.Error, e:
    print "MySQLdb error %d: %s" % (e.args[0],e.args[1])
    raise

The full error generated when this happens is as follows:

MySQLdb error 2013: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
Traceback (most recent call last):
  File "search.py", line 45, in <module>
    dataHandler = DataHandler()
  File "/home/guestos_user/workspace/Search/src/data_handler.py", line 25, in __init__
    db = "testdb")
  File "/usr/lib/pymodules/python2.6/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 170, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 0")

How to Solve Error Message ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

The error described in the title of this article is quite unique. Several attempts on solving the problem exist in other articles in the web. But in the context of this article, there is a specific condition that is quite unique so in the end it cause the error occurs. The error occurs with the specific error message. That error message is in the following output message :

The above error message is a consequence of connecting to a MySQL Database Server. It is actually a regular MySQL Database Server running in a machine. But the connection itself is a different one. The connection exist using docker container running process. Below is the actual running process of that docker container :

There are already lots of article discuss abot this error. For an example in this link in the stackoverflow or in this link , and another one in this link , furthermore in this link . The general problem is actually the same. There is something wrong in the running process of the normal MySQL Database Server.

Step for solving the problem

There are several steps for solving the problem above. There are two parts for solving the problem. The first part is for detecting the root cause of the problem. Later on, the second part is the actual solution taken for solving the root cause of that problem. So, the following section which is the first part will focus on trying to search for the cause of the problem.

Searching the cause of the problem

In the case of this article, the following is the steps for solving the error :

1. Check whether the MySQL Database Server process is actually running. Execute it as follows using any command pattern available in the operating for checking a running process. In the context of this article, it is ‘systemctl status mysql’. So, the following is an example for the execution of the command pattern :

2. Before connecting to MySQL Database Server using a different port listening to any incoming requests where it is a docker container process handling, just test the normal connection. In other words, connect using the normal port listening in the machine for any incoming connection to MySQL Database Server. Normally, it exists in port ‘3306’. Do it as follows :

The above error message is where the actual root problem is. Check for the actual file which is representing the socket file for mysql daemon process as follows :

Apparently, according to the above output, the file doesn’t exist. That is why the connection to MySQL Database Server is always failed. Eventhough the connection process is done through the default port of ‘3306’.

3. Try to restart the process and hope that it will solve the problem.

4. Unfortunately, the above process also ends in failure. Continue on the step for solving the problem, just check the MySQL Database configuration file. After checking the file configuration, apparently, it doesn’t fit at all. Eventually, spending hours for changing the configuration files, nothing happens.

For the cause happens above, check the correct configuration before to see which MySQL Database Server configuration used by the running MySQL Database Server.

Checking the MySQL Database Server configuration used by the running MySQL Database Server

In the previous section or part, there is a need to search for the actual configuration file used by the running MySQL Database Server. It is just to make sure that the configuration file used is the correct one. So, every change can bring the right impact on solving the error problem. Below is the step for searching for it :

1. Check the list of the service first by referring to the running process. In the previous part, the running process is the ‘mysql’ one. Execute the following command pattern to list the available running process :

The output of the above command pattern for an example is in the following one :

2. Next, check the content of the service by executing the following command. Choose the correct service, in this context, it is ‘mysql.service’ :

3. Apparently, the file responsible for starting the service is in the file ‘/usr/share/mysql/mysql-systemd-start’ according to the output message above. The following is the content of that file which is only part of it :

4. After checking the content of the file ‘/etc/mysql/my.cnf’, apparently it is not the correct file. So, in order to be more accurate, there are another way to find out the configuration file used by the running MySQL Database Server. The reference or the information exist in this link. So, according to the information in that link, just execute the following command pattern to get the right one. It is for getting the process ID and also the right MySQL Database Server running process :

After getting the right running process, do the following command of ‘strace file_name_process’ :

The following is part of the output of the command :

Источник

Потерянное соединение с сервером MySQL при «считывании исходного пакета связи», системная ошибка: 0

Я получаю ошибку:

«Потерянное соединение с сервером MySQL при чтении исходного пакета связи, системная ошибка: 0»

пока я собираюсь подключить свой db.

Если я использую localhost, все работает нормально. Но когда я использую свой живой IP-адрес, как показано ниже, он получает ошибку:

ОТВЕТЫ

Ответ 1

Кто-то здесь предполагает, что это может быть проблема с брандмауэром:

Я только что столкнулся с этой проблемой и обнаружил, что это мой брандмауэр. Я использую PCTools Firewall Plus, и он не разрешал полный доступ к MySQL. Однажды я изменил, что это было хорошо. Надеюсь, это поможет.

Может ли это быть?

Кроме того, кто-то здесь предполагает, что это может быть связано с тем, что сервер MySQL связан с IP-адресом обратной связи (127.0.0.1/localhost), который эффективно отключает вас от подключения извне.

Если это так, вам нужно загрузить скрипт на веб-сервер (который, вероятно, также работает на сервере MySQL) и сохранить хост сервера как «localhost».

Ответ 2

Откройте файл конфигурации mysql с именем my.cnf и попробуйте найти «bind-address», замените параметр (127.0.0.1 или localhost) на ваш IP-адрес в реальном времени (ip, который вы используете в функции mysql_connect)

Это решит проблему определенно.

Ответ 3

1) Разрешить удаленное подключение к MySQL. Редактировать файл:

2) Создайте пользователя для удаленного подключения.

3) В моем случае мне нужно подключиться удаленно от Windows к машине VirtualBox с помощью Ubuntu. Поэтому мне нужно разрешить порт 3306 в iptables:

Ответ 4

При возникновении этой проблемы при настройке нового подчиненного сервера. Было обнаружено, что IP-адрес подчиненного сервера отсутствовал на главном сервере /etc/hosts.allow . Добавлен IP-адрес, и он позволяет мне подключиться к главному серверу.

Обратите внимание, что я использую hosts.allow и hosts.deny для управления доступом.

Ответ 5

У меня была эта проблема, и в конечном итоге это был предыдущий sys admin, изменивший порт MySQL. MySQL Workbench пытался подключиться к 3306 по умолчанию, но сервер работал на 20300.

Ответ 6

Проблема в моем случае заключалась в том, что MySQL привязывался только к lo on linux. для решения проблемы я отредактировал файл my.cnf(найденный в /etc/mysql/my.cnf), удалив строку bind-address = 127.0.0.1

это позволяет mysql связываться с любым сетевым интерфейсом

Ответ 7

Эта ошибка возникла при попытке подключения к Google Cloud SQL с помощью MySQL Workbench 6.3.

После небольшого исследования я обнаружил, что мой IP-адрес был изменен интернет-провайдером, и он не был разрешен в Cloud SQL.

Я разрешил это и вернулся к работе.

Ответ 8

Проблема для меня заключалась в том, что DNS-запросы были заблокированы FW в подсети. Решение заключалось в отключении DNS-запросов в MySQL.

Ответ 9

Ошибка означает, что он не получил ответ от порта, на который он ожидал найти сервер. Причины могут быть связаны с неправильной машиной (по одному из нескольких причин), чтобы сервер не находился на ожидаемом порту.

Проверьте, к какому порту подключен ваш сервер в /etc/mysql/my.cnf. Соответствует ли это тому, что находится в вашем заявлении connect. Если они совпадают, попробуйте подключиться к mysql с самого сервера и из командной строки машины, на которой вы запускаете клиент. Если он работает, это одно место, а не другое, то может возникнуть проблема с настройкой брандмауэра/маршрутизатора.

Ответ 10

Я просто установил mysql в окне окна. Я получил ошибку OP при попытке установить соединение с клиентом Navicat MySql в том же поле. Я должен был указать 127.0.0.1 в качестве хоста, и он получил его.

localhost, или фактический IP-адрес сервера не работал.

Ответ 11

Я столкнулся с этой же ошибкой при подключении из MySQL Workbench. Вот как я это исправил. В моем файле конфигурации /etc/my.cnf значение привязанного адреса было установлено на IP-адрес сервера. Это должно было быть сделано для настройки репликации. Во всяком случае, я решил это, выполнив две вещи:

1. создать пользователя, который можно использовать для подключения с адреса привязки в файле my.cnf

1. измените значение имени хоста MySQL в деталях соединения в рабочей среде MySQL, чтобы соответствовать адресу привязки

Ответ 12

В моем случае у меня было ВСЕ: ВСЕ в hosts.deny. Изменение этого на ВСЕ: PARANOID решил мою проблему при подключении через ssh

Ответ 13

Для меня работала настройка bind-address = 0.0.0.0 в mysql/my.cnf . Он в основном прослушивает все адреса (но еще один порт).

И не забудьте перезагрузить сервер: systemctl restart mysql

Ответ 14

Проблема была для меня довольно глупой.

Я использовал эту проблему на машине AWS EC2 Ubuntu (на данный момент MariaDB установлен локально), поэтому я попытался выполнить туннелирование SSH и имел ту же проблему. Поэтому я попытался выполнить туннель ssh через терминал:

И он сказал мне это:

Пожалуйста, войдите в систему как пользователь «ubuntu», а не пользовательский «root».

Я изменил ssh-пользователя с root на ubuntu, как и на мою конфигурацию ssh, и он подключился просто отлично.

Итак, проверьте, подключен ли пользователь SSH.

Я наблюдал за этим, так что это слишком полчаса моего времени, поэтому я надеюсь, что это будет полезно для вас.

Ответ 15

Перейдите в эту же проблему, привяжите адрес туда и обратно безрезультатно. Решение для меня было привилегии очистки.

Ответ 16

У меня была такая же проблема, но в моем случае я решил ее с помощью

Ответ 17

Для меня был найден файл конфигурации «/etc/mysql/mysql.conf.d/mysqld.cnf», комментирующий адрес привязки сделал трюк.

Как мы видим здесь: Вместо skip-networking по умолчанию теперь прослушивается только localhost, который более совместим и не менее безопасен.

Ответ 18

При подключении к Mysql удаленно, я получил ошибку. У меня было это предупреждение в /var/log/mysqld.log :

Я просто добавил эту строку в файл /etc/hosts :

Проблема решена! Не использование skip-name-resolve вызвало некоторые ошибки в моем локальном приложении при подключении к MySQL.

Ответ 19

У меня была идентичная проблема. Чтобы исправить это, я просто сменил хост с localhost: 3306 на просто localhost. Таким образом, ошибка может возникать при отключении несовместимого порта для подключения. Лучше оставить его по умолчанию.

Ответ 20

Реестр чтения и записи каталога базы данных также является проблемой, которую я нашел. Просто убедитесь, что ваше приложение может rw файлы в местоположении db. Попробуйте chmod 777 для тестирования.

Ответ 21

Я сделал ниже 3 шага, затем работал на меня.

bind-address = «YOUR MACHINE IP» в файле my.cnf адресу /etc/my.cnf

Перезапустите службу командой: service httpd restart

GRANT ALL PRIVILEGES ON yourDB.* TO ‘username’@’YOUR_APPLICATION_IP’ IDENTIFIED BY ‘YPUR_PASSWORD’ WITH GRANT OPTION;

Ответ 22

Брандмауэр apache блокирует ip-адрес. поэтому для предоставления доступа используйте следующие команды:

firewall-cmd -permanent —zone = trusted -add-source = YOUR_IP/32

firewall-cmd -permanent —zone = trusted -add-port = 3306/tcp

Ответ 23

Я столкнулся с такой же проблемой. Я проверил и попытался установить AllowTcpForwarding Да, но он отсутствовал в моем sshd_config, поэтому никакой помощи. Я не изменил sshd_config или my.cnf. Убедитесь, что имя ssh не совпадает с именем mysql (используйте localhost).

В workbench выберите + для добавления нового подключения и установите следующее:

• метод подключения: стандартный TCP/IP через SSH
• SSH Имя хоста: 192.168.0.50:22 (замените удаленный SSH-сервер IP и порт (необязательно))
• SSH Имя пользователя: sshuser
• Вы можете установить пароль или добавить в приглашении
• Имя хоста MYSQL: localhost или 127.0.0.1
• Порт сервера MYSQL: 3306
• Вы можете установить пароль или добавить в приглашении

Проверить соединение. Он должен быть успешным, тогда нажмите ОК. Виола!

Ответ 24

Еще одна причина.

Я столкнулся с сервером Ubuntu, где все было настроено и не смог подключиться из-за той же ошибки.

Этот параметр был внутри /etc/ssh/sshd_config

После превращения в

Я был в состоянии удаленно подключиться к моей базе данных MySQL

Ответ 25

Если bind-адрес отсутствует в вашем файле конфигурации и mysql размещен на экземпляре AWS, проверьте свою группу безопасности. В идеальных условиях входящие правила должны принимать все соединения от порта 3306, а исходящее правило должно отвечать на все действительные IP-адреса.

Ответ 26

Я пытаюсь подключить мой док-контейнер db на Ubuntu 18.04, та же проблема.

Сначала проверьте ваше устройство, запустив nmcli dev чтобы проверить, подключено ли устройство docker0 .

Если он не подключен, попробуйте перезапустить службу Docker:

sudo service docker restart

Ответ 27

Я использую Windows, и моя проблема все еще сохраняется. Я не хочу использовать локальную БД MySQL, я пытаюсь подключиться к моему веб-сайту БД MySQL. Любая идея?

База данных: website_db Имя пользователя: website_un Пароль: 12A_B56 сервер: website.ws порт: 3306

В «cPanel-> Удаленный MySQL» я добавил IP-адрес своего ПК.

Источник

Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.

Is your database restore stuck with MySQL Error 2013 (hy000)?

Often queries or modifications in large databases result in MySQL errors due to server timeout limits.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix MySQL Error 2013 (hy000) for our customers.

Why this MySQL Error 2013 (hy000) happens?

While dealing with MySQL, we may encounter some errors. Today, we are going to discuss one such error.

This MySQL 2013 error occurs during a restore of databases via mysqldump, in MySQL replication, etc.

This error appears when the connection between MySQL client and database server times out.

In general, this happens in databases with large tables. As a result, it takes too much time for the query to return data and the connection drops with an error.

Other reasons for the error include a large number of aborted connections, insufficient server memory, server restrictions, etc.

How do we fix MySQL Error 2013 (hy000)?

The fix for MySQL Error 2013 (hy000) depends a lot on the triggering reason. Let’s now see how our MySQL Engineers help customers solve it.

1. Changing MySQL limits

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to connect with MySQL server.

So, our Engineers checked in detail and found that the connect_timeout value was set to only a few seconds. So, we increased it to 10 in the MySQL configuration file. For that, we followed the steps below:

Firstly, we opened the MySQL configuration file at /etc/mysql/my.cnf

Then, we searched for connect_timeout and set it as:

connect_timeout=10

Then we tried connecting with MySQL server and we were successful.

Additionally, it requires the proper setting of the variable max_allowed_packet in the MySQL configuration file too. While trying to restore the dump file in GB sizes, we increase the value to a higher one.

2. Disable Access restrictions

Similarly, this error also appears when the host has access restrictions. In such cases, we fix this by adding the client’s IP in /etc/hosts.allow or allow it in the server firewall.

Also, the error can happen due to the unavailability of the server. Recently, in a similar instance, the problem was not related to MySQL server or MySQL settings. We did a deep dig and found that high network traffic is causing the problem.

When we checked we found that a weird process running by the Apache user. So, we killed that and this fixed the error.

3. Increasing Server Memory

Last and not least, MySQL memory allocation also becomes a key factor for the error. Here, the server logs will have related entries showing the insufficient memory limit.

Therefore, our Dedicated Engineers reduce the innodb_buffer_pool size. This reduces the memory allocation on the server and fixes the error.

[Need assistance with MySQL errors – We can help you fix it]

Conclusion

In short, we discussed in detail on the causes for MySQL Error 2013 (hy000) and saw how our Support Engineers fix this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Quite recently, I had a following problem when connecting to MySQL server over SSH tunnel (in this case, client application was Navicat):

2013 - Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Possible causes:

1. SSH daemon does not allow port forwarding (most likely).
2. Hosts.deny/hosts.allow or iptables (less likely).

SSH server port forwarding

Edit /etc/ssh/sshd_config and make sure, there’s following line:

AllowTcpForwarding yes

Some of the system hardening guides disallow usage of this directive and some of sysadmins actually read those guides

Hosts.deny, hosts.allow and iptables

In case of above issues you most likely have no remote access to the machine, so tunneling MySQL connection won’t work either. Access control based on hosts files is checked in the following way:

• First, /etc/hosts.allow file is checked. If a rule placed there matches incoming connection, access is granted and further processing stops.
• Then, /etc/hosts.deny file is checked. If a rule denying access matches incoming connection, access is denied and further processing stops.
• If none of matching rules are found, access is granted to given service.

Note: configuration of hosts.allow and hosts.deny REQUIRES to have an empty line at the end of hosts.allow and hosts.deny.

So, log in locally and check:

/etc/hosts.deny

Hosts.deny and hosts.allow files are the most basic access control features of Linux machine. Typical usage of these files is denying all daemons from accepting connections from all possible sources, then enabling only services that really need to be accessed from the network. Hosts file based access control mechanism is very simple and effective, although it is not that widely used. Many of administrators prefer to use iptables firewall rules due to much higher flexibility.

Either way, a file that is used to deny access to your host is below. Default content of this file on Debian Squeeze:

# /etc/hosts.deny: list of hosts that are _not_ allowed to access the system.
#                  See the manual pages hosts_access(5) and hosts_options(5).
#
# Example:    ALL: some.host.name, .some.domain
#             ALL EXCEPT in.fingerd: other.host.name, .other.domain
#
# If you're going to protect the portmapper use the name "portmap" for the
# daemon name. Remember that you can only use the keyword "ALL" and IP
# addresses (NOT host or domain names) for the portmapper, as well as for
# rpc.mountd (the NFS mount daemon). See portmap(8) and rpc.mountd(8)
# for further information.
#
# The PARANOID wildcard matches any host whose name does not match its
# address.
#
# You may wish to enable this to ensure any programs that don't
# validate looked up hostnames still leave understandable logs. In past
# versions of Debian this has been the default.
# ALL: PARANOID
#

Adding ALL:ALL to that file will prevent any incoming connections to all of your services. Basic configuration pattern here is following:

[daemon]:[src 1],[src 2],...[src n]

Where [daemon] can be a listening service or wildcard (for example, ALL), and [src n] variables refer to remote machine trying to connect.

So, denying access to all services from all origins will look like:

# /etc/hosts.deny: list of hosts that are _not_ allowed to access the system.
#                  See the manual pages hosts_access(5) and hosts_options(5).
#
# I cut original comments here.
#
ALL:ALL
#

/etc/hosts.allow

Now, that we have followed our rule to deny all access and open our host only to several services that need to be open, we should edit /etc/hosts.allow to allow remote administration of our system:

# /etc/hosts.allow: list of hosts that are allowed to access the system.
#                   See the manual pages hosts_access(5) and hosts_options(5).
#
# I cut the original comments here.
#
# Allow access from 10.1.0.20 to all services
ALL:10.1.0.20
# Allow 10.1.0.15 to connect to ssh server
sshd:10.1.0.15
# Allow 10.1.0.16 to all services except for ssh server
ALL EXCEPT sshd:10.1.0.16
# Allow 10.1.0.1/24 (entire LAN with 255.255.255.0 netmask) to connect to print server
cupsd: 10.1.0.1/24
#

iptables

Iptables is used to create firewall rules on many Linux machines. Full configuration directives and methodology standing behind that is way beyond scope of this article, so let’s focus on enabling SSH access from a single machine.

Many systems have default iptables DROP policy, similar to ALL:ALL in /etc/hosts.deny file. It’s purpose is the same, as we want to deny all incoming traffic and then open several services to certain group of remote computers.
Typing iptables -L or iptables-save in terminal while logged in as root user will produce currently applied rules, similar to:

root@host:~# iptables-save
# Generated by iptables-save v1.4.8 on Wed Aug 15 16:30:20 2012
*nat
:PREROUTING ACCEPT [4846375:527813970]
:POSTROUTING ACCEPT [99547:5973129]
:OUTPUT ACCEPT [99547:5973129]
COMMIT
# Completed on Wed Aug 15 16:30:20 2012
# Generated by iptables-save v1.4.8 on Wed Aug 15 16:30:20 2012
*filter
:INPUT DROP [2031370:285099881]
:FORWARD DROP [0:0]
:OUTPUT ACCEPT [4167661:1116594356]
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -s 10.1.0.15/32 -p tcp -m tcp --dport 22 -j ACCEPT

-A FORWARD -o lo -j ACCEPT
-A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
COMMIT
# Completed on Wed Aug 15 16:30:20 2012

root@host:~# iptables -L
Chain INPUT (policy DROP)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:www
ACCEPT     tcp  --  10.1.0.15            anywhere            tcp dpt:ssh

Chain FORWARD (policy DROP)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Both outputs state the same: server accepts incoming connections to www (port 80) and a single host (10.1.0.15) can connect to ssh (port 22). In order to achieve similar results, type as root:

iptables -A INPUT -p tcp --dport 22 -s 10.1.0.15 -j ACCEPT

to enable SSH for  10.1.0.15, and:

iptables -A INPUT -p tcp --dport 80 -j ACCEPT

to enable world-wide access to www service.

Links

1. http://www.solarum.com/2012/04/10/navicat-ssh-tunnel-error-2013-lost-connection-to-mysql-server/
2. http://linux.die.net/man/5/hosts.allow

I am trying to use HAProxy as a load balancer for 2 mysql nodes. I have HAProxy listening on 3307 and it routes requests to the DBs on 3306. When the client is trying to connect to the Proxy (mysql -u … -h .. -P3307 -p…) it gets the following error:

ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system >error: 0

When the client is trying to connect directly to the DB it works fine. I have no idea why its failing when traffic goes via the Proxy.

Here’s what I’ve already checked:

1. /etc/hosts.allow includes «mysqld: ALL : allow »

2. MySQL config file (my.cnf) doesn’t have a bind address line (open to
   all).

I am using HAProxy 1.5dev, Mysql 5.1.x and the whole enchilada is running in ec2 on Amazon linux.

Here’s the HAProxy config file:

    global
     log 127.0.0.1   local0
     log 127.0.0.1   local1 notice
     #log loghost    local0 info
     user haproxy
     group haproxy
     maxconn 4096
     daemon
     #debug
     #quiet

    defaults
     log     global
     mode    tcp
     option  tcplog
     option  dontlognull
     retries 3
     option redispatch
     maxconn 4096
     contimeout      5000
     clitimeout      50000
     srvtimeout      50000

    frontend mysql_cluster
     bind 10.0.0.150:3307
     default_backend mysql_cluster

    backend mysql_cluster
     mode tcp
     balance roundrobin
     option tcpka
     option httpchk
     server lb1 10.0.0.140:3306 check port 9200 inter 5s rise 2 fall 2
     server lb2 10.0.0.214:3306 check port 9200 inter 5s rise 2 fall 2 

    listen stats 10.0.0.150:8081
     mode http
     option httpclose
     balance roundrobin
     stats uri /
     stats realm Haproxy Statistics
     stats auth ***:***

Has anyone tackled this problem before? Any ideas how to solve this?

Any help much appreciated

The misstep depicted in the title of this article is entirely outstanding. A couple of attempts on handling the issue exist in various articles on the web. However, in error 2013 you lost connection to MySQL server during a query, concerning this article, there is a specific condition that is exceptionally novel so in the end, it causes the error to occur. The error occurs with the specific error message. That error message is in the going with yield message:

• root@hostname ~# mysql – uroot – p – h 127.0.0.1 – P 4406
• Enter secret key:
• Error 2013 (HY000): Lost relationship with MySQL server at ‘examining initial correspondence bundle’, system error: 0
• root@hostname ~#

The above error message is a result of partner with a MySQL Database Server. It is a standard MySQL Database Server running on a machine. However, the real connection is a substitute one. The connection exists using Worker holder running collaboration. Coming up next is the reliable running course of that Worker holder:

• root@hostname ~# netstat – tulpn | grep 4406
• tcp6 0:4406: * LISTEN 31814/worker-go-between
• root@hostname ~#

There are at this point lots of articles analyze about this mix-up. For a model in this association in the stack overflow or this association and one more in this association, besides in this association. The general issue is truly something almost identical. There is something misguided in the running arrangement of the regular MySQL Database Server.

Why this happens

This misstep appears when the relationship between your MySQL client and database server times out. Essentially, it took unreasonably long for the request to return data so the connection gets dropped.

By far most of my work incorporates content migrations. These activities for the most part incorporate running complex MySQL requests that burn through a huge lump of the day to wrap up. I’ve found the WordPress wp_postmeta table especially hazardous considering the way that a site with countless posts can without a very remarkable stretch have two or three hundred thousand post meta sections. Joins of enormous datasets from such tables can be especially genuine.

Avoid the issue by sanitizing your requests

Generally speaking, you can avoid the issue absolutely by refining your SQL questions. For example, instead of joining all of the substance of two especially immense tables, have a go at filtering through the records you needn’t waste time with. Where possible, have a go at reducing the amount of partakes in a singular inquiry. This should have the extra benefit of simplifying your inquiry to examine. For my inspirations, I’ve found that denormalizing content into working tables can deal with the read execution. This avoids breaks.

Re-making the inquiries isn’t, by and large, another option so you can effort the going with server-side and client-side workarounds.

A server-side course of action

If you’re ahead for your MySQL server, make a pass at changing a couple of characteristics. The MySQL documentation proposes extending the net_read_timeout or connect timeout values on the server.

The client-side course of action

You can extend your MySQL client’s sever regards on the possibility that you don’t have exclusive induction to the MySQL server.

MySQL Worktable

You can adjust the SQL Editor tendencies in MySQL Work Table:

• In the application menu, select Edit > Preferences > SQL Editor.
• Quest for the MySQL Session portion and augmentation the DBMS connection read break regard.
• Save the settings, very MySQL Work Table, and return the connection.

Step for handling the issue

There are a couple of stages for handling the issue above. There are two segments for handling the issue. The underlying portion is for perceiving the principal driver of the issue. Later on, the ensuing part is the genuine plan taken for tending to the fundamental driver of that issue. Thusly, going with the region which is the underlying portion will focus on power to search for the justification behind the issue.

Glancing through the justification behind the issue

Because of this article, coming up next is the means for settling the mix-up

1. Check whether the MySQL Database Server measure is truly running. Effect it as follows using any request plan open in the working for truly taking a gander at a running connection. Concerning this article, it is ‘systemctl status MySQL. Thusly, coming up next is a model for the execution of the request plan:

• root@hostname ~# systemctl status MySQL
• service – MySQL Community Server
• Stacked: stacked (/lib/systemd/structure/mysql.service; horrible; vendor preset: engaged)
• Dynamic: dynamic (running) since Mon 2019-09-16 13:16:12; 40s back
• Cycle: 14867 ExecStart=/usr/sbin/mysqld – demonize – pid-file=/run/mysqld/mysqld. Pid (code=exited, status=0/SUCCESS)
• Connection: 14804 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
• Guideline PID: 14869 (mysqld)
• Tasks: 31 (limit: 4915)
• Group:/system. Slice/mysql.service
• └─14869/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
• root@hostname ~#

1. Preceding partner with MySQL Database Server using a substitute port focusing on any moving toward requesting where it is a worker compartment measure dealing with, basically test the regular connection. By the day’s end, the partner using the normal port tuning in the machine for any moving toward a relationship with MySQL Database Server. Normally, it exists in port ‘3306’. Do it as follow:

• root@hostname ~# mysql – uroot
• Goof 2002 (HY000): Can’t interface with neighborhood MySQL server through connection ‘/var/run/mysqld/mysqld. Sock’ (2)
• root@hostname ~#

The above screw-up message is where the genuine root issue is. Check for the genuine report which is tending to the connection record for MySQL daemon measure as follows:

• root@hostname ~# disc/var/run/mysqld/
• root@hostname ~# ls
• Pid MySQL. sock MySQL. sock. Lock
• root@hostname ~#

As shown by the above yield, the report doesn’t exist. That is the explanation the relationship with MySQL Database Server is continually failed. Even though the connection cooperation is done through the default port of ‘3306’.

1. The effort to restart the cooperation and trust that it will handle the issue.

• root@hostname ~# systemctl stop MySQL
• root@hostname ~# systemctl start MySQL
• root@hostname ~# mysql – uroot
• Slip-up 2002 (HY000): Can’t interface with neighborhood MySQL server through connection ‘/var/run/mysqld/mysqld. Sock’ (2)
• root@hostname ~#

1. Unfortunately, the above cycle moreover wraps up in disappointment. Progress forward the movement for handling the issue, just check the MySQL Database arrangement record. In the wake of really investigating the report course of action, it doesn’t fit in any way shape, or form. Eventually, going through hours for changing the arrangement records, nothing happens.

For the reason happens above, check the right game plan before to see which MySQL Database Server arrangement is used by the running MySQL Database Server.

How might we fix MySQL Error 2013 (hy000)?

The fix for MySQL Error 2013 (hy000) depends a ton upon the setting off reason. We should now see how our MySQL Engineers help customers with settling it.

1. Changing MySQL limits

Lately, one of our customers pushed toward us saying that he is getting a mix-up as the one showed underneath while he is efforting to interface with the MySQL server.

Along these lines, our Engineers checked thoroughly and found that the connect timeout regard was set to two or three minutes. Thusly, we extended it to 10 in the MySQL arrangement record. For that, we followed the means underneath:

First thing, we opened the MySQL plan archive at, etc/MySQL/my.cnf

Then, we searched for connect timeout and set it as:

• connect timeout=10

Then, we had a go at partner with MySQL server and we were viable.

Additionally, it requires the genuine setting of the variable max_allowed_packet in the MySQL arrangement record also. While efforting to restore the landfill record in GB sizes, we increase the value to a higher one.

2. Disabled person Access limits

This slip-up in like manner appears when the host approaches impediments. In such cases, we fix this by adding the client’s IP in, etc/hosts. Allow or license it in the server firewall.

Similarly, the error can happen as a result of the detachment of the server. Lately, in a similar case, the issue was not related to MySQL server or MySQL settings. We did a significant tunnel and found that high association traffic is causing the issue.

Exactly when we checked we found that an unconventional communication running by the Apache customer. Thusly, we killed that, and this good misstep.

3. Growing Server Memory

Last and not least, MySQL memory apportioning furthermore transforms into a basic factor for the slip-up. Here, the server logs will have related segments showing the lacking memory limit.

Subsequently, our Dedicated Engineers decline the innodb_buffer_pool size. This reduces the memory segment on the server and fixes the slip-up.

Checking the MySQL Database Server configuration used by the running MySQL Database Server

In the past fragment or part, there is a need to search for the real plan report used by the running MySQL Database Server. It is essential to guarantee that the plan record used is the right one. Thusly, every change can invite the right impact on dealing with the mix-up issue. Coming up next is the movement for searching for it:

1. Truly check out the once-over of the assistance first by suggesting the running framework. In the past part, the running framework is the ‘MySQL one. Execute the going with request guide to list the open running cycle:

• systemctl list-unit-reports | grep MySQL

The yield of the above request plan for a model is in the going with one:

• user hostname: ~$ systemctl list-unit-archives | grep MySQL
• service horrendous
• Service horrendous
• user hostname: ~$

1. Then, at that point, truly investigate the substance of the help by executing the going with the request. Pick the right help, in this particular circumstance, it is ‘MySQL. service’:

• user hostname: ~$ systemctl cat myself. service
• #/lib/system/structure/Mysql.service
• # MySQL systemd organization record
• [Unit]
• Description=MySQL Community Server
• After=network. Target
• [Install]
• Wanted by=multi-user. Target
• [Service]
• Type=forking
• User=mysql
• Group=mysql
• PIDFile=/run/mysqld/mysqld. Pid
• PermissionsStartOnly=true
• ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
• ExecStart=/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
• Timeouts=600
• Restart=on-dissatisfaction
• Runtime Directory=mysqld
• RuntimeDirectoryMode=755
• LimitNOFILE=5000
• user hostname: ~$

1. The record obligated for starting the help is in the archive ‘/usr/share/MySQL/MySQL-systems-start’ according to the yield message above. Coming up next is the substance of that record which is only fundamental for it:

• if [! – r, etc/MySQL/my.cnf]; then,
• resonation “MySQL arrangement not found at, etc/MySQL/my.in. Assuming no one minds, make one.”
• leave 1
• fi
• …..

1. Resulting in truly taking a gander at the substance of the report ‘/, etc/MySQL/my.on, obviously, it isn’t the right record. Accordingly, to be more exact, there are other ways to deal with find the planned archive used by the running MySQL Database Server. The reference or the information exists in this association. Hence, according to the information in that association, basically perform the going with request guide to get the right one. It is forgetting the cycle ID and the right MySQL Database Server running collaboration:

• root@hostname ~# netstat – tulpn | grep 3306
• tcp6 0:3306: * LISTEN 21192/mysqld
• root@hostname ~# ps aux | grep 21192
• root@hostname ~# ps aux | grep 21192
• mysql 21192 0.2 0.1 3031128 22664? Sl Sep16 1:39/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
• root 25442 0.0 23960 1068 pts/20 S+ 01:41 0:00 grep 21192
• root@hostname ~#
• Ensuing to getting the right running cycle, do the going with the request of ‘trace file_name_process’:
• root@hostname ~# album/usr/bin/
• root@hostname ~# strace. /mysqld
• Coming up next is fundamental for the yield of the request:
• detail, etc/my.cnf”, 0x7fff2e917880) = – 1 ENOENT (No such archive or vault)
• detail, etc/mysql/my.cnf”, {st_mode=S_IFREG|0644, st_size=839, …}) = 0
• openat (AT_FDCWD, “/, etc/mysql/my.cnf”, O_RDONLY) = 3
• fstat (3, {st_mode=S_IFREG|0644, st_size=839, …}) = 0
• brk(0x35f6000) = 0x35f6000
• read (3, “#n# The MySQL data base server co”…, 4096) = 839
• openat (AT_FDCWD, “/, etc/mysql/conf. d/”, O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4
• fstat (4, {st_mode=S_IFDIR|0755, st_size=4096, …}) = 0
• get dents (4, /* 4 entries */, 32768) = 120
• get dents (4, /* 0 entries */, 32768) = 0
• close (4) = 0
• detail, etc/mysql/conf.d/mysql.cnf”, {st_mode=S_IFREG|0644, st_size=629, …}) = 0
• openat (AT_FDCWD, “/, etc/mysql/conf.d/mysql.cnf”, O_RDONLY) = 4
• fstat (4, {st_mode=S_IFREG|0644, st_size=629, …}) = 0
• read (4, “[mysqld]nn# Connection and Three”…, 4096) = 629
• read (4, “”, 4096) = 0
• close (4) = 0
• detail, etc/mysql/conf.d/mysqldump.cnf”, {st_mode=S_IFREG|0644, st_size=55, …}) = 0
• openat (AT_FDCWD, “/, etc/mysql/conf.d/mysqldump.cnf”, O_RDONLY) = 4
• fstat (4, {st_mode=S_IFREG|0644, st_size=55, …}) = 0
• read (4, “[MySQL dump] nquicknquote-namesnma”…, 4096) = 55
• read (4, “”, 4096) = 0
• close (4) = 0
• read (3, “”, 4096) = 0
• close (3) = 0
• detail (“/root/.my. cnf”, 0x7fff2e917880) = – 1 ENOENT (No such record or list)

The right one is finally in ‘/, etc/MySQL/conf.d/mysql.cf. Resulting in truly investigating the substance of the record, it is an empty archive. This is its essential driver. There has been some update and inverse present type of the MySQL Database Server, it making some disaster area the MySQL Database Server. The plan is essentially to fill that empty plan record with the right arrangement. The reference for the right arrangement of MySQL Database Server exists in this association. Restart the MySQL Server again, the above error issue will be tended to.