Репликация MySQL Master-Slave на одной машине

Репликация MySQL — это процесс, позволяющий автоматически копировать данные с одного сервера базы данных MySQL (главного) на один или несколько серверов базы данных MySQL (подчиненные). Обычно он используется для распространения доступа для чтения на несколько серверов для масштабируемости, хотя его также можно использовать для других целей, таких как аварийное переключение или анализ данных на подчиненном устройстве, чтобы не перегружать ведущий.

Поскольку репликация master-slave является односторонней репликацией (от master к slave), для операций записи используется только главная база данных, а операции чтения могут быть распределены по нескольким подчиненным базам данных. Это означает, что если в качестве масштабируемого решения используется репликация ведущий-ведомый, вам необходимо определить как минимум два источника данных: один для операций записи, а второй — для операций чтения.

Разработчики MySQL обычно работают только на одной машине и, как правило, имеют всю свою среду разработки на этой одной машине, исходя из логики, что они не зависят от сети или подключения к Интернету. Если требуется репликация master-slave, потому что, например, им нужно протестировать репликацию в среде разработки перед развертыванием изменений в другом месте, они должны создать ее на той же машине. Хотя настройка одного экземпляра MySQL довольно проста, нам нужно приложить дополнительные усилия для настройки второго, а затем репликации master-slave.

Для этого пошагового руководства я выбрал Ubuntu Linux в качестве основной операционной системы, и предоставленные команды предназначены для этой операционной системы. Если вы хотите настроить репликацию master-slave MySQL в какой-либо другой операционной системе, вам нужно будет внести изменения для ее конкретных команд. Однако общие принципы настройки репликации master-slave MySQL на одной и той же машине одинаковы для всех операционных систем.

Установка первого экземпляра MySQL

Если на вашем компьютере уже установлен один экземпляр базы данных MySQL, вы можете пропустить этот шаг.

Самый простой способ установить MySQL на Ubuntu — запустить следующую команду из командной строки терминала:

 sudo apt-get install mysql-server

В процессе установки вам будет предложено установить пароль для пользователя root MySQL.

Настройка mysqld_multi

Чтобы эффективно управлять двумя экземплярами MySQL на одном компьютере, нам нужно использовать mysqld_multi .

Первым шагом в настройке mysqld_multi является создание двух отдельных групп [mysqld] в существующем файле my.cnf . Расположение файла my.cnf по умолчанию в Ubuntu — /etc/mysql/ . Итак, откройте файл my.cnf в своем любимом текстовом редакторе и переименуйте существующую группу [mysqld] в [mysqld1] . Эта переименованная группа будет использоваться для настройки первого экземпляра MySQL, а также будет настроена как главный экземпляр. Так как в репликации master-slave MySQL каждый экземпляр должен иметь свой уникальный server-id , добавьте следующую строку в группу [mysqld1] :

 server-id = 1

Поскольку нам нужна отдельная группа [mysqld] для второго экземпляра MySQL, скопируйте группу [mysqld1] со всеми текущими конфигурациями и вставьте ее ниже в тот же файл my.cnf . Теперь переименуйте скопированную группу в [mysqld2] и внесите следующие изменения в конфигурацию подчиненного устройства:

 server-id = 2 port = 3307 socket = /var/run/mysqld/mysqld_slave.sock pid-file = /var/run/mysqld/mysqld_slave.pid datadir = /var/lib/mysql_slave log_error = /var/log/mysql_slave/error_slave.log relay-log = /var/log/mysql_slave/relay-bin relay-log-index = /var/log/mysql_slave/relay-bin.index master-info-file = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only = 1

Чтобы настроить второй экземпляр MySQL в качестве ведомого, установите server-id равным 2, так как он должен отличаться от идентификатора сервера ведущего.

Поскольку оба экземпляра будут работать на одном компьютере, установите port для второго экземпляра на 3307 , так как он должен отличаться от порта, используемого для первого экземпляра, который по умолчанию равен 3306 .

Чтобы этот второй экземпляр мог использовать одни и те же двоичные файлы MySQL, нам нужно установить разные значения для socket , pid-file , datadir и log_error .

Также нам необходимо включить relay-log , чтобы использовать второй инстанс в качестве слейва (параметры relay-log , relay-log-index и relay-log-info-file ), а также установить master-info-file .

Наконец, чтобы сделать подчиненный экземпляр доступным только для чтения, параметр read_only устанавливается равным 1 . Вы должны быть осторожны с этой опцией, так как она не полностью предотвращает изменения на ведомом устройстве. Даже если для read_only установлено значение 1 , обновления будут разрешены только пользователям с привилегией SUPER . MySQL недавно представил новый параметр super_read_only , чтобы предотвратить внесение изменений пользователями SUPER . Эта опция доступна с версии 5.7.8.

Помимо [mysqld1] и [mysqld2] , нам также нужно добавить новую группу [mysqld_multi] в файл my.cnf :

 [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass

Как только мы установим второй экземпляр MySQL и запустим оба, мы предоставим соответствующие привилегии пользователю multi_admin , чтобы иметь возможность закрывать экземпляры MySQL.

Создайте новые папки для второго экземпляра MySQL

На предыдущем шаге мы подготовили файл конфигурации для второго экземпляра MySQL. В этом файле конфигурации используются две новые папки. Следующие команды Linux должны использоваться для создания этих папок с соответствующими привилегиями:

 mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave

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

В некоторых средах Linux параметры безопасности AppArmor необходимы для запуска второго экземпляра MySQL. По крайней мере, они требуются в Ubuntu.

Чтобы правильно настроить AppArmor, отредактируйте файл /etc/apparmor.d/usr.sbin.mysqld в своем любимом текстовом редакторе, добавив следующие строки:

 /var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock w, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,

После сохранения файла перезагрузите компьютер, чтобы эти изменения вступили в силу.

Установка второго экземпляра MySQL

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

Поскольку мы уже подготовили файл конфигурации и необходимые папки и изменения безопасности на предыдущих шагах, последним шагом установки второго экземпляра MySQL является инициализация каталога данных MySQL.

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

 mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

После инициализации каталога данных MySQL вы можете запустить оба экземпляра MySQL с помощью службы mysqld_multi :

 mysqld_multi start

Установите пароль root для второго экземпляра MySQL, используя mysqladmin с соответствующим хостом и портом. Имейте в виду, что если хост и порт не указаны, mysqladmin по умолчанию подключится к первому экземпляру MySQL:

 mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

В приведенном выше примере я установил пароль «rootpwd», но рекомендуется использовать более надежный пароль.

Дополнительная настройка mysqld_multi

В конце раздела «Настройка mysqld_multi» я написал, что позже мы дадим соответствующие привилегии пользователю multi_admin , так что сейчас самое время для этого. Нам нужно предоставить этому пользователю соответствующие привилегии в обоих экземплярах, поэтому давайте сначала подключимся к первому экземпляру:

 mysql --host=127.0.0.1 --port=3306 -uroot -p

После входа в систему выполните следующие две команды:

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Выйдите из клиента MySQL и подключитесь ко второму экземпляру:

 mysql --host=127.0.0.1 --port=3307 -uroot -p

После входа в систему выполните те же две команды, что и выше:

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Выйдите из клиента MySQL.

Запускайте оба экземпляра MySQL автоматически при загрузке

Последним шагом настройки mysqld_multi является установка скрипта автоматической загрузки в файле init.d

Для этого создайте новый файл с именем mysqld_multi в /etc/init.d и предоставьте ему соответствующие привилегии:

 cd /etc/init.d touch mysqld_multi chmod +x /etc/init.d/mysqld_multi

Откройте этот новый файл в своем любимом текстовом редакторе и скопируйте следующий скрипт:

 #!/bin/sh ### BEGIN INIT INFO # Provides: scriptname # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO bindir=/usr/bin if test -x $bindir/mysqld_multi then mysqld_multi="$bindir/mysqld_multi"; else echo "Can't execute $bindir/mysqld_multi"; exit; fi case "$1" in 'start' ) "$mysqld_multi" start $2 ;; 'stop' ) "$mysqld_multi" stop $2 ;; 'report' ) "$mysqld_multi" report $2 ;; 'restart' ) "$mysqld_multi" stop $2 "$mysqld_multi" start $2 ;; *) echo "Usage: $0 {start|stop|report|restart}" >&2 ;; esac

Добавьте службу mysqld_multi на runlevels по умолчанию с помощью следующей команды:

 update-rc.d mysqld_multi defaults

Перезагрузите компьютер и убедитесь, что оба экземпляра MySQL работают, используя следующую команду:

 mysqld_multi report

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

Теперь, когда у нас есть два экземпляра MySQL, работающих на одной машине, мы настроим первый экземпляр как ведущий, а второй — как подчиненный.

Часть настройки уже была выполнена в главе «Настройка mysqld_multi». Единственное оставшееся изменение в файле my.cnf — настройка ведения двоичного журнала на мастере. Для этого отредактируйте файл my.cnf со следующими изменениями и дополнениями в группе [mysqld1] :

 log_bin = /var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog-format = ROW

Перезапустите основной экземпляр MySQL, чтобы эти изменения вступили в силу:

 mysqld_multi stop 1 mysqld_multi start 1

Чтобы ведомое устройство могло подключиться к ведущему с правильными привилегиями репликации, на ведущем должен быть создан новый пользователь. Подключитесь к главному экземпляру с помощью клиента MySQL с соответствующим хостом и портом:

 mysql -uroot -p --host=127.0.0.1 --port=3306

Создайте нового пользователя для репликации:

 mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Выйдите из клиента MySQL.

Выполните следующую команду, чтобы создать дамп основных данных:

 mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

Здесь мы используем параметр --master-data=2 , чтобы иметь комментарий, содержащий оператор CHANGE MASTER внутри файла резервной копии. Этот комментарий указывает координаты репликации во время резервного копирования, и нам понадобятся эти координаты позже для обновления основной информации в подчиненном экземпляре. Вот пример этого комментария:

 -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=349;

Импортируйте дамп, созданный на предыдущем шаге, в подчиненный экземпляр:

 mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

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

Подключитесь к ведомому экземпляру с помощью клиента MySQL с соответствующим хостом и портом:

 mysql -uroot -p --host=127.0.0.1 --port=3307

Выполните следующую команду, чтобы обновить основную информацию (возьмите координаты репликации из файла дампа replicationdump.sql , как описано выше):

 mysql> CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_USER='replication', -> MASTER_PASSWORD='replication', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=349;

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

 mysql> START SLAVE;

Выполните следующую команду, чтобы убедиться, что репликация запущена и работает:

 mysql> SHOW SLAVE STATUS \G

Поздравляем. Теперь ваша репликация master-slave MySQL на том же компьютере успешно настроена.

Заворачивать

Настроенная репликация master-slave в вашей среде разработки полезна, если она вам нужна для масштабируемого решения в производственной среде. Таким образом, у вас также будут отдельные источники данных, настроенные для операций записи и чтения, чтобы вы могли локально проверить, что все работает должным образом, перед дальнейшим развертыванием.

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