前段时间因为客户购买云服务到期以及更换服务器的原因,负责了项目生产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 = 11 log-bin = /mnt/mysql/data/bin-log binlog-ignore-db = information_schema binlog-ignore-db = mysql binlog-ignore-db = performance_schema binlog-ignore-db = test /*my.cnf: slave*/ [mysqld] server-id = 1111 slave_load_tmpdir=/mnt/mysql/data slave_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;
如果是要作为一个高可用MySQL架构的管理员,需要和主从复制(replication)经常打交道,可以读读另一本详细介绍这方面内容的参考书《高可用MySQL》(https://book.douban.com/subject/26630834/)。