呕心沥血博一文--MySQL主从异步复制
浏览数：30 /
时间：2015年06月12日
目录
1、概述
2、主从时间同步
3、主从服务器mysql部署
4、配置、启动从服务
5、测试
6、总结
1、概述
mysql内建的复制功能是mysql应用的“水平扩展”的架构，是mysql高性能运用的基础，为一个服务器配置一个或多个备库来进行数据的同步(准备的说不应该说是“同步”，因为主从服务器的数据根本不可能做同步)。在这样一个分布式的架构中各个服务器间时间应该同步才是，能为后期日志的统计分析提供更为准备的信息，这样才能准确反应出某个时间点我们系统的访问情况。
主从复制是一种异步复制的模型，说成“主从同步”是不精确的，应说是“主从复制”，先有主，再有从。因mysql主从复制原理的限定，在从库上只有一个sq线程(即使在mysql5.6中支持多线程，但也是针对库级别的)来重放中继日志中的事件，这种在主服务器能并发的查询请求在从服务器就成了串行化来执行，这是一个从服务器会慢于主服务器的一个原因。
对主从服务器在硬件选择上的建议：如果在规划主从时考虑到有可能会把从服务器提升为主服务器，那主从服务器硬件配置建议完全相同，应选择核多、主频高的CPU，频率高、总容量大的内存，多块高速的SAS硬盘(或SSD)及带有BBU的RAID卡，在RAID级别上建议选择raid10或raid50；如果从库只是用来作为主库的一个副本，以便于数据的备份，那从服务器的CPU没必要与主服务一个规格，但硬盘的IO这一块还是不要太差，IO高有助于事务的重放效率，在一定程度上能缩小与主库的时间差。
2、主从时间同步
主服务器配置成一ntpd服务器，自身与外网的ntpd服务进行时间校准，而又对从服务器提供时间校准服务。在生产环境中最好不要用ntpdate来强行调整服务器的时间，这样会对让服务器的时间产生空白区，注意，ntpdate工具是调整时间，而不是像ntpd工具一样是校准时间，两者的差别是挺大的。
主服务器基础环境：
[root@master ~]# cat /etc/issue CentOS release 6.4 (Final) Kernel \r on an \m [root@master ~]# uname -r 2.6.32-358.el6.x86_64 [root@master ~]# ifconfig | grep Bcast #从服务器的系统与主服务器相同，只是IP地址是192.168.0.202 inet addr:192.168.0.201 Bcast:192.168.0.255 Mask:255.255.255.0
安装配置主服务器成为ntpd时间服务器：
[root@master ~]# yum -y install ntp [root@master ~]# service ntpd start #启用服务后等个1-3分钟，就可用ntpq -p命令查看是否能与远程ntpd服务器连接 [root@master ~]# vi /etc/ntp.conf #配置ntp配置文件，使其成为从服务器的时间服务器 ……略…… # Hosts on local network are less restricted. #restrict 192.168.1.0 mask 255.255.255.0 nomodify notrap restrict 192.168.0.0 mask 255.255.255.0 nomodify notrap #复制上边一行并启用，把ip修改成本地地址段 #外部时间服务器不可用时，以本地时间作为时间服务 server 127.127.0.0 fudge 127.127.0.0 stratum 10 ……略…… [root@master ~]# service ntpd restart #重启服务 [root@master ~]# chkconfig --level 235 ntpd on
在从服务器上安装配置ntp：
[root@slave ~]# yum -y install ntp [root@slave ~]# vim /etc/ntp.comf #注释掉默认的时间服务器，加上本地时间服务器 ……略…… # Use public servers from the pool.ntp.org project. # Please consider joining the pool (http://www.pool.ntp.org/join.html). server 192.168.0.201 #server 0.centos.pool.ntp.org iburst #erver 1.centos.pool.ntp.org iburst #server 2.centos.pool.ntp.org iburst #server 3.centos.pool.ntp.org iburst ……略…… [root@slave ~]# service ntpd restart [root@slave ~]# chkconfig ntpd on [root@slave ~]# ntpq -p #监控时间校准的状态信息
测试时可以试着把从服务器的时间调慢几分钟，然后观察从服务器的时间是否会慢慢的赶上来。
3、主从服务器mysql部署
3.1、主服务器安装配置
[root@master software]# pwd /root/software [root@master software]# ls mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz [root@master software]# tar xf mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ [root@master software]# cd /usr/local [root@master local]# ln -sv mysql-5.6.24-linux-glibc2.5-x86_64 mysql [root@master local]# cd mysql [root@master mysql]# chown -R root.mysql ./* [root@master mysql]# mkdir /mnt/mydata/dbdata #创建数据目录 [root@master mysql]# chown -R mysql.mysql /mnt/mydata/dbdata [root@master mysql]# scripts/mysql_install_db --user=mysql --datadir=/mnt/mydata/dbdata/ #初始化数据库，如果没有mysql用户请自行创建
因mysql 5.6中的my.cnf文件太过简单，所以复制mysql 5.5中的配置文件再作一些修改：
[root@master mysql]# cp /opt/lamp/mysql55/support-files/my-large.cnf /etc/my.cnf 修改my.cnf配置文件 [root@master mysql]# vim /etc/my.cnf …… #thread_concurrency = 8 #注释此选项，在5.6中已被放弃 #指定数据目录 datadir = /mnt/mydata/dbdata #基于Innodb的表让表使用单独的表空间 innodb_file_per_table = 1 #启用慢查询日志 slow_query_log = 1 #如果查询时间长于“long_query_time”设定的值，那就把此查询查询到“slow_query_log_file”文件中 long_query_time = 1 slow_query_log_file = /var/log/mysql/master-slow.log #定义错误日志文件 log_error = /var/log/mysql/master.error …… #定义二进制日志文件 log-bin=/var/log/mysql/mysql-bin #每一次事件提交就使日志同步到磁盘，默认“sync_binlog=0”，表示让系统自己来flush使在内存的日志刷到磁盘，设置为“1”是一种安全的设置，因为当系统crash时你最多丢失一个事件，但相反会增加系统的IO，即使这样也建议设置成“1” sync_binlog = 1 …… #在5.6中已建议把二进制日志的格式修改成“row” binlog_format=row …… #设置服务器ID号 server-id = 100
[root@master mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld56 #复制启动脚本 [root@master mysql]# service mysqld56 start [root@master mysql]# ls /var/log/mysql master.error master-slow.log mysql-bin.000001 mysql-bin.index [root@master mysql]# vim /etc/profile.d/mysql56.sh #导出二进制文件路径 export PATH=/usr/local/mysql/bin:$PATH [root@master mysql]# source /etc/profile.d/mysql56.sh [root@master ~]# mysql #数据库连接测试 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.6.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql>
3.2、从服务器安装配置
从服务器的安装方法与主服务器一样，只是在my.cnf配置文件有些不同：
[root@slave mysql]# vim /etc/my.cnf …… #thread_concurrency = 2 datadir = /mnt/mydata/dbdata innodb_file_per_table = 1 log_error = /var/log/mysql/slave.error #relay_log relay_log = /var/log/mysql/mysql-relay-bin sync_relay_log = 1 #设置让sql thread读取中继日志中的语句并在本地运用时记录到本地的二进制日志，当然从服务器要打开“log-bin”启用二进制日志，如果此从服务器还可以作为其他从服务器的主服务器，应启用“log_slave_updates = 1” log_slave_updates = 1 #在从服务器的数据中会多出两个文件，一个是master.info和relay-log.info，master.info文件记录了当前从服务器请求主服务器的二进制文件名与偏移量等信息，而relay.info文件记录了从服务器当前所使用的中继日志文件名与偏移量等信息，如果主服务器比较繁忙，文件中记录的偏移量这样的信息是时刻在变化的，可惜的是这些数据不是时时写入磁盘的，是先写进内存，再同步到磁盘，这样可能会对数据带来不一致的问题。在新版本中引入了下边两个变量来控制此行为，表示当每一次事件后就让数据更新到磁盘，也就是更新master.info和relay-log.info两个文件，这样尽可能让数据及时刷新到磁盘，让数据持久化，当然相应增加了系统的IO，但为了数据的安全就是值得的。 sync_master_info = 1 sync_relay_log_info =1 #security #设置从库为只读模式，但具有supper权限的用户不受此限制 read_only = 1 #跳过当mysqld启用时自动启用slave线程 skip_slave_start …… log-bin=/var/log/mysql/mysql-bin sync_binlog = 1 …… server-id = 200
[root@slave mysql]# service mysqld56 start Starting MySQL.. SUCCESS! [root@slave mysql]# ls /var/log/mysql #中继日志还没有生成，因从服务器线程没有启动 mysql-bin.000001 mysql-bin.index slave.error [root@slave mysql]# ls /mnt/mydata/dbdata/ #master.info和relay-log.info两个文件没有生成，因为从服务线程没有启动 auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema slave.pid test [root@slave mysql]# mysql #测试 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql>
4、配置、启动从服务
上边已把主从服务器各自的my.cnf配置文件已设置完毕，要想启动mysql的主从复制功能，现在还差一个从从服务器到主服务器的连接帐户，再启用从服务器上的复制线程即可。
4.1、在主服务器上创建拥有复制权限的帐户
[root@master ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 191 Server version: 5.6.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> GRANT replication slave,replication client ON *.* TO ‘repuser‘@‘192.168.0.%‘ IDENTIFIED BY ‘111111‘; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> SHOW MASTER STATUS; #记录下当前主服务器所使用的二进制文件及position +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 430 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
4.2、从服务器连接主服务器、启动复制线程
[root@slave mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> CHANGE MASTER TO -> MASTER_HOST=‘192.168.0.201‘, -> MASTER_USER=‘repuser‘, -> MASTER_PASSWORD=‘111111‘, -> MASTER_LOG_FILE=‘mysql-bin.000001‘, -> MASTER_LOG_POS=0; #这里的偏移量我指定为“0”，零表示从开二进制日志的开头开始进行复制 mysql> SHOW SLAVE STATUS\G #查看slave的状态信息，IO thread和sql thread线程都还没有启动 *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.0.201 Master_User: repuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No …… mysql> START SLAVE; #启动复制线程 mysql> SHOW SLAVE STATUS\G #两个线程已启动 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.201 Master_User: repuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 430 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 593 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> SHOW GRANTS FOR ‘repuser‘@‘192.168.0.%‘; #这个复制所用的帐户已经从主服务器复制到了从服务器 +--------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for [email protected]% | +--------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘repuser‘@‘192.168.0.%‘ IDENTIFIED BY PASSWORD ‘*FD571203974BA9AFE270FE62151AE967ECA5E0AA‘ | +--------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
5、测试
在主服务器上创建、修改数据：
mysql> CREATE DATABASE mydb1; Query OK, 1 row affected (0.00 sec) mysql> USE mydb1; Database changed mysql> CREATE TABLE tb1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name CHAR(20) NOT NULL,age TINYINT UNSIGNED); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO tb1 (name,age) VALUES (‘tom‘,12),(‘jem‘,23); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
在从服务器上查看在主服务器的修改：
[root@slave mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> SELECT * FROM mydb1.tb1; +----+------+------+ | id | name | age | +----+------+------+ | 1 | tom | 12 | | 2 | jem | 23 | +----+------+------+ 2 rows in set (0.00 sec)
测试证明：在主服务器上的修改已复制到了从服务器上。
6、总结
mysql的主从复制已搭建完毕，但这种复制并不能替代数据库的备份。由于从服务器是单进程模型（mysql 5.6的多线程也只能是库级别的），所以在一个比较繁忙的mysql系统上，从服务器可能会落后主服务器，这是mysql主从复制架构原生带来的特性，并不能真正做到主从的同步，所以在后期的维护工作中我们要用到一些监控工具来及时发现从服务器是否真正落后主服务器。在博文的最开始已提到在这种分布式的架构中时间同步的重要性，在用“show slave status\G”查看从服务器状态时有一个变量“Seconds_Behind_Master”这个值就是反应从服务器落后主服务器的时间，详细一点就是说明从服务器的SQL thread处理中继日志中的事件时，会把主服务器上日志中的timestamp与从服务器运行sql thread时的时间进行相关运算，这个差值就是“Seconds_Behind_Master”的值，所以主从服务器间的同步显得格外的重要。
“Seconds_Behind_Master”的值为0也不代表主服务器与从服务器已经同步，只能说sql thread已把relay log中的事件执行完了，因为主服务器的bin log不会实时的同步到从服务器上，从服务器上的relay log总会落后于主服务器的bin log，所以“Seconds_Behind_Master”这个值不具有真正意义上的参考价值。要监控主从是否一致，可以用percona-tools工具集中的pt-heartbeat工具。
本文出自 “知识需要总结与记录” 博客，请务必保留此出处http://zhaochj.blog.51cto.com/368705/1635982
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
