一、 MySQL 简介及安装
1.1、什么是数据库？
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复
制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据
量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代
数等数学概念和方法来处理数据库中的数据。
RDBMS 即关系数据库管理系统(Relational Database Management System)
的特点：
1、数据以表格的形式出现
2、每行为各种记录名称
3、每列为记录名称所对应的数据域
4、许多的行和列组成一张表单
5、若干的表单组成 database
1.2、 MySQL 数据库介绍
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 系统。
1.3、 MySQL 版本如何选择？
MySQL8.0 正式版 8.0.11 已发布，官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍，
还带来了大量的改进和更快的性能！
注意：从 MySQL 5.7 升级到 MySQL 8.0 仅支持通过使用 in-place 方式进行
升级，并且不支持从 MySQL 8.0 降级到 MySQL 5.7（或从某个 MySQL 8.0 版本降
级到任意一个更早的 MySQL 8.0 版本）。唯一受支持的替代方案是在升级之前
对数据进行备份。
MySQL 8.0 是全球最受欢迎的开源数据库的一个非常令人兴奋的新版本，全
面改进。一些关键的增强包括：
● SQL 窗口函数，公用表表达式， NOWAIT 和 SKIP LOCKED，降序索引，分
组，正则表达式，字符集，成本模型和直方图。
● JSON 扩展语法，新功能，改进排序和部分更新。使用 JSON 表函数，您
可以使用 JSON 数据的 SQL 机制。
● GIS 地理支持。空间参考系统（SRS），以及 SRS 感知空间数据类型，
空间索引和空间功能。
● 可靠性 DDL 语句已变得原子性和崩溃安全，元数据存储在单个事务数
据字典中。由 InnoDB 提供支持！
● 可观察性性能架构，信息架构，配置变量和错误记录的显着增强。
● 可管理性远程管理，撤消表空间管理和新的即时 DDL。
● 安全 OpenSSL 改进，新的默认身份验证， SQL 角色，分解超级特权，密
码强度等等。
● 性能 InnoDB 在读/写工作负载， IO 绑定工作负载和高争用“热点” 工
作负载方面明显更好。增加了资源组功能，通过将用户线程映射到 CPU，为用户
提供一个选项，以针对特定硬件上的特定工作负载进行优化。
下面简要介绍 MySQL 8 中值得关注的新特性和改进：
1、性能： MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。 MySQL 8.0 在以下方
面带来了更好的性能：读/写工作负载、 IO 密集型工作负载、以及高竞争（"hot
spot"热点竞争问题）工作负载。

2、 NoSQL： MySQL 从 5.7 版本开始提供 NoSQL 存储功能，目前在 8.0 版本中
这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 档数据库的
需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务
支持和完整的 ACID 合规性。
3、窗口函数(Window Functions)：从 MySQL 8.0 开始，新增了一个叫窗口
函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、 COUNT()
这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多
行当中。即窗口函数不需要 GROUP BY。
4、隐藏索引：在 MySQL 8.0 中，索引可以被“隐藏” 和“显示” 。
引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能
调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性
能有所下降，说明这个索引是有用的，然后将其“恢复显示” 即可；如果数据
库性能看不出变化，说明这个索引是多余的，可以考虑删掉。
5、降序索引： MySQL 8.0 为索引提供按降序方式进行排序的支持，在这种
索引中的值也会按降序的方式进行排序。
6、通用表表达式(Common Table Expressions CTE)：在复杂的查询中使用
嵌入式表时，使用 CTE 使得查询语句更清晰。
7、 UTF-8 编码：从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集。
8、 JSON： MySQL 8 大幅改进了对 JSON 的支持，添加了基于路径查询参数从
JSON 字段中抽取数据的 JSON_EXTRACT()函数，以及用于将数据分别组合到 JSON
数组和对象中的 JSON_ARRAYAGG()和 JSON_OBJECTAGG()聚合函数。
9、可靠性： InnoDB 现在支持表 DDL 的原子性，也就是 InnoDB 表上的 DDL
也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现 DDL 时部
分成功的问题，此外还支持 crash-safe 特性，元数据存储在单个事务数据字典
中。
10、高可用性(High Availability)： InnoDB 集群为您的数据库提供集成
的原生 HA 解决方案。
11、安全性：对 OpenSSL 的改进、新的默认身份验证、 SQL 角色、密码强度、
授权。
提示： 8.0.x 版本虽然很快，但是目前开发写到代码基本上是 5.7 的，一旦
使用 8.0 版本的数据库，那么很多东西都需要修改，程序会有问题。所以市面
上很多企业还是在使用 5.7 版本。
1.4、 MySQL 5.7.31 二进制版本安装
下载地址：
https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/

1、下载并上传软件至/server/tools
[root@localhost ~]# mkdir -p /server/tools
[root@localhost ~]# cd /server/tools/
[root@localhost tools]# ll mysql-5.7.31-el7-x86_64.tar.gz
-rw-r–r--. 1 root root 376537503 Dec 10 04:23
mysql-5.7.31-el7-x86_64.tar.gz
2、解压软件
[root@localhost tools]# tar zxvf mysql-5.7.31-el7-x86_64.tar.gz
[root@localhost tools]# mkdir /application
[root@localhost tools]# mv mysql-5.7.31-el7-x86_64
/application/mysql
3、用户的创建处理原始环境
[root@localhost ~]# yum remove mariadb-libs -y
[root@localhost ~]# rpm -qa | grep mariadb
[root@localhost ~]# useradd -s /sbin/nologin mysql
4、设置环境变量
[root@localhost ~]# vi /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@localhost ~]# source /etc/profile
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.31, for el7 (x86_64) using EditLine
wrapper
5、创建数据路径并授权
为了数据的安全，我们在这里添加一块新的磁盘，用于存放 mysql 数据部
分。软件部分存放在系统磁盘上面，防止机器故障导致全盘皆输。
添加一块新磁盘模拟数据盘：
#查看：
[root@localhost ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
|-sda1 8:1 0 1G 0 part /boot
-sda2 8:2 0 19G 0 part |-centos-root 253:0 0 17G 0 lvm /-centos-swap 253:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 20G 0 disk
sr0 11:0 1 4.5G 0 rom
格式化并挂载磁盘
[root@localhost ~]# mkfs.xfs /dev/sdb
[root@localhost ~]# mkdir /data
[root@localhost ~]# blkid
/dev/sdb: UUID=“4f4bed6c-4d81-434d-918d-8148a6591d84” TYPE=“xfs”
[root@localhost ~]# vi /etc/fstab
UUID=“4f4bed6c-4d81-434d-918d-8148a6591d84” /data xfs defaults 0 0
[root@localhost ~]# mount -a
[root@localhost ~]# df -h作者：

Filesystem Size Used Avail Use% Mounted on
devtmpfs 480M 0 480M 0% /dev
tmpfs 491M 0 491M 0% /dev/shm
tmpfs 491M 7.5M 484M 2% /run
tmpfs 491M 0 491M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 5.2G 12G 31% /
/dev/sda1 1014M 137M 878M 14% /boot
tmpfs 99M 0 99M 0% /run/user/0
/dev/sdb 20G 33M 20G 1% /data
6、授权
[root@localhost ~]# chown -R mysql.mysql /application/
[root@localhost ~]# chown -R mysql.mysql /application/*
[root@localhost ~]# chown -R mysql.mysql /data
7、初始化数据（创建系统数据）
[root@localhost ~]# mkdir /data/mysql/data -p
[root@localhost ~]# chown -R mysql.mysql /data
[root@localhost ~]# mysqld --initialize --user=mysql
–basedir=/application/mysql --datadir=/data/mysql/data
2020-12-10T09:34:40.753170Z 0 [Warning] TIMESTAMP with implicit
DEFAULT value is deprecated. Please use
–explicit_defaults_for_timestamp server option (see documentation for
more details).
2020-12-10T09:34:40.841402Z 0 [Warning] InnoDB: New log files
created, LSN=45790
2020-12-10T09:34:40.858333Z 0 [Warning] InnoDB: Creating foreign
key constraint system tables.
2020-12-10T09:34:40.914553Z 0 [Warning] No existing UUID has been
found, so we assume that this is the first time that this server has been
started. Generating a new UUID: ed9bc362-3aca-11eb-a144-000c296ca698.
2020-12-10T09:34:40.917019Z 0 [Warning] Gtid table is not ready to
be used. Table ‘mysql.gtid_executed’ cannot be opened.
2020-12-10T09:34:41.417720Z 0 [Warning] CA certificate ca.pem is
self signed.
2020-12-10T09:34:41.588387Z 1 [Note] A temporary password is
generated for root@localhost: )JWK=-haw1FV
说明：
–user：指定用户
–basedir：指定 mysql 软件路径
–datadir：指定数据存放路径
–initialize 参数：

对于密码复杂度进行定制： 12 位， 4 种
密码过期时间： 180
给 root@localhost 用户设置临时密码
（以下操作非必须执行）
–initialize-insecure 参数：无限制，无临时密码
[root@localhost ~]# \rm -rf /data/mysql/data/*
[root@localhost ~]# mysqld --initialize-insecure --user=mysql
–basedir=/application/mysql --datadir=/data/mysql/data
2020-12-10T09:33:58.211575Z 0 [Warning] TIMESTAMP with implicit
DEFAULT value is deprecated. Please use
–explicit_defaults_for_timestamp server option (see documentation for
more details).
2020-12-10T09:33:58.304129Z 0 [Warning] InnoDB: New log files
created, LSN=45790
2020-12-10T09:33:58.324330Z 0 [Warning] InnoDB: Creating foreign
key constraint system tables.
2020-12-10T09:33:58.379664Z 0 [Warning] No existing UUID has been
found, so we assume that this is the first time that this server has been
started. Generating a new UUID: d4417549-3aca-11eb-a086-000c296ca698.
2020-12-10T09:33:58.380257Z 0 [Warning] Gtid table is not ready to
be used. Table ‘mysql.gtid_executed’ cannot be opened.
2020-12-10T09:33:59.105064Z 0 [Warning] CA certificate ca.pem is
self signed.
2020-12-10T09:33:59.455229Z 1 [Warning] root@localhost is created
with an empty password ! Please consider switching off the
–initialize-insecure option.
8、配置文件的准备
[root@localhost ~]# cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
EOF
9、启动数据库
方法一： sys-v
[root@localhost ~]# cp
/application/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# service mysqld restart
方法二：配置 systemd 的 mysql 启动文件
[root@localhost ~]# cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# netstat -tlunp | grep mysql
tcp6 0 0 :::3306 ::😗
LISTEN 1839/mysqld
1.5、如何分析处理 MySQL 数据库无法启动
查看日志：
在哪？
/data/mysql/data/主机名.err
[ERROR] 上下文
可能情况：
/etc/my.cnf 路径不对等
/tmp/mysql.sock 文件修改过或删除过
数据目录权限不是 mysql
参数改错了
调试命令：会直接把错误信息输出到屏幕上
[root@localhost ~]# mysqld --default-file=/etc/my.cnf
1.6、管理员密码的设定
1、设定管理员密码
[root@localhost ~]# mysqladmin -uroot -p password zz123…
Enter password: #因为我们现在没有密码，直接回车即可！
mysqladmin: [Warning] Using a password on the command line interface
can be insecure.
Warning: Since password will be sent to server in plain text, use
ssl connection to ensure password safety.
2、修改密码
[root@localhost ~]# mysqladmin -uroot -p password 要修的密码
[root@localhost ~]# mysqladmin -uroot -p password 123456
Enter password:
mysqladmin: [Warning] Using a password on the command line interface
can be insecure.
Warning: Since password will be sent to server in plain text, use
ssl connection to ensure password safety.
1.7、管理员用户密码忘记了怎么办？
mysqld_safe 参数解释：
–skip-grant-tables #跳过授权表
–skip-networking #跳过远程登陆
关闭数据库
[root@bogon ~]# systemctl stop mysqld
启动数据库到维护模式
[root@bogon ~]# mysqld_safe --skip-grant-tables --skip-networking
& 登
陆并修改密码
[root@bogon ~]# mysql
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@‘localhost’ identified by ‘1’;

Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
启动数据库，正常启动验证
[root@bogon ~]# systemctl restart mysqld
[root@bogon ~]# mysql -uroot -p1
mysql> 成功！
二、 MySQL 体系结构和基础管理
2.1、 C/S 模型介绍
TCP/IP 方式（远程、本地）：
mysql -uroot -p -h 192.168.43.101 -P3306
Socket 方式（仅本地）：
mysql -uroot -p1 -S /tmp/mysql.sock
2.2、实例介绍
MySQL 实例的构成：
实例=mysqld 后台守护进程+Master Thread +干活的 Thread+预分配的内存
线程进程
公司=老板+经理+员工+办公室
2.3、 MySQL 程序运行原理
2.3.1、 MySQL 程序结构作者：
2.3.2、一条 SQL 语句的执行过程
1、连接层
（1）提供连接协议： TCP/IP 、 SOCKET
（2）提供验证：用户、密码， IP， SOCKET
（3）提供专用连接线程：接收用户 SQL，返回结果
通过以下语句可以查看到连接线程基本情况
mysql> show processlist;
2、 SQL 层
（1）接收上层传送的 SQL 语句
（2）语法验证模块：验证语句语法,是否满足 SQL_MODE
（3）语义检查：判断 SQL 语句的类型
DDL ：数据定义语言
DCL ：数据控制语言
DML ：数据操作语言
DQL：数据查询语言
…
（4）权限检查：用户对库表有没有权限
（5）解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了
就是生成多种执行方案.
（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执
行计划
代价模型：资源（CPU IO MEM）的耗损评估性能好坏
（7）执行器：根据最优执行计划，执行 SQL 语句，产生执行结果
执行结果：在磁盘的 xxxx 位置上
（8）提供查询缓存（默认是没开启的），会使用 redis tair 替代查询缓
存功能
（9）提供日志记录（日志管理章节）： binlog，默认是没开启的。
3、存储引擎层（类似于 Linux 中的文件系统）
负责根据 SQL 层执行的结果，从磁盘上拿数据。
将 16 进制的磁盘数据，交由 SQL 结构化化成表，
连接层的专用线程返回给用户。
执行过程图解：
2.4、 MySQL 逻辑存储结构
库 => Linux 目录
create database wordpress charset utf8mb4; => mkdir /wordpress
show databases; => ls /
use wordpress; => cd /wordpress
表 => Linux 的文件
列（字段） => 无
列属性 => 无
数据行（记录） => Linux 数据行
表属性（元数据） => Linux 文件属性
2.5、 MySQL 物理存储结构
库：
[root@bogon ~]# cd /data/mysql/data/
[root@bogon data]# mkdir zhang
[root@bogon data]# mysql -uroot -p1
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhang |
±-------------------+
5 rows in set (0.01 sec)
表：
MyISAM 引擎（相当于 Linux 的 ext2 文件系统）
user.frm：存储的表结构（列，列属性）
user.MYD：存储的数据记录
user.MYI：存储索引
提示： mysql 8.0 版本之后不再支持 MyISAM 引擎
InnoDB 引擎（相当于 Linux 的 XFS 文件系统）
time_zone.frm：存储的表结构（列，列属性）
time_zone.ibd：存储的数据记录和索引

ibdata1：数据字典信息
2.6、 InnoDB 段区页
InnoDB 存储引擎的逻辑存储结构中所有数据都被逻辑地存放在一个空间
中，我们称之为表空间（tablespace），空间又由段（segment）、区（extent）、
页(page）组成。
页：
InnoDB 管理存储空间的基本单位，数据页大小默认为 16KB，我们表中记录
都是存放在页中的，官方称这种存放记录的页为索引（INDEX）页。因为这种类
型的页是用来存放表数据的，也可以称为数据页。
区：
区是由连续的页组成的空间，无论页的大小怎么变，区的大小默认总是为
1MB。如连续的 64 个 16K 的页就是一个区，连续的 32 个 32K 的页也是一个区。
为了保证区中的页的连续性， InnoDB 存储引擎一次从磁盘申请 4-5 个区，在创
建一个段时就会创建一个默认的区。
段：
MySQL 的表根据存储需求会分配多个区，多个区构成的表称为段，理论上一
个表就是一个段（非分区表）。
总结：
一般情况下（非分区表）
一个表就是一个段
一个段由多个区构成
一个区在（16k）， 64 个连续的页， 1M 大小
2.7、用户和权限管理
2.7.1、作用
1、登陆 MySQL
2、管理 MySQL
2.7.2、用户的定义
用户名@‘白名单’
白名单支持的方式？
wordpress@‘10.0.0.%’

wordpress@’%’
wordpress@‘10.0.0.200’
wordpress@‘localhost’
wordpress@‘db02’
wordpress@‘10.0.0.5%’
wordpress@‘10.0.0.0/255.255.254.0’
2.7.3、用户的操作
创建用户
mysql> create user zhang@‘192.168.43.%’ identified by ‘123’;
Query OK, 0 rows affected (0.00 sec)
提示说明：
8.0 版本以前，可以自动创建用户并授权； 8.0 版本之后，必须先创建用户，
然后再授权；
授权用户：
mysql> grant all on . to zhang@‘192.168.43.%’ identified by ‘123’;
Query OK, 0 rows affected (0.00 sec)
查询用户
mysql> select user,host from mysql.user;
修改用户密码
mysql> alter user zhang@‘192.168.43.%’ identified by ‘123456’;
删除用户
mysql> drop user zhang@‘192.168.43.%’;
Query OK, 0 rows affected (0.00 sec)
2.8、权限管理
2.8.1、权限列表
ALL
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE
TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION
CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE
USER, EVENT, TRIGGER, CREATE TABLESPACE
2.8.2、授权命令
命令解析：
grant 权限 on 作用目标 to 用户 identified by 密码 with grant
option;
作用目标：
.：代表授权所有库所有表权限
wordpress.：代表授权 wordpress 库下的所有表权限
worpress.t1：代表授权 wordpress 库下的 t1 表权限
授权 zhang 用户所有库所有表的权限
mysql> grant all on . to zhang@‘192.168.43.%’ identified by ‘123’
with grant option;
Query OK, 0 rows affected (0.00 sec)
2.8.3、授权需求
1、创建一个管理员用户 root，可以通过 192.168.43 网段，管理所有数据库
grant all on . to root@‘192.168.43.%’ identified by ‘123’ with
grant option;
2、创建一个应用用户 wordpress，可以通过 192.168.43 网段，对 wordpress 库
下的所有表进行 SELECT,INSERT, UPDATE, DELETE
grant SELECT,INSERT, UPDATE, DELETE on wordpress. to
wordpress@‘192.168.43.%’ identified by ‘123’;
2.8.4、查看授权
查看当前用户拥有的权限：
show grants for wordpress@‘192.168.43.%’;
2.8.5、回收权限
收回 wordpress 用户的 delete 权限
mysql> revoke delete on wordpress.* from wordpress@‘192.168.43.%’;
mysql> show grants for wordpress@‘192.168.43.%’;
2.8.6、生产环境中如何开用户
（1）和开发沟通如何开用户
1.向领导发邮件申请是否审批，是否有邮件批复
2.询问开发对哪些库和表做操作
3.做什么操作
4.从什么地址来登录
（2）开发人员找你要 root 用户密码怎么办？

走流程拒绝他
如果是金融类的公司
（1）原则上是不允许任何非 DBA 人员持有或申请 root
（2）如果有人私下索要 root 密码，即使举报。
2.9、 MySQL 的启动和关闭
2.9.1、日常启停
总结：
以上多种方式，都可以单独启动 MySQL 服务
mysqld_safe 和 mysqld 一般是在临时维护时使用。
另外，从 Centos 7 系统开始，支持 systemd 直接调用 mysqld 的方式进行
启动数据库
2.9.2、维护性的任务
mysqld_safe --skip-grant-tables --skip-networking &
我们一般会将我们需要的参数临时加到命令行。也会读取/etc/my.cnf 的内
容,但是如果冲突,命令行优先级最高。
操作命令如下：
[root@bogon ~]# mysqld_safe &
[root@bogon ~]# mysqladmin -uroot -p1 shutdown
2.10、初始化配置
2.10.1、作用
(1)影响数据库的启动
(2)影响到客户端的功能
2.10.2、初始化配置的方法
(1)初始化配置文件(例如/etc/my.cnf)
(2)启动命令行上进行设置(例如:mysqld_safe mysqld)
(3)预编译时设置(仅限于编译安装时设置)
2.10.3、初始化配置文件的书写格式
[标签]
xxx=xxx
[标签]
xxx=xxx
2.10.4、配置文件标签的归类
服务器端:
[mysqld]
[mysqld_safe]
[server]
客户端:
[mysql]
[mysqladmin]
[mysqldump]
[client]
2.10.5、配置文件设置样板
#服务器端配置
[mysqld]
#用户
user=mysql
#软件安装目录
basedir=/application/mysql
#数据路径
datadir=/data/mysql/data
#socket 文件位置
socket=/tmp/mysql.sock
#服务器 id 号
server_id=6
#短口号
port=3306
#客户端配置
[mysql]
#socket 文件位置
socket=/tmp/mysql.sock
2.10.6、配置文件读取顺序
查看默认配置文件读取顺序
[root@bogon ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf
~/.my.cnf
测试：
[root@bogon ~]# vi ~/.my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/qq.sock
server_id=6
port=3306
[mysql]
socket=/tmp/qq.sock
[root@bogon ~]# chown mysql.mysql ~/.my.cnf
[root@bogon ~]# mysqld #启动 mysql
[root@bogon ~]# cd /tmp/
[root@bogon tmp]# ll
total 4
srwxrwxrwx. 1 mysql mysql 0 Dec 11 04:36 qq.sock
2.10.7、强制使用自定义配置文件
–defautls-file 参数可以强制使用自定义配置文件。
测试：
[root@bogon ~]# mysqld_safe --defaults-file=~/.my.cnf
[root@bogon ~]# ll /tmp/
srwxrwxrwx. 1 mysql mysql 0 Dec 11 04:42 qq.sock
2.11、 MySQL 的连接管理
2.11.1、 mysql 命令
注意:提前应该将用户授权做好
mysql> grant all on . to root@‘192.168.43.%’ identified by ‘123’;
如何知道哪些用户是远程连接上来的，哪些用户是本地连接的？
远程连接：
[root@bogon ~]# mysql -uroot -p123 -h192.168.43.101 -P3306
本地连接：
[root@bogon ~]# mysql -uroot -p123
命令总结：
TCPIP： mysql -uroot -p -h 192.168.43.101 -P3306
Socket： mysql -uroot -p -S /tmp/mysql.sock
2.11.2、客户端工具
sqlyog
navicat
这里有关客户端的工具不做太多介绍
2.12、多实例的应用
2.12.1、准备多个目录
[root@localhost ~]# mkdir -p /data/330{7,8,9}/data
2.12.2、准备配置文件
直接复制粘贴即可：
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF
cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF
2.12.3、初始化三套数据
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql
–datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql
–datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql
–datadir=/data/3309/data --basedir=/application/mysql
2.12.4、 systemd 管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
vim mysqld3307.service

修改为:

ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service

修改为:

ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service

修改为:

ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
2.12.5、授权
chown -R mysql.mysql /data/*
2.12.6、启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
2.12.7、验证多实例
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e “select @@server_id”
mysql -S /data/3308/mysql.sock -e “select @@server_id”
mysql -S /data/3309/mysql.sock -e “select @@server_id”
三、 SQL 基础应用
3.1、 MySQL 内置功能
3.1.1、连接数据库
mysql 常用参数：
-u：用户
-p：密码
-h： IP
-P：端口
-S： socket 文件
-e：免交互执行命令
<：导入 SQL 脚本
例子：

mysql -uroot -p -S /tmp/mysql.sock
mysql -uroot -p -h192.168.43.101 -P3306
-e 免交互执行 sql 语句
[root@db01 ~]# mysql -uroot -p -e “show databases;”
< 恢复数据
[root@db01 ~]# mysql -uroot -p1 < /root/world.sql
3.1.2、内置命令
help 打印 mysql 帮助
\c ctrl+c 结束上个命令运行
\q quit; exit; ctrl+d 退出 mysql
\G 将数据竖起来显示
source 恢复备份文件
3.2、 SQL 基础应用
3.2.1、 SQL 介绍
结构化的查询语言

关系型数据库通用的命令
遵循 SQL92 的标准（SQL_MODE 用于规范 SQL 语句）
3.2.2、 SQL 常用种类
DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言
3.3、数据类型、表属性、字符集
3.3.1、数据类型
1、作用
保证数据的准确性和标准性。
2、种类
数值类型：
类类型说明
整数 TINYINT 极小整数数据类型（0-255）
整数 SMALLINT 较小整数数据类型（-2^15 到 2^15-1）
整数 MEDIUMINT 中型整数数据类型
整数 INT 常规（平均）大小的整数数据类型（-2^31 到 2^31-1）
整数 BIGINT 较大整数数据类型（-2^63 到-2^63-1）
浮点数 FLOAT 小型单精度（四个字节）浮点数
浮点数 DOUBLE 常规双精度（八个字节）浮点数
浮点数 DECIMAL 包含整数部分、小数部分或同时包括二者的精确值数值
BIT BIT 位字段值
tinyint ： -128~127
int ： -2^31~231-1
说明：手机号是无法存储到 int 的。一般是使用 char 类型来存储收集号
字符类型：
类类型说明
文本 CHAR 固定长度字符串，最多为 255 个字符
文本 VARCHAR 可变长度字符串，最多为 65535 个字符
文本 TINYTEXT 可变长度字符串，最多为 255 个字符
文本 TEXT 可变长度字符串，最多为 65535 个字符
文本 MEDIUMTEXT 可变长度字符串，最多为 16777215 个字符
文本 LONGTEXT 可变长度字符串，最多为 4 294 967 295 个字符
整数 ENUM 由一组固定的合法值组成的枚举
整数 SET 由一组固定的合法值组成的集
char(11)：定长字符串类型,不管字符串长度多长，都立即分配 100 个字符
串长度的存储空间，为占满的空间使用“空格” 填充。
varchar(11)：变长字符串类型，每次存储数据之前，都要先判断一下长度，
按需分配磁盘空间。会单独申请一个字符长度的空间存储字符长度(少于 255，
如果超过 255 以上，会占用两个存储空间)
enum(‘bj’,‘tj’,‘sh’)：枚举类型，比较适合于将来此列的值是固定范围
内的特点，可以使用 enum,可以很大程度的优化我们的索引结构。
时间类型：
类型格式示例
DATE YYYY-MM-DD 2006-08-04
TIME hh:mm:ss[.uuuuuu] 12:59:02.123456
DATETIME YYYY-MM-DD
hh:mm:ss[.uuuuuu]
2006-08-04
12:59:02.1234
TIMESTAMP YYYY-MM-DD hh:mm:ss[.uuuuuu] 2006-08-04 12:59:02.12
YEAR YYYY 2006
datatime
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
timestamp
范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
二进制类型
类类型说明
二进制 BINARY 类似于 CHAR（固定长度）类型，但存储的是二进制字
节字符串，而不是非二进制字符串
二进制 VARBINARY 类似于 VARCHAR（可变长度）类型，但存储的是二进
制字节字符串，而不是非二进制字符串
BLOB TINYBLOB 最大长度为 255 个字节的 BLOB 列
BLOB BLOB 最大长度为 65 535 个字节的 BLOB 列
BLOB MEDIUDMBLOB 最大长度为 16 777 215 个字节的 BLOB 列
BLOB LONGBLOB 最大长度为 4 294 967 295 个字节的 BLOB 列
3.3.2、列属性
约束(一般建表时添加):
primary key：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，
但是可以有多个列一起构成。
not null：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以
设置默认值为 0
unique key：唯一键
列值不能重复
unsigned：无符号
针对数字列，非负数。
其他属性:
key：索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
default：默认值
列中，没有录入值时，会自动使用 default 的值填充
auto_increment：自增长
针对数字列，顺序的自动填充数据（默认是从 1 开始，将来可以设定起始
点和偏移量）
comment：注释
3.3.3、表属性
存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8
utf8mb4
3.3.4、字符集
相当于 MySQL 的密码本（编码表）
show charset; #查看 mysql 支持的所有字符集
utf8： 3 个字节
utf8mb4（建议）： 4 个字节，支持 emoji
3.4、 DDL 的应用
3.4.1、 DDL-库定义
创建库
create database zabbix charset utf8mb4 collate utf8mb4_bin;
删除库
drop database zabbix;
查询库（属于 DQL）
查看所有库
show databases;
查看库的建库语句：
show create database zabbix;
修改数据库字符集：
注意：一定是从小往大了改，比如 utf-8—>utf8mb4
目标字符集一定是源字符集的严格超级（包含的关系）
create database qq;
show create database qq;
alter database qq charset utf8mb4;
3.4.2、关于库定义规范
1.库名使用小写字符
2.库名不能以数字开头
3.不能是数据库内部的关键字
4.必须设置字符集
3.4.3、 DDL-表定义
创建表
CREATE TABLE stu(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(255) NOT NULL COMMENT ‘姓名’,
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘年龄’,
gender ENUM(‘m’,‘f’,‘n’) NOT NULL DEFAULT ‘n’ COMMENT ‘性别’,
intime DATETIME NOT NULL DEFAULT NOW() COMMENT ‘入学时间’
) ENGINE INNODB CHARSET UTF8mb4;
修改表：
在 stu 表中添加 qq 列
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT ‘qq 号’;
在 sname 后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT ’
微信号’ AFTER sname;
在 id 列前加一个新列 num
ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT ‘身份证’ FIRST;
把刚才添加的列都删掉
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
修改 sname 数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT ‘姓名’;
#MODIFY 用于修改属性，不修改列名
将 gender 改为 sex，数据类型改为 CHAR 类型
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别
';
#CHANGE 用于修改列名，然后跟列的属性。
查询表
查看所有表：
show tables;
查询建表信息：
DESC stu;
查询建表语句：
show create table stu;
创建一个表结构一样的表：
CREATE TABLE test LIKE stu;
删表：
drop table test;
3.4.4、建表规范
1.表名小写字母，不能数字开头
2.不能是保留字符，使用和业务有关的表名
3.选择合适的数据类型及长度
4.每个列设置 NOT NULL + DEFAULT，对于数据 0 填充，对于字符使用有效
字符串填充
5.每个列设置注释
6.表必须设置存储引擎和字符集
7.主键列尽量是无关列数字列，最好是自增长
8.enum 类型不要保存数字，只能是字符串类型
3.5、 DCL 的应用
grant
revoke
其实就是用户的授权和权限的回收。前面已经讲过了，这里略过！
3.6、 DML 的应用
作用：
对表中的数据行进行增、删、改
insert
针对性的录入数据：
INSERT INTO stu(id,snam,age,sex,intime) VALUES
(2,‘ls’,19,‘f’,NOW());
一次性录入多行：
INSERT INTO stu(snam,age,sex) VALUES
(‘aa’,11,‘m’),(‘bb’,12,‘f’),(‘cc’,13,‘m’);
update
指定 id 修改：
UPDATE stu SET sname=‘zhao4’ WHERE id=2;
delete
删除行，指定 id 删除： DELETE FROM stu WHERE id=3;
删除全表： DELETE FROM stu
3.7、 DQL 的应用（select）
创建测试表
CREATE TABLE books(
id int(8) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
category varchar(50) NOT NULL,
price decimal(10,2) DEFAULT NULL,
publish_time date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE INNODB CHARSET UTF8mb4;
插入测试数据：
insert into books(id,name,category,price,publish_time) values
(‘1’,‘Python1’,‘Python’,‘79.80’,‘2018-05-20’);
insert into books(id,name,category,price,publish_time) values
(‘2’,‘Python2’,‘Python’,‘79.80’,‘2018-05-20’);
insert into books(id,name,category,price,publish_time) values
(‘3’,‘Go1’,‘Go’,‘69.80’,‘2018-06-18’);
insert into books(id,name,category,price,publish_time) values
(‘4’,‘Go2’,‘Go’,‘69.80’,‘2018-06-18’);
insert into books(id,name,category,price,publish_time) values
(‘5’,‘PHP1’,‘PHP’,‘69.80’,‘2017-05-23’);
insert into books(id,name,category,price,publish_time) values
(‘6’,‘PHP2’,‘PHP’,‘69.80’,‘2017-05-23’);
insert into books(id,name,category,price,publish_time) values
(‘7’,‘Shell1’,‘Shell’,‘79.80’,‘2016-05-23’);
insert into books(id,name,category,price,publish_time) values
(‘8’,‘Shell2’,‘Shell’,‘79.80’,‘2016-05-23’);
insert into books(id,name,category,price,publish_time) values
(‘9’,‘Java1’,‘Java’,‘69.80’,‘2017-05-20’);
insert into books(id,name,category,price,publish_time) values
(‘10’,‘Java2’,‘Java’,‘69.80’,‘2017-05-20’);
select 通用语法(单表)
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit
3.7.1、单独使用
select @@xxx 查看系统参数：
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;
select 函数();：
mysql> show variables like ‘%innodb_flush%’; #模糊查询
mysql> select database();
mysql> select now(); #查看当前系统时间
3.7.2、单表子句 from
select 列,列,列 from 表
select * from 表
例子：
1.查看表中所有的信息
select id,name,category,price,publish_time from books;
或 se
lect * from books;
3.7.3、 select 配合 where 子句使用
1、 where 等值条件查询
示例 1：查找 Python1 这本书：
select * from books where name=“Python1”;
2、 where 配合比较操作符（>、 <、 >=、 <=）
示例 1：查找除了 Python1 这本书其他的所有图书
select * from books where name!=“Python1”;
示例 2：查找 id 号大于 1 的所有图书
select * from books where id>“1”;
3、 where 配合逻辑连接符(and or)
示例 1：查询 id 大于 1 和小于 3 的所有图书
select * from books where id>‘1’ and id<‘3’;
示例 2：查询 Python1 图书的价格
select price from books where category=“Python” and name=“Python1”;
4、 where 配合 like 子句模糊查询
示例 1：查询以 Python 中开头的书目录信息
select * from books where name like ‘Python%’;
注意： %不能放在前面，因为不走索引
5、 where 配合 in 语句
示例 1：查询 id 为 1 和 5 的行信息
select * from books where id in (‘1’,‘5’);
6、 where 配合 between and
示例 1：查询价格大于 60 小于 70 的图书
select * from books where price between 60 and 70;
3.7.4、 select 配合 group by + 聚合函数应用
1、作用
根据 by 后面的条件进行分组，方便统计， by 后面跟一个列或多个列
2、常用聚合函数介绍
max()最大值
MIN()最小值
AVG()平均值
COUNT()个数
SUM()总和
GROUP_CONCAT()列转行
3、 GROUP BY
将某列中有共同条件的数据行，分成一组，然后在进行聚合函数操作。
示例 1：统计每个种类图书的个数
select category,count(id) from books group by category;
示例 2：统计每个种类图书的总价格
select category,sum(price) from books group by category;
示例 3：将所有图书种类列为一行
select category,group_concat(name) from books group by category;
示例 4：列出 Python 种类的所有图书，展示为一行
select category,group_concat(name) from books where
category=“Python” group by category;
示例 5：拼接（Python：图书）
select CONCAT(category,":", group_concat(name)) from books where
category==‘Python’ group by category;
4、 select 配合 HAVING 应用
示例 1：将图书价格大于 70 的过滤出来
select name,sum(price)
from books
group by name
having sum(price)>70;
5、 select 配合 order by 应用
示例 1：将图书价格大于 50 的过滤出来，并且按照从大到小顺序排列
select name,sum(price)
from books
group by name
having sum(price)>50
order by sum(price) DESC;
6、 select 配合 limit 子句
limit m,n：跳过 m 行，显示一共 n 行
limit y offset x：跳过 x 行，显示一共 y 行
示例 1：将图书价格大于 50 的过滤出来，并且按照从大到小顺序排列，只
显示前三名
select name,sum(price) from books
group by name having sum(price)>50
order by sum(price) DESC
limit 3;
示例 2：将图书价格大于 50 的过滤出来，并且按照从大到小顺序排列，只
显示第 3 名到第 5 名
select name,sum(price) from books
group by name having sum(price)>50
order by sum(price) DESC
limit 3,2;
7、 union 和 union all
作用：多个结果集合并查询的功能
示例 1：查找 Python1 这本书的价格和 Java1 这本书的价格
select * from books where name=“Python1” union select * from books
where name=‘Java1’;
union 和 union all 的区别？
union all 不做去重复
union 会做去重操作
3.7.5、 join 多表连接查询
1、案例准备
按需求创建一下表结构：
use school
student ：学生表
sno：学号
sname：学生姓名
sage：学生年龄
ssex：学生性别
teacher ：教师表
tno：教师编号
tname：教师名字
course ：课程表
cno：课程编号
cname：课程名字
tno：教师编号
score ：成绩表
sno：学号
cno：课程编号
score：成绩
2、构建项目
drop database school;
CREATE DATABASE school CHARSET utf8mb4;
USE school作者：
CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(20) NOT NULL COMMENT ‘姓名’,
sage TINYINT UNSIGNED NOT NULL COMMENT ‘年龄’,
ssex ENUM(‘f’,‘m’) NOT NULL DEFAULT ‘m’ COMMENT ‘性别’
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT ‘课程编号’,
cname VARCHAR(20) NOT NULL COMMENT ‘课程名字’,
tno INT NOT NULL COMMENT ‘教师编号’
)ENGINE=INNODB CHARSET utf8;
CREATE TABLE sc (
sno INT NOT NULL COMMENT ‘学号’,
cno INT NOT NULL COMMENT ‘课程编号’,
score INT NOT NULL DEFAULT 0 COMMENT ‘成绩’
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT ‘教师编号’,
tname VARCHAR(20) NOT NULL COMMENT ‘教师名字’
)ENGINE=INNODB CHARSET utf8;
INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,‘zhang3’,18,‘m’);
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,‘zhang4’,18,‘m’),
(3,‘li4’,18,‘m’),
(4,‘wang5’,19,‘f’);
INSERT INTO student
VALUES
(5,‘zh4’,18,‘m’),
(6,‘zhao4’,18,‘m’),
(7,‘ma6’,19,‘f’);
INSERT INTO student(sname,sage,ssex)
VALUES
(‘oldboy’,20,‘m’),
(‘oldgirl’,20,‘f’),
(‘oldp’,25,‘m’);
INSERT INTO teacher(tno,tname) VALUES
(101,‘zhangsan’),
(102,‘hesw’),
(103,‘lisi’);
DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,‘linux’,101),
(1002,‘python’,102),
(1003,‘mysql’,103);
DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
3、练习题
关联关系：
示例 1：统计 zhang3 学习了几门课程
SELECT st.sname , COUNT(sc.cno)
FROM student AS st
JOIN
sc
ON st.sno=sc.sno
WHERE st.sname=‘zhang3’;
示例 2：查询 zhang3 学习的课程名称有哪些？
SELECT st.sname , GROUP_CONCAT(co.cname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
WHERE st.sname=‘zhang3’;
示例 3：查询 lisi 老师教的学生名
SELECT te.tname ,GROUP_CONCAT(st.sname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
JOIN teacher AS te
ON co.tno=te.tno
WHERE te.tname=‘lisi’;
示例 4：查询 lisi 所教课程的平均分数
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
WHERE te.tname=‘lisi’;
示例 5：查询每位老师所教课程的平均分，并按平均分排序
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
GROUP BY te.tname
ORDER BY AVG(sc.score) DESC ;
示例 6：查询 lisi 老师所教不及格的学生姓名
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE te.tname=‘lisi’ AND sc.score<60;
示例 7：查询所有老师所教学生不及格的信息
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE sc.score<60;

3.8、 information_schema 的基本应用
tables 视图的应用
mysql> use information_schema;
mysql> desc tables;
TABLE_SCHEMA ---->库名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行数
AVG_ROW_LENGTH ---->表中行的平均行（字节）
INDEX_LENGTH —>索引的占用空间大小（字节）
例子：
示例 1：显示整个数据库中所有库和对应表的信息
SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;
示例 2：统计所有库下的表个数
SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema;
示例 3：查询所有 innodb 引擎的表及所在的库
SELECT table_schema,table_name,ENGINE
FROM information_schema.TABLES
WHERE ENGINE=‘innodb’;
示例 4：统计 school 下的 student 表占用空间大小
公式：表的数据量=平均行长度行数+索引长度
AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH
SELECT table_name,(AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema=‘school’ AND table_name=‘student’;
示例 5：统计 school 数据库下的每张表的磁盘空间占用
SELECT
table_name,CONCAT((TABLE_ROWSAVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")
AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA=‘school’;
示例 6：统计每个库的数据量大小，并按数据量从大到小排序
SELECT
table_schema,SUM((AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH))/1024 AS
total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC;
示例 7：统计所有数据库的总的磁盘空间占用
SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWSAVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS
Total_KB
FROM information_schema.tables
GROUP BY table_schema;
3.9、 show 命令
show databases; #查看所有数据库
show tables; #查看当前库的所有表
SHOW TABLES FROM #查看某个指定库下的表
show create database world #查看建库语句
show create table world.city #查看建表语句
show grants for root@‘localhost’ #查看用户的权限信息
show charset; #查看字符集
show collation; #查看校对规则
show processlist; #查看数据库连接情况
show index from #表的索引情况
show status #数据库状态查看
SHOW STATUS LIKE ‘%lock%’; #模糊查询数据库某些状态
SHOW VARIABLES #查看所有配置信息
SHOW variables LIKE ‘%lock%’; #查看部分配置信息
show engines #查看支持的所有的存储引擎
show engine innodb status\G #查看 InnoDB 引擎相关的状态信息
show binary logs #列举所有的二进制日志
show master status #查看数据库的日志位置信息
show binlog evnets in #查看二进制日志事件
show slave status \G #查看从库状态
SHOW RELAYLOG EVENTS #查看从库 relaylog 事件信息
desc (show colums from city) #查看表的列定义信息
四、索引及执行计划
4.1、索引的作用
提供了类似于书中目录的作用,目的是为了优化查询
4.2、索引的种类
B 树索引默认使用的索引类型
Hash 索引
R 树
4.3、 B 树基于不同的查找算法分类介绍
B-tree
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)
B*Tree
4.4、 Btree 索引功能上的分类
1、辅助索引
（1）提取索引列的所有值，进行排序
（2）将拍好序的值，均匀的放在叶子节点，进一步生成枝节点和根节点
（3）在叶子节点中的值，都会对应存储主键 ID
2、聚集索引
（1） MySQL 会自动选择主键作为聚集索引列，没有主键会选择唯一键，如果都
没有会生成隐藏的。
（2） MySQL 进行存储数据时，会按照聚集索引列值得顺序，有序存储数据行
（3）聚集索引直接将原表数据页，作为叶子节点，然后提取聚集索引列向上生
成枝和根。
3、聚集索引和辅助索引的区别
（1）表中任何一个列都可以创建辅助索引，在你有需要的时候，只要名字不同
即可
（2）在一张表中，聚集索引只能有一个，一般是主键
（3）辅助索引，叶子节点只存储索引列的有序值 + 聚集索引列值。
（4）聚集索引，叶子节点存储的时有序的整行数据
（5） MySQL 的表数据存储是聚集索引组织表
4.5、辅助索引细分
1.普通的单列辅助索引
2.联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回
表
查询
3.唯一索引
索引列的值都是唯一的.
4.6、关于索引树的高度受什么影响

数据量级, 解决方法:分表,分库,分布式
索引列值过长 , 解决方法:前缀索引
数据类型:
变长长度字符串,使用了 char,解决方案:变长字符串使用 varchar
enum 类型的使用 enum (‘山东’,‘河北’,‘黑龙江’,‘吉林’,‘辽宁’,'陕西
'…)
1 2 3
4.7、索引的基本管理
4.7.1、查询索引
索引建立前：
mysql> desc books;
±-------------±--------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------------±--------------±-----±----±--------±---------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| category | varchar(50) | NO | | NULL | |
| price | decimal(10,2) | YES | | NULL | |
| publish_time | date | YES | | NULL | |
±-------------±--------------±-----±----±--------±---------------+
5 rows in set (0.00 sec)
Field：列名字
key：有没有索引,索引类型
PRI：主键索引
UNI：唯一索引
MUL：辅助索引(单列,联和,前缀)
mysql> desc books;
mysql> show index from books;
mysql> show index from books\G;
4.7.2、创建索引
1、单列的辅助索引：
mysql> alter table books add index idx_name(name);
2、多列的联合索引
mysql> alter table books add index idx_name(category,publish_time);
3、唯一索引：
mysql> alter table books add unique idx_name(category);
mysql> select count(name) from books;
mysql> select count(distinct name) from books;
4、前缀索引：
mysql> alter table books add index idx_dis(name(2));
4.7.3、删除索引
mysql> alter table books drop index uidx_dis;
4.8、压力测试准备
mysql> use test
mysql> source /tmp/t100w.sql
mysql> select count() from test.t100w;
±---------+
| count() |
±---------+
| 1030345 |
±---------+
1 row in set (0.23 sec)
4.8.1、未做优化之前测试
模拟 100 个用户访问数据库，每个用户做 2000 次查询
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query=“select * from test.t100w where k2=‘MN89’” engine=innodb
–number-of-queries=2000 -uroot -p1 -verbose
查看：
mysql> show processlist;
[root@bogon ~]# mysqlslap --defaults-file=/etc/my.cnf \

–concurrency=100 --iterations=1 --create-schema=‘test’
–query=“select * from test.t100w where k2=‘MN89’” engine=innodb

–number-of-queries=2000 -uroot -p1 -verbose
mysqlslap: [Warning] Using a password on the command line interface
can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 645.443 seconds
Minimum number of seconds to run all queries: 645.443 seconds
Maximum number of seconds to run all queries: 645.443 seconds
Number of clients running queries: 100
Average number of queries per client: 20
4.8.2、索引优化后
#创建优化索引
mysql> alter table test.t100w add index idx_k2(k2);
[root@bogon ~]# mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query=“select * from t100w where k2=‘EFBC’” engine=innodb
–number-of-queries=2000 -uroot -p1 -verbose
mysqlslap: [Warning] Using a password on the command line interface
can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 0.993 seconds
Minimum number of seconds to run all queries: 0.993 seconds
Maximum number of seconds to run all queries: 0.993 seconds
Number of clients running queries: 100
Average number of queries per client: 20
4.9、执行计划获取及分析
4.9.1、介绍
(1)获取到的是优化器选择完成的,他认为代价最小的执行计划。
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带
来的性能问题
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析
优化方案。
(2) select 获取数据的方法

全表扫描(应当尽量避免,因为性能低)
索引扫描
获取不到数据
4.9.2、执行计划获取
获取优化器选择后的执行计划
mysql> desc select * from test.t100w where id=“9000”;
mysql> explain select * from test.t100w where id=9000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t100w #查询的表
partitions: NULL
type: ALL #查询类型
possible_keys: NULL #可能走的索引
key: NULL #走的索引的名
key_len: NULL #应用索引的长度
ref: NULL
rows: 1001869 #查询结果集的长度
filtered: 10.00
Extra: Using where #额外信息
1 row in set, 1 warning (0.00 sec)
4.9.3、执行计划分析
4.9.3.1、重点关注的信息
table：查询操作的表
possible_keys：可能会走的索引
key：真正走的索引
type：索引类型
Extra：额外信息
4.9.3.2、 type 详解
从左到右性能依次变好。
ALL：全表扫描,不走索引
例子：
实例 1：查询条件列,没有索引
SELECT * FROM test.t100w WHERE k2=‘780P’;
实例 2：查询条件出现以下语句(辅助索引列)
desc select * from books where id > 5;
desc select * from books where name like ‘Python%’;
对于辅助索引来讲,!= 和 not in 等语句是不走索引的
对于主键索引列来讲,!= 和 not in 等语句是走 range
INDEX：全索引扫描
1、查询需要获取整个索引树种的值时
desc select k2 from test.t100w;
RANGE：索引范围扫描 (> < >= <= , between and ,or,in,like )
desc select * from test.t100w where id > 2000;
desc select * from test.t100w where 52 like ‘CH%’;
ref: 辅助索引等值查询
desc
select * from test.t100w where k2=‘wxuv’
union all
select * from test.t100w where k2=‘bc45’;
const(system)：主键或者唯一键的等值查询
desc select * from test.t100w where id=100;
4.9.4、 explain（desc）使用场景（面试题）
你做过哪些优化？
你用过什么优化工具？
你对索引这块怎么优化的？
题目意思：我们公司业务慢,请你从数据库的角度分析原因
mysql 出现性能问题,我总结有两种情况:
1、应急性的慢：突然夯住

应急情况:数据库 hang(卡了,资源耗尽)
处理过程:
1.show processlist; 获取到导致数据库 hang 的语句
2.explain 分析 SQL 的执行计划,有没有走索引,索引的类型情况
3.建索引,改语句
2、一段时间慢(持续性的):
1.记录慢日志 slowlog,分析 slowlog
2.explain 分析 SQL 的执行计划,有没有走索引,索引的类型情况
3.建索引,改语句
4.9.5、索引应用规范
4.9.5.1、建立索引的原则（DBA 运维规范）
1、建表必须要有主键,一般是无关列,自增长
2、经常做为 where 条件列 order by、 group by、 join on、 distinct 的条
件
3、最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化
细节来做
4、列值长度较长的索引列,我们建议使用前缀索引.
5、降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona
toolkit(xxxxx)
6、索引维护要避开业务繁忙期
7、小表不建索引
4.9.5.2、不走索引的情况（开发规范）
1、没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
2、查询结果集是原表中的大部分数据，应该是 25％以上。
3、索引本身失效，统计数据不真实
面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效
4、查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，
-，， /， ! 等)
mysql> desc select * from city where id-99=1;
5、隐式转换导致索引失效.
6、 <> ， not in 不走索引（辅助索引）
7、 like “%aa” 百分号在最前面不走
8、联合索引
五、存储引擎
5.1、简介
相当于 Linux 文件系统，只不过比文件系统强大。
5.2、功能了解
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
等
5.3、存储引擎介绍
show engines;
存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎
类型。
PerconaDB：默认是 XtraDB
MariaDB：默认是 InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点：压缩比较高,数据插入性能极高
现在很多的 NewSQL,使用比较多的功能特性。
5.4、 InnoDB 存储引擎介绍
5.4.1、优点
1、事务（Transaction）
2、 MVCC（Multi-Version Concurrency Control 多版本并发控制）
3、行级锁(Row-level Lock)
4、 ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、 Replication: Group Commit , GTID (Global Transaction ID) ,多线
程(Multi-Threads-SQL )
5.4.2、笔试题
请你列举 MySQL InnoDB 存储优点？
1）支持事务
InnoDB 最重要的一点就是支持事务，可以说这是 InnoDB 成为 MySQL 中
最流行的存储引擎的一个非常重要的原因。此外 InnoDB 还实现了 4 种隔离级
别（READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ 和 SERIALIZABLE），
使得对事务的支持更加灵活。
2）灾难恢复性好
InnoDB 通过 commit、 rollback、 crash-recovery 来保障数据的安全。
具体来说， crash-recovery 就是指如果服务器因为硬件或软件的问题而崩
溃，不管当时数据是怎样的状态，在重启 MySQL 后， InnoDB 都会自动恢复到
发生崩溃之前的状态。
3）使用行级锁
InnoDB 改变了 MyISAM 的锁机制，实现了行锁。虽然 InnoDB 的行锁机制
是通过索引来完成的，但毕竟在数据库中大部分的 SQL 语句都要使用索引来检
索数据。行锁定机制也为 InnoDB 在承受高并发压力的环境下增强了不小的竞
争力。
4）实现了缓冲处理
InnoDB 提供了专门的缓冲池，实现了缓冲管理，不仅能缓冲索引也能缓冲
数据，常用的数据可以直接从内存中处理，比从磁盘获取数据处理速度要快。
在专用数据库服务器上，通常会将最多 80％的物理 memory 分配给缓冲池。
5）支持外键
InnoDB 支持外键约束，检查外键、插入、更新和删除，以确保数据的完整
性。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显式在表
定义时指定主键， InnoDB 会为每一行生成一个 6 字节的 ROWID ，并以此作为
主键。
请你列举 InooDB 和 MyIsam 的区别？
1、存储结构
MyISAM：每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的
名字开始，扩展名指出文件类型。 .frm 文件存储表定义。数据文件的扩展名
为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者
是独立的表空间文件）， InnoDB 表的大小只受限于操作系统文件的大小，一般
为 2GB。
2、存储空间
MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默
认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用
于高速缓冲数据和索引。
3、可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。
在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用
mysqldump，在数据量达到几十 G 的时候就相对痛苦了。
4、事务支持
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比 InnoDB 类型
更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。具有事务
(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的
事务安全(transaction-safe (ACID compliant))型表。
5、 AUTO_INCREMENT
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索
引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排
序后递增。
InnoDB： InnoDB 中必须包含只有该字段的索引。引擎的自动增长列必须是
索引，如果是组合索引也必须是组合索引的第一列。
6、表锁差异
MyISAM：只支持表级锁，用户在操作 myisam 表时， select， update， delete，
insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，
可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是 innodb 的最大特色。行锁大幅度提高了多
用户并发操作的新能。但是 InnoDB 的行锁，只是在 WHERE 的主键是有效的，非
主键的 WHERE 都会锁全表的。
7、全文索引
MyISAM：支持 FULLTEXT 类型的全文索引
InnoDB：不支持 FULLTEXT 类型的全文索引，但是 innodb 可以使用 sphinx
插件支持全文索引，并且效果更好。
8、表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节
的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
9、表的具体行数
MyISAM：保存有表的总行数，如果 select count() from table;会直接取
出出该值。
InnoDB：没有保存表的总行数，如果使用 select count(*) from table；
就会遍历整个表，消耗相当大，但是在加了 wehre 条件后， myisam 和 innodb 处
理的方式都一样。
10、 CURD 操作
MyISAM：如果执行大量的 SELECT， MyISAM 是更好的选择。
InnoDB：如果你的数据执行大量的 INSERT 或 UPDATE，出于性能方面的考虑，
应该使用 InnoDB 表。 DELETE 从性能上 InnoDB 更优，但 DELETE FROM table 时，
InnoDB 不会重新建立表，而是一行一行的删除，在 innodb 上如果要清空保存

大量数据的表，最好使用 truncate table 这个命令。
11、外键
MyISAM：不支持
InnoDB：支持
通过上述的分析，基本上可以考虑使用 InnoDB 来替代 MyISAM 引擎了，原
因是 InnoDB 自身很多良好的特点，比如事务支持、存储过程、视图、行级锁
定等等，在并发很多的情况下，相信 InnoDB 的表现肯定要比 MyISAM 强很多。
另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类
型，才能最大的发挥 MySQL 的性能优势。如果不是很复杂的 Web 应用，非关键
应用，还是可以继续考虑 MyISAM 的，这个具体情况可以自己斟酌。
5.5、 InnoDB 个 MyISAM 存储引擎的替换（客户案例）
环境: centos 6.5， MySQL 5.6 版本,MyISAM 存储引擎,网站业务(LNMP),数
据量 50G 左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失。
问题分析：
1、 MyISAM 存储引擎表级锁,在高并发时,会有很高锁等待
2、 MyISAM 存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1、监控锁的情况:有很多的表锁等待
2、存储引擎查看:所有表默认是 MyISAM
解决方案:
1、升级 MySQL 版本
2、迁移所有表到新环境
3、开启双 1 安全参数
5.6、存储引擎的查看(了解)
5.6.1、查看存储引擎设置
数据库命令行操作
#查看数据库支持的引擎
mysql> show engines;
#查看数据库默认使用的引擎
mysql> SELECT @@default_storage_engine;
修改 my.cnf 配置文件来设置默认使用的数据库引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB
5.6.2、查看表存储引擎状态
#查看 t100w 表的建表语句，可以看到引擎
mysql> show create table t100w;
#查看除了’sys’,‘mysql’,‘information_schema’,‘performance_schema’
库之外的所有库所有表的引擎。
mysql> select table_schema,table_name ,engine from
information_schema.tables where table_schema not in
(‘sys’,‘mysql’,‘information_schema’,‘performance_schema’);
5.7、存储引擎的修改
5.7.1、修改存储引擎
#创建 t1 表
mysql> create table t1(id int);
#修改表的引擎
mysql> alter table t1 engine=innodb;
#查看表
mysql> show create table t1;
5.7.2、整理碎片
mysql> alter table t1 engine=innodb;
平常处理过的 MySQL 问题–碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB 存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工 truncate 表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为 truncate 方式
定期进行碎片整理
5.7.3、批量替换 zabbix 100 多张表 innodb 为 tokudb
单表替换： alter table zabbix.a engine=tokudb;
多表替换：
select concat(“alter table “,table_schema,”.”,table_name,"
engine=tokudb;") from information_schema.tables
where table_schema=‘zabbix’;
六、日志管理
6.1、错误日志
6.1.1、作用
记录启动\关闭\日常运行过程中,状态信息,警告,错误
6.1.2、错误日志配置
默认就是开启的： /数据路径下/hostname.err
手工设定：
查看当前错误日志的路径
Master [(none)]>select @@log_error;
#修改配置文件
[root@localhost ~]# vi /etc/my.cnf
log_error=/var/log/mysql.log
重启服务
[root@localhost ~]# systemctl restart mysqld
#查看当前错误日志的路径
show variables like ‘log_error’;
6.1.3、日志内容查看
主要关注[ERROR]，看上下文
6.2、二进制日志（binlog）
6.2.1、作用
（1）备份恢复必须依赖二进制日志
（2）主从环境必须依赖二进制日志
6.2.2、如何配置？
1、参数介绍
默认没有开启
server_id=6
log_bin=/data/binlog/mysql-bin
说明:
/data/binlog：提前定制好的目录,而且要有 mysql.mysql 的权限
mysql-bin：二进制日志文件名的前缀
例如: mysql-bin.000001、 mysql-bin.000002、 …
binlog_format=row —> 5.7 版本默认配置是 row，可以省略。
2、参数配置
[root@localhost ~]# vi /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin
binlog_format=row
3、创建目录和授权
[root@localhost ~]# mkdir -p /data/binlog/
[root@localhost ~]# chown -R mysql.mysql /data
4、重启生效
[root@localhost ~]# systemctl restart mysqld
6.3、二进制日志记录了什么?
6.3.1、引入
binlog 是 SQL 层的功能。记录的是变更 SQL 语句，不记录查询语句。
6.3.2、记录 SQL 语句种类
DDL：原封不动的记录当前 DDL(statement 语句方式)。
DCL：原封不动的记录当前 DCL(statement 语句方式)。
DML：只记录已经提交的事务 DML
6.3.3、 DML 三种记录方式
1、他记录的已提交的事务
2、 DML 记录格式(statement,row,mixed),通过 binlog_format=row 参数控
制
说明:
（1） statement（5.6 默认）： SBR（statement based replication），语
句模式记录日志,做什么命令,记录什么命令。
（2） ROW（5.7 默认值）： RBR（ROW based replication），行模式，记
录数据行的变化。
（3） mixed（混合）： MBR(mixed based replication)模式，以上两种模
式的混合
6.3.4、面试题
面试问题： SBR 和 RBR 什么区别?怎么选择?
SBR：可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情
况。
RBR：可读性较弱,对于范围操作日志大,不会出现记录错误.
高可用环境中的新特性要依赖于 RBR
我们公司对数据的严谨性要求较高,也用用到了新型的架构，所以选择 RBR
6.4、 event（事件）是什么？
6.4.1、事件的简介
二进制日志的最小记录单元
对于 DDL,DCL,一个语句就是一个 event
对于 DML 语句来讲：只记录已提交的事务。
例如以下列子,就被分为了 4 个 event
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
6.4.2、 event 的组成
三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在 binlog 中的相对位置号
位置号的作用是什么？
为了方便我们截取事件
6.5、二进制日志的管理
6.5.1、查看二进制日志位置
mysql> show variables like ‘%log_bin%’;
±--------------------------------±-----------------------------+
| Variable_name | Value |
±--------------------------------±-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog/mysql-bin |
| log_bin_index | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
±--------------------------------±-----------------------------+
6.5.2、查看所有已存在的二进制日志
mysql> show binary logs;
±-----------------±----------+
| Log_name | File_size |
±-----------------±----------+
| mysql-bin.000001 | 642 |
±-----------------±----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
±-----------------±----------+
| Log_name | File_size |
±-----------------±----------+
| mysql-bin.000001 | 689 |
| mysql-bin.000002 | 154 |
±-----------------±----------+
2 rows in set (0.00 sec)
6.5.3、查看正在使用的二进制日志
mysql> show master status ;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000002 | 154 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
6.5.4、查看二进制日志事件
mysql> create database binlog charset utf8mb4;
mysql> use binlog
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> show master status;
mysql> show binlog events in ‘mysql-bin.000002’;
mysql> show binlog events in ‘mysql-bin.000002’ limit 5;
6.5.5、查看二进制日志内容
[root@bogon ~]# mysqlbinlog /data/binlog/mysql-bin.000002
[root@bogon ~]# mysqlbinlog --base64-output=decode-rows -vvv
mysql-bin.000002
[root@bogon ~]# mysqlbinlog -d haoge mysql-bin.000002
6.5.6、截取二进制日志
[root@bogon ~]# mysqlbinlog --start-position=219
–stop-position=335 /data/binlog/mysql-bin.000002 >/tmp/a.sql
6.5.7、通过 binlog 恢复数据
1、模拟数据
mysql> create database haoge charset utf8mb4;
mysql> use haoge;
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> commit;
2、模拟故障
mysql> drop database haoge;
3、分析和截取 binlog（确认使用的是哪一个日志）
mysql> show master status;
mysql> show binlog events in ‘mysql-bin.000004’; #查看事件
#找到起点和终点,进行截取
[root@bogon ~]# mysqlbinlog --start-position=823
–stop-position=1335 /data/binlog/mysql-bin.000002 >/tmp/bin.sql
4、恢复 binlog
mysql> set sql_log_bin=0; #临时关闭恢复时产生的新日志
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1; set sql_log_bin=1; #改回来
5、测试
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| binlog |
| haoge |
| mysql |
| performance_schema |
| sys |
±-------------------+
6 rows in set (0.00 sec)
6.6、 binlog 的 gtid 记录模式的管理
6.6.1、 GTID 介绍
对于 binlog 中的每一个事务，都会生成一个 GTID 号码。
DDL， DCL 一个 event 就是一个事务，就会有一个 GTID 号。
DML 语句来讲， begin 到 commit，是一个事务，就是一个 GTID 号。
6.6.2、 GTID 的组成
是对于一个已提交事务的编号，并且是一个全局唯一的编号。它的官方定
义如下：
GTID = source_id ： transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
severi_uuid:TID
severi_uuid?
[root@localhost ~]# cat /data/mysql/data/auto.cnf
[auto]
server-uuid=1d79d84f-3e1a-11eb-9c89-000c296ca698
TID 是一个：自增长的数据，从 1 开始
1d79d84f-3e1a-11eb-9c89-000c296ca698:1-15
6.6.3、 GTID 的幂等性
如果拿有 GTID 的日志去恢复时，检查当前系统中是否有相同 GTID 号，有
相同的就自动跳过
会影响到 binlog 恢复和主从复制。
6.6.4、 GTID 的开启和配置
[root@localhost ~]# vi /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
[root@localhost ~]# systemctl restart mysqld
6.6.5、查看 GTID 信息
mysql> create database gtid charset utf8mb4;
mysql> show master status;
mysql> use gtid;
mysql> create table t1(id int);
mysql> show master status;
mysql> insert into t1 values(1);
mysql> commit;
mysql> show master status;
mysql> drop database gtid;
6.6.6、基于 GTID， binlog 恢复
1、截取日志
[root@localhost ~]# cd /data/binlog/
[root@localhost binlog]# mysqlbinlog
–include-gtids=‘1d79d84f-3e1a-11eb-9c89-000c296ca698:1-3’
mysql-bin.000002 >/tmp/gtid.sql
2、恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;
3、报错
ERROR 1049 (42000): Unknown database ‘gtid’
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
为什么报错?
因为幂等性的检查， 1-3 事务已经做过了。
4、正确的做法？
[root@localhost ~]# cd /data/binlog/
[root@localhost binlog]# mysqlbinlog --skip-gtids
–include-gtids=‘1d79d84f-3e1a-11eb-9c89-000c296ca698:1-3’
mysql-bin.000002 >/tmp/gtid.sql
–skip-gtids 作用：在导出时,忽略原有的 gtid 信息,恢复时生成最新的
gtid 信息
5、恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;
6.6.7、 GTID 相关的参数
–skip-gtids 作用：在导出时,忽略原有的 gtid 信息,恢复时生成最新的
gtid 信息
–include-gtids=‘1d79d84f-3e1a-11eb-9c89-000c296ca698:1-3’：获取
1-3gitd 信息
–exclude-gtids=‘1d79d84f-3e1a-11eb-9c89-000c296ca698:1-3’,‘1d79
d84f-3e1a-11eb-9c89-000c296ca698:2’：获取 1-3gitd 信息，排除 2git 信息。
6.7、慢日志（slow-log）
6.7.1、作用
记录运行较慢的语句，优化过程中常用的工具日志。
6.7.2、如何设置
参数介绍：

开关

slow_query_log=1

文件位置及名字

slow_query_log_file=/data/mysql/slow.log

设定慢查询时间

long_query_time=0.1

没走索引的语句也记录

log_queries_not_using_indexes
[root@localhost ~]# vi /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
[root@localhost ~]# systemctl restart mysqld
6.7.3、模拟慢查询
mysql> select * from t100w where k1=“aa” limit 1000,2000;
#多执行几次
6.7.4、分析慢日志
#slow.log 文件是纯文本格式的，可以直接打开。
[root@localhost ~]# mysqldumpslow -s c -t 10 /data/mysql/slow.log
6.7.5、第三方工具
Anemometer 基于 pt-query-digest 将 MySQL 慢查询可视化
下载地址：
https://www.percona.com/downloads/percona-toolkit/LATEST/
[root@localhost ~]# yum install perl-DBI perl-DBD-MySQL
perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
[root@localhost ~]# ll percona-toolkit-3.2.1-1.el7.x86_64.rpm
-rw-r–r--. 1 root root 17397876 Dec 15 07:18
percona-toolkit-3.2.1-1.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh
percona-toolkit-3.2.1-1.el7.x86_64.rpm
[root@localhost ~]# pt-query-digest /data/mysql/slow.log
七、备份恢复
7.1、运维在数据库备份恢复方面的职责
7.1.1、设计备份策略
1、备份周期
根据数据量

2、备份工具
mysqldump、 percona、 Xtrabackup、 mysqlbinlog
3、备份方式
逻辑：
全量备份： mysqldump
增量备份： binlog（flush logs ,cp）
物理备份：
全备： XBK
增量： XBK
7.1.2、日常备份检查
备份是否存在
备份空间是否够用
7.1.3、定期恢复演练（测试库）
一季度或者半年做一次数据库恢复演练
7.1.4、故障恢复
通过现有备份，能够将数据库恢复到故障之前的时间点。
7.1.5、数据迁移
1、操作系统不同的迁移
2、数据库不同版本的迁移
mysql 5.6 迁移 mysql 5.7
其他种类数据库迁移 mysql 5.7
mysql 5.7 迁移其他种类数据库
7.2、备份类型
7.2.1、热备
在数据库正常业务时，备份数据，并且能够一致性恢复（只能是 innodb）。
对业务影响非常小。
7.2.2、温备
锁表备份,只能查询不能修改（myisam）
影响到写入操作
7.2.3、冷备
关闭数据库业务,数据库没有任何变更的情况下,进行备份数据。
业务停止
7.3、备份方式及工具介绍
7.3.1、逻辑备份工具
基于 SQL 语句进行备份：
mysqldump
mysqlbinlog
7.3.2、物理备份工具
基于磁盘数据文件备份
xtrabackup(XBK)： percona 第三方
MySQL Enterprise Backup（MEB）
7.4、逻辑备份和物理备份的比较
7.4.1、 mysqldump（MDP）
优点：
1、不需要下载安装
2、备份出来的是 SQL、文本格式、可读性高、便于备份处理。
3、压缩比较高、节省备份的磁盘空间
缺点：
1、依赖于数据库引擎，需要从磁盘把数据读出。
然后转换成 SQL 进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G 以内的数据量级，可以使用 mysqldump
超过 TB 以上，我们也可能选择的是 mysqldump，配合分布式的系统
1EB=1024PB=1000000TB作者
7.4.2、 xtrabackup（XBK）
优点：
1、类似于直接 cp 数据文件，不需要管逻辑结构，相对来说性能较高。
缺点：
1、可读性差
2、压缩比低，需要更多磁盘空间
建议：
大于 100G，小于 TB
7.5、备份策略
备份方式：
全备：全库备份，备份所有数据
增量：备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份 =xtrabackup_full+xtrabackup_incr+binlog 或者
xtrabackup_full+binlog
备份周期：
根据数据量设计备份周期
比如：周日全备，周 1-周 6 增量
7.6、备份工具使用 mysqldump
7.6.1、 mysqldump（逻辑备份的客户端工具）
7.6.1.1、客户端通用参数
参数： -u：用户 -p：密码 -S： socket -h： IP 地址 -P：端口
本地备份：
mysqldump -uroot -p -S /tmp/mysql.sock
远程备份：
mysqldump -uroot -p -h 192.168.43.101 -P3306
7.6.1.2、备份专用基本参数
-A 全备参数
例子 1：
[root@localhost ~]# mkdir -p /data/backup
[root@localhost ~]# mysqldump -uroot -p1 -A >/data/backup/full.sql
补充:
1、常规备份是要加–set-gtid-purged=OFF，解决备份时的警告
[root@localhost ~]# mysqldump -uroot -p1 -A
–set-gtid-purged=OFF >/data/backup/full.sql
2、构建主从时，做的备份，不需要加这个参数
[root@localhost ~]# mysqldump -uroot -p1 -A
–set-gtid-purged=ON >/data/backup/full.sql
-B db1 db2 db3 备份多个单库
说明：生产中需要备份，生产相关的库和 MySQL 库
例子 2：
[root@localhost ~]# mysqldump -uroot -p1 -B mysql test
–set-gtid-purged=OFF >/data/backup/test.sql
备份单个或多个表
例子 3： test 数据库下的 t100w 表
[root@localhost ~]# mysqldump -uroot -p1 test t100w
–set-gtid-purged=OFF >/data/backup/t100w.sql
以上备份恢复时：必须库事先存在，并且 use 库之后，才能 source 恢复。
7.6.1.3、高级参数应用
特殊参数使用（必须要加）
-R：备份存储过程及函数
–triggers：备份触发器
-E：备份事件
例子 4：
[root@localhost ~]# mysqldump -uroot -p1 -A -R -E
–triggers >/data/backup/full.sql
-F 在备份开始时，刷新一个新 binlog 日志
例子 5：
[root@localhost ~]# mysqldump -uroot -p -A -R --triggers
-F >/data/backup/full.sql
–master-data=2
以注释的形式，保存备份开始时间点的 binlog 的状态信息
[root@localhost ~]# mysqldump -uroot -p1 -A -R --triggers
–master-data=2 >/data/backup/full.sql
功能：
1、在备份时，会自动记录，二进制日志文件名和位置号
0 默认值
1 以 change master to 命令形式，可以用作主从复制
2 以注释的形式记录，备份时刻的文件名+postion 号
2、自动锁表
3、如果配合–single-transaction，只对非 InnoDB 表进行锁表备份， InnoDB
表进行“热” 备，实际上是实现快照备份
–single-transaction
innodb 存储引擎开启热备（快照备份）功能
master-data 可以自动加锁
1、在不加–single-transaction，启动所有表的温备份，所有表都锁定
2、加上–single-transaction ,对 innodb 进行快照备份,对非 innodb 表
可以实现自动锁表功能
例子 6：备份必加参数
[root@localhost ~]# mysqldump -uroot -p1 -A -R -E --triggers
–master-data=2 --single-transaction
–set-gtid-purged=OFF >/data/backup/full.sql
–set-gtid-purged=auto
auto， on
off
使用场景：
1、 --set-gtid-purged=OFF，可以使用在日常备份参数中。
[root@localhost ~]# mysqldump -uroot -p1 -A -R -E --triggers
–master-data=2 --single-transaction
–set-gtid-purged=OFF >/data/backup/full.sql
2、 auto,on：在构建主从复制环境时需要的参数配置
[root@localhost ~]# mysqldump -uroot -p1 -A -R -E --triggers
–master-data=2 --single-transaction
–set-gtid-purged=ON >/data/backup/full.sql
–max-allowed-packet=#
[root@localhost ~]# mysqldump -uroot -p1 -A -R -E --triggers
–master-data=2 --single-transaction --set-gtid-purged=OFF
–max-allowed-packet=256M >/data/backup/full.sql
–max-allowed-packet=#
The maximum packet length to send to or receive from server.
发送到服务器或从服务器接收的最大数据包长度。
7.6.2、模拟故障案例并恢复
（1）每天全备
（2） binlog 日志是完整
（3）模拟白天的数据变化
（4）模拟下午两点误删除数据库
需求：利用全备+binlog 回复数据库误删除之前。
故障模拟及恢复：
1、模拟周一 23:00 的全备
mysqldump -uroot -p1 -A -R -E --triggers --master-data=2
–single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2、模拟白天的数据变化
Master [(none)]>create database day1 charset utf8;
Master [(none)]>use day1
Master [day1]>create table t1(id int);
Master [day1]>insert into t1 values(1),(2),(3);
Master [day1]>commit;
Master [world]>update city set countrycode=‘CHN’;
Master [world]>commit;
模拟磁盘损坏：
[root@db01 data]# \rm -rf /data/mysql/data/*
3、恢复故障
[root@db01 data]# pkill mysqld
[root@db01 data]# \rm -rf /data/mysql/data/*
4、恢复思路
1 检查备份可用性
2.从备份中获取二进制日志位置
3.根据日志位置截取需要的二进制日志
4.初始化数据库,并启动
5.恢复全备
6.恢复二进制日志
7.6.3、压缩备份并添加时间戳
例子：
mysqldump -uroot -p1 -A -R --triggers --master-data=2
–single-transaction|gzip > /backup/full_ $KaTeX parse error: Expected group after '_' at position 115: \dots > /backup/full_̲$ (date +%F-%T).sql.gz
mysqldump 备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）
set sql_log_bin=0;
source /backup/full_2018-06-28.sql
注意：
1、 mysqldump 在备份和恢复时都需要 mysql 实例启动为前提。
2、一般数据量级 100G 以内，大约 15-45 分钟可以恢复，数据量级很大很
大的时候（PB、 EB）
3、 mysqldump 是覆盖形式恢复的方法。
一般我们认为，在同数据量级，物理备份要比逻辑备份速度快。
逻辑备份的优势：
1、可读性强
2、压缩比很高
7.7、企业故障恢复案例
背景环境：
正在运行的网站系统， mysql-5.7.20 数据库，数据量 50G，日业务增量 1-5M。
备份策略：
每天 23:00 点，计划任务调用 mysqldump 执行全备脚本
故障时间点：
年底故障演练：模拟周三上午 10 点误删除数据库，并进行恢复。
思路：
1、停业务，避免数据的二次伤害

2、找一个临时库，恢复周三 23： 00 全备
3、截取周二 23： 00 到周三 10 点误删除之间的 binlog，恢复到临时库
4、测试可用性和完整性
5、
5.1、方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2、方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过 20 分钟的处理，最终业务恢复正常
故障模拟演练：
准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*
周二 23:00 全备
mysqldump -uroot -p1 -A -R --triggers --set-gtid-purged=OFF
–master-data=2 --single-transaction|gzip >/backup/full_$(date
+%F).sql.gz
模拟周二 23:00 到周三 10 点之间数据变化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
模拟故障，删除表（只是模拟，不代表生产操作）
drop database backup;
恢复过程：
准备临时数据库（多实例 3307）
systemctl start mysqld3307
准备备份：
（1）准备全备：
cd /backup
gunzip full_2018-10-17.sql.gz
（2）截取二进制日志
– CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000036’,
MASTER_LOG_POS=793;
mysqlbinlog --skip-gtids
–include-gtids=‘3ca79ab5-3e4d-11e9-a709-000c293b577e:6-7’
/data/binlog/mysql-bin.000036 >/backup/bin.sql
恢复备份到临时库
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2018-10-17.sql
source /backup/bin.sql
将故障表导出并恢复到生产
mysqldump -S /data/3307/mysql.sock backup t1 >/backup/t1.sql
mysql -uroot -p123
set sql_log_bin=0
use backup
source /backup/t1.sql;
7.8、 MySQL 物理备份工具 xtrabackup（XBK、 Xbackup）
7.8.1、安装
安装依赖包：
wget -O /etc/yum.repos.d/epel.repo
http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes
perl-DBD-MySQL libev
下载软件并安装
wget
https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.
12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.
rpm
https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-
2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_6
4.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
7.8.2、备份方式–物理备份
（1）对于非 Innodb 表（比如 myisam）是，锁表 cp 数据文件，属于一种
温备份。
（2）对于 Innodb 的表（支持事务的），不锁表，拷贝数据页，最终以数
据文件的方式保存下来，把一部分 redo 和 undo 一并备走，属于热备方式。
7.8.3、 innobackupex 使用
7.8.3.1、全备
[root@db01 backup]# innobackupex --user=root --password=123
/data/backup
自主定制备份路径名
[root@db01 backup]# innobackupex --user=root --password=123
–no-timestamp /data/backup/full
7.8.3.2、全备的恢复
准备备份（Prepared）
将 redo 进行重做，已提交的写到数据文件，未提交的使用 undo 回滚掉。
模拟了 CSR 的过程
[root@db01 ~]# innobackupex --apply-log /backup/full
恢复备份
前提：
1、被恢复的目录是空
2、被恢复的数据库的实例是关闭
systemctl stop mysqld
创建新目录：
[root@db01 backup]# mkdir /data/mysql1
数据授权
chown -R mysql.mysql /data/mysql1
恢复备份
[root@db01 full]# cp -a /backup/full/* /data/mysql1/
启动数据库
vim /etc/my.cnf
datadir=/data/mysql1
[root@db01 mysql1]# chown -R mysql.mysql /data/mysql1
systemctl start mysqld
7.8.4、恢复到周三误 drop 之前的数据状态
恢复思路：
1、挂出维护页，停止当天的自动备份脚本
2、检查备份：周日 full+周一 inc1+周二 inc2，周三的完整二进制日志
3、进行备份整理（细节），截取关键的二进制日志（从备份——误删除之
前）
4、测试库进行备份恢复及日志恢复
5、应用进行测试无误，开启业务
6、此次工作的总结
恢复过程：
1、检查备份
1afe8136-601d-11e9-9022-000c2928f5dd:7-9
2、备份整理（apply-log） +合并备份（full+inc1+inc2）
(1) 全备的整理
[root@db01 one]# innobackupex --apply-log --redo-only
/data/backup/full
(2) 合并 inc1 到 full 中
[root@db01 one]# innobackupex --apply-log --redo-only
–incremental-dir=/data/backup/inc1 /data/backup/full
(3) 合并 inc2 到 full 中
[root@db01 one]# innobackupex --apply-log
–incremental-dir=/data/backup/inc2 /data/backup/full
(4) 最后一次整理全备
[root@db01 backup]# innobackupex --apply-log /data/backup/full
3、截取周二 23:00 到 drop 之前的 binlog
[root@db01 inc2]# mysqlbinlog --skip-gtids
–include-gtids=‘1afe8136-601d-11e9-9022-000c2928f5dd:7-9’
/data/binlog/mysql-bin.000009 >/data/backup/binlog.sql
4、进行恢复
[root@db01 backup]# mkdir /data/mysql/data2 -p
[root@db01 full]# cp -a * /data/mysql/data2
[root@db01 backup]# chown -R mysql. /data/*
[root@db01 backup]# systemctl stop mysqld
vim /etc/my.cnf
datadir=/data/mysql/data2
systemctl start mysqld
Master [(none)]>set sql_log_bin=0;
Master [(none)]>source /data/backup/binlog.sql
八、主从复制基础
8.1、主从复制简介
1、基于二进制日志复制的。
2、主库的修改操作会记录二进制日志。
3、从库会请求新的二进制日志并回放，最终达到主从数据同步。
4、主从复制核心功能：
辅助备份，处理物理损坏
扩展新型的架构：高可用，高性能，分布式架构等。
8.2、主从复制前提（搭建主从的过程）
1、两台以上 mysql 实例， server_id， server_uuid 不同
2、主库开启二进制日志
3、专用的复制用户
4、保证主从开启之前的某个时间点，从库数据是和主库一致(补课)
5、告知从库，复制的 user、 passwd、 IP、 port 以及复制起点（change master
to）
6、线程（三个）： Dump thread、 IO thread、 SQL thread 开启（start slave）。
8.3、主从复制搭建
注意：需要依赖多实例环境，没有搭建好环境，请看上面多实例的搭建。
做主从之前一定要确定数据库中的数据是一致的。
1、清理主库数据
[root@localhost ~]# rm -rf /data/3307/data/*
2、重新初始化 3307
[root@localhost ~]# mysqld --initialize-insecure --user=mysql
–datadir=/data/3307/data --basedir=/application/mysql
3、修改 my.cnf 文件，开启二进制日志功能
[root@localhost ~]# vi /data/3307/my.cnf
log_bin=/data/3307/mysql-bin
4、重新启动所有节点
[root@localhost ~]# systemctl restart mysqld3307
[root@localhost ~]# systemctl restart mysqld3308
[root@localhost ~]# systemctl restart mysqld3309
5、主库中创建复制用户
[root@localhost ~]# mysql -S /data/3307/mysql.sock
mysql> grant replication slave on . to repl@‘192.168.43.%’
identified by ‘123’;
mysql> select user,host from mysql.user;
6、备份主库并恢复到从库
[root@localhost ~]# mysqldump -S /data/3307/mysql.sock -A
–master-data=2 --single-transaction -R --triggers >/root/full.sql
[root@localhost ~]# mysql -S /data/3308/mysql.sock
mysql> source /root/full.sql
[root@localhost ~]# mysqlbinlog /data/3307/mysql-bin.000003

at 123

7、告知从库关键复制信息
查看帮助信息：
mysql> help change master to
[root@localhost ~]# mysql -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’,
MASTER_USER=‘repl’,
MASTER_PASSWORD=‘123’,
MASTER_PORT=3307,
MASTER_LOG_FILE=‘mysql-bin.000003’,
MASTER_LOG_POS=123,
MASTER_CONNECT_RETRY=10;
[root@localhost ~]# mysql -S /data/3309/mysql.sock
mysql> CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’,
MASTER_USER=‘repl’,
MASTER_PASSWORD=‘123’,
MASTER_PORT=3307,
MASTER_LOG_FILE=‘mysql-bin.000003’,
MASTER_LOG_POS=123,
MASTER_CONNECT_RETRY=10;
8、开启主从专用线程
[root@localhost ~]# mysql -S /data/3308/mysql.sock
mysql> start slave;
[root@localhost ~]# mysql -S /data/3309/mysql.sock
mysql> start slave;
9、检查复制状态
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
解决的相关命令：
mysql> stop slave; 停止 slave 线程
mysql> reset slave all; 清空 slave 信息
mysql> 然后再告知从库关键复制信息即可！
10、测试
主库：
[root@localhost ~]# mysql -S /data/3307/mysql.sock -e “create
database qq;”
从库：
[root@localhost ~]# mysql -S /data/3308/mysql.sock -e “show
databases;”
8.4、主从复制原理
8.4.1、主从复制中涉及的文件
主库：
binlog
从库：
relaylog 中继日志
master.info 主库信息文件
relaylog.info relaylog 应用的信息
8.4.2、主从复制中涉及的线程
主库:
Binlog_Dump Thread： DUMP_T 线程
从库:
SLAVE_IO_THREAD： IO_T 线程
SLAVE_SQL_THREAD： SQL_T 线程
8.4.3、主从复制工作（过程）原理
主从复制过程：
1、从库执行 change master to 命令(主库的连接信息+复制的起点)
2、从库会将以上信息,记录到 master.info 文件
3、从库执行 start slave 命令,立即开启 IO_T 和 SQL_T
4、从库 IO_T，读取 master.info 文件中的信息。获取到 IP、 PORT、 User、
Pass、 binlog 的位置信息
5、从库 IO_T 请求连接主库,主库专门提供一个 DUMP_T,负责和 IO_T 交互
6、 IO_T 根据 binlog 的位置信息(mysql-bin.000004 , 444),请求主库新的
binlog
7、主库通过 DUMP_T 将最新的 binlog,通过网络 TP 给从库的 IO_T
8、IO_T 接收到新的 binlog 日志,存储到 TCP/IP 缓存,立即返回 ACK 给主库,
并更新 master.info

9、 IO_T 将 TCP/IP 缓存中数据,转储到磁盘 relaylog 中
10、 SQL_T 读取 relay.info 中的信息,获取到上次已经应用过的 relaylog
的位置信息
11、 SQL_T 会按照上次的位置点回放最新的 relaylog,再次更新 relay.info
信息
12、从库会自动 purge 应用过 relay 进行定期清理
补充说明:
一旦主从复制构建成功，主库当中发生了新的变化，都会通过 dump_T 发送
信号给 IO_T，增强了主从复制的实时性。
8.5、主从复制监控
命令:
show slave status \G
输出的重要信息如下面所示：
主库有关的信息(master.info)：
Master_Host: 192.168.43.101 #主库的 IP 地址
Master_User: repl #主库用于同步的用户
Master_Port: 3307 #主库的端口号
Connect_Retry: 10 #主从连接超时时间

Master_Log_File: mysql-bin.000004 #当前 I/O 线程正在读取的主服务
器二进制日志文件的名称。
Read_Master_Log_Pos: 609 #当前 I/O 线程正在读取的二进制日志的位置

从库 relay 应用信息有关的(relay.info)：
Relay_Log_File: db01-relay-bin.000002 #当前 slave SQL 线程正在读
取并执行的 relay log 的文件名。
Relay_Log_Pos: 320 #当前 slave SQL 线程正在读取并执行的 relay log
文件中的位置；（ Relay_Log_File 下的 Relay_Log_Pos 其实一一对应着
Relay_Master_Log_File 的 Exec_Master_Log_Pos。）
Relay_Master_Log_File: mysql-bin.000004 #当前 slave SQL 线程读取
并执行的 relay log 的文件中多数近期事件，对应的主服务器二进制日志文件
的名称。（说白点就是我 SQL 线程从 relay 日志中读取的正在执行的 sql 语句，
对应主库的 sql 语句记录在主库的哪个 binlog 日志中）
从库线程运行状态（排错）
Slave_IO_Running: Yes #I/O 线程是否被启动并成功地连接到主服务器
上。
Slave_SQL_Running: Yes #SQL 线程是否被启动。
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
过滤复制有关的信息:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
从库延时主库的时间(秒)：
Seconds_Behind_Master: 0
延时从库:
SQL_Delay: 0
SQL_Remaining_Delay: NULL
GTID 复制有关的状态信息
Retrieved_Gtid_Set: #获取到的 GTID<IO 线程>
Executed_Gtid_Set: #执行过的 GTID<SQL 线程>
Auto_Position: 0
8.6、主从延时监控及原因
8.6.1、主库方面原因
1、 binlog 写入不及时
sync_binlog=1
2、默认情况下 dump_t 是串行传输 binlog
在并发事务量大时或者大事务,由于 dump_t 是串型工作的，导致传送
日志较慢。
如何解决问题?
必须 GTID，使用 Group commit 方式。可以支持 DUMP_T 并行。
3、主库极其繁忙
慢语句
锁等待
从库个数
网络延时
8.6.2、从库方面原因
1、传统复制（Classic）中
如果主库并发事务量很大，或者出现大事务
由于从库是单 SQL 线程，导致，不管传的日志有多少，只能一次执行一个
事务。
5.6 版本，有了 GTID，可以实现多 SQL 线程，但是只能基于不同库的事务
进行并发回放。 (database)
5.7 版本中，有了增强的 GTID，增加了 seq_no，增加了新型的并发 SQL 线
程模式（logical_clock）， MTS 技术
2、主从硬件差异太大
3、主从的参数配置
4、从库和主库的索引不一致
5、版本有差异
8.6.3、主从延时的监控
从库：
show slave status\G
Seconds_Behind_Master: 0 #如果为 0，则表示主从同步不延时，反之同
步延时。
主库方面原因的监控
主库：
mysql> show master status\G;
File: mysql-bin.000001
Position: 1373
从库：
mysql> show slave status\G
Master_Log_File: mysql-bin.000001 #当前 I/O 线程正在读取的主服务
器二进制日志文件的名称。
Read_Master_Log_Pos: 1373 #当前 I/O 线程正在读取的二进制日志的位
置从
库方面原因监控：（不重要）
拿了多少：
Master_Log_File: mysql-bin.000001 #当前 I/O 线程正在读取的主服务器
二进制日志文件的名称。
Read_Master_Log_Pos: 691688 #当前 I/O 线程正在读取的二进制日志的
位置
执行了多少：
Relay_Log_File: db01-relay-bin.000004 #当前 SQL 线程当前正在读取
和执行的中继日志的位置。
Relay_Log_Pos: 690635 #在当前的中继日志中， SQL 线程已读取和执行的
位置
Exec_Master_Log_Pos: 691000 #slave SQL 线程当前执行的事前，对应在
master 相应的二进制日志中的 postion。
Relay_Log_Space: 690635
九、主从复制高级进阶
9.1、延时从库
9.1.1、介绍及配置
介绍：
主从延时是我们认为配置的一种特殊从库。人为配置从库和主库延时 N 小
时同步。
SQL 线程延时：数据已经写入 relaylog 中了， SQL 线程"慢点"运行。
一般企业建议 3-6 小时，具体看公司运维人员对于故障的反应时间。
配置：
从库上执行：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 300; #这里配置的是秒数。
可以在配置主从的时候加上这个参数 MASTER_DELAY = 300;
mysql> start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
9.1.2、延时从库的恢复思路
1、监控到数据库逻辑故障
2、停从库 SQL 线程，记录已经回放的位置点（截取日志起点）
mysql> stop slave sql_thread ;
mysql> show slave status \G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
3、截取 relaylog
起点:
mysql> show slave status \G
Relay_Log_File ,Relay_Log_Pos
终点： drop 之前的位置点
mysql> show relaylog events in ‘’
进行截取
4、模拟 SQL 线程回访日志
从库 source
5、恢复业务
情况一：就一个库的话
从库替代主库工作
情况二：
从库导出故障库，还原到主库中。
9.1.3、故障演练
主库：
create database delay charset utf8mb4;
use delay;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
从库：
1、停止从库 SQL 线程，获取 relay 的位置点
mysql> stop slave sql_thread;
mysql> show slave status \G
Relay_Master_Log_File: mysql-bin.000002
Relay_Log_Pos: 320
2、找到 relay 的截取终点
mysql> show relaylog events in ‘localhost-relay-bin.000002’;
|localhost-relay-bin.000002 | 993 | Query | 7 | 1510 | drop database delay |
3、截取 relay
[root@localhost ~]# cd /data/3308/data/
[root@localhost data]# mysqlbinlog --start-position=320
–stop-position=993 localhost-relay-bin.000002 >/tmp/relay.sql
4、恢复 relay 到从库
[root@localhost ~]# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
5、验证
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| delay |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.00 sec)
9.1.4、快速恢复测试环境
从库：
mysql -S /data/3308/mysql.sock
drop database delay ;
stop slave;
reset slave all;
主库：
mysql -S /data/3307/mysql.sock
reset master;
从库：
[root@db01 ~]# mysql -S /data/3308/mysql.sock
CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’,
MASTER_USER=‘repl’,
MASTER_PASSWORD=‘123’,
MASTER_PORT=3307,
MASTER_LOG_FILE=‘mysql-bin.000001’,
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
9.2、过滤复制
上述所提到的主从复制，几乎都是复制同步全部的数据库，但是有些情况
下可能只需要复制同步一两个指定的数据库。而这时为了复制同步一两个指定
的数据库而去同步整个数据库的话，有可能会造成主服务器的负载过大。因此
此时我们可以利用 Mysql 的复制过滤器来实现复制过滤，仅复制指定的数据库，
或者不复制指定的数据库，其实现方法有以下两种。
主库：
show master status;
binlog_do_db #相当于白名单，只对指定的 db 做复制同步
binlog_ignore_db #黑名单，不复制同步指定的 db
注意：这两个全局变量一般不同时指定，因此同时指定的话容易造成干扰
冲突。修改完成后，主服务器仅向二进制日志中记录指定的数据库相关的写操
作。但是修改 Master 的方法会使得其他数据库无法使用基于时间节点的恢复的
功能，这是一大弊端。
从库：
第二种方法，是修改从服务器的过滤变量，由从服务器的 SQL 线程来过滤
出指定的数据库或表的相关事件，并应用于本地。
mysql> show slave status \G
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
示例：
#配置从服务器只复制同步 test、 testdb 库
[root@localhost ~]# vi /data/3308/my.cnf
replicate_do_db=test
replicate_do_db=testdb
[root@localhost ~]# systemctl restart mysqld3308
#检查 3308 原有的数据库
[root@localhost ~]# mysql -S /data/3308/mysql.sock
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)
#在 3307 创建非配置的数据库
[root@localhost ~]# mysql -S /data/3307/mysql.sock
mysql> create database qq;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| qq |
| sys |
±-------------------+
5 rows in set (0.00 sec)
#在 3308 上查看是否同步过来
[root@localhost ~]# mysql -S /data/3308/mysql.sock
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)
没有被同步过来
#3307 创建 test、 testdb 数据库
[root@localhost ~]# mysql -S /data/3307/mysql.sock
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
#3308 测试
[root@localhost ~]# mysql -S /data/3308/mysql.sock
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| testdb |
±-------------------+
6 rows in set (0.00 sec)
9.3、 GTID 复制
9.3.1、介绍
GTID（Global Transaction ID）是对于一个已提交事务的唯一编号，并且
是一个全局（主从复制）唯一的编号。
它的官方定义如下：
GTID = source_id:transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是 sever_uuid，和 Server-id 区别？
核心特性：全局唯一,具备幂等性
9.3.2、 GTID 核心参数
重要参数：
gtid-mode=on #启用 gtid 类型，否则就是普通的复制架构
enforce-gtid-consistency=true #强制 GTID 的一致性
log-slave-updates=1 #slave 更新是否记入日志
9.3.3、 GTID 复制配置过程
主机名 ip 地址操作系统环境
db01 192.168.43.101 CentOS7.8 主节点
db02 192.168.43.102 CentOS7.8 从节点
db03 192.168.43.103 CentOS7.8 从节点
准备三台安装好的 mysql 服务器，操作如下：
1、下载并上传软件至/server/tools
mkdir -p /server/tools
cd /server/tools/
上传软件包
2、解压软件
tar zxvf mysql-5.7.31-el7-x86_64.tar.g
mkdir /application
mv mysql-5.7.31-el7-x86_64 /application/mysql
yum remove mariadb-libs -y
useradd -s /sbin/nologin mysql
echo “export PATH=/application/mysql/bin:$PATH” >>/etc/profile
source /etc/profile
mysql -V
3、创建数据路径并授权
mkfs.xfs /dev/sdb
[root@localhost ~]# blkid
/dev/sdb: UUID=“4f4bed6c-4d81-434d-918d-8148a6591d84” TYPE=“xfs”
[root@localhost ~]# vi /etc/fstab
UUID=“4f4bed6c-4d81-434d-918d-8148a6591d84” /data xfs defaults 0 0
mkdir /data
mount -a
4、授权
chown -R mysql.mysql /application/*
chown -R mysql.mysql /data
5、初始化数据（创建系统数据）
mkdir /data/mysql/data -p
chown -R mysql.mysql /data
mysqld --initialize-insecure --user=mysql
–basedir=/application/mysql --datadir=/data/mysql/data
6、配置文件的准备
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
EOF
7、启动数据库。
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
systemctl restart mysqld.service
提示：做完的话，如果使用的是 vm，可以做个快照操作
1、清理环境（全部节点）
mkdir /data/binlog
chown mysql.mysql /data/*
pkill mysqld
\rm -rf /data/mysql/data/*
\rm -rf /data/binlog/*
2、准备配置文件
#主库（db01）：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\d]>
EOF
#slave1（db02）：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\d]>
EOF
#slave2（db03）：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
prompt=db03 [\d]>
EOF
3、初始化数据（三台都执行）
mysqld --initialize-insecure --user=mysql
–basedir=/application/mysql --datadir=/data/mysql/data
4、启动数据库（三台都执行）
systemctl restart mysqld
5、构建主从
master:51
slave:52,53
#51：
grant replication slave on . to repl@‘192.168.43.%’ identified
by ‘123’;
#52\53：
change master to
master_host=‘192.168.43.101’,
master_user=‘repl’,
master_password=‘123’ ,
MASTER_AUTO_POSITION=1;
start slave;
9.3.4、 GTID 复制和普通复制的区别
代码区别：
CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’,
MASTER_USER=‘repl’,
MASTER_PASSWORD=‘123’,
MASTER_PORT=3307,
MASTER_LOG_FILE=‘mysql-bin.000001’,
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
change master to
master_host=‘192.168.43.101’
master_user=‘repl’,
master_password=‘123’ ,
MASTER_AUTO_POSITION=1;
start slave;
1、在主从复制环境中，主库发生过的事务，在全局都是由唯一 GTID 记录
的，更方便 Failover
2、额外功能参数（3 个）
3、 change master to 的时候不再需要 binlog 文件名和 position 号，
MASTER_AUTO_POSITION=1；
4、在复制过程中，从库不再依赖 master.info 文件，而是直接读取最后一
个 relaylog 的 GTID 号
5、 mysqldump 备份时，默认会将备份中包含的事务操作，以以下方式 SET
@GLOBAL.GTID_PURGED=‘8c49d7ec-7e78-11e8-9638-000c29ca725d:1’; 告诉从
库，我的备份中已经有以上事务，你就不用运行了，直接从下一个 GTID 开始请
求 binlog 就行。
9.4、 MHA 高可用（基于 GITD）
MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复
制架构提供了 automating master failover 功能。 MHA 在监控到 master 节点故
障时，会提升其中拥有最新数据的 slave 节点成为新的 master 节点，在此期间
MHA 会通过于其它从节点获取额外信息来避免一致性方面的问题。 MHA 还提供了
master 节点的在线切换功能，即按需切换 master/slave 节点。
MHA 是由日本人 yoshinorim（原就职于 DeNA 现就职于 FaceBook）开发的比
较成熟的 MySQL 高可用方案。 MHA 能够在 30 秒内实现故障切换，并能在故障切
换中，最大可能的保证数据一致性。目前淘宝也正在开发相似产品 TMHA，目前
已支持一主一从。
软件下载地址：
mha4mysql-node 下载地址：
https://github.com/yoshinorim/mha4mysql-node/releases
mha4mysql-master 下载地址：
https://github.com/yoshinorim/mha4mysql-manager/releases
9.4.1、搭建体验
基于上面 GTID 复制环境来做下面的 MHA 高可用
主机名 ip 地址操作系统环境
db01 192.168.43.101 CentOS7.8 主节点（node 节点）
db02 192.168.43.102 CentOS7.8 从节点（node 节点）
db03 192.168.43.103 CentOS7.8 从节点（node 节点）
manager 192.168.43.104 CentOS7.8 管理节点（manager 节点）
架构图：
1、配置关键程序软连接（db01、 db02、 db03 节点）
ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /application/mysql/bin/mysql /usr/bin/mysql
2、配置互信（db01 节点）
[root@db01 ~]# rm -rf /root/.ssh
[root@db01 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory ‘/root/.ssh’.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:0aAPp3TQljOaJ9nFzRDBzKyJ2zJOJzwMBZPA+U+sLXA root@db01
The key’s randomart image is:
±–[RSA 2048]----+
| …ooo…B+* |
| o …ooB o |
| . o+B+. |
| . E.O*=. |
| o O.=S |
| o % o |
| + * |
| . |
| |
±—[SHA256]-----+
[root@db01 ~]# cd /root/.ssh
[root@db01 .ssh]# mv id_rsa.pub authorized_keys
[root@db01 .ssh]# scp -r /root/.ssh 192.168.43.102:/root
[root@db01 .ssh]# scp -r /root/.ssh 192.168.43.103:/root
[root@db01 .ssh]# scp -r /root/.ssh 192.168.43.104:/root
各节点验证
db01:
ssh 192.168.43.101 date
ssh 192.168.43.102 date
ssh 192.168.43.103 date
ssh 192.168.43.104 date
db02:
ssh 192.168.43.101 date
ssh 192.168.43.102 date
ssh 192.168.43.103 date
ssh 192.168.43.104 date
db03:
ssh 192.168.43.101 date
ssh 192.168.43.102 date
ssh 192.168.43.103 date
ssh 192.168.43.104 date
db04:
ssh 192.168.43.101 date
ssh 192.168.43.102 date
ssh 192.168.43.103 date
ssh 192.168.43.104 date
3、安装软件包（全部节点）
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
4、在 db01 主库中创建 mha 需要的用户（db01 节点）
grant all privileges on . to mha@‘192.168.43.%’ identified by
‘mha’;
5、 Manager 软件安装（manager 节点）
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch
perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
6、配置文件准备（manager 节点）
#创建配置文件目录
mkdir -p /etc/mha
#创建日志目录
mkdir -p /var/log/mha/app1
#编辑 mha 配置文件
cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
[server1]
hostname=192.168.43.101
port=3306
[server2]
hostname=192.168.43.102
port=3306
[server3]
hostname=192.168.43.103
port=3306
EOF
7、状态检查（manager 节点）
masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf
8、开启 MHA（manager 节点）
nohup masterha_manager --conf=/etc/mha/app1.cnf
–remove_dead_master_conf --ignore_last_failover < /dev/null>
/var/log/mha/app1/manager.log 2>&1 &
9、查看 MHA 状态（manager 节点）
[root@manager ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:2482) is running(0:PING_OK), master:192.168.43.101
9.4.2、 MHA 软件的构成
Manager 工具包主要包括以下几个工具：
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
masterha_manger：启动 MHA
masterha_check_ssh：检查 MHA 的 SSH 配置状况
masterha_check_repl：检查 MySQL 复制状况
masterha_master_monitor：检测 master 是否宕机
masterha_check_status：检测当前 MHA 运行状态
masterha_master_switch：控制故障转移（自动或者手动）
masterha_conf_host：添加或删除配置的 server 信息
Node 工具包主要包括以下几个工具：
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
这些工具通常由 MHA Manager 的脚本触发，无需人为操作
save_binary_logs：保存和复制 master 的二进制日志
apply_diff_relay_logs：识别差异的中继日志事件并将其差异的事件应用
于其他的
purge_relay_logs：清除中继日志（不会阻塞 SQL 线程）
9.4.3、 Manager 额外参数介绍
说明：
主库宕机谁来接管？
1、所有从节点日志都是一致的，默认会以配置文件的顺序去选择一个新主。
2、从节点日志不一致，自动选择最接近于主库的从库
3、如果对于某节点设定了权重（candidate_master=1），权重节点会优先
选择。
但是此节点日志量落后主库 100M 日志的话，也不会被选择。可以配合
check_repl_delay=0，关闭日志量的检查，强制选择候选节点。
(1) ping_interval=1
#设置监控主库，发送 ping 包的时间间隔，尝试三次没有回应的时候自动
进行 failover
(2) candidate_master=1
#设置为候选 master，如果设置该参数以后，发生主从切换以后将会将此从
库提升为主库，即使这个主库不是集群中事件最新的 slave
(3)check_repl_delay=0
#默认情况下如果一个 slave 落后 master 100M 的 relay logs 的话，
MHA 将不会选择该 slave 作为一个新的 master，因为对于这个 slave 的恢
复需要花费很长时间，通过设置 check_repl_delay=0,MHA 触发切换在选择一个
新的 master 的时候将会忽略复制延时，这个参数对于设置了
candidate_master=1 的主机非常有用，因为这个候选主在切换的过程中一定是
新的 master
9.4.4、 MHA 的 Failover 如何实现？
从启动—>故障—>转移—>业务恢复
1、 MHA 通过 masterha_manger 脚本启动 MHA 的功能。
2、在 manager 启动之前,会自动检查 ssh 互信（masterha_check_ssh）和
主从状态（masterha_check_repl）。
3、 MHA-manager 通过 masterha_master_monitor 脚本（每隔 ping_interval
秒）。
4、masterha_master_monitor探测主库3次无心跳之后,就认为主库宕机了。
5、进行选主过程
算法一:
读取配置文件中是否有强制选主的参数?
candidate_master=1
check_repl_delay=0
算法二：
自动判断所有从库的日志量.将最接近主库数据的从库作为新主.
算法三：
按照配置文件先后顺序的进行选新主.
6、数据补偿
判断主库 SSH 的连通性
情况一: SSH 能连
调用 save_binary_logs 脚本,立即保存缺失部分的 binlog 到各个从节点,
恢复
情况二: SSH 无法连接
调用 apply_diff_relay_logs 脚本,计算从库的relaylog的差异,恢复到2
号从库
7、解除从库身份
8、剩余从库和新主库构建主从关系
9、应用透明
10、故障节点自愈(待开发…)
11、故障提醒
9.4.5、 MHA 应用透明（vip）
manager：
#上传 master_ip_failover.txt 文件到 manager 服务器本地
[root@manager ~]# cp /root/master_ip_failover.txt
/usr/local/bin/master_ip_failover
[root@manager ~]# vi /usr/local/bin/master_ip_failover
my $vip = ‘192.168.43.200/24’;
my $key = ‘1’;
my $ssh_start_vip = "/sbin/ifconfig ens33:$ key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$ key down";
[root@manager ~]# yum install -y dos2unix
[root@manager ~]# dos2unix /usr/local/bin/master_ip_failover
[root@manager ~]# chmod +x /usr/local/bin/master_ip_failover
[root@manager ~]# vi /etc/mha/app1.cnf
master_ip_failover_script=/usr/local/bin/master_ip_failover
db01：
[root@db01 ~]# ifconfig ens33:1 192.168.43.200/24
manager：
重启 MHA
[root@manager ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@manager ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf
–remove_dead_master_conf --ignore_last_failover < /dev/null>
/var/log/mha/app1/manager.log 2>&1 &
[root@manager ~]# masterha_check_status --conf=/etc/mha/app1.cnf
测试：
db01 停止 mysqld 服务
[root@db01 ~]# systemctl stop mysqld
db02 查看 VIP 是否漂移过来：
[root@db02 ~]# ifconfig
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.43.200 netmask 255.255.255.0 broadcast 192.168.43.255
ether 00:0c:29:a0:bd:5f txqueuelen 1000 (Ethernet)
9.4.6、 MHA 故障提醒
manager：
#上传软件包
[root@manager ~]# ll email.tar
-rw-r–r--. 1 root root 84480 Dec 26 00:40 email.tar
#解压
[root@manager ~]# tar xf email.tar
#拷贝配置文件并授权
[root@manager ~]# cp -a email/* /usr/local/bin/
[root@manager ~]# cd /usr/local/bin/
[root@manager bin]# chmod +x *
#本地测试：
[root@manager bin]# sh testpl
必须要收到邮件

testpl 文件参数说明：

[root@manager bin]# cat testpl
#!/bin/bash
/usr/local/bin/sendEmail -o tls=no -f 1754815191@qq.com -t
1754815191@qq.com -s smtp.qq.com -xu 1754815191 -xp dbllegnpfdfoegjf -u
“MHA Waring” -m “YOUR MHA MAY BE FAILOVER” &>/tmp/sendmail.log
-f 1754815191@qq.com：发件人邮箱
-t 1754815191@qq.com：收件人邮箱
-s smtp.qq.com：发件人邮箱的 smtp 服务器
-xu 1754815191：发件人邮箱的用户名
-xp dbllegnpfdfoegjf：发件人邮箱授权密码
-u “MHA Waring”：邮件的标题
-m “YOUR MHA MAY BE FAILOVER”：邮件的具体内容
配置 manager 配置文件：
[root@manager ~]# vi /etc/mha/app1.cnf
report_script=/usr/local/bin/send
重启 MHA：
[root@manager ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@manager ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf
–remove_dead_master_conf --ignore_last_failover < /dev/null>
/var/log/mha/app1/manager.log 2>&1 &
测试：
模拟主库宕机， vip
[root@db01 ~]# systemctl stop mysqld
[root@db02 ~]# ifconfig
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.43.200 netmask 255.255.255.0 broadcast 192.168.43.255
ether 00:0c:29:a0:bd:5f txqueuelen 1000 (Ethernet)
9.4.7、额外的数据补偿（binlog_server）
1、找一台额外的机器，必须要有 5.7 以上的版本，支持 gtid 并开启，我们直
接用的第二个 slave（db03）
在（manager 节点操作）：
[root@manager ~]# vi /etc/mha/app1.cnf
[binlog1]
no_master=1
hostname=192.168.43.103
master_binlog_dir=/data/mysql/binlog
2、创建必要目录（db03 节点操作）
[root@db03 ~]# mkdir -p /data/mysql/binlog
[root@db03 ~]# chown -R mysql.mysql /data/*
3、拉取主库 binlog 日志（db02 节点操作）
[root@db03 ~]# cd /data/mysql/binlog #必须进入到自己创建好的目
录
[root@db03 binlog]# mysqlbinlog -R --host=192.168.43.101
–user=mha --password=mha --raw --stop-never mysql-bin.000001 &
注意：
拉取日志的起点，需要按照目前主库正在使用的 binlog 为起点
4、重启 MHA-manager （manager 节点操作）
[root@manager ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@manager ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf
–remove_dead_master_conf --ignore_last_failover < /dev/null>
/var/log/mha/app1/manager.log 2>&1 &
9.4.8、故障模拟及故障处理
9.4.8.1、宕掉 db01 数据库
[root@db01 ~]# systemctl stop mysqld
9.4.8.2、恢复故障
查看当前 master 在哪个数据库节点上（manager 节点操作）：
[root@manager ~]# grep “CHANGE MASTER TO”
/var/log/mha/app1/manager
Sat Dec 26 08:17:09 2020 - [info] All other slaves should start
replication from here. Statement should be: CHANGE MASTER TO
MASTER_HOST=‘192.168.43.102’, MASTER_PORT=3306,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘xxx’;
恢复数据库（db01 节点操作）：
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql
db01 [(none)]>CHANGE MASTER TO MASTER_HOST=‘192.168.43.102’,
MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=‘rrepl’,
MASTER_PASSWORD=‘123’;
db01 [(none)]>start slave;
恢复配置文件（manager 节点操作）：
[root@manager ~]# vi /etc/mha/app1.cnf
[server1]
hostname=192.168.43.101
port=3306
重启 MHA-manager （manager 节点操作）：
[root@manager ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@manager ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf
–remove_dead_master_conf --ignore_last_failover < /dev/null>
/var/log/mha/app1/manager.log 2>&1 &
恢复 binlogserver（任一 slave 数据库节点即可。这里选用的还是 db03 节点）
[root@db03 ~]# cd /data/mysql/binlog
[root@db03 binlog]# rm -rf /data/mysql/binlog/*
[root@db03 binlog]# mysqlbinlog -R --host=192.168.43.102
–user=mha --password=mha --raw --stop-never mysql-bin.000001 &
9.5、 MHA 配合 Atlas 实现读写分离
9.5.1、 Atlas 介绍
架构图
Atlas 是由 Qihoo 360， Web 平台部基础架构团队开发维护的一个基于 MySQL
协议的数据中间层项目。
它是在 mysql-proxy 0.8.2 版本的基础上，对其进行了优化，增加了一些
新的功能特性。
360 内部使用 Atlas 运行的 mysql 业务，每天承载的读写请求数达几十亿条。
下载地址
https://github.com/Qihoo360/Atlas/releases
注意：
1、 Atlas 只能安装运行在 64 位的系统上
2 、 Centos 5.X 安装 Atlas-XX.el5.x86_64.rpm ， Centos 6.X 安装
Atlas-XX.el6.x86_64.rpm。
3、后端 mysql 版本应大于 5.1，建议使用 Mysql 5.6 以上
4、 Atlas 已经在 2015 年停止更新了
9.5.2、安装配置
在任一 slave 节点操作（这里选用的还是 db03 节点）：
[root@db03 ~]# yum -y install Atlas-2.2.1.el7.x86_64.rpm
[root@db03 ~]# cd /usr/local/mysql-proxy/conf/
[root@db03 conf]# mv test.cnf test.cnf.bak
#配置 Atlas
[root@db03 conf]# vi test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 192.168.43.102:3306
proxy-read-only-backend-addresses =
192.168.43.101:3306,192.168.43.103:3306
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8
#启动 Atlas
[root@db03 conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test
start #这里的 test 是配置文件名
[root@db03 conf]# ps -ef |grep proxy
9.5.3、 Atlas 功能测试
[root@db03 ~]# mysql -umha -pmha -h 192.168.43.103 -P 33060
测试读操作：
db03 [(none)]>select @@server_id;
±------------+
| @@server_id |
±------------+
| 53 |
±------------+
1 row in set (0.00 sec)
db03 [(none)]>select @@server_id;
±------------+
| @@server_id |
±------------+
| 51 |
±------------+
1 row in set (0.00 sec)
测试写操作：
db03 [(none)]>begin;select @@server_id;commit;
±------------+
| @@server_id |
±------------+
| 52 |
±------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
9.5.4、生产用户要求
开发人员申请一个应用用户 app（select、 update、 insert）密码 123456，
要通过 192.168.43 网段登录
1、在主库中，创建用户
#在 manager 管理节点查看主库位置
[root@manager ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:2482) is running(0:PING_OK), master:192.168.43.101
#在主库中，创建用户
grant select,update,insert on . to app@‘192.168.43.%’ identified
by ‘123456’;
2、在 atlas 中添加生产用户
#制作加密密码
[root@db03 conf]# /usr/local/mysql-proxy/bin/encrypt 123456
/iZxz+0GRoA=
#更改配置文件
[root@db03 conf]# vi /usr/local/mysql-proxy/conf/test.cnf
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,app:/iZxz+0GRoA=
#重启服务
[root@db03 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test
restart
#测试
[root@db03 ~]# mysql -uapp -p 123456 -h 192.168.43.103 -P 33060
9.5.5、 Atlas 基本管理
#连接管理接口
[root@db03 ~]# mysql -uuser -ppwd -h 127.0.0.1 -P 2345
语法：
select * from help; #查看帮助语法
SELECT * FROM backends; #列出后端及其状态
set offline 2; #脱机后端服务器， 2 是后端服务器的 ID
set online 2; #联机后端服务器
REMOVE BACKEND 3; #移除后端服务器， 3 是后端服务器的 ID
ADD SLAVE 192.168.43.101:3306; #添加从节点
ADD PWD zhang:123456; #添加用户密码
save config; #将后端保存到配置文件
十、 MySQL 分布式解决方案
10.1、 MyCAT 基础架构准备
10.1.1、环境准备
两台虚拟机 db01、 db02
每台创建四个 mysql 实例： 3307 3308 3309 3310
10.1.2、删除历史环境
db01、 db02 两个节点都执行：
pkill mysqld
\rm -rf /data/330*
\mv /etc/my.cnf /etc/my.cnf.bak
10.1.3、创建相关目录初始化数据
db01、 db02 两个节点都执行：
mkdir /data/33{07…10}/data -p
mysqld --initialize-insecure --user=mysql
–datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql
–datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql
–datadir=/data/3309/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql
–datadir=/data/3310/data --basedir=/application/mysql
10.1.4、准备 DB01 配置文件和启动脚本
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
10.1.5、准备 DB02 配置文件和启动脚本
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
10.1.6、修改权限，启动多实例
db01、 db02 两个节点都执行：
chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
mysql -S /data/3307/mysql.sock -e “show variables like ‘server_id’”
mysql -S /data/3308/mysql.sock -e “show variables like ‘server_id’”
mysql -S /data/3309/mysql.sock -e “show variables like ‘server_id’”
mysql -S /data/3310/mysql.sock -e “show variables like ‘server_id’”
10.1.7、节点主从规划
箭头指向谁是主库
192.168.43.101:3307 <-----> 192.168.43.102:3307
192.168.43.101:3309 ------> 192.168.43.101:3307
192.168.43.102:3309 ------> 192.168.43.102:3307
192.168.43.102:3308 <-----> 192.168.43.101:3308
192.168.43.102:3310 -----> 192.168.43.102:3308
192.168.43.101:3310 -----> 192.168.43.101:3308
架构图：
10.1.8、分片规划
shard1：
Master： 192.168.43.101:3307
slave1： 192.168.43.101:3309
Standby Master： 192.168.43.102:3307
slave2： 192.168.43.102:3309
shard2：
Master： 192.168.43.102:3308
slave1： 192.168.43.102:3310
Standby Master： 192.168.43.101:3308
slave2： 192.168.43.101:3310
10.1.9、配置主从
192.168.43.101:3307 <-----> 192.168.43.102:3307
#db02：
mysql -S /data/3307/mysql.sock -e “grant replication slave on .
to repl@‘192.168.43.%’ identified by ‘123’;”
mysql -S /data/3307/mysql.sock -e “grant all on . to
root@‘192.168.43.%’ identified by ‘123’ with grant option;”
#db01：
mysql -S /data/3307/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.102’, MASTER_PORT=3307,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3307/mysql.sock -e “start slave;”
mysql -S /data/3307/mysql.sock -e “show slave status\G”
#db02：
mysql -S /data/3307/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’, MASTER_PORT=3307,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3307/mysql.sock -e “start slave;”
mysql -S /data/3307/mysql.sock -e “show slave status\G”
192.168.43.101:3309 ------> 192.168.43.101:3307
#db01:
mysql -S /data/3309/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’, MASTER_PORT=3307,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3309/mysql.sock -e “start slave;”
mysql -S /data/3309/mysql.sock -e “show slave status\G”
192.168.43.102:3309 ------> 192.168.43.102:3307
#db02：
mysql -S /data/3309/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.102’, MASTER_PORT=3307,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3309/mysql.sock -e “start slave;”
mysql -S /data/3309/mysql.sock -e “show slave status\G”
第二组四节点结构
192.168.43.102:3308 <-----> 192.168.43.101:3308
#db01:
mysql -S /data/3308/mysql.sock -e “grant replication slave on .
to repl@‘192.168.43.%’ identified by ‘123’;”
mysql -S /data/3308/mysql.sock -e “grant all on . to
root@‘192.168.43.%’ identified by ‘123’ with grant option;”
#db02：
mysql -S /data/3308/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’, MASTER_PORT=3308,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3308/mysql.sock -e “start slave;”
mysql -S /data/3308/mysql.sock -e “show slave status\G”
#db01：
mysql -S /data/3308/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.102’, MASTER_PORT=3308,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3308/mysql.sock -e “start slave;”
mysql -S /data/3308/mysql.sock -e “show slave status\G”
192.168.43.102:3310 -----> 192.168.43.102:3308
#db02：
mysql -S /data/3310/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.102’, MASTER_PORT=3308,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3310/mysql.sock -e “start slave;”
mysql -S /data/3310/mysql.sock -e “show slave status\G”
192.168.43.101:3310 -----> 192.168.43.101:3308
#db01：
mysql -S /data/3310/mysql.sock -e “CHANGE MASTER TO
MASTER_HOST=‘192.168.43.101’, MASTER_PORT=3308,
MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’;”
mysql -S /data/3310/mysql.sock -e “start slave;”
mysql -S /data/3310/mysql.sock -e “show slave status\G”
10.1.10、检测主从状态
db01、 db02 两个节点都执行：
mysql -S /data/3307/mysql.sock -e “show slave status\G”|grep Yes
mysql -S /data/3308/mysql.sock -e “show slave status\G”|grep Yes
mysql -S /data/3309/mysql.sock -e “show slave status\G”|grep Yes
mysql -S /data/3310/mysql.sock -e “show slave status\G”|grep Yes
注：如果中间出现错误，在每个节点进行执行以下命令，从第 9 步重新开
始即可
mysql -S /data/3307/mysql.sock -e “stop slave; reset slave all;”
mysql -S /data/3308/mysql.sock -e “stop slave; reset slave all;”
mysql -S /data/3309/mysql.sock -e “stop slave; reset slave all;”
mysql -S /data/3310/mysql.sock -e “stop slave; reset slave all;”
10.2、 Mycat 安装
1、预先安装 Java 运行环境
[root@db01 ~]# yum install -y java
2、下载
官网地址： http://www.mycat.org.cn/
上传软件包
[root@db01 ~]# ll
Mycat-server-1.6.7.1-release-20200209222254-linux.tar.gz
-rw-r–r--. 1 root root 17568537 Dec 28 08:15
Mycat-server-1.6.7.1-release-20200209222254-linux.tar.gz
3、解压文件
[root@db01 ~]# tar xf
Mycat-server-1.6.7.1-release-20200209222254-linux.tar.gz
4、软件目录结构
[root@db01 ~]# mv mycat/ /application/
[root@db01 ~]# cd /application/mycat/
[root@db01 mycat]# ls
bin catlet conf lib logs version.tx
5、启动和连接
[root@db01 ~]# vi /etc/profile
export PATH=/application/mycat/bin:$PATH
[root@db01 ~]# source /etc/profile
#启动
[root@db01 ~]# mycat start
Starting Mycat-server…
#连接 mycat：
[root@db01 ~]# mysql -uroot -p123456 -h 127.0.0.1 -P8066
10.3、 Mycat 基础应用
10.3.1、主要配置文件介绍
logs 目录：
wrapper.log ---->mycat 启动日志
mycat.log ---->mycat 详细工作日志
conf 目录：
schema.xml
主配置文件（读写分离、高可用、分布式策略定制、节点控制）
server.xml
mycat 软件本身相关的配置
rule.xml
分片规则配置文件,记录分片规则列表、使用方法等
10.3.2、用户创建及数据库导入
db01：
mysql -S /data/3307/mysql.sock
grant all on . to root@‘192.168.43.%’ identified by ‘123’;
create database world;
use world;
source /root/t100w.sql
mysql -S /data/3308/mysql.sock
grant all on . to root@‘192.168.43.%’ identified by ‘123’;
create database world;
use world;
source /root/t100w.sql
10.3.3、配置文件结构介绍
[root@db01 ~]# cd /application/mycat/conf
[root@db01 ~]# mv schema.xml schema.xml.bak
[root@db01 ~]# vi schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>
mycat 逻辑库定义:

数据节点定义:

后端主机定义:

select user()

</mycat:schema>
10.3.4、 Mycat 实现 1 主 1 从读写分离
[root@db01 ~]# vi schema.xml

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user()

</mycat:schema>
#重启服务
[root@db01 conf]# mycat restart
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…
#测试
#123456 是 mycat 默认的密码， 8066 是 mycat 对外提供服务的端口
[root@db01 conf]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 9 |
±------------+
1 row in set (0.07 sec)
mysql> begin; select @@server_id; commit;
±------------+
| @@server_id |
±------------+
| 7 |
±------------+
1 row in set (0.00 sec)
注意：这套架构有一个弊端，就是一旦从库宕机了，那么就没办法读了。
就只能写。因为主库是写，从库是读。
10.3.5、 Mycat 高可用+读写分离
[root@db01 conf]# mv schema.xml schema.xml.1
[root@db01 conf]# vi schema.xml

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user()

</mycat:schema>
说明:

第一个 whost： 192.168.43.101:3307 真正的写节点,负责写操作
第二个 whost ： 192.168.43.102:3307 准备写节点，负责读，当
192.168.43.101:3307 宕掉，会切换为真正的写节点
#重启服务
[root@db01 conf]# mycat restart
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…
#测试
[root@db01 conf]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066
测试读：
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 17 |
±------------+
1 row in set (0.05 sec)
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 19 |
±------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 9 |
±------------+
1 row in set (0.02 sec)
测试写：
mysql> begin; select @@server_id; commit;
±------------+
| @@server_id |
±------------+
| 7 |
±------------+
1 row in set (0.00 sec)
#停止主库测试
[root@db01 conf]# systemctl stop mysqld3307
[root@db01 conf]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066
测试写：
mysql> begin; select @@server_id; commit;
±------------+
| @@server_id |
±------------+
| 17 |
±------------+
测试读：
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 19 |
±------------+
1 row in set (0.00 sec)
#开启主库测试
[root@db01 conf]# systemctl start mysqld3307
[root@db01 conf]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066
#测试写
mysql> begin; select @@server_id; commit;
±------------+
| @@server_id |
±------------+
| 17 |
±------------+
#测试读
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 7 |
±------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 19 |
±------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
±------------+
| @@server_id |
±------------+
| 9 |
±------------+

1 row in set (0.00 sec)
10.3.6、配置中的属性介绍
balance 属性
负载均衡类型，目前的取值有 3 种：
1、 balance=“0”，不开启读写分离机制，所有读操作都发送到当前可用的
writeHost 上。
2、 balance=“1”，全部的 readHost 与 standby writeHost 参与 select 语
句的负载均衡，简单的说，
当双主双从模式（M1->S1， M2->S2，并且 M1 与 M2 互为主备），正常情
况下， M2,S1,S2 都参与 select 语句的负载均衡。
3、 balance=“2”，所有读操作都随机的在 writeHost、 readhost 上分发。
writeType 属性
负载均衡类型，目前的取值有 2 种：
1、 writeType=“0”, 所有写操作发送到配置的第一个 writeHost，
第一个挂了切到还生存的第二个 writeHost，重新启动后已切换后的为主，
切换记录在配置文件中:dnindex.properties。
2. writeType=“1” ，所有写操作都随机的发送到配置的 writeHost，但不
推荐使用
switchType 属性
-1：表示不自动切换
1：默认值，自动切换
2：基于MySQL主从同步的状态决定是否切换，心跳语句为show slave status
datahost 其他配置

maxCon=“1000”：最大的并发连接数
minCon=“10”： mycat 在启动之后，会在后端节点上自动开启的连接线程
tempReadHostAvailable=“1”
这个一主一从时（1 个 writehost， 1 个 readhost 时），可以开启这个参数，
如果 2 个 writehost， 2 个 readhost 时select user()
监测心跳
10.4、 Mycat 高级应用-分布式解决方案
10.4.1、垂直分表
[root@db01 conf]# mv schema.xml schema.xml.2
[root@db01 conf]# vi schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user() select user() 创建测试库和表： [root@db01 ~]# mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;" [root@db01 ~]# mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;" [root@db01 ~]# mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))"; [root@db01 ~]# mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))" 重启服务 [root@db01 conf]# mycat restart Stopping Mycat-server... Stopped Mycat-server. Starting Mycat-server... 测试 [root@db01 ~]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066 mysql> show databases; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec) mysql> use TESTDB mysql> show tables; +------------------+ | Tables_in_taobao | +------------------+ | order_t | | user | +------------------+ 2 rows in set (0.01 sec) #插入数据 mysql> insert into user(id ,name ) values(1,'a'),(2,'b');

mysql> commit;
mysql> insert into order_t(id ,name ) values(1,‘a’),(2,‘b’);
mysql> commit;
#查单表
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e “show tables from
taobao;”
±-----------------+
| Tables_in_taobao |
±-----------------+
| user |
±-----------------+
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e “show tables from
taobao;”
±-----------------+
| Tables_in_taobao |
±-----------------+
| order_t |
±-----------------+
10.4.2、 Mycat 分布式-水平拆分(分片)介绍
分片：对一个"bigtable"，比如说 t3 表
(1)行数非常多， 800w
(2)访问非常频繁
分片的目的：
（1）将大数据量进行分布存储
（2）提供均衡的访问路由
分片策略：
范围 range 800w 1-400w 400w01-800w
取模 mod 取余数
枚举
哈希 hash
时间流水
优化关联查询
全局表
ER 分片

10.4.3、 Mycat 分布式-范围分片
比如说 t3 表
（1）行数非常多， 2000w（1-1000w:sh1， 1000w01-2000w:sh2）
（2）访问非常频繁，用户访问较离散
修改配置文件：
[root@db01 conf]# mv schema.xml schema.xml.3
[root@db01 conf]# vi schema.xml

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user() select user()

</mycat:schema>
查看配置文件的定义：
[root@db01 conf]# vi rule.xml

id
rang-long

autopartition-long.txt

设置范围：
[root@db01 conf]# vi autopartition-long.txt
1-10=0
10-20=1
解释：
1-10=0 -----> >=1， <=10
10-20=1 -----> >10， <=20
创建测试表：
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e “use
taobao;create table t3 (id int not null primary key auto_increment,name
varchar(20) not null);”
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "use

taobao;create table t3 (id int not null primary key auto_increment,name
varchar(20) not null);"
重启 mycat
[root@db01 conf]# mycat restart
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…
测试：
[root@db01 ~]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066
mysql> use TESTDB;
insert into t3(id,name) values(1,‘a’);
insert into t3(id,name) values(2,‘b’);
insert into t3(id,name) values(3,‘c’);
insert into t3(id,name) values(10,‘d’);
insert into t3(id,name) values(11,‘aa’);
insert into t3(id,name) values(12,‘bb’);
insert into t3(id,name) values(13,‘cc’);
insert into t3(id,name) values(14,‘dd’);
insert into t3(id,name) values(20,‘dd’);
commit;
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e “select * from
taobao.t3;”
±—±-----+
| id | name |
±—±-----+
| 1 | a |
| 2 | b |
| 3 | c |
| 10 | d |
±—±-----+
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e “select * from
taobao.t3;”
±—±-----+
| id | name |
±—±-----+
| 11 | aa |
| 12 | bb |
| 13 | cc |

| 14 | dd |
| 20 | dd |
±—±-----+
10.4.4、 Mycat 分布式-取模分片（mod-long）
修改配置文件：
[root@db01 conf]# mv schema.xml schema.xml.4
[root@db01 conf]# vi schema.xml

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user() select user()

</mycat:schema>
[root@db01 conf]# vi rule.xml +114

2

这里要注意一下：如果这个值大于 dataNode="sh1,sh2"的数量，那么 mycat
将会启动失败！
准备测试环境
创建测试表
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e “use
taobao;create table t4 (id int not null primary key auto_increment,name
varchar(20) not null);”
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e “use
taobao;create table t4 (id int not null primary key auto_increment,name
varchar(20) not null);”
重启 mycat
[root@db01 conf]# mycat restart
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…
测试：
[root@db01 ~]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066
use TESTDB

insert into t4(id,name) values(1,‘a’);
insert into t4(id,name) values(2,‘b’);
insert into t4(id,name) values(3,‘c’);
insert into t4(id,name) values(4,‘d’);
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e “select * from
taobao.t4;”
±—±-----+
| id | name |
±—±-----+
| 2 | b |
| 4 | d |
±—±-----+
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e “select * from
taobao.t4;”
±—±-----+
| id | name |
±—±-----+
| 1 | a |
| 3 | c |
±—±-----+
10.4.5、 Mycat 分布式-枚举分片（略过）
t5 表
id name telnum
1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555
修改配置文件：
[root@db01 conf]# vi schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

select user() select user() 查看配置文件的定义： [root@db01 conf]# vi rule.xml sharding_id hash-int partition-hash-int.txt 1 小细节： mycat 默认不识别字符串列，比如字符串 bj、或者文字，北京所以需要加上1 type=1 支持字符串枚举 partition-hash-int.txt 配置： [root@db01 conf]# vi partition-hash-int.txt bj=0 sh=1 DEFAULT_NODE=1 DEFAULT_NODE=1 #如果不是 bj、 sh 的默认走 1 的分片准备测试环境 [root@db01 ~]# mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);" [root@db01 ~]# mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);" 重启 mycat [root@db01 ~]# mycat restart Stopping Mycat-server... Stopped Mycat-server. Starting Mycat-server... 测试： [root@db01 ~]# mysql -uroot -p123456 -h 192.168.43.101 -P 8066 use TESTDB insert into t5(id,name) values(1,'bj'); insert into t5(id,name) values(2,'sh'); insert into t5(id,name) values(3,'bj'); insert into t5(id,name) values(4,'sh'); insert into t5(id,name) values(5,'tj'); 分别登录后端节点查询数据 mysql -S /data/3307/mysql.sock -e "select * from taobao.t5;" mysql -S /data/3308/mysql.sock -e "select * from taobao.t5;" 十一、 MySQL 优化 11.1、 Max_connections 1、简介 Mysql 的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多， mysql 会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。 2、判断依据 mysql> show variables like 'max_connections'; #最大连接数 +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> show status like 'Max_used_connections'; #当前用户连接数 +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 1 | +----------------------+-------+ 1 row in set (0.00 sec) 3、修改方式 vim /etc/my.cnf max_connections=1024 补充： 1、开启数据库时，我们可以临时设置一个比较大的测试值 2、观察“show status like 'Max_used_connections';” 变化

3 、如果 Max_used_connections 跟 max_connections 相同，那么就是
max_connections 设置过低或者超过服务器的负载上限了。
11.2、 back_log
1、简介
mysql 能暂存的连接数量，当主要 mysql 线程在一个很短时间内得到非常多
的连接请求时候它就会起作用，如果 mysql 的连接数据达到 max_connections
时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量
及 back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。
back_log 值指出在 mysql 暂时停止回答新请求之前的短时间内有多少个请
求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增
加它。
2、判断依据
mysql> show full processlist;
发现大量的待连接进程时，就需要加大back_log或者加大max_connections
的值
3、修改方式
vi /etc/my.cnf
back_log=1024
11.3、 wait_timeout 和 interactive_timeout
1、简介
wait_timeout：指的是 mysql 在关闭一个非交互的连接之前所要等待的秒
数。
interactive_timeout：指的是 mysql 在关闭一个交互的连接之前所需要等
待的秒数，比如我们在终端上进行 mysql 管理，使用的即使交互的连接，这时
候，如果没有操作的时间超过了 interactive_time 设置的时间就会自动的断开，
默认的是 28800，可调优为 7200。
wait_timeout：如果设置太小，那么连接关闭的就很快，从而使一些持久
的连接不起作用
2、设置建议
如果设置太大，容易造成连接打开时间过长，在 show processlist 时候，
能看到很多的连接，一般希望 wait_timeout 尽可能低。
3、修改方式
vi /etc/my.cnf
wait_timeout=60
interactive_timeout=1200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将 wait_timeout 设定比较小， interactive_timeout 要和应用
开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不
需要调整。
11.4、 key_buffer_size
1、简介
key_buffer_size 指定用于索引的缓冲区大小，增加它可得到更好的索引处
理性能。对于内存在 4GB 左右的服务器该参数可设置为 256M 或 384M。注意：该
参数值设置的过大反而会是服务器整体效率降低！
2、修改方式
vi /etc/my.cnf
key_buffer_size = 256M

快速和研发人员打好关系
找到领导要 ER 图
DESC ,show create table
select * from city limit 5;
附录二： MySQL+Keepalived 实战
环境说明
准备 4 台服务器
环境 IP 软件说明
db01 主库 192.168.43.101 mysql、 keepalived 101 和 102 是主从
db02 从库 192.168.43.102 mysql 103 和 104 是主从
db03 主库 192.168.43.103 mysql、 keepalived
db04 从库 192.168.43.104 mysql
VIP： 192.168.43.200
准备四台安装好的 mysql 服务器，操作如下：
1、下载并上传软件至/server/tools
mkdir -p /server/tools
cd /server/tools/
上传软件包
2、解压软件
tar zxvf mysql-5.7.31-el7-x86_64.tar.gz
mkdir /application
mv mysql-5.7.31-el7-x86_64 /application/mysql
yum remove mariadb-libs -y
useradd -s /sbin/nologin mysql
echo “export PATH=/application/mysql/bin:$PATH” >>/etc/profile
source /etc/profile
mysql -V
3、创建数据路径并授权
mkfs.xfs /dev/sdb
[root@localhost ~]# blkid
/dev/sdb: UUID=“4f4bed6c-4d81-434d-918d-8148a6591d84” TYPE=“xfs”
[root@localhost ~]# vi /etc/fstab
UUID=“4f4bed6c-4d81-434d-918d-8148a6591d84” /data xfs defaults 0 0
mkdir /data
mount -a
4、授权
chown -R mysql.mysql /application/*
chown -R mysql.mysql /data
5、初始化数据（创建系统数据）
mkdir /data/mysql/data -p
chown -R mysql.mysql /data
mysqld --initialize-insecure --user=mysql
–basedir=/application/mysql --datadir=/data/mysql/data
6、配置文件的准备
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
EOF
7、启动数据库
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.h
tml
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld
–defaults-file=/etc/my.cnf
LimitNOFILE = 5000

systemctl restart mysqld.service
提示：做完的话，如果使用的是 vm，可以做个快照操作
1、清理环境（全部节点）
mkdir /data/binlog
chown mysql.mysql /data/*
pkill mysqld
\rm -rf /data/mysql/data/*
\rm -rf /data/binlog/*
2、准备配置文件
#db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=1
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\d]>
EOF
#db02：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=2
port=3306
secure-file-priv=/tmp
autocommit=0

log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\d]>
EOF
#db03：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=3
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\d]>
EOF
#db04：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=4
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on

enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db04 [\d]>
EOF
3、初始化数据（4 台都执行）
mysqld --initialize-insecure --user=mysql
–basedir=/application/mysql --datadir=/data/mysql/data
4、启动数据库（三台都执行）
systemctl restart mysqld
5、构建主从
db01 <> db03
db01 < db02
db03 <== db04
#db01 和 db03：
grant replication slave on . to repl@‘192.168.43.%’ identified
by ‘123’;
#db01：
change master to
master_host=‘192.168.43.103’,
master_user=‘repl’,
master_password=‘123’ ,
MASTER_AUTO_POSITION=1;
start slave;
#db02：
change master to
master_host=‘192.168.43.101’,
master_user=‘repl’,
master_password=‘123’ ,
MASTER_AUTO_POSITION=1;
start slave;
#db03：
change master to
master_host=‘192.168.43.101’,

master_user=‘repl’,
master_password=‘123’ ,
MASTER_AUTO_POSITION=1;
start slave;
#db04：
change master to
master_host=‘192.168.43.103’,
master_user=‘repl’,
master_password=‘123’ ,
MASTER_AUTO_POSITION=1;
start slave;
6、配置主/被调度器 Keepalived（101 和 103 机器）（在主库上配置）
101 机器：
[root@master ~]# yum -y install keepalived
[root@master ~]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql1
} v
rrp_script check_run {
script “/etc/keepalived/check_mysql.sh”
interval 5
} v
rrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 88
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
} t
rack_script {
check_run
}

virtual_ipaddress {
192.168.43.200
}
} [
root@master ~]# systemctl restart keepalived
103 机器：
[root@backup ~]# yum -y install keepalived
[root@backup ~]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql2
} v
rrp_script check_run {
script “/etc/keepalived/check_mysql.sh”
interval 5
} v
rrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 88
priority 50
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
} t
rack_script {
check_run
} v
irtual_ipaddress {
192.168.43.200
}
} [
root@backup ~]# systemctl restart keepalived
7、配置 mysql 状态检测脚本（两台 MySQL 同样的脚本）
[root@master ~]# vi /etc/keepalived/check_mysql.sh

/usr/bin/mysql -uroot -e “show status” &>/dev/null
if [ $? != 0 ];then
systemctl stop keepalived
fi
[root@master ~]# chmod a+x /etc/keepalived/check_mysql.sh
[root@master ~]# systemctl restart keepalived
8、测试
C:\Users>mysql -urepl -p123 -h 192.168.43.200
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
±-------------------+
5 rows in set (0.00 sec)
[root@master ~]# systemctl stop mariadb
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
4 rows in set (0.00 sec)
附录三：数据库开发规范
1、配置规范
1、 MySQL 数据库默认使用 InnoDB 存储引擎。
2、保证字符集设置统一， MySQL 数据库相关系统、数据库、表的字符集都
是由 UTF8MB4，应用程序连接、展示等可以设置字符集的地方也都统一设置为
UTF8MB4 字符集。
3、数据库中的表要合理规划，控制单表数据量，对于 MySQL 数据库来说，
建议单表记录数控制在 2000W 以内。
4、 MySQL 实例下，数据库、表数量尽可能少，数据库一般不超过 50 个，每
个数据库下，数据表数量一般不超过 500 个（包括分区表）。
2、建表规范
1、 InnoDB 禁止使用外键约束，可以通过程序层保证。
2、存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE。
3、整型定义中无需定义显示宽度，比如：使用 INT，而不是 INT(4)。
4、不建议使用 ENUM 类型，可使用 TINYINT 来代替。
5、存储年时使用 YEAR(4)，不使用 YEAR(2)
6、建议字段定义为 NOT NULL。
7、建议 DBA 提供 SQL 审核工具，建表规范性需要通过审核工作审核后。
3、命名规范
1、库、表、字段全部采用小写
2、库名、表名、字段名、索引名称均是以小写字母，并以“_” 分隔。
3、库名、表名、字段名建议不超过 12 个字符。（库名、表名、字段名支
持最多 64 个字符，但为了统一规范、易于辨识以及减少传输量，统一不超过 12
个字符）。
4、库名、表名、字段名见名知意，不需要添加注释。
4、分表规范
1、不建议使用分区表来实现分表需求。
2、可以结合使用 hash、 range、 lookup table 进行拆分。
3、使用时间散表，表名后缀必须使用特定格式，比如：按日散表
user_20210212，按月散表 user_202102 等。
4、分表的设定规范可以借助数据库中间件来完成。
5、存储过程规范
存储过程规范很简单，核心就是一句话：尽量不要使用存储过程。
主要原因是存储过程不能实现业务逻辑层与数据存储层的分离，对于应用
逻辑不透明，调试复杂，不便于优化。
附录四： MySQL 经典面试题
1、简述你们公司使用的 MySQL 版本，并说明具体小版本及 GA 时间？
我们公司使用的 MySQL 版本是“5.7.20” ，发行时间是“2017.9.13”
2、请介绍你熟悉的数据库关系系统的种类和代表产品名称？
DBMS：数据库管理系统
RD（关系型数据库）： Oracle、 MySQL、 MSSQL
NoSQL（非关系数据库）： MongoDB、 Redis、 ES
NEWSQL（分布式数据库）： TiDB、 PolarDB、 Spanner
3、请简述 MySQL 二进制安装重点步骤？
1、要下载的 mysql 版本一定要选择正确，不然会出现不兼容情况。
2、卸载和下载相关的依赖包
3、创建的管理 MySQL 软件的数据用户是不可以登录虚拟用户的
4、需要授权属主属组为创建的管理 MySQL 的用户，这一点很容易忘记。
5、初始化数据库的方式有很多，但是尽量做到什么方式启动，也用什么方
式关闭。
4、怎么确认数据库启动成功了？
1、直接登录 MySQL 查看
2、 netsat -lntp|grep 3306 查看数据库端口是否存在
3、 ps -ef |grep mysql 查看数据库进程是否存在
4、查看 mysql.sock 文件是否按配置文件指定的路径出现（不建议此方法）
5、一般建议使用以上任意两种方法，即可确保 MySQL 是否启动。
5、请简述 mysqld 的程序结构（1 条 SQL 语句的执行过程）
MySQL 的程序结构分为三层：连接层， SQL 层，搜索引擎层
一条 SQL 语句的执行过程也就是 SQL 层的执行过程：
1、验证语法
2、验证语意
3、验证权限
4、解析器进行语句解析，生成多种执行该条件的执行方案
5、优化器进行筛选，根据多方面（cpu 的消耗，磁盘 io 等），找到一个最
小的执行方案
6、执行器按照优化器给出的最佳执行方式执行 SQL 语句
7、提供 query cache（默认没有开），一般也不开，用 redis 代替
8、记录操作日志（binlog） —（只记录修改的记录，默认没开）
6、请简述你了解的 MySQL 的启动方式
（二进制安装）
1、自带的启动方式： /application/mysql/support-files/mysql.server
‘start|restart|stop’
2 、 /etc/init.d 启动把 /mysql/support-files/mysql.server 移动到
/etc/init.d 下，然后即可通过该方式启动

3、 service mysqld ‘start|stop|restart’
4、 systemctl 管理启动在/etc/systemd/system/添加一个配置文件，即可
通过 systemctl 管理
7、简述 MySQL 配置文件默认读取顺序
mysqld --help --verbose | grep “my.cnf”
MySQL 配置文件的默认读取顺序是：
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf
~/.my.cnf
优先级为后面的大于前面的，因为按照正常的读取，后面的会将前面的都
覆盖掉。
8、 mysqld_safe --default-files=/opt/my.cnf &是什么作用？
表示在启动 MySQL 时候，强行指定读取的配置文件/opt/my.cnf 时，即不读
取其他任何的配置文件。
9、忘记管理员 root 的密码处理过程，请对参数详细说明
执行过程说明：
1、关闭数据库 systemctl stop mysqld
2、检查数据库是否关闭 systemctl status mysqld
3 、使用特殊的参数启动数据库进入救援模式 mysql_safe
–skip-grant-tables --skip-networking &
参数说明：
–skip-grant-tables 是跳过连接层的账户密码验证环节
–skip-networking 是将连接层的远程 TCP、 IP 连接方式关闭，即不
允许远程连接
& 是将其放在后台运行，敲几下回车即可正常操作
执行流程：
现在直接可以用户免密码登录
然后即可在 MySQL 内部通过 sql 语句重置密码
mysql> alter user root@‘localhost’ identified by ‘1’;
但是会报错，不允许该方式修改密码，没关系，执行这条语句：
mysql> flush privileges;
再执行，重置密码即可
mysql> alter user root@‘localhost’ identified by ‘1’;
密码重置之后，立即关闭数据库，然后正常启动数据库，验证登录即可。
10、请列举 SQL 语句的常用种类
DDL: 数据定义
DQL：数据查询
DCL：数据控制
DML：数据操作
11、你了解的 MySQL 存储引擎种类有哪些？
MyISAM 存储引擎和 INNODB 存储引擎
myISAM 把列的定义值，数据行以及索引分为三个文件进行存储，分别是
frm、 .myd、 .myi
InnoDB 把列的定义信息放在.frm 文件，把数据行和索引信息放在.ibd 文件
12、锁的优化策略
1、读写分离
2、分段加锁
3、减少锁持有的时间
4、多个线程尽量以相同的顺序去获取资源
不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，
反而效率不如一次加一把大锁。
13、如何优化 MySQL
1、 SQL 语句及索引的优化
2、数据库表结构的优化
3、系统配置的优化
4、硬件的优化
14、什么是索引？
索引是一种数据结构,可以帮助我们快速的进行数据的查找。
15、如何设计一个高并发的系统
1、数据库的优化，包括合理的事务隔离级别、 SQL 语句优化、索引的优化
2、使用缓存，尽量减少数据库 IO
3、分布式数据库、分布式缓存
4、服务器的负载均衡
16、 varchar(10)和 int(10)代表什么含义?
varchar 的 10 代表了申请的空间长度,也是可以存储的数据的最大长度,而
int 的 10 只是代表了展示的长度,不足 10 位以 0 填充.也就是说,int(1)和
int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长
度展示。

MySQL 数据库入门到架构

修改为:

修改为:

修改为:

开关

文件位置及名字

设定慢查询时间

没走索引的语句也记录

at 123

testpl 文件参数说明：

<mycat:schema xmlns:mycat=“http://io.mycat/”> mycat 逻辑库定义:

数据节点定义:

后端主机定义: select user()

<mycat:schema xmlns:mycat=“http://io.mycat/”>
mycat 逻辑库定义:

后端主机定义:

select user()