- Зміст статті Практично будь-який сучасний клієнт-серверний додаток не обходиться без СУБД, і в більшості...
- утиліти моніторингу
- аудит MySQL
- Percona Toolkit for MySQL
- Тюнінг MySQL
- висновок
Зміст статті
Практично будь-який сучасний клієнт-серверний додаток не обходиться без СУБД, і в більшості організацій обслуговування серверів баз даних лежить виключно на плечах сисадміна. Штатні інструменти дозволяють вирішити тільки базові завдання, і їх функцій не завжди достатньо. Утиліти сторонніх розробників зроблять адміністрування MySQL і клонів дуже простим.
інтерфейс адміністрування
MySQL і клони за замовчуванням поширюються без графічного інтерфейсу. У найпростіших випадках з декількома базами для управління досить командного рядка, коли ж кількість серверів, баз і адмінів, їх обслуговують, перевалює за десяток, необхідність в GUI стає більш очевидною. Oracle пропонує свою розробку - єдиний інструмент для розробників баз даних та адміністраторів MySQL Workbench для Windows, Linux, OS X. Це потужне середовище з великими можливостями, що дозволяє візуально проектувати, створювати бази даних і управляти ними. Доступні всі інструменти для настройки серверів, адміністрування облікових записів, бекапа і відновлення, аудиту і простого моніторингу стану. Також з його допомогою можна легко виконати міграцію з інших СУБД - MS SQL Server, Sybase ASE, PostreSQL та інших. Можливості до того ж можна розширити за допомогою плагінів. Інтерфейс не локалізований. Функцій дуже багато, тому деякий час доведеться витратити, щоб освоїтися, хоча в загальному середу зручна. Версія Community (OSS) Edition розповсюджується за ліцензією GNU GPL. Установка проблем не викликає, доступні пакети під різні дистрибутиви Linux, Windows і OS X. Для Red Hat / CentOS краще скористатися в EPEL, в Ubuntu APT репозиторієм розробника .
$ Wget -c http://dev.mysql.com/get/mysql-apt-config_0.2.1-1ubuntu14.04_all.deb $ sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb $ sudo apt -get install mysql-workbench
Дуже популярний серед хостерів phpMyAdmin , Що дозволяє виконати в інтуїтивної середовищі більшість операцій з управління базами даних, роботі з таблицями, індексами, правами доступу, налаштування реплікації, експорт інформації, бекап / відновлення, переглядати статистику і так далі. При цьому залишається можливість безпосереднього введення будь-яких SQL-запитів. Підтримується управління декількома серверами. Все досить інтуїтивно, і з адмініструванням може впоратися користувач без особливої підготовки, з будь-якого пристрою, де є браузер. У Мережі безліч інструкцій і прикладів по використанню phpMyAdmin. Установка зі сховищ пакетів проблем не викликає, як веб-сервера можна використовувати не тільки Apache, але і більш легкі nginx або lighttpd. Деякі панелі управління хостингом на кшталт cPanel і Plesk мають підтримку phpMyAdmin.
Користувачі Windows напевно оцінять HeidiSQL , Що підтримує управління MySQL, MS SQL і PostgreSQL (поки експериментально) і розповсюджується під Open Source ліцензією. Програма має дуже зручний інтерфейс, підтримує підключення відразу до декількох серверів, які доступні в одному вікні, це спрощує операції з експорту / імпорту даних. Доступно створення і редагування баз, таблиць, управління привілеями, експорт таблиць (CSV, HTML, XML, SQL, ...), пошук, оптимізація, моніторинг. При написанні запитів допомагає автодоповнення. Підтримується командний рядок, можливе підключення по SSH-тунелю. Є Portable-версія, використовуючи Wine, його можна запустити і в * nix / OS X.
Для тих, кому не підійшли описані продукти, в інтернеті можна знайти велику кількість аналогів: SQLyog , dbForge Studio for MySQL , TOra , SQL Buddy та інші.
утиліти моніторингу
Як і будь-який додаток, СУБД вимагає постійного спостереження за своєю роботою, щоб в разі проблем легко можна було знайти вузьке місце. Загальну інформацію про роботу MySQL можна отримати за допомогою стандартного клієнта mysqladmin. Запити на кшталт SHOW QUERY LOG, SHOW PROCCESSLIST, SHOW VARIABLES, SHOW GLOBAL STATUS і інші рідко дають чітку картину, так як повільні запити є завжди, але вони не обов'язково впливають на роботу сервісу. Є ще утиліта mysqldumpslow, яка аналізує дані slow.log і виводить найчастіші повільні запити.
Версія Enterprise пропонує спеціальний інструмент MySQL Enterprise Monitor, який надає в реальному часі інформацію про продуктивність і доступність всіх баз даних MySQL. Крім того, для більшості систем моніторингу, включаючи Open Source Nagios, Cacti, Zabbix, Ganglia, доступні спеціальні плагіни. наприклад, Nagios . Кожен з плагінів повинен бути правильно налаштований, щоб представити детальну інформацію про те, що відбувається в MySQL, а це нерідко вимагає досвіду. Розгортати повноцінну систему моніторингу в тому випадку, коли задача тільки контроль СУБД, не завжди раціонально. У цьому випадку на допомогу приходять спеціалізовані інструменти.
На жаль, дуже популярний mytop вже більше двох років не розвивається (хоча це не означає, що його не можна використовувати), але у нього є прекрасна і більш функціональна заміна. Починаючи з версії MySQL 3.23.41 до складу InnoDB входить InnoDB Monitor innotop , Якийсь аналог юніксовского top для цієї СУБД. Innotop виводить в зручному вигляді інформацію по внутрішньому стану InnoDB, яка може стати в нагоді при налаштуванні продуктивності. Варіантів запуску утиліти багато. Найпростіший - виконати від імені певного користувача без параметрів
$ Innotop -u root -p password
За замовчуванням підключення проводиться до локального сервера, але можна вказати будь-який вузол або моніторити відразу кілька серверів. Список всіх команд (Shift + клавіша) можна отримати, натиснувши клавішу питання. Наприклад, <Shift + Q> виведе список всіх поточних запитів. Параметр --write дозволяє зберегти дані з'єднання в файл .innotop / innotop.conf:
$ Innotop --write
Єдина незручність за вказаними утилітам - вони показують інформацію в реальному часі, а про збереження та подальшому аналізі статистичних даних слід подбати самому.
З'явився в 2009 році проект mycheckpoint за кілька років став фактично стандартним інструментом для моніторингу MySQL. Причина популярності - це унікальний підхід. Для зберігання і запиту використовується база даних, в яку (одна операція INSERT) збираються дані про метриках моніторингу, статистики і змінних MySQL, інформації про ОС Linux (стан ОЗУ і swap, навантаження та інше). Також в базу потрапляють результати призначених для користувача запитів. Зібрану інформацію легко переглянути за допомогою простих SELECT-запитів. При цьому адміністратор отримує наочні графіки (на основі Google Chart API), звіти і метрики, які генеруються на льоту. Для виведення HTML звітів може використовуватися власний веб-сервер. Також можуть бути налаштовані попередження, що відсилаються по email. Можливий моніторинг віддаленої системи і декількох серверів.
Написаний на Python, тому установка складнощів не викликає. Розробники пропонують deb, rpm і tar.gz пакет і сирці. В Ubuntu слід ввести наступні команди:
$ Sudo apt-get install python-mysqldb $ sudo dpkg -i mycheckpoint-231-1.deb
Після чого буде потрібно створити базу даних для зберігання інформації:
$ Mysql -uroot -ppassword mysql> CREATE DATABASE mycheckpoint; mysql> GRANT ALL PRIVILEGES ON mycheckpoint. * TO 'user' @ 'localhost' IDENTIFIED BY 'password';
Для збору і виведення даних використовується утиліта mycheckpoint, яку можна запускати вручну або через cron. Установки для перегляду MySQL- і SMTP-сервера вказуються також у командному рядку:
$ Mycheckpoint --user = user --password = password --host = server --port = 3306
Або записуються в конфігураційний файл (за замовчуванням /etc/mycheckpoint.cnf):
* / 5 * * * * mycheckpoint --defaults-file = / root / mycheckpoint.cnf
Тепер можемо переглядати зібрану інформацію і генерувати звіти за допомогою SQL-запитів.
$ Mysql mycheckpoint -e "SELECT html FROM sv_report_html_brief" --silent --raw> ./checkpoint_report.html
Ще один корисний інструмент, що дозволяє контролювати, аналізувати і при необхідності змінювати обмін даними між MySQL-сервером і клієнтським додатком, називається MySQL Proxy . Можливості у програми дуже великі. Працює як під Windows, так і під * nix-системами. Установка проблем не викликає, в настройках необхідно вказати порт MySQL (за замовчуванням 3306) і PHP (рядок mysql.default_port в php.ini). Всі запити формуються на мові Lua, документація тут добре допомагає.
аудит MySQL
Одна з проблем, пов'язаних з експлуатацією будь-якого програмного продукту, - його неправильне налаштування. Після установки присутні зайві демонстраційні облікові записи, тестові бази, самі користувачі можуть ставити прості паролі, які легко підібрати. Вирішити ці проблеми можна лише за допомогою постійного аудиту, який до того ж буде обов'язковий, якщо проводиться обробка конфіденційних / персональних даних (кредитні карти, медичні записи і подібне). Потрібно створити середовище, відповідну стандартам безпеки (SOX, HIPAA і іншим), при розслідуванні інцидентів, усунення неполадок. Після установки потрібно обов'язково використовувати mysql_secure_installation, який забезпечує мінімальний набір перевірок, що дозволяють скорегувати спільні настройки безпеки.
Далі вже слід використовувати інструменти і скрипти, про які нижче. Завдання аудиту спрощується тим, що розробники MySQL пропонують відповідний API. Правда, в MySQL плагін audit_log доступний тільки для версії Enterprise . Розробники Percona Server пропонують GPL альтернативу даному модулю, яка підходить для аудиту MySQL і клонів. Дві інші альтернативи, McAfee MySQL Audit Plugin і MariaDB Audit Plugin for MySQL , Також справляються зі своїм завданням і дозволяють виробляти аудит MariaDB, MySQL і Percona Server, але використовують свій власний формат журналу аудиту, що відрізняється від стандартного MySQL. Це зажадає трохи більших початкових налаштувань. Застосування плагінів простим назвати не можна і докладно висвітлено в документації, яку все одно потрібно прочитати, тому зупинятися не будемо.
Крім того, Патріком Карлссоном (Patrik Karlsson) представлений набір тестів для мережевого сканера Nmap, що дозволяє протестувати сервер на наявність основних проблем безпеки. В Ubuntu після установки настройки зберігаються в каталозі / usr / share / nmap / nselib / data і nmap / script, для перевірки сервера необхідно вказати параметри підключення та обліковий запис.
$ Nmap -p 3306 1.1.1.1 --script mysql-audit --script-args "mysql-audit.filename = '/ usr / share / nmap / nselib / data / mysql- cis.audit', mysql-audit.username = 'root', mysql- audit.password = 'password' "
Хакер їм скористається в будь-якому випадку, тому адмін просто зобов'язаний періодично перевіряти сервери таким способом. Два пакета, про які варто розповісти окремо, також містять інструменти аудиту.
Percona Toolkit for MySQL
Штатні інструменти, що поставляються з MySQL, надають лише базові можливості по адмініструванню, в результаті багато операцій доводиться виконувати вручну. Це може бути проблемою, адже встежити за всім дуже складно, і часто потрібно певний досвід, та й легко припуститися помилки. пакет Percona Toolkit for MySQL зібрав напрацювання двох проектів - Maatkit і Aspersa - і надає скрипти, що дозволяють виробляти багато рутинних операцій адміністрування: перевіряти стан реплікації, збирати інформацію, оптимізувати запити, проводити тюнінг сервера, архівувати / відновлювати дані і багато іншого. Всього більше 4000 тестів і налаштувань. Пакет доступний для основних дистрибутивів Linux (в Ubuntu пакет percona-toolkit). Після установки отримаємо 32 утиліти, ім'я яких починається з pt- *, призначення часто зрозуміло з назви. При запуску можна задавати різні фільтри і форматувати висновок. Тому документацію почитати все одно доведеться, тому що кожна утиліта має велику кількість параметрів. Наприклад, скрипт pt-summary виведе всю інформацію по серверу, зібрану / proc / cpuinfo, / proc / meminfo, mount, df і іншими утилітами, pt-show-grants покаже всі права користувачів СУБД, pt-query-digest дозволяє будувати звіти, засновані на аналізі логів, оброблених сервером запитів, а також інформації, зібраної processlist і tcpdump. Наприклад, перегляд повільних запитів двома способами:
$ Pt-query-digest slow.log $ pt-query-digest --user = user --password = password --processlist --host = example.org
Ще один популярний пакет - openark kit пропонує 14 утиліт, що дозволяють провести тестування СУБД: перевіряти установки, перевіряти паролі (порожні, однакові, слабкі), блокувати акаунти, переривати запити, фільтрувати записи в журналі, виводити статус реплікації, виправляти кодування і багато іншого. Розповсюджується за ліцензією BSD. Написаний на Python і вимагає python-mysqldb. Для установки пропонуються deb- і rpm-пакети і сирці, процес стандартний і проблем не викликає.
Найбільш популярний скрипт комплекту oak-security-audit, призначений для періодичного аудиту акаунтів, паролів, привілеїв та інших налаштувань сервера. У загальному випадку його можна запустити без параметрів. За замовчуванням використовується режим найбільшого рівня контролю системи (відповідає --audit-level = strict):
$ Oak-security-audit --socket = / var / run / mysqld / mysqld.sock --user = user --password = password
В результаті отримаємо звіт, все, що не позначене як Passed, вимагає пильної уваги. За замовчуванням утиліти з openark kit (включаючи і mycheckpoint, про який вище, того ж розробника) використовують для підключення до СУБД сокет /var/run/mysqld/mysql.sock (це вшиті в сам скрипт), в Ubuntu файл називається mysqld.sock . Щоб не ставити кожного разу шлях, можна використовувати заздалегідь підготовлений файл з настройками підключення і вказувати за допомогою -defaults-file. Як варіант: змінити налаштування MySQL в my.cnf. Правити скрипти незручно, так як при оновленні вони працювати не будуть. Наступний скрипт, oak-block-account, дуже популярний у різного роду хостерів для тимчасового відключення доступу до СУБД. Справа в тому, що стандартний механізм СУБД не дозволяє відключати тимчасово аккаунт (щось на зразок REVOKE login ON.), Тобто якщо обліковий запис є, користувач завжди може підключатися до бази даних. Можна, звичайно, використовувати щось на зразок
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USER' @ '%';
але в разі відновлення прав доведеться пам'ятати всі налаштування. І головне, якщо подивитися права за допомогою
mysql> SHOW GRANTS FOR 'USER'; GRANT USAGE ON *. * TO 'USER' @ '%' IDENTIFIED BY PASSWORD .....
ми переконаємося, що така команда не скидає USAGE. Використання REVOKE USAGE фактично означає DROP USER. Простіше змінити логін і пароль, але відновлення може бути проблемою. Загалом, метушні і ризиків багато. У разі використання oak-block-account обліковий запис залишається незмінною, їй просто задається тимчасовий пароль, тому підключитися з цим обліковим записом не можна.
$ Oak-block-account --block --account-user = USER --account-host = example.org
Додатковий параметр --kill дозволить скинути відразу всі активні підключення. Відновити працездатність учеткі також просто. Дивимося список акаунтів і їх статус:
$ Oak-block-account --list
І включаємо обліковий запис:
$ Oak-block-account --release --account-user = USER --account-host = example.org
Інші скрипти з openark kit дозволяють спростити деякі операції. Наприклад, oak-chunk-update дає можливість виконати великі операції UPDATE / DELETE без тривалих блокувань, розбивши завдання на невеликі шматки. Скрипт oak-show-replication-status виводить стан реплікації, oak-kill-slow-queries видаляє запити, що виконуються вже довгий час, oak-repeat-query виконує запит, поки не досягне певної умови (кількість ітерацій, час).
Тюнінг MySQL
Оптимізація налаштувань дуже тонкий процес, адже потрібно на підставі зібраної статистики змінити тільки те, що дійсно вплине на продуктивність. Найвідомішим інструментом для MySQL є Perl-скрипт MySQLTuner , Який доступний в репозиторіях більшості дистрибутивів Linux. Він читає поточні настройки сервера і установки MySQL, після чого видає рекомендації (тільки рекомендації) щодо їх зміни. Якщо установка проводилася за допомогою пакетів, то досить ввести ім'я скрипта, інакше слід викликати, вказуючи інтерпретатор:
$ Perl mysqltuner.pl
Далі будуть запитані логін і пароль адміністратора, після чого ми отримаємо метрики системи і рекомендації. Крім цього, MySQLTuner показує інформацію про індекси в таблицях і фрагментації, які також впливають на швидкість роботи сервера. У разі необхідності отримаємо рекомендації провести перестановку індексів і дефрагментацію. Для більш точної роботи рекомендується запускати скрипт після як мінімум доби аптайма.
Оригінальний скрипт написаний під * nix, але на CodePlex доступна адаптована версія для Win. Альтернативою можна назвати MySQL Performance Tuning Primer Script , Який видає не таку наочну інформацію, але зате більш «балакучий» про проблеми.
висновок
Це, звичайно, далеко не всі must have інструменти, які повинні бути під рукою у адміністратора баз даних. Але це, напевно, той необхідний мінімум, який слід вивчити. Крім того, в процесі знайомства починаєш більше розуміти механізми, закладені в MySQL.