: :

ПнВтСрЧтПтСбВс

2018-03-19 09:47:17 2720 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