日常运维
DBA运维工作
日常
- 导数据，数据修改，表结构变更
- 加权限，问题处理
其它
- 数据库选型部署，设计，监控，备份，优化等
日常运维工作：
- 导数据及注意事项
- 数据修改及注意事项
- 表结构变更及注意事项
- 加权限及注意事项
- 问题处理，如数据库响应慢
导数据及注意事项
- 数据最终形式（csv,sql文本，还是直接导入某库中）
- 导数据方法（mysqldump，select into outfile，）
- 注意事项
- 导出为csv格式需要file权限，并且只能数据库本地导
- 避免锁库锁表（mysqldump使用--single-transaction选项不锁表）
- 避免对业务造成影响，尽量在镜像库做
数据修改及注意事项
- 修改前切记做好备份
- 开事务做，修改过完检查好了再提交
- 避免一次修改大量数据，可以分批修改
- 避免业务高峰期做
表结构变更注意事项
- 在低峰期做
- 表结构变更是否会有锁？（5.6包含online ddl 功能）
- 使用pt-online-schema-change完成，表结构变更
- 可以避免主从延时
- 可以避免负载过高，可以限速
percona维护了mysql dba 必看的博客
加权限及注意事项
- 只给符合需求的最低权限
- 避免授权时修改密码
- 避免给应用账号super权限
问题处理（数据库慢？）
- 数据库慢在哪里？
- 是查询慢还是写入慢
- 是秒级别慢，还是毫秒级别慢
- show processlist 查看mysql连接信息
- 查看系统状态（iostat,top,vmstat）
小结
- 日常工作比较简单，但是任何一个操作都可能影响线上服务
- 结合不同环境，不同要求选择最合适的方法处理
- 日常工作应该求稳不求快，保障线上稳定是DBA的最大责任
不求最快，但求最稳；
例子
1.修改t1表中id<5的数据
1)备份 select * from t1 where id<5 into outfile '/tmp/t1id5.txt'; 2)执行修改 begin; update t1 set b=100,c=100 where id<5; select * from t1 where id<5; rollback;/commit;
2.表结构变更
1)5.5版本： alter table t55 add c1 int; delete from t55 where id<100;(卡住一会后才执行) 2)5.6版本： use db1; alter table t55 add c1 int; delete from t55 where id<100;(执行顺畅) alter table t1 modify c1 varchar(90); delete from t55 where id<100;(卡住一会后才执行) 3)pt-online-schema-change工具 ./pt-online-schema-change --user=root --password=123456 --host=localhost --socket=/mysqldata/node4/mysqld.sock D=db55,t=t55 --alter "add c2 int" --print --dry-run
3.加权限
grant select,insert,delete on *.* to netease@'localhost' identified by '163'; #mysql -unetease -p163 --socet=/mysqldata/node3/mysqld.sock --port=4001(登陆成功) grant select,insert,delete on *.* to netease@'localhost' identified by '123'; #mysql -unetease -p163 --socet=/mysqldata/node3/mysqld.sock --port=4001(登陆失败)
4.导数据
use db1; select count(*) from t1;(先看一下数据量) 1)mysqldump导出 #mysqldump -uroot -p123456 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t1.sql grant select on *.* to netease@'localhost' identified by '163'; #mysqldump -unetease -p163 --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t2.sql(报错，没有锁表权限) #mysqldump -unetease -p163 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 > /tmp/t2.sql(成功) #mysqldump -uroot -p123456 --single-transaction --socket=/mysqldata/node3/mysqld.sock db1 t1 -T /tmp use db1; 2)以file权限into outfile导出数据 select * from t1 into outfile '/tmp/t1_2.txt'; select t1.c,t3,b from t1.id=t3.id into outfile '/tmp/t13.txt';
5.数据库慢问题
../tcpstat --port 4001 -t 1 -n 0（tcpstat,查看每一个tcp连接的响应时间，percona公司出品）
参数调优
为什么要调整参数
- 不同服务器之间的配置，性能不一样
- 不同业务场景对数据的需求不一样
- mysql的默认参数只是个参考值，并不适合所有的应用场景
优化之前我们需要知道什么
- 服务器相关的配置
- 业务相关的情况
- mysql相关的配置
服务器相关的配置
- 硬件情况
- 操作系统版本
- CPU,网卡节电模式
- 服务器numa设置---内存分片，cpu对应内存；
- RAID卡缓存
磁盘调度策略--write back
- 数据写入cache即返回，数据异步的从cache刷入存储介质
磁盘调度策略--write through
- 数据同时写入cache和存储介质才返回写入成功
write back 性能高于 write through
而write through 的安全性更高。
RAID
RAID --廉价的存储阵列
RAID0
- 简单就是将多块盘当做一块盘来使用；容量是多盘的和，性能也是多盘之和；
- 问题，就是当其中一块盘损坏后，无法保证其数据的安全性；
RAID1
- 指两块盘做相互的镜像--达到高可用
- 问题，只能使用两块盘来做，存储空间 有限制
RAID5
- 至少使用三块盘，总存储空间只有两块；因为它需要存储校验数据块；
- 高可用的实现，是通过校验数据块，来恢复数据；
- 局限，只能坏一块盘，才能通过另外两块盘的 存储校验数据块，进行数据恢复，如果坏了两块盘则不能进行数据恢复
RAID10
- 先对两块盘做RAID1，再做RAID0
- RAID1保证数据安全性，RAID0保证数据扩展性；
- 局限，做RAID1的两块盘同时坏了，则也不能保证数据安全性；
RAID如何保证数据安全
- BBU(Backup Battery Unit)
-
- 保证在电池有电的情况下，即使服务器发生掉电或者宕机，也能够将缓存中的数据写入到磁盘，从而保证数据的安全
注意事项
mysql有哪些注意事项
- mysql的部署安装
- mysql的监控
- mysql参数调优
部署mysql的要求
- 推荐的mysql版本：>=mysql5.5
- 推荐的mysql存储引擎：innodb
系统调优的依据：监控
- 实时监控mysql的SLOW log
- 实时监控数据服务器的负载情况
- 实时监控mysql内部状态值
网易内部监控的参数：
- binlog文件大小（MB）
- BufferPool命中率（%）
- cpu利用率（%）
- 磁盘读操作延时（ms/op）
- 磁盘读取字节数（KB/s）
- 磁盘读取次数（次/秒）
- 占用磁盘存储空间（MB）
- 磁盘写入操作延时（ms/op）
- 磁盘写入字节数（KB/S）
- 磁盘写入次数（次/秒）
- 磁盘IO利用率（%）
- 占用内存量（%）
- 内存使用率（%）
- 一般事务提交操作（次/秒）
- 删除操作（次/秒）
- 插入操作（次/秒）
- 查询操作（次/秒）
- 更新操作（次/秒）
- 二阶段事务提交操作（次/秒）
通常关注哪些mysql status
- com_select/update/delete/insert
-
- 看数据库的请求是否变多
- Bytes_received/Bytes_sent
-
- 看 mysql总的吞吐量
- Buffer Pool Hit Rate
-
- innodb内存的命中率决定了性能
- Threads_connected/Threads_created/Threads_running
-
- 前两个多的话， 可以判断 应用是否使用连接池，或者连接池使用是否合理
- 活跃连接很多，说明数据库很忙，可能是被人恶意攻击；
为什么要调整mysql的参数：
- 需要根据业务区动态调整这个通用的mysql数据库，使其变成专用数据库
- 有些参数，很可能是老版本做的，可能是为了限流和保护用的，但是随着机器的性能提高这些参数，显然是不合适的。
读优化
- 合理利用索引对mysql查询性能至关重用
- 适当的调整mysql参数也能提升查询性能
innodb_buffer_pool_size：
缓存池大小，innodb自己维护一块内存区域完成新老数据的替换
innodb_thread_concurrency:
innodb内部并发控制参数，设置为0代表不做控制
如果并发请求较多，餐宿设置较小，后进来的请求将会排队
写优化
- 表结构设计上使用自增字段作为表的主键
- 只对合适的字段加索引，索引太多影响写入性能
- 监控服务器磁盘IO情况，如果写延迟较大则需要扩容
- 选择正确的mysql版本，合理设置参数
哪些参数有助于提高写入性能
- innodb_flush_log_at_trx_commit&&sync_binlog
-
- 控制redo log 刷新
- 控制二进制日志的刷新
- innodb log file size
- innodb_io_capacity
- innodb insert buffer
innodb_flush_log_at_trx_commit：0,1,2
n = 0（高效，但不安全--无论服务器宕机或者mysql宕机都会丢数据）
每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上
n = 1 （低效，非常安全--都不会丢数据）
每个事务提交时候，把事务日志从缓存区写到日志文件中，并且，刷新日志文件的数据到磁盘上，优化使用此模式保证数据安全性
n = 2（高效，但不安全--服务器宕机会丢数据）
每个事务提交的时候，把事务日志数据从缓存区写到日志文件中，每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；
sync_binlog
- 控制每次写入binlog，是否都需要进行一次持久化
如何保证事务安全
- innodb_flush_log_at_trx_commit&&sync_binlog 都设为1
- 事务要和binlog保证一致性---才不会导致主从不一致
事务提交过程
串行有哪些问题
- SAS盘每秒只能有150--200个Fsync
- 换算到数据每秒只能执行50--60个事务
社区和官方的改进
redo log 的作用
在数据库 崩溃后的数据恢复；
redo log的问题
- 如果写入频繁导致redo log里对应的最老的数据脏页还没有刷新到磁盘，此时数据库将卡住，强制刷新脏页到磁盘
- mysql默认配置文件才10M，非常容易写满，生成环境中应该提高redo log 的大小
innodb_io_capacity
- innodb每次刷多少个脏页，决定innodb存储引擎的吞吐能力。
- 在SSD等高性能存储介质下，应该提高该参数以提高数据库的性能。
insert buffer
- 顺序读写 VS 随机读写
- 随机请求性能远小于顺序请求
将尽可能多的随机请求合并为顺序请求才是提高数据库性能的关键
insert buffer 对二级索引，的增删改，的操作缓存到 insert buffer中，然后将这些随机请求合并成顺序请求；
小结：
- 服务器配置要合理（内核版本，磁盘调度策略，RAID卡缓存）
- 完善的监控系统，提前发现问题
- 数据库版本要跟上，不要太新，也不要太老
- 数据性能优化：
-
- 查询优化：索引优化为主，参数优化为辅
- 写入优化：业务优化为主，参数优化为辅
总结
- 日常运维工作：
- 导数据，
- mysqldump，select into outfile,
- 避免锁库锁表，mysqldump --single-transaction;
- 数据修改
- 做好备份，
- 开事务做，
- 分批修改，
- 避免高峰期
- 表结构变更
- 低峰做
- 5.6后包含online ddl，
- 使用pt-online-schema-change:避免主从延迟，限速；
- 加权限
- 最低权限，
- 避免授权时修改密码
- 不求最快，但求最稳；
- 导数据，
- 参数调优
- RAID0，RAID1，RAID5，RADI10，
- RAID如何保证数据安全：
- BBU,服务器掉电，使用电池电量将缓存内容刷新到磁盘
- 有助于提高写性能的参数：
- innodb_flush_log_at_trx_commit 控制redo log 刷新
- sync_binlog ：控制二进制日志的刷新
- innodb log file size: 重做日志循环写，如果太小，当新的写入来的时候，原日志文件写完且还没有持久化到磁盘，这时候就要阻塞写入；所以，增大事务日志大小，可能提升写性能；
- innodb insert buffer：
- 插入缓冲，将随机读写，通过这个缓冲，合并成可能的顺序读写，以提高写性能。
- 只对二级且非唯一索引生效；
- innodb_io_capacity：
- innodb每次刷新多少个脏页，决定innodb存储引擎的吞吐能力
- 在SSD，下应该提高该参数以提高数据库性能；
- 读优化：
- innodb_buffer_pool_size:缓存池大小
- innodb_buffer_pool_size：并发控制；
