Различия

Показаны различия между двумя версиями страницы.


—mysql_error [2022/10/02 09:49] (текущий) – создано - внешнее изменение 127.0.0.1
Строка 1: Строка 1:
 +====== MySQL Error устраняем типичные ошибки ======
 +
 +[[MySQL|MySQL]], [[MariaDB|MariaDB]], как и любой программа, имею тенденцию к ошибкам и поломкам. В этом руководстве попытаюсь создать инструкцию с чего начинать анализ проблем базы данных. В конце опишу ряд типичных ошибок и буду их дополнять.
 +
 +Начинать нужно не с просмотра логов, как бы не странно это звучало. Нужно собрать сведения о рабочем окружении базы данных.
 +
 +Если не будет сказано обратного, то статья тестировалась на [[CentOS|CentOS]] 7.
 +===== Шаг 1: Как узнать версию MySQL, MariaDB =====
 +Из листинга ниже, видно что на сервере установлен именно MySQL версии 5.6.51, а не MariaDB.
 +<file bash>
 +mysql --version
 +mysql  Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using  EditLine wrapper
 +</file>
 +
 +===== Шаг 2: Как узнать количество процессоров и ОЗУ в VPS =====
 +Теперь нужно узнать какими мощностями обладает сервер, как минимум количество процессоров, размер оперативной памяти и свободное место нужно проверить.
 +  * Размер ОЗУ смотрим утилитой [[free|free]]. Нам интересны столбцы total - полный размер установленной ОЗУ (в нашем случае 1ГБ) и столбец available - сколько в настоящее время свободно оперативной памяти.
 +<file bash>
 +# free -h
 +              total        used        free      shared  buff/cache   available
 +Mem:           991M        537M         76M        207M        376M        109M
 +</file>
 +  * Свободное место на жестком диске утилитой [[df|df]]:
 +{{ ::df_-h.jpg?nolink&600 |}}
 +  * Количество ядер процессора вашего сервера, можно узнать несколькими способами, я использую пожалуй самый универсальный и посмотрю в файловой системе [[procfs|proc]]. Я не буду выводить полный листинг, а покажу только на что обратить внимание.
 +<file bash>
 +cat /proc/cpuinfo
 +или
 +lscpu
 +</file>
 +<file bash>
 +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
 +</file>
 +В нашем случае всего 1 ядро, процессор DO-Regular (это самый минимум).
 +
 +===== Шаг 3: Как узнать реальный размер базы данных, таблиц MySQL/MariaDB =====
 +**Читайте также:** [[razmer_bazy_dannyx_postgresql]]
 +
 +Предполагаем, что все файлы баз данных хранятся физически в одном месте (по умолчанию это директория /var/lib/mysql). Стандартно используем утилиту [[du|du]]. 
 +Вывести полный размер директории:
 +<file bash>
 +# du -hsx /var/lib/mysql
 +208M    /var/lib/mysql
 +</file>
 +Вывести полный размер директории для каждой базы данных. **Учтите, что файлы находящие непосредственно в директории /var/lib/mysql показаны не будут, а в примере ниже как раз они и занимают больше всего места**. А это очень интересные файлы: ibdata1, ib_logfile0,ib_logfile1, но о них поговорим ниже.
 +<file bash>
 +# 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
 +</file>
 +
 +
 +Узнаем размер базы данных (в мегабайтах) с помощью SQL запроса. Запрос выведет точный размер базы MySQL для каждой из существующих баз, теперь вы можете ориентироваться что и сколько занимает
 +
 + (поменяйте название БД на свое в строке - "WHERE table_schema = "dbname""):
 +<file sql>
 +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;
 +</file>
 +{{ ::select_table_schema_as.jpg?nolink&600 |}}
 +Если какая -то из БД вам кажется большой, можно посмотреть для конкретной базы данных размер ее таблиц. Используя SQL запрос ниже не забудьте заменить, в параметре table_schema = "nameDB", nameDB на имя вашей БД.
 +<file sql>
 +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;
 +</file>
 +
 +
 +<callout type="primary" title="Заключение" icon="glyphicon glyphicon-alert">В этом примере наглядно показано, что размер базы данных физический (занимаемое место на жестком диске) и виртуальный  - РАЗНЫЙ. И это нужно понимать.</callout>
 +
 +===== Шаг 4: Как посмотреть какой движок (Engine) используют таблицы баз (INNODB, MyISAM)?  =====
 +Зачастую приблуды типо [[phpmyadmin|phpmyadmin]] или нет времени использовать или нет возможности. Значит все сведения нужно получить из консоли Mysql.
 +
 +Возможно понадобится узнать движки, которые поддерживает установленная версия MySQL. Для этого используем SQL запрос: [[mysql#show|SHOW]] ENGINES;
 +{{ ::mysql_show_engines.jpg?nolink&600 |}}
 +Обратите внимание на столбец Support, если указано значение "NO" тип хранения данных не поддерживается (не включен).
 +
 +Самый простой способ узнать тип движка у таблицы — это вызвать SHOW CREATE TABLE <имя_таблицы>
 +<file sql>
 +SHOW CREATE TABLE wp_posts;
 +...
 +ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
 +...
 +</file>
 +
 +===== Шаг ?: my.cnf  =====
 +  * [[my.cnf]]
 +===== Шаг ?: Лог медленный запросов (slow query log)  =====
 +
 +  * [[https://itnots.ru/linux/nastraivaem-log-medlennyh-zaprosov-mysql/|Подробноо временном включении slow query log]]
 +
 +Для обнаружения неэффективных запросов к базе, следует проверить лог медленных запросов. В случае оптимизации запросов этот лог поможет выяснить, что необходимо оптимизировать в первую очередь.
 +Настройки в my.cnf раздел mysqld
 +<file bash>
 +[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
 +</file>
 +
 +  * Местоположение лога: 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  =====
 +  * [[mytop]]
 +  * mysqladmin
 +===== Шаг ?: 100% загрузка процессора  =====
 +
 +
 +Загрузка mysql, которую вы видите в "top", представляет собой общую загрузку всех потоков mysql на всех ядрах. Если у вас есть система с 24 ядрами, вы можете приблизиться к нагрузке одного ядра, разделив 200%/24, что дает нагрузку ~ 8%, что неплохо. Если вы хотите иметь подробное представление о том, что происходит, используйте "htop" вместо "top", а в его настройках включите "древовидное представление" (settings- > display options- > tree view). Вы увидите все потоки mysql с их загрузкой и общую нагрузку (теперь вы видите) в корневом дереве.
 +
 +===== Шаг ?: MySQL полностью многопоточен? =====
 +Говорят! MySQL полностью многопоточен и будет использовать несколько процессоров, при условии, что операционная система поддерживает их, это также увеличит системные ресурсы при правильной настройке производительности.
 +
 +Но настройка этой так называемой многопоточности разница от версии к версии:
 +
 +Типичным параметром в my.ini, влияющим на производительность потока, является:
 +
 +<file>
 +thread_cache_size = 8
 +</file>
 +Thread_cache_size может быть увеличен для улучшения производительность, если у вас много новых подключений. Обычно это не обеспечивает заметного повышения производительности, если у вас хорошая реализация потоков. Однако, если ваш сервер видит сотни подключений в секунду, вам обычно следует установить значение thread_cache_size достаточно высоким, чтобы большинство новых подключений использовали кэшированные потоки
 +
 +Если вы используете Solaris, то вы можете использовать
 +
 +<file>
 +thread_concurrency = 8
 +</file> 
 +Thread_concurrency позволяет приложениям давать системе потоков подсказку о желаемом количестве потоков, которые должны выполняться одновременно.
 +
 +Эта переменная устарела с MySQL 5.6.1 и удалена в MySQL 5.7. Вы должны удалять ее из файлов конфигурации MySQL всякий раз, когда вы ее видите, если они не предназначены для Solaris 8 или более ранних версий.
 +
 +
 +===== Шаг ?: InnoDB  =====
 +Если же нужно посмотреть информацию в более удобном виде и узнать не только ENGINE, то можно выполнить такой SQL-запрос:
 +<file 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;
 +</file>
 +Колонка 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 - размер файла не уменьшается - он растет только в большую сторону. Так что если данных в базе у вас много или идет активное удаление - рано или поздно вы задумаетесь о том, чтобы выполнить подобное разделение
 +
 +[[http://michael-xiii.blogspot.com/2011/10/mysql-innodbfilepertable.html|Как уменьшить размер 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>
 +<file bash>
 +Table 'your table' is marked as crashed and last (automatic?) repair failed
 +</file>
 +
 +Предварительно остановите Базу данных, например так
 +<file>
 +service mysql stop
 +</file>
 +
 +  * Решение: запуск утилиты myisamchk
 +<code>
 +myisamchk --silent --force */*.MYI
 +</code>
 +  * Восстановить сразу во всех базах
 +<file bash>
 +myisamchk -r /var/lib/mysql/*/*.MYI
 +</file>
 +
 +Also, run the following SQL query using phpMyAdmin:
 +<code>
 +REPAIR TABLE TableName
 +</code>
 +===== MySQL продолжает «падать» =====
 +
 +Если такая проблема возникает, необходимо выяснить, заключается она в сервере или в клиенте. Обратите внимание, что многие сбои сервера вызваны повреждёнными файлами данных или индексными файлами.
 +
 +Чтобы узнать время безотказной работы сервера, запустите команду mysqladmin.
 +<file>
 +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
 +</file>
 +
 +Кроме того, можно остановить сервер, сделать отладку MySQL и снова запустить службу. Для отображения статистики процессов MySQL во время выполнения других процессов откройте окно командной строки и введите следующее:
 +
 +<file>
 +$ sudo mysqladmin -i 5 status
 +</file>
 +Или
 +
 +<file>
 +$ sudo mysqladmin -i 5 -r status
 +</file>
 +
 +===== Client requested master to start replication from impossible position (server_errno=1236) =====
 +<code>
 +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
 +</code>
 +  * Решение:
 +Остановить репликацию.
 +Сделать 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]] команды<file bash>
 +# 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; 
 +</file>
 +
 +====== MySQL Logs ======
 +
 +Существует несколько видов журналов (logs) сервера MySQL:
 +  - бинарный лог (binary log)
 +  - лог ошибок (error log)
 +  - лог медленный запросов (slow query log)
 +  - лог запросов (general query log)
 +  - лог репликаций (relay log)
 +===== Подробные логи (general query log) MySQL =====
 +Для включения подробного логгирования запросов ко всем базам [[MySQL]], нужно в файле my.cnf в секции [mysqld] нужно прописать строки:<file>
 +general_log_file        = /var/log/mysql/mysql.log
 +general_log             = 1
 +</file>Создать и присвоить права этому файлу, перегрузить сервер MySQL:<file>
 +touch /var/log/mysql/mysql.log
 +chown mysql:mysql /var/log/mysql/mysql.log
 +/etc/init.d/mysql restart
 +</file>
 +
 +====== Заключение ======
 +Самое важное при диагностике — понять, что именно вызвало ошибку. Следующие шаги помогут вам в этом:
 +
 +  * Cамый важный шаг — просмотреть журналы MySQL, которые хранятся в каталоге /var/log/mysql/. Вы можете использовать утилиты командной строки вроде tail для чтения файлов журнала.
 +  * Если служба MySQL не запускается, проверьте её состояние с помощью systemctl. Или используйте команду journalctl (с флагом -xe) в systemd.
 +  * Вы также можете проверить файл системного журнала (например, /var/log/messages) на предмет обнаружения ошибок.
 +  * Попробуйте использовать такие инструменты, как [[mytop|mytop]], glances, top, [[ps|ps]] или [[top|htop]], чтобы проверить, какая программа использует весь ресурс процессора или блокирует машину. Они также помогут определить нехватку памяти, дискового пространства, файловых дескрипторов или какого-либо другого важного ресурса.
 +  * Если проблема в каком-либо процессе, можно попытаться его принудительно остановить, а затем запустить (при необходимости).
 +  * Если вы уверены, что проблемы именно на стороне сервера, можете выполнить команды: mysqladmin -u root ping или mysqladmin -u root processlist, чтобы получить от него ответ.
 +  * Если при подключении проблема не связана с сервером, проверьте, нормально ли работает клиент. Попробуйте получить какие-либо его выходные данные для устранения неполадок.
  

📌 Удобный подбор VPS по параметрам доступен на DIEGfinder.com - официальном инструменте проекта DIEG. Это часть единой экосистемы, созданной для того, чтобы помочь быстро найти подходящий VPS/VDS сервер для любых задач хостинга.

📌 Для тестирования скриптов, установщиков VPN и Python-ботов рекомендуем использовать надежные VPS на короткий срок. Подробнее о быстрой аренде VPS для экспериментов - читайте здесь.

💥 Подпишись в Телеграм 💥 и задай вопрос по сайтам и хостингам бесплатно!

7 Самых Популярных Статей

  1. Как запустить скрипты и веб-приложения на Python
  2. Что такое страны TIER 1,2,3
  3. 7 способов сравнения файлов по содержимому в Windows или Linux
  4. Установка и тестирование веб-панели HestiaCP
  5. Nginx простые примеры конфигурации
  6. top, htop, atop определение загрузки ОС (Load average, LA)
  7. Использование rsync в примерах

7 Самых Популярных Обзоров

  1. Хостинг для Python-скриптов и приложений
  2. ТОП 4 лучших антидетект браузеров (Бесплатные & Платные)
  3. Подборка купонов (промокоды) на хостинг, антидетект браузеры
  4. Обзор THE.Hosting (PQ Hosting): надежный хостинг с профессиональной поддержкой
  5. Хостинг в России
  6. Хостинг в Европе
  7. Обзор браузера Dolphin {anty} для мультиаккаунтинга