乐趣区

关于数据库:谁再说学不会-MySQL-数据库就把这个给他扔过去

jiezi

大家好，我是民工哥。

又是新的一年奋斗路的开启，置信有不少人农历新年之后，必定会有所变动（跳槽加薪少不了）。所以，我把往期推送过的 MySQL 技术文章做了一个相干的整顿，根底不好的能够从最根底的学习一遍，进步的也能够从中再提取深刻一下。

码字不易，如有帮忙，请顺手 点在看 与转发朋友圈 反对一下民工哥，关注我，一起学习更多的 IT 技术常识，共同进步。

数据库是什么


数据库管理系统，简称为 DBMS（Database Management System），是用来存储数据的管理系统。

DBMS 的重要性

  • 无奈多人共享数据
  • 无奈提供操作大量数据所需的格局
  • 实现读取自动化须要编程技术能力
  • 无奈应答突发事变

DBMS 的品种

  • 层次性数据库

    • 最古老的数据库之一，因为突出的毛病，所以很少应用了

  • 关系型数据库

    • 采纳行列二维表构造来治理数据库，相似 Excel 的构造，应用专用的 SQL 语言对数据进行管制。

  • 关系数据库管理系统的常见品种

    • Oracle ==> 甲骨文
    • SQL Servce ==> 微软
    • DB2 ==> IBM
    • PostgreSQL ==> 开源
    • MySQL ==> 开源

  • 面向对象的数据库

    • XML 数据库
    • 键值存储系统
    • DB2
    • Redis
    • MongoDB

SQL 语句及其品种

  • DDL（数据定义语言）

    • create ==> 创立数据库或者表等对象
    • drop ==> 删除数据库或者表等对象
    • alter ==> 批改数据库或者表等对象的构造

  • DML（数据操作语言）

    • select ==> 查问表中数据
    • insert ==> 向表中插入数据
    • update ==> 更新表中数据
    • delete ==> 删除表中数据

  • DCL（数据管制语言）

    • commit ==> 决定对数据库中的数据进行变更
    • rollback ==> 勾销对数据库中的数据进行变更
    • grant ==> 赋予用户操作权限
    • revoke ==> 勾销用户的操作权限

SQL 的根本书写规定

  • SQL 语句要以；结尾
  • 关键字不辨别大小写，然而表中数据辨别大小写
  • 关键字大写
  • 表名的首字母大写
  • 列明等小写
  • 常数的书写形式是固定的
  • 遇到字符串、日期等类型须要用到 ”
  • 单词间须要应用空格宰割
  • 命名规定
  • 数据库和表的名称能够应用英文、数据以及下划线
  • 名称必须以英文作为结尾
  • 名称不能反复
  • 把握 SQL 这些外围知识点，进来吹牛逼再也不放心了

数据类型

  • integer

    • 数字型，然而不能寄存小数

  • char

    • 定长字符串类型，指定最大长度，有余应用空格填充

  • varchar

    • 可变长度字符串类型，指定最大长度，然而有余不填充

  • data

    • 存储日期，年 / 月 / 日

以上内容是对通用数据库以及 sql 语句相干的知识点介绍，本文不做过多的赘述，本文次要针对关系型数据库：MySQL 来进行各方面的知识点总结。

MySQL 数据库简介

MySQL 是最风行的关系型数据库管理系统，在 WEB 利用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保留在不同的表中，而不是将所有数据放在一个大仓库内，这样就减少了速度并进步了灵活性。

  • MySQL 是开源的，目前隶属于 Oracle 旗下产品。
  • MySQL 反对大型的数据库。能够解决领有上千万条记录的大型数据库。
  • MySQL 应用规范的 SQL 数据语言模式。
  • MySQL 能够运行于多个零碎上，并且反对多种语言。这些编程语言包含 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
  • MySQL 对 PHP 有很好的反对，PHP 是目前最风行的 Web 开发语言。
  • MySQL 反对大型数据库，反对 5000 万条记录的数据仓库，32 位零碎表文件最大可反对 4GB，64 位零碎反对最大的表文件为 8TB。
  • MySQL 是能够定制的，采纳了 GPL 协定，你能够批改源码来开发本人的 MySQL 零碎。

在日常工作与学习中，无论是开发、运维、还是测试，对于数据库的学习是不可避免的，同时也是日常工作的必备技术之一。在互联网公司，开源产品线比拟多，互联网企业所用的数据库占比拟重的还是 MySQL。

更多对于 MySQL 数据库的介绍，有趣味的读者能够参考官方网站的文档和这篇文章：可能是全网最好的 MySQL 重要知识点，对于 MySQL 架构的介绍能够参考：MySQL 架构总览 -> 查问执行流程 ->SQL 解析程序

MySQL 装置

MySQL 8 正式版 8.0.11 已公布，官网示意 MySQL8 要比 MySQL 5.7 快 2 倍，还带来了大量的改良和更快的性能！到底谁最牛呢？请看：MySQL 5.7 vs 8.0，哪个性能更牛？

具体的装置步骤请参阅：CentOS 下 MySQL 8.0 装置部署，超具体！，介绍几个 8.0 在关系数据库方面的次要新个性：MySQL 8.0 的 5 个新个性，太实用了！

MySQL 根底入门操作

Windows 服务
-- 启动 MySQL
net start mysql

-- 创立 Windows 服务
sc create mysql binPath= mysqld_bin_path(留神：等号与值之间有空格)
连贯与断开服务器
mysql -h 地址 -P 端口 -u 用户名 -p 明码

SHOW PROCESSLIST -- 显示哪些线程正在运行
SHOW VARIABLES -- 显示零碎变量信息
数据库操作
-- 查看以后数据库
SELECT DATABASE();

-- 显示以后工夫、用户名、数据库版本
SELECT now(), user(), version();

-- 创立库
CREATE DATABASE[IF NOT EXISTS] 数据库名 数据库选项
    数据库选项：CHARACTER SET charset_name
        COLLATE collation_name

-- 查看已有库
    SHOW DATABASES[LIKE 'PATTERN']

-- 查看以后库信息
    SHOW CREATE DATABASE 数据库名

-- 批改库的选项信息
    ALTER DATABASE 库名 选项信息

-- 删除库
    DROP DATABASE[IF EXISTS] 数据库名
        同时删除该数据库相干的目录及其目录内容
表的操作
-- 创立表
CREATE [TEMPORARY] TABLE[IF NOT EXISTS] [库名.]表名 (表的构造定义)[表选项]
每个字段必须有数据类型
最初一个字段后不能有逗号
TEMPORARY 长期表，会话完结时表主动隐没
对于字段的定义：字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- 表选项
  -- 字符集
  CHARSET = charset_name
  如果表没有设定，则应用数据库字符集
  -- 存储引擎
  ENGINE = engine_name
  表在治理数据时采纳的不同的数据结构，构造不同会导致解决形式、提供的个性操作等不同
  常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
  不同的引擎在保留表的构造和数据时采纳不同的形式
  MyISAM 表文件含意：.frm 表定义，.MYD 表数据，.MYI 表索引
  InnoDB 表文件含意：.frm 表定义，表空间数据和日志文件
  SHOW ENGINES -- 显示存储引擎的状态信息
  SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息
    -- 自增起始数
        AUTO_INCREMENT = 行数
    -- 数据文件目录
        DATA DIRECTORY = '目录'
    -- 索引文件目录
        INDEX DIRECTORY = '目录'
    -- 表正文
        COMMENT = 'string'
    -- 分区选项
        PARTITION BY ... (具体见手册)

-- 查看所有表
SHOW TABLES[LIKE 'pattern']
SHOW TABLES FROM 表名

-- 查看表机构
SHOW CREATE TABLE 表名（信息更具体）DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

-- 批改表
   -- 批改表自身的选项
    ALTER TABLE 表名 表的选项
    eg: ALTER TABLE 表名 ENGINE=MYISAM;
    -- 对表进行重命名
    RENAME TABLE 原表名 TO 新表名
    RENAME TABLE 原表名 TO 库名. 表名（可将表挪动到另一个数据库）-- RENAME 能够替换两个表名
    -- 批改表的字段机构（13.1.2. ALTER TABLE 语法）ALTER TABLE 表名 操作名
       -- 操作名
          ADD[COLUMN] 字段定义       -- 减少字段
            AFTER 字段名          -- 示意减少在该字段名前面
            FIRST               -- 示意减少在第一个
            ADD PRIMARY KEY(字段名)   -- 创立主键
            ADD UNIQUE [索引名] (字段名)-- 创立惟一索引
            ADD INDEX [索引名] (字段名) -- 创立一般索引
            DROP[COLUMN] 字段名      -- 删除字段
            MODIFY[COLUMN] 字段名 字段属性     -- 反对对字段属性进行批改，不能批改字段名(所有原有属性也需写上)
            CHANGE[COLUMN] 原字段名 新字段名 字段属性      -- 反对对字段名批改
            DROP PRIMARY KEY    -- 删除主键(删除主键前需删除其 AUTO_INCREMENT 属性)
            DROP INDEX 索引名 -- 删除索引
            DROP FOREIGN KEY 外键    -- 删除外键
-- 删除表
    DROP TABLE[IF EXISTS] 表名 ...

-- 清空表数据
    TRUNCATE [TABLE] 表名

-- 复制表构造
    CREATE TABLE 表名 LIKE 要复制的表名

-- 复制表构造和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

-- 检查表是否有谬误
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...

-- 优化表
   OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

-- 修复表
   REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

-- 剖析表
   ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

更多相干的操作根底知识点请参阅以下文章：

  • MySQL 数据库入门———罕用根底命令
  • 1047 行 MySQL 具体学习笔记（值得学习与珍藏）
  • MySQL 根底入门之常用命令介绍

MySQL 多实例配置

MySQL 数据库入门——多实例配置

MySQL 主从同步复制

复制概述

Mysql 内建的复制性能是构建大型，高性能应用程序的根底。将 Mysql 的数据分布到多个零碎下来，这种散布的机制，是通过将 Mysql 的某一台主机的数据复制到其它主机（slaves）上，并从新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并保护文件的一个索引以跟踪日志循环。这些日志能够记录发送到从服务器的更新。当一个从服务器连贯主服务器时，它告诉主服务器从服务器在日志中读取的最初一次胜利更新的地位。从服务器接管从那时起产生的任何更新，而后封闭并期待主服务器告诉新的更新。

请留神当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以防止用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的抵触。
mysql 反对的复制类型：

  • L 默认采纳基于语句的复制，效率比拟高。一旦发现没法准确复制时，会主动选着基于行的复制。
  • l5.0 开始反对
  • 采纳基于行的复制。
复制解决的问题

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

  • 数据分布 (Data distribution)
  • 负载平衡(load balancing)
  • 备份(Backups)
  • 高可用性和容错行 High availability and failover
复制如何工作

整体上来说，复制有 3 个步骤：

  • master 将扭转记录到二进制日志 (binary log) 中（这些记录叫做二进制日志事件，binary log events）；
  • slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)；
  • slave 重做中继日志中的事件，将扭转反映它本人的数据。

更多相干的更深刻的介绍参考：Mysql 主从架构的复制原理及配置详解

MySQL 复制有两种办法：
  • 传统形式：基于主库的 bin-log 将日志事件和事件地位复制到从库，从库再加以 利用来达到主从同步的目标。
  • Gtid 形式：global transaction identifiers 是基于事务来复制数据，因而也就不 依赖日志文件地位，同时又能更好的保障主从库数据一致性。
  • MySQL 数据库主从同步实战过程
  • 基于 Gtid 的 MySQL 主从同步实际
  • MySQL 主从同步架构中你不晓得的“坑”（上）
  • MySQL 主从同步架构中你不晓得的“坑”（下）
MySQL 复制有多种类型：
  • 异步复制：一个主库，一个或多个从库，数据异步同步到从库。
  • 同步复制：在 MySQL Cluster 中特有的复制形式。
  • 半同步复制：在异步复制的根底上，确保任何一个主库上的事务在提交之前至 少有一个从库曾经收到该事务并日志记录下来。
  • 提早复制：在异步复制的根底上，人为设定主库和从库的数据同步延迟时间，即保证数据提早至多是这个参数。

MySQL 主从复制提早解决方案：高可用数据库主从复制延时的解决方案

MySQL 数据备份与复原

数据备份多种形式：
  • 物理备份是指通过拷贝数据库文件的形式实现备份，这种备份形式实用于数据库很大，数据重要且须要疾速复原的数据库
  • 逻辑备份是指通过备份数据库的逻辑构造（create database/table 语句)和数据内容（insert 语句或者文本文件）的形式实现备份。这种备份形式实用于数据库不是很大，或者你须要对导出的文件做肯定的批改，又或者是心愿在另外的不同类型服务器上从新建设此数据库的状况
  • 通常状况下物理备份的速度要快于逻辑备份，另外物理备份的备份和复原粒度范畴为整个数据库或者是单个文件。对单表是否有恢复能力取决于存储引擎，比方在 MyISAM 存储引擎下每个表对应了独立的文件，能够独自复原；但对于 InnoDB 存储引擎表来说，可能每个示意对应了独立的文件，也可能表应用了共享数据文件
  • 物理备份通常要求在数据库敞开的状况下执行，但如果是在数据库运行状况下执行，则要求备份期间数据库不能批改
  • 逻辑备份的速度要慢于物理备份，是因为逻辑备份须要拜访数据库并将内容转化成逻辑备份须要的格局；通常输入的备份文件大小也要比物理备份大；另外逻辑备份也不蕴含数据库的配置文件和日志文件内容；备份和复原的粒度能够是所有数据库，也能够是单个数据库，也能够是单个表；逻辑备份须要再数据库运行的状态下执行；它的执行工具能够是 mysqldump 或者是 select … into outfile 两种形式
  • 生产数据库备份计划：高逼格企业级 MySQL 数据库备份计划
  • MySQL 数据库物理备份形式：Xtrabackup 实现数据的备份与复原
  • MySQL 定时备份：MySQL 数据库定时备份的几种形式（十分全面）

MySQL 高可用架构设计与实战

先来理解一下 MySQL 高可用架构简介：浅谈 MySQL 集群高可用架构
MySQL 高可用计划：MySQL 同步复制及高可用计划总结
官网也提供一种高可用计划：官网工具|MySQL Router 高可用原理与实战
MHA
  • MHA（Master High Availability）目前在 MySQL 高可用方面是一个绝对成熟的解决方案，该软件由两局部组成：MHA Manager（治理节点）和 MHA Node（数据节点。
  • MHA Manager: 能够独自部署在一台独立的机器上治理多个 master-slave 集群，也能够部署在一台 slave 节点上。
  • MHA Node: 行在每台 MySQL 服务器上。
  • MHA Manager 会定时探测集群中的 master 节点，当 master 呈现故障时，它能够主动将最新数据的 slave 晋升为新的 master，而后将所有其余的 slave 从新指向新的 master。整个故障转移过程对应用程序齐全通明。

MHA 高可用计划实战：MySQL 集群高可用架构之 MHA

MGR
  • Mysql Group Replication(MGR)是从 5.7.17 版本开始公布的一个全新的高可用和高扩张的 MySQL 集群服务。
  • 高一致性，基于原生复制及 paxos 协定的组复制技术，以插件形式提供统一数据安全保障；
  • 高容错性，大多数服务失常就可持续工作，主动不同节点检测资源征用抵触，按程序优先解决，内置动防脑裂机制；
  • 高扩展性，主动增加移除节点，并更新组信息；
  • 高灵活性，单主模式和多主模式。单主模式主动选主，所有更新操作在主进行；多主模式，所有 server 同时更新。

MySQL 数据库读写拆散高可用

海量数据的存储和拜访成为了零碎设计的瓶颈问题，日益增长的业务数据，无疑对数据库造成了相当大的负载，同时对于零碎的稳定性和扩展性提出很高的要求。随着工夫和业务的倒退，数据库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作的开销也会越来越大；另外，无论怎样降级硬件资源，单台服务器的资源（CPU、磁盘、内存、网络 IO、事务数、连接数）总是无限的，最终数据库所能承载的数据量、数据处理能力都将遭逢瓶颈。分表、分库和读写拆散能够无效地减小单台数据库的压力。

MySQL 读写拆散高可用架构实战案例：

ProxySQL+Mysql 实现数据库读写拆散实战

Mysql+Mycat 实现数据库主从同步与读写拆散

MySQL 性能优化

史上最全的 MySQL 高性能优化实战总结！
MySQL 索引原理：MySQL 的索引是什么？怎么优化？
  • 顾名思义，B-tree 索引应用 B -tree 的数据结构存储数据，不同的存储引擎以不同的形式应用 B -Tree 索引，比方 MyISAM 应用前缀压缩技术使得索引空间更小，而 InnoDB 则依照原数据格式存储，且 MyISAM 索引在索引中记录了对应数据的物理地位，而 InnoDB 则在索引中记录了对应的主键数值。B-Tree 通常意味着所有的值都是按顺序存储，并且每个叶子页到根的间隔雷同。
  • B-Tree 索引驱使存储引擎不再通过全表扫描获取数据，而是从索引的根节点开始查找，在根节点和两头节点都寄存了指向上层节点的指针，通过比拟节点页的值和要查找值能够找到适合的指针进入上层子节点，直到最上层的叶子节点，最终的后果就是要么找到对应的值，要么找不到对应的值。整个 B -tree 树的深度和表的大小间接相干。
  • 全键值匹配：和索引中的所有列都进行匹配，比方查找姓名为 zhang san，出生于 1982-1- 1 的人
  • 匹配最左前缀：和索引中的最右边的列进行匹配，比方查找所有姓为 zhang 的人
  • 匹配列前缀：匹配索引最右边列的结尾局部，比方查找所有以 z 结尾的姓名的人
  • 匹配范畴值：匹配索引列的范畴区域值，比方查找姓在 li 和 wang 之间的人
  • 准确匹配右边列并范畴匹配左边的列：比方查找所有姓为 Zhang，且名字以 K 结尾的人
  • 只拜访索引的查问：查问后果齐全能够通过索引取得，也叫做笼罩索引，比方查找所有姓为 zhang 的人的姓名
  • MySQL 罕用 30 种 SQL 查问语句优化办法 |
  • MySQL 太慢？试试这些诊断思路和工具
  • MySQL 性能优化的 9 种姿态，面试再也不怕了！
MySQL 表分区介绍：一文彻底搞懂 MySQL 分区
  • 能够容许在⼀个表⾥存储更多的数据，冲破磁盘限度或者⽂件零碎限度。
  • 对于从表⾥将过期或历史的数据移除在表分区很容易实现，只有将对应的分区移除即可
  • 对某些查问和批改语句来说，能够⾃动将数据范畴缩⼩到⼀个或⼏个表分区上，优化语句执⾏效率。⽽且能够通过显示指定表分区来执⾏语句，⽐如 select * from temp partition(p1,p2) where store\_id < 5;
  • 表分区是将⼀个表的数据依照⼀定的规定⽔平划分为不同的逻辑块，并别离进⾏物理存储，这个规定就叫做分区函数，能够有不同的分区规定。
  • MySQL5.7 版本能够通过 show plugins 语句查看以后 MySQL 是否⽀持表分区性能。
  • MySQL8.0 版本移除了 show plugins ⾥对 partition 的显示，但社区版本的表分区性能是默认开启的。
  • 但当表中含有主键或唯⼀键时，则每个被⽤作分区函数的字段必须是表中唯⼀键和主键的全副或⼀局部，否则就⽆法创立分区表。

MySQL 分库分表

  • 能不分就不分，1000 万以内的表，不倡议分片，通过适合的索引，读写拆散等形式，能够很好的解决性能问题。
  • 分片数量尽量少，分片尽量均匀分布在多个 DataHost 上，因为一个查问 SQL 跨分片越多，则总体性能越差，尽管要好于所有数据在一个分片的后果，只在必要的时候进 行扩容，减少分片数量。
  • 分片规定须要谨慎抉择，分片规定的抉择，须要思考数据的增长模式，数据的访 问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范畴分片，枚举分片，一致性 Hash 分片，这几种分片都有利于扩容。
  • 尽量不要在一个事务中的 SQL 逾越多个分片，分布式事务始终是个不好解决的问题。
  • 查问条件尽量优化，尽量避免 Select * 的形式，大量数据后果集下，会耗费大量 带宽和 CPU 资源，查问尽量避免返回大量后果集，并且尽量为频繁应用的查问语句建设索引。

数据库分库分表概述：数据库分库分表，何时分？怎么分？

Mysql 分库分表计划：MySQL 分库分表计划，总结的十分好！

Mysql 分库分表的思路：拯救 DBA—数据库分库分表思路及案例剖析

MySQL 性能监控

MySQL 性能监控的指标大体能够分为以下 4 大类：

  • 查问吞吐量
  • 查问提早与谬误
  • 客户端连贯与谬误
  • 缓冲池利用率

对于 MySQL 性能监控，官网也提供了相干的服务插件：MySQL-Percona，上面简略介绍一下插件的装置

[root@db01 ~]# yum -y install php php-mysql
[root@db01 ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
[root@db01 ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm
warning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing...
   1:percona-zabbix-templates-1.1.8-1 ################################# [100%]

Scripts are installed to /var/lib/zabbix/percona/scripts
Templates are installed to /var/lib/zabbix/percona/templates

最初，能够配合其它监控工具来实现对 MySQL 的性能监控。

MySQL 服务器配置插件：

  • 批改 php 脚本连贯 MySQL 的 monitor@localhost 用户

  • 批改 MySQL 的 sock 文件门路

    [root@db01 ~]# sed -i '30c $mysql_user ="monitor";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '31c $mysql_pass ="123456";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '33c $mysql_socket ="/tmp/mysql.sock";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

    测试是否可用（能够从 MySQL 中获取到监控值）

    [root@db01 ~]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
gg:12

# 确保以后文件的 属主 属组 是 zabbix，否则 zabbix 监控取值谬误。[root@db01 ~]# ll -sh /tmp/localhost-mysql_cacti_stats.txt
4.0K -rw-rw-r-- 1 zabbix zabbix 1.3K Dec 5 17:34 /tmp/localhost-mysql_cacti_stats.txt

    挪动 zabbix-agent 配置文件到 /etc/zabbix/zabbix_agentd.d/ 目录

    [root@db01 ~]# mv /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
[root@db01 ~]# systemctl restart zabbix-agent.service

    导入并配置 Zabbix 模板与主机：

默认模板监控工夫为 5 分钟（以后测试批改为 30s）同时也要批改 Zabbix 模板工夫

# 如果要批改监控获取值的工夫岂但要在 zabbix 面板批改取值工夫，bash 脚本也要批改。[root@db01 scripts]# sed -n '/TIMEFLM/p' /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`
if [`expr $TIMENOW - $TIMEFLM` -gt 300]; then   
# 这个 300 代表 300s 同时也要批改。

默认模板版本为 2.0.9，无奈在 4.0 版本应用，能够先从 3.0 版本导出，而后再导入 4.0 版本。

Zabbix 自带模板监控 MySQL 服务

其实，在理论生产过程中，还是有相干的业余监控数据库的第三方开源软件的，民工哥之前也写过相干的文章，明天收回来供大家参考：弱小的开源企业级数据库监控利器 Lepus

MySQL 管理工具

MySQL 是最宽泛应用和风行的开源数据库之一，围绕它有许多工具，能够让设计，创立和治理数据库的过程变得更加容易和便捷。然而如何抉择最适宜本人需要的工具，并不容易。这里为大家举荐：10 款 MySQL 的 GUI 工具，它们对开发人员和 DBA 来说都是不错的解决方案。

很早之前民工哥就给大家介绍过一款开源的 SQL 管理工具：主动补全、回滚！介绍一款可视化 sql 诊断利器。

明天，民工哥再给大家举荐一款 SQL 审核利器：MySQL 自动化运维工具 goinception。

可视化管理工具，大家能够试试这个：介绍一款收费好用的可视化数据库管理工具

俗话说工欲善其事，必先利其器，定期对你的 MYSQL 数据库进行一个体检，是保障数据库安全运行的重要伎俩，因为，好的工具是使你的工作效率倍增！

明天和大家分享几个 mysql 优化的工具，你能够应用它们对你的 mysql 进行一个体检，生成 awr 报告，让你从整体上把握你的数据库的性能状况。

性能优化诊断工具：别小看这几个工具！要害时能帮你疾速解决数据库瓶颈

MySQL 常见错误代码阐明

先给大家看几个实例的谬误剖析与解决方案。

  • 1.ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/data/mysql/mysql.sock’

问题剖析：可能是数据库没有启动或者是端口被防火墙禁止。

解决办法：启动数据库或者防火墙凋谢数据库监听端口。

  • 2.ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

问题剖析：明码不正确或者没有权限拜访。

解决办法：

1）批改 my.cnf 主配置文件，在 [mysqld] 下增加 skip-grant-tables，重启数据库。最初批改明码命令如下：

mysql> use mysql;
mysql> update user set password=password("123456") where user="root";

再删除刚刚增加的 skip-grant-tables 参数，再重启数据库，应用新密码即可登录。

2）从新受权，命令如下:

mysql> grant all on *.* to 'root'@'mysql-server' identified by '123456';
  • 3. 客户端报 Too many connections

问题剖析：连接数超出 Mysql 的最大连贯限度。

解决办法：

  • 1、在 my.cnf 配置文件外面减少连接数，而后重启 MySQL 服务。max_connections = 10000

  • 2、长期批改最大连接数，重启后不失效。须要在 my.cnf 外面批改配置文件，下次重启失效。

    set GLOBAL max_connections=10000;
  • 4.Warning: World-writable config file ‘/etc/my.cnf’ is ignored ERROR! MySQL is running but PID file could not be found

问题剖析：MySQL 的配置文件 /etc/my.cnf 权限不对。

解决办法：

chmod 644 /et/my.cnf
  • 5.InnoDB: Error: page 14178 log sequence number 29455369832 InnoDB: is in the future! Current system log sequence number 29455369832

问题剖析：innodb 数据文件损坏。

解决办法：批改 my.cnf 配置文件，在 [mysqld] 下增加 innodb_force_recovery=4, 启动数据库后备份数据文件，而后去掉该参数，利用备份文件复原数据。

  • 6. 从库的 Slave_IO_Running 为 NO

问题剖析：主库和从库的 server-id 值一样.

解决办法：批改从库的 server-id 的值，批改为和主库不一样，比主库低。批改完后重启，再同步即可！

  • 7. 从库的 Slave_IO_Running 为 NO 问题

问题剖析：造成从库线程为 NO 的起因会有很多，次要起因是主键抵触或者主库删除或更新数据，从库找不到记录，数据被批改导致。通常状态码报错有 1007、1032、1062、1452 等。

解决办法一：

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

解决办法二：设置用户权限，设置从库只读权限

set global read_only=true;
8.Error initializing relay log position: I/O error reading the header from the binary log

剖析问题：从库的中继日志 relay-bin 损坏.
解决办法：手工修复，从新找到同步的 binlog 和 pos 点，而后从新同步即可。

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

保护过 MySQL 的运维或 DBA 都晓得，常常会遇到的一些错误信息中有一些相似 10xx 的代码。

Replicate_Wild_Ignore_Table:
         Last_Errno: 1032
         Last_Error: Could not execute Update_rows event on table xuanzhi.test; Can't find record in'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 3704

然而，如果不深究或者之前遇到过，还真不太分明，这些代码具体的含意是什么？这也给咱们排错造成了肯定的妨碍。

所以，明天民工哥就把主从同步过程中一些常见的错误代码，它的具体阐明给大家整理出来了: 倡议珍藏备查！MySQL 常见错误代码阐明

MySQL 开发标准与应用技巧

命名标准

  • 1. 库名、表名、字段名必须应用小写字母，并采纳下划线宰割。

    • a)MySQL 有配置参数 lower_case_table_names，不可动静更改，Linux 零碎默认为 0，即库表名以理论状况存储，大小写敏感。如果是 1，以小写存储，大小写不敏感。如果是 2，以理论状况存储，但以小写比拟。
    • b)如果大小写混合应用，可能存在 abc，Abc，ABC 等多个表共存，容易导致凌乱。
    • c)字段名显示辨别大小写，但理论使⽤用不辨别，即不能够建设两个名字一样但大小写不一样的字段。
    • d)为了对立标准，库名、表名、字段名应用小写字母。

  • 2. 库名、表名、字段名禁止超过 32 个字符。

    • 库名、表名、字段名反对最多 64 个字符，但为了对立标准、易于辨识以及缩小传输量，禁止超过 32 个字符。

  • 3. 应用 INNODB 存储引擎。

    • INNODB 引擎是 MySQL5.5 版本当前的默认引擘，反对事务、行级锁，有更好的数据恢复能力、更好的并发性能，同时对多核、大内存、SSD 等硬件反对更好，反对数据热备份等，因而 INNODB 相比 MyISAM 有显著劣势。

  • 4. 库名、表名、字段名禁止应用 MySQL 保留字。

    • 当库名、表名、字段名等属性含有保留字时，SQL 语句必须用反引号援用属性名称，这将使得 SQL 语句书写、SHELL 脚本中变量的本义等变得⾮非常复杂。

  • 5. 禁止应用分区表。

    • 分区表对分区键有严格要求；分区表在表变大后，执⾏行 DDL、SHARDING、单表复原等都变得更加艰难。因而禁止应用分区表，并倡议业务端手动 SHARDING。

  • 6. 倡议应用 UNSIGNED 存储非负数值。

    • 同样的字节数，非负存储的数值范畴更大。如 TINYINT 有符号为 -128-127，无符号为 0 -255。

  • 7. 倡议应用 INT UNSIGNED 存储 IPV4。

    • 用 UNSINGED INT 存储 IP 地址占用 4 字节，CHAR(15)则占用 15 字节。另外，计算机解决整数类型比字符串类型快。应用 INT UNSIGNED 而不是 CHAR(15)来存储 IPV4 地址，通过 MySQL 函数 inet_ntoa 和 inet_aton 来进行转化。IPv6 地址目前没有转化函数，须要应用 DECIMAL 或两个 BIGINT 来存储。

例如：

SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480);
209.207.224.40

  • 8. 强烈建议应用 TINYINT 来代替 ENUM 类型。

    • ENUM 类型在须要批改或减少枚举值时，须要在线 DDL，老本较高；ENUM 列值如果含有数字类型，可能会引起默认值混同。

  • 9. 应用 VARBINARY 存储大小写敏感的变长字符串或二进制内容。

    • VARBINARY 默认辨别大小写，没有字符集概念，速度快。

  • 10.INT 类型固定占用 4 字节存储

    • 例如 INT(4)仅代表显示字符宽度为 4 位，不代表存储长度。数值类型括号前面的数字只是示意宽度而跟存储范畴没有关系，比方 INT(3)默认显示 3 位，空格补齐，超出时失常显示，Python、Java 客户端等不具备这个性能。

  • 11. 辨别应用 DATETIME 和 TIMESTAMP。

    • 存储年应用 YEAR 类型。存储日期应用 DATE 类型。存储工夫 (准确到秒) 倡议应用 TIMESTAMP 类型。
    • DATETIME 和 TIMESTAMP 都是准确到秒，优先选择 TIMESTAMP，因为 TIMESTAMP 只有 4 个字节，而 DATETIME8 个字节。同时 TIMESTAMP 具备主动赋值以及⾃自动更新的个性。留神：在 5.5 和之前的版本中，如果一个表中有多个 timestamp 列，那么最多只能有一列能具备自动更新性能。

如何应用 TIMESTAMP 的主动赋值属性?

a)主动初始化，而且自动更新：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b)只是主动初始化：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自动更新，初始化的值为 0：column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值为 0：column1 TIMESTAMP DEFAULT 0

  • 12. 索引字段均定义为 NOT NULL。

    • a)对表的每一行，每个为 NULL 的列都须要额定的空间来标识。
    • b)B 树索引时不会存储 NULL 值，所以如果索引字段能够为 NULL，索引效率会降落。
    • c)倡议用 0、非凡值或空串代替 NULL 值。

具体的可参阅以下文章

  • 值得珍藏：一份十分残缺、具体的 MySQL 标准
  • MySQL 开发标准与应用技巧总结

MySQL 高频企业面试题

学好常识，当然就得去面试，进大厂，拿高薪。然而进入面试之前，必要的筹备是必须的，刷题是其中之一。

Linux 运维必会的 100 道 MySql 面试题之（一）
Linux 运维必会的 100 道 MySql 面试题之（二）
Linux 运维必会的 100 道 MySql 面试题之（三）
Linux 运维必会的 100 道 MySql 面试题之（四）

以下内容次要受众为开发人员, 所以不波及到 MySQL 的服务部署等操作, 且内容较多, 大家筹备好急躁和瓜子矿泉水.

前一阵零碎的学习了一下 MySQL, 也有一些实际操作教训, 偶尔看到一篇和 MySQL 相干的面试文章, 发现其中的一些问题本人也答复不好, 尽管知识点大部分都晓得, 然而无奈将常识串联起来.

因而决定搞一个 MySQL 灵魂 100 问, 试着用答复问题的形式, 让本人对知识点的了解更加深刻一点.

此文不会事无巨细的从 select 的用法开始解说 mysql, 次要针对的是开发人员须要晓得的一些 MySQL 的知识点, 次要包含索引, 事务, 优化等方面, 以在面试中高频的问句模式给出答案.

  • MySQL 高频面试题，都在这了
  • 史上最全的大厂 Mysql 面试题在这里
  • MySQL 数据库面试题（2021 最新版）

MySQL 用户行为平安

  • 假如这么一个状况，你是某公司 mysql-DBA，某日忽然公司数据库中的所有被人为删了。
  • 只管有数据备份，然而因服务进行而造成的损失上千万，当初公司须要查出那个做删除操作的人。
  • 然而领有数据库操作权限的人很多，如何排查，证据又在哪？
  • 是不是感觉无能为力？
  • mysql 自身并没有操作审计的性能，那是不是意味着遇到这种状况只能自认倒霉呢？

民工哥技术之路公众号不定期更新 MySQL 技术常识体系，大家能够关注我查阅 MySQL 技术专栏 学习更多的 MySQL 常识。

也欢送点赞、转发反对，因程度无限，如有谬误请留言斧正，为感！！！

退出移动版