预计阅读本页时间：-
7.4.6 案例分享六：生产环境下的MySQL数据库主从同步
MySQL的主从Replication同步（又叫主从复制）是一个很成熟的架构（如图7-9所示），笔者负责的许多电商平台的线上环境采用的都是这种方案，它的优点为：
·在业务繁忙的阶段，可以在从服务器上执行查询工作（即我们常说的读写分离），降低主服务器的压力。
广告：个人专属 VPN，独立 IP，无限流量，多机房切换，还可以屏蔽广告和恶意软件，每月最低仅 5 美元
·在从服务器上进行备份，避免备份期间影响主服务器服务。
·当主服务器出现问题时，可以迅速切换到从服务器，这样就不会影响线上环境了。
·数据分布。由于MySQL复制并不需要很大的带宽，所以可以在不同的数据中心实现跨机房数据的复制（阿里巴巴这种级别的公司业务另当别论）。比如笔者目前的数据中心的adserver业务数据库，就是一主四从。
主从复制是MySQL数据库提供的一种高可用、高性能的解决方案，其实现并不复杂，它不是完全实时的，而是异步实时的，如果网络延迟比较严重，则要考虑将其延迟时间作为Nagios报警的选项参数，其具体工作步骤为：
1）主服务器把数据更新记录到二进制日志中。
2）从服务器把主服务器的二进制日志复制到自己的中继日志中，这个由从服务器的I/O线程负责。
3）从服务器执行中继日志，把其更新应用到自己的数据库上，这个由从服务器的SQL线程负责。
图7-9 MySQL数据库主从同步的工作流程
鉴于生产环境下对MySQL有更严谨的要求，推荐采用源码编译的方法安装MySQL数据库。以下内容笔者曾在个人博客中阐述过，并且收到了许多热心网友的中肯意见，笔者据此进行了5次修改。如果大家在工作中有相应的需求，可以参考下。
编译安装MySQL数据库之前建议在服务器上安装基础库文件，命令如下：
yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel cmake
由于服务器采用的是最小化安装，因此建议也安装一下开发工具和开发库，以防止源码编译安装MySQL时报错，另外，MySQL从5.5版本开始，通过./configure进行编译配置的方式已经被取消，取而代之的是cmake工具，因此，我们首先要在系统中源码编译安装cmake工具。
MySQL数据库涉及的文件及对应的目录如下。
MySQL的安装位置：/usr/local/mysql
MySQL的配置文件：/etc/my.cnf
MySQL数据库的位置：/data/mysql/
下面介绍一下工作环境。
主数据库IP：192.168.1.205
从数据库IP：192.168.1.204
系统：CentOS 6.4 x86-64
内核版本：2.6.32-358.el6.x86_64
MySQL版本：5.5.40
服务器硬件环境：DELL PowerEdge R410或PowerEdge R710（6块300G SAS300G做成RAID 10）
首先，在MySQL官方网站（http://downloads.mysql.com/archives/community/ ）上下载MySQL 5.5.40的源码包，并分别在两台机器上进行安装。
生成运行MySQL服务的用户及用户组的命令如下：
groupadd mysql
useradd -g mysql mysql
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
源码编译安装MySQL 5.5.40的命令如下：
cd /usr/local/src/
tar xvf mysql-5.5.40.tar.gz
cd mysql-5.5.40
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_unicode_ci -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_DEBUG=0
make && make install && cd ../
然后，对MySQL进行权限配置，将MySQL的数据安装路径设为/data/mysql，并配置MySQL为服务启动状态，命令如下：
cd /usr/local/src/mysql-5.5.40
cp ./support-files/my-huge.cnf /etc/my.cnf
cp ./support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chown -R mysql:mysql /usr/local/mysql
这里暂时只用系统自带的“my-huge.cnf”作为MySQL数据库的配置文件，后期将根据MySQL的status运行状态来进行调优整理。
第三步是修改这两台服务器的MySQL服务器下的/etc/my.cnf的[mysqld]项，在其下面添加MySQL运行时的数据存放路径，命令如下所示：
datadir=/data/mysql
第四步是在这两台服务器上分别运行如下命令初始化数据库，在/data/mysql下生成MySQL的初始化文件和初始库等。
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
此时就可以顺利地以服务的形式来启动MySQL服务了，命令如下：
service mysqld start
现在将其配置成为自启动状态，命令如下：
chkconfig mysqld on
配置完成后可以用如下命令来检查：
chkconfig --list mysqld
结果显示如下，表示上面的设定是成功的：
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
最后，将MySQL的执行路径添加进PATH环境变量，在/etc/profile的最后一行添加如下内容：
export PATH=$PATH:/usr/local/mysql/bin
执行如下命令使更改立即生效：
source /etc/profile
下面详细介绍一下主从复制同步的过程。
1.设置主库
1）修改主库my.cnf，主要是设置各不一样的server-id，以及要同步的数据库名字。可以用vim编辑/etc/my.cnf文件，在[mysqld]段下面增加如下内容：
server-id = 1
log-bin= binlog
binlog_format=mixed
从库my.cnf文件跟主库不一样，具体改动如下：
server-id=2
log-bin= binlog
binlog_format=mixed
replicate_wild_do_table=adserver.%
replicate_wild_ignore_table=mysql.%
adserver为要同步的数据库的名字，mysql为不需要同步的数据库，这里为了避免发生跨库同步失败的问题，建议在从库里面这样配置。将从库所在服务器的server-id修改为非1的数字即可，不然会在同步的过程中发现错误，主库所在的机器的是必须要开启二进制日志的，其日志格式为mixed，而从库服务器则不必非要开启二进制日志。
2）分别重启主从库服务器让修改的配置生效，命令如下：
service mysqld restart
3）登录主库：
mysql -u root -p
此处不必输入密码即可进行，这是由于MySQL服务器是刚配置的，所以MySQL数据库的root密码暂时没有配置，为了安全起见，大家可以稍后再进行设置。
4）赋予从库权限账号，允许用户在主库上读取日志，命令如下：
mysql> grant replication slave on *.* to 'admin'@'192.168.11.27' identified by 'admin@101';
replication slave是一个基本的必须的权限，它直接授予从机服务器以该账户连接主机执行replication操作的权限，为了安全起见，建议只分配admin账户replication slave权限。
此操作完成以后，建议立即在从机上验证一下，如果成功显示MySQL登录界面则表示设置成功，命令如下：
mysql -u admin -p -h 192.168.1.204
Enter password:
输入admin密码以后应该有如下成功登录的显示结果。
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.40-log Source distribution
Copyright (c) 2000, 2014, 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>
这里有个知识点需要说明一下，MySQL数据库的权限系统在实现上比较简单，相关权限信息主要存储在几个被称为grant tables的系统表中，即：mysql.user、msyql.db、mysql.host、mysql.table_priv和mysql.columm_priv。由于权限信息的数据量比较小，访问又非常频繁，所以MySQL在启动的时候，就会将所有的权限信息都加载到内存中，并保存在几个特定的结构里。这就使得每次在手动修改了相关权限表之后，都必须要执行flush privileges，通知MySQL重新加载MySQL的权限信息。当然，如果通过grant、revoke或drop user命令来修改相关权限，则不必再手动执行flush privileges命令了。
5）检查创建是否成功，命令显示结果如下（我们只需关注user名为admin的那行即可）：
mysql> select user,host from mysql.user;
+-------+---------------+
| user | host |
+-------+---------------+
| root | 127.0.0.1 |
| admin | 192.168.1.205 |
| root | ::1 |
| | fabric |
| root | fabric |
| | localhost |
| root | localhost |
+-------+---------------+
7 rows in set (0.01 sec)
6）锁主库表，命令如下（建议不要退出此终端，以免锁表失败）：
mysql> flush tables with read lock;
7）显示主库信息。
这里要记录一下File和Position，因为在设置从库时将会用到它们，命令如下：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 106 | mydata | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
8）Slave端机器获取Master端MySQL数据库的“快照”有两种方法。第一种是锁表后直接用tar将原Master打包给Slave机器，这种情况适用于网站初始化，而且数据库比较单一的工作场景。比如说笔者的数据库已经在线运行了一段时间，由于MySQL中有默认设置，因此没有按表空间分离数据，所有的表数据都被放到ibdata1文件中了，其中包括数据字典（也就是InnoDB表的元数据）、变更缓冲区、双写缓冲区、撤销日志。ibdata1文件的大小会持续增长，就算删除了表的数据，ibdata1文件的体积也不会减小。所以如果是采用tar方案的话，首先需要对ibdata1文件进行“瘦身”操作。
如果机器上还存在着不同的数据库，那么就不适合用tar打包的方法了，比如笔者的订单系统的数据库上面的生产数据库数据差不多有9.8GB了，321张表，但有的数据库不需要同步，所以可以用别的方法来进行（比如mysqldump，它可以单独对某个特定的数据库进行逻辑备份，下面会重点讲解这种方法）。
如果只需单独备份主机上的adserver数据库，可以用如下的方式：
mysqldump --master-data -u root -p adserver > adserver.sql
另外，稍微解释一下--master-data参数的作用，mysqldump程序的开发者给程序设计这项参数是为了帮助获取对应的Log Position，在添加了这个参数选项以后，mysqldump会在dump文件中产生一条CHANGE MASTER TO命令，命令中记录了dump时刻所对应的Log Position的详细信息。
2.设置从库
1）在主库上将adserver.sql传输给从机，推荐用rsync命令，如果是生产环境下的数据库，那么rsync尤其适合在服务器之间传输上百GB的生产数据库数据，命令如下：
rsync -vzrtopg adserver.sql root@192.168.1.205:/root/
2）登录从库，建立adserver数据库，命令如下：
mysql>create database adserver;
然后，退出mysql命令行，导入adserver.sql数据，命令如下：
mysql -u root -p adserver < /root/adserver.sql
3）解锁主库表，命令如下所示：
mysql> unlock tables;
4）在从库上设置同步。
设置连接MASTER“MASTER_LOG_FILE”为主库的File，“MASTER_LOG_POS”为主库的Position，命令如下所示：
mysql> slave stop;
mysql> change master to master_host='192.168.1.204',master_user='admin', master_password='admin@101',
master_log_file='mysql-bin.000004', master_log_pos=7323251;
mysql> slave start;
5）查看从库的status状态，命令如下所示：
mysql>show slave status\G;
结果如下所示：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.204
Master_User: admin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 7323251
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: adserver.%
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 7323251
Relay_Log_Space: 413
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR:
No query specified
注意上面显示结果中两部分的显示内容，“Slave_IO_Running：Yes”表示网络正常，“Slave_SQL_Running：Yes”表示结构正常，根据MySQL主从同步的原理，这两个部分必须都为YES（正常）才表示同步是成功的，此外，还要注意Seconds_Behind_Master这个选项，它表示主从同步延迟时间，在一些对数据即时性要求很高的生产场景下，这个选项也应该引起我们足够的重视。
6）进行一些测试工作。
在主库的adserver数据上建立名为yuhongchun的表，命令如下：
mysql> CREATE TABLE `yuhongchun` (
`id` INT(5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(20) NOT NULL ,
`password` CHAR(32) NOT NULL ,
`time` DATETIME NOT NULL ,
`number` FLOAT(10) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = MYISAM ;
在从机中马上就可以看到，在mydata数据库下产生了名为yuhongchun的表，只不过表的记录目前为空，另外，观察下从机的中继日志，可以用如下命令：
mysqlbinlog localhost-relay-bin.000002
结果显示如下所示：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150707 4:16:16 server id 2 end_log_pos 107 Start: binlog v 4, server v 5.5.40-log created 150707 4:16:16
BINLOG '
0IqbVQ8CAAAAZwAAAGsAAAAAAAQANS41LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#691231 19:00:00 server id 1 end_log_pos 0 Rotate to mysql-bin.000004 pos: 7323251
# at 150
#150707 3:11:08 server id 1 end_log_pos 0 Start: binlog v 4, server v 5.5.40-log created 150707 3:11:08
BINLOG '
jHubVQ8BAAAAZwAAAAAAAAAAAAQANS41LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 253
#150707 4:20:17 server id 1 end_log_pos 7323578 Query thread_id=12 exec_time=0 error_code=0
use `adserver`/*!*/;
SET TIMESTAMP=1436257217/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `yuhongchun` (
`id` INT(5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(20) NOT NULL ,
`password` CHAR(32) NOT NULL ,
`time` DATETIME NOT NULL ,
`number` FLOAT(10) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = MYISAM
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
这表明主从同步复制是成功的。
3.MySQL主从同步常见的问题
熟悉MySQL主从复制架构的朋友应该很熟悉常见的错误，其实主要就是网络、MySQL访问权限、iptables和SELinux等的问题，我们平时注意检查这些问题，处理起来应该不是很困难，由于我们一般将MySQL主从服务器放在局域网（内网）环境内，在局域网环境内我们要记得关闭iptables和SELinux，注意Slave_IO_Running和Slave_SQL_Running的状态必须确保为YES才行，另外也要注意从机的Seconds_Behind_Master值。
这里跟大家分享下笔者在工作中主从同步时遇到的问题。
（1）表结构不同导致的主从同步失败的问题，从机报错信息如下所示：
120307 14:44:50 [ERROR] Slave: Error 'Cannot add or update a child row: a foreign key constraint fails (`offer99/fulfillment`, CONSTRAINT `fulfillment_ibfk_1` FOREIGN KEY (`offer_id`) REFERENCES `offer` (`offer_id`))' on query. Default database: 'offer99'. Query: 'INSERT INTO fulfillment(account_id,product_id,f_record_ts,fulfilled_ts,transaction_id,statuscode,gross_rev_in,gross_rev_out,merch_rev,net_rev,vc_points,vc_exchange_rate,offer_id,tab_name,advertising) VALUES('9496896','613','2012-03-07 09:33:46','2012-03-09:30:02','10139126','1','20.00','12.00','12.00','8','120000','10000.00','663','-1','1')', Error_code: 1452
笔者起初以为是外键约束的问题，在这上面浪费了不少时间；在这里跟大家提个醒，这个时候千万不要盲目地删除外键，这会导致在以后的MySQL主从同步维护工作中后患无穷，是个治标不治本的方法。这个时候更应该静下心来处理这个问题，我仔细地检查了两边数据库的fulfillment和offer表，发现两边的offer表数据不一致，特别是从数据库，差了3条数据，那么我们如何从几万条数据中找到究竟是差了哪3条数据呢？
这里可以用select into outfile的方法导出两边offer表的数据，将其保存为CSV格式的文件，然后用Linux下的diff命令进行比较，这样很快就能找出差了的数据的offer_id值，然后以SQL格式的形式导出数据并导进从机（load命令也是可以的），然后在从机上重新执行slave start命令进行同步，这样就可以排除故障了。
SELECT * FROM offer WHERE offer_id IN (658,663,694)
（2）表结构不同导致的从机更新失败
根据MySQL官方文档的说明，如果遇到错误的SQL执行语句的时候，故障的表象是从机不会去同步主库，所以要手工地让这个语句不被执行，跳过N个事件步骤后直接处理下一个事件，而这个跳过去的事件对数据的完整性是没有什么影响的。一般设置“SET GLOBAL sql_slave_skip_counter=1”就可以跳过去了，如果跳不过去，就要具体判断得跳多少步才能正确了。
这里再提供一个具体案例以供大家参考，我们本来就在从机上安装了bugfree数据库，结果笔者不小心在主数据库上也安装了bugfree数据库，这时候从机上不能再创建bugfree数据库了，直接导致Replication同步失败。这个时候可以用“set global sql_slave_skip_counter=N”忽略这个操作（N值取决于在主机上建立bugfree库和表的值），从而让数据库主从同步正常。
（3）主机硬件故障，如何切换主从服务器（一主多从）
某天早上笔者一来就发现公司的网站打不开了，初步断定是主数据库出了问题，发现SSH都登录不上去了，这时候紧急联系机房人员让他们帮忙重启，发现居然连重启都重启不了，他们断定是MySQL服务器硬件出了问题。这时候需要紧急将从机提升为主服务器，我们应该如何操作呢？可以按照如下的步骤进行操作：
1）用stop slave IO_THREAD命令在从机上停掉IO_Thread进程，确保从机上再没有同步的SQL语句，即出现“Has read all relay log”语句字样。
2）在从数据库上执行stop slave命令停止从机服务，然后执行reset master命令将其设置成主数据库。
3）在起始的从机上将原有的主机IP地址更换为此机器（现在已变为Master主数据库）的IP地址。
4）删除新的主数据库服务器的master.info和relay-log.info文件，防止它下次重启时还是按照从机来启动。
MySQL主从Replication复制非常快，在保证网络的前提下，小数据的改变几乎感觉不到延迟（但还是属于异步同步），通常在Master端改动以后，Slave端也会立即改动，这种模式非常适合那种对延时性要求很低的工作环境，比如线上的BBS论坛；如果是电子商务网站，由于对数据的即时性要求很高，建议不要用读写分离的方案，即：所有的读写操作均在主机上实现，从机只是作为主机的备份。其他非电子商务性质的网站，可以考虑MySQL一主多从，读写分离的方案。
