MySQL Error устраняем типичные ошибки
MySQL, MariaDB, как и любой программа, имею тенденцию к ошибкам и поломкам. В этом руководстве попытаюсь создать инструкцию с чего начинать анализ проблем базы данных. В конце опишу ряд типичных ошибок и буду их дополнять.
Начинать нужно не с просмотра логов, как бы не странно это звучало. Нужно собрать сведения о рабочем окружении базы данных.
Если не будет сказано обратного, то статья тестировалась на CentOS 7.
Шаг 1: Как узнать версию MySQL, MariaDB
Из листинга ниже, видно что на сервере установлен именно MySQL версии 5.6.51, а не MariaDB.
mysql --version mysql Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using EditLine wrapper
Шаг 2: Как узнать количество процессоров и ОЗУ в VPS
Теперь нужно узнать какими мощностями обладает сервер, как минимум количество процессоров, размер оперативной памяти и свободное место нужно проверить.
- Размер ОЗУ смотрим утилитой free. Нам интересны столбцы total - полный размер установленной ОЗУ (в нашем случае 1ГБ) и столбец available - сколько в настоящее время свободно оперативной памяти.
# free -h total used free shared buff/cache available Mem: 991M 537M 76M 207M 376M 109M
- Свободное место на жестком диске утилитой df:
- Количество ядер процессора вашего сервера, можно узнать несколькими способами, я использую пожалуй самый универсальный и посмотрю в файловой системе proc. Я не буду выводить полный листинг, а покажу только на что обратить внимание.
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
В нашем случае всего 1 ядро, процессор DO-Regular (это самый минимум).
Шаг 3: Как узнать реальный размер базы данных, таблиц MySQL/MariaDB
Читайте также: Размер базы данных PostgreSQL
Предполагаем, что все файлы баз данных хранятся физически в одном месте (по умолчанию это директория /var/lib/mysql). Стандартно используем утилиту du. Вывести полный размер директории:
# du -hsx /var/lib/mysql 208M /var/lib/mysql
Вывести полный размер директории для каждой базы данных. Учтите, что файлы находящие непосредственно в директории /var/lib/mysql показаны не будут, а в примере ниже как раз они и занимают больше всего места. А это очень интересные файлы: ibdata1, ib_logfile0,ib_logfile1, но о них поговорим ниже.
# 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
Узнаем размер базы данных (в мегабайтах) с помощью SQL запроса. Запрос выведет точный размер базы MySQL для каждой из существующих баз, теперь вы можете ориентироваться что и сколько занимает
(поменяйте название БД на свое в строке - "WHERE table_schema = "dbname""):
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;
Если какая -то из БД вам кажется большой, можно посмотреть для конкретной базы данных размер ее таблиц. Используя SQL запрос ниже не забудьте заменить, в параметре table_schema = "nameDB", nameDB на имя вашей БД.
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;
Заключение
В этом примере наглядно показано, что размер базы данных физический (занимаемое место на жестком диске) и виртуальный - РАЗНЫЙ. И это нужно понимать.Шаг 4: Как посмотреть какой движок (Engine) используют таблицы баз (INNODB, MyISAM)?
Зачастую приблуды типо phpmyadmin или нет времени использовать или нет возможности. Значит все сведения нужно получить из консоли Mysql.
Возможно понадобится узнать движки, которые поддерживает установленная версия MySQL. Для этого используем SQL запрос: SHOW ENGINES; Обратите внимание на столбец Support, если указано значение "NO" тип хранения данных не поддерживается (не включен).
Самый простой способ узнать тип движка у таблицы — это вызвать SHOW CREATE TABLE <имя_таблицы>
SHOW CREATE TABLE wp_posts; ... ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ...
Шаг ?: my.cnf
Шаг ?: Лог медленный запросов (slow query log)
Для обнаружения неэффективных запросов к базе, следует проверить лог медленных запросов. В случае оптимизации запросов этот лог поможет выяснить, что необходимо оптимизировать в первую очередь. Настройки в my.cnf раздел mysqld
[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
- Местоположение лога: log_slow_queries = /var/log/mysql/mysql_slow.log
- Со скольки секунд выполнения запрос считается медленным, минимальное значений — 1 секунда, по умолчанию 10 секунд: long_query_time = 10
- Если надо логировать запросы, которые не используют индексы, надо добавить строку: log-queries-not-using-indexes
- Если надо вести лог медленных команд, таких как OPTIMIZE TABLE, ANALYZE TABLE и ALTER TABLE: log-slow-admin-statements
Шаг ?: Дополнительные утилиты диагностики MysQL
Шаг ?: 100% загрузка процессора
Загрузка mysql, которую вы видите в "top", представляет собой общую загрузку всех потоков mysql на всех ядрах. Если у вас есть система с 24 ядрами, вы можете приблизиться к нагрузке одного ядра, разделив 200%/24, что дает нагрузку ~ 8%, что неплохо. Если вы хотите иметь подробное представление о том, что происходит, используйте "htop" вместо "top", а в его настройках включите "древовидное представление" (settings- > display options- > tree view). Вы увидите все потоки mysql с их загрузкой и общую нагрузку (теперь вы видите) в корневом дереве.
Шаг ?: MySQL полностью многопоточен?
Говорят! MySQL полностью многопоточен и будет использовать несколько процессоров, при условии, что операционная система поддерживает их, это также увеличит системные ресурсы при правильной настройке производительности.
Но настройка этой так называемой многопоточности разница от версии к версии:
Типичным параметром в my.ini, влияющим на производительность потока, является:
thread_cache_size = 8
Thread_cache_size может быть увеличен для улучшения производительность, если у вас много новых подключений. Обычно это не обеспечивает заметного повышения производительности, если у вас хорошая реализация потоков. Однако, если ваш сервер видит сотни подключений в секунду, вам обычно следует установить значение thread_cache_size достаточно высоким, чтобы большинство новых подключений использовали кэшированные потоки
Если вы используете Solaris, то вы можете использовать
thread_concurrency = 8
Thread_concurrency позволяет приложениям давать системе потоков подсказку о желаемом количестве потоков, которые должны выполняться одновременно.
Эта переменная устарела с MySQL 5.6.1 и удалена в MySQL 5.7. Вы должны удалять ее из файлов конфигурации MySQL всякий раз, когда вы ее видите, если они не предназначены для Solaris 8 или более ранних версий.
Шаг ?: InnoDB
Если же нужно посмотреть информацию в более удобном виде и узнать не только ENGINE, то можно выполнить такой SQL-запрос:
SELECT TABLE_NAME,ENGINE,ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql' ORDER BY ENGINE ASC;
Колонка ENGINE — это тип движка у таблицы; Колонка ROW_FORMAT — это формат строк таблицы; Колонка TABLE_ROWS — это количества записей в таблице; Колонка DATA_LENGTH — это размер данных в таблице в байтах; Колонка INDEX_LENGTH — это размер индекса в таблице в байтах;
InnoDB — основной движок для MySQL, который с версии 5.5 стал дефолтным. Поддерживает транзакции, репликацию, построчную блокировку. В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах. То есть данные для всех таблиц и всех баз данных хранятся в одном файле, изменить это можно с помощью настроек опции innodb_file_per_table (Как включить MySQL innodb_file_per_table?). http://michael-xiii.blogspot.com/2011/10/mysql-innodbfilepertable.html
Чтобы уменьшить размер своей базы данных с движком InnoDB, необходимо воспользоваться mysqldump, чтобы сделать дамп всех своих таблиц, создать новую базу данных и импортировать таблицы в новую базу данных.
Источник https://runebook.dev/ru/docs/mariadb/innodb-persistent-statistics/index
MariaDB начиная с 10.0.4 Постоянная статистика для InnoDB была введена в MariaDB 10.0.4 .
До MariaDB 10.0 статистика InnoDB не хранилась на диске, а это означало, что при перезапуске сервера статистику необходимо было пересчитывать, что является как ненужным вычислением, так и приводило к несогласованным планам запросов.
Существует ряд переменных,контролирующих постоянную статистику:
innodb_stats_persistent - если установлено (по умолчанию), включает постоянную статистику InnoDB. innodb_stats_auto_recalc - если установлено (по умолчанию), постоянная статистика автоматически пересчитывается при значительном изменении таблицы (более 10% строк) innodb_stats_persistent_sample_pages - Количество страниц индекса, отобранных (по умолчанию 20) при оценке количества элементов и статистики для индексированных столбцов. Увеличение этого значения повысит точность статистики индекса, но потребует больше ресурсов ввода-вывода при запуске ANALYZE TABLE . Эти настройки могут быть перезаписаны для каждой таблицы с помощью предложений STATS_PERSISTENT , STATS_AUTO_RECALC и STATS_SAMPLE_PAGES в операторе CREATE TABLE или ALTER TABLE .
innodb_file_per_table
Данные, относящиеся к таблицам формата InnoDB, могут храниться в одном файле ibdata1 или в раздельных файлах формата .ibd. Исторически хранение в одном файле является основным методом.
Ранее MySQL по умолчанию все таблички innodb хранил в одном файле - когда их накапливается приличное количество - файл значительно разрастается. Плюс не забывайте, что при удалении данных в innodb - размер файла не уменьшается - он растет только в большую сторону. Так что если данных в базе у вас много или идет активное удаление - рано или поздно вы задумаетесь о том, чтобы выполнить подобное разделение
Данные, загруженные в таблицы MySQL, остаются в файле ibdata1 и не удаляются даже после того как удалены сами таблицы. Размер файла при этом может значительно увеличиваться. Чтобы этого избежать в конфигурацию сервера баз данных добавляют директиву innodb_file_per_table, благодаря ей данные вновь создаваемых таблиц сохраняются в отдельные файлы.
Значения параметра, используемые по умолчанию для разных версий пакетов:
- MySQL версии до 5.6.6 и MariaDB 5.5 — innodb_file_per_table = 0 (данные хранятся в одном файле ibdata1)
- MySQL 5.6.6 и старше + MariaDB 10.x — innodb_file_per_table = 1 (данные хранятся в разных файлах с расширением .ibd)
FAQ: MySQL Error
MYSQL/MariaDB table is marked as crashed
Аналогичная ошибки, если вы в логах видите Can't open file: '*.MYI'. (errno: 145) или Fix: MySQL table is marked as crashed and last repair failed То лечится это одинаково, что MYSQL, что в MariaDB <code> 100125 9:33:30 /usr/local/libexec/mysqld: Can't open file: '*.MYI'. (errno: 145) ERROR: 1016 Can't open file: '***.MYI'. (errno: 145) </code>
Table 'your table' is marked as crashed and last (automatic?) repair failed
Предварительно остановите Базу данных, например так
service mysql stop
- Решение: запуск утилиты myisamchk
myisamchk --silent --force */*.MYI
- Восстановить сразу во всех базах
myisamchk -r /var/lib/mysql/*/*.MYI
Also, run the following SQL query using phpMyAdmin:
REPAIR TABLE TableName
MySQL продолжает «падать»
Если такая проблема возникает, необходимо выяснить, заключается она в сервере или в клиенте. Обратите внимание, что многие сбои сервера вызваны повреждёнными файлами данных или индексными файлами.
Чтобы узнать время безотказной работы сервера, запустите команду mysqladmin.
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
Кроме того, можно остановить сервер, сделать отладку MySQL и снова запустить службу. Для отображения статистики процессов MySQL во время выполнения других процессов откройте окно командной строки и введите следующее:
$ sudo mysqladmin -i 5 status
Или
$ sudo mysqladmin -i 5 -r status
Client requested master to start replication from impossible position (server_errno=1236)
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
- Решение:
Остановить репликацию. Сделать mysqldump на мастере, перед этим SHOW MASTER STATUS\G; Отметить у себя текущий File (binlog.000xx) и позицию Position. Дамп залить на slave, там же сделать CHANGE MASTER TO MASTER_LOG_FILE="записанный binlog.000xx", MASTER_LOG_POS="записанная позиция"; Запустить репликацию
Access denied for user 'debian-sys-maint'@'localhost'
Ошибка "Access denied for user 'debian-sys-maint'@'localhost'" в Операционная система Debian может появляться в нескольких случаях, например
- при повреждении системной БД mysql и установки её заново
- при переносе MySQL из другой операционной системы
Пользователь debian-sys-maint служит например для проверки таблиц при запуске сервера MySQL.
Для исправления ошибки, запоминаем пароль(строка password) из файла debian.cnf. И через клиента mysql и выполняем две SQL команды
# 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;
MySQL Logs
Существует несколько видов журналов (logs) сервера MySQL:
- бинарный лог (binary log)
- лог ошибок (error log)
- лог медленный запросов (slow query log)
- лог запросов (general query log)
- лог репликаций (relay log)
Подробные логи (general query log) MySQL
Для включения подробного логгирования запросов ко всем базам Движок БД MySQL, нужно в файле my.cnf в секции [mysqld] нужно прописать строки:
general_log_file = /var/log/mysql/mysql.log general_log = 1
Создать и присвоить права этому файлу, перегрузить сервер MySQL:
touch /var/log/mysql/mysql.log chown mysql:mysql /var/log/mysql/mysql.log /etc/init.d/mysql restart
Заключение
Самое важное при диагностике — понять, что именно вызвало ошибку. Следующие шаги помогут вам в этом:
- Cамый важный шаг — просмотреть журналы 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, чтобы получить от него ответ.
- Если при подключении проблема не связана с сервером, проверьте, нормально ли работает клиент. Попробуйте получить какие-либо его выходные данные для устранения неполадок.
📌 Для тестирования скриптов, установщиков VPN, Python ботов рекомендуем использовать надежные VPS на короткий срок. Если вам нужна помощь с более сложными задачами, вы можете найти фрилансера, который поможет с настройкой. Узнайте больше о быстрой аренде VPS для экспериментов и о фриланс-бирже для настройки VPS, WordPress. 📌
💥 Подпишись в Телеграм 💥 и задай вопрос по сайтам и хостингам бесплатно!
7 Самых Популярных Статей
- Как запустить скрипты и веб-приложения на Python
- Что такое страны TIER 1,2,3
- 7 способов сравнения файлов по содержимому в Windows или Linux
- Установка и тестирование веб-панели HestiaCP
- Китайский VPN Shadowsocks простая установка и настройка
- top, htop, atop определение загрузки ОС (Load average, LA)
- Использование rsync в примерах