细细探究MySQL,MYSQL数据库导入大数据量sql文件失败的解决方案

细细探究MySQL Group Replicaiton — 配置维护故障处理全集,replicagroup

         本文主要描述 MySQL Group
Replication的简易原理、搭建过程以及故障维护管理内容。由于是新技术,未在生产环境使用过,本文均是虚拟机测试,可能存在考虑不周跟思路有误情况,欢迎交流指正。
  如果转载,请注明博文来源: www.cnblogs.com/xinysu/   ,版权归 博客园
苏家小萝卜
所有。望各位支持! 图片 1

MYSQL数据库导入大数据量sql文件失败的解决方案,mysqlsql

1.在讨论这个问题之前首先介绍一下什么是“大数据量sql文件”。

导出sql文件。图片 2选择数据库—–右击选择“转储SQL文件”—–选择”结构和数据”
 。保存文件db_mras.sql文件。

2.导入sql文件。在MYSQL中新建数据库db_mras。图片 3选择数据库—–右击选择“运行SQL文件”—–选择文件db_mras.sql,运行。

现在发现运行失败,提示错误“MySQL server has gone away”
。针对该问题提出如下解决方案:

提示该错误意思是:客户端与mysql的链接断开了,原因一般为sql运行时间过长或者sql文件太大。

排查问题原因:

(1)mysql服务宕了

运行命令:show  global status like  ‘uptime’;    如果uptime的值很大
表明最近mysql服务没有重启。
 若日志也没有相关信息,表明服务没有重启过,可以排除这个可能了。

(2)mysql链接超时

运行命令:show global variables  like ‘%timeout’;
查看运行结果中wait_timeout的值,一般为28800。代表mysql在误操作28800秒之后链接会关闭。

(3)mysql文件过大

运行命令:show global variables  like ‘max_allowed_packet’;  
查看运行结果max_allowed_packet的值 ,如果过小,需要调整大。

解决方法:

在mysql的my.ini文件末尾加如下几句话: wait_timeout=2880000;    
interactive_time=2880000;    max_allowed_packet=16M;  

其中max_allowed_packet代表控制其缓存区的最大长度。 wait_timeout代表无操作链接等待时间。

修改完以上参数之后重启mysql服务。

查看是否修改成功:运行命令:show global variables  like ‘%timeout’;    
 show global variables  like ‘max_allowed_packet’;

  小贴士:如果找不到my.ini文件可以运行命令:mysql –help|grep my.ini
来查找文件路径。  

注意:如果以上办法没有解决你的问题,你还需要查看你的mysql文件安装盘的空间是否足够。
                                    

 

1.在讨论这个问题之前首先介绍一下什么是“大数据量sql文件”。 导出sql文件。
选…

SQL入门之多表查询,SQL入门之

如果查询需要针对两个或者更多个表,则在需要涉及到表的连接操作(join)。

0.笛卡儿积

最简单的连接方式是直接在from子句中加入两个表,并且用join操作符隔开。形式为Table1
join Table2。下面是例子:

select e.fname, e.lname, d.name
from employee e join department d;

图片 4

产生108行数据,这是因为,对于单纯使用join进行的是笛卡儿积运算。对于两个表,分别由m和n个元组,进行笛卡尔积后将产生mXn个元组。但这个不是我们需要的结果,包含由很多不需要的数据。若要得到正确的结果,则需要内连接。

1.内连接

内连接根据相同属性值进行连接。只需要在上面的表中加上on和所需要相等的属性即可。

select e.fname, e.lname, d.name
from employee e join department d
on e.dept_id = d.dept_id;

图片 5

结果正是想要的。其实,在join前可加inner这是默认情况。但对于良好习惯,应该加上,以指明这是什么连接,这有助于代码阅读。对于on
e.dept_id = d.dept_id可以用using(dept_id)来代替。

以上结果也可以用SQL92的标准进行操作

 

select e.fname, e.lname, d.name
from employee e, department d
where e.dept_id = d.dept_id;

 

对于两种标准,选择在于自己,各有优缺点。

2.自连接

不仅在同一查询中多次包含同一个表,还可以对表自身进行连接。只需要取不同别名即可。

总结:对于连接操作方法挺好懂的,比较简单,对于连接3个以上,连接子查询,不等连接等类似。

 

如果查询需要针对两个或者更多个表,则在需要涉及到表的连接操作( join )。
0.笛卡儿积 最简单的连接方…

1 What’s Group Replication 

   
主从复制,一主多从,主库提供读写功能,从库提供写功能。当一个事务在master
提交成功时,会把binlog文件同步到从库服务器上落地为relay
log给slave端执行,这个过程主库是不考虑从库是否有接收到binlog文件,有可能出现这种情况,当主库commit一个事务后,数据库发生宕机,刚好它的binlog还没来得及传送到slave端,这个时候选任何一个slave端都会丢失这个事务,造成数据不一致情况。原理图如下:
 图片 6
      
为了避免出现主从数据不一致的情况,MySQL引入了半同步复制,添加多了一个从库反馈机制,这个有两种方式设置:

  • 主库执行完事务后,同步binlog给从库,从库ack反馈接收到binlog,主库提交commit,反馈给客户端,释放会话;
  • 主库执行完事务后,主库提交commit
    ,同步binlog给从库,从库ack反馈接收到binlog,反馈给客户端,释放会话;

图片 7

 

   
但是,但是,但是,问题来了,虽然满足了一主多从,读写分析,数据一致,但是,依旧有两个弊端

  • 写操作集中在MASTER服务器上;
  • MASTER宕机后,需要人为选择新主并重新给其他的slave端执行change
    master(可自行写第三方工具实现,但是mysql的复制就是没提供,所以也算是弊端)

   
于是乎,官方感应到民间怨气以及业界压力,于2016年12月12日正式发布了MySQL
Group
Replication,此处应有掌声 图片 8
    那么,MySQL Group Replication可以提供哪些功能呢?

  • 多主,在同一个group里边的所有实例,每一个实例可以执行写操作,也就是每个实例都执行Read-Write
    • 注意一点,多主情况下,当执行一个事务时,需要确保同个组内的每个实例都认可这个事务无冲突异常,才可以commit,如果设置的是单主,其他实例ReadOnly,则不需要进行上面的判断
    • 多主情况下,事务并发冲突问题就凸显出来了,如何避免呢?数据库内部有一个认证程序,当不同实例并发对同一行发起修改,在同个组内广播认可时,会出现并发冲突,那么会按照先执行的提交,后执行的回滚
  • 弹性,同个Group
    Replication中,节点的加入或者移除都是自动调整;如果新加入一个节点,该节点会自动从Group的其他节点同步数据,直到与其他节点一致;如果移除一个节点,那么剩下的实例会自动更新,不再向这个节点广播事务操作,当然,这里要注意,假设一个Group的节点有n个(max(n)=9,同个Group最多节点数为9),移除或者宕机的节点数应该小于等于
    floor((n-1)/2)
    ,注意是向下取整;如果是单主模式,宕机的是单主,则人为选择新主后,其他节点也会自动从新主同步数据。
  • 更高性能的同步机制

 图片 9

 

涉及知识点 故障探测( Failure Detection): Group
Replication中有一个故障检测机制,会提供某些节点可能死掉的信息,然后广播给同一个Group的各个节点,如果确定宕机,那么组内的节点就会与它隔离开来,该节点即无法同步其他节点的传送过来的binlog
events,也无法执行任何本地事务。
这里有个问题,故障探测中,假设N个节点,一个节点故障,是采用多数投票机制还是全部一致投票机制?
 

2 配置要求与限制

2.1 数据库要求

2.1.1 innodb引擎

    为什么需要使用innodb引擎呢?在MySQL Group
Replication中,事务以乐观形式执行,但是在提交时检查冲突,如果存在冲突,则会在某些实例上回滚事务,保持各个实例的数据一致性,那么,这就需要使用到
事务存储引擎,同事Innodb提供一些额外的功能,可以更好的管理和处理冲突,所以建议 业务使用表格使用inndb存储引擎,类似于系统表格mysql.user使用MyISAM引擎的表格,因为极少修改及添加,极少出现冲突情况。

**2.1.2 主键**

   
每个需要复制的表格都必须定义一个显式主键,注意跟隐式主键区分(使用Innodb引擎的表格,如果没有指定主键,默认选择第一个非空的唯一索引作为主键,如果没有,则自动创建一个6个字节的rowid隐式主键)。这个主键能在冲突发生时启动极其重要的作用,同时,能够有效提高relay
log的执行效率。

**2.1.3 隔离级别**

    官网建议使用READ COMMITTED级别,除非应用程序依赖于REPLEATABLE
READ,RC模式下没有GAP
LOCK,比较好支持Innodb本身的冲突检测机制何组复制的内部分布式检测机制一起协同工作。不支持SERIALIZABLE隔离级别。

**2.1.4 外键**

   
不建议使用级联外键,如果旧库本身有外键,业务上无法去除并且使用的是多主模式,那么,请配置 group_replication_enforce_update_everywhere_check
,强制检查每个组成员的级联检查,避免多主模式下执行级联操作造成的检测不到的冲突。

**2.1.5 IPv4网络,网络性能稳定延迟小带宽充足**

**2.1.6 自增跟步长**

   
这里需要注意到,搭建group的时候,每个实例中的auto_increment_increment跟auto_increment_offset的配置情况。

  • auto_increment_increment,在GROUP中范围在1-9(因为一个GROUP最多只能有9个组成员),GROUP中安装的时候,默认为7;
  • auto_increment_offset,增长步长,GROUP安装过程,是等于@@server_id的,但是注意有个规则是,
    auto_increment_offset > auto_increment_increment的时候,则是忽略
    auto_increment_offset的设置,第一个insert的从1开始,组内其他成员的初始值按照插入顺序
    1+n*组员个数,若GROUP有3个成员,A,B,C,serverid分别为2243310,2243320,3423340,A先insert,C再insert,B最后insert,则初始值
    A是1,B是9,C是6 (测试结论,未找到实际说明文档)

1 mysql> show global variables like 'auto_inc%';
2 +--------------------------+---------+
3 | Variable_name            | Value   |
4 +--------------------------+---------+
5 | auto_increment_increment | 7       |
6 | auto_increment_offset    | 2143340 |
7 +--------------------------+---------+
8 2 rows in set (0.00 sec)

 

2.2 安装mysql_replication引擎前提

  • master info and relay log info repositories
    • master_info_repository
      • set global master_info_repository =’table’;
    • relay_log_info_repository
      • set global relay_log_info_repository=‘table’;
    • 如果不设置会报错,报错信息如下
      • [ERROR] For the creation of replication channels the
        master info and relay log info repositories must be set to
        TABLE
  • binlog_checksum
    • binlog的校验方式应该设置为none
    • 如果不设置,报错性能如下
    • [ERROR] Plugin group_replication reported: ‘binlog_checksum
      should be NONE for Group Replication’

2.3 其他参数要求

  • binary log设置
    • 需要启动记录binary log,任何复制都需要使用到二进制内容
    • 在配置文件中添加 log-bin = [binlog存储路径及命名方式]
    • 例子: log-bin = /data/mysql/mysql3310/logs/bin_log
  • log-slave-updates设置
    • 默认情况下,主库同步到从库执行的内容,是不产生binlog日志的,一般开启该参数是为了满足
      多级复制,比如
      A->B->C(A是B的主库,B是C的主库),那么这个时候B就需要开启这个参数记录从A同步到B上面的所有操作到binary
      log中,这样才能完整的同步到C上。
    • 而在MGR中,组中的server需要记录从组接收和应用的所有事务,因为恢复的时候,是依赖域各个组的二进制日志内容的。
    • 那么这个时候,可能就有个疑问,在多主模式下,假设实例A ,B ,
      C三台中,每个实例修改的内容都记录到binary
      log中,再同步给其他组成员,那在B上执行事务 tranb :
      update了一行数据,tranb提交后同步到
      A跟C,各自执行后,由于启动了log-slave-updates设置,A跟C也生成了binary
      log,那么这些日志如果再同步回B,再执行一遍,不就可能出现问题了吗?实际上这个担忧是多余的,在MGR中,启动了GTID模式,会检查GTID
      EXCUTED集合,如果是已经执行的,则不会再次执行。
  • binary log格式
    • MGR依赖以及与行复制格式
    • binlog_format=row
  • GTID模式启动
    • 组复制使用全局事务标识符来记录哪些事务已在所有server实例上提交,从而判断哪些是已提交事务哪些是冲突事务,避免重复执行及数据不一致
    • gtid_mode=ON
  • transaction_write_set_extraction
    • 这个神奇的参数5.7.6版本引入,用于定义一个记录事务的算法,这个算法使用hash标识来记录事务。如果使用MGR,那么这个hash值需要用于分布式冲突检测何处理,在64位的系统,官网建议设置该参数使用 XXHASH64
      算法。
    • transaction_write_set_extraction =’XXHASH64′
    • 官网解释:Defines the algorithm used to generate a hash
      identifying the writes associated with a transaction. If you are
      using Group Replication, the hash value is used for distributed
      conflict detection and handling. On 64-bit systems running Group
      Replication, we recommend setting this to XXHASH64 in order to
      avoid unnecessary hash collisions which result in certification
      failures and the roll back of user transactions

3 搭建Mysql Group Replication

本次搭建采用3个实例,两个服务器,同一个网段,MGR的参数配置在配置文件中添加。

  • 注意通讯端口号的配置,它用于组成员之间的通讯使用
  • 请确定当前MySQL版本为5.7.17或者之后版本
  • 每个实例的serverid必须是唯一标识,建议使用ip末端+端口描述

基础信息如下:  

实例名 A B C
IP 192.168.9.242 192.168.9.242 192.168.9.244
实例端口号 3310 3320 3340
Server-ID 2423310 2423320 2443340
通讯端口号 24201 24202 24404
MySQL Versoin 5.7.17 5.7.17 5.7.17
MGR参数配置方式 修改配置文件 修改配置文件 修改配置文件

 

3.1 单主模式(group_replication_single_primary_mode =ON

3.1.1 主机名修改

   
为了方便后续管理维护以及一些不必要的错误,强烈建议修改主机名,尤其是当同个GROUP里边的SERVER主机名都是一样的情况下,由于本人在虚拟机中测试,虚拟机的主机名都是一样的,导致后续出现了部分问题,建议修改。
注意在两台SERVER上都修改哈!

 1 #查看当前主机名
 2 hostname
 3 
 4 #修改主机名
 5 hostname sutest242
 6 
 7 #进入vim /etc/hosts 
 8 #添加记录,不要修改默认的 127.0.0.1跟::1的记录,其他的系统服务会使用到的
 9 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
10 ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
11 192.168.9.242 sutest242
12 192.168.9.244 sutest244

配置后检查如下:
 图片 10

3.1.2 设置环境变量

关于GTID及日志信息记录相关参数(这部分的参数设置含义可以查看
第二部分:配置要求与限制 gtid_mode=on
enforce-gtid-consistency=on binlog_gtid_simple_recovery=1
log-slave-updates=1 binlog_checksum=NONE master_info_repository=TABLE
relay_log_info_repository=TABLE   关于MGR相关参数说明
transaction_write_set_extraction #记录事务的算法
group_replication_start_on_boot #是否随服务器启动而自动启动组复制
group_replication_bootstrap_group
#引导组成员的组,这个用于第一次搭建MGR跟重新搭建MGR的时候使用
group_replication_group_name
 #此GROUP的名字,必须是一个有效的UUID,以此来区分整个内网里边的各个不的GROUP
group_replication_local_address #本地的IP地址字符串,host:port
group_replication_group_seeds
 #需要接受本实例的信息服务器IP地址字符串
group_replication_single_primary_mode
#是否启动单主模式,如果启动,则本实例是主库,提供读写,其他实例仅提供读

group_replication_enforce_update_everywhere_checks
#多主模式下,强制检查每一个实例是否允许该操作   关于MGR相关参数配置

 1 #动态配置:
 2 set global transaction_write_set_extraction='XXHASH64';
 3 set global group_replication_start_on_boot=OFF;
 4 set global group_replication_bootstrap_group = OFF ;
 5 set global group_replication_group_name= '9ac06b4e-13aa-11e7-a62e-5254004347f9'; #某个UUID
 6 set global group_replication_local_address='192.168.9.242:24201';
 7 set global group_replication_group_seeds ='192.168.9.242:24201,192.168.9.242:24202,192.168.9.242:24401';
 8 set global group_replication_ip_whitelist = '127.0.0.1/8,192.168.9.0/24';
 9 set global group_replication_single_primary_mode=True;
10 set global group_replication_enforce_update_everywhere_checks=False;
11  
12 #cnf文件配置:
13 server-id=12001
14 transaction_write_set_extraction = XXHASH64
15 loose-group_replication_group_name = '9ac06b4e-13aa-11e7-a62e-5254004347f9'
16 loose-group_replication_ip_whitelist = '127.0.0.1/8,192.168.9.0/24'
17 loose-group_replication_start_on_boot = OFF
18 loose-group_replication_local_address = '192.168.9.242:24201'
19 loose-group_replication_group_seeds = '192.168.9.242:24201,192.168.9.242:24202,192.168.9.242:24401'
20 loose-group_replication_bootstrap_group = OFF
21 loose-group_replication_single_primary_mode = true
22 loose-group_replication_enforce_update_everywhere_checks = false

      这一步这里采用配置文件添加的方式,添加成功后重启数据库服务。
 图片 11

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图