Как исправить ошибку mysql error

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
$ mysqltuner

MySQL продолжает «падать»

Если такая проблема возникает, необходимо выяснить, заключается она в сервере или в клиенте. Обратите внимание, что многие сбои сервера вызваны повреждёнными файлами данных или индексными файлами.

Вы можете проверить состояние сервера, чтобы определить, как долго он работал.

$ 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»

mysql --version
mysql  Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using  EditLine wrapper

# free -h
              total        used        free      shared  buff/cache   available
Mem:           991M        537M         76M        207M        376M        109M

cat /proc/cpuinfo
или
lscpu

CPU(s):                1
Model name:            DO-Regular
CPU MHz:               2294.606
Hypervisor vendor:     KVM
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              4096K

# du -hsx /var/lib/mysql
208M    /var/lib/mysql

# du -h /var/lib/mysql
2.0M    /var/lib/mysql/mysql
636K    /var/lib/mysql/performance_schema
13M     /var/lib/mysql/task41topadm
2.8M    /var/lib/mysql/task42topadm
2.7M    /var/lib/mysql/task43topadm
1.6M    /var/lib/mysql/postfix
1.9M    /var/lib/mysql/task3
12M     /var/lib/mysql/task42wordpress
208M    /var/lib/mysql

SELECT table_schema AS "Name DB",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

SELECT TABLE_NAME AS "Name table",  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema.TABLES WHERE table_schema = "nameDB" ORDER BY (data_length + index_length) DESC;

SHOW CREATE TABLE wp_posts;
...
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
...

[mysqld]
log_output = file
slow_query_log = ON
long_query_time = 0
log_slow_admin_statements = ON
log_slow_slave_statements = ON
slow_query_log_file = /var/log/mariadb/slow-queries.log

Загрузка mysql, которую вы видите в «top», представляет собой общую загрузку всех потоков mysql на всех ядрах. Если у вас есть система с 24 ядрами, вы можете приблизиться к нагрузке одного ядра, разделив 200%/24, что дает нагрузку ~ 8%, что неплохо. Если вы хотите иметь подробное представление о том, что происходит, используйте «htop» вместо «top», а в его настройках включите «древовидное представление» (settings- > display options- > tree view). Вы увидите все потоки mysql с их загрузкой и общую нагрузку (теперь вы видите) в корневом дереве.

thread_cache_size = 8

thread_concurrency = 8

SELECT TABLE_NAME,ENGINE,ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql' ORDER BY ENGINE ASC;

Table 'your table' is marked as crashed and last (automatic?) repair failed

service mysql stop

myisamchk --silent --force */*.MYI

myisamchk -r /var/lib/mysql/*/*.MYI

REPAIR TABLE TableName

mysqladmin version -uroot -p

Server version          5.6.51
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 4 min 51 sec

Threads: 1  Questions: 59  Slow queries: 0  Opens: 102  Flush tables: 1  Open tables: 95  Queries per second avg: 0.015

$ sudo mysqladmin -i 5 status

$ sudo mysqladmin -i 5 -r status

100125  9:49:57 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)
100125  9:49:57 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
100125  9:49:57 Slave I/O thread exiting, read up to log 'broadband-bin.3239', position 61452688

Access denied for user ‘debian-sys-maint’@’localhost’

# nano /etc/mysql/debian.cnf
# mysql -u root -p
mysql> GRANT RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'пишем пароль из debian.cnf';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'пишем пароль из debian.cnf' WITH GRANT OPTION; 

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

touch /var/log/mysql/mysql.log
chown mysql:mysql /var/log/mysql/mysql.log
/etc/init.d/mysql restart

От автора: функционал системы управления базами данных MySQL невероятен. Освоить работу с этим инструментом не легче, чем выучить абсолютно новый язык программирования. Многие начинающие разработчики даже не знают с чего начать. С другой стороны, когда MySQL прописывает «error» с неизвестным номером, у новичка и вовсе опускаются руки. Среди тем, которые мы обсудим в сегодняшней статье, будет работа с MySQL в Linux. Полностью мы не сможем охватить все, но покажем, что не стоит паниковать при первых трудностях. У каждой MySQL error есть решение!

Установить на Linux – легко

Если вы работаете с языком программирования PHP, то базы данных MySQL уже установлены на вашем компьютере. Они поставляются вместе с AMP наборами разработчика. Но в пакете программного обеспечения может находится MariaDB или другая СУБД, а вам нужен именно MySQL. Тем более, что обработка ошибок намного лучше реализована именно на версии My.

Как всегда, главным героем этого параграфа станет терминал. Он наилучшим образом подходит для установки программного обеспечения на Linux-подобные операционные системы:

Предпочтительная установка MySQL на Linux тоже происходит благодаря консоли. Чтобы ее совершить, будет необходимо использовать три команды:

Первая из них служит для того, чтобы обновить пакеты. Когда мы рассматривали установку MySQL в Windows, то одним из первых шагов были установки утилит: фреймворка и Visual C++. Примерно то же самое мы делаем и командой apt update, чтобы обеспечить совместимость оси с новым программным обеспечением.

Далее, по команде apt install mysql-server будет произведена установка самой MySQL. На данном этапе все могло бы быть окончено, но такая СУБД пока небезопасна, а коды ошибок будут частыми гостями в вашем воркфлоу. Но и здесь консоль выручает пользователя дистрибутива Linux. Вместо того, чтобы вручную настраивать всю конфигурацию, он просто пропишет одну sudo-команду: sudo mysql_secure_installation.

Далее, автопилот Linux на время отключается и управлять настройками начинает администратор. На этом этапе, он получит предложение об инсталляции валидатора паролей (надежное средство от подбора и брутфорса). А после, необходимо будет создать рут-пароль, как и в GUI-установщике Windows.

Существуют и решения для обеспечения безопасности при подключении к MySQL других инструментов, таких как phpMyAdmin. Согласно стандартным настройкам, рут-права получает пользователь, который введет пароль либо использует плагин для автоматического входа. phpMyAdmin не позволит программе вводить данные аутентификации. Он требует лишь ввода рут-пасса, который подтвердит санкционированность действий пользователя.

База данных из консоли

Если программное обеспечение можно установить при помощи терминала, значит можно проводить управление ею точно таким же способом. К примеру, создать базу данных можно не только с GUI-интерфейса Linux, но и при помощи командной строки. Для этого нужно лишь знать немного команд и владеть root в MySQL, где мы и будем создавать базу данных. Сделать это можно при помощи команды:

Далее, в том же интерфейсе, необходимо дать команду для создания базы данных:

Чтобы убедится в том, что база данных создана, нужно прописать команду:

Прямо из терминала, базами данных можно и управлять. Но, это тема для целой книги, а не краткого параграфа в рамках статьи. Чтобы ознакомится с другими возможностями такого управления, читайте документацию MySQL и операционной системы, с которой вы стартуете.

Обработка исключений

Некоторые называют их ошибками, но это не совсем верный подход. Дело в том, что исключения не всегда являются таковыми, несмотря на то, что даже утилиты могут называть «обработчиками ошибок». Исключительная ситуация – это такое состояние данных или целой системы, которое делает дальнейшие операции невозможными или лишенными всякого смысла. То есть, это может быть не столько ошибка программиста или системы, сколько закономерное явление, которое препятствует работе. Конечно же, подобные ситуации возникают и в MySQL. Благо, программа умеет обрабатывать ошибки.

Иногда, ошибка может показаться незначительной. Например, несмотря на то, что какая-то из операций выдала ошибку, сама программа сработала правильно, полностью игнорируя искаженные данные. В ряде случаев программисты даже создают алгоритмы, в которых код выполняется несмотря ни на что. Но, с базами данных MySQL так поступать нельзя. Хранимые операции, если окажутся ошибочными, могут полностью дискредитировать целостность базы данных, как и информацию, которая в ней хранится.

Разработчики, которые используют MySQL в качестве системы управления базами данных, назначают сразу несколько обработчиков, а после, задают им приоритет. Важно, чтобы программа не только остановила свою работу при обнаружении ошибки, но и сигнализировала об этом. Игнорирование здесь неуместно: лучше увидеть разочаровывающие оповещения с номером ситуации, чем иметь полностью неправильные вычисления.

Цифры в ошибках

MySQL обозначает все свои ошибки при помощи цифр. Взирая на то, что неполадка может возникнуть как на клиентской, так и на серверной стороне, числа помогают понять, где искать проблему. Кроме чисел, ошибки имеют название и описание. Но, искать ошибку по слову – не очень хорошая затея. Эти атрибуты меняются от версии к версии программного обеспечения, в то время, как числа – довольно стойкая характеристика.

ER_INVALID_DEFAULT

Это одна из самых распространённых ошибок, обозначение которой 1067. В документации объяснение мало чем поможет: оно создано программистами. Но, сообщество пользователей уже сумело разгадать причину invalid_default. Ошибка 1067 может сигнализировать сразу о нескольких ситуациях. Как правило, причина преткновения в том, что неверно была указана директория для инсталлирования MySQL. Если построить процедуру установки СУБД и не использовать пробелы в «пути», то такая проблема больше не возникнет.

А у нас все! Осваивайте MySQL на практике: только так можно стать джедаем баз данных.

Server Not Running in Specified Location

If the error you get is something like:

mysql -uname -p
ERROR 2002 (HY000): Can't connect to local MySQL server through 
  socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

or

mysql -uname -p --port=3307 --protocol=tcp
ERROR 2003 (HY000): Can't connect to MySQL server on  'localhost' 
  (111 "Connection refused")

the server is either not running, or not running on the specified port, socket or pipe. Make sure you are using the correct host, port, pipe, socket and protocol options, or alternatively, see Getting, Installing and Upgrading MariaDB, Starting and Stopping MariaDB or Troubleshooting Installation Issues.

The socket file can be in a non-standard path. In this case, the socket option is probably written in the my.cnf file. Check that its value is identical in the [mysqld] and [client] sections; if not, the client will look for a socket in a wrong place.

If unsure where the Unix socket file is running, it’s possible to find this out, for example:

netstat -ln | grep mysqld
unix  2      [ ACC ]     STREAM     LISTENING     33209505 /var/run/mysqld/mysqld.sock

Unable to Connect from a Remote Location

Usually, the MariaDB server does not by default accept connections from a remote client or connecting with tcp and a hostname and has to be configured to permit these.

(/my/maria-10.4) ./client/mysql --host=myhost --protocol=tcp --port=3306 test
ERROR 2002 (HY000): Can't connect to MySQL server on 'myhost' (115)
(/my/maria-10.4) telnet myhost 3306
Trying 192.168.0.11...
telnet: connect to address 192.168.0.11: Connection refused
(/my/maria-10.4) perror 115
OS error code 115:  Operation now in progress

To solve this, see Configuring MariaDB for Remote Client Access

Authentication Problems

Note that from MariaDB 10.4.3, the unix_socket authentication plugin is enabled by default on Unix-like systems. This uses operating system credentials when connecting to MariaDB via the local Unix socket file. See unix_socket authentication plugin for instructions on connecting and on switching to password-based authentication as well as Authentication from MariaDB 10.4 for an overview of the MariaDB 10.4 changes..

Authentication is granted to a particular username/host combination. user1'@'localhost', for example, is not the same as user1'@'166.78.144.191'. See the GRANT article for details on granting permissions.

Passwords are hashed with PASSWORD function. If you have set a password with the SET PASSWORD statement, the PASSWORD function must be used at the same time. For example, SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass') rather than just SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass';

Problems Exporting Query Results

If you can run regular queries, but get an authentication error when running the SELECT … INTO OUTFILE, SELECT … INTO DUMPFILE or LOAD DATA INFILE statements, you do not have permission to write files to the server. This requires the FILE privilege. See the GRANT article.

Access to the Server, but not to a Database

If you can connect to the server, but not to a database, for example:

USE test;
ERROR 1044 (42000): Access denied for user 'ian'@'localhost' to database 'test'

or can connect to a particular database, but not another, for example
mysql -u name db1 works but not mysql -u name db2, you have not been granted permission for the particular database. See the GRANT article.

Option Files and Environment Variables

It’s possible that option files or environment variables may be providing incorrect connection parameters. Check the values provided in any option files read by the client you are using (see mysqld Configuration Files and Groups and the documentation for the particular client you’re using — see Clients and Utilities).

Option files can usually be suppressed with no-defaults option, for example:

mysqlimport --no-defaults ...

Unable to Connect to a Running Server / Lost root Password

If you are unable to connect to a server, for example because you have lost the root password, you can start the server without using the privilege tables by running the --skip-grant-tables option, which gives users full access to all tables. You can then run FLUSH PRIVILEGES to resume using the grant tables, followed by SET PASSWORD to change the password for an account.

localhost and %

You may have created a user with something like:

CREATE USER melisa identified by 'password';

This creates a user with the ‘%’ wildcard host.

select user,host from mysql.user where user='melisa';
+--------+------+
| user   | host |
+--------+------+
| melisa | %    |
+--------+------+

However, you may still be failing to login from localhost. Some setups create anonymous users, including localhost. So the following records exist in the user table:

select user,host from mysql.user where user='melisa' or user='';
+--------+-----------+
| user   | host      |
+--------+-----------+
| melisa | %         |
|        | localhost |
+--------+-----------+

Since you are connecting from localhost, the anonymous credentials, rather than those for the ‘melisa’ user, are used. The solution is either to add a new user specific to localhost, or to remove the anonymous localhost user.

See Also

• CREATE USER
• GRANT
• Authentication from MariaDB 10.4
• Authentication from MariaDB 10 4 video tutorial