Mysql高可用复制原理及主从实例测试解析

Mysql高可用复制原理及主从实例测试解析

一、Mysql复制简介

mysql复制功能可以用来将主数据的数据复制到多个从服务器上。默认采用异步传输，可以在各种网络环境下进行数据复制。主从复制技术已经广泛应用于企业生产中。它避免了数据库的单点故障。当一台服务器出现故障时，其他服务器可以提供稳定可靠的数据服务。

1mysql复制原则

Mysql的复制功能是在多个系统上分发数据。这个机制是通过将一个Mysql服务器(主)的数据复制到其他服务器(从)上来实现的。在复制过程中，一台服务器充当主服务器，而一台或多台其他服务器充当从服务器。主服务器将更新写入二进制日志文件，从服务器接收到更新的日志内容后，根据日志内容在本地计算机上执行，从而实现主从数据复制。当主服务器写入日志时，它还维护一个文件索引来跟踪日志周期。当从服务器连接到主服务器时，它会通知主服务器和从服务器在日志中读取的最后一次成功更新的位置，从该时间点接收来自服务器的任何更新，然后阻止并等待主服务器通知新的更新。

2mysql复制的优势

高性能:通过将请求分配给不同的从服务器，可以提高性能和速度。在这种环境下，所有的写 *** 作都必须在主服务器上执行，而读 *** 作可以平均分配给多个从服务器。

数据安全性:数据从主服务器复制到从服务器，并从主服务器复制一份或多份数据，这意味着数据得到了安全备份

数据共享:对于大型企业来说，各个地区的分支机构也需要共享总部的数据资源，只有主从复制才能共享，需要的时候才能复制。

3mysql复制格式

基于语句的复制:在主服务器上执行SQL语句，在从服务器上执行相同的语句。MySQL默认采用基于语句的复制，效率更高。

基于行的复制:复制更改的内容，而不是在从属服务器上执行命令。

混合型复制:默认采用基于语句的复制，一旦发现基于语句的复制不准确，就会采用基于行的复制。

mysql复制的4个步骤

总的来说，复制有3个步骤:

(1)主控在二进制日志中记录变化(二进制日志事件)；

(2)从设备将主设备的二进制日志事件复制到其中继日志中；

(3)从站重做中继日志中的事件，并改变反映其自身的数据。

5主从复制形式

一主一从

主复制

主从式——扩展系统阅读的性能，因为阅读是从图书馆开始的；

-5.7以上支持。

级联复制-

6半同步复制

5.5版本集成在mysql中，作为插件存在，需要单独安装。

确保在事务提交后，binlog被传输到至少一个从属库。

不保证从库中应用此事务的Binlog。

性能有一定的降低，响应时间会更长。

网络异常或从库宕机，主库卡死，直到超时或从库恢复。

半同步原理图:

二。Mysql主从复制实例分析

一、了解数据库主从复制部署的要点。

>>打开主服务器上的二进制日志，并设置一个唯一的服务器ID号。设置完成后，重启mysql即可生效。

>>在所有从服务器上设置唯一的服务器ID号，重启后生效。

>>在主服务器上创建一个帐户以从从属服务器读取日志文件，或者使用统一帐户。

>>在数据复制之前，需要在主服务器上记录二进制日志的位置标记。

下面是整个主从复制过程的详细分析。

1台主服务器设置数据复制环境

在已安装或已使用的数据库上创建数据库和数据表，并演示如何同步现有数据。

[root@master~]#mysql-uroot-p

mysql>创建数据库HR；

mysql>用HR；

mysql>创建表雇员(

->；employee_idINTNOTNULLAUTO_INCREMENT，

->；namechar(20)不为空，

->；电子邮件varchar(50)，

->；主键(雇员标识))；

mysql>插入员工价值观

->；(1，'汤姆'，'汤姆@example.com')，

->；(2，'Jerry'，'Jerry@example.com')；

mysql>出口

打开主服务器上的二进制日志并设置服务器号。服务器的唯一编号必须是1到232-1之间的整数。根据自己的实际情况来设置。要进行这些设置，需要关闭MySQL数据库并编辑my.cnf或my.ini文件，在[mysqld]设置部分添加相应的配置选项。

[root@master~]#vim/etc/my.cnf

[mysqld]

Log-bin=Jacob-bin#启用二进制日志并设置二进制日志文件前缀。

Server-id=25#设置服务器号

[root@master~]#服务mysqld重启

[root@master~]#服务iptables停止

2从服务器设置数据复制环境

在已安装或已使用的数据库机上设置，并且服务器ID必须设置为集群中的唯一编号。对于从服务器，不需要打开二进制日志功能。当然也可以根据业务需要开启，比如主从互抄时需要开启。

[root@slave~]#vim/etc/my.cnf

[mysqld]

服务器id=24

[root@slave~]#服务mysqld重启

[root@slave~]#服务iptables停止

3创建数据复制帐户

主服务器上必须至少有一个帐户，从属服务器才能连接。该帐户必须有复制从属权限。您可以为不同的从服务器创建不同的帐户和密码，也可以使用统一的帐户和密码。使用CREATEUSER语句创建用户，使用GRANT语句授权帐户。

[root@master~]#mysql-uroot-p

mysql>创建由“123456”标识的用户“从属”@“%”；

mysql>在*上授予复制从属服务器。*TO'slave'@'%

mysql>出口

4读取主服务器的二进制日志信息。

在复制主从数据之前，首先检查主服务器的二进制日志文件的基本信息。在设置从服务器时，需要使用主服务器的二进制文件名和当前日志位置。

[root@master~]#mysql-uroot-p

mysql>用读锁刷新表；

mysql>显示主机状态；

+-+-+-+-+-

|文件|位置|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|

+-+-+-+-+-

|jacob-log.000001|1226|||

+-+-+-+-+-

mysql>解锁表格；

5备份现有数据库，并将其导入从属服务器。

如果在使用数据复制之前，数据库系统中已经存在数据资源，那么使用mysqldump工具备份这些数据，然后在主服务器上使用该工具备份数据，然后就可以在从服务器上恢复数据了。当数据达到主从协议时，可以使用数据复制功能从这里自动同步。

[root@master~]#MySQLdump-all-databases-lock-all-tables>；/tmp/dbdump.sql

[root@master~]#scp/tmp/dbdump.SQL192.168.1.8:/tmp/

[root@slave~]#MySQL-uroot-p<；dbdump.sql

6配置从服务器连接到主服务器进行数据复制。

数据复制的关键 *** 作是配置从服务器连接到主服务器进行数据复制，需要配置从服务器建立网络连接的所有必要信息。使用更改主到语句来完成这项工作。MASTER_HOST指定主服务器的主机名或IP地址，MASTER_USER是在主服务器上创建的具有复制权限的帐户的名称，ASTER_PASSWORD是该帐户的密码，MASTER_LOG_FILE指定主服务器的二进制日志文件的名称，MASTER_LOG_POS是主服务器的二进制日志的当前记录位置。启动从服务器打开主从连接的从服务器功能，显示从服务器状态检查从服务器状态。

[root@slave~]#mysql-uroot-p

mysql>将母版更改为

->；主主机='192.168.1.7'，

->；MASTER_USER='slave'，

->；MASTER_PASSWORD='123456'，

->；MASTER_LOG_FILE='Jacob-LOG.000001'，

->；MASTER_LOG_POS=1226

mysql>启动从机；

mysql>显示从属状态\G；

7数据同步测试验证

在主数据库上创建数据库和表，然后转到从服务器查看它们是否同步。

[root@master~]#MySQL-uroot-p

mysql>创建数据库test2

mysql>使用test2

mysql>创建表t_table(

->；姓名字符(20)，

->；年龄int，

->；注意varchar(50))；

mysql>插入t_table值

->；('琳达'，23，'北京')，

->；('杰瑞'，33，'上海')，；

mysql>出口

[root@slave~]#mysql-uroot-p

mysql>select*fromtest2.t_table

mysql>出口

三。主从复制的问题和解决方案

mysql主从复制中的问题:

主库关闭后，数据可能会丢失。

从库只有一个sql线程，主库写压力大，复制很可能会延迟。

解决方法:

半同步复制——解决数据丢失问题

并行复制——解决从库复制延迟的问题。

复制错误处理:

常见:1062(主键冲突)，1032(记录不存在)

解决方法:

手工处理

跳过复制错误设置:setglobalSQL_slave_skip_counter=1