运维高级工程师必备之MySQL数据的主从复制、半同步复制和主主复-MySQL-Tutorial-php.cn

运维工程师必备之MySQL数据的主从复制、半同步复制和主主复制详解原创作品，允许转载，转载时请务必以超链接形式标明文章 ? 原始出处 ? 、作者信息和本声明。否则将追究法律责任。 http://chrinux.blog.51cto.com/6466723/1204586 一、MySQL复制概述 ? ?⑴、

运维工程师必备之MySQL数据的主从复制、半同步复制和主主复制详解

原创作品，允许转载，转载时请务必以超链接形式标明文章?原始出处?、作者信息和本声明。否则将追究法律责任。http://chrinux.blog.51cto.com/6466723/1204586

一、MySQL复制概述

? ?⑴、MySQL数据的复制的基本介绍

? ?目前MySQL数据库已经占去数据库市场上很大的份额，其一是由于MySQL数据的开源性和高性能，当然还有重要的一条就是免费~不过不知道还能免费多久，不容乐观的未来，但是我们还是要能熟练掌握MySQL数据的架构和安全备份等功能，毕竟现在它还算是开源界的老大吧！

? ?MySQL数据库支持同步复制、单向、异步复制，在复制的过程中一个服务器充当主服务，而一个或多个服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
? ?单向复制有利于健壮性、速度和系统管理：

? ?健壮性：主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。

? ?速度快：通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均衡策略很有效，但一般是更新查询。

? ?系统管理：使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。

? ?⑵、MySQL数据复制的原理

? ?MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。

? ?每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

? ?认识到二进制日志只是一个从启用二进制日志的固定时间点开始的记录非常重要。任何设置的从服务器需要主服务器上的在主服务器上启用二进制日志时的数据库拷贝。如果启动从服务器时，其数据库与主服务器上的启动二进制日志时的状态不相同，从服务器很可能失败。

? ?将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定，因此当表正复制到从服务器上时，不可能在主服务器上进行更新。当我们执行表的无锁热备份时，则不再需要全局读锁定。

? ?MySQL数据复制的原理图大致如下：

从上图我们可以看出MySQL数据库的复制需要启动三个线程来实现：

? ?其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

? ?在前面的描述中，每个从服务器有3个线程。有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

? ?这样读取和执行语句被分成两个独立的任务。如果语句执行较慢则语句读取任务没有慢下来。例如，如果从服务器有一段时间没有运行了，当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程远远滞后。如果从服务器在SQL线程执行完所有索取的语句前停止，I/O 线程至少已经索取了所有内容，以便语句的安全拷贝保存到本地从服务器的中继日志中，供从服务器下次启动时执行。这样允许清空主服务器上的二进制日志，因为不再需要等候从服务器来索取其内容。

二、实列说明MySQL的主从复制架构和实现详细过程

? ? ?主从架构数据库的复制图如下：

其配置详细过程如下：

? ?1、环境架构：

? ? ? ?RedHat Linux Enterprise 5.8 ? ? ? ? mysql-5.5.28-linux2.6-i686.tar

? ? ? ?Master：172.16.7.1/16 ? ? ? ? ? ? ? ? Slave：172.16.7.2/16

? ?2 、安装mysql-5.5.28，需要在主节点和备节点上安装mysql

? ? ? ?Master：

? ? ? ?安装环境准备：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 为mysql的安装提供前提环境和初始化安装mysql 创建数据库目录 # mkdir /mydata/data –pv 创建mysq用户 # useradd -r mysql 修改权限 # chown -R mysql.mysql /mydata/data/ 使用mysql-5.5通用二进制包安装 解压mysql软件包 # tar xf mysql-5.5.28-linux2.6-i686.tar.gz-C /usr/local/ 创建连接，为了方便查看mysql的版本等信息 # cd /usr/local/ #ln –sv mysql-5.5.28-linux2.6-i686.tar.gzmysql 修改属主属组 # cd mysql # chown -R root.mysql ./* 初始化数据库 # scripts/mysql_install_db –user=mysql --datadir=/mydata/data/ 提供配置文件 # cp support-files/my-large.cnf /etc/my.cnf 提供服务脚本 # cp support-files/mysql.server/etc/rc.d/init.d/mysqld 添加至服务列表 # chkconfig --add mysqld # chkconfig --list mysqld # chkconfig mysqld on 编辑配置文件，提供数据目录 # vim /etc/my.cnf # The MySQL server? 修改mysqld服务器端的内容 log-bin=master-bin 主服务器二进制日志文件前缀名 log-bin-index=master-bin.index? 索引文件 innodb_file_per_table=?1?????开启innodb的一表一个文件的设置 server-id?????? =?1??????????必须是唯一的 datadir =/mydata/data??????? 数据目录路径 启动mysql服务 # servicemysqld start 为了便于下面的测试，设置环境变量 # vim/etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin 执行环境变量脚本，使其立即生效 # . /etc/profile.d/mysql.sh

?启动服务并进行相关的测试：

?mysql的安装配置完成，下面增加一个用于同步数据的账户并设置相关的权限吧！

1 2 3 4 建立用户账户 mysql> grant replication slave on *.* to?'chris'@'172.16.%.%'?identified by?'work'; 刷新数据使其生效 mysql> flush privileges;

? ?至此我们mysql的Master设置完成，下面进行slave端的设置吧！

? ?Slave：

? ?安装环境配置：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 创建mysql数据库目录 # mkdir /mydata/data –pv 创建mysql用户 # useradd -r mysql 修改数据目录权限 # chown -R mysql.mysql /mydata/data/ 使用mysql-5.5通用二进制包安装mysql 解压mysql软件包 # tar xf mysql-5.5.28-linux2.6-i686.tar.gz-C /usr/local/ 创建连接，便于查看mysql的版本等信息 # cd /usr/local/ # ln –sv mysql-5.5.28-linux2.6-i686.tar.gzmysql 修改mysql属主属组 # cd mysql # chown -R root.mysql ./* 初始化mysql数据库 # scripts/mysql_install_db –user=mysql--datadir=/mydata/data/ 提供mysql配置文件 # cp support-files/my-large.cnf /etc/my.cnf 提供服务脚本 # cp support-files/mysql.server /etc/init.d/mysqld 添加至服务列表 # chkconfig --add mysqld 编辑配置文件 # vim /etc/my.cnf # The MySQL server #log-bin=mysql-bin????? 禁用二进制日志，从服务器不需要二进制日志文件 datadir = /mydata/data? mysql的数据目录 relay-log = relay-log?? 设置中继日志 relay-log-index = relay-log.index? 中继日志索引 innodb_file_per_table =?1 server-id?????? =?2????id不要和主服务器的一样 设置环境变量 # vim/etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin 执行此脚本（导出环境变量） # . /etc/profile.d/mysql.sh 启动服务 # service mysqld start

? 到这slave服务的mysql安装和配置完成，下面启动slave复制吧，开启之前先查看下从服务上的二进制文件吧

1 2 3 4 5 mysql> show master status; #在Master上执行查看二进制文件 在从服务器上开启复制功能 change master to master_host='172.16.7.1',master_user='chris',master_password='work',master_log_file='master-bin.000001',master_log_pos=407; 开启复制功能 mysql>start slave;

至此我们的mysql服务器的主从复制架构已经基本完成，下面开启服务并测试测试吧~

在从服务器开启复制进程：mysql>start slave;

? ?至此我们mysql服务器的主从复制架构已经完成，但是我们现在的主从架构并不完善，因为我们的从服务上还可以进行数据库的写入操作，一旦用户把数据写入到从服务器的数据库内，然后从服务器从主服务器上同步数据库的时候，会造成数据的错乱，从而会造成数据的损坏，所以我们需要把从服务器设置成只读~方法如下：

注意：read-only = ON ，这项功能只对非管理员组以为的用户有效！

OK，此致我们的mysql基于主从架构的复制功能已经搭建全部完成~下面介绍下关于mysql数据目录下面各个文件的功能和作用！

? ?由于二进制文件的缓冲区内，当我们的服务器宕机的时候，缓存区内的数据并没有同步到二进制日志文件内的时候，那就悲剧了，缓冲区内的数据就无法找回了，为了防止这种情况的发送，我们通过设置mysql直接把二进制文件记录到二进制文件而不再缓冲区内停留。

sync-binlog = ON 在主服务器上进行设置，用于事务安全

? 从上面我们可以看到从服务器启动的时候其Slave_IO_Running: Yes和Slave_SQL_Running: Yes是自动启动的，但是有时候我们在主服务上进行的误操作等，也会直接同步到从服务器上的，要想恢复那就难了，所以我们需要关闭其自动执行功能，让其能够停止，skip-slave-start = 1 ,让其不开启自动同步，但是遗憾的是mysql5.28上已经没有了，我们可以通过停止相关线程来实现：

mysql>STOP SLAVE 或STOP SLAVE ?IO_THREAF或STOP SLAVE SQL_THREAD

注意：从服务器的所有操作日志都会被记录到数据目录下的错误日志中！

三、MySQL的半同步复制

? ?实现半同步复制的功能很简单，只需在mysql的主服务器和从服务器上安装个google提供的插件即可实现，

? ?主服务上使用semisync_master.，从服务器上使用sosemisync_slave.so插件即可实现，插件在mysql通用二进制的mysql/lib/plugin目录内。

其配置步骤如下

1、分别在主从节点上安装相关的插件

master：

1 2 3 安装插件：mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME?'semisync_master.so'; 启动模块：mysql> SET GLOBAL rpl_semi_sync_master_enabled =?1; 设置超时时间：mysql> SET GLOBAL rpl_semi_sync_master_timeout =?1000；

1 2 3 4 slave： 安装插件：msyql> INSTALL PLUGIN rpl_semi_sync_slave SONAME?'semisync_slave.so'; 启动模块：mysql> SET GLOBAL rpl_semi_sync_slave_enabled =?1; 重启进程使其模块生效：mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

? 上面的设置时在mysql进程内动态设定了，会立即生效但是重启服务以后就会失效，为了保证永久有效，需要把相关配置写到主、从服务器的配置文件my.cnf内：

1 2 3 4 5 6 7 8 在Master和Slave的my.cnf中编辑： # On Master [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000???#此单位是毫秒 # On Slave [mysqld] rpl_semi_sync_slave_enabled=1

? 确认半同步功能已经启用，通过下面的操作进行查看

1 2 3 4 5 master： mysql> CREATE DATABASE asyncdb; master> SHOW STATUS LIKE?'Rpl_semi_sync_master_yes_tx'; slave> SHOW DATABASES; 其测试过程如下

然后把从服务器上的复制进程开启，

? 我们至此已经实现了mysql数据库复制的半同步方式的架构，并且通过测试查看了复制功能，下面我们进行双主模型架构吧。

四、MySQL设置主-主复制：masterslave?
1、在两台服务器上各自建立一个具有复制权限的用户；让两个数据库互为主从的关系

2、修改配置文件：

把上面的连个数据库的配置文件重新配置，其配置如下?

1 2 3 4 5 6 7 8 # 主服务器上 [mysqld] server-id =?1 log-bin = mysql-bin relay-log = relay-mysql relay-log-index = relay-mysql.index auto-increment-increment =?2???????????#每次跳两个数。 auto-increment-offset =?1??????????????#从1开始。

1 2 3 4 5 6 7 [mysqld] server-id =?2 log-bin = mysql-bin relay-log = relay-mysql relay-log-index = relay-mysql.index auto-increment-increment =?2 auto-increment-offset =?2

? 如果此时两台服务器均为新建立，且无其它写入操作，各服务器只需记录当前自己二进制日志文件及事件位置，以之作为另外的服务器复制起始位置即可

?在各个服务器上建立账号和权限，来进行同步设置

1 2 3 master： mysql> GRANT REPLICATION SLAVE ON *.* TO?'chrislee'@'172.16.%.%'?IDENTIFIED BY?'work'; mysql> flush privileges;

1 2 3 slave： mysql> GRANT REPLICATION SLAVE ON *.* TO?'chrisli'@'172.16.%.%'?IDENTIFIED BY?'work'; mysql> flush privileges

在各服务器上指定对另一台服务器为自己的主服务器即可：

1 2	`server1` `mysql> CHANGE MASTER TO MASTER_HOST='172.16.7.2',MASTER_USER='chrisli',MASTER_PASSWORD='work',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=344;`

1 2	`server2：` `mysql> CHANGE MASTER TO MASTER_HOST='172.16.7.1',MASTER_USER='chrislee',MASTER_PASSWORD='work',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=345;`