Очистка, оптимизация, настройка mysql базы zabbix

Тип параметра

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

Мы часто используем динамические параметры, такие как те, с которыми мы знакомы, Параметр уровня транзакции, прямой. Для статических параметров, таких как мы видели вышеВ файлеПуть к файлу данных является статическим параметром. Изменение статического параметра приведет к непосредственному сообщению об ошибке.

По динамическим параметрам он разделен на глобальныеВсе еще разговорДва типа, вы можете пройтиЧтобы изменить параметры. Например:

1 ответ

94

Помните, что самым загруженным файлом в инфраструктуре InnoDB является /var /lib /mysql /ibdata1

Этот файл обычно содержит много классов информации (когда — 0)

• Данные таблицы
• Табличные индексы

• MVCC (многоуровневое управление параллелизмом).

  • Откат сегментов
  • Отменить табличное пространство
• Метаданные таблицы
• См. Изобразительное представление

Многие люди создают несколько файлов ibdata, надеясь на лучшее управление дисками и производительность. Это не помогает.

К сожалению, OPTIMIZE TABLE против таблицы InnoDB, хранящейся в ibdata1, выполняет две вещи:

• Делает данные таблицы и индексы смежными внутри ibdata1
• Это увеличивает ibdata1, потому что смежные данные добавляются к ibdata1

Вы можете отделить данные таблицы и таблицы таблицы от ibdata1 и управлять ими независимо, используя .
Чтобы сжать ibdata1 раз и навсегда, вы должны сделать следующее

Шаг 01) MySQLDump все базы данных в текстовый файл SQL (назовите его SQLData.sql) ()

Шаг 02) Отбросьте все базы данных (кроме , и )

Шаг 03) Завершение работы mysql

Шаг 04) Добавьте следующие строки в /etc/my.cnf

Sidenote: независимо от вашего набора для innodb_buffer_pool_size, убедитесь, что innodb_log_file_size составляет 25% от innodb_buffer_pool_size.

Шаг 05) Удалите ibdata1, ib_logfile0 и ib_logfile1

В этот момент должна быть только схема mysql в /var /lib /mysql

Шаг 06) Перезапустить mysql

Это будет воссоздать ibdata1 в 10MB, ib_logfile0 и ib_logfile1 по 1G каждый

Шаг 07) Перезагрузите SQLData.sql в mysql

ibdata1 будет расти, но будет содержать только метаданные таблицы

Каждая таблица InnoDB будет существовать вне ibdata1

Предположим, что у вас есть таблица InnoDB с именем mydb.mytable. Если вы войдете в /var /lib /mysql /mydb, вы увидите два файла, представляющих таблицу

• mytable.frm (Заголовок двигателя хранения)
• mytable.ibd (Главная таблицы и индексы таблицы для mydb.mytable)

ibdata1 больше не будет содержать данные и индексы InnoDB.

С опцией innodb_file_per_table в файле /etc/my.cnf вы можете запустить и файл будет фактически сокращаться.

Я делал это много раз в своей карьере в качестве DBA MySQL

Фактически, в первый раз, когда я это сделал, я свернул 50GB ibdata1-файл в 500 МБ.

Попробуй. Если у вас есть дополнительные вопросы по этому поводу, напишите мне. Доверьтесь мне. Это будет работать в краткосрочной и долгосрочной перспективе. !!!

Если вы хотите узнать, сколько фактических данных хранится в MyISAM и InnoDB, запустите этот запрос:

Файл параметров

Когда экземпляр mysql запускается, он переходит к файлу конфигурации параметров для чтения различных параметров инициализации, таких как файл конфигурации, используемый для поиска местоположения различных параметров конфигурации базы данных (например, файл my.cnf, используемый для хранения файла, в котором находится файл данных) , Расположение файла журнала ошибок, расположение файла сокета и т. Д.), А также файл конфигурации, который определяет параметры конфигурации определенных размеров структуры.

По умолчанию mysql будет искать эти файлы параметров конфигурации в соответствии с этими путями к файлам при запуске, а затем инициализировать экземпляр mysql. Мы также можем проверить путь, по которому расположен файл my.cnf, с помощью mysql —help | grep my.cnf, как показано на следующем рисунке. : мойПуть к файлу, А затем кошачий: Вот записьфайл данныхмаршрут,файл сокетаТропинка,журналПуть к файлу, в котором он находится, ипроцесс экземпляра mysqlПуть, где хранится pid.

Хранение параметров в файле конфигурации mysql фактически хранится в форме ключ / значение. Например: параметр innodb_buffer_pool_size в innodb, этот параметр представляет размер пула буферов mysql, вы можете просмотреть его следующими способами:

Журнальный файл

В файл журнала записываются различные записи журнала работы базы данных для выполнения SQL, а также записи журнала ошибок. Файлы журнала в основном включают следующие категории:

1. Журнал ошибок: при сбоях базы данных при выполнении определенных операций здесь также будут записываться записанные записи журнала ошибок и предупреждения для определенных операций.
2. Двоичный журнал: записывает все операции, которые изменяют базу данных mysql, но не включает операции выбора и отображения, потому что эти два типа не изменяют базу данных.
3. Журнал медленных запросов: операторы SQL, которые достигают настраиваемого диапазона медленных запросов mysql, будут записываться в этот журнал, что более полезно для обнаружения трудоемких SQL-запросов.
4. Журнал запросов: все записи запросов для mysql будут записаны в этот файл журнала.

Файл журнала ошибок

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

Журнал медленных запросов

В mysql есть динамический параметр long_query_time, который представляет порог медленного запроса, что означает, что, когда выполненный sql превышает время этого параметра, запись будет сохранена в журнале для области медленного запроса данных. Значение по умолчанию равно 10. Следующая команда для просмотра.

В реальной разработке обычно нет sql более 10 секунд, поэтому мы можем установить значение немного меньше, равное 1 секунде, с помощью командыУстановите значение 1 секунда, но функция регистрации медленного запроса по умолчанию отключена. Как показано ниже: по командеВключите эту функцию и, наконец, выполните тест на выполнение SQL:Затем перейдите в каталог datadir mysql, там будет файл медленного запроса выполнить cat + изменить имя файла, вы можете увидеть время выполнения sql и медленного оператора sql: Также путем установкиПараметр, вы также можете получить записи sql, которые не используют индекс в файле.

Журнал запросов

В файл журнала запросов записывается вся информация о работе базы данных mysql, независимо от того, является ли запрос правильным. Местоположение журнала, которое можно просмотреть с помощью следующей команды:

Двоичный журнал

Записывает все операции по изменению базы данных mysql, но не включает、Этот тип, поскольку этот тип не изменяет базу данных, если вы хотите записывать операции выбора и отображения, вы можете использовать толькоЖурнал запросов。

Файлы двоичного журнала в основном выполняют следующие функции:

1. Восстановление: для восстановления некоторых данных требуются двоичные файлы.
2. Репликация: синхронизируйте данные с другим удаленным MySQL.
3. Аудит: используется для определения наличия инъекционной атаки на базу данных.

В mysql двоичный файл существует вВ каталоге, как показано ниже: Бинарный файл не запускается по умолчанию, нам нужно вручную установить параметры для его запуска. Но запуск двоичного файла снизит общую производительность базы данных. Но если база данных не работает в определенный момент, это очень полезно для восстановления данных.

MYSQL, чтобы узнать, включено ли ведение журнала, Если он не включен, вы также можете установить значениеДля просмотра текущего журнала, Место хранения файла журнала также находится вПод содержанием

Повторить файл журнала

По умолчанию в mysqlБудетс участиемДва файла, эти два файлаПовторить файл журнала. Запись файла журнала повторов предназначена для транзакции ACID в базе данных.C (консистенция) Предусмотрена охрана. В каждом движке хранилища innodb будет как минимум два файла журнала повторного выполнения.

Размер каждого файла журнала повторного выполнения одинаков, ациклЖурнал ведется таким образом, что при заполнении файла ib_logfile0 происходит переключение на файл журнала ib_logfile1.

Функция файла журнала повторного выполнения такая же, как и у двоичного файла журнала. Это файл, который записывает журнал транзакций. Итак, в чем разница между двоичным файлом и файлом журнала повторного выполнения?

1. Двоичный файл предназначен для записи всех файлов журнала, связанных с базой данных mysql, и диапазон записи шире, чем файл журнала повторного выполнения.
2. Двоичные файлы сбрасываются на диск только после подтверждения транзакции. Пока транзакция выполняется, журнал повторов будет постоянно записываться на диск,

Журналы повторного выполнения не записываются непосредственно на диск, а сначала записываются в журнал буфера повторного выполнения, и для запуска записи требуются определенные условия. Запуск записи журнала повторного буфера на диск, один из которых является основным потоком, Журналы повторов сбрасываются на диск каждую секунду.

Другой — управление путем передачи параметра innodb_flush_log_at_trx_commit. Параметры: 0, 1, 2, где 0 означает, что журнал повторов не будет сброшен на диск при фиксации транзакции, а основной поток должен инициировать вывод журнала на диск.

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

Файл определения структуры таблицы и файл механизма хранения

Файл определения структуры таблицы и табличное пространство

Файл — это файл, который есть во всех базах данных mysql и записывает определение структуры таблицы. Файл, используемый для хранения данных в InnoDB, состоит из двух частей, одна из которых является файлом системного табличного пространства, включая、 И другие файлы, в которых хранится системная информация InnoDB и данные таблиц пользовательской базы данных и индексы, общие для всех таблиц, а другой -Файл уникален для каждой таблицы. Как показано ниже:

Когда открыт Вариант, Файл — это табличное пространство, уникальное для каждой таблицы.Файл хранит данные текущей таблицы, данные индекса и информацию о буфере вставки. Просмотр и установка Конфигурация