Решил написать несколько статей о установке, настройке и роботе с mysql базой данных. В качестве mysql-сервера будем использовать Percona Server 5.6 под управлением операционной системой Ubuntu 12.04. Данная серия статей будет полезной для людей, которые впервые сталкиваются с базами данных(БД) mysql, и хотят немного изучить установку, базовые команды(запросы), научиться делать backup и restore данных, настройку и тестирование репликации БД percona mysql.
В этой части пойдет речь о базовой установке и добавлении тестовой БД, репликация которой будет настроена далее.

Поскольку статьи будут взаимосвязаны, наведем схему репликации (рис. 1) и базовую конфигурацию серверов.

1 Установка

Для начала нам нужно установить Percona Server 5.6.
Mysql нужно установить на всех серверах, в нашем случаи на 4-х (2 мастера, 2 слейва). Начинаем установку.

root@m-serv1:~# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /tmp/tmp.igWqa1jBp0 --trustdb-name /etc/apt/trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg: requesting key CD2EFD2A from hkp server keys.gnupg.net
gpg: key CD2EFD2A: public key "Percona MySQL Development Team <mysql-dev@percona.com>" imported
gpg: Total number processed: 1
gpg:               imported: 1
 
root@m-serv1:~# cat >> /etc/apt/sources.list.d/percona-mysql.list
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main
root@m-serv1:~# apt-get update
root@m-serv1:~# apt-get install percona-server-server

При установке, у Вас спросят пароль root-a, который будет использоваться для подключения к mysql серверу – не забудьте его. Далее можно запустить mysql_secure_installation для обновления пароля, удаления ненужных БД и пользователей.

root@m-serv1:~# mysql_secure_installation
Enter current password for root (enter for none):
OK, successfully used password, moving on...
 
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
 
Remove anonymous users? [Y/n] Y
 ... Success!
 
Disallow root login remotely? [Y/n] Y
 ... Success!
 
Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
 
Reload privilege tables now? [Y/n] Y
 ... Success!

Все эти действия нужно проделать на m-serv2, m-slave1 и m-slave2. Теперь можно создать тестовую БД.

2 Добавление данных

2.1 Создание тестовой БД

Можно создать БД (testdb) на одном сервере, сделать ее дамп и развернуть на всех остальных. Что мы и сделаем.
Подключаемся на m-serv1 к mysql консоли и создаем тестовую БД.

root@m-serv1:~# mysql -u root -p
mysql> CREATE DATABASE testdb;

Далее добавим таблицу в новую БД.

mysql> CREATE TABLE IF NOT EXISTS testdb.users (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20));

Теперь добавим одну строку в таблицу users.

mysql> INSERT INTO users(name) VALUES ("Alex");

Далее проверим что у нас получилось.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
5 rows in set (0.02 sec)
 
mysql> use testdb; show tables;
Database changed
+------------------+
| Tables_in_testdb |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)
 
mysql> use testdb; select * from users;
Database changed
+----+------+
| id | name |
+----+------+
|  1 | Alex |
+----+------+
1 row in set (0.00 sec)
 
mysql> use testdb; describe users;
Database changed
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

Как видим, мы успешно создали БД testdb и в ней создали таблицу users, в которую добавили новую запись.

2.2 Дамп и деплой БД на всех серверах

Снимаем дамп нашей новой БД и копируем его на второй мастер m-serv2.

root@m-serv1:~# mysqldump -u root -p testdb  > testdb.sql
root@m-serv1:~# rsync testdb.sql alex@m-serv2:/home/alex/

Теперь логинимся на второй сервер и разворачиваем дамп.

root@m-serv2:~# mysql -u root -p -e 'create database testdb;'
root@m-serv2:~# mysql -uroot -p testdb < testdb.sql
root@m-serv2:~# mysql -u root -p -e 'use testdb; select * from users;'
+----+------+
| id | name |
+----+------+
|  1 | Alex |
+----+------+

Те же действия нужно проделать со слейвами(m-slave1 и m-slave2), т.е. синкануть БД на оба слейва и развернуть таким же макаром.

2.3 Создание юзера для репликации

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

mysql> CREATE USER 'replica'@'%' IDENTIFIED BY '%repl2015';
Query OK, 0 rows affected (0.22 sec)
 
mysql> GRANT replication slave ON *.* TO 'replica'@'%';
Query OK, 0 rows affected (0.10 sec)

Как вы поняли, эти действия нужно проделать на всех 4-х серверах.

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

В mysql существует два типа репликации данных:

• Master-Slave
• Master-Master

Master-Slave репликация. На Master сервере данные добавляются, удаляются и изменяются. Slave сервер стягивает эти обновления себе и постепенно выполняет все полученные запросы. Если на Slave сервере будет добавлена новая таблица или БД, то данные не попадут на Master.
При Master-Master репликации данные попавшие на оба сервера будут среплицированы между собой.

3.1 Master-Master репликация

Сначала настроим Мастер-Мастер репликацию (рис. 2).

Теперь переходим к настройке репликации.
Для этого нам нужно добавить конфигурационный файл /etc/mysql/my.cnf для каждого mysql-сервера, который входит в репликацию. Здесь нужно прописать уникальный идентификатор сервера и БД, которые нужно и не нужно реплицировать. Также здесь прописывается множество дополнительных настроек mysql сервера, о которых можно узнать на официальном сайте. Я же наведу самую нужную малость.

3.1.1 Настройка m-serv1 мастера

Сначала настроим первый мастер-сервер.

root@m-serv1:~# cat /etc/mysql/my.cnf
[mysqld]
#Уникальный идентификатор сервера
server-id = 1
 
#Логи ошибок
log_error = /var/log/mysql/mysql.err
 
#Путь к bin-логам сервера(бинлог, который ведет мастер)
log-bin = /var/lib/mysql/server-mysql-bin
log-bin-index = /var/lib/mysql/server-mysql-bin.index
 
#Путь к relay-логам слейва (бинлог, скачанный с мастера)
relay-log = /var/lib/mysql/slave-mysql-relay-bin
relay-log-index = /var/lib/mysql/slave-mysql-relay-bin.index
 
#БД, которые нужно/не нужно реплицировать
replicate-do-db = testdb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
 
#Не вести журнал бин-лога для БД
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
 
#Чтобы не было конфликтов автоинкремента, говорим серверу,
#чтобы id генерировались начиная с 3-го прибавляя по 10,
# например 13, 23, 33, 43...
auto_increment_increment = 10
auto_increment_offset = 3
 
#Сохранять логи с мастера в своий бин-лог, чтобы передать слейву
log-slave-updates

Как видим, здесь мы добавили для репликации только testdb БД. Теперь рестартуем mysql.

root@m-serv1:~# /etc/init.d/mysql restart
 * Stopping MySQL (Percona Server) mysqld                                                                                                                       [ OK ]
 * Starting MySQL (Percona Server) database server mysqld                                                                                                       [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

3.1.2 Настройка m-serv2 мастера

Настройка второго мастера аналогична первому, только меняется id и offset

root@m-serv2:~# cat /etc/mysql/my.cnf
[mysqld]
#Уникальный идентификатор сервера
server-id = 2
 
#Логи ошибок
log_error = /var/log/mysql/mysql.err
 
#Путь к bin-логам сервера(бинлог, который ведет мастер)
log-bin = /var/lib/mysql/server-mysql-bin
log-bin-index = /var/lib/mysql/server-mysql-bin.index
 
#Путь к relay-логам слейва (бинлог, скачанный с мастера)
relay-log = /var/lib/mysql/slave-mysql-relay-bin
relay-log-index = /var/lib/mysql/slave-mysql-relay-bin.index
 
#БД, которые нужно/не нужно реплицировать
replicate-do-db = testdb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
 
#Не вести журнал бин-лога для БД
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
 
#Чтобы не было конфликтов автоинкремента, говорим серверу,
#чтобы id генерировались начиная с 4-го прибавляя по 10,
# например 14, 24, 34, 44...
auto_increment_increment = 10
auto_increment_offset = 4
 
#Сохранять логи с мастера в своий бин-лог, чтобы передать слейву
log-slave-updates

Рестартуем mysql.

root@m-serv2:~# /etc/init.d/mysql restart
 * Stopping MySQL (Percona Server) mysqld                                                                                                                       [ OK ]
 * Starting MySQL (Percona Server) database server mysqld                                                                                                       [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

3.1.3 Запуск репликации

Сначала запустим репликацию на первом мастере m-serv1. Для этого нам нужно знать MASTER_LOG_FILE и MASTER_LOG_POS m-serv2 сервера, т.е. нашего второго мастера. Логинимся на m-serv2 и смотрим master status.

root@m-serv2:~# mysql -u root -p -e 'show master status;'
+-------------------------+----------+--------------+------------------------------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------------------------------+-------------------+
| server-mysql-bin.000001 |      120 |              | information_schema,mysql,performance_schema      |                   |
+-------------------------+----------+--------------+------------------------------------------+-------------------+

Следовательно MASTER_LOG_FILE = server-mysql-bin.000001, а MASTER_LOG_POS = 120. Теперь переходим на m-serv1 и настраиваем репликацию.

root@m-serv1:~# mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST = '192.168.1.202', MASTER_USER = 'replica', MASTER_PASSWORD = '%repl2015', MASTER_LOG_FILE = 'server-mysql-bin.000001', MASTER_LOG_POS = 120;"

Все интуитивно понятно. Теперь стартуем слейв и смотрим статус.

root@m-serv1:~# mysql -u root -p -e 'start slave;'
root@m-serv1:~# mysql -u root -p -e 'show slave status G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.202
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: server-mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: slave-mysql-relay-bin.000002
                Relay_Log_Pos: 290
        Relay_Master_Log_File: server-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testdb
          Replicate_Ignore_DB: information_schema,mysql,performance_schema
           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: 120
              Relay_Log_Space: 469
              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: 2
                  Master_UUID: 25f9f3ac-fd3b-11e4-bb77-080027ead940
             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 the slave I/O thread to update it
           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

Со всего этого вывода нас интересуют Seconds_Behind_Master (время отставания реплики от мастера), Slave_IO_State (должно писать, что ждет новостей от мастера), Slave_IO_Running (Yes) и Slave_SQL_Running (Yes). Если репликация идет нормально, реплика будет следовать за мастером (номер лога в Master_Log_File и позиция Exec_Master_Log_Pos будут расти). Отставания реплики от мастера (Seconds_Behind_Master), должно быть нулевым, но может расти. Если же значение Slave_IO_State пусто, а Seconds_Behind_Master равно NULL, репликация не началась.
У нас все гуд. Поэтому узнаем master статус на m-serv1 и беремся за m-serv2.

root@m-serv1:~# mysql -u root -p -e 'show master status;'
+-------------------------+----------+--------------+------------------------------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------------------------------+-------------------+
| server-mysql-bin.000005 |      120 |              | information_schema,mysql,performance_schema      |                   |
+-------------------------+----------+--------------+------------------------------------------+-------------------+

Логинимся на m-serv2 и стартуем репликация.

root@m-serv2:~# mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST = '192.168.1.201', MASTER_USER = 'replica', MASTER_PASSWORD = '%repl2015', MASTER_LOG_FILE = 'server-mysql-bin.000005', MASTER_LOG_POS = 120;"
root@m-serv2:~# mysql -u root -p -e 'start slave;'
root@m-serv2:~# mysql -u root -p -e 'show slave status G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.201
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: server-mysql-bin.000005
          Read_Master_Log_Pos: 120
               Relay_Log_File: slave-mysql-relay-bin.000002
                Relay_Log_Pos: 290
        Relay_Master_Log_File: server-mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testdb
          Replicate_Ignore_DB: information_schema,mysql,performance_schema
           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: 120
              Relay_Log_Space: 469
              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: f208be92-fa66-11e4-a905-08002742f2f0
             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 the slave I/O thread to update it
           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

Все прошло успешно. О парочке возможных ошибок и их исправлении будет написано в следующей статье.

3.1.4 Тестируем репликацию

Теперь можно немножко и протестировать. Перейдем на m-serv1 и добавим в testdb.users новую строку.

root@m-serv1:~# mysql -u root -p -e 'USE testdb; INSERT INTO users(name) VALUES ("Vova");'
root@m-serv1:~# mysql -u root -p -e 'USE testdb; SELECT * FROM users;'
+----+------+
| id | name |
+----+------+
|  1 | Alex |
|  3 | Vova |
+----+------+

Теперь проверим среплицировалась ли запись на второй сервер.

root@m-serv2:~# mysql -u root -p -e 'USE testdb;SELECT * FROM users;'
+----+------+
| id | name |
+----+------+
|  1 | Alex |
|  3 | Vova |
+----+------+

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

root@m-serv2:~# mysql -u root -p -e 'USE testdb; INSERT INTO users(name) VALUES ("Pasha");'
root@m-serv2:~# mysql -u root -p -e 'USE testdb; SELECT * FROM users;'
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  3 | Vova  |
|  4 | Pasha |
+----+-------+

Смотрим на первом мастере.

root@m-serv1:~# mysql -u root -p -e 'USE testdb; SELECT * FROM users;'
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  3 | Vova  |
|  4 | Pasha |
+----+-------+

Как видим, все ок. Добавим еще по одной записи.

root@m-serv1:~# mysql -u root -p -e 'USE testdb;INSERT INTO users(name) VALUES ("Frodo");'
root@m-serv1:~# mysql -u root -p -e 'USE testdb; SELECT * FROM users;'
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  3 | Vova  |
|  4 | Pasha |
| 13 | Frodo |
+----+-------+
 
root@m-serv2:~# mysql -u root -p -e 'USE testdb; INSERT INTO users(name) VALUES ("Misha");'
root@m-serv2:~# mysql -u root -p -e 'USE testdb; SELECT * FROM users;'
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  3 | Vova  |
|  4 | Pasha |
| 13 | Frodo |
| 14 | Misha |
+----+-------+

Как видим, если запись добавлена с сервера m-serv1, то поле auto_increment(id) имеет значения 3, 13, а при добавлении записей с m-serv2, эти значения равны 4, 14. Это нужно чтобы избежать ошибок типа Duplicate entry.

Продолжим знакомство с Percona mysql репликацией и перейдем к настройке и тестированию Master-Slave репликации. Как и в предыдущих статьях, наведу рисунок нашей схемы репликации, которую мы затеяли (рис. 3).

3.2 Master-Slave репликация

Мастер-Мастер репликация была настроена, теперь можно добавлять слейвы (рис. 3). Для этого нам нужно добавить конфигурационный файл /etc/mysql/my.cnf для каждого mysql-слейва, который входит в репликацию.

3.2.1 Настройка m-slave1 слейва

Сначала настроим первый слейв-сервер.

root@m-slave1:~# cat /etc/mysql/my.cnf
[mysqld]
#Уникальный идентификатор сервера
server-id = 3
 
#Логи ошибок
log_error = /var/log/mysql/mysql.err
 
#Путь к relay-логам слейва (бинлог, скачанный с мастера)
relay-log = /var/lib/mysql/slave-mysql-relay-bin
relay-log-index = /var/lib/mysql/slave-mysql-relay-bin.index
 
#БД, которые нужно/не нужно реплицировать
replicate-do-db = testdb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
 
#Чтобы не было конфликтов автоинкремента, говорим серверу,
#чтобы id генерировались начиная с 4-го прибавляя по 10,
# например 11, 21, 31, 41...
auto_increment_increment = 10
auto_increment_offset = 1

Теперь рестартуем mysql

root@m-slave1:~# /etc/init.d/mysql restart
 * Stopping MySQL (Percona Server) mysqld                                                                                                                       [ OK ]
 * Starting MySQL (Percona Server) database server mysqld                                                                                                       [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

3.2.2 Настройка m-slave2 слейва

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

root@m-slave2:~# cat /etc/mysql/my.cnf
[mysqld]
#Уникальный идентификатор сервера
server-id = 4
 
#Логи ошибок
log_error = /var/log/mysql/mysql.err
 
#Путь к relay-логам слейва (бинлог, скачанный с мастера)
relay-log = /var/lib/mysql/slave-mysql-relay-bin
relay-log-index = /var/lib/mysql/slave-mysql-relay-bin.index
 
#БД, которые нужно/не нужно реплицировать
replicate-do-db = testdb
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
 
#Чтобы не было конфликтов автоинкремента, говорим серверу,
#чтобы id генерировались начиная с 4-го прибавляя по 10,
# например 12, 22, 32, 42...
auto_increment_increment = 10
auto_increment_offset = 2

Теперь рестартуем mysql

root@m-slave2:~# /etc/init.d/mysql restart
 * Stopping MySQL (Percona Server) mysqld                                                                                                                       [ OK ]
 * Starting MySQL (Percona Server) database server mysqld                                                                                                       [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

3.2.3 Запуск репликации

Если вы следовали  пункту 3.1.4  (Тестирование мастер репликации) после настройки Мастер – Мастер репликации, то на обоих слейвах нужно разворачивать новый дамп testdb, так как в эту БД добавлялись данные. Т.е. для слейва m-slave1 нужно снять дамп с мастера m-serv1 и развернуть (описано в  пункте 2.2 Дамп и деплой), для m-slave2 дамп нужно снять с m-serv2 и развернуть. После того, как дамп будет развернут, у нас будут следующие данные в БД testdb.

root@m-slave1:~# mysql -u root -p -e 'use testdb;select * from users;'
Enter password:
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  3 | Vova  |
|  4 | Pasha |
| 13 | Frodo |
| 14 | Misha |
+----+-------+

Т.е. те же данные, что остались на мастер-серверах после тестирования Масте-Мастер репликации.
Теперь осталось запустить репилкацию. В случаи с m-slave1 мастер сервер должен быть m-serv1, поэтому переходим на первый мастер сервер и смотрим MASTER_LOG_FILE и MASTER_LOG_POS.

root@m-serv1:~# mysql -u root -p -e 'show master status;'
Enter password:
+-------------------------+----------+--------------+--------------------------------------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+-------------------------+----------+--------------+--------------------------------------------------+-------------------+
| server-mysql-bin.000006 |      120 |              | information_schema,mysql,performance_schema      |                   |
+-------------------------+----------+--------------+--------------------------------------------------+-------------------+

Следовательно MASTER_LOG_FILE = server-mysql-bin.000006, а MASTER_LOG_POS = 120. Теперь переходим обратно на m-slave1 и настраиваем репликацию.

root@m-slave1:~# mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST = '192.168.1.201', MASTER_USER = 'replica', MASTER_PASSWORD = '%repl2015', MASTER_LOG_FILE = 'server-mysql-bin.000006', MASTER_LOG_POS = 120;"

Все интуитивно понятно. Теперь стартуем слейв и смотрим статус.

root@m-slave1:~# mysql -u root -p -e 'start slave;'
root@m-slave1:~# mysql -u root -p -e 'show slave status G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.201
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: server-mysql-bin.000006
          Read_Master_Log_Pos: 120
               Relay_Log_File: slave-mysql-relay-bin.000002
                Relay_Log_Pos: 290
        Relay_Master_Log_File: server-mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testdb
          Replicate_Ignore_DB: information_schema,mysql,performance_schema
           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: 120
              Relay_Log_Space: 469
              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: f208be92-fa66-11e4-a905-08002742f2f0
             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 the slave I/O thread to update it
           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

Все ок. Теперь делаем те же движения для m-slave2. Переходим на второй мастер сервер и смотрим MASTER_LOG_FILE и MASTER_LOG_POS.

root@m-serv2:~# mysql -u root -p -e 'show master status;'
Enter password:
+-------------------------+----------+--------------+--------------------------------------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+-------------------------+----------+--------------+--------------------------------------------------+-------------------+
| server-mysql-bin.000002 |      120 |              | information_schema,mysql,performance_schema      |                   |
+-------------------------+----------+--------------+--------------------------------------------------+-------------------+

Следовательно MASTER_LOG_FILE = server-mysql-bin.000002, а MASTER_LOG_POS = 120. Теперь переходим обратно на m-slave2 и настраиваем репликацию.

root@m-slave2:~# mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST = '192.168.1.202', MASTER_USER = 'replica', MASTER_PASSWORD = '%repl2015', MASTER_LOG_FILE = 'server-mysql-bin.000002', MASTER_LOG_POS = 120;"

Все интуитивно понятно. Теперь стартуем слейв и смотрим статус.

root@m-slave2:~# mysql -u root -p -e 'start slave;'
root@m-slave2:~# mysql -u root -p -e 'show slave status G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.202
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: server-mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: slave-mysql-relay-bin.000002
                Relay_Log_Pos: 290
        Relay_Master_Log_File: server-mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testdb
          Replicate_Ignore_DB: information_schema,mysql,performance_schema
           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: 120
              Relay_Log_Space: 469
              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: 2
                  Master_UUID: 25f9f3ac-fd3b-11e4-bb77-080027ead940
             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 the slave I/O thread to update it
           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

3.2.4 Тестируем репликацию

Тестируем всю нашу цепочку репликации (рис. 1). Перейдем на первый мастер сервер и добавим новую запись в testdb.users.

root@m-serv1:~# mysql -u root -p -e 'USE testdb; INSERT INTO users(name) VALUES ("Server1 record");'
root@m-serv1:~# mysql -u root -p -e 'select * from testdb.users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
+----+----------------+

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

root@m-slave1:~# mysql -u root -p -e 'use testdb;select * from users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
+----+----------------+
 
root@m-slave2:~# mysql -u root -p -e 'use testdb;select * from users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
+----+----------------+
 
root@m-serv2:~# mysql -u root -p -e 'use testdb;select * from users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
+----+----------------+

Как видим, все в порядке. Теперь добавим запись на втором мастере.

root@m-serv2:~# mysql -u root -p -e 'USE testdb; INSERT INTO users(name) VALUES ("Server2 record");'
root@m-serv2:~# mysql -u root -p -e 'select * from testdb.users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
| 24 | Server2 record |
+----+----------------+

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

root@m-slave1:~# mysql -u root -p -e 'use testdb;select * from users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
| 24 | Server2 record |
+----+----------------+
 
root@m-slave2:~# mysql -u root -p -e 'use testdb;select * from users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
| 24 | Server2 record |
+----+----------------+
 
root@m-serv1:~# mysql -u root -p -e 'use testdb;select * from users;'
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Alex           |
|  3 | Vova           |
|  4 | Pasha          |
| 13 | Frodo          |
| 14 | Misha          |
| 23 | Server1 record |
| 24 | Server2 record |
+----+----------------+

Как видим, репликация работает как и предполагалось.

4. Распространенные ошибки

==========================================================================
Ошибка: ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
Решение:

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.201', MASTER_USER='rep_user', MASTER_PASSWORD='rep_user', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
 
mysql> start slave;

==========================================================================
Ошибки:

• Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND for deleting row
• Can’t drop database ‘********’: database doesn’t exist’
• Error ‘Duplicate entry’
• Could not execute Write_rows event on table ***********: Duplicate entry ‘XXXXXXXX’ for key ‘ххххххх’, Error_code: 1062

Решение: Эти ошибки можно просто скипнуть, но посмотреть их причины сначала.

mysql -uroot -p -e 'STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;'

==========================================================================
Ошибка: Relay log read failure (#1594): Could not parse relay log event entry.
Решение:

#Подключаемся к серверу, где возникла проблема и смотрим статус репликации
root@server:~# mysql -uroot -p -e 'show slave status G;' | grep -E 'Relay_Master_Log_File|Exec_Master_Log_Pos'
        Relay_Master_Log_File: mysql-bin.008189
      Exec_Master_Log_Pos: 71687831
 
# Останавливаем репликацию и обновляем бин-лог и позицию
#master_log_file = Relay_Master_Log_File = mysql-bin.008189
#master_log_pos = Exec_Master_Log_Pos = 71687831
root@server:~# mysql -uroot -p -e "STOP SLAVE;"
root@server:~# mysql -uroot -p -e "CHANGE MASTER TO master_log_file='mysql-bin.008189', master_log_pos=71687831;"
 
#Стартуем слейв
root@server:~# mysql -uroot -p218e5ccb4a834382%FBF87B604F1FE14B -e "START SLAVE;"

==========================================================================

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

5.1 Резервное копирования и восстановление БД

Дамп одной базы данных.

mysqldump -u root -p companyDB  > company_DB.sql

Дамп нескольких баз данных.

mysqldump -u root -p --databases companyDB projectsDB  > company_and_projects_DBs.sql

Дамп всех баз данных.

mysqldump -u root -p --all-databases > all_DBs.sql

Дамп одной таблицы.

mysqldump -u root -p companyDB employeesTB > companyDB_employeesTB.sql

Дамп структуры базы данных.

mysqldump -u root -p --no-data companyDB > companyDB_structure.sql

Дамп только триггеров, процедур и событий с gzip-ом.

mysqldump --no-create-info --no-data --triggers --routines --events –u root -p companyDB| gzip > companyDB.sql.gz

Простой скрипт дампа для cron-a.

#!/bin/bash
# Список БД через пробел
dbs="cacti_db inventory_db monitor_db"
# Маленький лог файл для хранение информиции о старте бекапа
echo "`date +%Y-%m-%d` - Start sync backups" >> /tmp/backup.log
for databases in $dbs
do
        /usr/bin/mysqldump --max_allowed_packet=512M -uroot -p'your_pass_here' $databases | gzip > /tmp/`date +${databases}.sql.%Y-%m-%d.gz`
        if [ "$?" -eq 0 ]
        then
                /usr/bin/rsync --remove-source-files -a /tmp/`date +${databases}.sql.%Y-%m-%d.gz` mystorage.company.com:/backups/
        else
                echo "`date +%Y-%m-%d` - FAILED to sync $databases" >> /tmp/backup.log
        fi
done

Развернуть дамп существующей базы данных, способ 1.

mysql -u root -p companyDB < companyDB.sql

Развернуть дамп существующей базы данных, способ 2.

mysql> use companyDB;
mysql> source companyDB.sql

Развернуть дамп существующей базы данных из архива.

zcat companyDB.sql.gz | mysql -u root -p companyDB

Развернуть дамп несуществующий базы данных, способ 1.

mysql> create database companyDB;
mysql> use companyDB;
mysql> source companyDB.sql;

Развернуть дамп несуществующий базы данных, способ 2.

mysql -u root -p -e "create database companyDB;"
mysql -u root -p companyDB < companyDB.sql

5.2 Переменные, состояние и статус БД

Проверить состояние БД.

mysqladmin -u root -p ping

Узнать версию БД.

mysqladmin -u root -p version
или
mysql> select version();

Статус БД.

mysqladmin -u root -p status
или
mysql> status;

Здесь,

• Uptime: сколько секунд работает mysql сервер.
• Threads: суммарное количество подключений к mysql серверу.
• Questions: суммарное количество выполненных запросов за время работы сервера.
• Slow queries: суммарное число запросов, выполненных за время, которое превышает long_query_time.
• Opens: суммарное количество открытых таблиц сервером.
• Flush tables: сколько раз таблица была flushed.
• Open tables: суммарное количество открытых таблиц в базе.

Список переменных статуса mysql.

mysqladmin -u root -p extended-status
или
mysql -u root -p -e 'show status;'

Список всех системных(глобальных) переменных сервера.

mysqladmin -u root -p variables
или
mysql> show variables;

Список процессов/запросов в mysql.

mysqladmin -u root -p processlist
или
mysql> show processlist;

Обновлять каждую секунду список процессов.

mysqladmin -u root -p -i 1 processlist

Список золочёных таблиц.

mysql -u root -p -e 'show open tables WHERE In_use > 0;'

Перезагрузка(запись изменений) привилегий.

mysqladmin -u root -p reload

Очистка (закроет все базы и переоткроет логи).

mysqladmin -u root -p refresh

Потушить сервер.

mysqladmin -u root -p shutdown

Stop/start репликации.

mysqladmin  -u root -p stop-slave
mysqladmin  -u root -p start-slave

5.3 Работа со базами данных и таблицами

Поменять пароль root-а.

mysqladmin -u root -p'old_pass' password 'new_pass'
или
mysql> use mysql;
mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

Создать БД.

mysqladmin -u root -p create test1db
или
mysql> create database test1db;

Удалить БД.

mysqladmin -u root -p drop test1db
или
mysql> drop test1db;

Список flush-команд.

mysqladmin -u root -p flush-hosts
mysqladmin -u root -p flush-logs
mysqladmin -u root -p flush-privileges
mysqladmin -u root -p flush-status
mysqladmin -u root -p flush-tables
mysqladmin -u root -p flush-threads

Несколько команд в одной.

mysqladmin -u root -p processlist stat

Посмотреть существующие базы.

mysqlshow -u root -p

Посмотреть все таблицы конкретной БД.

mysqlshow -u root -p test1db

… с количеством полей

mysqlshow -v -u root -p test1db

… с количеством полей и строк

mysqlshow -v -v -u root -p test1db

… со структурой таблицы

mysqlshow -u root -p mysql servers

… со структурой таблицы по конкретному полю

mysqlshow -u root -p mysql servers server_name

5.4 Работа с пользователями

Создать нового юзера

mysql> create user 'user1'@'localhost' identified by 'user1pass';

Добавить все права на все базы и все таблицы.

#GRANT [тип прав] ON [название БД].[название таблицы] TO '[имя пользователя]'@'localhost';
mysql> grant ALL PRIVILEGES ON *.* to 'user1'@'localhost';
mysql> flush privileges;

Удаление прав для пользователя.

#REVOKE [тип прав] ON [название БД].[название таблицы] FROM '[имя пользователя]'@'localhost';
mysql> revoke ALL on *.* from 'user1'@'localhost';

Удалить пользователя.

mysql> drop user 'user1'@'localhost';

5.4.1 Сброс пароля root, если забыли.

1. Остановка сервера

# /etc/init.d/mysql stop

2. Запуск mysql со скипом привилегий

mysqld --skip-grant-tables --user=mysql &
или
mysqld_safe --skip-grant-tables &

3. Логинимся в БД без пароля. Пароль хэшируется с помощью функции PASSWORD(str). Это специальная функция, которая возвращает строку 16-byte и используется системой аутентификации исключительно mysql сервером. Поэтому, используйте MD5 или SHA1 для хранения паролей приложений, которые юзают mysql, но не PASSWORD.

mysql> use mysql;
mysql> select Host,User,Password  from user;
+-----------+------------------+-------------------------------------------+
| Host      | User             | Password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *30657C01D8312B05274147BF51D53702F90CB68C |
| ubuntu    | root             | *30657C01D8312B05274147BF51D53702F90CB68C |
| 127.0.0.1 | root             | *30657C01D8312B05274147BF51D53702F90CB68C |
| ::1       | root             | *30657C01D8312B05274147BF51D53702F90CB68C |
| localhost |                  |                                           |
| ubuntu    |                  |                                           |
| localhost | debian-sys-maint | *90A841D52ED52171F0500125A2460FA4443BCF7F |
+-----------+------------------+-------------------------------------------+
7 rows in set (0.00 sec)

4. Теперь меняем пароль.

mysql> update user set Password=PASSWORD('alex1111') WHERE User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

5. Так как данная информация храниться в mysql кэше — нужно его освободить, иначе пароль останется закешированным и дела не будет

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

6. Теперь останавливаем mysqld, который запускали вручную и запускаем init-скрипт.

kill `pgrep mysql`
/etc/init.d/mysql start
 * Starting MySQL (Percona Server) database server mysqld

Установка и настройка Percona XtraDB Cluster

Поднять mysql кластер можно в несколько раз быстрее используя такую вещь, как Percona XtraDB Cluster, о чем и пойдет речь в данной статье.

Создадим Percona XtraDB Cluster с трех серверов под управлением операционной системы Ubuntu 12.04.
Установка будет произведена из репозитория. Для начала, логинимся на первый сервер, добавляем ключик и репозитории.

root@pxc1:~#apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
root@pxc1:~#vim /etc/apt/sources.list.d/pxe.list
…
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main
...
root@pxc1:~#apt-get update

Теперь ставим непосредственно Percona XtraDB Cluster пакет.

root@pxc1:~#apt-get install percona-xtradb-cluster-56

Во время установки нужно будет ввести пароль для доступа к mysql БД.
Перед добавлением ноды в кластер, нужно стопнуть mysql.

root@pxc1:~#/etc/init.d/mysql stop

Добавляем главный конфигурационных файл для первого mysql PXC1 сервера.

root@pxc1:~#cat /etc/mysql/my.cnf
…
[mysqld]
 
# Логи
log_error = /var/log/mysql/mysql.err
# Директория с БД
datadir=/var/lib/mysql
# Пользователь
user=mysql
# Путь к Galera модуля
wsrep_provider=/usr/lib/libgalera_smm.so
# URL с IP адресами серверов, которые входят в кластер
wsrep_cluster_address=gcomm://192.168.1.150,192.168.1.151,192.168.1.152
# Формат бинлогов
binlog_format=ROW
# Дефолтный механизм хранения данных
default_storage_engine=InnoDB
# Режим лока при работе с автоинкремент значениями
innodb_autoinc_lock_mode=2
 
# Адрес первой ноды в кластере
wsrep_node_address=192.168.1.150
 
# Метод передачи снепшотов БД
wsrep_sst_method=xtrabackup-v2
 
# Имя кластера
wsrep_cluster_name=test_mysql_cluster
 
# Аутентификация для SST
wsrep_sst_auth="sstuser:DOGUQpj0Se8Q9oy7"
…

Теперь можно стартовать mysql сервер в режиме bootstrap.

root@pxc1:~#/etc/init.d/mysql bootstrap-pxc

После этого у нас добавиться первая нода в кластер. Для проверки, можно запустить команду.

root@pxc1:~# mysql -u root -p -e "show status like 'wsrep%';" | grep -E 'local_state|cluster|ready|connected'
 
wsrep_local_state_uuid  b2cf979d-54ca-11e5-9bb4-628c43a251a6
wsrep_local_state       4
wsrep_local_state_comment       Synced
wsrep_cluster_conf_id   1
wsrep_cluster_size      1
wsrep_cluster_state_uuid        b2cf979d-54ca-11e5-9bb4-628c43a251a6
wsrep_cluster_status    Primary
wsrep_connected ON
wsrep_ready     ON

Этот вывод означает, что все ок. Теперь нужно добавить юзера с привилегиями для SST операций, которого мы добавили в конце my.cnf файла.

root@pxc1:~# mysql -u root -p
 
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'DOGUQpj0Se8Q9oy7';
Query OK, 0 rows affected (1.27 sec)
 
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Query OK, 0 rows affected (0.62 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.48 sec)

Далее переходим к настройке второго сервера. Устанавливаем mysql Percona XtraDB Cluster таким же образом и переходим к редактированию my.cnf файла.

root@pxc2:~#cat /etc/mysql/my.cnf
…
[mysqld]
 
# Логи
log_error = /var/log/mysql/mysql.err
# Директория с БД
datadir=/var/lib/mysql
# Пользователь
user=mysql
# Путь к Galera модуля
wsrep_provider=/usr/lib/libgalera_smm.so
# URL с IP адресами серверов, которые входят в кластер
wsrep_cluster_address=gcomm://192.168.1.150,192.168.1.151,192.168.1.152
# Формат бинлогов
binlog_format=ROW
# Дефолтный механизм хранения данных
default_storage_engine=InnoDB
# Режим лока при работе с автоинкремент значениями
innodb_autoinc_lock_mode=2
 
# Адрес первой ноды в кластере
wsrep_node_address=192.168.1.151
 
# Метод передачи снепшотов БД
wsrep_sst_method=xtrabackup-v2
 
# Имя кластера
wsrep_cluster_name=test_mysql_cluster
 
# Аутентификация для SST
wsrep_sst_auth="sstuser:DOGUQpj0Se8Q9oy7"
…

И стартуем mysql в нормально режиме.

root@pxc2:~#/etc/init.d/mysql start

После этого, второй сервер добавиться в кластер. Проверяем.

root@pxc2:~# mysql -u root -p -e "show status like 'wsrep%';" | grep -E 'local_state|cluster|ready|connected'
 
wsrep_local_state_uuid  b2cf979d-54ca-11e5-9bb4-628c43a251a6
wsrep_local_state       4
wsrep_local_state_comment       Synced
wsrep_cluster_conf_id   4
wsrep_cluster_size      2
wsrep_cluster_state_uuid        b2cf979d-54ca-11e5-9bb4-628c43a251a6
wsrep_cluster_status    Primary
wsrep_connected ON
wsrep_ready     ON

Ну и добавляем последнюю ноду. Устанавливаем Percona XtraDB Cluster и создаем my.cnf.

root@pxc3:~#cat /etc/mysql/my.cnf
…
[mysqld]
 
# Логи
log_error = /var/log/mysql/mysql.err
# Директория с БД
datadir=/var/lib/mysql
# Пользователь
user=mysql
# Путь к Galera модуля
wsrep_provider=/usr/lib/libgalera_smm.so
# URL с IP адресами серверов, которые входят в кластер
wsrep_cluster_address=gcomm://192.168.1.150,192.168.1.151,192.168.1.152
# Формат бинлогов
binlog_format=ROW
# Дефолтный механизм хранения данных
default_storage_engine=InnoDB
# Режим лока при работе с автоинкремент значениями
innodb_autoinc_lock_mode=2
 
# Адрес первой ноды в кластере
wsrep_node_address=192.168.1.152
 
# Метод передачи снепшотов БД
wsrep_sst_method=xtrabackup-v2
 
# Имя кластера
wsrep_cluster_name=test_mysql_cluster
 
# Аутентификация для SST
wsrep_sst_auth="sstuser:DOGUQpj0Se8Q9oy7"
…

И стартуем mysql.

root@pxc3:~#/etc/init.d/mysql start

Проверяем статус кластера.

root@pxc3:~# mysql -u root -p -e "show status like 'wsrep%';" | grep -E 'local_state|cluster|ready|connected'
 
wsrep_local_state_uuid  b2cf979d-54ca-11e5-9bb4-628c43a251a6
wsrep_local_state       4
wsrep_local_state_comment       Synced
wsrep_cluster_conf_id   7
wsrep_cluster_size      3
wsrep_cluster_state_uuid        b2cf979d-54ca-11e5-9bb4-628c43a251a6
wsrep_cluster_status    Primary
wsrep_connected ON
wsrep_ready     ON

Как видим, у нас все 3 ноды успешно добавлены в кластер. Теперь можно протестировать работу.
Переходим на первую ноду, создаем тестовую БД, таблицу и добавляем запись в неё.

root@pxc1:~# mysql -u root –p
 
mysql> CREATE DATABASE firstDB;
Query OK, 1 row affected (0.01 sec)
 
mysql> USE firstDB;
Database changed
 
mysql> CREATE TABLE records (rec VARCHAR(50));
Query OK, 0 rows affected (0.08 sec)
 
mysql> INSERT INTO records VALUE ("pxc1 record");
Query OK, 1 row affected (0.12 sec)
 
mysql> SELECT * FROM records;
+-------------+
| rec         |
+-------------+
| pxc1 record |
+-------------+
1 row in set (0.00 sec)

Теперь идем на второй сервер и проверяем работает ли репликация.

root@pxc2:~# mysql -uroot -p
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| firstDB            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

Как видим, БД среплицировалась. Теперь добавив еще одну запись в таблицу.

mysql> INSERT INTO records VALUE ("pxc2 record");
Query OK, 1 row affected (0.04 sec)
 
mysql> SELECT * FROM records;
+-------------+
| rec         |
+-------------+
| pxc1 record |
| pxc2 record |
+-------------+
2 rows in set (0.00 sec)

Первая запись среплицировалась и теперь у нас есть две записи. Проверяем последний сервер.

root@pxc3:~# mysql -u root -p
 
mysql> USE firstDB;
Database changed
 
mysql> SELECT * FROM records;
+-------------+
| rec         |
+-------------+
| pxc1 record |
| pxc2 record |
+-------------+
2 rows in set (0.00 sec)

Кластер работает как положено. Для тестов, еще можно выключить одну машину и через некоторое время включить обратно. В этом случаи в кластере будет только две машины, а после включения третей – все данные, которые были добавлены в период колапса будут среплицированы на поднявшуюся ноду.

Источник: http://sysadm.pp.ua/linux/mysql-install.html