Databases play a critical role in the proper working of any web applications. They store, organize and manage a large amount of user data.
Any problem with database connectivity can cause website errors. And the error show up as “sqlstate hy000 error 2002.”
At Bobcares, we specialize in fixing database errors for our customers as part of our Server Management Services.
Today, we’ll see various reasons for sqlstate hy000 error 2002 and how our Database Engineers fix it.
Examining sqlstate 2002 error
Before we dive into the solution, let’s first check the sqlstate 2002 error in detail.
In general, when we configure any PHP application, we specify the details of the database in the configuration file. The same is applicable in case of popular content management systems like WordPress, Joomla, Magento, etc. And, when we update or change any setting in the website, it gets saved on the database. For this to work, there should not be any problem with database connection with MySQL host server.
The database connection can fail due to reasons like wrong database details in the configuration file, port restrictions on the server, missing files, lack of resources on the server and so on.
In simple terms, MySQL error 2002 denotes a problem of connection between the website code and the database.
There are many variants of the sqlstate hy000 2002 error. The details are readily available in the second part of the error message.
From our experience in managing servers, our Dedicated Engineers see variants of sqlstate hy000 error 2002 as
Connection failed: SQLSTATE[HY000] [2002] Connection refusedConnection failed: SQLSTATE[HY000] [2002] No such file or directorySQLSTATE[HY000] [2002] Resource temporarily unavailable
exit status 3Again, a similar error on a Magento website appear as :
What causes sqlstate hy000 error 2002?
Now, let’s have a look on the possible causes for sqlstate hy000 error 2002.
1.MySQL server not running
In many of the cases where websites report sqlstate 2002 error, the underlying reason would be stopped MySQL server. And, this can happen due to broken MySQL libraries, resource crunch on the server, etc. When MySQL is not running on the server, the website will not be able to connect to the database and its reports “Connection refused error.
2. Incorrect database settings
Similarly, a major reason for sqlstate hy000 2002 error is the incorrect database settings in the configuration file. For the proper working of the program, the file should contain correct database server name, database user details and password.
If the MySQL service is listening on 127.0.0.1, the same settings should be given in the database connection string. Moreover, the MySQL server should accept connections on this IP address too.
3. Insufficient server resources
Last and not the least, insufficient server resources also can be a reason for MySQL errors. When the server has too many MySQL queries, it requires too much server memory. Additionally, when websites use resource intensive queries, it add to the server load and lack of memory on the server. It ends up in errors like “SQLSTATE[HY000] [2002] Resource temporarily unavailable.”
How we fix sqlstate hy000 error 2002
Let’s take a look on how our Dedicated Engineers helped one of our customers to fix the sqlstate hy000 error 2002.
The customer was using a PHP tracking script on his website. There was a high volume of MySQL activity (about 4 inserts a second), from this tracking script, and this was causing the MySQL Connection time out error. And, on the website it resulted in error:
Exception 'PDOException' with message 'SQLSTATE[HY000] [2002] Connection timed outIt was a sign of PHP script timing out while connecting to MySQL host.
1. Ensure MySQL running status
Here, we first checked the status of MySQL server. It was running fine on the server as per the snippet below.
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2019-03-09 15:25:43 xx; 2 weeks 5 days agoTherefore, we isolated the problem with MySQL server status.
2. Correcting Configuration file
As the second step, we checked and confirmed that the script was using correct Database details including Hostname, User and Password. Moreover, the website was showing data from the database too. Thus, the connection was happening. The error was happening only at certain intervals.
Additionally, we checked that MySQL database user was able to connect from the command line.
3. Fixing server resources
Since we already ruled out configuration errors, the next thing to check was the server resources. Here, our Dedicated Engineers checked the memory usage on the server. We found that MySQL memory usage was high in the server (around 89% of available memory).
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
30571 mysql 20 0 58.947g 0.055t 9832 S 135.1 89.2 16872:22 /usr/sbin/mysqld The memory usage statistics on the server looked as below.
We could see that there was a scope for MySQL tweaks in the server. Therefore, we analyzed the MySQL server performance by watching the MySQL queries and its impact on the server. Then our expert Database Engineers tweaked certain MySQL parameters such as join_buffer_size, table_open_cache and innodb_buffer_pool_instances.
After the tweaking, we restarted MySQL services at off-peak hours to minimize the impact on live websites. The performance on the MySQL server improved drastically and it fixed sqlstate hy000 error 2002 as well.
[Looking for tweaking MySQL server for improved performance? Our MySQL experts are available 24×7.]
Conclusion
In short, sqlstate hy000 error 2002 can happen due to stopped MySQL server, insufficient server resources, etc. Today we saw how our Dedicated Engineers troubleshooted and fixed MySQL errors for one of our customers.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
When you run MySQL queries, sometimes you may encounter an error saying you lost connection to the MySQL server as follows:
Error Code: 2013. Lost connection to MySQL server during query
The error above commonly happens when you run a long or complex MySQL query that runs for more than a few seconds.
To fix the error, you may need to change the timeout-related global settings in your MySQL database server.
Increase the connection timeout from the command line using –connect-timeout option
If you’re accessing MySQL from the command line, then you can increase the number of seconds MySQL will wait for a connection response using the --connect-timeout option.
By default, MySQL will wait for 10 seconds before responding with a connection timeout error.
You can increase the number to 120 seconds to wait for two minutes:
mysql -uroot -proot --connect-timeout 120
You can adjust the number 120 above to the number of seconds you’d like to wait for a connection response.
Once you’re inside the mysql console, try running your query again to see if it’s completed successfully.
Using the --connect-timeout option changes the timeout seconds temporarily. It only works for the current MySQL session you’re running, so you need to use the option each time you want the connection timeout to be longer.
If you want to make a permanent change to the connection timeout variable, then you need to adjust the settings from either your MySQL database server or the GUI tool you used to access your database server.
Let’s see how to change the timeout global variables in your MySQL database server first.
Adjust the timeout global variables in your MySQL database server
MySQL database stores timeout-related global variables that you can access using the following query:
SHOW VARIABLES LIKE "%timeout";
Here’s the result from my local database. The highlighted variables are the ones you need to change to let MySQL run longer queries:
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------------+----------+
To change the variable values, you can use the SET GLOBAL query as shown below:
SET GLOBAL connect_timeout = 600;
The above query should adjust the connect_timeout variable value to 600 seconds. You can adjust the numbers as you see fit.
Adjust the timeout variables in your MySQL configuration files
Alternatively, if you’re using a MySQL configuration file to control the settings of your connections, then you can edit the my.cnf file (Mac) or my.ini file (Windows) used by your MySQL connection.
Open that configuration file using the text editor of your choice and try to find the following variables in mysqld :
[mysqld]
connect_timeout = 10
net_read_timeout = 30
wait_timeout = 28800
interactive_timeout = 28800
The wait_timeout and interactive_timeout variables shouldn’t cause any problem because they usually have 28800 seconds (or 8 hours) as their default value.
To prevent the timeout error, you need to increase the connect_timeout and net_read_timeout variable values. I’d suggest setting it to at least 600 seconds (10 minutes)
If you’re using GUI MySQL tools like MySQL Workbench, Sequel Ace, or PHPMyAdmin, then you can also find timeout-related variables that are configured by these tools in their settings or preferences menu.
For example, in MySQL Workbench for Windows, you can find the timeout-related settings in Edit > Preferences > SQL Editor as shown below:
If you’re using Mac, then the menu should be in MySQLWorkbench > Preferences > SQL Editor as shown below:
If you’re using Sequel Ace like me, then you can find the connection timeout option in the Preferences > Network menu.
Here’s a screenshot from Sequel Ace Network settings:
For other GUI tools, you need to find the option yourself. You can try searching the term [tool name] connection timeout settings in Google to find the option.
And those are the four solutions you can try to fix the MySQL connection lost during query issue.
I hope this tutorial has been helpful for you 🙏
MySQL — система управления базами данных (СУБД) с открытым исходным кодом от компании Oracle. Она была разработана и оптимизирована специально для работы веб-приложений. MySQL является неотъемлемой частью таких веб-сервисов, как Facebook, Twitter, Wikipedia, YouTube и многих других.
Эта статья расскажет, как определять, с чем связаны частые ошибки на сервере MySQL, и устранять их.
Не удаётся подключиться к локальному серверу
Одной из распространённых ошибок подключения клиента к серверу является «ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)».
Эта ошибка означает, что на хосте не запущен сервер MySQL (mysqld) или вы указали неправильное имя файла сокета Unix или порт TCP/IP при попытке подключения.
Убедитесь, что сервер работает. Проверьте процесс с именем mysqld на хосте сервера, используя команды ps или grep, как показано ниже.
$ ps xa | grep mysqld | grep -v mysqldЕсли эти команды не показывают выходных данных, то сервер БД не работает. Поэтому клиент не может подключиться к нему. Чтобы запустить сервер, выполните команду systemctl.
$ sudo systemctl start mysql #Debian/Ubuntu
$ sudo systemctl start mysqld #RHEL/CentOS/FedoraЧтобы проверить состояние службы MySQL, используйте следующую команду:
$ sudo systemctl status mysql #Debian/Ubuntu
$ sudo systemctl status mysqld #RHEL/CentOS/Fedora
Если в результате выполнения команды произошла ошибка службы MySQL, вы можете попробовать перезапустить службу и ещё раз проверить её состояние.
$ sudo systemctl restart mysql
$ sudo systemctl status mysql
Если сервер работает (как показано) и вы по-прежнему видите эту ошибку, вам следует проверить, не заблокирован ли порт TCP/IP брандмауэром или любой другой службой блокировки портов.
Для поиска порта, который прослушивается сервером, используйте команду netstat.
$ sudo netstat -tlpn | grep "mysql"Ещё одна похожая и часто встречающаяся ошибка подключения — «(2003) Can’t connect to MySQL server on ‘server’ (10061)». Это означает, что в сетевом соединении было отказано.
Следует проверить, работает ли в системе сервер MySQL (смотрите выше) и на тот ли порт вы подключаетесь (как найти порт, можно посмотреть выше).
Похожие частые ошибки, с которыми вы можете столкнуться при попытке подключиться к серверу MySQL:
ERROR 2003: Cannot connect to MySQL server on 'host_name' (111)
ERROR 2002: Cannot connect to local MySQL server through socket '/tmp/mysql.sock' (111)Ошибки запрета доступа в MySQL
В MySQL учётная запись (УЗ) определяется именем пользователя и клиентским хостом, с которого пользователь может подключиться. УЗ может также иметь данные для аутентификации (например, пароль).
Причин для запрета доступа может быть много. Одна из них связана с учётными записями MySQL, которые сервер разрешает использовать клиентским программам при подключении. Это означает, что имя пользователя, указанное в соединении, может не иметь прав доступа к базе данных.
В MySQL есть возможность создавать учётные записи, позволяющие пользователям клиентских программ подключаться к серверу и получать доступ к данным. Поэтому при ошибке доступа проверьте разрешение УЗ на подключение к серверу через клиентскую программу.
Увидеть разрешённые привилегии учётной записи можно, выполнив в консоли команду SHOW GRANTS
Входим в консоль (пример для Unix, для Windows консоль можно найти в стартовом меню):
В консоли вводим команду:
> SHOW GRANTS FOR 'tecmint'@'localhost';Дать привилегии конкретному пользователю в БД по IP-адресу можно, используя следующие команды:
> grant all privileges on *.test_db to 'tecmint'@'192.168.0.100';
> flush privileges;Ошибки запрещённого доступа могут также возникнуть из-за проблем с подключением к MySQL (см. выше).
Потеря соединения с сервером MySQL
С этой ошибкой можно столкнуться по одной из следующих причин:
- плохое сетевое соединение;
- истекло время ожидания соединения;
- размер BLOB больше, чем
max_allowed_packet.
В первом случае убедитесь, что у вас стабильное сетевое подключение (особенно, если подключаетесь удалённо).
Если проблема с тайм-аутом соединения (особенно при первоначальном соединении MySQL с сервером), увеличьте значение параметра connect_timeout.
В случае с размером BLOB нужно установить более высокое значение для max_allowed_packet в файле конфигурации /etc/my.cnf в разделах [mysqld] или [client] как показано ниже.
[mysqld]
connect_timeout=100
max_allowed_packet=500MЕсли файл конфигурации недоступен, это значение можно установить с помощью следующей команды.
> SET GLOBAL connect_timeout=100;
> SET GLOBAL max_allowed_packet=524288000;Слишком много подключений
Эта ошибка означает, что все доступные соединения используются клиентскими программами. Количество соединений (по умолчанию 151) контролируется системной переменной max_connections. Устранить проблему можно, увеличив значение переменной в файле конфигурации /etc/my.cnf.
[mysqld]
max_connections=1000Недостаточно памяти
Если такая ошибка возникла, это может означать, что в MySQL недостаточно памяти для хранения всего результата запроса.
Сначала нужно убедиться, что запрос правильный. Если это так, то нужно выполнить одно из следующих действий:
- если клиент MySQL используется напрямую, запустите его с ключом
--quick switch, чтобы отключить кешированные результаты; - если вы используете драйвер MyODBC, пользовательский интерфейс (UI) имеет расширенную вкладку с опциями. Отметьте галочкой «Do not cache result» (не кешировать результат).
Также может помочь MySQL Tuner. Это полезный скрипт, который подключается к работающему серверу MySQL и даёт рекомендации по настройке для более высокой производительности.
$ sudo apt-get install mysqltuner #Debian/Ubuntu
$ sudo yum install mysqltuner #RHEL/CentOS/Fedora
$ mysqltunerMySQL продолжает «падать»
Если такая проблема возникает, необходимо выяснить, заключается она в сервере или в клиенте. Обратите внимание, что многие сбои сервера вызваны повреждёнными файлами данных или индексными файлами.
Вы можете проверить состояние сервера, чтобы определить, как долго он работал.
$ sudo systemctl status mysql #Debian/Ubuntu
$ sudo systemctl status mysqld #RHEL/CentOS/FedoraЧтобы узнать время безотказной работы сервера, запустите команду mysqladmin.
$ sudo mysqladmin version -p 
Кроме того, можно остановить сервер, сделать отладку MySQL и снова запустить службу. Для отображения статистики процессов MySQL во время выполнения других процессов откройте окно командной строки и введите следующее:
$ sudo mysqladmin -i 5 statusИли
$ sudo mysqladmin -i 5 -r statusЗаключение
Самое важное при диагностике — понять, что именно вызвало ошибку. Следующие шаги помогут вам в этом:
- Первый и самый важный шаг — просмотреть журналы MySQL, которые хранятся в каталоге
/var/log/mysql/. Вы можете использовать утилиты командной строки вродеtailдля чтения файлов журнала. - Если служба MySQL не запускается, проверьте её состояние с помощью
systemctl. Или используйте командуjournalctl(с флагом-xe) в systemd. - Вы также можете проверить файл системного журнала (например,
/var/log/messages) на предмет обнаружения ошибок. - Попробуйте использовать такие инструменты, как Mytop, glances, top, ps или htop, чтобы проверить, какая программа использует весь ресурс процессора или блокирует машину. Они также помогут определить нехватку памяти, дискового пространства, файловых дескрипторов или какого-либо другого важного ресурса.
- Если проблема в каком-либо процессе, можно попытаться его принудительно остановить, а затем запустить (при необходимости).
- Если вы уверены, что проблемы именно на стороне сервера, можете выполнить команды:
mysqladmin -u root pingилиmysqladmin -u root processlist, чтобы получить от него ответ. - Если при подключении проблема не связана с сервером, проверьте, нормально ли работает клиент. Попробуйте получить какие-либо его выходные данные для устранения неполадок.
Перевод статьи «Useful Tips to Troubleshoot Common Errors in MySQL»
The MySQL server has gone away error, which means that the MySQL server (mysqld) timed out and closed the connection. By default, MySQL will close connections after eight hours (28800 seconds) if nothing happens. However, in some cases, your web host, DBA, or app developer may have decreased this timeout setting, as discussed below.
MySQL server has gone away, can be a frustrating error to solve. This is partly because, to solve this error, sometimes the solution involves multiple layers, application, or service config changes. This article includes solutions I’ve seen for this MySQL server general error. If you’ve found a solution not listed or linked to on this page, please send me a note or leave a comment.
MySQL server has gone away error log examples.
Keep in mind that this error can be logged in a few ways, as listed below. In addition, at times, the error is only an indication of a deeper underlying issue. Meaning the error could be due to a problem or bug in your connecting application or remote service. In this case, you need to check ALL related error logs with the same timestamp to determine whether another issue may be to blame. Application Performance Monitoring solutions and PHP Stack trace tools can be of help. With this in mind, here are error log examples of the MySQL server has gone away error:
General error: 2006 MySQL server has gone away
Error Code: 2013. Lost connection to MySQL server during query
Warning: Error while sending QUERY packet
PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Sponsored: Datadog – View query metrics and explain plans from all of your databases in a single place.
Datadog is a unified monitoring, analytics, and security platform that offers end-to-end monitoring for all of your databases, including MySQL, PostgreSQL, and more! Quickly pinpoint costly, slow queries and troubleshoot performance issues faster with key database metrics and patterns in one place. Easily search, compare, and filter your queries on execution plans to quickly identify areas for performance and cost improvements. Utilize our integrations for MySQL, PostgreSQL, SQL Server, and more to visualize key system performance metrics on an out-of-the-box dashboard alongside query and host-level metrics. Datadog Database Monitoring is closely integrated with the rest of the Datadog platform; further improve your Database performance with other key features such as SLO tracking, monitors & alerts, Security Monitoring, and more!
Cost: Free plan, or starting at $70.
MySQL wait_timeout
The reason for MySQL server has gone away error is often because MySQL’s wait_timeout was exceeded. MySQL wait_timeout is the number of seconds the server waits for activity on a non-interactive connection before closing it. You should make sure the wait_timeout is not set too low. The default for MySQL wait_timeout is 28800 seconds. Often, it gets lowered arbitrarily. That said, the lower you can set wait_timeout without affecting database connections, can be a good sign of MySQL database efficiency. Also, check the variables: net_read_timeout, net_write_timeout and interactive_timeout. Adjust or add the following lines in my.cnf to meet your requirements:
wait_timeout=90 net_read_timeout=90 net_write_timeout=90 interactive_timeout=300 connect_timeout=90
MySQL connect timeout in PHP config.
Have a look at your php.ini config file. You’ll find MySQL configuration options. Make sure the mysql.connect_timeout setting isn’t set lower than MySQL wait_timeout, discussed above. The PHP option mysql.connect_timeout is not only used for connect timeout. It’s also when waiting for the first response from the MySQL server. Try increasing mysql.connect_timeout to match or exceed your MySQL wait_timeout and make sure that mysql.allow_persistent is on (default = enabled).
mysql.connect_timeout=90 mysql.allow_persistent=1
IMPORTANT: Read first about PHP Persistent Database Connections to understand the benefits and caveats.
Also, adjust PHP’s default_socket_timeout. For example, a PHP script could be running a slow query. Creating a wait that utilizes the default_socket_timeout. Eventually, it quits with the “MySQL server has gone away” error. Before you send hate mail, please read here first. Here’s an excerpt:
“PHP, by default, sets a read timeout of 60s for streams. This is set via php.ini, default_socket_timeout. This default applies to all streams that set no other timeout value. mysqlnd does not set any other value and therefore connections of long running queries can be disconnected after default_socket_timeout seconds resulting in an error message 2006 – MySQL Server has gone away
.”
default_socket_timeout=90
To be throughout, also adjust max_execution_time and max_input_time still in php.ini, if necessary. If PHP’s execution time is longer than max_execution_time, then MySQL server might disconnect.
max_execution_time = 90 max_input_time = 90
MySQL max_allowed_packet
max_allowed_packet is the maximum size of one packet. The default size of 4MB helps the MySQL server catch large (possibly incorrect) packets. As of MySQL 8, the default has been increased to 16MB. If mysqld receives a packet that is too large, it assumes that something is wrong and closes the connection. To fix this, you should increase the max_allowed_packet in my.cnf, then restart MySQL. The max for this setting is 1GB. For example:
max_allowed_packet = 512M
MySQL innodb_log_file_size
You may need to increase the innodb_log_file_size MySQL variable in your my.cnf configuration. MySQL’s innodb_log_file_size should be 25% of innodb_buffer_pool_size (if possible, no less than 20%). Remember that the larger this value, the longer it will take to recover from a database crash. (Source: Phpmyadmin Advisor)
This means for example: if your buffer pool size is set to innodb_buffer_pool_size=16G and your innodb_log_files_in_group setting is still set to the recommended default of 2 files (innodb_log_files_in_group=2), then your innodb_log_file_size should be set to 2G. This will create two (2) log files at 2GB each, which equals 25% of innodb_buffer_pool_size=16G.
WARNING: You must stop MySQL server in order to change innodb_log_file_size or innodb_log_files_in_group. If you don’t, you risk catastrophe! (Read: MySQL Log Redo instructions.)
Other causes of MySQL server has gone away
Remote MySQL connections
Remember earlier I mentioned that the error, at times, is only an indication of a deeper underlying issue. For example, remote MySQL connections to 3rd party services. Using a 3rd party payment processing plugin for osCommerce, Magento, etc.
MySQL database charset and collation
Changing default database charset to latin1 and default collation to latin1_general_ci seemed to have solved MySQL server has gone away for some.
Exceeding MySQL max_connections setting
Max_connections set the maximum permitted number of simultaneous client connections. Be careful with this setting!! Exhaustion of memory and other resources can occur when set too large and scheduling overhead also increases. As a guide, set max_connections to approximately double the previous number of maximum simultaneous client connections. E.g., if after a month of uptime, the maximum simultaneous client connections were 114, then set to max_connections=250. Before you go crazy with this setting, please read: How MySQL Handles Client Connections.
Still unresolved? See MySQL’s help page.
Oracle has put together a nice self-help page for MySQL server has gone away errors. On that page, they also suggest that you make sure MySQL didn’t stop/restart during the query. Excerpt:
“You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server’s uptime. If the client connection was broken because mysqld crashed and restarted, you should concentrate on finding the reason for the crash.”
# mysqladmin version mysqladmin Ver 9.1 Distrib 10.1.40-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Server version 10.1.40-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 20 days 11 hours 49 min 40 sec Threads: 5 Questions: 1030744326 Slow queries: 3343 Opens: 3585 Flush tables: 1 Open tables: 2564 Queries per second avg: 582.150
# mysqladmin status Uptime: 1770590 Threads: 4 Questions: 1030752268 Slow queries: 3343 Opens: 3585 Flush tables: 1 Open tables: 2564 Queries per second avg: 582.151
I hope this helps!
Related articles:
- MySQL Performance Tuning: Tips, Scripts and Tools
- Tuning MySQL: my.cnf, avoid this common pitfall!
- MySQL Performance: Stop hoarding. Drop unused MySQL databases
Published: June 7th, 2019 | Last updated: Nov 10th, 2022
Tags: apm, linux, mariadb, mysql, performance, server, sysadmins
MySQL is a widely used open source relational database management system (RDMS) owned by Oracle. It has over the years been the default choice for web-based applications and still remains popular in comparison to other database engines.
Read Also: How to Install Latest MySQL on RHEL/CentOS and Fedora
MySQL was designed and optimized for web applications – it forms an integral part of major web-based applications such as Facebook, Twitter, Wikipedia, YouTube, and many others.
Is your site or web application powered by MySQL? In this detailed article, we will explain how to troubleshoot problems and common errors in MySQL database server. We will describe how to determine the causes of the problems and what to do to solve them.
1. Can’t Connect to Local MySQL Server
One of the common client to server connection errors in MySQL is “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)”.
This error indicates that there is no MySQL server (mysqld) running on the host system or that you have specified a wrong Unix socket file name or TCP/IP port when trying to connect to the server.
Ensure that the server is running by checking a process named mysqld on your database server host using the ps command and grep command together as shown.
$ ps xa | grep mysqld | grep -v mysqld
If the above commands show no output, then the database server isn’t running. Therefore the client can’t connect to it. To start the server, run the following systemctl command.
$ sudo systemctl start mysql #Debian/Ubuntu $ sudo systemctl start mysqld #RHEL/CentOS/Fedora
To verify the MySQL service status, use the following command.
$ sudo systemctl status mysql #Debian/Ubuntu $ sudo systemctl status mysqld #RHEL/CentOS/Fedora
From the output of the above command, the MySQL service has failed. In such a case, you can try to restart it and check its status once more.
$ sudo systemctl restart mysql $ sudo systemctl status mysql
In addition, if the server is running as shown by the following command, but you still see the above error, you should also verify that the TCP/IP port is blocked by a firewall or any port blocking service.
$ ps xa | grep mysqld | grep -v mysqld
To find the port the server is listening on, use the netstat command as shown.
$ sudo netstat -tlpn | grep "mysql"
2. Can’t Connect to MySQL Server
Another commonly encountered connection error is “(2003) Can’t connect to MySQL server on ‘server’ (10061)”, which means that the network connection has been refused.
Here, start by checking that there is a MySQL server running on the system as shown above. Also ensure that the server has network connections enabled and that the network port you are using to connect is the one configured on the server.
Other common errors you are likely to encounter when you try to connect to the MySQL server are:
ERROR 2003: Can't connect to MySQL server on 'host_name' (111) ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
These errors indicate that the server might be running, however, you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening.
3. Access Denied Errors in MySQL
In MySQL, a user account is defined in terms of a username and the client host or hosts from which the user can connect to the server. In addition, an account may also have authentication credentials such as a password.
Although there are many different causes of “Access denied” errors, one of the common causes is relating to the MySQL accounts that the server permits client programs to use when connecting. It indicates that username specified in the connection does not have privileges to access the database.
MySQL allows the creation of accounts that enable client users to connect to the server and access data managed by the server. In this regard, if you encounter an access denied error, check if the user account is allowed to connect to the server via the client program you are using, and possibly the host from which the connection is coming from.
You can see what privileges a given account has by running the SHOW GRANTS command as shown.
> SHOW GRANTS FOR 'tecmint'@'localhost';
You can grant privileges to a particular user on specific database to the remote ip address using the following commands in the MySQL shell.
> grant all privileges on *.test_db to 'tecmint'@'192.168.0.100'; > flush privileges;
Furthermore, access denied errors can also result from problems with connecting to MySQL, refer to the previously explained errors.
4. Lost Connection to MySQL Server
You may encounter this error due to one of the following reasons: poor network connectivity, connection timeout or a problem with BLOB values that are larger than max_allowed_packet. In case of a network connection problem, ensure that you have a good network connection especially if you are accessing a remote database server.
If it is a connection timeout problem, particularly when MySQL is trying to use an initial connection to the server, increase the value of the connect_timeout parameter. But in case of BLOB values that are larger than max_allowed_packet, you need to set a higher value for the max_allowed_packet in your /etc/my.cnf configuration file under [mysqld] or [client] section as shown.
[mysqld] connect_timeout=100 max_allowed_packet=500M
If the MySQL configuration file is not accessible for you, then you can set this value using the following command in the MySQL shell.
> SET GLOBAL connect_timeout=100; > SET GLOBAL max_allowed_packet=524288000;
5. Too Many MySQL Connections
In case a MySQL client encounters the “too many connections” error, it means that all available connections are in use by other clients. The number of connections (default is 151) is controlled by the max_connections system variable; you can remedy the problem by increasing its value to permit more connections in your /etc/my.cnf configuration file.
[mysqld] max_connections=1000
6. Out of Memory MySQL
In case you run a query using the MySQL client program and encounter the error in question, it means that MySQL does not have enough memory to store the entire query result.
The first step is to ensure that the query is correct, if it is, then do the following:
- if you are using MySQL client directly, start it with
--quick switch, to disable cached results or - if you are using the MyODBC driver, the configuration user interface (UI) has an advanced tab for flags. Check “Do not cache result“.
Another great tool is, MySQL Tuner – a useful script that will connect to a running MySQL server and gives suggestions for how it can be configured for higher performance.
$ sudo apt-get install mysqltuner #Debian/Ubuntu $ sudo yum install mysqltuner #RHEL/CentOS/Fedora $ mysqltuner
For MySQL optimization and performance tuning tips, read our article: 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips.
7. MySQL Keeps Crashing
If you encounter this problem, you should try to find out whether the problem is that the MySQL server dies or whether its the client with an issue. Note that many server crashes are caused by corrupted data files or index files.
You can check the server status to establish for how long it has been up and running.
$ sudo systemctl status mysql #Debian/Ubuntu $ sudo systemctl status mysqld #RHEL/CentOS/Fedora
Alternatively, run the following mysqladmin command to find uptime of MySQL server.
$ sudo mysqladmin version -p
Other solutions include but not limited to stopping the MySQL server and enabling debugging, then start the service again. You can try to make a test case that can be used to repeat the problem. In addition, open an additional terminal window and run the following command to display MySQL process statistics while you run your other queries:
$ sudo mysqladmin -i 5 status OR $ sudo mysqladmin -i 5 -r status
The Bottom Line: Determining What Is Causing a Problem or an Error
Although we have looked at some common MySQL problems and errors and also provided ways to troubleshoot and solve them, the most important thing with diagnosing an error is understanding what it means (in terms of what is causing it).
So how can you determine this? The following points will guide you on how to ascertain what is exactly causing a problem:
- The first and most important step is to look into the MySQL logs which are stored in the directory
/var/log/mysql/. You can use command line utilities such as tail to read through the log files. - If MySQL service fails to start, check its status using systemctl or use the journetctl (with the
-xeflag) command under systemd to examine the problem. - You can also examine system log file such as
/var/log/messagesor similar for reasons for your problem. - Try using tools such as Mytop, glances, top, ps, or htop to check which program is taking all CPU or is locking the machine or to inspect whether you are running out of memory, disk space, file descriptors, or some other important resource.
- Assuming that problem is some runaway process, you can always try to kill it (using the pkill or kill utility) so that MySQL works normally.
- Supposing that the mysqld server is causing problems, you can run the command:
mysqladmin -u root pingormysqladmin -u root processlistto get any response from it. - If the problem is with your client program while trying to connect to the MySQL server, check why it is not working fine, try to get any output from it for troubleshooting purposes.
You might also like to read these following MySQL related articles:
- Learn MySQL / MariaDB for Beginners – Part 1
- How to Monitor MySQL/MariaDB Databases using Netdata on CentOS 7
- How to Transfer All MySQL Databases From Old to New Server
- Mytop – A Useful Tool for Monitoring MySQL/MariaDB Performance in Linux
- 12 MySQL/MariaDB Security Best Practices for Linux
For more information, consult the MySQL Reference manual concerning Problems and Common Errors, it comprehensively lists common problems and error messages that you may encounter while using MySQL, including the ones we have discussed above and more.