mysql 配置主备数据库_mysql主从数据库工作原理和配置

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。

mysql支持两种复制方式：基于行的复制和基于语句的复制。基于语句的复制在mysql3.23版本中就存在，而基于行的复制方式在5.1版本中才加进来。这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。这意味着，在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主库之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。

复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。除此之外，每个备库也会对主库增加一些负载(例如网络I/O开销)，尤其当备库请求从主库读取旧的二进制日志文件时，可能会造成更高的I/O开销。另外锁竞争也可能阻碍事务的提交。最后，如果是从一个高吞吐量的主库上复制到多个备库，唤醒多个复制线程发送时间的开销将会累加。

通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过复制来扩展写操作。在一主库多备库的架构中，写操作会被执行多次，这时候整个系统的性能取决于写入最慢的部分。

当使用一主库多备库的架构时，可能会造成一些浪费，因为本质上他会复制大量不必要的重复数据。例如，对于一台主库和10台备库，会有11分数据拷贝，并且这11台服务器的缓存中存储了大部分相同的数据。这和在服务器上有11台RAID1类似。这不是一种经济的硬件使用方式，但这种复制架构却很常见。

1、复制解决的问题

数据分布：mysql复制通常不会对带宽造成很大的压力，并且可以随意的停止或开始复制，并在不同的地理位置来分布数据备份，例如不同的数据中心。即使在不稳定的网络环境下，远程复制也是可以工作。但如果为了保持很低的复制延迟，最好有一个稳定的、低延迟的连接。

负载均衡：通过mysql复制可以将读操作分不到多个服务器上，实现对读密集型应用的优化，并且实现很方面，通过简单的代码修改就能实现基本的负载均衡。对于小规模的应用，可以简单对机器，名做硬编码或使用DNS轮询。当然也可以使用更复杂的方法，例如网络负载均衡这一类的标准负载均衡解决方案。能够很好的将负载均衡分配到不同的mysql服务器上。linux虚拟服务器(LVS)也能够很好的工作。

备份：对于备份来说，复制是一项很有意义的技术补充，但复制既不是备份也不能取代备份。

高可用性和故障切换：复制能够补助应用程序避免mysql单点失败，一个包含复制的设计良好的故障切换系统能显著缩短当机时间。

mysql升级测试：这种做法比较普遍，使用一个更高版本的mysql作为备库，保证在升级全部实例前，查询能够在备库按照预期执行。

2、复制如何工作

mysql复制数据实际上有三个步骤：

在主库把数据更改记录到二进制日志中(binary log)。第一步是在主库上记录二进制日志，在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。mysql会按照事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。

备库将主库上的日志复制到自己的中继日志中(relay log)。第二步备库将主库的二进制日志复制到本地的中继日志中。首先备库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储线程，这个二进制转储线程会读取主库上二进制日志中的事件。他不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生才会被唤醒，备库I/O线程会将接收到的事件记录到中继日志中。

备库读取中继日志中的事件，将其重放到备库数据之上；第三步备库的SQL线程执行，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上I/O线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中。

如上图，在主库上有一个运行的线程，在备库上有两个运行的线程：和其他普通连接一样，由备库发起的连接，在主库上同样拥有一个线程。这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说I/O线程能够独立于SQL线程之外工作，但也限制了复制的过程，其中最重要的一点是在主库上并发运行的查询在备库上只能串行执行，因为只有一个SQL线程来重放中继日志中的事件。

3、配置复制

一般情况下，配置复制有一下几部：

在每台服务器上创建复制账号。mysql会赋予一些特殊的权限给复制线程。在备库运行的I/O线程会建立一个到主库的TCP/IP连接，这意味着必须在主库创建一个用户，并赋予其合适的权限。备库I/O线程以该用户连接到主库并读取其二进制日志：【grant replication slave,replication client on *.* to user@'host' indetified by 'password'】。

配置主库和备库。配置主库打开二进制日志并指定一个独一无二的服务器ID，修改主库的my.cnf增加如下内容：【log_bin = mysql-bin;server_id = 10;】修改完成后重启mysql，为确认二进制日志文件是否已经在主库上创建，使用【show master  status】命令，检查输出是否一致；在备库上也需要修改my.cnf添加一些配置，如下：【log_bin=mysql-bin  server_id=2  relay_log=/var/lib/mysql/relay-bin  log_slave_updates=1 read_only=1】其中server_id是必须的。并且设置read_only，阻止任何没有特权线程修改数据。

通知备库连接到主库并从主库复制数据。通过使用change master to语句，基本命令如下：【change master to master_host='host',master_user='user',master_password='password'k,master_log_file='mysql-bin.000001',master_log_pos=0;】其中master_log_pos参数设置为0，即从日志开头读起。运行下面的命令开始复制：【start slave；】。执行命令后，可以通过：【show slave status】语句来检查复制是否正确执行。并且还可以在主/从库上执行【show processlist】查看连接。

通常在配置主从库时，主库已经运行了一段时间，这时在配置时，就需要先从主库复制数据。需要三个条件来让主库和备库保持同步：

在某个时间点的主库的数据快照；

主库当前的二进制日志文件，和获得数据快照时在该二进制日志文件中的偏移量，把这两个值称为日志文件坐标。通过这两个值可以确定二进制日志的位置。可以通过show master status命令来获取这些值。

从快照时间到现在的二进制日志。

从服务器克隆备库的方法有：使用冷备份、使用热备份、使用mysqldump、使用快照或备份、使用percona xtrabackup、使用另外的备库。

4、推荐的复制配置

主库配置：

#set master-slave,this machine is master;

server-id=10

#error log setting

log-error=/var/lib/mysql/mysql-error.err

#slow_query log setting

long_query_time=2

log-slow-queries= /var/lib/mysql/mysql-slow

#bin log setting

log_bin=/var/lib/mysql/mysql-bin

log-bin-index=/var/lib/mysql/mysql-bin.index

#sync database

binlog-do-db=test

#sync log

sync_binlog=1 #开启该选项，mysql每次提交事务前将二进制日志同步到磁盘上，保证服务器在崩溃时不会丢失事件。

#if innodb recommond

#flush every log write mysql 5.0 and newer only

#mysql 4.1 only,roughly equivalent to innodb_suport_xa

#innodb_flush_logs_at_trx_commit=1

#innodb_support_xa=1

#innodb_safe_binlog

注意：如果无法容忍崩溃导致表损坏，推荐使用InnoDB。在表损坏无关紧要时，MyISAM是可以接受的，但在一次备库服务器崩溃重启后，MyISAM表可能已经处于不一致状态。一种可能是语句没有完全应用到一个或多个表上，那么即使修复了表，数据也可能不一致，因此推荐使用InnoDB。