HOME
BLOG
TIME
FRIENDS
SAY TO ME
MOOD
TRAVELING
MariaDB_Mysql数据库进阶知识
2020/11/15 18.5k 75 分钟 点击

这一篇章主要说一下关于MariaDB/Mysql的一些进阶知识,希望可以加深自己的理解

MariaDB的特性

  • 插件式存储引擎:也称为“表类型”,存储管理器有多种实现版本,功能和特性可能均略有差别;用户可根据需要灵活选择 ,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎
    MyISAM ==> Aria
    InnoDB ==> XtraDB
    存储引擎比较:
    网址:https://docs.oracle.com/cd/E17952_01/mysql- 5.5-en/storage-engines.html
  • 单进程,多线程
  • 诸多扩展和新特性
  • 提供了较多测试组件
  • 开源

存储引擎

  • MyISAM引擎特点:

    • 不支持事务
      • 表级锁定
      • 读写相互阻塞,写入不能读,读时不能写
      • 只缓存索引
      • 不支持外键约束
      • 不支持聚簇索引
      • 读取数据较快,占用资源较少
      • 不支持MVCC(多版本并发控制机制)高并发
      • 崩溃恢复性较差
      • MySQL5.5.5前默认的数据库引擎
  • 适用场景:只读(或者写较少)、表较小(可以接受长时间进行修复操作)

  • MyISAM引擎文件:

    • tbl_name.frm: 表格式定义
    • tbl_name.MYD: 数据文件
    • tbl_name.MYI: 索引文件
  • InnoDB引擎特点:

    • 支持事务,适合处理大量短期事务
    • 行级锁
    • 读写阻塞与事务隔离级别相关
    • 可缓存数据和索引
    • 支持聚簇索引
    • 崩溃恢复性更好
    • 支持MVCC高并发
    • 从MySQL5.5后支持全文索引
    • 从MySQL5.5.5开始为默认的数据库引擎
  • InnoDB数据库文件

  • 所有InnoDB表的数据和索引放置于同一个表空间中表空间文件:datadir定义的目录下

    • 数据文件:ibddata1, ibddata2, …
  • 每个表单独使用一个表空间存储表的数据和索引

    • 启用:innodb_file_per_table=ON
    • 两类文件放在数据库独立目录中
      • 数据文件(存储数据和索引):tb_name.ibd
      • 表格式定义:tb_name.frm

其它存储引擎

  • Performance_Schema:Performance_Schema数据库

  • Memory :将所有数据存储在RAM中,以便在需要快速查找参 考和其他类似数据的环境中进行快速访问。适用存放临时数据 。引擎以前被称为HEAP引擎

  • MRG_MyISAM:使MySQL DBA或开发人员能够对一系列相 同的MyISAM表进行逻辑分组,并将它们作为一个对象引用。 适用于VLDB(Very Large Data Base)环境,如数据仓库

  • Archive :为存储和检索大量很少参考的存档或安全审核信息 ,只支持SELECT和INSERT操作;支持行级锁和专用缓存区

  • Federated联合:用于访问其它远程MySQL服务器一个代理, 它通过创建一个到远程MySQL服务器的客户端连接,并将查询 传输到远程服务器执行,而后完成数据存取,提供链接单独 MySQL服务器的能力,以便从多个物理服务器创建一个逻辑数 据库。非常适合分布式或数据集市环境

  • BDB:可替代InnoDB的事务引擎,支持COMMIT、 ROLLBACK和其他事务特性

  • Cluster/NDB:MySQL的簇式数据库引擎,尤其适合于具有 高性能查找要求的应用程序,这类查找需求还要求具有最高的 正常工作时间和可用性

  • CSV:CSV存储引擎使用逗号分隔值格式将数据存储在文本文 件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应 用程序之间的数据交换

  • BLACKHOLE :黑洞存储引擎接受但不存储数据,检索总是 返回一个空集。该功能可用于分布式数据库设计,数据自动复 制,但不是本地存储

  • example:“stub”引擎,它什么都不做。可以使用此引擎创 建表,但不能将数据存储在其中或从中检索。目的是作为例子 来说明如何开始编写新的存储引擎

  • MariaDB支持的其它存储引擎:

    • OQGraph
    • SphinxSE
    • TokuDB
    • Cassandra
    • CONNECT
    • SQUENCE

管理存储引擎

  • 查看mysql支持的存储引擎:
    • show engines;
  • 查看当前默认的存储引擎:
    • show variables like ‘%storage_engine%’;
  • 设置默认的存储引擎:
    vim /etc/my.conf
    [mysqld]
    default_storage_engine= InnoDB; 
  • 查看库中所有表使用的存储引擎
    • Show table status from db_name;
  • 查看库中指定表的存储引擎
    • show table status like ‘tb_name‘;
    • show create table tb_name;
  • 设置表的存储引擎:
    • CREATE TABLE tb_name(… ) ENGINE=InnoDB;
    • ALTER TABLE tb_name ENGINE=InnoDB;

MySQL中的系统数据库

  • mysql数据库:是mysql的核心数据库,类似于sql server中 的master库,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
  • PERFORMANCE_SCHEMA:MySQL 5.5开始新增的数据库 ,主要用于收集数据库服务器性能参数,库里表的存储引擎均 为PERFORMANCE_SCHEMA,用户不能创建存储引擎为 PERFORMANCE_SCHEMA的表
  • information_schema数据库:MySQL 5.0之后产生的,一个 虚拟数据库,物理上并不存在。information_schema数据库 类似与“数据字典”,提供了访问数据库元数据的方式,即数据的数据。比如数据库名或表名,列类型,访问权限(更加细化的访问方式)

使用MariaDB

  • 提高安全性 mysql_secure_installation
    • 设置数据库管理员root口令
    • 禁止root远程登录
    • 删除anonymous用户帐号
    • 删除test数据库
  • 客户端程序:
    • mysql: 交互式的CLI工具
    • mysqldump: 备份工具,基于mysql协议向mysqld发起查询请求,并将查得的所有数据转换成insert等写操作语句保存文本文件中
    • mysqladmin:基于mysql协议管理mysqld
    • mysqlimport: 数据导入工具
  • MyISAM存储引擎的管理工具:
    • myisamchk:检查MyISAM库
    • myisampack:打包MyISAM表,只读
  • 服务器端程序
    • mysqld_safe
    • mysqld 获取默认设置:mysqld –print-defaults
    • mysqld_multi:多实例 ,示例:mysqld_multi –example

用户账号

  • mysql用户账号由两部分组成: ‘USERNAME’@’HOST‘
  • 说明: HOST限制此用户可通过哪些远程主机连接mysql服务器
    • 支持使用通配符:
      • % 匹配任意长度的任意字符 172.16.0.0/255.255.0.0 或 172.16.%.%
      • _ 匹配任意单个字符

Mysql 客户端

  • mysql使用模式:

    • 交互式模式: 可运行命令有两类:
      • 客户端命令:
        \h, help; \u,use; \s,status;\!,system
      • 服务器端命令:
        SQL, 需要语句结束符;
  • 脚本模式:

    • mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
    • mysql> source /path/from/somefile.sql
  • mysql客户端可用选项:

    • -A, –no-auto-rehash 禁止补全
    • -u, –user= 用户名,默认为root
    • -h, –host= 服务器主机,默认为localhost
    • -p, –passowrd= 用户密码,建议使用-p,默认为空密码
    • -P, –port= 服务器端口
    • -S, –socket= 指定连接socket文件路径
    • -D, –database= 指定默认数据库
    • -C, –compress 启用压缩
    • -e “SQL“ 执行SQL命令
    • -V, –version 显示版本
    • -v –verbose –print-defaults 显示详细信息 获取程序默认使用的配置

socket地址

  • 服务器监听的两种socket地址:
    • ip socket: 监听在tcp的3306端口,支持远程通信 unix
    • sock: 监听在sock文件上,仅支持本机通信
         如:/var/lib/mysql/mysql.sock 
    • 说明:host为localhost,127.0.0.1时自动使用unix sock

服务器端配置

  • 服务器端(mysqld):工作特性有多种配置方式
    • 1、命令行选项:
    • 2、配置文件:类ini格式
      集中式的配置,能够为mysql的各应用程序提供配置信息
      [mysqld]
      [mysqld_safe]
      [mysqld_multi]
      [mysql]
      [mysqldump]
      [server]
      [client]
      格式:parameter = value
      说明:
      • _和- 相同
      • 0,OFF,FALSE意义相同,1,ON,TRUE意义相同
      • 大小写相同

配置文件

  • 配置文件: 后面覆盖前面的配置文件,顺序如下:
    • /etc/my.cnf Global选项
    • /etc/mysql/my.cnf Global全局选项
    • SYSCONFDIR/my.cnf Global选项
    • $MYSQL_HOME/my.cnf (多实例) Server-specific 选项
    • –defaults-extra-file=path 启动指定配置文件
    • ~/.my.cnf User-specific 选项
  • 获取可用参数列表:
    mysqld –help –verbose

MairaDB配置

  • 侦听3306/tcp端口可以在绑定有一个或全部接口IP上
      vim /etc/my.cnf 
      [mysqld] 
      skip-networking=1 
    关闭网络连接,只侦听本地客户端, 所有和服务器的交互都通过一个socket实现,socket的配置存放 在/var/lib/mysql/mysql.sock 可在/etc/my.cnf修改 用于备份或者进行维护

视图

  • 视图:VIEW,虚表,保存有实表的查询结果
  • 创建方法:
    CREATE VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
  • 查看视图定义:SHOW CREATE VIEW view_name
  • 删除视图:
    • DROP VIEW [IF EXISTS]
    • view_name [, view_name] … [RESTRICT | CASCADE]
  • 视图中的数据事实上存储于“基表”中,因此,其修改操作也会针对基表实现;其修改操作受基表限制

函数

  • 函数:系统函数和自定义函数
    • 系统函数:
    • 自定义函数 (user-defined function UDF)
      • 保存在mysql.proc表中
        • 创建UDF:
      • CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,…])
      • RETURNS {STRING|INTEGER|REAL} runtime_body
    • 说明:
      • 参数可以有多个,也可以没有参数
      • 必须有且只有一个返回值

自定义函数

  • 查看函数列表:
    SHOW FUNCTIOIN STATUS;

  • 查看函数定义
    SHOW CREATE FUNCTION function_name

  • 删除UDF:
    DROP FUNCTION function_name

  • 调用自定义函数语法:
    SELECT function_name(parameter_value,…)

  • 示例:无参UDF
    CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN “Hello World!“;

  • 示例:有参数UDF

DELIMITER //            #(用于修改;的含义,使它不执行)
CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) 
BEGIN
DELETE FROM students WHERE stuid = uid; RETURN (SELECT COUNT(uid) FROM students); 
END//
DELIMITER ; 
  • 自定义函数中定义局部变量语法:
  • DECLARE 变量1[,变量2,… ]变量类型 [DEFAULT 默认值]
  • 说明:局部变量的作用范围是在BEGIN…END程序中,而且定义局 部变量语句必须在BEGIN…END的第一行定义
  • 示例:
DELIMITER // 
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED) 
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED DEFAULT 10; SET a = x, b = y;
RETURN a+b;
END// 
  • 为变量赋值语法
    • SET parameter_name = value[,parameter_name = value…]
    • SELECT INTO parameter_name
  • 示例:
       ... 
DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x; RETURN x;
END// 

存储过程

  • 存储过程:存储过程保存在mysql.proc表中

  • 创建存储过程
    CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]])
    routime_body
    其中:proc_parameter : [IN|OUT|INOUT] parameter_name type
    其中IN表示输入参数,OUT表示输出参数,INOUT表示既 可以输入也可以输出;param_name表示参数名称;type表示 参数的类型

  • 查看存储过程列表
    SHOW PROCEDURE STATUS

  • 查看存储过程定义
    SHOW CREATE PROCEDURE sp_name

  • 调用存储过程:

    • CALL sp_name ([ proc_parameter [,proc_parameter …]])
    • CALL sp_name 说明:当无参时,可以省略”()”,当有参数时,不可省略”()”
  • 存储过程修改:
    ALTER语句修改存储过程只能修改存储过程的注释等无关紧 要的东西,不能修改存储过程体,所以要修改存储过程,方法就 是删除重建

  • 删除存储过程:

    • DROP PROCEDURE [IF EXISTS] sp_name

存储过程示例

  • 创建无参存储过程:
delimiter // 
    CREATE PROCEDURE showTime() BEGIN 
    SELECT now(); END// 
    delimiter ;

CALL showTime;

  • 创建含参存储过程:只有一个IN参数
delimiter // 
    CREATE PROCEDURE seleById(IN id SMALLINT UNSIGNED) 
    BEGIN
    SELECT * FROM students WHERE stuid = uid; 
    END// delimiter ; 

call seleById(2);

  • 示例
delimiter // 
    CREATE PROCEDURE dorepeat(p1 INT) BEGIN 
    SET @x = 0;
    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; 
    END // 
    delimiter ;

CALL dorepeat(1000);
SELECT @x;

  • 创建含参存储过程:包含IN参数和OUT参数
delimiter // 
    CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num     SMALLINT UNSIGNED) 
    BEGIN
    DELETE FROM students WHERE stuid = id; SELETE row_count() into num;
    END//
    delimiter ;

call seleById(2,@Line);
SELETE @Line;

  • 说明:创建存储过程deleteById,包含一个IN参数和一个OUT 参数.调用时,传入删除的ID和保存被修改的行数值的用户变 量@Line,select @Line;输出被影响行数.
  • 循环创建表存储过程
create procedure slowquery() begin declare i int;set i = 1; while i < 100000 do insert into t1(name , age)values(concat('yang',i),i);set i = i+1;end while; end //

存储过程

  • 存储过程优势:
  • 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编 译保存在数据库中,当需要时从数据库中直接调用,省去了编 译的过程
  • 提高了运行速度
  • 同时降低网络数据传输量
  • 存储过程与自定义函数的区别:
  • 存储过程实现的过程要复杂一些,而函数的针对性较强
  • 存储过程可以有多个返回值,而自定义函数只有一个返回值
  • 存储过程一般独立的来执行,而函数往往是作为其他SQL语 句的一部分来使用

流程控制

  • 存储过程和函数中可以使用流程控制来控制语句的执行

  • 流程控制:

    • IF:用来进行条件判断。根据是否满足条件,执行不同语句

    • CASE:用来进行条件判断,可实现比IF语句更复杂的条件判断

    • LOOP:重复执行特定的语句,实现一个简单的循环

    • LEAVE:用于跳出循环控制

    • ITERATE:跳出本次循环,然后直接进入下一次循环

    • REPEAT:有条件控制的循环语句。当满足特定条件时,就会跳 出循环语句

    • WHILE:有条件控制的循环语句

触发器

  • 触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、 激活从而实现执行
  • 创建触发器
CREATE 
    [DEFINER = { user | CURRENT_USER }] 
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW 
    trigger_body 
  • 说明:
    • trigger_name:触发器的名称
    • trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发
    • trigger_event::{ INSERT |UPDATE | DELETE },触发的具体事件
    • tbl_name:该触发器作用在表名

触发器示例

CREATE TABLE student_info (
    stu_no INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255)     DEFAULT NULL, PRIMARY KEY (stu_no) 
    );
    CREATE TABLE student_count ( 
    student_count INT(11) DEFAULT 0 
    ); 

INSERT INTO student_count VALUES(0);

  • 示例:创建触发器,在向学生表INSERT数据时,学生数增加 ,DELETE学生时,学生数减少
CREATE TRIGGER trigger_student_count_insert 
    AFTER INSERT 
    ON student_info FOR EACH ROW 
    UPDATE student_count SET student_count=student_count+1; 
    CREATE TRIGGER trigger_student_count_delete AFTER DELETE
    ON student_info FOR EACH ROW 
    UPDATE student_count SET 

student_count=student_count-1;

  • 查看触发器
    • SHOW TRIGGERS
      查询系统表information_schema.triggers的方式指定查询条件,查看指定的触发器信息。
      mysql> USE information_schema;
      Database changed
      mysql> SELECT * FROM triggers WHERE trigger_name=’trigger_student_count_insert’;
  • 删除触发器
    DROP TRIGGER trigger_name;

服务器配置

  • mysqld选项,服务器系统变量和服务器状态变量

  • 获取运行中的mysql进程使用各服务器参数及其值

    • mysql> SHOW GLOBAL VARIABLES;
    • mysql> SHOW [SESSION] VARIABLES;
  • 注意:其中有些参数支持运行时修改,会立即生效;有些参数不支持,且只能通过修改配置文件,并重启服务器程序生效;有 些参数作用域是全局的,且不可改变;有些可以为每个用户提供 单独(会话)的设置

  • 设置服务器系统变量三种方法:

    • 在命令行中设置: shell> ./mysqld_safe –aria_group_commit=”hard“
    • 在配置文件my.cnf中设置: aria_group_commit = “hard”
    • 在mysql客户端使用SET命令: SET GLOBAL aria_group_commit=”hard”;
  • 修改服务器变量的值: mysql> help SET

  • 修改全局变量:仅对修改后新创建的会话有效;对已经建立的 会话无效

    • mysql> SET GLOBAL system_var_name=value;
    • mysql> SET @@global.system_var_name=value;
  • 修改会话变量:

    • mysql> SET [SESSION] system_var_name=value;
    • mysql> SET @@[session.]system_var_name=value;
  • 状态变量(只读):用于保存mysqld运行中的统计数据的变量,不可更改

    • mysql> SHOW GLOBAL STATUS;
    • mysql> SHOW [SESSION] STATUS;

SQL_MODE

  • 服务器变量SQL_MODE:对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置

  • 常见MODE:

    • NO_AUTO_CREATE_USER
      禁止GRANT创建密码为空的用户

    • NO_AUTO_VALUE_ON_ZERO
      在自增长的列中插入0或NULL将不会是下一个自增长值

    • NO_BACKSLASH_ESCAPES
      反斜杠“\”作为普通字符而非转义字符

    • PAD_CHAR_TO_FULL_LENGTH
      启用后,对于CHAR类型将不会截断空洞数据

    • PAD_CHAR_TO_FULL_LENGTH
      启用后,对于CHAR类型将不会截断空洞数据

    • PIPES_AS_CONCAT
      将”||”视为连接操作符而非“或运算符”

MySQL架构

查询的执行路径

查询缓存

  • 查询缓存( Query Cache )原理:
    缓存SELECT操作或预处理查询的结果集和SQL语句,当有 新的SELECT语句或预处理查询语句请求,先去查询缓存,判断 是否存在可用的记录集,判断标准:与缓存的SQL语句,是否完全一样,区分大小写 (把上一次的命令进行哈希运算,之后的与其比较,所以区分大小写,空格)

  • 优缺点

    • 不需要对SQL语句做任何解析和执行,当然语法解析必须通 过在先,直接从Query Cache中获得查询结果,提高查询性能
    • 查询缓存的判断规则,不够智能,也即提高了查询缓存的使用门槛,降低其效率;
    • 查询缓存的使用,会增加检查和清理Query Cache中记录集 的开销
  • 哪些查询可能不会被缓存

    • 查询语句中加了SQL_NO_CACHE参数
    • 查询语句中含有获得值的函数,包含自定义函数,如:NOW()CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
    • 对系统数据库的查询:mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
    • 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句 查询语句中类似SELECT …INTO 导出数据的语句
    • 对临时表的查询操作;存在警告信息的查询语句;不涉及任何表或视图的查询语句;某用户只有列级别权限的查询语句
    • 事务隔离级别为Serializable时,所有查询语句都不能缓存
  • 查询缓存相关的服务器变量

    • query_cache_min_res_unit: 查询缓存中内存块的最小分配单位,默 认4k,较小值会减少浪费,但会导致更频繁的内存分配操作,较大值 会带来浪费,会导致碎片过多,内存不足

    • query_cache_limit:单个查询结果能缓存的最大值,默认为1M, 对于查询结果过大而无法缓存的语句,建议使用SQL_NO_CACHE

    • query_cache_size:查询缓存总共可用的内存空间;单位字节,必须 是1024的整数倍,最小值40KB,低于此值有警报

    • query_cache_wlock_invalidate:如果某表被其它的会话锁定,是否 仍然可以从查询缓存中返回结果, 默认值为OFF,表示可以在表被其 它会话锁定的场景中继续从缓存返回数据;ON则表示不允许

    • query_cache_type: 取值为ON, OFF, DEMAND

  • 参看:https://dev.mysql.com/doc/refman/5.7/en/query-cache- configuration.html

  • SELECT语句的缓存控制

    • SQL_CACHE: 显式指定存储查询结果于缓存之中
    • SQL_NO_CACHE: 显式查询结果不予缓存
  • query_cache_type参数变量:

    • query_cache_type的值为OFF或0时,查询缓存功能关闭
    • query_cache_type的值为ON或1时,查询缓存功能打开, SELECT的结果符合缓存条件即会缓存,否则,不予缓存,显式指定SQL_NO_CACHE,不予缓存,此为默认值
    • query_cache_type的值为DEMAND或2时,查询缓存功能 按需进行,显式指定SQL_CACHE的SELECT语句才会缓存 ;其它均不予缓存

优化查询缓存

  • 查询缓存相关的状态变量
    SHOW GLOBAL STATUS LIKE ‘Qcache%’;

    • Qcache_free_blocks:处于空闲状态 Query Cache中内存 Block 数

    • Qcache_free_memory:处于空闲状态的 Query Cache 内存总量

    • Qcache_hits:Query Cache 命中次数

    • Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次 数,即没有命中的次数

    • Qcache_lowmem_prunes:当 Query Cache 内存容量不够,需要删 除老的 Query Cache 以给新的 Cache 对象使用的次数

    • Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句

    • Qcache_queries_in_cache:在 Query Cache 中的 SQL 数量

    • Qcache_total_blocks:Query Cache 中总的 Block

命中率

  • 命中率和内存使用率估算
  • query_cache_min_res_unit ≈(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
  • 查询缓存命中率 ≈ (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
  • 查询缓存内存使用率 ≈ (query_cache_size – qcache_free_memory) / query_cache_size * 100%

InnoDB存储引擎

  • InnoDB存储引擎的缓冲池:

    • 通常InnoDB存储引擎缓冲池的命中不应该小于99%
  • 查看相关状态变量:

    • show global status like ‘innodb%read%’\G
      • Innodb_buffer_pool_reads: 表示从物理磁盘读取页的次数
      • Innodb_buffer_pool_read_ahead: 预读的次数
      • Innodb_buffer_pool_read_ahead_evicted: 预读页,但是没 有读取就从缓冲池中被替换的页数量,一般用来判断预读的效率
      • Innodb_buffer_pool_read_requests: 从缓冲池中读取页次数
      • Innodb_data_read: 总共读入的字节数
      • Innodb_data_reads: 发起读取请求的次数,每次读取可能需要读取多个页
        存储引擎
  • Innodb缓冲池命中率计算:

  • 平均每次读取的字节数:

索引

  • 索引是特殊数据结构:定义在查找时作为查找条件的字段
  • 优点:提高查询速度,缺点:占用额外空间,影响插入速度
  • 索引实现在存储引擎
  • 索引类型:
    • 聚簇(集)索引、非聚簇索引:数据是否与索引存储在一起
    • 主键索引、辅助索引
    • 稠密索引、稀疏索引:是否索引了每一个数据项
    • B+ TREE、HASH、R TREE
    • 简单索引、组合索引
      • 左前缀索引:取前面的字符做索引
      • 覆盖索引:从索引中即可取出要查询的数据,性能高

聚簇和非聚簇索引,主键和二级索引

B+TREE索引

B-Tree索引

  • B+ Tree索引:顺序存储,每一个叶子节点到根结点的距 离是相同的;左前缀索引,适合查询范围类的数据
  • 可以使用B-Tree索引的查询类型:
    • 全值匹配:精确所有索引列,如:姓wang,名xiaochun,年龄30
    • 匹配最左前缀:即只使用索引的第一列,如:姓wang
    • 匹配列前缀:只匹配一列值开头部分,如:姓以w开头的
    • 匹配范围值:如:姓ma和姓wang之间
    • 精确匹配某一列并范围匹配另一列:如:姓wang,名以x开头的
    • 只访问索引的查询
  • B-Tree索引的限制:
    • 如果不从最左列开始,则无法使用索引:如:查找名为xiaochun,或姓为g结尾
    • 不能跳过索引中的列:如:查找姓wang,年龄30的,只能使
      用索引第一列
    • 如果查询中某个列是为范围查询,那么其右侧的列都无法 再使用索引:如:姓wang,名x%,年龄30,只能利用姓和名 上面的索引
  • 特别提示:
    • 索引列的顺序和查询语句的写法应相匹配,才能更好的利用索引
    • 为优化性能,可能需要针对相同的列但顺序不同创建不同 的索引来满足不同类型的查询需求

Hash索引

  • Hash索引:基于哈希表实现,只有精确匹配索引中的所有列 的查询才有效,索引自身只存储索引列对应的哈希值和数据指 针,索引结构紧凑,查询性能好
  • 只有Memory存储引擎支持显式hash索引
  • 适用场景:
    只支持等值比较查询,包括=, IN(), <=>
  • 不适合使用hash索引的场景:
    • 不适用于顺序查询:索引存储顺序的不是值的顺序
    • 不支持模糊匹配
    • 不支持范围查询
    • 不支持部分索引列匹配查找:如A,B列索引,只查询A列索引无效
  • 空间索引(R-Tree): MyISAM支持空间索引,可以使用任意维度组合查询,使用
    特有的函数访问,常用于做地理数据存储,使用不多

  • 全文索引(FULLTEXT):
    在文本中查找关键词,而不是直接比较索引中的值,类似搜索引擎

  • 索引优点:

    • 索引可以降低服务需要扫描的数据量,减少了IO次数
    • 索引可以帮助服务器避免排序和使用临时表
    • 索引可以帮助将随机I/O转为顺序I/O
  • 高性能索引策略:

    • 独立地使用列:尽量避免其参与运算,独立的列指索引列 不能是表达式的一部分,也不能是函数的参数,在where条 件中,始终将索引列单独放在比较符号的一侧
    • 左前缀索引:构建指定索引字段的左侧的字符数,要通过 索引选择性来评估
      • 索引选择性:不重复的索引值和数据表的记录总数的比值
    • 多列索引:AND操作时更适合使用多列索引,而非为每个 列创建单独的索引
    • 选择合适的索引列顺序:无排序和分组时,将选择性最高放左侧
  • 冗余和重复索引:(A),(A,B)即为冗余索引 不好的索引使用策略,建议扩展索引,而非冗余

索引优化建议

  • 只要列中含有NULL值,就最好不要在此例设置索引,复合索 引如果有NULL值,此列在使用时也不会使用索引
  • 尽量使用短索引,如果可以,应该制定一个前缀长度
  • 对于经常在where子句使用的列,最好设置索引
  • 对于有多个列where或者order by子句,应该建立复合索引
  • 对于like语句,以%或者‘-’开头的不会使用索引,以%结尾会使用索引
  • 尽量不要在列上进行运算(函数操作和表达式操作)
  • 尽量不要使用not in和<>操作

EXPLAIN

  • 通过EXPLAIN来分析索引的有效性:

  • EXPLAIN SELECT clause
    获取查询执行计划信息,用来查看查询优化器如何执行查询

  • 输出信息说明:

  • id: 当前查询语句中,每个SELECT语句的编号

    • 复杂类型的查询有三种:
      • 简单子查询
      • 用于FROM中的子查询
      • 联合查询:UNION
    • 注意:UNION查询的分析结果会出现一个额外匿名临时表
  • select_type:

    • 简单查询为SIMPLE
    • 复杂查询:
      • SUBQUERY: 简单子查询
      • PRIMARY:最外面的SELECT
      • DERIVED: 用于FROM中的子查询
      • UNION:UNION语句的第一个之后的SELECT语句
      • UNION RESULT: 匿名临时表
  • table:SELECT语句关联到的表

  • type:关联类型或访问类型,即MySQL决定的如何去查询表 中的行的方式,以下顺序,性能从低到高

    • ALL: 全表扫描
    • index:根据索引的次序进行全表扫描;如果在Extra列出现“Using index”表示了使用覆盖索引,而非全表扫描
    • range:有范围限制的根据索引实现范围扫描;扫描位置始于索引中的某一点,结束于另一点
    • ref: 根据索引返回表中匹配某单个值的所有行
    • eq_ref:仅返回一个行,但与需要额外与某个参考值做比较
    • const, system: 直接返回单个行
  • possible_keys:查询可能会用到的索引

  • key: 查询中使用到的索引

  • key_len: 在索引使用的字节数

    管理索引

  • 创建索引:
    CREATE INDEX index_name ON tbl_name (index_col_name,…);
    help CREATE INDEX

  • 删除索引:
    DROP INDEX index_name ON tbl_name;

  • 查看索引:
    SHOW INDEXES FROM [db_name.]tbl_name;

  • 优化表空间:
    OPTIMIZE TABLE tb_name

SQL语句性能优化

  • 查询时,能不要就不用,尽量写全字段名
  • 大部分情况连接效率远大于子查询
  • 多表连接时,尽量小表驱动大表,即小表 join 大表
  • 在千万级分页时使用limit
  • 对于经常使用的查询,可以开启缓存
  • 多使用explain和profile分析查询语句
  • 查看慢查询日志,找出执行时间长的sql语句优化

并发控制

  • 锁粒度:

    • 表级锁
    • 行级锁
  • 锁:

    • 读锁:共享锁,只读不可写,多个读互不阻塞
    • 写锁:独占锁,排它锁,一个写锁会阻塞其它读和它锁
  • 实现

    • 存储引擎:自行实现其锁策略和锁粒度
    • 服务器级:实现了锁,表级锁;用户可显式请求
  • 分类:

    • 隐式锁:由存储引擎自动施加锁
    • 显式锁:用户手动请求
  • 锁策略:在锁粒度及数据安全性寻求的平衡机制

  • 显示使用锁

    • LOCK TABLES
      tbl_name [[AS] alias] lock_type
      [, tbl_name [[AS] alias] lock_type] …
      lock_type: READ , WRITE
      UNLOCK TABLES 解锁
  • FLUSH TABLES tb_name[,…] [WITH READ LOCK]
    关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁 配合MyISAM使用

  • SELECT clause [FOR UPDATE | LOCK IN SHARE MODE] 查询时加写或读锁

  • 死锁: 两个或多个事务在同一资源相互占用,并请求锁定对方占
    用的资源的状态

  • 事务日志:
    事务日志的写入类型为“追加”,因此其操作为“顺序 IO”;通常也被称为:

    • 预写式日志 write ahead logging
    • 日志文件: ib_logfile0, ib_logfile1

      事务

  • 事务Transactions:一组原子性的SQL语句,或一个独立工 作单元

  • 事务日志:记录事务信息,实现undo,redo等故障恢复功能

  • ACID特性:

    • A:atomicity原子性;整个事务中的所有操作要么全部成 功执行,要么全部失败后回滚
    • C:consistency一致性;数据库总是从一个一致性状态转 换为另一个一致性状态
    • I:Isolation隔离性;一个事务所做出的操作在提交之前, 是不能为其它事务所见;隔离有多种隔离级别,实现并发
    • D:durability持久性;一旦事务提交,其所做的修改会永 久保存于数据库中

    Transaction生命周期

  • 启动事务:START TRANSACTION

  • 结束事务:

    • (1) COMMIT:提交
    • (2) ROLLBACK: 回滚
  • 注意:只有事务型存储引擎方能支持此类操作

  • 建议:显式请求和提交事务,而不要使用“自动提交”功能
    set autocommit={1|0}

  • 事务支持保存点:savepoint

    • SAVEPOINT identifier
    • ROLLBACK [WORK] TO [SAVEPOINT] identifier
    • RELEASE SAVEPOINT identifier

事务隔离级别

  • 事务隔离级别:从上至下更加严格
    • READ UNCOMMITTED 可读取到未提交数据,产生脏读
    • READ COMMITTED 可读提交数据,但未提交数据不可读 ,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致
    • REPEATABLE READ 可重复读,多次读取数据都一致,产 生幻读,即读取过程中,即使有其它提交的事务修改数据, 仍只能读取到未修改前的旧数据。此为MySQL默认设置
    • SERIALIZABILE 可串行化,未提交的读事务阻塞修改事 务,或者未提交的修改事务阻塞读事务。导致并发性能差
  • MVCC: 多版本并发控制,和事务级别相关

事务

  • 指定事务隔离级别:
  • 服务器变量tx_isolation指定,默认为REPEATABLE-READ,可在GLOBAL和SESSION级进行设置
    • SET tx_isolation=’’
    • READ-UNCOMMITTED
    • READ-COMMITTED
    • REPEATABLE-READ
    • SERIALIZABLE
  • 服务器选项中指定
    vim /etc/my.cnf
    [mysqld]
    transaction-isolation=SERIALIZABLE

日志

  • 日志

    • 事务日志:transaction log
    • 错误日志:error log
    • 查询日志:query log
    • 慢查询日志:slow query log
    • 二进制日志:binary log
    • 中继日志:reley log
  • 事务日志:transaction log

    • 事务型存储引擎自行管理和使用
      • redo log
      • undo log
    • Innodb事务日志相关配置:
      • show variables like ‘%innodb_log%’;
      • innodb_log_file_size 5242880 每个日志文件大小
      • innodb_log_files_in_group 2 日志组成员个数
      • innodb_log_group_home_dir ./ 事务文件路径
  • 中继日志:relay log
    主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取到的事件

  • 错误日志

    • mysqld启动和关闭过程中输出的事件信息
    • mysqld运行中产生的错误信息
    • event scheduler运行一个event时产生的日志信息
    • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
  • 错误日志相关配置

    • SHOW GLOBAL VARIABLES LIKE ‘log_error’
    • 错误文件路径: log_error=/PATH/TO/LOG_ERROR_FILE
    • 是否记录警告信息至错误日志文件 log_warnings=1|0 默认值1
  • 查询日志:记录查询操作

    • 文件:file,默认值
    • 表:table
  • 查询日志相关设置

  • general_log=ON|OFF

  • general_log_file=HOSTNAME.log

  • log_output=TABLE|FILE|NONE

慢查询日志

  • 慢查询日志:记录执行查询时长超出指定时长的操作
    • slow_query_log=ON|OFF 开启或关闭慢查询
    • long_query_time=N 慢查询的阀值,单位秒
    • slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
    • log_slow_filter = admin,filesort,filesort_on_disk,full_join,
    • full_scan,query_cache,query_cache_miss,tmp_table,tmp_tabl e_on_disk
    • log_queries_not_using_indexes=ON 不使用索引也没有达到慢查 询阀值的语句是否记录日志,默认OFF,即不记录
    • log_slow_rate_limit = 1 多少次查询才记录,mariadb特有
    • log_slow_verbosity= Query_plan,explain 记录内容
    • log_slow_queries = OFF 同slow_query_log 新版已废弃
  • 二进制日志

    • 记录导致数据改变或潜在导致数据改变的SQL语句
    • 功能:通过“重放”日志文件中的事件来生成数据副本
    • 注意:建议二进制日志和数据文件分开存放
  • 二进制日志相关配置

    • 查看mariadb自行管理使用中的二进制日志文件列表
      • SHOW {BINARY | MASTER} LOGS
    • 查看使用中的二进制日志文件
      • SHOW MASTER STATUS
    • 查看二进制文件中的指定内容
      • SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
      • show binlog events in ‘mariadb-bin.000001’ from 6516 limit 2,3
  • 二进制日志记录格式

    • 二进制日志记录三种格式
      • 基于“语句”记录:statement,记录语句,默认模式
      • 基于“行”记录:row,记录数据,日志量较大
      • 混合模式:mixed, 让系统自行判定该基于哪种方式进行
  • 格式配置

    • show variables like ‘%binlog_format%’;
  • 二进制日志文件的构成 有两类文件

    • 日志文件:mysql|mariadb-bin.文件名后缀,二进制格式 如: mysql-bin.000001
    • 索引文件:mysql|mariadb-bin.index,文本格式
  • 二进制日志相关的服务器变量:

    • sql_log_bin=ON|OFF:是否记录二进制日志,默认ON
    • log_bin=/PATH/BIN_LOG_FILE:指定文件位置;默认 OFF,表示不启用二进制日志功能,上述两项都开启才可
    • binlog_format=STATEMENT|ROW|MIXED:二进制日 志记录的格式,默认STATEMENT
    • max_binlog_size=1073741824:单个二进制日志文件的 最大体积,到达最大值会自动滚动,默认为1G 说明:文件达到上限时的大小未必为指定的精确值
    • sync_binlog=1|0:设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘
    • expire_logs_days=N:二进制日志可以自动删除的天数。 默认为0,即不自动删除
  • mysqlbinlog:二进制日志的客户端命令工具

  • 命令格式:

    • mysqlbinlog [OPTIONS] log_file…
    • –start-position=# 指定开始位置
    • –stop-position=#
    • –start-datetime=
    • –stop-datetime=
    • 时间格式:YYYY-MM-DD hh:mm:ss
    • –base64-output[=name]
  • 示例:

    • mysqlbinlog –start-position=6787 –stop-position=7527 /var/lib mysql/mariadb-bin.000003
    • mysqlbinlog –start-datetime=”2018-01-30 20:30:10” – stop-datetime=”2018-01-30 20:35:22” mariadb-bin.000003;
  • 二进制日志事件的格式:
    # at 328
    #151105 16:31:40 server id 1 end_log_pos 431 Query
    thread_id=1 exec_time=0 error_code=0
    use `mydb`/*!*/;
    SET TIMESTAMP=1446712300/*!*/;
    CREATE TABLE tb1 (id int, name char(30))
    /*!*/;

    • 事件发生的日期和时间:151105 16:31:40
    • 事件发生的服务器标识:server id 1
    • 事件的结束位置:end_log_pos 431
    • 事件的类型:Query 事件发生时所在服务器执行此事件的线程的ID:thread_id=1
    • 语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
    • 错误代码:error_code=0
    • 事件内容:
      GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性
  • 清除指定二进制日志:
    PURGE { BINARY | MASTER } LOGS
    { TO ‘log_name’ | BEFORE datetime_expr }
    示例:
    PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3前日志
    PURGE BINARY LOGS BEFORE ‘2017-01-23’;
    PURGE BINARY LOGS BEFORE ‘2017-03-22 09:25:30’;

  • 删除所有二进制日志,index文件重新记数
    RESET MASTER [TO #]; 日志文件从#开始记数,默认从1开始,一般是master第一次启动时执行,MariaDB10.1.6开始支持TO #

  • 切换日志文件:

    • FLUSH LOGS;

备份和恢复

  • 为什么要备份

    • 灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
  • 备份注意要点

    • 能容忍最多丢失多少数据
    • 恢复数据需要在多长时间内完成
    • 需要恢复哪些数据
  • 还原要点

    • 做还原测试,用于测试备份的可用性
    • 还原演练
  • 备份类型

    • 完全备份,部分备份
      • 完全备份:整个数据集
      • 部分备份:只备份数据子集,如部分库或表
    • 完全备份、增量备份、差异备份
      • 增量备份:仅备份最近一次完全备份或增量备份(如果存 在增量)以来变化的数据,备份较快,还原复杂
      • 差异备份:仅备份最近一次完全备份以来变化的数据,备 份较慢,还原简单
  • 注意:二进制日志文件不应该与数据文件放在同一磁盘

  • 冷、温、热备份

    • 冷备:读写操作均不可进行
    • 温备:读操作可执行;但写操作不可执行
    • 热备:读写操作均可执行 (MVCC 通过事物)
      • MyISAM:温备,不支持热备
      • InnoDB: 都支持
  • 物理和逻辑备份

    • 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
    • 逻辑备份:从数据库中“导出”数据另存而进行的备份, 与存储引擎无关,占用空间少,速度慢,可能丢失精度
  • 备份时需要考虑的因素

    • 温备的持锁多久
    • 备份产生的负载
    • 备份过程的时长
    • 恢复过程的时长
  • 备份什么

    • 数据
    • 二进制日志、InnoDB的事务日志
    • 程序代码(存储过程、存储函数、触发器、事件调度器) 服务器的配置文件
  • 设计备份方案

    • 数据集:完全+增量
    • 备份手段:物理,逻辑
  • 备份工具

    • mysqldump:逻辑备份工具,适用所有存储引擎,温备; 支持完全或部分备份;对InnoDB存储引擎支持热备
    • cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
    • LVM的快照:先加锁,做快照后解锁,几乎热备;借助文 件系统管理工具进行备份
    • mysqlhotcopy:几乎冷备;仅适用于MyISAM存储引擎
  • 备份工具的选择:

    • mysqldump+复制binlog:
      • mysqldump:完全备份
      • 复制binlog中指定时间范围的event:增量备份
    • LVM快照+复制binlog:
      • LVM快照:使用cp或tar等做物理备份;完全备份
      • 复制binlog中指定时间范围的event:增量备份
    • xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
    • MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
    • mysqlbackup:热备份, MySQL Enterprise Edition组件
  • 逻辑备份工具:mysqldump, mydumper, phpMyAdmin

  • Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

  • mysqldump工具:客户端命令,通过mysql协议连接至mysqld 服务器进行备份

    • mysqldump [OPTIONS] database [tables]
    • mysqldump [OPTIONS] –B DB1 [DB2 DB3…]
    • mysqldump [OPTIONS] –A [OPTIONS]
  • mysqldump参考: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.h tml

  • mysqldump常见选项:

    • -A, –all-databases 备份所有数据库,含create database
    • -B , –databases db_name… 指定备份的数据库,包括 create database语句
    • -E, –events:备份相关的所有event scheduler
    • -R, –routines:备份所有存储过程和存储函数
    • –triggers:备份表相关的触发器,默认启用,用–skip- triggers,不备份触发器
    • –master-data[=#]: 此选项须启用二进制日志
      • 1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1
      • 2:记录为注释的CHANGE MASTER TO语句
        • 此选项会自动关闭–lock-tables功能,自动打开–lock-all- tables功能(除非开启–single-transaction)
    • -F, –flush-logs :备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A时,会导致刷新多次数据库,在同一时刻执行转储和日志刷新,则应同时使用–flush-logs和-x,–master-data或-single-transaction,此时只刷新一次
      建议:和-x,–master-data或 –single-transaction一起使用
    • –compact 去掉注释,适合调试,生产不使用
    • -d, –no-data 只备份表结构
    • -t, –no-create-info 只备份数据,不备份create table
    • -n,–no-create-db 不备份create database,可被-A或-B覆盖
    • –flush-privileges 备份mysql或相关时需要使用
    • -f, –force 忽略SQL错误,继续执行
    • –hex-blob 使用十六进制符号转储二进制列(例如,“abc”变 为0x616263),受影响的数据类型包括BINARY, VARBINARY, BLOB,BIT
    • -q, –quick 不缓存查询,直接输出,加快备份速度
  • MyISAM备份选项:

    • 支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
    • 锁定方法如下:
      • -x,–lock-all-tables:加全局读锁,锁定所有库的所有表 ,同时加–single-transaction或–lock-tables选项会关闭 此选项功能
    • 注意:数据量大时,可能会导致长时间无法并发访问数据库
    • -l,–lock-tables:对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,–skip-lock-tables选 项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
    • 注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用
  • InnoDB备份选项:

    • 支持热备,可用温备但不建议用
    • –single-transaction
    • 此选项Innodb中推荐使用,不适用MyISAM,此选项会 开始备份前,先执行START TRANSACTION指令,并且在 备份期间,不允许对数据进行修改操作
    • 此选项和–lock-tables(此选项隐含提交挂起的事务) 选项是相互排斥
    • 备份大型表时,建议将–single-transaction选项和– quick结合一起使用

生产备份策略

  • InnoDB建议备份策略
    mysqldump –uroot –A –F –E –R –single-transaction –master-data=1 –flush-privileges –triggers –hex- blob >$BACKUP_DIRfullbak_$BACKUP_TIME.sql

  • MyISAM建议备份策略
    mysqldump –uroot –A –F –E –R –x –master-data=1 – flush-privileges —triggers –hex-blob >$BACKUP_DIR/fullbak_$BACKUP_TIME.sql

实验:恢复误删除的数据库到最新状态

    1. 完全备份
      mysqldump –uroot –A –F –E –R –single-transaction –master-data=1 –flush-privileges –triggers –hex- blob > mysql-`date +%F-%T`.sql
  • 2.对数据库修改
    insert drop database hellodb;

  • 3.发现问题

mysql>flush tables with read lock;
iptables 禁止其它用户访问

  • 4.查看完全备份的位置
    less /app/all-2018-02-23-09:48:53.sql
    CHANGE MASTER TO MASTER_LOG_FILE=’mariadb-bin.000008’, MASTER_LOG_POS=245;

  • 5.flush logs 生成新日志文件

  • 6.备份日志文件
    cp /var/lib/mysql/mariadb-bin.000008 /app/
    mysqlbinlog –start-position=245 mariadb-bin.000008 > /app/bin.sql
    mysqlbinlog mariadb-bin.000009 >> /app/bin.sql

  • 7.vim /app/bin.sql
    删除drop database hellodb; 此命令

  • 8.mysql>set sql_log_bin=0;

  • 9.mysql>unlock tables;

  • 10.mysql> source /app/all-2018-02-2309:48:53.sql

  • 11.mysql> source /app/bin.sql

  • 12.mysql>set sql_log_bin=1;

  • 13.iptables 恢复用户访问

备份和恢复

基于LVM的备份

  • (1) 请求锁定所有表
    mysql> FLUSH TABLES WITH READ LOCK;

  • (2) 记录二进制日志文件及事件位置
    mysql> FLUSH LOGS;
    mysql> SHOW MASTER STATUS;
    mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE

  • (3) 创建快照
    lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME

  • (4) 释放锁
    mysql> UNLOCK TABLES;

  • (5) 挂载快照卷,执行数据备份

  • (6) 备份完成后,删除快照卷

  • (7) 制定好策略,通过原卷备份二进制日志

实验:基于LVM的备份还原

  • 1.分离数据和binlog到各自的逻辑卷

      * (创建逻辑卷)
      pvcreate /dev/sdb1
      vgcreate vg0  /dev/sdb1
      lvcreate -n lv0 -L 10G vg0
      mkfs.xfs /dev/逻辑卷名
      写到/etc/fstab文件
      mount -a 挂载
       binlogs         mysqldat

    vim /etc/my.cnf
    datadir=/data/mysqldata
    socket=/var/lib/mysql/mysql.sock
    log-bin=/data/binlogs/mysql-bin

  • 2.请求锁定所有表
    mysql> FLUSH TABLES WITH READ LOCK;

  • 3.记录二进制日志文件及事件位置
    mysql> FLUSH LOGS;
    mysql> SHOW MASTER STATUS;
    mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE

  • 4.创建快照
    lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME

  • 5.释放锁
    mysql> UNLOCK TABLES;

  • 6.挂载快照卷,执行数据备份
    mount -o nouuid,norecovery /dev/vg0/mysqldata-snapshot /mnt/snap
    cp -a /mnt/snap/* /backups/

  • 7.备份完成后,删除快照卷

  • 8.破坏数据库
    systemctl stop mariadb
    rm -rf /data/mysqldata/*

  • 9.还原
    cp -a /backups/* /data/mysqldata/

  • 10.binlog还原
    cp /data/binlogs/mysql-bin.00000{4,5} /app/ -a
    mysqlbinlog –start-position=245 mysql-bin.000004 > bin.sql
    mysqlbinlog mysql-bin.000005 >> bin.sql

    system start mariadb
    进入mysql
    flush tables with read lock;
    set sql_log_bin=0;
    unlock tables;
    source /app/bin.sql
    set sql_log_bin=1;

xtrabackup

  • Percona

  • Xtrabackup

    • percona提供的mysql数据库备份工具,惟一开源的能够 对innodb和xtradb数据库进行热备的工具
    • 特点:
      • (1)备份还原过程快速、可靠
      • (2)备份过程不会打断正在执行的事务
      • (3)能够基于压缩等功能节约磁盘空间和流量
      • (4)自动实现备份检验
      • (5)开源,免费
  • Xtrabackup2.2版之前包括4个可执行文件:

    • Innobackupex: perl 脚本
    • Xtrabackup: C/C++ 编译的二进制
    • Xbcrypt:加解密
    • Xbstream:支持并发写的流文件格式
  • xtrabackup 是用来备份 InnoDB 表的,不能备份非 InnoDB表,和 mysqld server 没有交互;innobackupex 脚本用来备份非 InnoDB 表,同时会调用 xtrabackup 命令来备份 InnoDB 表,还 会和 mysqld server 发送命令进行交互,如加读锁(FTWRL)、 获取位点(SHOW SLAVE STATUS)等。即innobackupex是在 xtrabackup 之上做了一层封装实现的。

  • 虽然目前一般不用 MyISAM 表,只是mysql 库下的系统表是 MyISAM 的,因此备份基本都通过 innobackupex 命令进行

Xtrabackup备份过程

Xtrabackup的新版变化

  • xtrabackup版本升级到2.4后,相比之前的2.1有了比较大的 变化:innobackupex 功能全部集成到 xtrabackup 里面,只 有一个 binary程序,另外为了兼容考虑,innobackupex作为 xtrabackup 的软链接,即xtrabackup现在支持非Innodb表 备份,并且Innobackupex在下一版本中移除,建议通过 xtrabackup替换innobackupex
  • xtrabackup安装:

innobakupex备份

  • 使用innobakupex备份时,其会调用xtrabackup备份所有的 InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以 及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同 时还会备份触发器和数据库配置信息相关的文件。这些文件会 被保存至一个以时间命名的目录中,在备份时,innobackupex 还会在备份目录中创建如下文件:
    • (1)xtrabackup_checkpoints:备份类型(如完全或增量)、 备份状态(如是否已经为prepared状态)和LSN(日志序列号) 范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志 序列号,即LSN。LSN是整个数据库系统的系统版本号,每 个页面相关的LSN能够表明此页面最近是如何发生改变的
    • (2)xtrabackup_binlog_info:mysql服务器当前正在使用的 二进制日志文件及至备份这一刻为止二进制日志事件的位置
    • (3)xtrabackup_binlog_pos_innodb:二进制日志文件及用于 InnoDB或XtraDB表的二进制日志文件的当前position
    • (4)xtrabackup_binary:备份中用到的xtrabackup的可执行 文件
    • (5)backup-my.cnf:备份命令用到的配置选项信息
      • 在使用innobackupex进行备份时,还可以使用–no- timestamp选项来阻止命令自动创建一个以时间命名的目录 ;innobackupex命令将会创建一个BACKUP-DIR目录来存 储备份数据

Xtrabackup用法

  • 备份:innobackupex [option] BACKUP-ROOT-DIR

  • 选项说明:

    • –user:该选项表示备份账号
    • –password:该选项表示备份的密码
    • –host:该选项表示备份数据库的地址
    • –databases:该选项接受的参数为数据名,如果要指定多个数据库,彼此间 需要以空格隔开;如:”xtra_test dba_test”,同时,在指定某数据库时,也 可以只指定其中的某张表。如:”mydatabase.mytable”。该选项对innodb引 擎表无效,还是会备份所有innodb表
    • –defaults-file:该选项指定了从哪个文件读取MySQL配置,必须放在命令 行第一个选项的位置
    • –incremental:该选项表示创建一个增量备份,需要指定–incremental- basedir
    • –incremental-basedir:该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录,与–incremental同时使用
    • –incremental-dir:该选项表示增量备份的目录
    • –include=name:指定表名,格式:databasename.tablename
  • Prepare:innobackupex –apply-log [option] BACKUP-DIR

  • 选项说明:

    • –apply-log:一般情况下,在备份完成后,数据尚且不能用于恢复 操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但 尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致 状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务 至数据文件使数据文件处于一致性状态
    • –use-memory:该选项表示和–apply-log选项一起使用, prepare 备份的时候,xtrabackup做crash recovery分配的内存 大小,单位字节。也可(1MB,1M,1G,1GB),推荐1G
    • –defaults-file:该选项指定了从哪个文件读取MySQL配置,必 须放在命令行第一个选项的位置
    • –export:表示开启可导出单独的表之后再导入其他Mysql中
    • –redo-only:这个选项在prepare base full backup,往其中 merge增量备份时候使用
  • 还原:innobackupex –copy-back [选项] BACKUP-DIR

  • innobackupex –move-back [选项] [–defaults-group=GROUP-NAME] BACKUP-DIR

  • 选项说明:

  • –copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir

  • –move-back:这个选项与–copy-back相似,唯一的区别 是它不拷贝文件,而是移动文件到目的地。这个选项移除 backup文件,用时候必须小心。使用场景:没有足够的磁盘 空间同事保留数据文件和Backup副本

  • 还原注意事项:

    • 1.datadir目录必须为空。除非指定innobackupex –force-non-empty-directorires选项指定,否则–copy-backup选项 不会覆盖
    • 2.在restore之前,必须shutdown MySQL实例,你不能将一个 运行中的实例restore到datadir目录中
    • 3.由于文件属性会被保留,大部分情况下你需要在启动实例之前 将文件的属主改为mysql,这些文件将属于创建备份的用户
      chown -R mysql:mysql /data/mysql
      以上需要在用户调innobackupex之前完成
      –force-non-empty-directories:指定该参数时候,使得 innobackupex –copy-back或–move-back选项转移文件到非 空目录,已存在的文件 不会被覆盖。如果–copy-back和– move-back文件需要从备份目录拷 贝一个在datadir已经存在的 文件,会报错失败

    实验:完全备份及还原

  • 1.在原主机

    • 备份
      innobackupex –user=root /backups
      scp -r /backups/2020-02-23_11-55-57/ 192.168.27.17:/app/
  • 2.在目标主机

    • 整理
      innobackupex –apply-log /app/2018-02-23_11-55-57/
    • 模拟删除
      systemctl stop mariadb
      rm -rf /var/lib/mysql/*
    • 还原
      innobackupex –copy-back /app/2020-02-23_11-55-57/
      chown -R mysql.mysql /var/lib/mysql/
      systemctl start mariadb

实验:完全,增量备份及还原

  • 1.在原主机
    • 完全备份
      innobackupex /backups
    • 创建存放备份的文件夹
      mkdir /backups/inc{1,2}
    • 修改数据
      insert students (name,age,gender)values(‘a’,29,’F’);
    • 第一次增量备份
      innobackupex –incremental /backups/inc1 –incremental-basedir=/backups/2018-02-23_14-21-42
    • 修改数据
      insert students (name,age,gender)values(‘b’,20,’M’);
    • 第二次增量备份
      innobackupex –incremental /backups/inc2 –incremental-basedir=/ backups/inc1/2018-02-23_14-26-17
    • 发送到远程主机
      scp -r /backups/ 192.168.27.17:/app/
  • 2.在目标主机
    • 不启动mariadb
      systemctl stop mariadb
    • rm -rf /var/lib/mysql/*
    • 整理完全备份与第一次增量备份
      innobackupex –apply-log –redo-only /app/backups/2018-02-23_14-21-42/
      innobackupex –apply-log –redo-only /app/backups/2018-02-23_14-21-42/ –incremental-dir=/app/backups/inc1/2018-02-23_14-26-17
    • 整理完全备份与第二次增量备份
      innobackupex –apply-log –redo-only /app/backups/2018-02-23_14-21-42/ –incremental-dir=/app/backups/inc2/2018-02-23_14-28-29/
    • 还原
      innobackupex –copy-back /app/backups/2018-02-23_14-21-42/
    • 修改权限
      chown -R mysql.mysql /var/lib/mysql/
    • 启动
      systemctl start mariadb

实验:单表导出和导入

  • 1 单表备份
    innobackupex –include=’hellodb.students’ /backups

  • 2 mysql -e ‘show create table hellodb.students’ > student.sql

CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
  • 3 删除表
    mysql -e ‘drop table hellodb.students’

  • 4 整理备份
    innobackupex –apply-log –export /backups/2018-02-23_15-03-23/

  • 5 创建表

mysql> CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
  • 6 删除表空间(存放数据的文件)
    alter table students discard tablespace;

  • 7 复制数据表的全部结构文件
    cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd} /var/ lib/mysql/hellodb/

  • 8 修改权限
    chown -R mysql.mysql /var/lib/mysql/hellodb/

  • 9 导入表空间
    mysql>alter table students import tablespace;
    mysql>select * from students;

MySQL复制

  • 扩展方式: Scale Up ,Scale Out
  • MySQL的扩展
    • 复制:每个节点都有相同的数据集
    • 向外扩展
    • 二进制日志
    • 单向
  • 复制的功用:
    • 数据分布
    • 负载均衡读
    • 备份
    • 高可用和故障切换
    • MySQL升级测试


  • 主从复制线程:
    • 主节点:
      • dump Thread:为每个Slave的I/O Thread启动一个dump线程 ,用于向其发送binary log events
    • 从节点:
      • I/O Thread:向Master请求二进制日志事件,并保存于中继日志中
      • SQL Thread:从中继日志中读取日志事件,在本地完成重放
  • 跟复制功能相关的文件:
    • master.info:用于保存slave连接至master时的相关信息,例如 账号、密码、服务器地址等
    • relay-log.info:保存在当前slave节点上已经复制的当前二进制 日志和本地replay log日志的对应关系
  • 主从复制特点:
    • 异步复制
    • 主从数据不一致比较常见
  • 复制架构:
    • Master/Slave, Master/Master, 环状复制
    • 一主多从
    • 从服务器还可以再有从服务器
    • 一从多主:适用于多个不同数据库
  • 复制需要考虑二进制日志事件记录格式
    • STATEMENT(5.0之前)、ROW(5.1之后,推荐)、MIXED
  • 各种复制模型实战:
    • 主从、主主、半同步复制、复制过滤器
  • 主从配置过程:

  • 从节点配置:

    • (1) 启动中继日志
      [mysqld]
      server_id=# 为当前节点设置一个全局惟的ID号
      relay_log=relay-log relay log的文件路径,默认值hostname-relay-bin relay_log_index=relay-log.index 默认值hostname-relay-bin.index
    • (2) 使用有复制权限的用户账号连接至主服务器,并启动复制线程
      mysql> CHANGE MASTER TO MASTER_HOST=’host’, MASTER_USER=’repluser’, MASTER_PASSWORD=’replpass’, MASTER_LOG_FILE=’mysql-bin.xxxxx’, MASTER_LOG_POS=#;
      mysql> START SLAVE [IO_THREAD|SQL_THREAD];
  • 如果主节点已经运行了一段时间,且有大量数据时,如何配置 并启动slave节点

    • 通过备份恢复数据至从服务器
    • 复制起始位置为备份时,二进制日志文件及其POS
  • 如果要启用级联复制,需要在从服务器启用以下配置
    [mysqld]
    log_bin
    log_slave_updates

  • 复制架构中应该注意的问题:

  • 1、限制从服务器为只读

      * 在从服务器上设置read_only=ON 注意:此限制对拥有SUPER权限的用户均无效 
      * 阻止所有用户, 包括主服务器复制的更新 mysql> FLUSH TABLES WITH READ LOCK; 
  • 2、如何保证主从复制的事务安全

    • 参看https://mariadb.com/kb/en/library/server-system-variables/
    • 在master节点启用参数:
      • sync_binlog=1 每次写后立即同步二进制日志到磁盘,性能差
      • 如果用到的为InnoDB存储引擎: innodb_flush_logs_at_trx_commit=1
        每次事务提交立即同步日志写磁盘
      • innodb_support_xa=ON 默认值,分布式事务MariaDB10.3.0废除
      • sync_master_info=# 多少次事件后master.info同步到磁盘
    • 在slave节点启用服务器选项:
      • –skip_slave_start=ON 不自动启动slave
    • 在slave节点启用参数:
      • sync_relay_log=# #次写后同步relay log到磁盘
      • sync_relay_log_info=#多个次事务后同步relay-log.info到磁盘

实验:级联复制

Amaster (主)

  • 配置文件主要配置必须加
    [mysqld]
    innodb_file_per_table
    log-bin
    server_id=1
  • mysql>show master logs;
  • 给从权限
    mysql> grant replication slave on . to repluser@’192.168.27.%’ identified by ‘centos’;

Bslave

  • 配置文件主要配置必须加
    [mysqld]
    innodb_file_per_table
    read-only
    server_id=2
    log_slave_updates
    log-bin
  • mysql>show master logs;
  • 连接主服务器
    mysql>
    CHANGE MASTER TO
    MASTER_HOST=’masterip’,
    MASTER_USER=’repluser’,
    MASTER_PASSWORD=’centos’,
    MASTER_LOG_FILE=’mariadb-bin.000001’, 前面查看到的结果
    MASTER_LOG_POS=245;
  • 启动slave
    start slave;
  • 查看slave状态
    show slave status;

Cslave’s

  • 配置文件主要配置必须加
    [mysqld]
    innodb_file_per_table
    read-only
    server-id=3
  • 连接上一个从服务器
    mysql>
    CHANGE MASTER TO
    MASTER_HOST=’slaveip’,
    MASTER_USER=’repluser’,
    MASTER_PASSWORD=’centos’,
    MASTER_LOG_FILE=’mariadb-bin.000001’, 前面查看到的结果
    MASTER_LOG_POS=245;
  • 启动slave
    start slave;
  • 查看slave状态
    show slave status;

Mysql 拆库

MySQL垂直分区

MySQL水平分片(Sharding)

对应shard中查询相关数据

主主复制

  • 主主复制:互为主从
    • 容易产生的问题:数据不一致;因此慎用
    • 考虑要点:自动增长id
      • 配置一个节点使用奇数id
        • auto_increment_offset=1 开始点
        • auto_increment_increment=2 增长幅度
      • 另一个节点使用偶数id auto_increment_offset=2
  • 主主复制的配置步骤:
    • (1) 各节点使用一个惟一server_id
    • (2) 都启动binary log和relay log
    • (3) 创建拥有复制权限的用户账号
    • (4) 定义自动增长id字段的数值范围各为奇偶
    • (5) 均把对方指定为主节点,并启动复制线程

实验:主主复制

  • 1.node1 修改配置文件
    vim /etc/my.cnf
    [mysqld]
    log-bin
    server_id=1
    auto_increment_offset=1
    auto_increment_increment=2
    innodb_file_per_table

  • 授权
    grant replication slave on . to repluser@’192.168.27.%’ identified by ‘centos’;

  • 连接
    CHANGE MASTER TO MASTER_HOST=’192.168.27.17’,MASTER_USER=’repluser’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000001’,
    MASTER_LOG_POS=522555;

  • 2.node2 修改配置文件
    vim /etc/my.cnf
    [mysqld]
    log-bin
    server_id=2
    auto_increment_offset=2
    auto_increment_increment=2
    innodb_file_per_table

  • 导入备份
    mysql < /app/all.sql

  • 授权
    show master logs
    grant replication slave on . to repluser@’192.168.27.%’ identified by ‘centos’;

  • 连接
    CHANGE MASTER TO MASTER_HOST=’192.168.27.7’,MASTER_USER=’repluser’,MASTER_PASSWORD=’centos’,MASTER_LOG_FILE=’mariadb-bin.000002’, MASTER_LOG_POS=245;

半同步复制

  • 默认情况下,MySQL的复制功能是异步的,异步复制可以提 供最佳的性能,主库把binlog日志发送给从库即结束,并不验 证从库是否接收完毕。这意味着当主服务器或从服务器端发生 故障时,有可能从服务器没有接收到主服务器发送过来的 binlog日志,这就会造成主服务器和从服务器的数据不一致, 甚至在恢复时造成数据的丢失

  • 半同步复制实现:

    • 主服务器配置:
      mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
      mysql> SET GLOBAL VARIABLES rpl_semi_sync_master_enabled=1; mysql> SHOW GLOBAL VARIABLES LIKE ‘%semi%’;
      mysql> SHOW GLOBAL STATUS LIKE ‘%semi%‘;
    • 从服务器配置:
      mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME
      ‘semisync_slave.so’;
      mysql> SET GLOBAL VARIABLES rpl_semi_sync_slave_enabled=1;
  • 复制过滤器: 让从节点仅复制指定的数据库,或指定数据库的指定表

  • 两种实现方式:

    • (1) 主服务器仅向二进制日志中记录与特定数据库(特定表)相关的事件
      • 问题:时间还原无法实现;不建议使用
      • binlog_do_db= 数据库白名单列表,用逗号分隔
      • binlog_ignore_db= 数据库黑名单列表,和前项不要同时使用
    • (2) 从服务器SQL_THREAD在replay中继日志中的事件时,仅读取与特定数据库(特定表)相关的事件并应用于本地
      • 问题:会造成网络及磁盘IO浪费
  • 复制过滤器从服务器上的相关设置

    • replicate_do_db= 指定复制库的白名单
    • replicate_ignore_db= 指定复制库黑名单
    • replicate_do_table= 指定复制表的白名单
    • replicate_ignore_table= 指定复制表的黑名单
    • replicate_wild_do_table= foo%.bar% 支持通配
    • replicate_wild_ignore_table=

MySQL复制加密

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

  • 配置实现:

  • Master配置
    [mysqld]
    log-bin
    server_id=1
    ssl
    ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
    ssl-cert=/etc/my.cnf.d/ssl/master.crt
    ssl-key=/etc/my.cnf.d/ssl/master.key

  • Slave配置

    • 第一种:
      [mysqld]
      og-bin
      server_id=1
      ssl
      ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
      ssl-cert=/etc/my.cnf.d/ssl/slave.crt
      ssl-key=/etc/my.cnf.d/ssl/slave.key
    • 第二种
      mysql>CHANGE MASTER TO MASTER_HOST=’MASTERIP’, MASTER_USER=’rep’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001’, MASTER_LOG_POS=245,
      MASTER_SSL=1,
      MASTER_SSL_CA = ‘/etc/my.cnf.d/ssl/cacert.pem’, MASTER_SSL_CERT = ‘/etc/my.cnf.d/ssl/slave.crt’, MASTER_SSL_KEY = ‘/etc/my.cnf.d/ssl/slave.key’;

实验:主从复制SSL加密

  • 1.搭建CA
    mkdir /etc/my.cnf.d/ssl
    cd /etc/my.cnf.d/ssl
    openssl genrsa 2048 > cakey.pem
    chmod 600 cakey.pem
    openssl req -new -x509 -key cakey.pem -days 3650 -out cacert.pem

  • 2.颁发证书
    openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr
    openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem - set_serial 01 > master.crt
    openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave.key > slave.csr
    openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem - set_serial 02 > slave.crt

  • 3.发送证书
    scp -r /etc/my.cnf.d/ssl/ MasterIP:/etc/my.cnf.d/
    scp -r /etc/my.cnf.d/ssl/ SlaveIP:/etc/my.cnf.d/

  • 4.master
    vim /etc/my.cnf

    log-bin
    server_id=1
    innodb_file_per_table
    ssl
    ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
    ssl-cert=/etc/my.cnf.d/ssl/master.crt
    ssl-key=/etc/my.cnf.d/ssl/master.key
  • 5.授权
    mysql>grant replication slave on . to repluser@’192.168.27.%’ identified by ‘centos’ require ssl;

  • 6.slave
    vim /etc/my.cnf

    server_id=2
    innodb_file_per_table
    ssl
    ssl-ca=/etc/my.cnf.d/ssl/cacert.pem 
    ssl-cert=/etc/my.cnf.d/ssl/slave.crt 
    ssl-key=/etc/my.cnf.d/ssl/slave.key 
  • 7.连接主服务器
    mysql>CHANGE MASTER TO MASTER_HOST=’192.168.27.17’, MASTER_USER=’repluser’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001’, MASTER_LOG_POS=821, MASTER_SSL=1;
  • 8.验证加密
    mysql –ssl-ca=/etc/my.cnf.d/ssl/cacert.pem –ssl-cert=/etc/my.cnf.d/ ssl/slave.crt –ssl-key=/etc/my.cnf.d/ssl/slave.key -h192.168.1.14 -umysql -p1

复制的监控和维护:

  • (1) 清理日志
    • PURGE
    • RESET MASTER 重置二进制日子,相当于都删除
  • (2) 复制监控
    • SHOW MASTER STATUS
    • SHOW BINLOG EVENTS
    • SHOW BINARY LOGS
    • SHOW SLAVE STATUS
    • SHOW PROCESSLIST
  • (3) 从服务器是否落后于主服务
    • Seconds_Behind_Master: 0
  • (4) 如何确定主从节点数据是否一致
    • percona-tools
  • (5) 数据不一致如何修复
    • 删除从数据库,重新复制

MySQL高可用

  • Master HA或多主模型
  • MMM: Multi Master MySQL,基于主从复制实现
  • MHA:Master HA,对主节点进行监控,可实现自动故障转移至其它从节点;通过提升某一从节点为新的主节点,基于主 从复制实现,还需要客户端配合实现,目前MHA主要支持一 主多从的架构,要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,另外一台充当从库,出于机器成本的考虑, 淘宝进行了改造,目前淘宝TMHA已经支持一主一从
    https://code.google.com/archive/p/mysql-master-ha/
  • Galera Cluster:wresp
    通过wresp协议在全局实现复制;任何一节点都可读写,不需要主从复制,实现多主可读可写

MHA


MHA工作原理

  • 1 从宕机崩溃的master保存二进制日志事件(binlog events)
  • 2 识别含有最新更新的slave
  • 3 应用差异的中继日志(relay log)到其他的slave
  • 4 应用从master保存的二进制日志事件(binlog events)
  • 5 提升一个slave为新的master
  • 6 使其他的slave连接新的master进行复制

MHA

  • MHA软件由两部分组成,Manager工具包和Node工具包
  • Manager工具包主要包括以下几个工具:
    • masterha_check_ssh 检测mha的ssh状态
      • masterha_check_ssh –conf=/etc/mastermha/mysql.cnf 配置文件
    • masterha_check_repl 检测mysql的复制状态
      • masterha_check_repl –conf=/etc/mastermha/mysql.cnf 配置文件
    • masterha_manger 启动mha
      • masterha_manager –conf=/etc/mastermha/mysql.cnf 配置文件
    • masterha_check_status 检测当前mha的运行状态
    • masterha_master_monitor 检测master是否宕机
    • masterha_master_switch 制故障转移(自动或手动)
    • masterha_conf_host 添加或删除配置的server信息
  • Node工具包:这些工具通常由MHA Manager的脚本触发, 无需人为操作)主要包括以下几个工具:
    • save_binary_logs 保存和复制master的二进制日志
    • apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
    • filter_mysqlbinlog 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
    • purge_relay_logs 清除中继日志(不会阻塞SQL线程)
  • 注意:为了尽可能的减少主库硬件损坏宕机造成的数据丢失, 因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制
  • 自定义扩展:
    • 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地址
  • 配置文件:
    • global配置,为各application提供默认配置
    • application配置:为每个主从复制集群

实验:MHA高可用

之前需要先按照正常主从复制来进行搭建,最好进行半同步复制

  • 基于ssh-key验证

在192.168.27.7上:
ssh-keygen
ssh-copy-id 192.168.27.7
scp -rp /root/.ssh 192.168.27.17:/root/
scp -rp /root/.ssh 192.168.27.27:/root/
scp -rp /root/.ssh 192.168.27.37:/root/

  • 配置MHA

[root@mha ~]# mkdir /etc/mastermha/
[root@mha ~]#vim /etc/mastermha/app1.cnf

[server default]
            user=mhauser
            password=centos
            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=centos
            ping_interval=1

            [server1]
            hostname=192.168.27.17
            candidate_master=1
            [server2]
            hostname=192.168.27.27
            candidate_master=1
            [server3]
            hostname=192.168.27.37

下载MHA:
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-manager-0.55-0.el6.noarch.rpm

wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-node-0.54-0.el6.noarch.rpm
安装MHA:
yum install mha4mysql-manager-0.55-0.el6.noarch.rpm mha4mysql-node-0.54-0.el6.noarch.rpm (epel)

  • master

[mysqld]
log-bin
server_id=1
innodb_file_per_table
skip_name_resolve=1

yum install mha4mysql-node-0.56-0.el6.noarch.rpm

mysql>show master logs
mysql>grant replication slave on . to repluser@’192.168.27.%’ identified by ‘centos’;
mysql>grant all on . to mhauser@’192.168.27.%’ identified by ‘centos’;

  • slave1
    [mysqld]
    server_id=2
    log-bin
    read_only
    relay_log_purge=0
    skip_name_resolve=1
    innodb_file_per_table
    yum install mha4mysql-node-0.56-0.el6.noarch.rpm

mysql>CHANGE MASTER TO MASTER_HOST=’192.168.27.17’, MASTER_USER=’repluser’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001’, MASTER_LOG_POS=245;

  • slave2

[mysqld]
server_id=3
log-bin
read_only
relay_log_purge=0
skip_name_resolve=1
innodb_file_per_table

yum install mha4mysql-node-0.56-0.el6.noarch.rpm

mysql>CHANGE MASTER TO MASTER_HOST=’192.168.27.17’, MASTER_USER=’repluser’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001’, MASTER_LOG_POS=245;

  • mha检查
    masterha_check_ssh –conf=/etc/mastermha/app1.cnf
    masterha_check_repl –conf=/etc/mastermha/app1.cnf
    masterha_manager –conf=/etc/mastermha/app1.cnf

  • 排错
    rm -f /data/mastermha/app1//app1.failover.complete
    cat /data/mastermha/app1/manager.log

Galera Cluster

  • Galera Cluster:集成了Galera插件的MySQL集群,是一种新型 的,数据不共享的,高度冗余的高可用方案,目前Galera Cluster 有两个版本,分别是Percona Xtradb Cluster及MariaDB Cluster,Galera本身是具有多主特性的,即采用multi-master的 集群架构,是一个既稳健,又在数据一致性、完整性及高性能方面 有出色表现的高可用解决方案
  • 下图图示:三个节点组成了一个集群,与 普通的主从架构不同,它们都可以作为主 节点,三个节点是对等的,称为multi- master架构,当有客户端要写入或者读取 数据时,连接哪个实例都是一样的,读到 的数据是相同的,写入某一个节点之后, 集群自己会将新数据同步到其它节点上面 ,这种架构不共享任何数据,是一种高冗余架构

Galera Cluster特点

  • 多主架构:真正的多点读写的集群,在任何时候读写数据,都 是最新的。
  • 同步复制:集群不同节点之间数据同步,没有延迟,在数据库 挂掉之后,数据不会丢失。
  • 并发复制:从节点APPLY数据时,支持并行执行,更好的性能
  • 故障切换:在出现数据库故障时,因支持多点写入,切换容易
  • 热插拔:在服务期间,如果数据库挂了,只要监控程序发现的 够快,不可服务时间就会非常少。在节点故障期间,节点本身 对集群的影响非常小
  • 自动节点克隆:在新增节点,或者停机维护时,增量数据或者 基础数据不需要人工手动备份提供,Galera Cluster会自动拉 取在线节点数据,最终集群会变为一致
  • 对应用透明:集群的维护,对应用程序是透明的

Galera Cluster工作过程

Galera Cluster

实现Galera Cluster的集群功能需要使用mariadb10.1版本以上的软件包

yum install mariadb-server -y

  • 修改配置文件:
    Vim /etc/my.cnf.d/server.cnf
[galera]
\# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.14,192.168.1.44,192.168.1.47" (为集群主机IP)
binlog_format=row

三台主机都需要这样修改

  • 启动:
    • 首次启动时,需要初始化集群,在其中一个节点上执行命令:
      • galera_new_cluster
    • 而后正常启动其它节点
      • systemctl start mysql
    • 查看集群中相关系统变量和状态变量:
      • SHOW VARIABLES LIKE ‘wsrep_%‘;
      • SHOW STATUS LIKE ‘wsrep_%‘;
      • SHOW STATUS LIKE ‘wsrep_cluster_size‘;

读写分离:

读写分离的工具有下面三种,可以去尝试

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

  • 复制的问题和解决方案:
    • (1) 数据损坏或丢失
      • Master:MHA + semi repl
      • Slave: 重新复制
    • (2) 混合使用存储引擎
      • MyISAM:不支持事务
      • InnoDB:支持事务
    • (3) 不惟一的server id
      • 重新复制
    • (4) 复制延迟
      • 需要额外的监控工具的辅助
      • 一从多主
      • 多线程复制:对多个数据库复制

性能衡量指标

  • 数据库服务衡量指标:
    • qps: query per second
    • tps: transaction per second
  • 压力测试工具:

MYSQL压力测试

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

mysqlslap示例

  • 单线程测试
    mysqlslap -a -uroot -p1
  • 多线程测试。使用–concurrency来模拟并发连接 mysqlslap -a -c 100 -uroot -p1
  • 迭代测试。用于需要多次执行测试得到平均值
    mysqlslap -a -i 10 -uroot -pmagedu
    mysqlslap —auto-generate-sql-add-autoincrement -a
    mysqlslap -a –auto-generate-sql-load-type=read
    mysqlslap -a –auto-generate-secondary-indexes=3
    mysqlslap -a –auto-generate-sql-write-number=1000
    mysqlslap –create-schema world -q “select count(*) from City” mysqlslap -a -e innodb -uroot -pmagedu
    mysqlslap -a –number-of-queries=10 -uroot -pmagedu
  • 测试同时不同的存储引擎的性能进行对比:
    mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info -uroot -p1
  • 执行一次测试,分别50和100个并发,执行1000次总查询: mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info -uroot -p1
  • 50和100个并发分别得到一次测试结果(Benchmark),并发数 越多,执行完所有查询的时间越长。为了准确起见,可以多迭 代测试几次:
    mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –debug-info -uroot -p1
数据库