Mysql实现企业级数据库主从复制架构实战

背景

公司规模已经形成，用户数据已成为公司的核心命脉，一次老王一不小心把数据库文件删除，通过mysqldump备份策略恢复用了两个小时，在这两小时中，公司业务中断，损失100万，老王做出深刻反省，公司也因此对于数据库的性能和可靠性提出更高要求。要求对数据库进行改造，使其承载力进行提升，故障修复时间减少，有没有能实现的方案呢？

数据库常遇到的问题

一、性能问题

1、向上拓展 scale up ：针对单台服务器，提高服务器的硬件性能，比如：内存，cpu等，个体本身容易达到极限

2、向外拓展 scale out ：多台服务器形成集群，共同完成一件事情

二、可用性问题

1、数据库服务中断

2、误操作数据损坏

3、硬件故障

4、数据库升级测试遭遇bug

5、黑客攻击

数据库高可用技术说明

高可用架构对于互联网服务基本是标配，无论是应用服务还是数据库服务都需要做到高可用。虽然互联网服务号称7*24小时不间断服务，但多多少少有一些时候服务不可用，比如某些时候网页打不开，百度不能搜索或者无法发微博，发微信等。一般而言，衡量高可用做到什么程度可以通过一年内服务不可用时间作为参考，要做到3个9的可用性，一年内只能累计有8个小时不可服务，而如果要做到5个9的可用性，则一年内只能累计5分钟服务中断。所以虽说每个公司都说自己的服务是7*24不间断的，但实际上能做到5个9的屈指可数，甚至根本做不到，国内互联网巨头BAT(百度，阿里巴巴，腾讯)都有因为故障导致的停服问题。对于一个系统而言，可能包含很多模块，比如前端应用，缓存，数据库，搜索，消息队列等，每个模块都需要做到高可用，才能保证整个系统的高可用。对于数据库服务而言，高可用可能更复杂，对用户的服务可用，不仅仅是能访问，还需要有正确性保证，因此，对于实现数据库高可用，对互联网公司来说极其重要！

企业级数据库高可用架构图

Mysql主从架构技术说明

Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机（Master）的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

主从复制架构图

数据库复制特性

Mysql复制解决的问题

MySQL复制技术有以下一些特点：

(1) 数据分布 (Data distribution )

(2) 负载平衡(load balancing)

(3) 备份(Backups)

(4) 高可用性和容错性 High availabilityand failover

Mysql复制如何工作

Mysql的复制功能主要有3个步骤：

(1) 主服务器（master）将改变记录到二进制日志(binarylog)中（这些记录叫做二进制日志事件，binary log events）

(2) 从服务器（slave）将主服务器master的binary logevents拷贝到它的中继日志(relay log)

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

1、该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志，在事件写入二进制日志完成后，master通知存储引擎提交事务。此后可接收slave的请求

2、下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始在主节点上binlog dump process（二进制转存线程）。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

3、 SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

I/O线程：将master数据库二进制日志拉到slave数据库上，并将二进制日志写到中继日志，写完之后，他会睡眠并等待master数据库二进制日志更新，一旦更新，就会写入slave数据库的中继日志中

SQL线程：读取中继日志的事件，并在数据库中执行，写入到内存中，使slave数据库的数据与master数据库中的数据一致

Mysql实现企业级数据库主从复制架构实战

注意：slave数据库只能是可读的，不能是可写的，如果改变了slave数据库的数据，master不能从slave数据库上同步数据，导致主从数据库数据不一致。

实战演练

一、环境准备

centos系统服务器2台、一台用户做Mysql主服务器，一台用于做Mysql从服务器，都在同一个网段中，配置好yum源、防火墙关闭、各节点时钟服务同步、各节点之间可以通过主机名互相通信

二、准备步骤：

1、iptables -F && setenforce 清空防火墙策略，关闭selinux

2、拿两台服务器都使用yum方式安装Mysql服务，要求版本一致

3、分别启动两台服务器mysql服务，确保服务正常

三、实现步骤:

1、配置master主服务器

对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值

vim /etc/my.cnf

server-id=1 #配置server-id，让主服务器有唯一ID号（让从服务器知道他的主服务器是谁）

log-bin=mysql-bin #打开Mysql日志，日志格式为二进制

skip-name-resolve#关闭名称解析，（非必须）

然后重启数据库服务

systemctl restart mariadb

2．创建复制帐号

在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master

。进行复制操作的用户会授予REPLICATION SLAVE权限。（给从服务器授权，让他能从主服务器拷贝二进制日志）

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.10.%' IDENTIFIED BY 'magedu';

3．查看主服务器状态

在Master的数据库执行show master status，查看主服务器二进制日志状态

4、配置slave从服务器

对slave进行配置，打开中继日志，指定唯一的servr ID，设置只读权限。在配置文件加入如下值

vim /etc/my.cnf

server-id=2 #配置server-id，让从服务器有唯一ID号

relay_log = mysql-relay-bin #打开Mysql日志，日志格式为二进制

read_only = 1 #设置只读权限

log_bin = mysql-bin #开启从服务器二进制日志

log_slave_updates = 1 #使得更新的数据写进二进制日志中

然后重启数据库服务

systemctl restart mariadb

5．启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

CHANGE MASTER TO MASTER_HOST='192.168.10.190',

MASTER_USER='slave',

MASTER_PASSWORD='magedu',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=278;

执行start slave；# 启动复制线程。

6、查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看前复制状态：

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes #IO线程正常运行

Slave_SQL_Running: Yes #SQL线程正常运行

7.测试

理想的结果是在主服务器上添加的数据，在从服务器上也会同步

在主服务器上

在从服务器上

四、添加新slave服务器

假如master已经运行很久了，想对新安装的slave进行数据同步，甚至它没有master的数据。

此时，有几种方法可以使slave从另一个服务开始，例如，从master拷贝数据，从另一个slave克隆，从最近的备份开始一个slave。为了加快Slave与master同步，可用以下方式先进行数据同步：

(1)master的某个时刻的数据快照；

(2)数据库的备份数据

(3)master的二进制日志文件。

实现主从从架构

也可以搭建主从从架构，让从服务器之间进行复制

就是在从服务器也开启二进制日志，然后从的从I/O线程再将从的二进制日志给拷贝过来写入到自己的relay log中，然后sql线程再读取relay log中的事件，在数据库中执行，写入到内存中。

Mysql复制过滤器

复制过滤器：

仅复制有限一个或几个数据库相关的数据，而非所有；由复制过滤器进行；

有两种实现思路：

(1) 主服务器

主服务器仅向二进制日志中记录有关特定数据库相关的写操作；

binlog_ignore_db=

(2) 从服务器

从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；

Replicate_Do_DB=

Replicate_Ignore_DB=

企业常见数据库架构

一、单一master和多slave

在实际应用场景中，MySQL复制90%以上都是一个Master复制到一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只要Master和Slave的压力不是太大（尤其是Slave端压力）的话，异步复制的延时一般都很少很少。尤其是自从Slave端的复制方式改成两个线程处理之后，更是减小了Slave端的延时问题。而带来的效益是，对于数据实时性要求不是特别高的应用，只需要通过廉价的pcserver来扩展Slave的数量，将读压力分散到多台Slave的机器上面，即可通过分散单台数据库服务器的读压力来解决数据库端的读性能瓶颈，毕竟在大多数数据库应用系统中的读压力还是要比写压力大很多。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。

单一master和多slave架构图

(1) 不同的slave扮演不同的作用(例如使用不同的索引，或者不同的存储引擎)；

(2) 用一个slave作为备用master，只进行复制；#主服务器挂了之后，可在从服务器执行

1> 在备机上执行STOP SLAVE 和RESET MASTER

2> 查看show slave status \G;

3> 然后修改应用的连接地址。

(3) 用一个远程的slave，用于灾难恢复；

二、互为主从Master-Master(Master-Master in Active-Active Mode)

Master-Master复制的两台服务器，既是master，又是另一台服务器的slave。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

即：在两台服务器上既执行master的操作又执行slave的操作（注意：两台数据库都必须是可写的）

互为主从复制过程

互为主从：两个节点各自都要开启binlog和relay log；

1、数据不一致；

2、自动增长id；

什么是自增长ID？

对于某些唯一性的字段，可以通过设置自增长ID来实现,自增长ID的数据，代表这个表中存在一条唯一的记录；而自增长id是肯定不会重复的；

创建表，设置ID为自增长

create table userInfo (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);

两边插入数据看数据增长

insert into userInfo(name) value('xiao'),('da'),('lao');

定义一个节点使用奇数id

auto_increment_increment=2 #表示自增长字段每次递增的量

auto_increment_offset=1 #表示自增长字段从那个数开始

另一个节点使用偶数id

auto_increment_increment=2

auto_increment_offset=2

配置：

1、server_id必须要使用不同值；

2、均启用binlog和relay log； read only = 0(因为互为主从，所以必须是可写的)

3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；

服务启动后执行如下两步：

4、都授权有复制权限的用户账号；

5、各把对方指定为主节点；

实验：数据库互为主从复制步骤

1.修改mysql配置文件

一台数据库服务器上

vim /etc/my.cnf

server-id = 1

log_bin = mysql_bin

relay_log = relay-log

skip-name-resolve = on

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=1

另一台数据库服务器上

vim /etc/my.cnf

server-id = 2

relay_log = relay-log

log_bin = mysql-log

skip-name-resolve = on

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=2

修改完配置文件后，重启数据库服务

systemctl restart mariadb

2．创建复制帐号

分别在两台数据库服务器上创建复制账号

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.10.%' IDENTIFIED BY 'magedu';

3．启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

CHANGE MASTER TO MASTER_HOST='192.168.10.190',

MASTER_USER='slave',

MASTER_PASSWORD='magedu',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=278;

执行start slave；# 启动复制线程。

另一台数据库服务器也是如此

4、查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看前复制态：

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes #IO线程正常运行

Slave_SQL_Running: Yes #SQL线程正常运行

两台数据库服务器都显示如上结果就ok

5.创建表，设置ID为自增长,两边插入数据看数据增长

在一台数据库服务器上

create database dnf;

create table userinfo (id int PRIMARY KEY AUTO_INCREMENT,name varchar(20) NOT NULL);

insert into userinfo (name) values('ni'),('wo'),('ta');

然后查看表，因为是自增长id，从1开始，步长为2，所以添加的数据id为1,3,5

然后在另一台数据库服务器插入数据，因为是自增长id，从2开始，步长为2，所以新添加的数据id为6,8,10

排错：当配置文件中配置中继日志格式不小心配置错了，或者让slave连接master，执行sql语句不小心写错了，都有可能导致start slave；报错，此时可以show slave status\G;会出现一大串信息，里面会提示错误。找到错误以后，重置slave，reset slave;重新设置，然后再start slave；

注意：mysql的错误日志非常重要，可以提供错误信息，从而找到错误原因。

互为主从容易导致数据不一致，此时我们可以用两个实例来互为主从

mysql优化：

1.可以用数据缓存，常见的memcache

2.数据库本身有很多缓存机制，可使用对应的缓存策略

3.对数据来说，竟可能使用索引

4.对请求而言，可以实现读写分离，对读请求负载均衡

5.对大数据库或者表，可根据业务逻辑进行分库分表

6.多有的优化，尽可能网内存中存放

半同步复制

异步复制（Asynchronous replication）

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整

全同步复制（Fully synchronous replication）

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。需要有超时时间。

半同步复制（Semisynchronous replication）

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

半同步复制

支持多种插件：/usr/lib64/mysql/plugins/

需要安装方可使用：

mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';

半同步复制：

semisync_master.so

semisync_slave.so

主节点：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

+------------------------------------+-------+

| Variable_name | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

+------------------------------------+-------+

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=ON/1;

从节点：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

MariaDB [mydb]> STOP SLAVE IO_THREAD;

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_slave_enabled = ON ;

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

MariaDB [mydb]> START SLAVE IO_THREAD;

可查看从库错误日志观察是否生效

master错误日志

slave错误日志

posted @ 2017-12-18 19:47 梦梦萌阅读(289) 评论(0) 编辑收藏举报

刷新页面返回顶部