mysql高可用详解（三）：mysql复制的原理

【通过热备份达到高可用性】

如果服务器宕机，一切都将停止：不能执行（可能很关键的）事务，无法得到用户信息，严重破坏业务。最简单的方式就是配置一个额外的服务器专门作为热备份（hotstandby），在主服务器宕机的时候随时接管业务；

【产生报表】

直接使用服务器上的数据创建报表将大大降低服务器的性能，在某些情况下尤其显著。如果产生报表需要大量的后台作业，最好创建一个额外的服务器来运行这些作业。停止报表数据库上的复制，然后在不影响主要业务服务器的情况下运行大量的查询，从而得到数据库在某一个特定时间的快照。例如，如果在每天最后一个事务处理完毕之后停止复制，可以提取日报表而其他的业务仍然正常的运转；

【调试和审计】

还可以通过审查服务器上的查询。例如，查看某些查询是否有性能上的问题，以及服务器是否由于某个糟糕的查询而不同步；

【复制的基本步骤】

（1）配置一个服务器作为master；

（2）配置一个从服务器作为slave；

（3）将slave连接到master；

注意：除非你从一开始就规划了复制且正确的配置了my.cnf文件，否则步骤一和步骤二要求必须重启每个服务器；

1》配置master：

将服务器配置为master，首先需要确保服务器有一个二进制日志（binary log）和唯一的服务器ID。

二进制日志：保存了master上的所有变更记录，并且可以在slave上重新执行。

服务器ID：用于区分服务器。

将log-bin、log-bin-index和server-id添加到my.cnf配置文件

举例：

在master上创建一个复制用户：

master>CREATE USER repl_user;

master>GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY ‘xyzzy’;

提示：

REPLICATION SLAVE 权限并没有什么特别之处，不过拥有这个权限的用户可以获取master上的二进制日志。完全可以给一个常规的用户赋予REPLICATION SLAVE 权限，但是最好还是将复制slave用户与其他的用户区别开来。这样的话，以后如果想要禁止某些slave的连接，只需要删除该用户就可以了；

2》配置slave：

需要为每个slave分配在一个唯一的服务器ID，分别使用relay-log和relay-log-index选项向my.cnf文件添加中继日志文件（relay log file）和中继日志索引文件（relay log index file）的文件名

 

注意：relay-log和relay-log-index和log-bin、log-bin-index一样，其默认值取决于hostname。relay-log的默认值为hostname-relay-bin，relay-log-index的默认值为hostname-relay-bin.index。使用默认值有一个问题，即一旦服务器的主机名改变，将会因为无法找到中继日志索引文件而认为中继日志文件为空；

修改my.cnf文件之后重启服务器，是配置生效；

配置复制用户的用户权限：

         想要把slave连接到master做复制，除了需要一个能够访问关键文件的shell账户，还需要一个具有特定权限的账户。处于安全考虑，通常要严格的控制这个账号只具备配置maser和slave所需的必要权限；为了创建和删除用户，这个账号需要具备CREATE USER 权限。为了将REPLICATION SLAVE权限赋予复制账号，还需要具有GRANT OPTION 的REPLICATION SLAVE 权限；

 

执行复制相关的工作：

执行FLUSH LOGS命令或者任何的FLUSH 命令，需要RELOAD权限；

执行SHOW MASTER STATUS 和 SHOW SLAVE STATUS 命令需要SUPER 或者REPLICATION CLIENT权限；

执行CHANGE MASTER TO 命令需要SUPER权限；

3》连接master和slave：

将slave定向到master：让他知道从哪里进行复制。为此需要master的4部分信息：

主机名、端口号、具备REPLICATION SLAVE 权限的用户账号、该用户账号的密码

 

slave>CHANGE MASTER TO                 （将slave定向到master）

                   MASTER_HOST=’master-01’ ,

                   MASTER-PORT=’3306’,

                   MASTER_USER=’repl_user’,

                  MASTER_PASSWORD=’xyzzy’

slave>START SLAVE;                        （启动复制）

【二进制日志简介】

 

通常只有即将执行完毕的语句才会被写入到二进制日志，但是在一些特殊情况下可能会写入其他的信息，包括语句的附加信息或者直接代替语句被写入。

 

二进制日志记录了什么：
二进制日志的作用是记录数据库中表的更改，然后用于复制和PITR（即时恢复），另外少数审计情况下也会用到；

 

注意：二进制日志只包括数据库的改动，所以对于哪些不改变数据的语句则不会写入二进制文件；

《基于语句的复制》：

MySQL复制记录了产生变化的SQL语句，称为基于语句的复制。由于基于语句的复制会在slave上重新执行语句，如果master和slave的上下文环境不完全一致的话，可能导致slave上的结果与master不同。

《基于行的复制》：

基于行的复制将每一条记录记为二进制日志中的一行，基于行的复制不仅更加的方便，而且有时候速度更快；

 

【观察复制的工作】

FLUSH LOGS

         该命令强制轮换（rotate）二进制日志，从而得到一个“完成的”二进制日志文件。

SHOW BINLOG EVENTS\G

         检查二进制日志中有哪些事件；

SHOW BINLOG EVENTS IN ‘二进制日志文件名’\G

         查看具体二进制日志文件

SHOW MASTER STATUS

         查看当前正在写入的是哪个二进制日志文件

事件中包含的字段：

Event_type

         这是事件类型。事件的类型表明什么样的信息会被传递给slave。目前一共有35种事件类型（其中有些事件是不可以使用的，但是为了向后兼容而保留了）；

Server_id

         创建事件的服务器ID

Log_name

         用来存储事件的文件名。一个事件只能存储在一个文件中，永远不能跨两个文件。

Pos

         这是事件在文件中的开始位置，即事件的第一个字节；

End_log_pos

         这是事件在文件中的结束位置，也是下一个事件的开始位置。这个位置比事件的最后一个字节高一位，因此事件的字节范围是Pos到End_log_pos-1。通过计算End_log_pos-Pos可以得到这个事件的长度。

Info

         这是关于事件信息的可读文本。不同的事件会显示不同的信息，不过至少可以通过查询事件来知道它所包含的语句；

除了以上的字段以外，每个事件还包含了很多其他的信息，例如时间戳；

查询事件：用于描述如何将数据库上执行的语句写入二进制日志；

XID事件：用于事务管理；

格式描述事件、日志轮换事件：用于在服务器内部管理二进制日志。

【二进制日志的结构和内容】

二进制日志并不是一个单独的文件，而是由一系列易于管理的（例如：在不影响新日志的情况下来移除旧的日志）文件组成的。二进制日志包括一组存储实际内容的二进制文件和一个用来跟踪二进制日志文件存储位置的二进制日志索引文件。

 

每个二进制文件都是以格式描述事件（format description event，Format_desc）开始，以日志轮换事件（rotate event，Rotate）结束。

格式描述事件：包括事件产生该文件的服务器版本号、服务器以及二进制日志的信息等。

格式描述事件还包括一个标记表示二进制文件是否正常的关闭，如果正在写入二进制日志文件，则设置该标记；如果文件关闭，则清除该标记；这样就可以检测出在崩溃事件中损坏的二进制文件，并允许通过复制进行恢复；

日志轮换事件：包含下一个二进制日志文件的名称，以告知二进制日志继续写入哪个文件；

每个二进制日志文件中有多个二进制日志事件，每个事件之间相互独立，同时也是构成二进制日志的基本单位。

 

RESET MASTER ：删除了所有的二进制日志文件并清空了二进制日志索引文件

RESET SLAVER：删除了slave上复制用的所有文件，重新开始；

注意：无论是RESET MASTER还是RESET SLAVER都要求没有活动的复制正在运行；

因此：执行RESET MASTER （在master上）确保没有slave连接到该master

执行RESET SLAVER （在slaver上），先执行STOP SLAVE命令，确保slave上没有活动的复制；

【建立新的slave】

自举slave，而不是从头开始复制：

CHANGE MASTER TO命令中有两个有用的参数：

MASTER_LOG_FILE

MASTER_LOG_POS

使用这些参数指定master开发发送事件的binlog位置；

 

步骤：

（1）配置新的slave

（2）备份master（或者备份已经复制了master的slave）

（3）记下该备份相应的binlog位置（即产生master当前状态的最后一个事件所在的位置）

（4）在新的slave上恢复备份

（5）配置slave从这个binlog位置开始复制；

【克隆master】

克隆master是指获取服务器的快照，通常通过创建备份来完成。服务器备份的技术有很多，一种比较简单的技术是运行mysqldump来创建逻辑备份。其他的备份技术包括复制数据库文件创建物理备份、诸如MySQL企业备份工具的在线备份技术，以及使用Linux的LVM（Logical Volume Manager，逻辑卷管理器）进行卷快照等。

1、首先需要刷新所有表并锁定数据库，防止在检查binlog位置之前数据库发生变化。

使用命令：FLUSH TABLES WITH READ LOCK

一旦数据库被锁定，就可以创建备份，并记录binlog的位置了；

2、创建数据库备份最简单的方法使用mysqldump --all –databases –host=master -1 >backup.sql

3、解锁数据库：UNLOCK TABLES;

4、接下来，在slave上使用mysql实用工具恢复备份：

$ mysql --host=slave -1<back.sql

5、利用前面记下的master的binlog位置，使用CHANGE MASTER TO命令配置slave；

6、启动slave：

START SLAVE

mysqldump可以自动执行前面的所有步骤，要对服务器上所有的数据库进行逻辑备份，输入：

$mysqldump –host=master –all-databases \

--master-data=1 >backup-source.sql

--master-data=1该选项使mysqldump产生CHANGE MASTER TO语句，其参数为二进制文件及其位置，（可以通过SHOW MASTER STATUS得到）

然后就可以在slave上恢复备份了

$mysql --host=slave-1 <backup-source.sql

注意：只有克隆master的时候能够使用—master-data=1自动执行CHANGE MASTER TO语句，后面克隆slave的时候，这些步骤需要分开；

【克隆slave】

只要有一个slave连在master上，就可以使用这个slave创建新的slave，而不需要在离线master了；

克隆slave和克隆master的区别是如何找到binlog的位置，另外，需要注意的是你克隆的那个slave同时还在执行从master的复制；

1、必须在备份前停止slave，保证slave上不再有变化发生（如果使用在线备份的方法，则不需要停止slave）；

2、当slave停止以后，就可以像从前一样刷新数据表，然后创建备份。使用SHOW SALVE STATUS来确定从哪里开始复制。

3、创建备份，然后在新的slave上恢复备份以后，将复制的起点位置为从这个位置开始，然后启动新的slave；

注意：在InnoDB中使用FLASH TABLES READ LOCK是不安全的

【克隆操作的脚本】

Python程序库实现克隆master的方法很简单，使用一个Server对象代表master，然后从这个master上复制数据库即可。使用clone函数即可实现；

【使用Python处理报表】

【在UNIX上调度任务】

可以设置特定事件执行脚本；

【Windows上调度任务】

taskschd.msc打开任务计划程序（Task Scheduler）