1、高可用数据库架构设计完整版 标准化管理处编码BBX968T-XBB8968-NNJ668-MM9N高可用数据库架构设计MySQL数据库高可用架构设计目标：MySQL 数据库服务器不受单点宕机的影响，即时 A 服务器挂掉或者磁盘损坏物理故障导致数据库不可用也不会导致整个系统处于不可用状态，因为还有另外一台备用的数据库服务器可以提供服务。派宝箱采取方案双机主从热备 (Mater Slave 模式)背景：双机热备的概念简单说一下，就是要保持两个数据库的状态自动同步。对任何一个数据库的操作都自动应用到另外一个数据库，始终保持两个数据库数据一致。 这样做的好处: 1. 可以做灾备，其中一个坏了可以切换到
2、另一个。2. 可以做负载均衡，可以将请求分摊到其中任何一台上，提高网站吞吐量。 对于异地热备，尤其适合灾备。原理：MySQL Replication双机热备 + 每天自动sqldump出物理文件备份双机主从自动热备实现数据库服务的高可用加sqldump导出数据文件的方式备份。双重保险！可能遇到的问题与挑战：主从数据库数据一致性问题宕机后主从切换的问题1 复制概述 Mysql内建的复制功能(MySQL REPLICATION)是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍
3、来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。 mysql支持的复制类型：（）：基于语句的复制： 在主服务器上执行的SQL语句，在从
4、服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。 一旦发现没法精确复制时， 会自动选着基于行的复制。（）：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从开始支持（）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。 . 复制解决的问题 MySQL复制技术有以下一些特点： (1) 数据分布 (Data distribution )(2) 负载平衡(load balancing) (3) 备份(Backups) (4) 高可用性和容错行 High availability and failover
5、 复制如何工作 整体上来说，复制有3个步骤： (1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）； (2) slave将master的binary log events拷贝到它的中继日志(relay log)； (3) slave重做中继日志中的事件，将改变反映它自己的数据。下图描述了复制的过程： 该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，mast
6、er通知存储引擎提交事务。 下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。 SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要
7、该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。 此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。2 .复制配置有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器，初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和s
8、lave的数据信息同步，达到备份的目的。要点：负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。环境：Master和slave的MySQL数据库版本同为操作系统：unbuntu IP地址：、创建复制帐号1、在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件中命令如
9、下：mysql GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*IDENTIFIED BY 1234;建立一个帐户backup，并且只能允许从这个地址上来登陆，密码是1234。(如果因为mysql版本新旧密码算法不同，可以设置：）、拷贝数据（假如是你完全新安装mysql主从服务器，这个一步就不需要。因为新安装的master和slave有相同的数据）关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要
10、相同！、配置master接下来对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值：server-id=1log-bin=mysql-binserver-id：为主服务器A的ID值log-bin：二进制变更日值重启master，运行SHOW MASTER STATUS，输出如下：、配置slaveSlave的配置与master类似，你同样需要重启slave的MySQL。如下：log_bin = mysql-binserver_id = 2relay_log = mysql-relay-binlog_slave_updates = 1read_only
11、 = 1server_id是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_o
12、nly，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。、启动slave接下来就是让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：mysql CHANGE MASTER TO MASTER_HOST=server1,- MASTER_USER=repl,- MASTER_PASSWORD=p4ssword,- MASTER_
13、LOG_FILE=, - MASTER_LOG_POS=0;MASTER_LOG_POS的值为0，因为它是日志的开始位置。你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：mysql SHOW SLAVE STATUSG* 1. row *Slave_IO_State: Master_Host: server1 Master_User: repl Master_Port: 3306 Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: Relay_Log_Pos: 4Relay_
14、Master_Log_File: Slave_IO_Running: NoSlave_SQL_Running: No .omitted.Seconds_Behind_Master: NULLSlave_IO_State, Slave_IO_Running,和Slave_SQL_Running是No表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。实际上，MySQL知道的第一个事件的位置是4。为了开始复制，你可以运行：mysql START SLAVE;运行SHOW SLAVE STATUS查看输出结果：mysql SHOW SLAVE
15、STATUSG* 1. row *Slave_IO_State: Waiting for master to send event Master_Host: server1 Master_User: repl Master_Port: 3306 Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 164Relay_Log_File: Relay_Log_Pos: 164Relay_Master_Log_File: Slave_IO_Running: YesSlave_SQL_Running: Yes .omitted.Seconds_B
16、ehind_Master: 0在这里主要是看: Slave_IO_Running=Yes Slave_SQL_Running=Yesslave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。你可查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接：在master上输入show processlistG;mysql show pro
17、cesslist G* 1. row *Id: 1User: rootHost: localhost:2096db: testCommand: QueryTime: 0State: NULLInfo: show processlist* 2. row *Id: 2User: replHost: localhost:2144db: NULLCommand: Binlog DumpTime: 1838State: Has sent all binlog to slave; waiting for binlog to be updatedInfo: NULL2 rows in set sec)行2为
18、处理slave的I/O线程的连接。在slave服务器上运行该语句：mysql show processlist G* 1. row *Id: 1User: system userHost:db: NULLCommand: ConnectTime: 2291State: Waiting for master to send eventInfo: NULL* 2. row *Id: 2User: system userHost:db: NULLCommand: ConnectTime: 1852State: Has read all relay log; waiting for the slave
19、 I/O thread to update itInfo: NULL* 3. row *Id: 5User: rootHost: localhost:2152db: testCommand: QueryTime: 0State: NULLInfo: show processlist3 rows in set sec)行1为I/O线程状态，行2为SQL线程状态。问题与挑战之 主从数据库一致性问题MYSQL复制不同步的原因mysql replication（复制）采用binlog进行网络传输，所以网络延时是产生mysql主从不同步的主要原因，这会给我们进行主从复制读写分离带来一定困难为了避免这种情
20、况，在配置服务器的时候推荐使用INNODB存储引擎的表，在主机上可以设置sync_binlog下面内容摘抄自MYSQL行调优和架构设计“sync_binlog”：这个参数是对于 MySQL 系统来说是至关重要的，他不仅影响到 Binlog 对 MySQL 所带来的性能损耗，而且还影响到 MySQL 中数据的完整性。对“sync_binlog”参数的各种设置的说明如下： sync_binlog=0，当事务提交之后，MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache 满了之后才同步到
21、磁盘。 sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。在 MySQL 中系统默认的设置是 sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统 Crash，在 binlog_cache 中的所有 binlog 信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统Crash，也最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响
22、。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。如果master主机上的max_allowed_packet比较大，但是从机上没有配置该值的话，该参数还是使用默认值1MB此时很有可能导致同步失败，建议主从两台机器都设为5MB比较合适1.配置优化在MySQL中，一次事务提交后，需要写undo、写redo、写binlog，写数据文件等等。在这个过程中，可能在某个步骤发生crash，就有可能导致主从数据的不一致。为了避免这种情况，我们需要调整主从上面相关选项配置，确保即便发生crash了，也不能发生
23、主从复制的数据丢失。在master上修改配置innodb_flush_log_at_trx_commit = 1sync_binlog = 1上述两个选项的作用是：保证每次事务提交后，都能实时刷新到磁盘中，尤其是确保每次事务对应的binlog都能及时刷新到磁盘中，只要有了binlog，InnoDB就有办法做数据恢复，不至于导致主从复制的数据丢失。 在slave上修改配置master_info_repository = TABLErelay_log_info_repository = TABLErelay_log_recovery = 1上述前两个选项的作用是：确保在slave上和复制相关的元数
24、据表也采用InnoDB引擎，受到InnoDB事务安全的保护，而后一个选项的作用是开启relay log自动修复机制，发生crash时，会自动判断哪些relay log需要重新从master上抓取回来再次应用，以此避免部分数据丢失的可能性。通过上面几个选项的调整，就可以确保主从复制数据不会发生丢失了。但是，这并不能保证主从数据的绝对一致性，因为，有可能设置了ignoredorewrite等replication规则，或者某些SQL本身存在不确定因素，或者人为在slave上修改数据，最终导致主从数据不一致。这种情况下，可以采用pt-table-checksum和pt-table-sync工具来进行
25、数据的校验和修复。2. 一致性检测和修复工具pt-table-checksum 和 pt-table-sync问题与挑战之 主从切换1 正常切换1）从服务器检查SHOW PROCESSLIST语句的输出，直到你看到Has read all relaylogwaiting for the slave I/O thread to update it2）确保从服务器已经处理了日志中的所有语句。 STOP SLAVE IO_THREAD当从服务器都执行完这些，它们可以被重新配置为一个新的设置。3）在被提升为主服务器的从服务器上，发出 STOP SLAVE和RESET MASTER和RESET SLAV
26、E操作。4）然后重启mysql服务。5）在备用服务器（新的主服务器）创建用户grant replication slave on *.* torepdcsIP of A identified by replpwdgrant allprivileges on *.* to repdcsIP identified by replpwd;6) 在主服务器上RESET MASTER。然后CHANGE MASTER TOMASTER_HOST=原从服务器IP,MASTER_USER=用户名,MASTER_PASSWORD=密码 ,master_log_file= ;7)查看状态 show slave status G;Show master status G;8）修改应用的连接地址到新的主库切换完成。2 主机直接宕机1 在备机上执行STOP SLAVE 和RESET MASTER2 查看show slave status G;3 然后修改应用的连接地址。一般大部分切换为直接宕机主机已经没法提供服务
