mysql存储博客日记_mysql运维日记

mysql半同步复制

半同步的特性

收到ack或者超时关闭半同步

从库只有将binlog flush到repay log才会给主库的等待线程发送ack

超时转换为异步复制后，当至少一个半同步从节点赶上来是，主库便会自动转换为半同步复制

半同步必须在主从库上都是打开的状态，否则便为异步复制

相较于异步复制,半同步变慢时间至少是TCP/IP一次发送与接收所用的时间(多IDC部署本地至少有一个slave)

半同步主库端

after_flush -- 最后一个event才需要发送ACK

after_sync -- 从库数据可能会多余主库数据

after_commit -- 从库会丢数据，主库是commit后再等待

after_rollback -- 同after_commit

transmit_start -- 发送binlog之前做判断，所有等待这个点的主库线程都可以不等了，直接commit

transmit_stop -- 主库想从库发送完binlog结束之后

defore_send_event -- 发送binlog之前，是否需要从库发送ACK信息

after_send_event -- binlog发送完成之后，

after_reset_master -- reset_master语句执行之后

半同步从库端

半同步实现

插件安装

半同步自动开关

MySQL5.7多线程复制

延迟优化方法

增加buffer_pool的大小、缓存更多数据，减少IO压力

增大log_buffer_size及group，减少buffer_pool的刷盘IO，提升写入性能

修改flush_method为O_DIRECT，提升写入性能

如果可以的话，关掉从库binlog，或者log_slave_uodates

修改参数innodb_flush_log_at_trx_commit为0或者2

如果没有关掉binlog，修改参数sync_binlog为0或者更大的数，减少IO的压力

MySQL5.6多线程复制

1.数据库比较多

2.每个数据库的写入比较均匀

因为MySQL5.6复制分发级别设置的是库

MySQL5.7多线程复制

所有处于prepare阶段的事务都可以并行提交

last_commit

ordered commit

flush:stage #1 flushing trans to bin log

binlog写入文件

写入成功后通知dump线程dump binlog发送给slave

sync:stage #2 syncing binlog file to disk

commit:stage #3 commit all trans in order.(取决于参数binlog_order_commits设置)

多线程复制分发原理

比较last_commit与sequence_number

异常故障恢复

1.找到链接主库的信息-- slave_master_info

2.找到复制位置及线程个数  -- slave_relay_log_info

3.找到每个线程的复制信息 -- slave_worker_info

大量MySQL表导致复制变慢的问题

performance_schema_max_file_instances=open_file_limit/0.65

快速删除大表

buffer pool mutex

flush list mutex

通过创建硬链接的方式删除ibd文件 --分块truncate os大文件

galera cluster的处理

单实例set @@session.wsrep_on=off;ln硬链接;drop table xxx；  -- 循环所有实例

两条不同的插入语句导致的死锁

环形死锁

唯一索引，联合唯一索引

隔离级别

gap锁

MySQL在并发删除同一行数据时导致死锁的分析

并发数

隔离级别

业务控制

参数sql_slave_skip_counter的奥秘

sql_slave_skip_counter=1，跳过整个事务，多个事件，有可能包含多个dml，跳过后确定是否需要修补数据

sql_slave_skip_counter>1，跳过一个事件减1，不可控

binlog中的时间戳

InnoDB中Rowid对binlog的影响

Rowid是存储引擎层的东西 ，不会记录到binlog中

MySQL备份：Percona Xtrabackup的原理与实践

备份背景及类型

认识percona Xtrabackup

Xtrabackup的工作流程

innobackupex   fork Xtrabackup备份innodb文件

两种线程

ibd复制线程

redo log复制线程  --最近checkpoint

检测文件唤醒innobackupex进程

innobackupex执行备份锁(lock table for backup),取得一致性位点，开始备份非innodb文件

innobackupex开始执行lock binlog for backup,获取binlog位置

创建Xtrabackup_binlog_info,写入位点信息

后续工作

Xtrabackup的备份原理

lock tables for backup与flush tables with read lock的区别

xtrabackup_binlog_info与xtrabackup_slave_info区别

Xtrabackup所需权限

innobackupex常用备份选项说明

MySQL分库分表

分库分表的种类

表分区

垂直拆分

单实例拆分成多实例

字段多，可以独立拆分出来

水平拆分

分库分表的原则

原则零:能不分则不分

原则一：正常的运维影响正常的业务访问

数据库备份

表修改

热点数据频繁访问  --水平拆分，减少锁粒度

原则二：表设计不合理，需要对某些字段进行垂直拆分

原则三：某些数据表出现无穷增长的情况

原则四：安全性和可用性的考虑

原则五：业务耦合性考虑

分库分表实现

数据库层的实现

replication

触发器

业务层的实现

MySQL数据安全

单机安全

进程奔溃

主机宕机

恢复机制：undo、redo

磁盘数据完整性：doublewrite

日志刷盘策略：innodb_flush_log_at_trx_commit

集群安全

服务器硬件或操作系统故障，系统无法重启

原生replication

第三方插件：galera

备份安全

时效性

恢复时间最小化：针对Xtrabackup做好apply_log

备份可用性：apply_log

MySQL实例安全保证

doublewrite:针对页面写失败，导致无法重启

redo log：crash recover

innodb_flush_log_at_trx_commit

0:每隔1秒刷一次，刷到文件

1:每次提交都写文件，刷盘

2:每次提交都写文件，不刷盘，每隔1秒刷一次盘

宕机与innodb_flush_log_at_trx_commit的关系

1.进程挂掉，os正常

0：丢一秒

1：不丢失

2：os刷盘，不丢失

2.主机宕机

0：丢一秒

1：不丢失

2：丢一秒

MySQL集群安全

sync_binlog

0:事务提交时，binlog写入文件(OS cache)不刷盘，主机不宕机不丢失

1:事务提交时都会刷盘

N：每N次事务提交，MySQL调用文件系统的刷新操作刷盘，主机宕机或服务挂掉都有可能丢失一些事务

两阶段提交(innodb_support_xa)

prepare阶段：告诉InnoDB引擎做prepare，innodb更改事务状态，并将redo log刷入磁盘

commit阶段：先记录binlog，然后commit

主从复制参数的影响

binlog_format

master_info_repository与sync_master_info

semi_Syng_replication方式复制

参数

rpl_semi_sync_master_wait_point

after_commit：commit后等待ACK，主库宕机，slave可能丢数据

after_sync：接收到ACK后再commit，slave可能多数据

MySQL集群化如何保证数据库安全

galera cluster

多线程的并行复制，自带节点管理机制，主动监测集群节点状态，自动管理有问题的数据节点，多点写入和平滑扩容

所有的表都必须是innodb表

参数设置

innodb_doublewrite=on

innodb_flush_log_at_trx_commit=0

sync_binlog=0

MGR

innodb_doublewrite=on

innodb_flush_log_at_trx_commit=0

binlog-format=row

master-info_repository=table

relay-log-info-repository=table

MySQL性能拾遗

适当的数据文件大小

碎片空洞问题

show table status data_length/data_free optimize table table_name/alter table table_name engine=innodb重建表空间;

设计问题

1.字段的合理设置

最小的也是最有效的，

ip无符号整形数字，

not null：不需要逐个值去判断是否为null，每个字段会节省1bit的空间

2.行存储格式

如果空间有问题，可以选择compact

3.索引问题

合理设计表结构

冗余存储

查询某人的好友，订单信息

查询特别好友

拆分存储

重复存储

正确使用索引

1.最左匹配原则

2.计算列无法使用索引

3.否定条件不能使用索引

4.join连接的字段类型不一致，表的字符集不一致，也不能使用索引

5.覆盖索引

6.其他

MySQL系统参数

general_log

query_cache_size

sort_buffer_size

join_buffer_size

tmp_table_szie

innodb_buffer_pool_size

innodb_buffer_pool_instances

innodb_log_file_size

innodb_log_files_in_group

innodb_numa_interleave   -- 0,1,2

innodb_old_blocks_pct

innodb_old_blocks_time

innodb_autoinc_lock_mode

innodb_flush_method

innodb_doublewrite

innodb_io_capacity

innodb_thread_concurrency

inndb_flush_log_at_trx_commit

sync_binlog

binlog_format

binlog_order_commits

tx_isolation

slave_parallel_workers

内存和cpu

cpu  cores 主频

内存  SQL优化

磁盘的革命               -- raid10 raid5 r/s  顺序 随机 存储 读取 缓存 flash SSD

SSD基本概念

SSD优越性

MGR

GR概述

组的概念

创建组：当组的第一个成员启动时，需要对组进行初始化

加入组

离开组

多组复制

单独的通信机制

独立的端口传输binlog

group replication服务模式

单主模式

主成员的自动选取和切换

读写模式的自动切换

failover：从其他成员查询到主成员的UUID

多主模式

自增字段的处理

1.直接通过系统变量来配置

auto_increment_increment

auto_increment_offset

2.通过插件配置

group_replication_auto_increment_increment

段大小设置

多主模式的限制

不支持串行化的隔离级别

不支持外键级联的操作

限制的检查

group_replication_enforce_update_everywhere_checks = true

DDL语句并发执行的问题

对多主模式在使用上的一些思考

可以当作单主来用

服务模式的配置

group_replication_single_primary_mode=OFF

默认是单主模式，如果要使用多主，在别的成员加入主前，设置该参数为OFF，这个参数不能在线修改

binlog event的多线程执行

group_replication_applier通道

start slave sql_thread for channnel 'group_replication_applier'

stop slave sql_thread for channnel 'group_replication_applier'

基于主键的并行执行

set global slave_parallel_type='logical_clock'

set global slave_parallel_workers=N

set global slave_preserve_commit_order=ON

搭建GR复制环境

MySQL的参数设置

开启binlog和relaylog

server_id=1

log_bin=binlog

log_slave_updates = on

relay_log=relay-log

开启GTIT功能

gtid_mode=on

enforce_gtid_consistency=on

设置row格式的binlog

禁用binlog_checksum

使用系统表来存储slave信息

开启并行复制

开启主键信息采集功能

GR插件的使用

加载插件

启用插件

停用插件

GR插件的基本参数设置

设置组的名字

设置成员的本地地址

设置种子成员的地址

设置成员ip白名单

将参数写入配置文件

group replication的数据库用户

_gr_user@localhost用户

root用户

group replication组初始化

组初始化特有的参数

组初始化的步骤

新成员加入组

配置group_replication_recovery通道

成员加入组的步骤

group replication的高可用性

组内成员数量变化

强制移除故障成员

group replication的监控

group replication基本原理

状态机复制

分布式的状态机复制

分布式的高可用数据库

深入理解group replication中事务的执行过程

本地事务控制模块

发送事务信息

等待全局事务认证模块的认证结果

认证结果的处理

成员间的通信模块

MySQL Document Store

新的json数据类型和json函数

json数据类型

json函数

json_extract