Mysql&Mariadb我爱编程

MySQL(5)运维实践

2017-05-31  本文已影响0人  极客圈

MySQL运维实践

5.1-MySQL日志系统

什么是日志

MySQL日志的分类

分类 日志名称
服务器日志 服务错误日志
服务器日志 慢查询日志
服务器日志 综合查询日志
事务日志 存储引擎事务日志
事务日志 二进制日志

服务错误日志

慢查询日志

slow_query_log = 1
slow_query_log_file = /data/mysql_data/node-1/mysql-slow.log
long_query_time = 5

综合查询日志

general_log = 1
general_log_file = /data/mysql_data/node-1/mysql-slow.log

查询日志的输出与文件切换

log_output={file|table|none}

flush log;

存储引擎事务日志

InnoDB事务日志重用机制

二进制日志binlog

开启binlog

log_bin = c:/tmp/mylog/mysql-bin
sql_log_bin = 1
sync_binlog = 1

show binary logs;

binlog管理

max_binlog_size = 100MB
expire_logs_days = 7

purge binary logs to 'mysql-bin.000009';
purge binary logs before '2016-4-2 21:00:40'

查看binlog内容

show binlog events in 'mysql-bin.000011';
show binlog events in 'mysql-bin.000011' from 60 limit 3;

mysqlbinlog c:/tmp/mylog/mysql-bin.000001
--start-datetime | --stop-datetime
--start-position | --stop-position

binlog格式

binlog_format = {ROW|STATEMENT|MIXED}

mysqlbinlog --base64-output=decode-rows -v c:/tmp/mylpg/mysql-bin.000001

5.2-MySQL数据备份

基本指数 - 备份用途

基本知识 - 备份内容

基本知识 - 冷备份与热备份

基本知识 - 物理备份与逻辑备份

基本知识 - 本地备份与远程备份

基本知识 - 全量备份与增量备份

基本知识 - 备份周期

考虑因素：

常用工具及用法

常用工具及用法 - mysqldump

MySQL官方自带的命令行工具

主要示例：

# 备份所有数据库
mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock --all-databases > /dbbackup/all_db.sql
# 备份指定的数据库
mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock --databases db2 > /dbbackup/db2.sql
# 备份单个表
mysqldump -uroot -p123456 --socket=/var/run/mysqld/mysqld.sock db2 t1 >/dbbackup/db2_t1.sql
# 还原表
mysql > source /dbbackup/db2_t1.sql

mysqldump --single-transaction -uroot -p123456 --all-databases > /dbbackup/add_db_2.sql

mysqldump -utest -ptest -h192.168.0.68 -P3306 --all-databases > /dbbackup/remote_bakall.sql

mysqldump -uroot -p123456 --single-transaction --fields-terminated-by=, db1 -T /tmp

常用工具及用法 - xtrabackup

特点：

xtrabackup备份原理

实用脚本innobackupex

innobackupex备份基本流程

start xtrabackup_log -> copy .ibd; ibdata1 -> FLUSH TABLE WITH READ LOCK -> copy .FRM; MYD; MYI; misc files -> Get binary log position -> UNLOCK TABLES -> stop and copy xtrabackup_log

innobackupex使用

主要示例：

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf /dbbackup

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-dir /dbbackup/2016-4-3_13:24:32 /dbbackup

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --stream=xbstream /dbbackup/ > /dbbackup/stream.bak

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --parallel=4 /dbbackup/

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --throttle=10 /dbbackup/

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --compress --compress-thread 4 /dbbackup/

如何制定备份策略

需要考虑的因素

5.3-MySQL数据恢复

什么时候需要恢复数据

数据恢复的必要条件

数据恢复思路

反转SQL语句

例：

t1(id primary key, a int)

反转SQL语句：

insert into t(id, a) values(1, 1) -> delete t1 where id=1 and a=1
update t1 set a=5 where id=1 -> update t1 set a=1 where id=1
delete from t1 where id=1 -> insert into t(id, a) values(1, 1)

数据库恢复工具与命令

详细示例讲解

恢复误删除数据

case：误操作，删除数据忘记带完整条件，执行delete from user where age > 30 [and sex=male]

需求：将被删除的数据还原

恢复前提：完整的数据库操作日志(binlog)

delete from user where sex='female';

# 首先需要找到binlog里的信息
mysqlbinlog -vv mysql-bin.000001
# 找出sql语句，然后写出反转sql语句

恢复误删表、库

case：业务被黑，表被删除了(drop teble user)

需求：将表恢复

前提：备份 + 备份以来完整binlog

innobackupex --apply-log /dbbackup/filename
# 查看binlog的位置点
cat xtrabackup_binlog_info
# 查看结束点
mysqlbinlog -vv filename

mysqlbinlog -vv --start-position=2556990 -- stop-position=2776338
mysqlbinlog -vv --start-position=2556990 -- stop-position=2776338 | mysql -uroot -p123456 --sock=/dbbackup/mysql_3309/mysqld.sock

课程小结

5.4-MySQL线上部署

MySQL线上部署

考虑因素：

二进制安装MySQL

编译安装MySQL

MySQL升级

MySQL多实例安装

MySQL多实例部署

为啥多实例部署？

MySQL线上安装小结

5.5-MySQL主从复制

MySQL主从复制

MySQL主从复制用途

MySQL主从复制部署

主从部署必要条件

主从部署步骤：

MySQL复制存在的问题

存在的问题

解决方法：

MySQL semi-sync(半同步复制)

半同步复制

MySQL异步复制

./sorence/images/04.png

异步复制

MySQL semi-sync(半同步复制)

./sorence/images/05.png

半同步复制

配置MySQL半同步复制

只需一次：

主库：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从库：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

动态设置：

主库：

SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout=N; master 延迟切异步

从库：

SET GLOBAL rpl_semi_sync_slave_enabled=1;

配置MySQL并行复制

并行复制

设置

set global slave_parallel_workers=10; 设置sql线程数为10

联级复制

A -> B -> C

B中添加参数：
log_slave_updates
B将把A的binlog记录到自己的binlog日志中

复制监控

查询从库状态：

show slave status\G

复制出错处理

常见：1062(主键冲突) 1032(记录不存在)
解决：手动处理
或：
跳过复制出错
set global sql_slave_skip_counter=1

总结

5.6-MySQL日常运维

DBA运维工作

日常

导数据及注意事项

数据修改及注意事项

表结构变更注意事项

加权限及注意事项

问题处理(数据库慢？)

小结

5.7-MySQL参数调优

为什么要调整参数

优化之前我们需要知道什么

服务器上需要关注哪些

磁盘调度策略-write back

磁盘调度策略-write through

Write Back VS Write Through

RAID

RAID0 VS RAID1

RAID5 VS RAID10

RAID如何保证数据安全

MySQL有哪些注意事项

部署MySQL的要求

系统调优的依据：监控

通常关注哪些MySQL Status

MySQL参数调优

读优化

innodb_buffer_pool_size

innodb_thread_concurrency

写优化

哪些参数有助于提高写入性能

主要影响MySQL写性能的两个参数

innoDB_flush_log_at_trx_commit

sync_binlog

如何保证事务的安全

(加锁)-> xa_prepare, Fsync -> Write And Fsync Binlog -> InnoDB Commit, Fsync ->(释放锁)

串行有哪些问题

社区和官方的改进

Tips:

InnoDB Redo log

Redo log的作用

Redo log有哪些问题

innodb_io_capacity

Insert Buffer

尽可能多的随机请求合并为顺序请求才是提高数据库性能的关键

总结

上一篇下一篇

猜你喜欢

热点阅读