运维同学需要掌握的 MySQL 数据库知识

连接层
服务管理层
SQL 解析, 优化器, 缓存;
插件式存储引擎
文件系统存储

1. 联合索引;
2. 前缀索引;
3. 覆盖索引;
4. 唯一索引;
5. 主键及第二索引的区别;

1. 基于 binlog 格式(statement, row 或 mixed)实现复制;
2. binary log 和 relay log 的区别;
3. 高版本(5.6 及以上) GTID 的原理;
4. 常见 slave 错误的修复方法;  

pt-query-digest           # 主要用来排错
pt-online-schema-change   # 在线修改表结构

[mysql]
prompt = 'mysql \u@[\h:\p \d] > '
default-character-set = utf8
[mysqladmin]
default-character-set = utf8
[mysqlcheck]
default-character-set = utf8
[mysqldump]
default-character-set = utf8
[mysqlimport]
default-character-set = utf8
[mysqlshow]
default-character-set = utf8
[client]
port = 3396
socket = /export/mysql/node3306/data/s3306

[mysqld]
# common InnoDB/XtraDB settings
innodb_buffer_pool_size = 32G  # x 1.2 + 2GB for OS = 32GB node w/o MyISAM
innodb_data_file_path = ibdata1:200M:autoextend
innodb_log_file_size = 512M  # suitable for most environments
innodb_log_files_in_group = 4
innodb_log_buffer_size = 32M  # no bigger than max_allowed_packet
innodb_flush_log_at_trx_commit = 2
innodb_spin_wait_delay = 30
innodb_sync_spin_loops = 100
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_stats_on_metadata = 0  # disable innodb statistic when statistics sql was  running
innodb_stats_sample_pages = 8
innodb_max_dirty_pages_pct = 75
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000
innodb_open_files = 3000
innodb_read_io_threads = 8
innodb_write_io_threads = 4
innodb_io_capacity = 1000
skip-symbolic-links
default-storage-engine = innodb

# common business settings MySQL node
back_log = 1024
max_connections = 5000  # should be easy job in a big server
max_connect_errors = 100000
max_heap_table_size = 128M
open_files_limit = 65535
thread_cache_size = 128
transaction_isolation = REPEATABLE-READ
table_open_cache = 1024  # table_cache is deprecated in 5.1.3
tmp_table_size = 32M
lower_case_table_names = 1
event_scheduler = 1
log_bin_trust_function_creators = 1
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
join_buffer_size = 1M
net_buffer_length = 16K
thread_stack = 256K
myisam_sort_buffer_size = 16M  # myisam sort buffer size
myisam_repair_threads = 1  # myisam auto repair
key_buffer_size = 128M
query_cache_type = 1  # enable use of the query cache altogether
query_cache_size = 0  # disable query cache

tmpdir = /dev/shm
skip-name-resolve
skip-slave-start
character-set-server = utf8  # default-character-set is deprecated in 5.0
collation-server = utf8_general_ci
log_output = FILE
general_log = OFF
slow_query_log = 1  # ON is not recognized in 5.1.46
long_query_time = 1  # in seconds, determine slow query
general_log_file = query.log  # log is deprecated as of 5.1.29
slow_query_log_file = slow-query.log  # log_slow_queries and log_queries_not_using_index are deprecated as of 5.1.29
slave_skip_errors = 1062  # skip primary duplicate error
log-bin = mysql-bin.log
sync_binlog = 1  # BBU-backed RAID or flash

binlog_format = MIXED  # for replication
max_allowed_packet = 1024M  # same to master

mydumper   # 可选
mysqldump
xtrabackup

zabbix
grafana + prometheus 或 influxdb  # 更细粒度, 更方便故障排错

1. qps 过高(自定义);
2. 当前连接数过多(比如占 max connection 的一定比例);
3. 同时运行的线程过多(超过 cpu 核数);
4. 死锁及长时间运行的 sql 语句;
5. 慢查询监控(如果需要报警, 要提前做好聚合);

1. 硬件是否正常, 尤其磁盘和 RAID 卡(以及充放电)对数据库的 io 影响很大;
2. 修改表是否有合适的索引;
3. 开发人员是否过度提交(频繁的 select, update, insert 等), 程序是否可以避免过度提交, 引入 cache 等功能;
4. 部分大表是否可以归档清理;
5. 非及时的请求是否可以引入消息队列延后处理;
6. 一定要多关注慢查询, 阈值设为 1s 记录所有的慢查询;
7. 如果有条件, 最好能在测试环境做简单的功能压测;