Репликация MySQL чтобы не забыть.

Настраивать репликацию на серверах MySQL приходится настраивать не часто поэтому решил написать эту напоминалку.

Настройка главного сервера.

Нам необходимо внести изменения в /etc/mysql/my.cnf

Обязательно укажем уникальный ID сервера, путь для бинарных логов и имя БД для репликации в секции [mysqld]:

server-id = 1

log-bin = /var/lib/mysql/mysql-bin

replicate-do-db = mydb

Проверьте достаточно ли места для хранения бинарных логов сервера.

Добавим пользователя "replicator" для репликации. И разрешим репликацию только с хоста вторичного сервера базы данных.

 mysql> GRANT replication slave ON "mydb".* TO "replicator"@"172.16.1.11" \

IDENTIFIED BY "password";

 mysql> flush privileges;

Перезапустим сервер:

 $sudo service mysql restart

Если все прошло успешно проверим статус масстера:

mysql@master> SHOW MASTER STATUS\G
File: mysql-bin.000001
Position: 236733
Binlog_Do_DB:
Binlog_Ignore_DB:

Значение Position меняется при внесении изменений на базе данных

Настройка подчиненного сервера

ID подчиненного сервера должно быть больше чем у мастер сервера. Внесем изменения в конфигурацию вторичного сервера  и перезапустим его:

server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
replicate-do-db = mydb

 $sudo service mysql restart

Перенос данных.

Это достаточно отвественный момент. Есть два способа переноса данных.

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

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

$sudo mysqldump -u root -p --lock-all-tables mydb > mydb_dump.sql

Разблокируем таблицы

mysql> SET GLOBAL read_only = OFF;

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

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

$sudo tar -czf /var/backups/mydb.tar.gz /var/lib/mysql/mydb


Теперь остановим оба сервире баз данных.


$mysqladmin -h replica.mydomain.com -uroot -p shutdown
$mysqladmin -h localhost -uroot -p shutdown

Скопируем сохраненную базу данных на подчиненный сервер и развернем его там

$ scp mydb.tar.gz(mydb_dump.sql) 172.16.1.11:/home/dbmaster
$ ssh 172.16.1.11


dbmaster@replica$ cp mydb.tar.gz /var/lib/mysql
dbmaster@replica$ sudo service mysql start
dbmaster@replica$ mysql -uroot -p -e 'create database mydb'
dbmaster@replica$ sudo service mysql stop
dbmaster@replica$ tar -xzf mydb.tar.gz
dbmaster@replica$ sudo service mysql start



или восстанавливаем из дампа


dbmaster@replica$ sudo service mysql start
dbmaster@replica$ mysql -uroot -p -e 'create database mydb'
dbmaster@replica$ mysql -uroot -p mydb < mydb_dump.sql

Запустим базу данных на основном сервере 


$ sudo service mysql start


Теперь запустим репликацию 
$mysql -h 172.16.1.11 -uroot -p
mysql >CHANGE MASTER TO MASTER_HOST = "172.16.1.11", MASTER_USER = "replicator",\
 MASTER_PASSWORD = "password", MASTER_LOG_FILE = "mysql-bin.000001", \
MASTER_LOG_POS = 236733;
mysql> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.11
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb,testdb
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: 98
Relay_Log_Space: 235
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: 5


Slave_IO_Running: Yes, Slave_SQL_Running: Yes - говорит о том что репликация работает.  

Seconds_Behind_Master: 5 говорит о том на сколько секунд slave база данных отстает от матера в идеале должно быть 0.

!!! Бинарные файлы логов могут переполнить раздел. Если такое произошло делаем следующее
mysql>show binary logs;
mysql>purge binary logs before '2014-10-1 00:00:01' 

Или запускаем через crond например раз в сутки следующий скрипт.

Он оставляет бинарные логи за только предыдущие сутки. 

#!/usr/bin/perl

#

#Script for purge Mysql binagy log

#Starting every day

#

my $date=`/bin/date "+%Y-%d-%m"`;

chomp($date);

my ($y,$d,$m)=split "-", $date;

$d--;

$date= $y."-".$m."-".$d." 00:00:01";

my $binary_logs=`/usr/bin/mysql -h 172.16.11.4 -uroot -pcacti -e "show binary logs \\G"|grep mysql-bin| wc -l`;

print "$date $binary_logs\n";

if ($binary_logs > 5) {

   my  $purge_bin_log=`/usr/bin/mysql -h 172.16.11.4 -uroot -pcacti -e "purge binary logs before \'$date\'"`;

    print "Purging binary logs $purge_bin_log";

    }