MySQL/MariaDB: разное

Установка mysql на Centos 7 подробно описана тут.
.mysql_history – в этом файле в домашней директории хранится история команд, введенных в консоли mysql.
Ошибка “ERROR 1114 (HY000) at line 1192: The table ‘<name>’ is full” может говорить не о достижения предела таблицы mysql или базы, а о том, что закончилось место на диске.
mysql_secure_installation – после установки пакета рекомендуется запускать безопасную настройку сервера (отключаеь, например, анонимный доступ, удаленный доступ к базе с учеткой root, удаляет тестовую базу), при этом он не изменяет возможность заходить из под учетной записи root в консоль mysql без пароля (кейс восстановления пароля) https://dev.mysql.com/doc/refman/8.4/en/mysql-secure-installation.html
Рабочий step-by-step по смене datadir в mysql. После выполнения проверяем что все работает в новой директории (напр. du -h /usr/data/mysql/ и накатываем дамп), потом удаляем старую.

sudo rm -rf /var/lib/mysql

DATE/TIME

Забрать из базы все записи внесенные (в time пишется время внесения) за последние 10 секунд.

SELECT * FROM message_log WHERE time > TIME_FORMAT(NOW()-10, '%H:%i:%s');

Оперируем часами.

SELECT NOW(), NOW() + INTERVAL 48 HOUR;

CLI

Переход в mysql/mariadb CLI происходит с использованием команды “mysql” (для maria можно “mariadb”). Переход в представление базы wordpress, просмотр всех таблиц.

mysql -u root -p
mysql> use wordpress;
mysql> show tables;

Запросы в CLI.

mysql -ss -e "SHOW DATABASES;" --host=172.17.0.4 --user=wordpress --password=wordpress wordpress

Бекап базы в файл.

mysqldump -u root -p wordpress > backup.sql

mysqladmin

Утилита позволяет посмотреть разную статистику сервера – по запросам, по процессам, версию сервера и проч.

# mysqladmin -u root -p status
Enter password: 
Uptime: 334  Threads: 2  Questions: 22  Slow queries: 0  Opens: 126  Flush tables: 2  Open tables: 102  Queries per second avg: 0.065

# mysqladmin -u root -p ping
Enter password: 
mysqld is alive

# mysqladmin -u root -p version
Enter password: 
mysqladmin  Ver 8.0.13 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          8.0.13
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 6 min 28 sec

Threads: 2  Questions: 26  Slow queries: 0  Opens: 126  Flush tables: 2  Open tables: 102  Queries per second avg: 0.067

MySQL Query analyzer

Полезная утилита как при разработке, так и при траблшутинге проблем с базой. Внизу информация по количеству затраченного времени (Latency Total), количеству запросов (Execution Counts) и величине выборки (Rows Total).

phpmyadmin

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

СБРОС ПАРОЛЯ

Errors

Mysql может крашиться из-за недостатка оперативки. Смотрим через free -h. Простых выхода два – уменьшать количество возможных сессий (хуже), добавлять оперативку (лучше).

MySQL лучше PostgreSQL?

Не однозначно, что postgreSQL лучше Mysql, особенно в контексте производительности. Пример: Uber мигрировал с postgre на mysql.

MySQL: простая реплика данных с задержкой на базе rename MySQL

В mysql очень просто реализовать простую реплику с задержкой (например, раз в минуту/пять минут/etc по cron) в таблицу используя двойной rename.

В mysql rename даже на 10 млн записей делается почти мгновенно, причем на время rename база не отдает ложных значений, а ждет отработки rename.

The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running.

В Oracle, например, эта схема не работает т.к. невозможен rename (как и любой другой DDL) в рамках одного запроса сразу двух таблиц.

Используя эту схему можно делать реплику не только локальных/удаленных mysql таблиц, но и любых данных в mysql. К примеру, можно выгружать данные из SQL баз oracle/postgre или noSQL баз типа mongobd в array или ассоциированный array, обновлять временную таблицу данными (через insert или вгружая из файла, в зависимости от нашего метода получения данных) и делать двойной rename для обновления продакшн таблицы. Просто и из коробки!

CREATE TABLE tb_new LIKE tb; --- создаем временную таблицу на базе продакшн
--- заполняем данными созданную временную таблицу, например через Insert из array или вгружаем через файл
RENAME TABLE tb TO tb_old, tb_new TO tb; --- делаем двойной rename (из продакшн в old, из new в продакшн)
DROP TABLE tb_old; --- дропаем old

MySQL view

View (представление) – создание динамической таблицы на основе запроса в другую таблицу. Стандартные View автоматически апдейтиться при апдейте основной таблицы. Я использовал view когда нужно было чтобы данные из одного, использующего свою БД проекта, были видны в другой БД. Разные БД в этом случае обязательны т.к. так проще, надежнее и безопаснее работать.

Делаем таблицу (view) users в базе destination_bd, включающую столбец username таблицы allowed_users базы source_bd.

CREATE VIEW destination_bd.users AS SELECT username FROM source_bd.allowed_users;

Так же существуют материализированные view, но они поддерживаются не во всех базах.

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

Синтаксис создания такого представления представлен ниже:
CREATE MATERIALIZED VIEW view_name AS SELECT columns FROM tables;

Данный тип представлений один раз выполняет запрос и хранит данные. Тем самым повышается быстродействие, но данные в представлении необходимо обновлять вручную. Для этого применяется следующая команда: 
REFRESH MATERIALIZED VIEW view_name;

Материализованные представления не являются частью стандарта ANSI SQL и поддерживаются не всеми системами управления базами данных (СУБД). PostgreSQL и Oracle Database поддерживают данный тип представлений.

Создание и просмотр пользователей MySQL

Создаем пользователя с полными правами на базу cacti (первая строка) или на все базы (вторая) и обновляем привилегии:

mysql> GRANT ALL ON cacti.* TO cactiuser@localhost IDENTIFIED BY 'somepassword';
mysql> GRANT ALL ON *.* TO cactiuser@localhost IDENTIFIED BY 'somepassword';
mysql> flush privileges;

Смотрим пользователей для базы:

mysql> use wordpress;
mysql> SELECT User,Host FROM mysql.user;
+-----------+-----------+
| User | Host |
+-----------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| wordpress | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

replication

Пример настройки master-save и master-master
- Screenshot
  
  Screenshot
  
  Screenshot
Настройка кластера (мастер-мастер) MariaDB средствами самого mysql, так же есть очень известный инструмент кластеризации mysql – galera – указываем id нод (server-id), путь к бинарным логам (log_bin – на основе них осуществляется синхронизация двух систем), какую БД синхронизируем (binlog-do-db). Правильно собирать три сервера в кластер (защита от split brain).

# INSTALL
apt install mariadb-server-10.3 
service mariadb enable --now
mysql_secure_installation
chown -R mysql:mysql /var/log/mysql   # иначе будет ошибка mysqld: File '/var/log/mariadb-bin.index' not found (Errcode: 13 "Permission denied")

# CLUSTER SETTINGS (mysqld section)
# DEBIAN: /etc/mysql/mariadb.conf.d/50-server.cnf
# RHEL: /etc/my.cnf.d/mariadb-server.cnf
server-id=1  # NODE2: server-id=2
log_bin=/var/log/mariadb-bin.log
binlog-do-db=site
systemctl restart mariadb
systemctl status mariadb

(haproxy, mysql, glusterfs) Взрослая реализация балансировки/отказоусточивости инфраструктуры web сервера включает несколько уровней балансировки – к примеру, сначала балансировка перед web фронт-серверами и последующая балансировка перед БД.

Схема балансировки:

- client ->
- (cluster) external proxy ->
- 2 x web apache (независимые) ->
- (cluster) glusterFS share/replica ->
- (cluster) internal proxy ->
- 2 x bd (master-master)
крайне желателен кворум для всех кластеров – mysql/mariadb, haproxy и реплик glusterfs (по 3 сервера в кластере, а не два), для защиты от split brain через кворум/арбитра в виде третьей машины, иначе нужно шаманить с конфигами для обхода дефолтного ограничения на необходимость кворума (см. примеры ниже)
haproxy для балансировки к нескольким незавимым web серверам wordpress; кластер haproxy active-passive реализуется за счет использования одинаковых конфигов (можно просто копипастить, а можно забирать с шары-smb/glusterfs или из git) между тремя (кворум) и использования специальных демонов, которые реализуют переключение: pacemaker (его юзали в курсе LPIC, проще настраивается и нет мультикаста) или keepalived (на базе vrrp, требует работающий мультикаст, желательно настройка отдельного vlan/аутентификации). IP активной ноды присваивается на пассивную в случае недоступности активной ноды. При этом pacemaker может в качестве разделяемой сущности использовать не только IP адрес, но и другие объекты (препод LINUX: блочные, демоны). И pacemaker и keepalived используется во взрослых промышленных эксплуатациях. Pacemaker без тюнинга позволяет сконвергироваться с секунды – пример потери одного ping при переключении (pcs rouserse move jниже👍
- Для pacemaker с двумя серверами нужно в конфиге pcs отключать проверку на кворум (pcs property set no-quorum-policy=ignore) и prevention по роли (когда две ноды могут постоянно друг у друга отнимать роль после восстановления связности pcs property set stonith-enable=fale). С точки зрения настройки (ниже скрины, базовая инструкция тут https://www.server-world.info/en/note?os=CentOS_8&p=pacemaker&f=1) сложного ничего нет – ставится/включается демон, меняется пароль на служебного юзара hacluster, делается связность через pcs команду между нодами через служебного пользователя, настраивается конфиг, организовывается кластер: pcs resource create ClusterIPocf:heartbeat:IPaddr2 ip=”172.16.30.50″ cidr_netmask=”24″ op monitor interval=60s
glusterfs для общей шары/единого хранилища между web серверами – одно хранилище для файлов wordpress по /var/www/html, при этом glusterfs можно собрать в stripe (raid 0), а не только mirror (raid 1)
mysql/mariadb с master-master репликацией, репликация через бинарный лог для запросов web серверов (через прокси т.к. самые стандартные cms не поддерживают failover sql)