MySQL主从复制服务器(replication)创建笔记

前段时间因为客户购买云服务到期以及更换服务器的原因，负责了项目生产MySQL数据库的迁移工作。与此同时也为其建立了复制服务器。MySQL主从复制能够实现主服务器(master)向从服务器(slave)的实时同步。主服务器的更新变动事件“event”(二进制日志)，通过主从服务器间的实时通信，传输到从服务器(中继日志)。从服务器通过两个线程I/O Thread和SQL Thread来完成复制工作。

建立从复制服务器有很多好处，这里借用一些网络流行语总结一下，即便这些词汇的原始网络意义比较邪恶。在实际使用中，如果合理利用，会发现slave真的可以像“奴隶”一样供你压榨。

①备胎：master宕了slave可以补上。

②千斤顶：slave可以充当部分读服务器的角色以减轻master的读写压力。

③打气筒：slave是master数据的实时快照并且可以代替master执行dump计划。

④接盘侠：slave可以代替master供第三方外部访问以降低潜在风险。

建立主从复制的具体操作过程其实并不算复杂，我是参考《MySQL技术内幕》(https://book.douban.com/subject/26436525/)这本“葵花宝典”来做的。该书的作者Paul DuBois就是MySQL官方文档的编写者之一。具体过程参考如下。

①准备好用于工作的MySQL数据库

MySQL主、从服务器尽量安装使用当下最新版本。如果master和slave的MySQL版本不一致，replication支持从旧版本的master向新版本的slave进行复制，反向操作就不行了。

②写入同一版本的备份文件

Master和slave服务器写入同一个数据快照，一般是mysqldump文件。如果没有或不需要这步就可以跳过了。

③修改MySQL配置文件

MySQL的系统变量成千上万，不过很多都是不用额外修改的，就主从复制而言，首先是必改项：master启用二进制日志功能和为master以及slave设置服务器id(二者的id不能相同)。这些修改添加到配置文件[mysqld]选项组下。

Master：

[mysqld]

log-bin = /自定义目录/bin-log

server-id = （自定义1 ~ 2的32次幂 - 1的一个整数互不重复即可）

Slave：

[mysqld]

server-id = （自定义1 ~ 2的32次幂 - 1的一个整数互不重复即可）

还有一些自定义的配置修改，这里列出一个常用的。更多详情当然是参考官方文档了(http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html)。例如，并不是所有的库表都是想要或需要开启主从复制的(MySQL系统库表)，这可以在配置文件中指定，以下两种形式皆可，该操作既可配置master(master写入二进制日志忽略)又可配置slave(slave读取中继日志忽略)。

binlog-ignore-db = mysql

replicate_wild_ignore_table = information_schema.%

④重启MySQL

配置文件修改后需要重启MySQL，所以最好一次性添加完所有配置选项，然后重启master和slave。

⑤创建slave账户并赋权

Slave与master保持实时通信需要通过一个有replication权限的账户从master读取event。MySQL重启后，接下来master就需要来为slave创建这个账户。

CREATE USER 'slave_user'@'%' IDENTIFIED BY 'slave_password';GRANT REPLICATION SLAVE, SELECT ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';

⑥获取master初始复制点

Master库执行“SHOW MASTER STATUS;”，记录输出的“File”和“Position”值，稍后开启slave的时候需要传入，即复制任务是以此开始的。

mysql> SHOW MASTER STATUS\G

*************************** 1. row ***************************

              File: bin-log.000002

          Position: 4891

⑦开启slave配置

Slave重启后，配置连接master。执行SQL如下：

    CHANGE MASTER TO    MASTER_HOST = 'master_host',          -- 主机/库host    MASTER_PORT = port,                   -- 主机/库port    MASTER_USER = 'slave_user',           -- Step⑤创建    MASTER_PASSWORD = 'slave_password',    -- Step⑤创建    MASTER_LOG_FILE = 'file',              -- Step⑥输出    MASTER_LOG_POS  = position;           -- Step⑥输出

⑧开启主从复制任务

Slave库执行开启任务SQL：

START SLAVE;

到此，如果顺利的话replication已经顺利开启了，并且在MySQL的进程列表(processlist)里也会看到跟主从复制相关的记录。在主从复制运行过程中，有一些常用的命令语句帮助我们进行维护，这里简单列举几个常用的。

①SHOW MASTER STATUS;

建立主从复制的过程中已经提到过，用以显示master状态。

②SHOW SLAVE STATUS;

显示slave状态，其实这个应该是最常用了。

③STOP SLAVE;

STOP SLAVE IO_THREAD;

STOP SLAVE SQL_THREAD;

START SLAVE;

START SLAVE IO_THREAD;

START SLAVE SQL_THREAD;

关闭/开启全部或者指定线程复制工作。

④PURGE BINARY LOGS

清理master MySQL server的过期bin log。例如：

PURGE BINARY LOGS TO 'mysql-bin.010';              -- 清理到指定文件日志

PURGE BINARY LOGS BEFORE '2016-09-19 22:44:23';    -- 清理到指定时间日志

例附个人创建replication过程记录：

/*my.cnf:  master*/[mysqld]server-id = 11log-bin = /mnt/mysql/data/bin-logbinlog-ignore-db = information_schemabinlog-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = test/*my.cnf:  slave*/[mysqld]server-id = 1111slave_load_tmpdir=/mnt/mysql/dataslave_skip_errors = 1146/*cfg_sql:  master*/CREATE USER 'bridge'@'%' IDENTIFIED BY '_bridge';GRANT REPLICATION SLAVE, SELECT ON *.* TO 'bridge'@'%' IDENTIFIED BY '_bridge';FLUSH TABLES;SHOW MASTER STATUS;/*cfg_sql:  slave*/CHANGE MASTER TO   MASTER_HOST = '11...11',   MASTER_PORT = 3306,   MASTER_USER = 'bridge',   MASTER_PASSWORD = '_bridge',   MASTER_LOG_FILE = 'bin-log.000002',   MASTER_LOG_POS  = 4891;    START SLAVE;