2018-03-19 09:47:17 4416 1
Настройка Master-Slave репликации Mysql
MySQL репликация типа Master-Slave часто используется для обеспечения отказоустойчивости приложений. Кроме этого, она позволяет распределить нагрузку на базу данных между несколькими серверами
Настройка будеи производится на двух серверах с операциооной системой Ubuntu16.04. Master - mysql-node1(10.25.128.116), Slave - mysql-node2(10.25.128.149)
Настройка Master сервера
Устанавливаем mysql
root@mysql-node1:~# apt-get install mysql-server mysql-client
Задаем пароль администратора СУБД
Запускаем скрипт первоначальной настройки Mysql
root@mysql-node1:~# mysql_secure_installation
Securing the MySQL server deployment. -----Вводим пароль администратора Enter password for user root: VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? -----Оставляем пароль администратора без извениний Press y|Y for Yes, any other key for No: Using existing password for root. Change the password for root?((Press y|Y for Yes, any other key for No): no ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. ----- Удаляем ананимных пользователей Remove anonymous users? (Press y|Y for Yes, any other key for No): y Success. Normally, root should only be allowed to connect from "localhost". This ensures that someone cannot guess at the root password from the network. ----- Отключаем возможность подключаения под пользователем root удаленно Disallow root login remotely? (Press y|Y for Yes, any other key for No): y Success. By default, MySQL comes with a database named "test" that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. ------Удаляем тестовую базу данных Remove test database and access to it? (Press y|Y for Yes, any other key for No): y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. ------Перезапуск привелегий Reload privilege tables now? (Press y|Y for Yes, any other key for No): y Success. All done!
Переходим к настройке Master сервера
Создадим базу и таблицу в этой базе
Подключаемся к СУБД под пользователем root
root@mysql-node1:~# mysql -u root -p
Создаем базу:
mysql> CREATE DATABASE repdb; Query OK, 1 row affected (0.00 sec)
Переходим в созданную базу:
mysql> USE repdb Database changed
Создаем таблицу:
mysql> CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(255), age VARCHAR(10), role VARCHAR(100)); Query OK, 0 rows affected (0.26 sec)
Добавим несколько записей в эту таблицу:
mysql> INSERT INTO users (name,age,role) VALUES ("Alexei", 30, "admin");
Query OK, 1 row affected (0.04 sec)
Добавим еще некоторое количество и получаем такую таблицу:
mysql> SELECT * FROM users; +----+--------+------+-------+ | id | name | age | role | +----+--------+------+-------+ | 1 | Alexei | 30 | admin | | 2 | Petya | 20 | user | | 3 | Ivan | 40 | boss | +----+--------+------+-------+
Переходим в файл настроек /etc/mysql/mysql.conf.d/mysqld.cnf и прописываем следующие директивы:
root@mysql-node1:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
... # На каких сетевых адресах ожидать подключения, ставим все bind-address = 0.0.0.0 # Id сервера, произвольное число server-id = 1 # путь к бинарному лог-файлу log_bin = /var/log/mysql/mysql-bin.log #Имя базы, которую будем реплицировать binlog_do_db = repdb ...
Перезапускаем Mysql
root@mysql-node1:~# systemctl restart mysql
Теперь необходимо создать пользователя с правами на репликацию
Подключаемся к СУБД
root@mysql-node1:~# mysql -u root -p
Создаем пользователя:
mysql> GRANT REPLICATION SLAVE ON *.* TO "slave_user"@"%" IDENTIFIED BY "1234567"; Query OK, 0 rows affected, 1 warning (0.04 sec)
Создаем пользователя slave_user с правами на slave репликацию на все базу данных, который может подключаться с луюого хоста. Пароль - 1234567
Обновляем привелегии:
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.04 sec)
Блокируем все таблицы в нашей базе:
mysql> USE repdb Database changed mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec)
Проверяем статус Master:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 595 | repdb | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)>
Колонки "File" и "Postion" мы будем использовать при запуске Slave-севрера.
Теперь необходимо сделать Дамп базы, что бы перенести ее на Slave-сервер, выходим из консоли СУБД и выполняем команду:
root@mysql-node1:~# mysqldump -u root -p repdb > /tmp/repdb.sql
Был созздан файл /tmp/repdb.sql, который содержит Дамп базы repdb
Теперь снова подключаемся к СУБД и разблокируем таблицы
root@mysql-node1:~# mysql -u root -p
mysql> USE repdb Database changed mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
Настройка Slave-сервера
Выполняем теже операции по установке СУБД Mysql
Подключаемся к СУБД и создаем пустую базу repdb
root@mysql-node2:~# mysql -u root -p
mysql> CREATE DATABASE repdb; Query OK, 1 row affected (0.00 sec)
Выходим из консоли СУБД, переносим на Slave-сервер файл с Дампом базу с Master-севрера, например с помощью scp (только предварительно надо настроить ssh-сервер)
root@mysql-node2:~# scp root@10.25.128.116:/tmp/repdb.sql /tmp/ root@10.25.128.116"s password: repdb.sql 100% 2030 2.0KB/s 00:01
теперь загружаем Дамп.
root@mysql-node2:~# mysql -u root -p repdb < /tmp/repdb.sql
Далее переходим к настройке самого СУБД, идем в файл /etc/mysql/mysql.conf.d/mysqld.cnf и правим там директивы
root@mysql-node2:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
... # Указываем отличный от Master-сервера ID server-id = 2 # Путь к relay лог-файлу relay-log = /var/log/mysql/mysql-relay-bin.log # Путь к лог-файлу на Master-сервере log_bin = /var/log/mysql/mysql-bin.log # База для реплики binlog_do_db = repdb ...
Перезапускаем сервер
root@mysql-node2:~# systemctl restart mysql
Теперь необходимо включить репликацию, заходим в консоль СУБД и выполняем команду
root@mysql-node2:~# mysql -u root -p
mysql> CHANGE MASTER TO MASTER_HOST = "10.25.128.116", MASTER_USER = "slave_user", MASTER_PASSWORD = "1234567", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 595; Query OK, 0 rows affected, 2 warnings (0.41 sec)
Где указываем ip-адрес Master-сервера, имя и пароль пользователя для репликации, и данные из ответа команды "SHOW MASTER STATUS", которую выполняли на Master-сервере ранее (File,Position)
Запускаем репликацию на Slave:
mysql> START SLAVE; Query OK, 0 rows affected (0.03 sec)
Проверяем статус репликации:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.25.128.116
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_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_Master_Log_Pos: 154
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: e3eca077-1d30-11e8-a190-00163e54223e
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Все хорошо, теперь проверим работу репликации, добавим на Master-сервере запись в таблицу, а потом проверим появится ли она на Slave
mysql> INSERT INTO users (name,age,role) VALUES ("NEW USER", "99", "test");
Query OK, 1 row affected (0.09 sec)
Теперь переходим на Slave сервер и делаем запрос SELECT на таблицу и видим, что запись сделанная на Mastere появилась и на Slave
mysql> SELECT * FROM users; +----+----------+------+-------+ | id | name | age | role | +----+----------+------+-------+ | 1 | Alexei | 30 | admin | | 2 | Petya | 20 | user | | 3 | Ivan | 40 | boss | | 4 | NEW USER | 99 | test | +----+----------+------+-------+ 4 rows in set (0.00 sec)
Теперь мы сохраним нашу базу в случае падения Mastera или сможем сбалансировать запросы на чтение на оба сервера.
Введите ответ:
+
=
Игорь
2020-02-13 12:57:09
Отличная статья! Думаю, было бы не лишним про версию mysql сказать. И про GTID, SBR/RBR/mixed