MySQL6

MySQL数据库6

1. 集群概述

  • 性能达到瓶颈的解决方案

    1. Scale Up
      向上扩展能力,如增加更好的硬件固态磁盘,加大内存等,成本高,效果不显著
    2. Scale Out
      向外扩展,例如建立更多的服务器
  • MySQL扩展采用的方式读写分离
    将数据库的访问拆分成两种访问,读(select)和写(增删改),通过调度器,将用户的不同请求分别发送给读服务器或者写服务器,在写服务器增加数据时,需要使用主从复制机制来同步数据到读服务器

  • 主从复制特征

    1. 每个节点都有相同的数据集
    2. 向外扩展
    3. 使用二进制日志功能实现主从复制
    4. 单向
  • 主从复制的功用

    1. 负载均衡读
      在主从复制的基础上,配合读写分离,可以由主节点提供写服务,由从节点提供读服务,分担服务器负载;尤其是在写少读多的场景下,通过多个从节点分担读负载,可以大大提高Redis服务器的并发量
    2. 数据冗余
      可以防止物理上的损坏,如果主服务器数据删除,从服务器数据也会丢失,所以还要备份
    3. 高可用和故障切换
      主服务器损坏,更改从服务器设置替换为主,从服务器损坏,调整调度器策略,跳过损坏的从服务器
    4. MySQL升级测试
      类似灰度发布,升级集群中的一部分服务器
  • 读写分离应用:

    1. mysql-proxy:Oracle公司开源产品
      https://downloads.mysql.com/archives/proxy/
    2. Atlas:Qihoo(360公司),基于mysql-proxy的二次修改
      https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
    3. dbproxy:美团
      https://github.com/Meituan-Dianping/DBProxy
    4. Cetus:网易乐得
      https://github.com/Lede-Inc/cetus
    5. Amoeba:
      https://sourceforge.net/projects/amoeba/
  • 主从复制原理
    在这里插入图片描述

    1. 主服务器
      要求必须启用二进制日志服务,执行写操作(例如update),只要事务提交,就会写入二进制日志(Bin Log)里,然后通过启用的Slave服务器线程,通过网络将二进制日志读出来,传送给从服务器的线程io Thread
    2. 从服务器
      1. io Thread线程接收到主服务器传送的二进制文件,放入relay log中
      2. SQL Thread线程读出relay log中的二进制文件,写入数据库中,完成同步。
    3. 主从复制线程作用
      1. 主节点(dump Thread)
        为每个Slave的I/O Thread启动一个dump线程,用于向其发送binary log events
      2. 从节点:
        1. I/O Thread
          向Master请求二进制日志事件,并保存于中继日志中
        2. SQL Thread
          从中继日志中读取日志事件,在本地完成重放
    4. 从服务器跟复制功能相关的文件:
      1. master.info
        用于保存slave连接至master时的相关信息,例如账号、密码、服务器地址等
      2. relay-log.info
        保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系
    5. 缺点
      主服务器写操作可以实现并行操作,因为有事务功能可以隔离操作,二进制日志生成后推送给从服务器时,是串行的,由多车道变为单车道,当用户访问量很大时,就会产生延迟很大的问题。
  • MySQL垂直分区
    在这里插入图片描述

    1. 工作原理
      如图所示,服务器中有三部分数据库Part1、Part2、Part3,当服务器负载过大时,可以将其拆分放到三个服务器中来分别管理
    2. 缺点
      在同一个服务器中可以使用多表查询,拆分存放不支持此功能,需要配合开发进行
  • MySQL水平分片(Sharding)
    在这里插入图片描述

    1. 工作原理
      将数据按照一定的逻辑切成小片,以用户为例,有100万用户信息,可以将用户按奇偶数切分,奇数号的用户放在Shard1中,偶数号的用户放在Shard2中。也需要结合业务来分
    2. 缺点
      需要增加分片调度器,来判断用户请求,找打相应服务器
  • 主从复制架构:

    1. Master/Slave(主从)
    2. Master/Master(主主)
      都可以写入,容易发生数据冲突,当服务器同步延迟,不能及时发现冲突,用于对数据要求不高情况
    3. 一主多从
      从服务器还可以再有从服务器
    4. 一从多主:
      适用于多个不同数据库,版本较高的数据库支持,将从服务器分为多个部分,分别存放主服务器数据

2. MySQL主从复制实现

  • 主从配置官网资料
    https://mariadb.com/kb/en/library/setting-up-replication/
    https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

  • 主节点配置

    1. mariadb配置文件,通常为/etc/my.cnf,在[mysqld]下添加
      1. 必须项
        log_bin:启用二进制日志,在mariadb配置文件,[mysqld]内添加项
        server_id=#:为当前节点设置一个全局惟一的ID号,判断数据来源的标识,[mysqld]内添加项
      2. 可选项
        log-basename=master:设置datadir中日志名称
        sync_binlog=1: 每次写后立即同步二进制日志到磁盘,性能差
        innodb_flush_log_at_trx_commit=1: 每次事务提交立即同步日志写磁盘
        innodb_support_xa=ON: 默认值,分布式事务MariaDB10.3.0废除
        sync_master_info=# :#次事件后master.info同步到磁盘
    2. 创建有复制权限的用户账号
      1. 语法
        GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
        
        • 1
      2. 选项描述
        REPLICATION SLAVE:给予从服务器的权限,只可以同步复制二进制日志
        repluser:用户账号命名
        HOST:从服务器IP或网段
        IDENTIFIED BY :设置口令关键字
  • 从节点配置:

    1. 配置文件

      1. 必须项,[mysqld]内
        server_id=#:为当前节点设置一个全局惟的ID号
      2. 可选项
        relay_log=relay-log:中继日志的文件路径,默认值文件名为hostname-relay-bin
        relay_log_index=relay-log.index:中继日志索引文件路径,默认值hostname-relay-bin.index
        skip_slave_start=ON:不自动启动slave
        sync_relay_log=# :#次写后同步relay log到磁盘
        sync_relay_log_info=#:#次事务后同步relay-log.info到磁盘
    2. 使用有复制权限的用户账号连接至主服务器,并启动复制线程

      1. 从服务器复制同步设置
        mysql> CHANGE MASTER TO             <==同步关键字
        MASTER_HOST='host',    		        <==主服务器名
        MASTER_USER='repluser',    		    <==主服务器授权的账号
        MASTER_PASSWORD='replpass', ,       <==授权账号的密码
        MASTER_LOG_FILE='mysql-bin.xxxxx',  <==数据同步开始位置来自哪个二进制日志
        MASTER_LOG_POS=#;  					<==二进制日志的开始位置			
        
        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
      2. 启动从服务器复制线程
        mysql> START SLAVE [IO_THREAD|SQL_THREAD];
        
        • 1
  • 复制架构中应该注意的问题

    1. 限制从服务器为只读
      在从服务器配置文件[mysqld]中设置read_only=ON
      注意:此限制对拥有SUPER权限的用户均无效,例如root账号
    2. 阻止所有用户, 包括主服务器复制
      mysql> FLUSH TABLES WITH READ LOCK;
    3. 从新设置从服务器同步,清除master.inforelay-log.info,relay log 等信息
      1. 先停止从服务器同步线程
        STOP SLAVE
      2. 清空同步数据
        RESET SLAVE ALL
    4. 从服务器忽略几个主服务器的复制事件,
      1. 忽略用到的global变量
        sql_slave_skip_counter = N
      2. 示例
        在从服务器上建立一条teachers表上id为13的记录
        MariaDB [hellodb]> INSERT teachers VALUES(13,'k',26,'M');
        
        • 1
        主服务器在此之后推送来一条相同Id的记录,就会在从服务器中出现如下报错
        '查看从服务器同步信息'
        MariaDB [hellodb]> SHOW SLAVE STATUS\G
        '出现如下错误提示'
        Last_SQL_Error: Error 'Duplicate entry '13' for key 'PRIMARY'' on query. Default database: 'hellodb'. Query: 'INSERT teachers VALUES(13,'k',30,'M')'   
        
        • 1
        • 2
        • 3
        • 4
        之后主服务器推送的所有记录,都将失败,可以使用sql_slave_skip_counter = N来忽略这种错误,解决此堵塞现象
        1. '在堵塞的从服务器上,先停止线程'
        MariaDB [hellodb]> STOP SLAVE;
        2. '执行全局变量sql_slave_skip_counter,通过第2步得知只有1个错误,所以值为1'
        MariaDB [hellodb]> SET GLOBAL sql_slave_skip_counter=1;
        Query OK, 0 rows affected (0.01 sec)
        3. '启动线程,恢复主从复制'
        MariaDB [hellodb]> START SLAVE;
        
        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
    5. 如何保证主从复制的事务安全
      参看https://mariadb.com/kb/en/library/server-system-variables/

3. 主从复制实现

理论上应该保持主从服务器版本一致,避免出现未知错误

  • 主从复制

    1. 安装mariadb服务,设置主服务器配置文件
      '修改配置文件'
      [root@hai7-6 ~]$vim /etc/my.cnf
      [mysqld]
      server_id=1            <==设置主服务器server_id
      innodb_file_per_table  <==将表数据文件单独存放
      skip_name_resolve      <==不允许反向解析为主机名
      log_bin                <==启动二进制
      '启动服务'
      [root@hai7-6 ~]$systemctl restart mariadb
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
    2. 授权从服务器
      记录二进制日志文件位置,将来复制数据到从服务器,是将授权账号信息一起复制过去,还是复制授权账号后的信息
      '授权账户为repluser在网段192.168.50.%,密码为centos,可以复制数据'
      MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%'  IDENTIFIED BY 'centos';
      '授权账号后二进制日志位置'
      MariaDB [(none)]> show master status;
      | mysql_bin.000007 |      401 |
      
      • 1
      • 2
      • 3
      • 4
      • 5
    3. 配置从服务器,从服务器这里没有设下级从服务器,暂时不需要加二进制日志功能
      [root@hai7-8 ~]$vim /etc/my.cnf
      [mysqld]
      server_id=2      <==id号只要唯一就可以
      innodb_file_per_table
      skip_name_resolve
      
      • 1
      • 2
      • 3
      • 4
      • 5
    4. 在主服务器导入一些数据,便于查看主从复制功能是否启用
      [root@hai7-6 ~]$mysql < hellodb_innodb.sql
      '二进制日志大小为'
      MariaDB [(none)]> show master status;
      | mysql_bin.000007 |     7811 |      
      
      • 1
      • 2
      • 3
      • 4
    5. 在从服务器上执行复制命令,这个命令需要记住CHANGE关键字,用help补齐剩下部分
      MariaDB [(none)]> help change   <==帮助关键字
      topics:
      ALTER TABLE
      CHANGE MASTER TO        <==得到CHANGE MASTER TO
      '再次help关键字`CHANGE MASTER TO`,得到修改模板,拿来用,修改为我们需要的配置,执行'
      MariaDB [(none)]> CHANGE MASTER TO
          ->   MASTER_HOST='192.168.50.107',
          ->   MASTER_USER='repluser',
          ->   MASTER_PASSWORD='centos',
          ->   MASTER_PORT=3306,
          ->   MASTER_LOG_FILE='mysql_bin.000007',
          ->   MASTER_LOG_POS=245;
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
    6. 同步命令执行后查看从服务器同步状态
      MariaDB [(none)]> SHOW SLAVE STATUS\G
      			  Master_Log_File: mysql_bin.000007   <==从这个二进制日志复制     
                Read_Master_Log_Pos: 245       	  	  <==从以上日志的什么位置开始复制       
               Relay_Master_Log_File: mysql_bin.000007  <==将来生成的文件
                     Relay_Log_File: mariadb-relay-bin.000001  <==中继日志
                      Relay_Log_Pos: 4           		  <==中继位置
                   Slave_IO_Running: No         		  <==IO线程启动状态  
                  Slave_SQL_Running: No   	  		  <==SQL线程启动状态
       	 	Seconds_Behind_Master: NULL	      		  <==和主服务器复制延迟有多久
       	 	        Exec_Master_Log_Pos: 245	      <==写入从服务器时,当前写入的主服务器的位置
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
    7. 启动从服务器同步复制线程Slave_IO_Running和Slave_SQL_Running
      MariaDB [(none)]> START SLAVE;
      
      • 1
    8. 测试是否可以同步数据,修改hellodb数据库的表teachers
      '增加一条记录'
      MariaDB [hellodb]> insert teachers values(5,'a',20,'F');
      
      • 1
      • 2
    9. 在从服务器上查看,是否已经同步
      MariaDB [hellodb]> select * from teachers;
      |   5 | a             |  20 | F      |      <==同步成功
      
      • 1
      • 2
  • 已经有主从服务器,额外添加一个从服务器

    1. 在准备新增的从服务B上修改配置文件
      [root@hai7-7 data]$vim /etc/my.cnf
      [mysqld]
      server_id=3
      innodb_file_per_table
      skip_name_resolve
      
      • 1
      • 2
      • 3
      • 4
      • 5
    2. 将主服务器数据做完全备份,并传送给从服务器B
      '完全备份主服务器数据'
      [root@hai7-6 data]$mysqldump -A -F --single-transaction --master-data=1 > all.sql
      '传送给从服务器B'
      [root@hai7-6 data]$scp all.sql 192.168.50.115:/data
      
      • 1
      • 2
      • 3
      • 4
    3. 完全备份时--master-data=1选项,会自动在备份文件中加入CHANGE MASTER TO关键词,修改完全备份文件,在导入时备份文件时,使其可以直接执行从服务器同步复制指令
      `修改前完全备份--master-data=1选项,添加的行`
      [root@hai7-7 data]$vim all.sql
      CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000008', MASTER_LOG_POS=245;
      `修改为从服务器执行的CHANGE MASTER TO复制指令`
      [root@hai7-7 data]$vim all.sql
      CHANGE MASTER TO 
      MASTER_HOST='192.168.50.107',MASTER_USER='repluser',
      MASTER_PASSWORD='centos',MASTER_PORT=3306,
      MASTER_LOG_FILE='mysql_bin.000008', MASTER_LOG_POS=245;
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
    4. 修改完成后,将完全备份文件导入从服务器B的数据库中,查看从服务器配置情况
      [root@hai7-7 data]$mysql < all.sql
      MariaDB [(none)]> show slave status\G
                     Slave_IO_State: 
                        Master_Host: 192.168.50.107    <==已自动执行CHANGE MASTER TO
                        Master_User: repluser
      
      • 1
      • 2
      • 3
      • 4
      • 5
    5. 启动从服务器线程,在主服务器添加数据,测试主从复制功能
      1. '启动从服务器线程'
      MariaDB [(none)]> START SLAVE;
      2. '在主服务器增加数据'
      MariaDB [hellodb]> INSERT teachers VALUES(7,'c',30,'F');
      3. '在从服务器查看是否自动同步'
      MariaDB [hellodb]> SELECT * FROM teachers;
      |   7 | c             |  30 | F      |    <==同步成功
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
  • 如果主服务器A宕机,在从服务器(B、C)中,选一个来充当主服务器

    1. 选择合适的从服务器作为提升机,参考文件master.info二进制日志保存位置,判断那台从服务器数据较新,就拿那台当做提升机
      '查看从服务器文件master.info,可以得到二进制日志信息'
      [root@hai7-8 ~]$cat /var/lib/mysql/master.info 
      18
      mysql_bin.000008
      445
      
      • 1
      • 2
      • 3
      • 4
      • 5
    2. 将准备提升的从服务器C的线程停止
      '停止线程'
      MariaDB [(none)]> STOP SLAVE;
      '查看线程状态'
      MariaDB [(none)]> show slave status\G
                   Slave_IO_Running: No     <==已停止
                  Slave_SQL_Running: No
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
    3. 清理从服务器其他配置信息,比如家目录下的master.inforelay-log.info
      '清理从服务器信息命令'
      MariaDB [(none)]> RESET SLAVE ALL; 
      '执行清理命令后,从服务器信息将清空'
      MariaDB [(none)]> show slave status\G
      Empty set (0.00 sec)      <==信息被清空
      
      • 1
      • 2
      • 3
      • 4
      • 5
    4. 修改配置文件,打开二进制日志功能,清除read_only、中继日志等从服务器配置
      [root@hai7-8 ~]$vim /etc/my.cnf
      [mysqld]
      server_id=2     <==id只要唯一就可以
      log-bin         <==打开二进制日志功能
      
      • 1
      • 2
      • 3
      • 4
    5. 授权从服务器账号,如果从服务器已经同步过主服务器授权信息,这里可以跳过,如果从服务器B的数据与其他服务器不同,需要备份还原步骤,来同步数据
      '授权账户为repluser在网段192.168.50.%,密码为centos,可以复制数据'
      MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%'  IDENTIFIED BY 'centos';
      
      • 1
      • 2
    6. 停止从服务器B线程,清空从服务信息
      MariaDB [(none)]> STOP SLAVE;
      MariaDB [(none)]> RESET SLAVE ALL;
      
      • 1
      • 2
    7. 重新配置从服务信息将主服务器地址指向C,启动线程
      1. '重新配置从服务器信息'
      MariaDB [(none)]> CHANGE MASTER TO 
          -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
          -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
          -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
      2. '启动线程'
      MariaDB [(none)]> START SLAVE;
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
    8. 在主服务器C上添加数据,查看是否可以主从复制
      '主服务器C上添加数据'
      MariaDB [hellodb]> INSERT teachers VALUES(9,'E',32,'f');
      '从服务器B上查看'
      MariaDB [hellodb]> select * from teachers;
      |   9 | E             |  32 | F      |
      
      • 1
      • 2
      • 3
      • 4
      • 5
  • 级联复制,给从服务器B再下设从服务器C
    主从复制依赖的是二进制日志文件,而从服务器接收主服务器的二进制文件通过中继日志执行写入磁盘,不会产生新的二进制日志(二进制日志产生是对数据库直接进行增删改),想要在从服务器生成二进制文件供级联使用,就需要在配置文件中加入选项log_slave_updates

    1. 主服务器为A,从服务器为B,从服务器的下设从服务器为C;在从服务器B上修改配置文件,开启二进制日志功能,重启服务,查看二进制日志位置

      [root@hai7-8 ~]$vim /etc/my.cnf
      [mysqld]
      server_id=3
      log_slave_updates     <==级联从服务器关键项,产生二进制日志
      log-bin               <==为下设的从服务器复制提供二进制日志文件
      read_only=ON
      MariaDB [(none)]> show master logs;
      +--------------------+-----------+
      | Log_name           | File_size |
      +--------------------+-----------+
      | mariadb-bin.000001 |       245 |
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
    2. 在C上设置从服务器设置,修改配置文件,设置唯一id,设只读设置等,然后进入mysql设置从服务器,将主机指向B

      '如果之前为从服务器需要执行清理操作'
      MariaDB [hellodb]> reset slave all;
      Query OK, 0 rows affected (0.00 sec)
      '重新配置从服务器设置'
      MariaDB [hellodb]>  CHANGE MASTER TO 
          -> MASTER_HOST='192.168.50.115',MASTER_USER='repluser',
          ->  MASTER_PASSWORD='centos',MASTER_PORT=3306,
          -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
      '启动线程'
      MariaDB [hellodb]> START SLAVE;
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10

4. 主主复制(互为主从)

  • 容易产生的问题
    数据同步有延迟,两主机可能修改的是同一个数据,容易冲突;因此慎用

  • 考虑要点:自动增长id

    1. 配置一个节点使用奇数id
      auto_increment_offset=1 开始点
      auto_increment_increment=2 增长幅度
    2. 另一个节点使用偶数id
      auto_increment_offset=2
      auto_increment_increment=2
  • 主主复制的配置步骤:

    1. 修改主机配置文件
      A主机
      [root@hai7-7 data]$vim /etc/my.cnf
      [mysqld]
      server_id=3
      log-bin
      auto_increment_offset=2       <==自增ip起始数为2
      auto_increment_increment=2    <==自增ip递增为2
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      B主机
      [root@hai7-7 data]$vim /etc/my.cnf
      [mysqld]
      server_id=2
      log-bin
      auto_increment_offset=1    <==修改ip起始数为1
      auto_increment_increment=2
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
    2. 创建用户
      A主机,B主机上也需要建账户,通过同步复制实现即可
      MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%' IDENTIFIED BY 'centos';
      
      • 1
    3. 达成同步复制功能
      B主机,实现复制功能,主服务器地址指向A(192.168.50.111)
      1. '先实现同步复制功能'
      MariaDB [(none)]> CHANGE MASTER TO 
          -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
          ->  MASTER_PASSWORD='centos',MASTER_PORT=3306,
          -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
      2. '启动同步复制线程'
      MariaDB [(none)]> START SLAVE;   
      3. '在A主机授权的账号信息也一起同步到了B主机'
      MariaDB [(none)]> SELECT user , host FROM mysql.user;
      | repluser | 192.168.50.% |
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      回到A主机,实现复制功能,主服务器地址指向B(192.168.50.115)
      MariaDB [(none)]> CHANGE MASTER TO 
          -> MASTER_HOST='192.168.50.115',MASTER_USER='repluser',
          -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
          -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
      Query OK, 0 rows affected (0.02 sec)
      '启动同步复制线程'
      MariaDB [(none)]> START SLAVE;
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
    4. 测试
      建立一个自动递增id的表,测试主主复制效果
      MariaDB [hellodb]> CREATE TABLE test (id int auto_increment primary key,name char(20));
      
      • 1
      在A主机添加两条name字段的记录,让id自动增长
      MariaDB [hellodb]> INSERT test (name)VALUES('a'),('b');
      MariaDB [hellodb]> select * from test;
      +----+------+
      | id | name |
      +----+------+
      |  1 | a    |    <==奇数式增长
      |  3 | b    |
      +----+------+
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      在B主机上添加两条name字段的记录,让id自动增长
      MariaDB [hellodb]> INSERT test (name)VALUES('e'),('f');
      MariaDB [hellodb]> select * from test;
      +----+------+
      | id | name |
      +----+------+
      |  1 | a    |
      |  3 | b    |
      |  4 | e    |     <==auto_increment只取最大值,不会插入,所以显示偶数为4
      |  6 | f    |
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9

5 半同步复制

默认情况下,MySQL的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库即结束,并不验证从库是否接收完毕。
由于MySQL的复制内在机制,可能会产生比较大的延迟,这意味着当主服务器或从服务器端发生故障时,有可能从服务器没有接收到主服务器发送过来的binlog日志,这就会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失;而同步方式效率又不高。
在这里插入图片描述

  • 半同步复制工作原理
    1. 用户发送写操作请求,调度器将指令转发给主数据库,主库数据发生改变后,不会马上将数据更改成功的结果发送给用户,而是先把binlog日志发送给从库,在众多从库中只要有一个同步成功,就会返回成功结果给用户 ;
    2. 如果长时间没有同步成功任何一个从库,超过超时时长(timeout)定义的时间后,也会返回成功结果给用户
  • 半同步复制配置:
    需要在主从服务器上都安装插件rpl_semi_sync_master,这个插件表现为库so文件
    在mariadb-server安装包中已经包含:
    主服务器插件文件/usr/lib64/mysql/plugin/semisync_master.so
    从服务器插件文件/usr/lib64/mysql/plugin/semisync_slave.so
    1. 主服务器
      1. 安装插件语法:
        mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
        
        • 1
      2. 选项描述
        INSTALL PLUGIN:安装插件命令
        rpl_semi_sync_master:插件类型
        SONAME:关键字,后面跟插件文件名
      3. 启用半同步功能
        mysql>SET GLOBAL rpl_semi_sync_master_enabled=1;    
        
        • 1
      4. 设置超时时长,单位以毫秒为单位
        mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1000;
        
        • 1
    2. 从服务器配置:
      1. 安装插件语法
        mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
        
        • 1
      2. 启动半同步功能
        mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
        
        • 1
    3. 查看半同步信息
      1. 显示变量设定值
        mysql>SHOW GLOBAL VARIABLES LIKE ‘%semi%’;
      2. 显示服务器当前状态
        mysql>SHOW GLOBAL STATUS LIKE '%semi%‘;
  • 实现步骤,基于主从复制实现
    1. 配置主服务器配置文件,启动服务,授权账号
      1. '修改主服务器配置文件'
      [root@hai7-8 ~]$vim /etc/my.cnf
      [mysqld]
      server_id=2
      log-bin
      2. '重启服务'
      root@hai7-8 ~]#systemctl start mariadb
      3. '授权账户'
      [root@hai7-8 ~]$mysql
      MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%' IDENTIFIED BY 'centos';
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
    2. 配置从服务器配置文件
      从服务器B
      1. '修改配置文件'
      [root@hai7-7 data]$vim /etc/my.cnf
      [mysqld]
      server_id=3
      2. '重启服务'
      [root@hai7-7 data]$systemctl start mariadb
      3. '实现从服务器同步复制'
      [root@hai7-7 data]$mysql
      MariaDB [(none)]> CHANGE MASTER TO 
          -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
          -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
          -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
      4. '启动同步线程'
      MariaDB [(none)]> START SLAVE;
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      从服务器C
      1. '修改配置文件'
      [root@hai7-6 ~]$vim /etc/my.cnf
      [mysqld]
      server_id=1
      2. '启动服务'
      [root@hai7-6 ~]$systemctl restart mariadb
      3. '实现从服务器同步复制功能'
      [root@hai7-6 ~]$mysql
      MariaDB [(none)]> CHANGE MASTER TO 
          -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
          -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
          -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
      4. '启动同步线程'
      MariaDB [(none)]> START SLAVE;
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
    3. 达成主从复制后开始安装插件
      主服务器上
      1. '查看系统自带插件'
      MariaDB [(none)]> SHOW PLUGINS;
      2. '安装半同步插件'
      MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
      3. '启动半同步插件,重启失效,生产环境要写入配置文件my.cnf'
      MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
      +--------------------------------------------+-------+
      | Variable_name                              | Value |
      +--------------------------------------------+-------+
      | Rpl_semi_sync_master_clients               | 1     |  <==半同步服务器个数
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      在客户端上
      1. '安装插件'
      MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
      2. '启动插件'
      MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
      3. '启动后,从服务器线程并没有价值此插件,所以需要重启从服务器线程'
      MariaDB [(none)]> stop slave;
      MariaDB [(none)]> start slave;
      4. '查看插件运行状态,值为ON说明设置半同步成功'
      MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Rpl_semi_sync_slave_status | ON    |
      +----------------------------+-------+
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
    4. 测试半同步是否达成
      1. '停掉从服务器同步线程,根据半同步原理可知,主服务无法同步数据,会在超过等待时长后返回成功'
      MariaDB [(none)]> STOP SLAVE;
      2. '在主服务器更改数据'
      MariaDB [hellodb]> INSERT teachers VALUES(6,'A',30,'M');
      Query OK, 1 row affected (10.00 sec)     <==等待10秒后返回成功结果
      
      • 1
      • 2
      • 3
      • 4
      • 5

6. 复制过滤器:

让从节点仅复制指定的数据库,或指定数据库的指定表

  • 两种实现方式:
    1. 服务器选项:主服务器仅向二进制日志中记录与特定数据库相关的事件
      注意:此项和binlog_format相关,基于二进制还原将无法实现;不建议使用
      参看:https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
      binlog-do-db = 数据库白名单列表,多个数据库需多行实现
      binlog-ignore-db = 数据库黑名单列表

    2. 从服务器SQL_THREAD在replay中继日志中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地
      问题:会造成网络及磁盘IO浪费

      1. replicate_do_db= 指定复制库的白名单
      2. replicate_ignore_db= 指定复制库黑名单
      3. replicate_do_table= 指定复制表的白名单
      4. replicate_ignore_table= 指定复制表的黑名单
      5. replicate_wild_do_table= foo%.bar% 支持通配符
      6. replicate_wild_ignore_table=
    3. 示例,在从服务器上在白名单中添加数据库hellodb,只能对进入库操作起作用,需要use hellodb进入数据库,改变数据后从服务器才能同步,hellodb.teacher写法会被过滤掉

      MariaDB [hellodb]> SET GLOBAL replicate_do_db=hellodb;
      
      • 1

7 MySQL复制加密

在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的,外网里访问数据或则复制,存在安全隐患。通过SSL/TLS加密的方式进行复制的方法,来进一步提高数据的安全性

  • 配置实现:
    参看:https://mariadb.com/kb/en/library/replication-with-secure-connections/

    1. 主服务器开启SSL:[mysqld] 加一行ssl
    2. 主服务器配置证书和私钥;并且创建一个要求必须使用SSL连接的复制账号
    3. 从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项
  • 配置示例

    1. 申请证书
      自搭建CA,生产CA秘钥cakey.pem
      openssl genrsa 2048 > cakey.pem
      
      • 1
      生成自签名证书
      openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
      
      • 1
      为主服务器A建立证书
      rsa表示算法1024位,有效期为100天 ,-nodes表示不加密 -keyout表示输出私钥文件为master.key,然后生成证书申请csr
      openssl req -newkey rsa:1024 -days 100 -nodes  -keyout master.key > master.csr
      
      • 1
      为主服务器颁发证书
      -CA表示ca的证书文件,-CAkey表示ca的私钥文件,-set_serial表示给证书设置一个序列号从01开始编码
      openssl x509 -req -in master.csr  -days 100 -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
      
      • 1
      从服务器B生成私钥,并生成证书申请
      openssl req -newkey rsa:1024 -days 100 -nodes  -keyout slave.key > slave.csr
      
      • 1
      为从服务器B颁发证书
      openssl x509 -req -in slave.csr -days 100 -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
      
      • 1
      将证书和私钥复制给主从服务器的对应目录下
      '传送ca证书、主服务器私钥、主服务器证书给主服务器'
      scp cacert.pem master.crt master.key 192.168.50.107:/etc/my.cnf.d/ssl/
      '传送ca证书、从服务器私钥、从服务器证书给从服务器'
      scp cacert.pem slave.crt slave.key 192.168.50.115:/etc/my.cnf.d/ssl/
      
      • 1
      • 2
      • 3
      • 4
    2. 配置主服务器
      1. '修改配置文件'
      [root@hai7-8 ssl]$vim /etc/my.cnf
      [mysqld]
      server_id=2
      log-bin
      ssl     <==主服务器启用加密功能
      ssl-ca=/etc/my.cnf.d/ssl/cacert.pem  <==ca证书
      ssl-cert=/etc/my.cnf.d/ssl/master.crt  <==主服务器证书
      ssl-key=/etc/my.cnf.d/ssl/master.key   <==主服务器私钥
      2. '重启服务'
      [root@hai7-8 ssl]$systemctl restart mariadb
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      进入数据库查看变量,判断是否配置成功
      [root@hai7-8 ssl]$mysql
      MariaDB [(none)]> show variables like '%ssl%';
      +---------------+------------------------------+
      | Variable_name | Value                        |
      +---------------+------------------------------+
      | have_openssl  | YES                          |    <==YES表示配置成功
      | have_ssl      | YES                          |
      
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      建立强制加密账号,强制加密关键词为require ssl
      MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO ssluser@'192.168.50.%' IDENTIFIED BY 'centos' require ssl;
      
      • 1
    3. 配置从服务器
      1. '清空同步信息,重新以加密方式同步'
      MariaDB [(none)]> STOP SLAVE;
      MariaDB [(none)]> RESET SLAVE ALL
      2. '重新配置加密同步'
      MariaDB [(none)]> CHANGE MASTER TO
          -> MASTER_HOST='192.168.50.111',
          -> MASTER_USER='ssluser',
          -> MASTER_PASSWORD='centos',
          -> MASTER_LOG_FILE='mariadb-bin.000002',
          -> MASTER_LOG_POS=411,
          -> MASTER_SSL=1,                       				  <=启用加密
          -> MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',    <==ca的证书
          -> MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',	  <==从服务器的证书
          -> MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';    <==从服务器的私钥
      Query OK, 0 rows affected (0.00 sec)
      3. '启动从服务器同步线程'
      MariaDB [(none)]> start slave;
      PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
      RESET MASTER
      RESET SLAVE
      SHOW MASTER STATUS
      SHOW BINLOG EVENTS
      SHOW BINARY LOGS
      SHOW SLAVE STATUS
      SHOW PROCESSLIST
      SHOW SLAVE STATUS
      Seconds_Behind_Master: 0
      percona-tools
      
工具 功能描述
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 故障转移(自动或手动)
masterha_conf_host 添加或删除配置的server信息
  1. Node工具包:
    这些工具通常由MHA Manager的脚本触发,无需人为操作)主要包括以下几个工具
工具 功能描述
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog 去除不必要的ROLLBACK事件(MHA已不再使用此工具)
purge_relay_logs 清除中继日志(不会阻塞SQL线程)
  • 工具包安装位置

    1. 在管理节点两个都要安装
      mha4mysql-manager
      mha4mysql-node
    2. 在被管理节点安装node包
      mha4mysql-node
  • MHA集群搭建

    1. 管理节点
      在管理节点建立配置文件,定义集群服务的设置
      vim /etc/mastermha/app1.cnf
      [server default]
      user=mhauser          <==MHA服务器管理各主从服务器使用的账号
      password=magedu    	  <==MHA服务器密码
      manager_workdir=/data/mastermha/app1/            <==工作目录
      manager_log=/data/mastermha/app1/manager.log     <==日志
      remote_workdir=/data/mastermha/app1/      <==被管理节点上manager的工作目录,自动生成
      ssh_user=root              <==主服务器上设置的管理员权限的账号,用来读取日志、提升从服务器等
      repl_user=repluser         <==复制数据的账号密码
      repl_password=magedu
      ping_interval=1            <==1秒监控一次服务器是否正常工作
      [server1]   			   <==被管理的服务器
      hostname=192.168.8.17
      candidate_master=1         <==可以充当master的从服务器,人为定义充当主服务器
      [server2]
      hostname=192.168.8.27
      candidate_master=1
      [server3]
      hostname=192.168.8.37
      
  • 自定义扩展选项
    secondary_check_script: ‘通过多条网络路由检测master的可用性’
    master_ip_ailover_script: ‘更新Application使用的masterip’
    shutdown_script: ‘强制关闭master节点’
    report_script: ‘发送报告’
    init_conf_load_script: ‘加载初始配置参数’
    master_ip_online_change_script:‘更新master节点ip地址’

  • 实现主服务器(Master)
    1. '修改配置文件'
    vim /etc/my.cnf
    [mysqld]
    log-bin
    server_id=1
    skip_name_resolve=1       <==在MHA中,反向解析为强制项
    2. '查看二进制文件,备用配置从服务器'
    mysql>show master logs    
    3. '创建从服务器复制权限账号'
    mysql>grant replication slave on *.* to repluser@'192.168.8.%' identified by 	'magedu';
    4. '创建高权限账号查看二进制与提升从服务器'
    mysql>grant all on *.* to mhauser@'192.168.8.%’identified by‘magedu';
    
  • 实现slave
    1. '修改配置文件'
    vim /etc/my.cnf
    [mysqld]
    server_id=2  <==不同节点此值各不相同
    log-bin
    read_only    <==Manage会自动在提升为主的时候去掉此项
    skip_name_resolve=1
    2. '实现从服务器同步复制'
    mysql>CHANGE MASTER TO MASTER_HOST=‘MASTER_IP', MASTER_USER='repluser', MASTER_PASSWORD=‘magedu', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
    1. '管理机manager上生成ssh秘钥'
    [root@hai7-8 ~]$ssh-keygen
    2. '将秘钥导出至本机'
    [root@hai7-8 ~/.ssh]$ssh-copy-id 192.168.50.110
    3. '将整个目录拷贝至集群所有主机上'
    [root@hai7-8 ~]$scp -pr .ssh  192.168.50.115:/root/
    ......其他主机类似操作
    [root@hai7-8 ~]$vim /etc/my.cnf
    [mysqld]
    server_id=1
    innodb_file_per_table
    log_bin
    skip_name_resolve
    重启服务
    [root@hai7-8 ~]$systemctl start mariadb
    1. '用来主从复制的账号'
    MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%' IDENTIFIED BY 'magedu';
    2. '用于提升从服务器,读取日志等账号'
    mysql>GRANT all ON *.* TO mhauser@'192.168.50.%'  IDENTIFIED BY 'magedu';
    3. '查看二进制日志文件位置'
    MariaDB [(none)]> SHOW MASTER LOGS;
    +--------------------+-----------+
    | Log_name           | File_size |
    +--------------------+-----------+
    | mariadb-bin.000001 |       541 |
    +--------------------+-----------+
    [mysqld]
    server_id=2
    log-bin
    innodb_file_per_table
    skip_name_resolve
    relay_log_purge=0  <==禁止SQL线程执行完中继日志就删除,保留日志以备宕机补齐数据
    read_only
    [root@hai7-6 ~]$systemctl restart mariadb
    1. '设置复制命令'
    MariaDB [(none)]> CHANGE MASTER TO
        -> MASTER_HOST='192.168.50.108',
        -> MASTER_USER='repluser',
        -> MASTER_PASSWORD='magedu',
        -> MASTER_LOG_FILE='mariadb-bin.000001',
        ->  MASTER_LOG_POS=541;
    2. '启动复制线程命令'
    MariaDB [(none)]> START SLAVE;  
    3. '查看从服务器设置,确定状态'
    MariaDB [(none)]> SHOW SLAVE STATUS\G
    mha4mysql-node-0.56-0.el6.noarch.rpm
    mha4mysql-manager-0.56-0.el6.noarch.rpm
    [root@hai7-8 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
    [root@hai7-8 ~]$yum -y install mha4mysql-manager-0.56-0.el6.noarch.rpm 
    mha4mysql-node
    [root@hai7-8 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm 
    [root@hai7-8 ~]$mkdir /etc/mha/              <==自建存放配置文件的目录,路径没有要求
    [root@hai7-8 ~]$vim /etc/mha/appq.conf         <==编辑配置文件,文件名没有要求
    [server default]
    user=mhauser
    password=magedu
    manager_workdir=/data/mastermha/app1/      <==工作目录,会自动生成
    manager_log=/data/mastermha/app1/manager.log
    remote_workdir=/data/mastermha/app1/
    ssh_user=root
    repl_user=repluser
    repl_password=magedu
    ping_interval=1
    [server1]
    hostname=192.168.50.108
    candidate_master=1
    [server2]
    hostname=192.168.50.115
    candidate_master=1
    [server3]
    hostname=192.168.50.107
    [root@hai7-8 ~]$masterha_check_ssh --conf=/etc/mha/app1.conf
    [root@hai7-8 ~]$masterha_check_repl --conf=/etc/mha/appq.conf
    [root@hai7-8 ~]$masterha_manager --conf=/etc/mha/appq.conf
    在主服务器端执行,杀掉进程,模拟宕机
    [root@hai7-8 ~]$killall mysqld
    管理端提升从服务器后,会从前端管理模式退出,为一次性任务,可以通过查看管理端日志,查看被提升从服务器。被提升的从服务器会自动将read_only修改为OFF状态,但是配置文件中内容还在,需要手工清除,避免重启修改回去
    [root@hai7-8 ~]$vim /etc/yum.repos.d/mysql.repo
    [mysql]
    baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.59/yum/centos7-amd64/
    gpgcheck=0
    MariaDB-Galera-server
    [root@hai7-8 ~]$yum install MariaDB-Galera-server -y
    [root@hai7-8 ~]$vim  /etc/my.cnf.d/sevim server.cnf
    [galera]      <==在此语句块下修改
    # Mandatory settings
    wsrep_provider=/usr/lib64/galera/libgalera_smm.so       <==galera提供的驱动模块
    wsrep_cluster_address='gcomm://192.168.50.108,192.168.50.115,192.168.50.107'   <==galera组中的IP地址
    binlog_format=row               <==基于二进制行的复制格式
    default_storage_engine=InnoDB   <==默认存储引擎
    innodb_autoinc_lock_mode=2      <==innodb锁级别
    bind-address=0.0.0.0            <==绑定的地址,默认就行
    
  • 下面为配置可选项
    wsrep_cluster_name = ‘mycluster‘默认my_wsrep_cluster <==集群名称
    wsrep_node_name = ‘node1’ <==自定义节点名称
    wsrep_node_address = ‘192.168.8.7’

    首次启动时,需要初始化集群,在其中一个节点上执行脚本mysql
    mysql脚本来自安装包

    [root@hai7-8 init.d]$rpm -qf mysql
    MariaDB-Galera-server-5.5.59-1.el7.centos.x86_64
    
    • 1
    • 2

    在其中一个节点上运行此脚本,后面跟start --wsrep-new-cluster表示开启新的组,后续的节点只要启动服务即可

    [root@hai7-8 ~]$/etc/init.d/mysql start --wsrep-new-cluster
    其他节点启动服务即可
    [root@hai7-6 mysqldata]$service mysql start
    
    • 1
    • 2
    • 3

    9.4 复制的问题和解决方案

    • 数据损坏或丢失
      1. 主服务器(Master)
        MHA(提升从服务器为主) + semi repl(半同步复制确保数据完整)
      2. 从服务器(Slave)
        重新复制
    • 不惟一的server id
      重新同步复制
    • 复制延迟
      1. 需要额外的监控工具的辅助
      2. 一从多主:mariadb10版后支持
      3. 多线程复制:对多个数据库复制

    10. 性能衡量指标

    • 数据库服务衡量指标:

      1. qps
        query per second(每秒查询次数)
      2. tps:
        transaction per second(每秒执行的事务次数)
    • 压力测试工具

      1. mysqlslap
      2. Sysbench:功能强大
        https://github.com/akopytov/sysbench
      3. tpcc-mysql
      4. MySQL Benchmark Suite
      5. MySQL super-smack
      6. MyBench
    • MYSQL压力测试工具Mysqlslap
      来自于mariadb包,测试的过程默认生成一个mysqlslap的schema,生成测试表t1,查询和插入测试数据,mysqlslap库自动生成,如果已经存在则先删除。用–only-print来打印实际的测试过程,整个测试完成后不会在数据库中留下痕迹

      1. 使用格式
        mysqlslap [options]
      2. 常用参数 [options] 说明
        1. --auto-generate-sql, -a
          自动生成测试表和数据,表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
        2. --auto-generate-sql-load-type=type
          测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括:read,key,write,update和mixed(默认)
        3. --auto-generate-sql-add-auto-increment
          代表对生成的表自动添加auto_increment列,从5.1.18版本开始支持|
        4. --number-char-cols=N, -x
          N 自动生成的测试表中包含多少个字符类型的列,默认1
        5. --number-int-cols=N, -y
          N 自动生成的测试表中包含多少个数字类型的列,默认1
        6. --number-of-queries=N
          总的测试查询次数(并发客户数×每客户查询次数)
        7. --query=name,-q
          使用自定义脚本执行测试,例如可以调用自定义的存储过程或者sql语句来执行测试
        8. --create-schema
          代表自定义的测试库名称,测试的schema
        9. --commint=N
          多少条DML后提交一次
        10. --compress, -C
          如服务器和客户端都支持压缩,则压缩信息
        11. --concurrency=N, -c
          N 表示并发量,即模拟多少个客户端同时执行select。可指定多个值,以逗号或者–delimiter参数指定值做为分隔符如:--concurrency=100,200,500
        12. --engine=engine_name, -e engine_name
          代表要测试的引擎,可以有多个,用分隔符隔开。例如:–engines=myisam,innodb
        13. --iterations=N, -i
          N测试执行的迭代次数,代表要在不同并发环境下,各自运行测试多少次|
        14. --only-print
          只打印测试语句而不实际执行
        15. --detach=N
          执行N条语句后断开重连
        16. --debug-info, -T
          打印内存和CPU的相关信息
    • 示例

      1. 单线程测试
        mysqlslap -a -uroot -pmagedu
      2. 多线程测试。使用–concurrency来模拟并发连接
        mysqlslap -a -c 100 -uroot -pmagedu
      3. 测试同时不同的存储引擎的性能进行对比
        mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -pmagedu
        
        • 1
        --number-of-queries 1000:查询一千次
        --concurrency=50,100:并发50和100分别测试
        --iterations=5:迭代5次
        --engine=myisam,innodb:分别对myisam,innodb引擎测试
      4. 执行一次测试,分别50和100个并发,执行1000次总查询
        mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -pmagedu
        
        • 1
      5. 50和100个并发分别得到一次测试结果(Benchmark),并发数越多,执行完所有查询的时间越长。为了准确起见,可以多迭代测试几次
        mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -pmagedu
        
        • 1

    11 生产环境my.cnf配置示例

    高并发大数据的互联网业务,架构设计思路是“解放数据库CPU,将计算转移到服务层”,并发量大的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就加性能”

    '硬件:内存32G'
    innodb_file_per_table = 1
    '打开独立表空间'
    max_connections = 8000
    'MySQL服务所允许的同时会话数的上限,经常出现Too Many Connections的错误提示,则需要增大此值,根据服务器性能判断,防止设置过大超过承载能力崩溃'
    back_log = 300
    'back_log 是操作系统在监听队列中所能保持的连接数,也就是最大会话数上限后,等待数上限'
    max_connect_errors = 1000
    '每个客户端连接最大的错误允许数量,当超过该次数,MYSQL服务器将禁止此主机的连接请求,直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息'
    open_files_limit = 10240
    '所有线程所打开表的数量'
    max_allowed_packet = 32M
    '每个连接传输数据大小.最大1G,须是1024的倍数,一般设为最大的BLOB的值'
    wait_timeout = 10
    '指定一个请求的最大连接时间,超时时长'
    sort_buffer_size = 16M
    '排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序'
    join_buffer_size = 16M
    '不带索引的全表扫描.使用的buffer的最小值'
    query_cache_size = 128M
    '查询缓冲大小'
    query_cache_limit = 4M
    '指定单个查询能够使用的缓冲区大小,缺省为1M'
    transaction_isolation = REPEATABLE-READ
    '设定默认的事务隔离级别'
    thread_stack = 512K
    '线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性,此容量的内存在每次连接时被预留.'
    log-bin
    '二进制日志功能'
    binlog_format=row
    '二进制日志格式'
    innodb_buffer_pool_size = 24G
    'InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到服务器物理内存大小的80%'
    innodb_file_io_threads = 4
    '用来同步IO操作的IO线程的数量'
    innodb_thread_concurrency = 16
    '在InnoDb核心内的允许线程数量,建议的设置是CPU数量加上磁盘数量的两倍'
    innodb_log_buffer_size = 16M
    '用来缓冲日志数据的缓冲区的大小'
    innodb_log_file_size = 512M
    '在日志组中每个日志文件的大小'
    innodb_log_files_in_group = 3
    '在日志组中的文件总数'
    innodb_lock_wait_timeout = 120
    'SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间'
    long_query_time = 2
    '慢查询时长'
    log-queries-not-using-indexes
    '将没有使用索引的查询也记录下来'
    

    作者:静默虚空
    欢迎任何形式的转载,但请务必注明出处。
    限于本人水平,如果文章和代码有表述不当之处,还请不吝赐教。

    posted @   朝圣布达拉  阅读(512)  评论(0编辑  收藏  举报
    编辑推荐:
    · 如何打造一个高并发系统?
    · .NET Core GC压缩(compact_phase)底层原理浅谈
    · 现代计算机视觉入门之:什么是图片特征编码
    · .NET 9 new features-C#13新的锁类型和语义
    · Linux系统下SQL Server数据库镜像配置全流程详解
    阅读排行:
    · Sdcb Chats 技术博客:数据库 ID 选型的曲折之路 - 从 Guid 到自增 ID,再到
    · Winform-耗时操作导致界面渲染滞后
    · Phi小模型开发教程:C#使用本地模型Phi视觉模型分析图像,实现图片分类、搜索等功能
    · 语音处理 开源项目 EchoSharp
    · drools 规则引擎和 solon-flow 哪个好?solon-flow 简明教程
    点击右上角即可分享
    微信分享提示