Репликация mysql (master-slave)
Настройка репликации Mysql
master
my.cnf:
server-id = 1 log_bin = mysql-bin-log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = <dbname> binlog-format = ROW
Права на репликацию для слейва:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'PASSWORD';
Лочим базу, запоминаем Position:
mysql> use <dbname>; mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +----------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------------+----------+--------------+------------------+ | mysql-bin-log.000002 | 12440102 | <dbname> | | +----------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Делаем дамп
mysqldump <dbname> > <dbname>.sql
Снимаем лок с базы:
mysql> UNLOCK TABLES;
Копируем на слейв:
scp ./<dbname>.sql cosmonaut@192.168.59.11:/tmp/
slave
my.cnf:
server-id = 2 #log_bin = /var/log/mysql/mysql-bin.log log_bin = mysql-bin-log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = nsk #binlog_ignore_db = include_database_name binlog-format = ROW # replication slave relay-log = mysql-relay-bin
Заливаем дамп
cat <dbname>.sql | mysql <dbname>
Включаем репликацию:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.59.11', MASTER_USER='replication', MASTER_PASSWORD='PASSWORD',MASTER_LOG_FILE = 'mysql-bin-log.000052', MASTER_LOG_POS = 98270989; mysql> START SLAVE; mysql> SHOW SLAVE STATUS \G
Готово!