В предыдущих версиях MySQL создание пользователя и предоставление ему прав на базу выполнялось командой, использующей сочетание GRANT и IDENTIFIED BY. В восьмой версии этот синтаксис выдает ошибку:
Архив метки: MySQL
SQL. Сборник рецептов
Книга Энтони Молинаро «SQL. Сборник рецептов» предназначена тем, кто уже знаком с основами языка запросов SQL и хочет повысить свой профессиональ! ный уровень. Она будет полезна и экспертам SQL, поскольку автор предлагает варианты решения задач для разных СУБД: DB2, Oracle, PostgreSQL, MySQL и SQL Server. Если вы постоянно работаете с SQL на одной платформе, то, воз! можно, найдете в рецептах более эффективное решение на другой. Вы научи! тесь использовать SQL для решения более широкого спектра задач – от опера! ций внутри баз данных до передачи данных по сети в приложения. Для этого достаточно открыть книгу на странице с интересующим вас рецептом.
2021-10-24T09:03:53Книги и Курсы
Как усечь таблицу в MySQL
На этом этапе вам может потребоваться очистить таблицу и все хранящиеся в ней данные, сохранив структуру таблицы. В таком сценарии предложение усечения MySQL является очень эффективным запросом.
В этой статье показано, как использовать оператор TRUNCATE в MySQL для удаления всех данных в таблице базы данных.
Оператор TRUNCATE является частью операторов языка определения данных. Однако его функции аналогичны оператору DELETE, что делает его частью языка манипулирования данными.
Чтобы использовать оператор TRUNCATE, у вас должны быть привилегии DROP в базе данных.
Особенности Truncate
Ниже приведены некоторые характерные особенности оператора TRUNCATE, которые отличают его от оператора DELETE:
- Операцию усечения нельзя откатить, поскольку она выполняет неявную фиксацию.
- Он работает, удаляя таблицу и воссоздавая ее, сохраняя ее структуру, но не данные.
- Truncate поддерживает поврежденные таблицы, удаляя все данные и восстанавливая пустую таблицу.
- Он не вызывает никаких триггеров удаления.
- Сохраняет разбиение таблицы
- Оператор TRUNCATE не возвращает никакой информации о затронутых строках — это означает, что возвращаемое значение равно 0.
Основное использование
Общий синтаксис использования оператора TRUNCATE:
TRUNCATE TABLE tbl_name;
Примечание
Вы можете пропустить ключевое слово TABLE, и оператор TRUNCATE будет работать аналогично. Однако лучше добавить ключевое слово TABLE, чтобы избежать путаницы с функцией Truncate.
Пример использования
Давайте посмотрим на пример использования оператора TRUNCATE.
В этом примере мы будем использовать таблицу сотрудников, представленную в ресурсе ниже:
https://dev.mysql.com/doc/index-other.html
Сначала выберите несколько значений из таблицы, чтобы убедиться, что она не пуста:
SELECT * FROM employees LIMIT 10;
Теперь, когда мы подтвердили, что таблица заполнена данными, давайте попробуем усечь таблицу следующим образом:
SET FOREIGN_KEY_CHECKS = FALSE; TRUNCATE TABLE employees;
Сначала мы устанавливаем для переменной FOREIGN_KEY_CHECK значение False, потому что оператор TRUNCATE не работает, если таблица содержит ограничения из других таблиц.
После того, как мы удалили возможность проверки ограничений из других таблиц, мы вызываем оператор TRUNCATE для удаления данных.
Вы можете подтвердить, щелкнув выбрать:
SELECT * FROM employees;
Примечание
ВНИМАНИЕ ! Не удаляйте проверку ограничений в таблицах реальной базы данных.
Заключение
В этой статье вы узнали, как использовать оператор TRUNCATE в MySQL для удаления данных из таблицы. Надеемся, этот урок был вам полезен.
2021-06-16T13:48:30
База данных MySQL
MySQL. Просмотр запросов в реальном времени
В Linux существует команда «watch», которая позволяет запускать команды, стоящие после неё с определённым интервалом. Так можно почти в реальном времени отследить значения в выводе. К сожалению, иногда её неудобно или невозможно использовать для просмотра запросов MySQL. Но это ещё можно делать через mysqladmin.
Мониторинг Mysql в Zabbix
С появлением стандартных готовых шаблонов для различных приложений жизнь с заббиксом стала значительно проще. Сегодня я покажу это на примере мониторинга Mysql сервера в Zabbix 5 с использованием стандартного шаблона. Все стало не просто, а очень просто. Практически ничего делать не надо, разработчики все сделали за нас.
Введение
Напоминаю одну важную деталь. Если вы ставите Zabbix Server не с нуля, а обновляете старую версию, у вас не обновляются стандартные шаблоны. А они последнее время сильно изменились, плюс появились новые. Посмотреть их можно на github — https://github.com/zabbix/zabbix/tree/master/templates.
В данном случае я буду использовать шаблон из директории /db/mysql_agent/. Он написан для старого агента. Напомню, что начиная с версии 4.4 доступна новая версия агента, написанная на Go — zabbix_agent2. Для него появился новый функционал и новые шаблоны. Я пока буду использовать старого агента, так как с новым еще не разбирался.
Если у вас еще нет своего сервера для мониторинга, то рекомендую материалы на эту тему. Для тех, кто предпочитает систему CentOS:
То же самое на Debian 10, если предпочитаете его:
Ставьте себе сервер и погнали настраивать.
Подготовка mysql к мониторингу
Для примера настроим мониторинг Mysql на самом сервере мониторинга Zabbix. Так как это часто узкое место производительности системы, мониторинг базы zabbix лишним не будет. Первым делом добавим новые параметры в агенте. Для этого создаем конфигурационный файл /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf следующего содержания.
UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
После этого сразу перезапустим zabbix-agent.
systemctl restart zabbix-agent
Дальше идем в консоль mysql и создаем пользователя, от которого будет работать мониторинг. Ему достаточно ограниченных прав на чтение.
mysql -uroot -p
> CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY 'TTRy1bRRgLIB';
> GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
> quit
Теперь смотрим, где у нас домашняя директория пользователя zabbix.
cat /etc/passwd | grep zabbix
zabbix:x:990:986:Zabbix Monitoring System:/var/lib/zabbix:/sbin/nologin
У меня ее не было, так что создаем.
mkdir /var/lib/zabbix
Кладем в эту директорию конфиг .my.cnf с реквизитами доступа к серверу mysql.
[client]
user='zbx_monitor'
password='TTRy1bRRgLIB'
Назначаем пользователя zabbix владельцем своей домашней директории и файла в ней. Файлу ограничиваем доступ.
chown -R zabbix. /var/lib/zabbix
chmod 400 /var/lib/zabbix/.my.cnf
Подготовка к мониторингу mysql сервера завершена. Идем теперь в web интерфейс системы мониторинга Zabbix.
Настройка мониторинга Mysql сервера
В веб интерфейсе идем в раздел Настройка -> Шаблоны и импортируем шаблон template_db_mysql_agent.xml.
После этого прикрепляем добавленный шаблон к хосту, где мы только что настроили zabbix-agent и добавили пользователя mysql. Для того, чтобы сразу увидеть все метрики, принудительно выполним сбор данных. Для начала вручную запустим правила автообнаружения, так как у них интервал проверок 1 час. Не хочется столько времени ждать данных. Идем в хост, далее во вкладку Правила обнаружения. Выбираем 2 правила от шаблона mysql и запускаем их.
Ждем несколько секунд и переходим на вкладку Элементы данных. Фильтруем элементы по названию группы MySQL и Zabbix raw items.
Теперь переходим к списку элементов данных. Выделяем все элементы, которые относятся к Mysql и имеют тип Zabbix Agent и запускаем их принудительную проверку. Основной элемент тут — MySQL: Get status variables. Почти все итемы получаются в результате предобработки данных с него.
После этого идем в раздел Мониторинг -> Последние данные и наблюдаем собираемые метрики.
На этом по базовой настройке мониторинга сервера mysql все. Дальше раскрою некоторые нюансы.
Мониторинг репликации MySQL
Вообще, шаблон достаточно навороченный. Там и автообнаружение, и зависимые элементы с предобработкой xml, и предобработка с помощью JavaScript. Рассмотрю отдельно некоторые моменты представленного шаблона zabbix по мониторингу mysql. Во-первых, некоторые параметры задаются с помощью макросов. Вот их список.
Из настраиваемых параметров ясно, что мониторить можно не только локальный mysql сервер, но и удаленный, задав параметры подключения к нему.
Так же в шаблоне реализован мониторинг репликации базы данных. Для этого есть отдельное правила автообнаружения с триггерами. Теперь моя старая статья по мониторингу репликации mysql стала не актуальна. Этот же функционал реализован в базовом шаблоне. Если у вас не настроена репликация, то автообнаружение просто не найдет ничего. Можно это правило выключить.
Для мониторинга репликации автоматически создаются 4 триггера.
- Replication lag is too high (over {$MYSQL.REPL_LAG.MAX.WARN} for 5m) — отставание реплики больше заданного в макросе времени. По умолчанию 30 минут.
- The slave I/O thread is not connected to a replication master — Демон по сбору бинарного лога запущен, но не подключен к мастеру. Его параметр slave_io_running имеет значение не Yes.
- The slave I/O thread is not running — демон по сбору бинарного лога не запущен. Его параметр slave_io_running равен No.
- The SQL thread is not running — демон выполнения команд локального relay лога не запущен. Его парметр slave_sql_running равен No.
В целом, этих четырех метрик достаточно для мониторинга репликации. Я так же настраивал мониторинг именно их.
Триггеры шаблона
Для полноты картины, поясню остальные триггеры шаблона, чтобы у вас было понимание, за чем они следят и как правильно реагировать на них. Ниже список триггеров шаблона для мониторинга mysql сервера.
- Buffer pool utilization is too low (less {$MYSQL.BUFF_UTIL.MIN.WARN}% for 5m) — под innodb пул выделено слишком много памяти и она не используется вся. Триггер чисто информационный, делать ничего не надо, если у вас нет дефицита памяти на сервере. Если нехватка оперативной памяти есть, то имеет смысл забрать немного памяти у mysql и передать другому приложению. Настраивается потребление памяти пулом параметром innodb_buffer_pool_size.
- Failed to get items (no data for 30m) — от mysql сервера не поступают новые данные мониторинга в течении 30 минут. Имеет смысл уменьшить этот интервал до 5-10 минут.
- Refused connections (max_connections limit reached) — срабатывает ограничение на максимальное количество подключений к mysql. Увеличить его можно параметром mysql сервера — max_connections. Его необходимо увеличить, если позволяют возможности сервера. Напомню, что увеличенное количество подключений требует увеличения потребления оперативной памяти. Если у вас ее уже не хватает, нет смысла увеличивать число подключений. Нужно решать вопрос с потреблением памяти.
- Server has aborted connections (over {$MYSQL.ABORTED_CONN.MAX.WARN} for 5m) — сервер отклонил подключений выше заданного порога в макросе. Надо идти в лог mysql сервера и разбираться в причинах этого события. Скорее всего там будут подсказки.
- Server has slow queries (over {$MYSQL.SLOW_QUERIES.MAX.WARN} for 5m) — количество медленных запросов выше установленного макросом предела. Надо идти и разбираться с медленными запросами. Тема не самая простая. Надо заниматься профилированием запросов и решать проблемы по факту — добавлением индексов, редактированием запросов, увеличения ресурсов mysql сервера и т.д.
- Service has been restarted (uptime < 10m) — информационный триггер, срабатывающий на перезапуск mysql сервера (не ребут самого сервера).
- Service is down — служба mysql не запущена.
- Version has changed (new version value received: {ITEM.VALUE}) — версия mysql сервера изменилась. Тоже информационный триггер, сработает, к примеру, после обновления mysql сервера.
На этом по мониторингу MySQL сервера с помощью стандартного шаблона Zabbix все. Надеюсь, я доступно и понятно раскрыл данную тему. Если у вас есть замечания, жду вас в комментариях.
Заключение
Не понравилась статья и хочешь научить меня администрировать? Пожалуйста, я люблю учиться. Комментарии в твоем распоряжении. Расскажи, как сделать правильно!
Здорово, что разработчики сами занялись написанием готовых шаблонов для мониторинга сетевых устройств, операционных систем и приложений. Поняли, что это будет способствовать развитию продукта. Многие шаблоны, которые разрабатывали сами пользователи, становятся неактуальными, так как разработчики их делают лучше. Собственно, это и логично. Кто лучше всех знает продукт, как не они. За последнее время появилось много обновления по этой теме. Надеюсь, будет еще больше.
Источник: https://serveradmin.ru/monitoring-mysql-v-zabbix/
2021-03-10T21:51:20
Software
Шифрование в состоянии покоя в MariaDB
Неактивное шифрование предотвращает доступ злоумышленника к зашифрованным данным, хранящимся на диске, даже если у него есть доступ к системе. Базы данных с открытым исходным кодом MySQL и MariaDB теперь поддерживают функцию шифрования в состоянии покоя, которая отвечает требованиям нового законодательства ЕС о защите данных. Шифрование MySQL в состоянии покоя немного отличается от MariaDB, поскольку MySQL обеспечивает шифрование только для таблиц InnoDB. В то время как MariaDB также предоставляет возможность шифровать файлы, такие как журналы повторного выполнения, журналы медленных операций, журналы аудита, журналы ошибок и т. д. Однако оба они не могут зашифровать данные в ОЗУ и защитить их от вредоносного корня.
В этой статье мы научимся настраивать шифрование на уровне базы данных для MariaDB.
Начиная
Для шифрования данных в состоянии покоя требуется плагин шифрования вместе с управлением ключами. Плагин шифрования отвечает за управление ключом шифрования, а также за шифрование/дешифрование данных.
MariaDB предоставляет три решения для управления ключами шифрования, поэтому то, как ваши базы данных управляют ключом шифрования, зависит от используемого вами решения. В этом руководстве будет продемонстрировано шифрование на уровне базы данных с помощью решения MariaDB File Key Management. Однако этот плагин не поддерживает функцию ротации ключей.
Если вы используете сервер LAMP, файлы для добавления этого плагина находятся в каталоге «/opt/lamp». В противном случае изменения вносятся в папку «/etc/mysql/conf.d».
Создание ключей шифрования
Перед шифрованием базы данных с помощью плагина управления ключами файлов нам необходимо создать файлы, содержащие ключи шифрования. Мы создадим файл с двумя частями информации. Это ключ шифрования в шестнадцатеричном формате вместе с 32-битным идентификатором ключа.
Мы создадим новую папку «keys» в каталоге « /etc/mysql/» и воспользуемся утилитой OpenSSL для случайной генерации трех шестнадцатеричных строк и перенаправления вывода в новый файл в папке ключей. Введите следующие команды:
ubuntu@ubuntu:~$ sudo mkdir /etc/mysql/keys ubuntu@ubuntu:~$ echo -n "1;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys" ubuntu@ubuntu:~$ echo -n "2;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys" ubuntu@ubuntu:~$ echo -n "3;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"
Где 1,2,3 — ключевые идентификаторы; мы включаем их, чтобы создать ссылку на ключи шифрования, используя переменную innodb_default_encryption_key_id в MariaDB. Выходной файл будет выглядеть так:
1;01495ba35e1c9602e14e40bd6de41bb8 2;3cffa4a5d288e90108394dbf639664f8 3;9953297ed1a58ae837486318840f5f1d
Шифрование ключевого файла
Мы можем легко установить системную переменную file_key_management_filename с соответствующим путем внутри плагина File Key Management. Но оставлять ключи в виде обычного текста небезопасно. Мы можем до некоторой степени снизить риск, назначив права доступа к файлам, но этого недостаточно.
Теперь мы зашифруем ранее созданные ключи, используя случайно сгенерированный пароль. Напротив, размер ключа может варьироваться от 128/192/256 бит.
ubuntu@ubuntu:~$ openssl rand -hex 192> /etc/mysql/keys/enc_paswd.key
Поэтому мы будем использовать OpenSSL ENC команду в терминале для шифрования enc_key.txt файл enc_key.enc, с помощью ключа шифрования , созданного выше. Кроме того, MariaDB поддерживает только режим CBC AES для шифрования ключей шифрования.
ubuntu@ubuntu:~$ openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/keys/enc_paswd.key -in /etc/mysql/keys/enc_key.txt -out /etc/mysql/keys/enc_key.enc && sudo rm /etc/mysql/keys/enc_key.txt
Мы также удаляем наш файл enc_keys.txt, поскольку он больше не нужен. Кроме того, мы всегда можем расшифровать наши данные в MariaDB, если наш файл паролей в безопасности.
Настройка плагина управления файловыми ключами
Теперь мы настроим MariaDB с помощью плагина File Key Management, добавив следующие переменные в файл конфигурации. Файлы конфигурации обычно находятся в ‘/etc/mysql’ и по умолчанию читают все файлы .cnf. Или вы можете создать новый файл конфигурации «mariadb_enc.cnf» в каталоге /etc/mysql/conf.d/.
Теперь ваш файл конфигурации может выглядеть совершенно иначе. Однако добавьте эти переменные шифрования в [sqld]. Если ключ зашифрован, плагину требуется настроить две системные переменные, то есть file_key_management_filename и file_key_management_filekey.
[sqld] #File Key Management Plugin plugin_load_add=file_key_management file_key_management = ON file_key_management_encryption_algorithm=aes_cbc file_key_management_filename = /etc/mysql/keys/enc_keys.enc file_key_management_filekey = /etc/mysql/keys/enc_paswd.key # InnoDB/XtraDB Encryption Setup innodb_default_encryption_key_id = 1 innodb_encrypt_tables = ON innodb_encrypt_log = ON innodb_encryption_threads = 4 # Aria Encryption Setup aria_encrypt_tables = ON # Temp & Log Encryption encrypt-tmp-disk-tables = 1 encrypt-tmp-files = 1 encrypt_binlog = ON
Вы можете найти подробную информацию о каждой системной переменной на официальном сайте MariaDB.
Защита файла паролей
Мы изменим права доступа к каталогу MySQL, чтобы защитить пароль и другие конфиденциальные файлы. Право собственности на MariaDB будет изменено на текущего пользователя, которым в Ubuntu является mysql.
sudo chown -R mysql:root /etc/mysql/keys sudo chmod 500 /etc/mysql/keys/
Теперь мы изменим пароль и права доступа к зашифрованным файлам на
sudo chown mysql:root /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc sudo chmod 600 /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc
Теперь перезапустите службу базы данных.
sudo service mysql restart
Вывод
В этой статье мы узнали, как шифрование на уровне базы данных является актуальной задачей и как мы можем настроить шифрование в состоянии покоя в MariaDB. Единственным недостатком плагина File Key Management является то, что он не поддерживает ротацию ключей. Однако, помимо этого подключаемого модуля, существует множество других решений для шифрования управления ключами, например подключаемый модуль AWS Key Management и подключаемый модуль Eperi Key Management. Вы можете найти более подробную информацию об этих плагинах на официальном сайте MariaDB.
2021-02-25T04:45:11
MariaDB