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

===== Импорт =====
Скрипт Powershell для преобразования списка баз MySQL в команды для импорта сжатых баз
<code powershell>
$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
</code>

===== Slow queries =====
<code bash>
# найти расположение лога
show global variables like '%slow%';
# статистика по логу
mysqldumpslow /var/log/mysql/slow_queries.log
</code>
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 =====
<code bash>
# 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 {} \;
</code>
https://help.cloud66.com/maestro/how-to-guides/databases/shells/uninstall-mysql.html

===== Перенос баз с одного сервера на другой =====
==== Все базы ====
<code bash>
# 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
</code>

==== Одна база ====
<code bash>
mysqldump -u [user] -p --opt one_db > /tmp/one_db.sql
scp /tmp/one_db.sql user@example.com:~/tmp
# На новом сервере
mysql -u root -p
</code>
<code sql>
-- Создать базу, пользователя и дать ему права на базу
CREATE DATABASE one_db;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON one_db . * TO 'username'@'localhost';
FLUSH PRIVILEGES;
quit;
</code>
<code bash>
mysql -u username -p one_db < /tmp/one_db.sql
</code>
https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb

==== Перенос вообще всего - баз, пользователей, разрешений и структуры данных ====
<code bash>
# 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/
</code>
https://www.tecmint.com/transfer-mysql-databases-from-old-to-new-server/
==== Прогресс-бар при импорте ====
<code bash>
# mysql database < database.sql
pv database.sql |mysql -uxxxx -pxxxx database
# если уже запущено без прогресс-бара
pv --watchfd $(pidof mysql) # в другой консоли
</code>

===== MyISAM ↔ InnoDB =====
<code mysql>
# 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';
</code>
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/\\
[[https://mysql.rjweb.org/doc.php/myisam2innodb#why_innodb_is_preferred|Why InnoDB is preferred]]

<code sql>
# info
SHOW TABLES FROM database_name;
SHOW TABLE STATUS FROM database_name WHERE Name='index_'\G;
</code>
===== Полезные команды =====
<code bash>
# Проверить и починить все таблицы во всех базах MySQL (bash):
mysqlcheck -u root -p --auto-repair --check --all-databases
</code>
<code sql>
-- Список пользователей:
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';
</code>

==== Сделать пользователя и дать ему все права на базы ====
<code sql>
# только локальный вход:
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;
</code>
https://www.digitalocean.com/community/tutorials/mysql-ru

На чтение:
<code sql>
GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
</code>
Чтение в таком виде можно дать только глобально, т. к. при попытке дать на одну базу возникает ошибка:\\
<color #ed1c24>//ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES//</color>

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

==== Вернуть руту права логина без ввода пароля ====
<code sql>
update mysql.user set authentication_string=password(''), plugin='mysql_native_password' where user='root';
flush privileges;
quit;
</code>

==== Ошибки ====
<color #ed1c24>ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)</color>
<code bash>
nano /etc/mysql/my.cnf
# Закомментировать
bind-address = 127.0.0.1
# Если там нет этого параметра, то
nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Перезапустить mysql
systemctl restart mysql
</code>

<color #ed1c24>ERROR 1130 (HY000): Host 'computer' is not allowed to connect to this MySQL server</color>\\
В данном случае, юзер был создан как user@localhost, и может заходить только с localhost. Чтобы разрешить удалённое подключение, переделать в user@%
<code sql>
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;
</code>

<color #ed1c24>InnoDB: page_cleaner: 1000ms intended loop took 4554ms. The settings might not be optimal.</color>\\
<code sql>
SET GLOBAL innodb_lru_scan_depth=256;
</code>
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]]

<code bash>
# Поставить необходимые компоненты
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"
</code>

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

Как из кластера вернуться [[https://serverfault.com/questions/861222/transform-galera-cluster-into-single-node-mariadb-server|в одиночный режим работы]]: корректно остановить все ноды, последнюю - ту, которую планируется оставить, после чего убрать конфигурацию Галеры из настроек и запустить на этой ноде MySQL снова.\\

===== Workbench (Docker) =====
<code bash>
docker run --rm --name=mysql-workbench -p 3000:3000 -p 3001:3001 lscr.io/linuxserver/mysql-workbench
</code>

===== xtrabackup =====
==== Установка из tarball ====
Если система древняя, например Ubuntu 14, а надо поставить версию посвежее.
<code bash>
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
</code>
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/

==== Бэкап ====
Права на базу для бэкапа:
<code sql>
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;
</code>
https://docs.percona.com/percona-xtrabackup/2.4/using_xtrabackup/privileges.html#permissions-and-privileges-needed

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



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

Если после полного бэкапа посмотреть файл xtrabackup_checkpoints, там будет информация о LSN.
<code>
backup_type = full-backuped
from_lsn = 0
to_lsn = 1626007
last_lsn = 1626007
compact = 0
recover_binlog_info = 1
</code>

Сделаем инкрементную копию, указав каталог полного бэкапа как основу, откуда берётся последний LSN:
<code bash>
xtrabackup --backup --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base
</code>

Если открыть файл xtrabackup_checkpoints уже в каталоге инкрементной копии:
<code>
backup_type = incremental
from_lsn = 1626007
to_lsn = 4124244
last_lsn = 4124244
compact = 0
recover_binlog_info = 1
</code>

Теперь можно делать инкрементную копию уже указывая каталог предыдущей инкрементной копии:
<code bash>
xtrabackup --backup --target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1
</code>

В xtrabackup_checkpoints ''to_lsn'' и ''last_lsn'' различаются, что означает, что во время копирования в базе что-то происходило.
<code>
backup_type = incremental
from_lsn = 4124244
to_lsn = 6938371
last_lsn = 7110572
compact = 0
recover_binlog_info = 1
</code>

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

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

1) Подготовить полный бэкап с указанием ''%%--apply-log-only%%''
<code>
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
</code>
Если сразу восстановить такой полный бэкап - а это можно сделать - то MySQL при запуске перейдёт в режим восстановления и сам откатит незафиксированные транзакции, посчитав, что работа была завершена некорректно.

2) Применить первую инкрементную копию к полной:
<code>
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1
</code>
Здесь дельта применяется к основным файлам и версия сдвигается вперёд по времени и будет соответствовать меткам inc1. Результат будет в каталоге base, а не в каталоге inc1. Теперь при восстановлении ''/data/backups/base'' база будет на момент бэкапа inc1.\\ 
:!: Применение одной и той же инкрементной копии на две копии бэкапа не поддерживается. Не запускайте два раза ''%%xtrabackup --prepare%%'', указывая один и то же каталог в ''%%--incremental-dir%%''.

3) Применить вторую инкрементную копию по аналогии с первой:
<code>
xtrabackup --prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2
</code>
Так как эта копия последняя, то ключ ''%%--apply-log-only%%'' уже не применяется. Если ключ оставить, то сервер будет сам откатывать незафиксированные транзакции в режиме восстановления как после некорректно завершённой работы.

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

==== Сжатие ====
xtrabackup
<code bash>
xtrabackup --backup --parallel 2 --stream=tar | gzip - > /tmp/mysqlbackup-full.tar.gz
</code>

mariabackup
<code bash>
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
</code>

<code bash>
# 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
</code>
''extra-lsndir'' создаётся автоматически.

https://mariadb.com/kb/en/incremental-backup-and-restore-with-mariabackup/#combining-with-stream-output
==== Восстановление ====
<code bash>
# Восстановление в каталог datadir сервера, указанной в my.cnf
xtrabackup --copy-back --target-dir=/data/backups/
# Восстановление с удалением бэкапа
# xtrabackup --move-back --target-dir=/data/backups/
# Может потребоваться поправить разрешения в каталоге перед запуском сервера
chown -R mysql:mysql /var/lib/mysql
</code>

==== Литература ====
[[https://docs.percona.com/|Документация]]\\
[[https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html|The xtrabackup Option Reference]]\\
[[https://docs.percona.com/percona-xtrabackup/2.4/innobackupex/streaming_backups_innobackupex.html|Streaming and Compressing Backups]]\\

[[https://habr.com/ru/companies/first/articles/582230/|Дифференциальные и инкрементальные бэкапы MySQL]]\\
[[https://vc.ru/dev/158815-sozdanie-rezervnoy-kopii-mysql-pri-pomoshchi-utility-xtrabackup|Создание резервной копии MySQL при помощи утилиты XtraBackup]]

[[https://mariadb.com/kb/en/incremental-backup-and-restore-with-mariabackup/|Incremental Backup and Restore with Mariabackup]]\\
[[https://mariadb.com/kb/en/using-encryption-and-compression-tools-with-mariabackup/|Using Encryption and Compression Tools With Mariabackup]]\\
[[https://mariadb.com/kb/en/mariabackup-options|Mariabackup Options]]\\