MySQL

Основы SQL: https://www.youtube.com/watch?v=P2Eaf9M4gOU

Импорт

Скрипт Powershell для преобразования списка баз MySQL в команды для импорта сжатых баз

$src = "
-rw-r--r-- 1 root root 1902716934 Nov 26 07:57 ccc.sql.gz
-rw-r--r-- 1 root root    8586406 Nov 26 07:57 sitemanager.sql.gz
-rw-r--r-- 1 root root   33122958 Nov 26 07:57 testcab.sql.gz
"
 
# Кроме mysql.sql.gz, т. к. затрутся все исходные пользователи.
($src.split().where{$_ -match 'sql.gz' -and $_ -notmatch 'mysql.sql.gz'}).foreach({
# Удалить базу
"mysql -e `"drop database $($_ -replace '.sql.gz')`";"
# Создать базу
"mysql -e `"create database $($_ -replace '.sql.gz')`";"
# Импортировать их архива
"zcat $_ | mysql $($_ -replace '.sql.gz')"
}) |Set-Clipboard
 
mysql -e "drop database ccc";
mysql -e "create database ccc";
zcat ccc.sql.gz | mysql ccc
mysql -e "drop database sitemanager";
mysql -e "create database sitemanager";
zcat sitemanager.sql.gz | mysql sitemanager
mysql -e "drop database testcab";
mysql -e "create database testcab";
zcat testcab.sql.gz | mysql testcab

Slow queries

# найти расположение лога
show global variables like '%slow%';
# статистика по логу
mysqldumpslow /var/log/mysql/slow_queries.log

https://blog.toadworld.com/2017/08/09/logging-and-analyzing-slow-queries-in-mysql
https://serverpilot.io/docs/how-to-read-the-mysql-slow-query-log/

Удалить mysql

# Use apt to uninstall and remove all MySQL packages:
$ sudo apt-get remove --purge mysql-server mysql-client mysql-common -y
$ sudo apt-get autoremove -y
$ sudo apt-get autoclean
# Remove the MySQL folder:
$ rm -rf /etc/mysql
# Delete all MySQL files on your server:
$ sudo find / -iname 'mysql*' -exec rm -rf {} \;

https://help.cloud66.com/maestro/how-to-guides/databases/shells/uninstall-mysql.html

Перенос баз с одного сервера на другой

Все базы

# dump all your MySQL databases to a single file using the mysqldump command.
mysqldump -u [user] -p --all-databases > all_databases.sql
# Transfer MySQL Databases Dump File to New Server
scp all_databases.sql user@example.com:~/
# Import MySQL Databases Dump File to New Server
mysql -u [user] -p --all-databases < all_databases.sql
# in case it's very old server
mysql -u [user] -p < all_databases.sql

Одна база

mysqldump -u [user] -p --opt one_db > /tmp/one_db.sql
scp /tmp/one_db.sql user@example.com:~/tmp
# На новом сервере
mysql -u root -p

-- Создать базу, пользователя и дать ему права на базу
CREATE DATABASE one_db;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON one_db . * TO 'username'@'localhost';
FLUSH PRIVILEGES;
quit;

mysql -u username -p one_db < /tmp/one_db.sql

https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb

Перенос вообще всего - баз, пользователей, разрешений и структуры данных

# Stop server before!
systemctl stop mysql
rsync -avz /var/lib/mysql/* user@example.com:/var/lib/mysql/ 
# on the new server
chown mysql:mysql -R /var/lib/mysql/

https://www.tecmint.com/transfer-mysql-databases-from-old-to-new-server/

Прогресс-бар при импорте

# mysql database < database.sql
pv database.sql |mysql -uxxxx -pxxxx database
# если уже запущено без прогресс-бара
pv --watchfd $(pidof mysql) # в другой консоли

MyISAM ↔ InnoDB

# To convert all DBNAME’s InnoDB Tables to MyISAM
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'
 
# To convert all DBNAME’s MyISAM Tables to InnoDB
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'
 
# To convert all InnoDB Tables to MyISAM (all databases)
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') 
FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';
 
# To convert all MyISAM Tables to InnoDB (all databases)
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html
https://www.ryadel.com/en/mysql-convert-all-tables-of-one-or-more-databases-to-myisam-or-innodb/
https://server-gu.ru/convert-to-innodb/
https://mariadb.com/kb/en/innodb-file-format/
https://mariadb.com/kb/en/innodb-row-formats-overview/
Why InnoDB is preferred

# info
SHOW TABLES FROM database_name;
SHOW TABLE STATUS FROM database_name WHERE Name='index_'\G;

Полезные команды

# Проверить и починить все таблицы во всех базах MySQL (bash):
mysqlcheck -u root -p --auto-repair --check --all-databases

-- Список пользователей:
SELECT USER FROM mysql.user;
 
-- Посмотреть права:
SHOW GRANTS FOR 'username'@'localhost';
 
-- Поменять пароль:
SET password FOR 'user'@'localhost' = password('123456');
FLUSH privileges;
 
-- Удалить базу, создать базу:
DROP DATABASE database_name;
CREATE DATABASE database_name;
 
-- Показать список баз:
SHOW DATABASES;
 
-- Вывести информацию о формате файлов баз
SHOW VARIABLES LIKE '%innodb_file%';
+--------------------------+----------+
| Variable_name            | VALUE    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | OFF      |
+--------------------------+----------+
4 ROWS IN SET (0.00 sec)
 
-- Размер баз
-- https://www.a2hosting.com/kb/developer-corner/mysql/determining-the-size-of-mysql-databases-and-tables#Using-the-command-line
SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;
+--------------------+-----------+
| DATABASE           | SIZE (MB) |
+--------------------+-----------+
| information_schema |      0.16 |
| mysql              |      2.65 |
| performance_schema |      0.00 |
| sys                |      0.02 |
| zabbix             |  13559.56 |
+--------------------+-----------+
5 ROWS IN SET (3.80 sec)
 
-- Дать права пользователю username на таблицу table_name в базе database_name.
-- Таблицу и базу можно заменить звёздочками, если нужно дать права на всё.
GRANT ALL PRIVILEGES ON database_name . TABLE_NAME TO 'username'@'localhost';
 
-- Извлечь ФИО и дни рожденья из БД Битрикса (http://promst59.ru/page/bitrix-struktura-bazy-dannyh-modulja-polzovatelej):
USE dbname;
SELECT concat(last_name, ' ', name, ' ', second_name) AS fio, personal_birthday FROM b_user;
 
-- Вывести выборочную информацию о таблицах в базе mydb (engine - движок MyISAM или InnoDB)
SELECT TABLE_NAME,engine,table_rows,data_length,update_time,check_time FROM information_schema.tables WHERE table_schema = 'mydb';

Сделать пользователя и дать ему все права на базы

# только локальный вход:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
# с подсети 192.168.1.0
CREATE USER 'newuser'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'password';
# вход откуда угодно
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
# * . * - database_name . TABLE_NAME
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

https://www.digitalocean.com/community/tutorials/mysql-ru

На чтение:

GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Чтение в таком виде можно дать только глобально, т. к. при попытке дать на одну базу возникает ошибка:
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

https://stackoverflow.com/questions/20036547/mysql-grant-read-only-options
https://sysadmin.ru/articles/ispravlyaem-oshibku-error-1221-hy000-incorrect-usage-of-db-grant-and-global-privileges

Вернуть руту права логина без ввода пароля

UPDATE mysql.user SET authentication_string=password(''), plugin='mysql_native_password' WHERE USER='root';
FLUSH privileges;
quit;

Ошибки

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

nano /etc/mysql/my.cnf
# Закомментировать
bind-address = 127.0.0.1
# Если там нет этого параметра, то
nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Перезапустить mysql
systemctl restart mysql

ERROR 1130 (HY000): Host 'computer' is not allowed to connect to this MySQL server
В данном случае, юзер был создан как user@localhost, и может заходить только с localhost. Чтобы разрешить удалённое подключение, переделать в user@%

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND USER='username';
FLUSH PRIVILEGES;

InnoDB: page_cleaner: 1000ms intended loop took 4554ms. The settings might not be optimal.

SET GLOBAL innodb_lru_scan_depth=256;

https://dba.stackexchange.com/questions/121236/mysql-innodb-page-cleaner-settings-might-not-be-optimal
https://stackoverflow.com/questions/41134785/how-to-solve-mysql-warning-innodb-page-cleaner-1000ms-intended-loop-took-xxx

Кластер

Лучше делать MariaDB Galera cluster, т. к. все узлы активные.

Выбор репозиториев: https://downloads.mariadb.org/mariadb/repositories

https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/#configuring-mariadb-galera-cluster
https://itsecforu.ru/2019/03/20/%F0%9F%93%8A-%D0%BA%D0%B0%D0%BA-%D0%BD%D0%B0%D1%81%D1%82%D1%80%D0%BE%D0%B8%D1%82%D1%8C-%D0%BA%D0%BB%D0%B0%D1%81%D1%82%D0%B5%D1%80-mariadb-galera-%D0%B2-ubuntu-18-04-18-10-%D0%B8-16-04/
https://netpoint-dc.com/blog/mariadb-galera-3-node-cluster/
https://itgala.xyz/ru/mariadb-failover-cluster-haproxy-keepalived/
https://www.claudiokuenzler.com/blog/887/comparing-galera-wsrep-sst-methods-rsync-vs-mariabackup
https://kb.kolabenterprise.com/documentation/reference-architecture-for-mariadb-galera-cluster

# Поставить необходимые компоненты
apt install socat mariadb-backup -y
 
# Создать кластер:
galera_new_cluster
 
# Количество нод
mysql -e "show status like 'wsrep_cluster_size'"
# Состояние
mysql -e "SHOW STATUS LIKE 'wsrep_local_state_comment';"
# Полный статус
mysql -e "SHOW GLOBAL STATUS LIKE 'wsrep_%';"
 
# Конфиг
nano /etc/mysql/mariadb.conf.d/galera.cnf
 
[mysqld]
# Если запустить без этого параметра, будет ошибка
# [ERROR] WSREP: Only binlog_format = 'ROW' is currently supported. Configured value: 'MIXED'.
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
 
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
# Galera Cluster Configuration
wsrep_cluster_name="db_cluster"
wsrep_cluster_address="gcomm://ip-node1,ip-node2,ip-node3"
 
# Galera Synchronization Configuration
# Неблокирующий и быстрый вариант синхронизации, rsync - блокирующий.
wsrep_sst_method=mariabackup
# При режиме mariabackup аутентификация обязательна (надо завести юзера и пароль на всех нодах)
# https://mariadb.com/kb/en/mariabackup-sst-method/
wsrep_sst_auth = mariabackup:mypassword
 
# Galera Node Configuration
wsrep_node_address="192.168.*.*"
wsrep_node_name="Node1"

Если все узлы кластера выключены, кластер перестаёт существовать. Чтобы запустить кластер заново, нужно его заново же создать, начиная с первой ноды.

Как из кластера вернуться в одиночный режим работы: корректно остановить все ноды, последнюю - ту, которую планируется оставить, после чего убрать конфигурацию Галеры из настроек и запустить на этой ноде MySQL снова.

Workbench (Docker)

docker run --rm --name=mysql-workbench -p 3000:3000 -p 3001:3001 lscr.io/linuxserver/mysql-workbench

xtrabackup

Установка из tarball

Если система древняя, например Ubuntu 14, а надо поставить версию посвежее.

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/tarball/percona-xtrabackup-2.4.28-Linux-x86_64.glibc2.17-minimal.tar.gz -O xtrabackup.tar.gz
mkdir /opt/xtrabackup
tar xf xtrabackup.tar.gz --strip=1 -C /opt/xtrabackup && \
rm xtrabackup.tar.gz
ln -s /opt/xtrabackup/bin/xtrabackup /usr/bin/xtrabackup
# Т. к. mariabackup и xtrabackup имеют фактически идентичный синтаксис,
# можно сделать алиас, чтобы не переделывать скрипт для разных серверов
ln -s /opt/xtrabackup/bin/xtrabackup /usr/bin/mariabackup

https://www.percona.com/downloads
https://docs.percona.com/percona-xtrabackup/2.4/installation/binary-tarball.html
https://mariadb.com/kb/en/incremental-backup-and-restore-with-mariabackup/

Бэкап

Права на базу для бэкапа:

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, PROCESS, SUPER, CREATE, ALTER, INSERT, SELECT ON *.* TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;

https://docs.percona.com/percona-xtrabackup/2.4/using_xtrabackup/privileges.html#permissions-and-privileges-needed

# Полный бэкап
xtrabackup --backup --target-dir=/data/backups/
# "Подготовка", нужна, чтобы полный бэкап был консистентным (если дальше не будет инкрементных)
xtrabackup --prepare --target-dir=/data/backups/
# Если полный бэкап будет основой для последующих инкрементных:
xtrabackup --prepare --target-dir=/data/backups/ --apply-log-only

Инкрементные копии

Базируются на log sequence number (LSN) - это версия страниц БД. Т. к. при инкрементном бэкапе не используется сравнение с предыдущим бэкапом, можно сделать инкрементную копию, зная только номер LSN предыдущего бэкапа (--incremental-lsn). Основой для инкрементных бэкапов может являться только полный бэкап, частичный не может.

Если после полного бэкапа посмотреть файл xtrabackup_checkpoints, там будет информация о LSN.

backup_type = full-backuped
from_lsn = 0
to_lsn = 1626007
last_lsn = 1626007
compact = 0
recover_binlog_info = 1

Сделаем инкрементную копию, указав каталог полного бэкапа как основу, откуда берётся последний LSN:

xtrabackup --backup --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base

Если открыть файл xtrabackup_checkpoints уже в каталоге инкрементной копии:

backup_type = incremental
from_lsn = 1626007
to_lsn = 4124244
last_lsn = 4124244
compact = 0
recover_binlog_info = 1

Теперь можно делать инкрементную копию уже указывая каталог предыдущей инкрементной копии:

xtrabackup --backup --target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1

В xtrabackup_checkpoints to_lsn и last_lsn различаются, что означает, что во время копирования в базе что-то происходило.

backup_type = incremental
from_lsn = 4124244
to_lsn = 6938371
last_lsn = 7110572
compact = 0
recover_binlog_info = 1

Подготовка инкрементных бэкапов отличатся от подготовки полных. У полных делается 2 операции для обеспечения консистентности:

Зафиксированные транзакции воспроизводятся из файла журнала в сравнении с файлами данных
Незафиксированные транзакции откатываются

У инкрементных копий откат незафиксированных транзакций проводиться не должен, т. к. транзакции, которые были не зафиксированы во время копирования, скорее всего будут зафиксированы во время следующей инкрементной копии. Поэтому нужно использовать опцию --apply-log-only. Если --apply-log-only не использовать, то инкрементная копия будет бесполезна, потому что после отката незафиксированных транзакций нельзя накатить следующую инкрементную копию.

Следовательно, чтобы подготовить восстановление полного бэкапа с двумя инкрементными, нужно:

1) Подготовить полный бэкап с указанием --apply-log-only

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

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

2) Применить первую инкрементную копию к полной:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1

Здесь дельта применяется к основным файлам и версия сдвигается вперёд по времени и будет соответствовать меткам inc1. Результат будет в каталоге base, а не в каталоге inc1. Теперь при восстановлении /data/backups/base база будет на момент бэкапа inc1.
Применение одной и той же инкрементной копии на две копии бэкапа не поддерживается. Не запускайте два раза xtrabackup --prepare, указывая один и то же каталог в --incremental-dir.

3) Применить вторую инкрементную копию по аналогии с первой:

xtrabackup --prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2

Так как эта копия последняя, то ключ --apply-log-only уже не применяется. Если ключ оставить, то сервер будет сам откатывать незафиксированные транзакции в режиме восстановления как после некорректно завершённой работы.

После применения всех инкрементных копий можно восстанавливать БД как обычно.

Сжатие

xtrabackup

xtrabackup --backup --parallel 2 --stream=tar | gzip - > /tmp/mysqlbackup-full.tar.gz

mariabackup

mariabackup --backup --user root --parallel 2 --stream=xbstream | gzip - > /tmp/mysqlbackup-full.gz
mkdir /tmp/mysqlfullbackup
gunzip -c /tmp/mysqlbackup-full.gz |mbstream -x -C /tmp/mysqlfullbackup

# initial full backup
mariabackup --backup --stream=mbstream \
--user=mariabackup --password=mypassword \
--extra-lsndir=backup_base | gzip > backup_base.gz
 
# incremental backup
mariabackup --backup --stream=mbstream \
--user=mariabackup --password=mypassword \
--incremental-basedir=backup_base \
--extra-lsndir=backup_inc1 | gzip > backup-inc1.gz

extra-lsndir создаётся автоматически.

https://mariadb.com/kb/en/incremental-backup-and-restore-with-mariabackup/#combining-with-stream-output

Восстановление

# Восстановление в каталог datadir сервера, указанной в my.cnf
xtrabackup --copy-back --target-dir=/data/backups/
# Восстановление с удалением бэкапа
# xtrabackup --move-back --target-dir=/data/backups/
# Может потребоваться поправить разрешения в каталоге перед запуском сервера
chown -R mysql:mysql /var/lib/mysql

Литература

Документация
The xtrabackup Option Reference
Streaming and Compressing Backups

Дифференциальные и инкрементальные бэкапы MySQL
Создание резервной копии MySQL при помощи утилиты XtraBackup

Incremental Backup and Restore with Mariabackup
Using Encryption and Compression Tools With Mariabackup
Mariabackup Options

Вячеслав Булынкин

Содержание

MySQL

Импорт

Slow queries

Удалить mysql

Перенос баз с одного сервера на другой

Все базы

Одна база

Перенос вообще всего - баз, пользователей, разрешений и структуры данных

Прогресс-бар при импорте

MyISAM ↔ InnoDB

Полезные команды

Сделать пользователя и дать ему все права на базы

Вернуть руту права логина без ввода пароля

Ошибки

Кластер

Workbench (Docker)

xtrabackup

Установка из tarball

Бэкап

Инкрементные копии

Сжатие

Восстановление

Литература