Mysql参数查询

1 2	方法1. select @@[参数名] 方法2. show variables like [参数名]%

Mysql版本的问题

Mysql的发行版有哪些

Mysql官方版本(社区版,企业版)
Percona Mysql
1. 与mysql官方版本兼容
2. 具有mysql企业版的功能
3. 性能比社区版的好
4. 版本会比mysql官方版落后
MariaDB
1. 不完全兼容mysql官方版本

各个发行版本之间的区别

服务器特性

特性	Mysql	Percona Mysql	MariaDB
是否开源	开源	开源	开源
是否支持分区表	支持	支持	支持
使用的引擎	InnoDB	XtraDB	XtraDB
运维监控工具	企业版提供,社区版不提供	Percon Monitor工具	Monyog

高可用特性

Mysql	Percona Mysql	MariaDB
基于日志点复制	基于日志点复制	基于日志点复制
基于Gtid复制	基于Gtid复制	基于Gtid复制,但Gtid同Mysql不兼容
MGR	MGR & PXC	Galera Cluster
Mysql Router	Proxy SQL	MaxScale

安全性

Mysql	Percona Mysql	MariaDB
企业版防火墙	ProxySQL FireWall	MaxScale FireWall
企业版用户审计	审计日志	审计日志
用户密码生命周期	用户密码生命周期	-
sha256_passwordcaching_sha2_password	sha256_passwordcaching_sha2_password	ed25519sha256_password

开发及管理

Mysql	Percona Mysql	MariaDB
窗口函数(8.0)	窗口函数(8.0)	窗口函数(10.2)
-	-	支持基于日志回滚
-	-	支持记在表中记录修改
Super read_only	Super read_only	-

Mysql8.0版本的新特性

服务器功能

所有元数据使用InnoDB引擎存储,无frm文件
系统表采用InnoDB存储并采用独立表空间
支持定义资源管理资源组(目前仅支持CPU(2019-01-22))
支持不可见索引和降序索引,支持直方图优化
支持窗口函数
支持在线修改全局参数持久化

用户及安全

默认使用caching_sha2_password认证插件
新增支持定义角色(role)
新增密码历史记录功能,限制重复使用密码

InnoDB功能

InnoDB DDL语句支持原子操作
支持在线修改UNDO表空间
新增管理视图用于监控InnoDB表状态
新增innodb_dedicated_server配置项

Mysql升级的问题

升级前需要考虑的问题

升级可以给业务带来的益处
升级可能对业务带来的影响
数据库升级方案的制定
升级失败的回滚方案

1. 升级可以给业务带来的益处

是否可以解决业务上某一方面的痛点
是否可以解决运维上某一方面的痛点

2. 升级可能对业务带来的影响

对原业务程序的支持是否有影响
对原业务程序的性能是否有影响

3. 数据库升级方案的制定

评估受影响的业务系统
升级的详细步骤
升级后的数据库环境检查
升级后的业务检查

4. 升级失败的回滚方案

升级失败回滚的步骤
回滚后的数据库环境检查
回滚后的业务检查

Mysql用户管理的问题

Mysql数据库账户定义

用户名@可访问控制列表
1. 可访问控制列表
  - %: 代表可以从所有外部主机访问
  - 192.168.1.%: 表示可以从192.168.1 网段访问
  - localhost: DB服务器本地访问
使用Create User命令建立用户 (\h Create User查看帮助)

Mysql常用的用户权限

Admin
1. Create User : 建立新的用户的权限
2. Grant option : 为其他用户授权的权限
3. Super : 管理服务器的权限
DDL
1. Create : 新建数据库,表的权限
2. Alter : 修改表结构的权限
3. Drop : 删除数据库和表的权限
4. Index : 建立和删除索引的权限
DML
1. Select : 查询表中数据的权限
2. Insert : 向表中插入数据的权限
3. Update : 更新表中数据的权限
4. delete : 删除表中数据的权限
5. Execute : 执行存储过程的权限

如何为用户授权

遵循最小权限原则
使用Grant命令对用户授权
1. 授权:grant Select,insert,update,delete on [数据库名].[表名(*全部表)] to user@ip;
2. 收回:revoke delete on [数据库名].[表名(*全部表)] from user@ip;

如何保证数据账户的安全

数据库用户管理流程规范
1. 最小权限原则
2. 密码强度策略
3. 密码过期原则
4. 限制历史密码重用原则

迁移数据库账户

导出用户建立及授权语句
1. pt-show-grants u=root,p=123456,h=localhost

用户管理问题

问题1: Access denied for user ‘root‘@’xxx.xxx.xxx.xxx’ (using password: YES)

问题原因:账户无法外网访问

1
2
3

mysql -uroot -p

grant all privileges on *.* to 账户名@'%' identified by '账户密码';

Mysql服务器配置的问题

SQL_MODE

配置Mysql处理Sql的方式
set [session/global/persist] sql_mode=’xxxxx’
[mysqld] sql_mode=xxxx
查看当前sql_mode的设置:show variables like 'sql_mode'

常用的sql Mode

SQL_MODE	说明
ONLY_FULL_GROUP_BY	对于Group By聚合操作,如果出现在Select中的列,Having或者Order By子句的非聚合列,没有在Group By中出现,那么这个sql语法检查报错
ANSI_QUOTES	禁止用双引号来引用字符串
REAL_AS_FLOAT	Real作为float的同义词
PIPES_AS_CONCAT	将 `
STRICT_TRANS_TABLES / STRICT_ALL_TABLES	在事务存储引擎 / 所有存储引擎上启用严格模式出现,那么这个sql语法检查报错
ERROR_FOR_DIVISION_BY_ZERO	不允许0作为除数
NO_AUTO_CREATE_USER	在用户不存在时不允许grant语句自动建立应用
NO_ZERO_IN_DATE / NO_ZERO_DATE	日期数据内 / 日期数据不能含0
NO_ENGINE_SUBSTITUTION	当指定的存储引擎不可用时报错

Mysql常见的性能参数

服务器配置参数

参数	说明
max_connections	设置Mysql允许访问的最大连接数量
interactive_timeout	设置交互连接的timeout时间
wait_timeout	设置非交互连接的timeout时间
max_allowed_packet	控制mysql可以接收的数据包的大小
sync_binlog	表示每写多少次缓冲会向磁盘同步一次binlog
sort_buffer_size	设置每个会话使用的排序缓冲区的大小
join_bugger_size	设置每个会话使用的连接缓冲的大小
read_buffer_size	指定了当对一个MYISAM进行表扫描时所分配的读缓存池的大小
read_rnd_buffer_size	设置控制索引缓冲区的大小
binlog_cache_size	设置每个会话用于缓存未提交的事务缓存大小

存储引擎参数

参数	说明
innodb_flush_log_at_trx_commit	0:每秒进行一次重做日志的磁盘刷新操作,1:每次事务提交都会刷新事务日志到磁盘中,2:每次事务提交写入系统缓存每秒向磁盘刷新一次 (默认为1)
innodb_buffer_pool_size	设置Innodb缓存池的大小,应为系统可用内存的75%
innodb_buffer_pool_instances	InnoDB缓冲池的实例个数,每个实例的大小(建议不要超过8个)
innodb_file_per_table	设置每个表独立使用一个表空间(建议启动)

Mysql日志类问题

Mysq常用的日志类型

日志名称	作用
错误日志(error_log)	记录mysql在启动,运行或停止时出现的问题
常规日志(general_log)	记录所有发向mysql的请求
慢查日志(slow_query_log)	记录符合条件的查询
二进制日志(binary_log)	记录全部有效的数据修改日志
中继日志(relay_log)	用于主从复制,临时存储从主库同步的二进制日志

错误日志(error_log)

作用

分析排除Mysql运行错误
记录未经授权的访问

配置参数

配置和查询参数的方法

# 配置
set persist [参数名称]=[内容];
# 例
set persist log_timestamps='SYSTEM';

# 查看
select @@[参数];
#例
select @@log_timestamps

配置日志文件的位置:log_error

# 例:
log_error = $mysql/sql_log/mysql-error.log\

# 查看日志文件位置
select @@log_error;

配置日志记录信息的级别
1
2
3
4
5
# 配置日志级别
log_error_verbosity= [1,2,3]

# 查看日志级别
select @@log_error_verbosity;
verbosity 作用

1 Error messages

2 Eeeor and warning messages

3 Error,warning and note messages

verbosity	作用
1	Error messages
2	Eeeor and warning messages
3	Error,warning and note messages

配置日志时间使用系统时间

1
2
3

log_timestamps='SYSTEM'
# 查看日志时间
select @@log_timestamps;

Msql日志服务组件配置(Mysql8.0以上)

log_error_services=[日志服务组件;日志服务组件]

# 查看日志服务组件
select @@log_error_services;

组件名称	作用
log_filter_internal	默认的日志过滤组件,依赖log_error_verbosity
log_sink_internal	默认的日志输出组件,依赖log_error
log_sink_json	将错误日志输出到json文件
log_sink_syseventlog	将错误日志输出到系统日志文件

常规日志(general_log)

作用

分析客户端发送到Msql的实际请求

配置参数

配置和查询参数的方法

# 配置
set global [参数名称]=[内容];
# 例
set global general_log='ON';

# 查看
select @@[参数];
#例
select @@log_timestamps

日志开启和关闭(默认关闭)
1
general_log=[ON|OFF]

指定日志文件地址

1	general_log_file= $mysql/sql_log/general.log

日志存储的方式

1 2	# FILE:存储到文件,TABLE:存储到数据库表中(mysql库的general_log表中) log_output=[FILE\|TABLE\|NONE]

慢查日志(slow_query_log)

作用

将执行成功并符合条件的查询记录到日志中
找的需要优化的sql

配置参数

配置和查询参数的方法

# 配置
set global [参数名称]=[内容];
# 例
set global slow_query_log='ON';

# 查看
select @@[参数];
#例
select @@slow_query_log

日志开启和关闭(默认关闭)
1
slow_query_log=[ON|OFF]

日志存储位置

1	slow_query_log_file=$mysql/sql_log/showlog.log

查询超时时间
1
2
# 单位(秒)
long_query_time=0.001
是否将没使用索引的sql记录下来(默认关闭)
1
log_queries_not_using_indexes=[ON|OFF]
是否记录管理数据库管理命令(默认关闭)
1
log_slow_admin_statements=[ON|OFF]
二进制日志(binary_log)
作用
记录所有对数据库中数据的修改
基于时间点的备份和恢复
主从复制

配置参数

开启设置日志
1
log-bin = [base_name]
日志格式(默认ROW)
1
binlog_format=[ROW|STATEMENT|MIXED]
日志格式记录的方式(默认FULL)
1
binlog_row_image=[FULL|MINIMAL|NOBLOB]
日志加入实际提交的sql(默认OFF)
1
binlog_rows_query_log_events=[ON|OFF]

日志清理

# 定时清理,单位(天)
exprie_logs_days= days

# 指定清理(从000到009删除)
PURGE BINARY LOGS TO 'mysql-bin.010'

# 将指定时间之前的删除
PURGE BINARY LOGS BEFORE '2018-O9-02 13:22:21'

查看配置信息命令

sql语句	作用
`show variables like 'log_bin';`	查询binlog日志是否开启
`show variables like 'binlog_format';`	查看日志格式
`show master logs;`	查看所有 Binlog 的日志列表
`show master status;`	查看最后一个 Binlog 日志的编号名称，及最后一个事件结束的位置（pos）
`flush logs;`	刷新 Binlog，此刻开始产生一个新编号的 Binlog 日志文件
`reset master;`	清空所有的 Binlog 日志
`show binlog events;`	查看第一个 Binlog 日志
`show binlog events in ‘binlog.000030’;`	查看指定的 Binlog日志
`show binlog events in ‘binlog.000030’ from 931;`	从指定的位置开始，查看指定的 Binlog 日志
`show binlog events in ‘binlog.000030’ from 931 limit 2;`	从指定的位置开始，查看指定的Binlog日志，限制查询的条数
`show binlog events in ‘binlog.000030’ from 931 limit 1, 2;`	从指定的位置开始，带有偏移，查看指定的 Binlog 日志，限制查询的条数

binlog日志格式

Row的日志
statement模式

mixed模式

1	# 普通操作使用statement,同步会出现问题的操作选择row格式

Binlog 的 Event 类型

Event Type	事件	重要程度
QUERY_EVENT	与数据无关的操作， begin、drop table、truncate table 等	了解即可
XID_EVENT	标记事务提交	了解即可
TABLE_MAP_EVENT	记录下一个操作所对应的表信息，存储了数据库名和表名	非常重要
WRITE_ROWS_EVENT	插入数据，即 insert 操作	非常重要
UPDATE_ROWS_EVENT	更新数据，即 update 操作	非常重要
DELETE_ROWS_EVENT	删除数据，即 delete 操作	非常重要

Mysql存储引擎的问题

Mysql常用的存储引擎

引擎名称	事务	说明
MYISAM	N	Mysql5.6之前的默认引擎,最常用的非事务型存储引擎
CSV	N	以CSV格式存储的非事务型存储引擎
Archive	N	只允许查询和新增数据而不允许修改的非事务型存储引擎
Memory	N	是一种易失性非事务型存储引擎
InnoDB	Y	最常用的事务型存储引擎
NDB	Y	Mysql集群所使用的内存型事务存储引擎

InnoDB

事务型存储引擎支持ACID
数据按主键聚集存储
支持行级锁及MVCC
支持Btree和自适应Hash索引
支持全文和空间索引

问题

Innodb不支持在线修改表结构的场景

操作	语法
加全文索引	CREATE FULLTEXT INDEX name ON table(column);
加空间索引	ALTER TABLE geom ADD SPATIAL INDEX(g)
删除主键	ALTER TABKE tbl_name DROP PRIMARY KEY
增加自增列	ALTER TABLE t add column id int auto_increment not null primary key
修改列类型	ALTER TABLE tbl_name CHANGE c1 c1 NEW_TYPE
改表字符集	ALTER TABLE tbl_name CHARACTER SET = charset_name

事务和并发控制

并发所带来的问题

脏读

一个事务读取了另一个事务未提交的数据
不可重复读

一个事务前后两次读取的同一数据不一致
幻读

指一个事务两次查询的结果集记录数不一致

事务的隔离性

Innodb的隔离级别

隔离级别	脏读	不可重复读	幻读	隔离性	并发性
顺序读(`SERIALIZABLE`)	N	N	N	最高	最低
可重复读(`REPEATABLE READ`) 默认	N	N	N
读以提交(`READ COMMITTED`)	N	Y	Y
读未提交(`READ UNCONNITTRED`)	Y	Y	Y	最低	最高

设置事务隔离级别

# 语法
SET [PERSIST|GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
  {
       REPEATABLE READ
     | READ COMMITTED
     | READ UNCOMMITTED
     | SERIALIZABLE
   }

PERSIST:对所有新的连接生效,而且重启不会失效
GLOBAL:对所有新的连接生效,重启会失效
SESSION(开发人员使用):只对当前连接生效

innodb中的锁

查询需要对资源加共享锁(S)
数据修改需要对资源加排它锁(x)

	排它锁	共享锁
排它锁	不兼容	不兼容
共享锁	不兼容	兼容

Mysql备份和恢复类的问题

常用的备份工具

名称	特点
mysqldump	最常用的逻辑备份工具,支持全量备份及条件备份
mysqlpump	多线程逻辑备份工具,mysqldump的增强版本
xtrabackup	InnoDB在线物理备份工具,支持多线程和增量备份

mysqldump

优点
1. 备份结构为可读的sql文件,可用于跨平台跨版本恢复数据
2. 备份文件的尺寸小于物理备份,便于长时间存储
3. Mysql发行版自带工具,无需安装第三方软件
缺点
1. 只能单线程执行备份恢复任务,备份恢复速度较慢
2. 为完成一致性备份需要对备份表加锁,容易造成阻塞
3. 会对InnoDB Buffer Pool造成污染

mysqlpump

优点
1. 语法同mysqldump高度兼容,学习成本低
2. 支持基于库和表的并行备份,可以提高逻辑备份的性能
3. 支持使用ZLIB和LZ4算法对备份进行压缩
缺点
1. 基于表进行并行备份,对于大表来说性能较差
2. 5.7.11之前版本不支持一致性并行备份
3. 会对InnoDB Buffer Pool造成污染

xtrabackup

优点
1. 支持InnoDB存储引擎的在线热备份,对innodb缓冲没有影响
2. 支持并行对数据库的全备和增量备份.
3. 备份和恢复效率比逻辑备份高
缺点
- 做单表恢复时比较复杂
- 完整的数据文件拷贝,故备份文件比逻辑备份大
- 对跨平台和数据库版本的备份恢复支持度不如逻辑备份

Mysql优化和异常处理

数据库服务器负载过大的原因

服务器磁盘IO超负荷
存在大量阻塞线程
存在大量并发慢查询
存在其他占用CPU的服务
服务器硬件资源原因

排查方法

慢查询造成的磁盘IO爆表的原因

Mysql输出大量日志
Mysql正在进行大批量写
慢查询产生了大量的磁盘临时表

慢查询产生了大量的磁盘临时表排查

1	grep /tmp mysql_2018_0908_15.txt

慢查询产生了大量的磁盘临时表解决方式

优化慢查询,减少使用磁盘临时表

Mysql参数查询

Mysql版本的问题

Mysql的发行版有哪些

各个发行版本之间的区别

服务器特性

高可用特性

安全性

开发及管理

Mysql8.0版本的新特性

服务器功能

用户及安全

InnoDB功能

Mysql升级的问题

升级前需要考虑的问题

1. 升级可以给业务带来的益处

2. 升级可能对业务带来的影响

3. 数据库升级方案的制定

4. 升级失败的回滚方案

Mysql用户管理的问题

Mysql数据库账户定义

Mysql常用的用户权限

如何为用户授权

如何保证数据账户的安全

迁移数据库账户

用户管理问题

Mysql服务器配置的问题

SQL_MODE

常用的sql Mode

Mysql常见的性能参数

服务器配置参数

存储引擎参数

Mysql日志类问题

Mysq常用的日志类型

错误日志(error_log)

作用

配置参数

常规日志(general_log)

作用

配置参数

慢查日志(slow_query_log)

作用

配置参数

二进制日志(binary_log)

作用

配置参数

查看配置信息命令

binlog日志格式

Binlog 的 Event 类型

Mysql存储引擎的问题

Mysql常用的存储引擎

InnoDB

问题

事务和并发控制

并发所带来的问题

事务的隔离性

Mysql备份和恢复类的问题

常用的备份工具

mysqldump

mysqlpump

xtrabackup

Mysql优化和异常处理

数据库服务器负载过大的原因

排查方法

慢查询造成的磁盘IO爆表的原因

慢查询产生了大量的磁盘临时表排查

慢查询产生了大量的磁盘临时表解决方式