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

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

  • # 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
  • Рабочий 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)

 

Leave a Reply