Mysql Replication 设定

Mysql Replication 是一个资料同步的功能，可以让你将 databases 的资料同步至另一台 MySql Server ，用来作分流，或是备份，Replication 非常实用，就算网站只是一个小小的 Blog 最好也要设定 Replication，因为我们无法预知机器什么时候会出现问题，当有一天机器的硬碟突然挂点，你就只能从前一天的备份将资料救回来，像一般系统每天最多只会备份一次，那代表你最多有可能 24 小时内的资料是救不回来的，而如果你有设定好 Replication ，那么最多只有几分钟的资料救不回来 ，甚至资料可以百分之百的还原。

名词解释

• Master : 是指提供写入的 server，Replication 系统中只能有一台机器可以新增，修改资料，而这台机器，我们称之为 Master 。
• Slave : 是指 read only 的 server ，只用来提供 user 读取资料，本身不提供资料写入。

一个大型系统，通常会需要多台 Database 来应付线上的流量，这时你就会需要设定 Replication ，将 Database 的资料同步到多台 MySql slave server ，让每一台 slave 都拥有相同的资料，上图中，可以看到当 User 要写入一笔新的资料时，系统会由 Master 负责写入，然后再透过 Replication 机制，将新资料转给 Slave DB ，而其它的 User 就可以从 Slave DB 中读取到新的资料， Slave DB 可以拥有 1 ~ N 台机器，可以有效的分配流量。

设定 Master 机器

• http://dev.mysql.com/doc/refman/5.0/en/replication-howto-masterbaseconfig.html

请打开 my.cnf 设定档，并设定 log-bin, server-id, binlog_do_db 这三个值。

当 Master 的资料被修改后，Mysql 就会写一份 log 到 mysql-bin.0001 这个档案，这个档案非常重要，所有的 replication 行为都要靠这个档案来完成，而我们可以透过设定 log-bin ，来改变其档名。

server-id 是一个代号，每一台 Mysql 都有一个独立的 ID ，这个 ID 必需手动设定，而且一定要确保每一台 Mysql 的 ID 都是不同的。

binlog_do_db 这个设定是指，你要同步的 database 名称，如果你有多个 database ，那么可以写成两行。

[mysqld]
log-bin = mysql-bin
server-id = 1
binlog_do_db = test1
binlog_do_db = test2

修改完成后要重启 Mysql 。

增加新帐号给 slave 连接

CREATE USER 'repl'@'%' IDENTIFIED BY 'password???';
 
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

将 master 资料搬到 slave

在系统自动同步功能设定完成前，我们得先手动将资料搬到 slave，也就是说第一次的同步，必需手动来完成。

首先用 mysqldump 的指令将资料库的资料存入 all.sql

mysqldump -uroot --all-databases -p >~/all.sql

我在使用 Mysql Dump 时，有发生 general_log is not exist 与 slow_log is not exist 的问题，解决方式就是手动去建立这两个 table。

use mysql;
 
CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
 
CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='just only a slow log';

再来你要找出目前 Master Mysql 资料的情况，在 Master 使用指令 show master status

show master status

请记下 File 与 Position 的值，分别是 mysql-bin.000028 与 24473

Slave 机器设定

你得先修改 slave 机器中的 my.cnf 设定档，设定一个与 master 不同值的 server-id ，这里我就设定成 2 ，另外我加了一个 read_only = 1 ，因为 slave 的机器，只需要同步即可，不需要被其他 process 写入资料，修改完成后要重启 Mysql 。

[mysqld]
server-id=2
relay-log=mysqld-relay-bin
read_only=1
binlog_do_db = test1
binlog_do_db = test2

还记得在 Master 机器 dump 下来的 all.sql 吗，现在你要在 slave 的机器中，将 all.sql 的资料汇入 mysql ，你可以用下列的指令。

mysql -h localhost -u root --default-character-set=utf8 -p < all.sql

最后你要在 slave 的 mysql 中，设定 Master Mysql 的帐号，密码与 hostname，这一步完成后， Slave Mysql 就会开始同步罗 ，你可以查看 relay-log.info 这个 log 是否有更新，里面会记录 replication 的进度。

# 先停掉  replication
stop slave;
 
# 设定下一次 replication 的起始位置
CHANGE MASTER TO
    MASTER_HOST='master.host.com',
    MASTER_USER='repl',
    MASTER_PASSWORD='passowrd',
    MASTER_LOG_FILE='mysql-bin.000028',
    MASTER_LOG_POS=24473,
    MASTER_PORT=3306;
 
#再启用 slave
start slave;

这些的设定也可以写在 my.cnf ，但是密码就变成明码存在 my.cnf 了

[mysqld]
server-id=2
master-host=master.com
master-port=3306
master-user=root
master-password=xxxxxx
master-connect-retry=60

启动 Replication 后就可以去检查 Mysql 的 log，看看 slave 机器是否有正确的更新，我的 Mysql slave 会将 sync 过来的资讯写在档案 "/var/log/mariadb/mariadb.log" ，如果 Replication 过程中有碰到错误 ，你会在 log 中看到类似下面这种错误 。

160403 17:06:17 [Warning] Slave: Operation CREATE USER failed for 'xx'@'zz' Error_code: 1396
160403 17:06:17 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000017' position 35164

这个错误发生的原因是，我在 Slave 设定完下次开始 sync 的 log position 之后，我又到 master 机器新增一个 user ，而这个 user 刚好在 slave 已经存在，造成 slave 无法执行这一个 SQL Statement ，解决方式为跳过这一个 SQL 语法，必须先登入 slave MySQL Server ，输入下面这三句 SQL 指令。

stop slave;
SET GLOBAL sql_slave_skip_counter = 1;
start slave;

参考资料

• http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
• https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql