MySQL 5.6 replicate原理与实践

MySQL作为常用生产数据库中的一种，容灾及高可用方面设计是必须考虑的。而MySQL replicate是容灾、负载均衡、读写分离、高可用性等技术的基础，不仅使用广泛且易于扩展，是一项成熟的技术。成功的商业案例如：replicate+MHA;replicate+corbar;replicate+MMM等。 ## 1.MySQL replicate复制常见用途： * 数据容灾：复制是备份的扩展方案，但不能直接取代备份； * 负载均衡：通过复制可以将读操作分布到多个服务器上； * 高可用性：能避免单点故障，一个包含复制的良好设计系统能够显著缩短宕机时间； * MySQL升级：复制也是作为常用升级测试的解决方案。 ## 2.MySQL replicate复制原理：  * 在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句执行的顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。 * 备库将主库的二进制日志复制到本地中继日志中。备库会启动一个IO线程，IO线程跟主库建立一个普通的客户端连接，然后再主库上启动一个特殊的二进制（binlog dump）线程，这个二进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，知道主库发送信号通知其有新的事件产生时才会被唤醒，备库IO线程会将接受到的事件记录到中继日志中。 * 备库启动SQL线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上IO线程时，中继日志通常已经在系统缓存中，所以中继日志开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中。 ## 3.MySQL replicate实现方式 * 环境 主库IP:192.168.1.75 从库IP:192.168.1.76 ### 3.1 追加主库my.cnf配置文件 ``` [mysqld] # 要体现出server的唯一性 server-id = 75 # 启用二进制日志 log_bin = /var/log/mysql/mysql-bin.log # 磁盘刷新指令.1表示每次写入时都将binlog与硬盘同步,影响效率 sync_binlog=1 # binlog日志格式，mysql默认采用statement，建议使用mixed binlog_format=mixed ``` 修改完成之后，重新启动主数据库 `service mysql restart` ### 3.2 在主库创建同步账号 ``` GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.76' IDENTIFIED BY 'repl'; flush privileges; ``` >注意：IP是从库所在的IP地址 ## 3.2 修改从库my.cnf配置文件 ``` [mysqld] # 服务器唯一ID，默认是1，一般取IP最后一段 server-id=76 # 启用二进制日志 log-bin=/var/log/mysql/mysql-bin.log ``` 修改完成之后，重新启动从数据库 ### 3.3 主库锁表备份 * 锁表 ``` #mysql -uroot -p mysql>FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ``` 二进制日志文件是mysql-bin.000002，位置是120，记录下这两个值，稍后要用到。 * 备份主库数据 ``` # mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases --triggers --routines --events > ./all.sql ``` * 解锁主库锁表 ``` mysql>UNLOCK TABLES; ``` ### 3.4 从库导入快照 * 导入主库备份的数据 ``` mysql -uroot -p -h127.0.0.1 -P3306 < ./all.sql ``` * 开启从库同步 ``` #mysql -uroot -p mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.75',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=120; #然后启动从数据库的复制线程： mysql>START slave; # 接着查询数据库的slave状态： mysql> SHOW slave STATUS #如果下面两个参数都是Yes(不能是Running)，则说明主从配置成功！ Slave_IO_Running:Yes Slave_SQL_Running:Yes ``` > 注意：MASTER_LOG_FILE,MASTER_LOG_POS修改成主库3.3中的查询结果 ## 参考 http://www.cnblogs.com/kezf/p/mysql-slave.html http://blog.csdn.net/stuartjing/article/details/9719701