Несколько полезных трюков Mysql

Набор SQL-конструкций и практик применяемых в администрировании MySQL-серверов

HELP-ME-24.COM (Freelance Team), Черноусов Антон

Автоматический ввод пароля для консольного клиента MySQL

Для задания пароля по умолчанию пользователя для доступа к базе данных, например для работы с разного рода скриптами автоматизации вам необходимо создать в корне  домашнего каталога пользователя файл .my.cnf (например, для пользователя root полный путь будет выглядит следующим образом /root/.my.cnf) содержащий:

[client]
password = "SecretPassword"

Если ваш пароль содержит спец-символы, используйте экранирование кавычками.

Резервное копирование баз данных с одновременным сжатием

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

# mysqldump --all-databases | gzip -c > /opt/backup/mysql-dump.sql.gz

Естественно, что вместо конструкции --all-databases вы можете указать имя конкретной базы данных.

Получение списка баз данных на сервере и их размера

Первостепенная задача с которой начинается ознакомление с инфраструктурой клиента:

SELECT table_schema "DB Name", 
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM   information_schema.tables
GROUP  BY table_schema;

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

Получение списка таблиц в базе данных и их размера

Следующим этапом анализа инфраструктуры является получение сведений о размерах таблиц в отдельно взятой базе данных:
SELECT 
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES WHERE table_schema="DataBaseName"
ORDER BY (data_length + index_length) DESC;
Дополнительно к всему, этот запрос сортирует таблицы по убыванию размера данных и индексов.

Выборка данных старше определенной даты (чистка устаревших данных)

Выборка, копирование и удаление данных из таблицы с фильтром "старше определенной даты" используется при чистке таблиц, логов, создании архивов и многих других операциях. Построение запроса выборки данных по полю date старше одного года от текущего момента выполняется следующей конструкцией:
SELECT * FROM database.order_comments WHERE date <= curdate() - interval 1 year;
Копирование старых данных из боевой таблицы в таблицу архива, выполняется несколько более сложной конструкцией:
INSERT INTO database.order_comments_archive SELECT * FROM database.order_comments WHERE date <= curdate() - interval 1 year;
Эта конструкция применяется если таблицы полностью идентичны, а в противном случае потребуется указывать имена столбцов:
INSERT INTO database.order_comments_archive (id, order_id,manager_id,date,text) SELECT id, order_id,manager_id,date,text FROM database.order_comments WHERE date <= curdate() - interval 1 year;
По окончании переноса данных в таблицу-архив мы удаляем старые данные данные из исходной таблицы:
DELETE FROM uflOfJ.order_comments WHERE date >= curdate() - interval 1 year;
Обратите внимание, что операция удаления в MySql может вызвать ошибку:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
Если вы готовы к удалению по столбцу не являющемуся ключевым, то выполните команду:
SET SQL_SAFE_UPDATES = 0;
Полностью скрипт для реального примера переноса данных выглядит следующим образом:
START TRANSACTION;
INSERT INTO database.order_comments_archive SELECT * FROM database.order_comments WHERE date <= curdate() - interval 1 year;
SET SQL_SAFE_UPDATES = 0;
DELETE FROM database.order_comments WHERE date <= curdate() - interval 1 year;
SET SQL_SAFE_UPDATES = 1;
COMMIT;

Выполняющиеся сейчас запросы

Очень важный вопрос оперативной диагностики базы данных. Когда вы начинаете получать сообщения от сотрудников, что база тормозит или ваша система мониторинга сообщает о резком снижении производительности сервера баз данных, то необходимо оперативно проверить какие сейчас выполняются запросы. Список выполняющихся сейчас запросов вы можете получить выполнив SQL-запрос:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB="DataBaseName";
Для фильтрации запросов к определенной базе данных используйте фильтр DB="DataBaseName", где DataBaseName имя базы запросы к которой мы исследуем.

 

Оставьте комментарий

Вы должны быть вошедший в чтобы отправить комментарий

  • Черноусов Антон, Черноусов Антон на 05.10.2017 03:55:41

    Проверка http://help-me-24.com/test