27 KiB

Raw Permalink Blame History

MySQL

Описан процесс установки MySQL и настройка базовой репликации мастер -> слейв

MySQL

Установка сервера MySQL 8 в Debian 12

На сайте MySQL в разделе Community выбрать необходимую версию (8.4.3 LTS на момент написания заметки), в качестве ОС выбрать Debian. После выбора ОС появится предложение выполнить установку используя репозиторий APT, нажимаем ссылку, будет предложено скачать пакет для настройки репозитория (mysql-apt-config_0.8.33-1_all.deb на момент написания заметки). Далее в консоли по документации - https://dev.mysql.com/doc/refman/8.4/en/linux-installation-apt-repo.html

su -

wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
apt install -y ./mysql-apt-config_0.8.33-1_all.deb

В появившемся окне выбрать - Ok

dpkg-reconfigure mysql-apt-config

apt update
apt install mysql-server

Можно также выполнить настройки безопасности

mysql_secure_installation

Создание тестовой БД и наполнение данными

Подключение к СУБД

mysql -u root -p

Создание базы данных

CREATE DATABASE IF NOT EXISTS sape_test_db;

По ошибке была создана БД с именем - sape_test_db.db

При её удалении полачал ошибку

mysql> drop database 'sape_test_db.db';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sape_test_db.db'' at line 1

Поиск причин привёл - https://stackoverflow.com/questions/16135987/error-on-drop-default-database-error

при использовании в названии зарезервированных слов, следует заключать имя БД в косые ковычки `

mysql> drop database `sape_test_db.db`;
Query OK, 0 rows affected (0,17 sec)

Переключение на созданную базу данных

USE sape_test_db;

Создание таблицы в базе данных

таблица будет содержать три столбца: id, name, surname, address. Первичным ключом будет id. В качестве движка явно укажем InnoDB.

CREATE TABLE admin_table
( id int NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
surname char(20) NOT NULL,
city char(20) NULL,
PRIMARY KEY (id) )
ENGINE=InnoDB;

для удаления таблицы

DROP TABLE admin_table;

Добавление записей в таблицу

INSERT admin_table(name, surname, city) VALUES ('Andrei', 'Ahmadulin', 'Ekaterinburg');
INSERT admin_table(name, surname, city) VALUES ('Kit', 'Root', 'Georgia');
INSERT admin_table(name, surname, city) VALUES ('Ilya', 'Paramonov', 'Moscow');
INSERT admin_table(name, surname, city) VALUES ('Andrei', 'Zharkov', 'Moscow');
INSERT admin_table(name, surname, city) VALUES ('Artem', 'Dolgiy', 'Zelenograd');

Выборка из таблицы

SELECT * FROM admin_table;

Репликация - общие настройки

Эти настройки выполняются вне зависимости от выбранного способа репликации

Пользователь для выполнения задач репликации

Будет создан пользователь repl_user с паролем repl_passwd

mysql -u root -p

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_passwd';

Если возникнет ошибка - ERROR 1819 (HY000): Your password does not satisfy the current policy requirements - https://stackoverflow.com/questions/43094726/your-password-does-not-satisfy-the-current-policy-requirements

кратко - достаточно изменить политику паролей

mysql> SHOW VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| validate_password.dictionary_file               |        |
| validate_password.length                        | 8      |
| validate_password.mixed_case_count              | 1      |
| validate_password.number_count                  | 1      |
| validate_password.policy                        | MEDIUM |
| validate_password.special_char_count            | 1      |
+-------------------------------------------------+--------+
8 rows in set (0,03 sec)

mysql> SET GLOBAL validate_password.policy = LOW;
Query OK, 0 rows affected (0,00 sec)

mysql>

Теперь пользователя можно будет создать с простым паролем - 'repl_passwd'

Назначение привилегий для пользователя

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Настройка мастера для репликации

В файл /etc/mysql/my.cnf (или в /etc/mysql/mysql.conf.d/mysqld.cnf, в зависимости от настроек) в секции [mysqld] необходимо добавить следующие параметры:

server_id = 1 # назначает серверу уникальный целочисленный идентификатор
log_bin = mysql-bin # включает двоичный журнал и указывает его расположение

После изменения конфигурации необходимо перезагрузить СУБД

systemctl restart mysql.service

Стоит убедиться, что двоичный журнал включен

SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      158 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0,00 sec)

В более ранних версиях используется команда - SHOW MASTER STATUS;

Репликация средствами mysqldump

Дамп БД средствами mysqldump

Для того, чтобы начать репликацию данных, необходимо “подтянуть” слейв до состояния мастера. Для этого, нужно временно заблокировать сам мастер, чтобы сделать слепок актуальных данных.

FLUSH TABLES WITH READ LOCK;

Далее, с помощью mysqldump сделать экспорт данных из базы.

mysqldump -u root -p sape_test_db > sape_test_db.db

После этого, необходимо еще раз выполнить команду SHOW BINARY LOG STATUS;, и запомнить или записать значения File и Position. Это, так называемые координаты двоичного журнала. Именно от них далее будет указано стартовать слейву.

Теперь мастер можно разблокировать.

UNLOCK TABLES;

Мастер настроен, и готов реплицироваться на другие сервера.

Настройка репликации mysqldump

Настройка слейва для репликации

В первую очередь на слейв необходимо загрузить дамп, полученный с мастера

root@master:~# scp sape_test_db.db slave:/root
sape_test_db.db                                                                                                       100% 2169     2.5MB/s   00:00    
root@master:~#

CREATE DATABASE `sape_test_db`;

mysql -u root -p sape_test_db < /root/sape_test_db.db

Изменить конфигурацию слейва /etc/mysql/my.cnf (или в /etc/mysql/mysql.conf.d/mysqld.cnf, в зависимости от настроек) в секции [mysqld]

log_bin = mysql-bin  # указываем слейву вести собственный двоичный журнал
server_id = 2  # указываем идентификатор сервера
relay-log = /var/lib/mysql/mysql-relay-bin # указываем расположение журнала ретрансляции
relay-log-index = /var/lib/mysql/mysql-relay-bin.index  # этот файл служит перечнем всех имеющихся журналов ретрансляции
read_only = 1  # переводим слейв в режим “только чтение”

После изменения конфигурации необходимо перезагрузить службу

systemctl restart mysql.service

Необходимо указать слейву, какой сервер будет являться для него мастером, и откуда начинать реплицировать данные. Вместо MASTER_LOG_FILE и MASTER_LOG_POS необходимо подставить значения, полученные из SHOW BINARY LOG STATUS; на мастере. Эти параметры вместе называются координатами двоичного журнала.

Адрес мастера указан в hosts, поэтому достаточно указать его имя

Начиная с MySQL 8.4+: GET_SOURCE_PUBLIC_KEY является обязательным параметром, который необходимо добавить - https://stackoverflow.com/questions/69936021/error-002061-authentication-plugin-caching-sha2-password-reported-error-aut

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='master',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='repl_passwd',
  SOURCE_LOG_FILE='mysql-bin.000002',
  SOURCE_LOG_POS=158,
  GET_SOURCE_PUBLIC_KEY=1;

В более ранних версиях используется команда - CHANGE MASTER TO...

Запуск воспроизведения журнала ретрансляции, и проверка статуса репликации mysqldump

start replica;

mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: master
                  Source_User: repl_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000002
          Read_Source_Log_Pos: 158
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 328
        Relay_Source_Log_File: mysql-bin.000002
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 158
              Relay_Log_Space: 539
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: 1337616f-ad64-11ef-ba97-bc2411fcdf96
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0,00 sec)

Если все прошло успешно, ваш статус должен иметь аналогичный вид. Ключевые параметры здесь:

Replica_IO_State, Replica_SQL_Running_State — состояние IO потока, принимающего двоичный журнал с мастера, и состояние потока, применяющего журнал ретрансляции соотвественно. Только наличие обоих потоков свидетельствует об успешном процессе репликации.
Read_Source_Log_Pos — последняя позиция, прочитанная из журнала мастера.
Relay_Source_Log_File — текущий файл журнала мастера.
Seconds_Behind_Source — отставание слейва от мастера, в секундах.
Last_IO_Error, Last_SQL_Error — ошибки репликации, если они есть.

Проверка репликации mysqldump

На мастере добавить данные и убедиться, что они появились на слейве

mysql -u root -p
use sape_test_db;

INSERT admin_table(name, surname, city) VALUES ('Nikita', 'Samoilov', 'Sankt-Petersburg');

mysql> select * from admin_table;
+----+--------+-----------+------------------+
| id | name   | surname   | city             |
+----+--------+-----------+------------------+
|  1 | Andrei | Ahmadulin | Ekaterinburg     |
|  2 | Kit    | Root      | Georgia          |
|  3 | Ilya   | Paramonov | Moscow           |
|  4 | Andrei | Zharkov   | Moscow           |
|  5 | Artem  | Dolgiy    | Zelenograd       |
|  6 | Nikita | Samoilov  | Sankt-Petersburg |
+----+--------+-----------+------------------+
6 rows in set (0,00 sec)

Проверить данные на слейве

mysql> select * from admin_table;
+----+--------+-----------+------------------+
| id | name   | surname   | city             |
+----+--------+-----------+------------------+
|  1 | Andrei | Ahmadulin | Ekaterinburg     |
|  2 | Kit    | Root      | Georgia          |
|  3 | Ilya   | Paramonov | Moscow           |
|  4 | Andrei | Zharkov   | Moscow           |
|  5 | Artem  | Dolgiy    | Zelenograd       |
|  6 | Nikita | Samoilov  | Sankt-Petersburg |
+----+--------+-----------+------------------+
6 rows in set (0,00 sec)

вверх

Репликация - Percona XtraBackup

Установка Percona XtraBackup

Установка согласно документации

Стоит иметь ввиду, что версии MySQL и Percona XtraBackup должны совпадать, иначе создание резервной копии может завершиться ошибкой или невозможностью восстановления.

Порядок установки:

обновление индекса пакетов и установка вспомогательных утилит

su -
apt update && apt install -y curl gnupg2 lsb-release lz4 zstd

установка репозитория percona с помощью пакета

curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
apt install ./percona-release_latest.generic_all.deb

общие репозитории percona будут прописаны после установки пакета

root@master:~# ls -l /etc/apt/sources.list.d/
total 16
-rw-r--r-- 1 root root 662 Jan 23 18:30 mysql.list
-rw-r--r-- 1 root root 294 Jan 23 18:34 percona-pmm2-client-release.list
-rw-r--r-- 1 root root 280 Jan 23 18:34 percona-prel-release.list
-rw-r--r-- 1 root root 290 Jan 23 18:34 percona-telemetry-release.list

после установки общих репозиториев необходимо обновить индекс пакетов

apt update

далее выбирается конкретный релиз, который будет использоваться

percona-release enable pxb-84-lts

в результате выполнения команды будет добавлен репозиторий выбранного релиза

root@master:~# ls -l /etc/apt/sources.list.d/
total 20
-rw-r--r-- 1 root root 662 Jan 23 18:30 mysql.list
-rw-r--r-- 1 root root 294 Jan 23 18:34 percona-pmm2-client-release.list
-rw-r--r-- 1 root root 280 Jan 23 18:34 percona-prel-release.list
-rw-r--r-- 1 root root 292 Jan 24 13:20 percona-pxb-84-lts-release.list
-rw-r--r-- 1 root root 290 Jan 23 18:34 percona-telemetry-release.list

установка Percona XtraBackup

apt update && apt install percona-xtrabackup-84

root@master:~# xtrabackup --version
xtrabackup version 8.4.0-2 based on MySQL server 8.4.0 Linux (x86_64) (revision id: d4373834)

Настройка репликации xtrabackup

https://docs.percona.com/percona-xtrabackup/8.4/set-up-replication.html

xtrabackup --backup --user=root --password --target-dir=/tmp/dump

параметров достаточно много, вот некоторые из них:

--backup - создание резервной копии; --compress - сжатие резервной копии; --stream=xbstream - собрать файлы резервной копии в единый архив; --datadir - директория из которой будут копироваться данные. Если этот параметр не указан, будет использован путь по-умолчанию; --target-dir - директория, в которую будет сохранена резервная копия

также могут использоваться дополнительные параметры, например

--slave-info - указывает, что нужно сохранить информацию о репликации (например, позицию бинарного лога) в резервной копии. Это полезно, если планируется использовать резервную копию для настройки реплики; --use-memory - ограничения на использование памяти; --parallel - использование нескольких ядер процессора для ускорения процесса создания резервной копии; --compress-threads - сжатие в несколько потоков.

Подготовка резервной копии перед восстановлением

xtrabackup --prepare --target-dir=/tmp/dump

Копирование резервной копии на слейв

rsync -avpP -e ssh /tmp/dump slave:/tmp

Восстановление БД на слейве

Остановить сервер БД и переместить имеющиеся данные на слейве

systemctl stop mysql

mkdir /root/mysql_old && mv /var/lib/mysql/* /root/mysql_old

Позднее, если не будет ошибок, перемещённые данные можно будет удалить

rm -rf /root/mysql_old

копирование дампа в целевой каталог слейва

xtrabackup --move-back --target-dir=/tmp/dump --datadir=/var/lib/mysql

установить корректные права

chown -R mysql:mysql /var/lib/mysql

Настройка репликации

Изменить конфигурацию слейва /etc/mysql/my.cnf
!!! (из других конфиг.файлов конфигурация не применялась) !!! в секции [mysqld]

[mysqld]
server_id = 2

Запустить сервер БД

systemctl start mysql

Убедиться в том, что нужные параметры применились можно запросом в БД

mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0,00 sec)

Уточнить значение и позицию bin-log в каталоге, в который скопировали резервную копию с мастера, в данном примере - /tmp/dump

root@slave:~# cat /tmp/dump/xtrabackup_binlog_info
mysql-bin.000002        158

Команда для запуска репликации в консоли mysql

mysql -u root -p

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='master',
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='repl_passwd',
    SOURCE_LOG_FILE='mysql-bin.000002',
    SOURCE_LOG_POS=158,
    GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

SHOW REPLICA STATUS\G

на что обратить внимание в выводе:

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: master
                  Source_User: repl_user
...
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
...
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
...

Проверка репликации xtrabackup