mysql学习

he3912377

已于 2024-07-28 15:00:29 修改

阅读量592

点赞数 3

文章标签： mysql

于 2024-03-01 17:09:26 首次发布

本文链接：https://blog.csdn.net/he3912377/article/details/136401617

版权

查看磁盘调度策略
more /sys/block/vda/queue/scheduler
数据库应设置为deadline
echo"deadline" > /sys/block/sda/queue/scheduler
修改后需要重启mysql

--查看隔离级别
select @@tx_isolation;
select @@transaction_isolation;

--参数
--查看参数的信息
select * from variables_info limit 1 \G
default_authentication_plugin:默认的密码认证插件
master_info_repository:复制的相关信息保存在文件还是表里(mysql.slave_master_info,mysql.slave_relay_log_info)
gtid_mode=on
enforce_gtid_consistency=on

relay_log_recovery=ON
relay_log_info_repository=TABLE

sync_binlog=1
innodb_flush_log_at_trx_commit=1

binlog_rows_query_log_events:设置将具体的SQL以rows_query_log_event形式打印到binlog

查看glibc版本
ldd --version

--mysql启动失败,尝试启动
1 查看错误日志,端口被占用,参数名写错,有不支持的参数
2 通过mysqld启动 mysqld --default-file=my.cnf &
3 mysqld --no-defaults --basedir=/user/local/mysql --datadir=/data/mysql/3306/data/ --user=mysql
4 strace查看mysql启动过程的系统调用情况

查看当前用户
select user();

系统表中注册已加载的组件
rpm包安装的软件默认加载了validate_password
show plugins;
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS;
select * from mysql.component;
show variables like 'validate_password%';

加载validate_password
mysql5.7
install plugin validate_password soname 'validate_password.so';
vi /etc/my.cnf
plugin-load=validate_password.so

mysql8.0
install component 'file://component_validate_password';

--错误
--mysql8.0以下的客户端连接mysql8.0报错
Authentication plugin ‘caching_sha2_password‘ cannot be loaded
原因:mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password。
解决方法:
1 升级mysql客户端或驱动
2 将参数default_authentication_plugin设置为mysql_native_password
3 创建用户时指定auth_plugin为mysql_native_password
create user user1@'%' identified with mysql_native_password 'xxxxx';
创建用户不指定密码认证插件,实际上被默认指定

--主从复制
mysql8.0要加 get_master_public_key=1 ,主要是由于复制用户使用新的密码认证插件
change master to master_host='xxx', master_port=xxx, master_user='xxx', master_password='xxxx', get_master_public_key=1,master_log_file='binlog.000003',master_log_pos=155 for channel 'channel_201_3306';
基于gtid复制
change master to master_host='xxx', master_port=xxx, master_user='xxx', master_password='xxxx', master_auto_position=1;

--数据字典
mysql.slave_master_info 保存连接主库的信息,IO线程读取主库binlog信息(非实时,写入sync_master_info参数个事务后更新)
mysql.slave_relay_log_info sql线程重放relay_log的位置

--查看binlog事件
show binlog events in 'mysql-bin.000001';

--查看全局变量
show global variables like 'gtid_executed';

============================解读binlog============================
pager grep -iB 5 drop
show binlog events in 'mysql-bin.000003';

show binlog events in 'mysql-bin.000001';
mysqlbinlog -vv --start-position=845 --stop-position=1438 /mysql/logs/3306/binlog/21_mysql_bin.000001
mysqlbinlog -vv --base64-output=decode-rows --start-position=845 --stop-position=1438 /mysql/logs/3306/binlog/21_mysql_bin.000001
增量恢复时不能添加--base64-output=decode-rows,否则会数据不一致
mysqlbinlog mysql1_bin.000042 --start-position='451882869' |mysql -uroot -p

============================解读relaylog============================
show relaylog events in 'xxxxxxxx';
5.6开始,基于位点的复制,建议设置
relay_log_recovery=ON
relay_log_info_repository=TABLE

基于gtid复制,设置
sync_binlog=1
innodb_flush_log_at_trx_commit=1

binlog_rows_query_log_events:设置将具体的SQL以rows_query_log_event形式打印到binlog

binlog_parser.py工具解析binlog生成sql语句

============================主从复制搭建============================
mysqldump导出,在8.0.26以后使用--source-data,不要使用--master-data
mysql8.0要加 get_master_public_key=1 ,主要是由于复制用户使用新的密码认证插件
change master to master_host='xxx', master_port=xxx, master_user='xxx', master_password='xxxx', get_master_public_key=1,master_log_file='binlog.000003',master_log_pos=155 for channel 'channel_201_3306';

复制信息保存在两个数据字典里
mysql.slave_master_info 保存连接主库的信息,IO线程读取主库binlog信息(非实时,写入sync_master_info参数个事务后更新)
mysql.slave_relay_log_info sql线程重放relay_log的位置

8.0.22开始,start slave,stop slave,show slave status,show slave hosts和reset slave都被弃用
使用start replica,stop replica,show replaca status,show replicas和reset replica

8.0.23开始,change master to弃用,使用change replication source to

8.0.26开始,标识符(系统参数,状态变量)中的master,slave和MTS(multithreaded slave)被source,replica和mta(multithreaded applier)替换

============================gtid复制============================
基于gtid复制
change master to master_host='xxx', master_port=xxx, master_user='xxx', master_password='xxxx', master_auto_position=1;

在线将GTID的环境切换到非GTID
在线关闭gtid复制
从库执行  stop slave;
从库执行  show slave status;change master to master_host修改为位点的位置
从库执行  start slave;
主从库执行  set global GTID_MODE = ON_PERMISSIVE
主库执行  set global GTID_MODE = OFF_PERMISSIVE
这个时候已经从gtid转到传统复制了,不过还是中间状态
在从库执行  set global GTID_MODE = OFF_PERMISSIVE
主从库执行  select @@GLOBAL.GTID_OWNED;是不是为空,空则进行下一步
等待所有gtid事务应用完毕,也可以使用MASTER_POS_WAIT函数判断
主从库执行  set global gtid_mode=off;
主从库执行  set global enforce_gtid_consistency=off;
----永久生效,记得配置文件修改
gtid_mode=off
enforce_gtid_consistency=off

在线将传统复制切换到GTID复制
在线开启gtid复制
在主从库执行  set global enforce_gtid_consistency=WARN;
观察一段时间,看error日志里是否有违反gtid限制的报错
在主从库执行  set global enforce_gtid_consistency=on;
主从库执行  set global GTID_MODE = OFF_PERMISSIVE;
在从库执行  set global GTID_MODE = ON_PERMISSIVE;
在主库执行  set global GTID_MODE = ON_PERMISSIVE;
查看show status like 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';是否为0,等于0是表示所有连接都转为gtid复制
等待从库匿名事务应用完毕
或通过master_pos_wait判断
主库show master status;
从库执行 SELECT MASTER_POS_WAIT('master_log_file', master_log_pos);会一直等待pos之前的日志应用完

主从库执行 set global gtid_mode=on;
stop slave;
change master to master_auto_position=1;
start slave;

--永久生效,记得更改配置文件
gtid_mode=on
enforce_gtid_consistency=on

gtid复制的限制
不支持create table ... select ...
因为实际有两个gtid event,create和insert,gtid相同,insert会被忽略,8.0.21开始取消此限制
不允许事务,存储函数,触发器中执行create temporary table和drop temporary table
8.0.12开始,binlog为row或mixed无此限制
不能将innodb表和非innodb表放在一个事务内操作

============================半同步复制============================
rpl_semi_sync_master_wait_point参数
AFTER_SYNC:无损复制
AFTER_COMMIT:传统半同步复制

have_dynamic_loading:动态加载插件的参数

============================安装半同步复制
主库执行
install plugin rpl_semi_sync_master soname 'semisync_master.so';
或MySQL 8.0.26以上版本执行下面命令：
install plugin rpl_semi_sync_source soname 'semisync_source.so';

从库执行
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
或MySQL 8.0.26以上版本执行下面命令：
install plugin rpl_semi_sync_replica soname 'semisync_replica.so';

show plugins;
select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%Semi%';

============================启用半同步复制
主库执行
set global rpl_semi_sync_master_enabled=1;
或MySQL 8.0.26以后版本执行：
set global rpl_semi_sync_source_enabled = 1;

从库执行
set global rpl_semi_sync_slave_enabled=1;
或MySQL 8.0.26以后版本执行：
set global rpl_semi_sync_replica_enabled = 1;

持久化到配置文件
主从库都设置,切换后还可以继续使用
[mysqld]
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
# 或8.026版本添加：
# plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# rpl_semi_sync_source_enabled=1
# rpl_semi_sync_replica_enabled=1

重启从库IO进程
stop slave io_thread;
start slave io_thread;

查看是否是半同步复制
主:
show status like '%Rpl_semi_sync_master_status%';
从:
show status like '%Rpl_semi_sync_slave_status%';
都为ON,说明已启用

============================注意事项
1 主从都必须开启
2 响应的事件是二进制日志写入relay log
3 半同步复制会超时切换为异步复制,超时时间rpl_semi_sync_master_timeout,恢复后会切换为半同步复制
4 一主多从,不要求所有的从库都开启半同步复制
5 5.7 binlog dump负责发送日志并等待反馈,5.7之后有ack collector负责接受反馈
6 多了至少一个TCP/IP往返的时间,建议在低网络延时环境使用

rpl_semi_sync_master_wait_for_slave_count:主库需要等待从库反馈的个数
rpl_semi_sync_master_wait_no_slave
rpl_semi_sync_master_trace_level:半同步复制日志级别

状态变量
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------

============================并行复制============================
1 5.6基于库级别的并行
2 5.7基于组提交的并行
3 8.0基于writeset的并行

============================开启并行复制
从库设置:
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = on

5.7.22和8.0可以设置WRITESET方案进一步提升并行复制
主库设置
binlog_transaction_dependency_tracking = WRITESET_SESSION
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 25000
binlog_format=ROW

============================多源复制============================
以下两个参数必须设置为TABLE
master-info-repository=TABLE
relay-log-info-repository=TABLE
============================注意事项
1 多个主库可以是GTID,也可以不是.如果不一样GTID_MOD必须为ON_PERMISSIVE或OFF_PERMISSIVE
2 备库初始化要么全使用逻辑工具,或者只能第一个库使用物理工具
3 gtid_purged,8.0之前,如果主库都开启了gtid,第二个库导入要使用reset master.最后执行set global gtid_purged='uuid:1-9,uuid:1-14'为多个库gtid_purged合集
4 mysql8.0不需要手工设置gtid_purged,dump文件有添加的语句
5 binlog要为ROW模式
============================多源复制的管理
change master to master_host='xx',master_port=3306,master_user='repl',master_password='xx',master_log_file='binlog.000003',master_log_pos=812 for channel 'source_91';
show slave status for channel '';
start slave for channel '';
stop slave for channel '';
reset slave for channel '';
reset slave all for channel '';
不指定channel对所有channel生效

============================延迟复制============================
暂停SQL线程应用,并不会暂停IO线程接受日志
在主库执行后,要等待若干秒才在备库执行
===============使用场景:
1 应对主库的误操作,比如drop table
delete,update误操作,如果日志格式是ROW,可通过binlog闪回恢复.drop操作binlog只记录原生SQL,无法使用工具恢复
2 查看数据的历史状态
3 人为模拟主从延迟
也可使用flush tables with read lock模拟来模拟延迟
===============开启延迟复制
CHANGE MASTER TO master_host='xxxxx',master_port=xxxx,master_user='xxx',master_password='******',MASTER_LOG_FILE='mysql1_bin.000007', MASTER_LOG_POS=426114256 ,master_delay=28800;
stop slave;
CHANGE MASTER TO master_delay=28800;
start slave;

show slave status\G
SQL_Delay: 28800 ---期望的延迟时间
SQL_Remaining_Delay: 28774 ---需要等待多久才能到达期望延迟时间
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
===============使用延迟复制恢复主库误删的表
从库开启延迟复制
主库查看删表的binlog位置
show master status;
show binlog events in 'mysql-bin.000003';
pager grep -iB 5 drop
show binlog events in 'mysql-bin.000003';

从库恢复到drop之前的位点
stop slave;
CHANGE MASTER TO master_delay=0;
start slave until master_log_file='xxx',master_log_pos=xxx;

show slave status\G --确认恢复到指定位置
Master_Log_File=Relay_Master_Log_File
Exec_Master_Log_Pos=Until_Log_Pos
Slave_SQL_Running='No'
导出数据导入到主库

============================常见管理操作============================
============================查看主库状态
show master status;
Binlog_Do_DB:参数binlog-do-db指定mysql的binlog日志记录哪个db
Binlog_Ignore_DB:参数binlog-ignore-db指定忽略某个db的binlog

show slave status\G;

start slave io_thread;
start slave sql_thread;

结合起来看从库的IP和端口
show processlist;
show slave hosts;

从库显示指定从库的IP和端口后show slave hosts和显示IP和端口
report_host
report_port

查看实例当前拥有的binlog,反映的是mysql-bin.index索引文件
show binary logs;

删除binlog
purge binary logs to 'mysql-tb-bin.000005';
PURGE BINARY LOGS BEFORE '2014-04-28 23:59:59';

添加binlog,注册binlog
还原binlog,修改mysql-bin.index
flush binary logs;

查看binlog内容
show binlog events in 'xxxxx';

reset master;
reset master to xxx;指定binlog号

reset slave;
gtid模式可以,位点模式不行
============================跳过事务
位点模式
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

gtid模式
stop slave;
set session gtid_next=''; 设置为Executed_Gtid_Set+1,gtid号+1
begin;
commit;
set session gtid_next='automatic';
start slave;
============================操作不写如binlog
set session sql_log_bin=0;
也不会分配gtid
============================判断某个操作是否在从库上执行
1 通过对比主从的位点信息判断是否执行
2 给点位点信息,未执行到就会一直卡着
SELECT MASTER_POS_WAIT('master_log_file', master_log_pos ,timeout ,'channel');
3 gtid模式
select wait_for_executed_gtid_set(gtid_set,timeout);
============================mysql5.7在线设置复制过滤
要添加到参数文件永久生效
CHANGE REPLICATION FILTER filter[, filter][, ...]
filter:
REPLICATE_DO_DB = (db_list)
| REPLICATE_IGNORE_DB = (db_list)
| REPLICATE_DO_TABLE = (tbl_list)
| REPLICATE_IGNORE_TABLE = (tbl_list)
| REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
| REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
| REPLICATE_REWRITE_DB = (db_pair_list)
db_list:
db_name[, db_name][, ...]
tbl_list:
db_name.table_name[, db_table_name][, ...]
wild_tbl_list:
'db_pattern.table_pattern'[, 'db_pattern.table_pattern'][, ...]
db_pair_list:
(db_pair)[, (db_pair)][, ...]
db_pair:
from_db, to_db
查询复制过滤
replication_applier_filters 单独channel
replication_applier_global_filters 全局规则,所有channel

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);
CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = ('db1.table1');
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.imp%');
CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2);
CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = ('db1.table1');
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');
清除过滤规则
CHANGE REPLICATION FILTER REPLICATE_DO_DB=(),REPLICATE_IGNORE_DB=();

============================复制相关的监控
performance_schema.replication_applier_configuration
performance_schema.replication_applier_filters
performance_schema.replication_applier_global_filters
performance_schema.replication_applier_status
performance_schema.replication_applier_status_by_coordinator
performance_schema.replication_applier_status_by_worker
performance_schema.replication_connection_configuration
performance_schema.replication_connection_status
performance_schema.replication_group_member_stats
performance_schema.replication_group_members

============================主从延迟============================
===============如何分析主从延迟
1 从库服务器的负载情况
top
iostat -xm 1
2 主从复制状态
show master status;
show slave status\G
对比主位点以及备读取位点
对比备读取位点以及备执行位点
3 主库binlog写入量
===============主从延迟常见原因以及解决方法
1 IO线程存在延迟
网络延迟 --开启slave_compressed_protocol,启用binlog压缩
磁盘IO存在瓶颈 --调整从库双1设置或者关闭binlog
网卡问题
2 sql线程存在延迟
a 主库binlog写入量过大,SQL线程单线程重放
具体体现:
从库磁盘IO无明显瓶颈
relay_master_log_file和exec_master_log_pos不断变化
binlog生成速度快于5分钟一个,主库写入量过大
解决:升级到5.7以上,开启并行复制
b statement格式下的慢SQL
具体体现:
relay_master_log_file和exec_master_log_pos一段时间没变化
解决:设置log_slow_slave_statements,记录从库的慢SQL,优化SQL
c 表上无索引且binlog为row格式
具体体现:
relay_master_log_file和exec_master_log_pos一段时间没变化
表无索引操作时,主库表只会被扫描一次,而row格式下的从库,对于每条记录都会扫描一次
解决
从库临时创建一个索引
将参数slave_rows_search_algorithms设置为INDEX_SCAN,HASH_SCAN
d 大事务
拆分成小事务
e 从库上有查询
消耗系统资源,有锁等待
查询操作阻塞主库的DDL
f 从库上有备份
全局读锁阻塞SQL线程
g 磁盘IO存在瓶颈
===============如何理解seconds_behind_master
根据计算逻辑
1 seconds_behind_master只计算SQL线程的延迟,不计算IO线程的延迟
网络原因,磁盘瓶颈,slave_net_timeout设置过大,导致的binlog未及时发送
2 binlog为statement和row计算逻辑不一样
3 级联复制无法真正反映延迟

主从延迟的监控
8.0之前使用pt-hearteat
8.0使用如下SQL
select case when min_commit_timestamp is null then 0
else unix_timestamp(now(6))-unix_timestamp(min_commit_timestamp) end as seconds_behind_master
from (select min(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) as min_commit_timestamp
from performance_schema.replication_applier_status_by_worker
where applying_transaction<>'') t;

============================复制常见的问题============================
============================IO线程连接不上主库
1 防火墙 itables -L
2 主机名或端口设置错误 netstat -ntlup|grep 3306
3 复制用户的用户名密码设置错误实际登录测试
select user_name,user_password from mysql.slave_master_info;
============================server_id重复
在线修改server_id,修改参数文件
============================包大小超过slave_max_allowed_packet
如果有blob,text等大字段,包的大小可能超过1G
1 开启了binlog_rows_query_log_events,可拿到SQL手工在从库执行,执行之前需要设置gtid_next
2 跳过事务,后续使用pt_table_sync修复
3 重建复制
============================找不到需要的binlog
1 基于位点的复制,先还原binlog,修改mysql-bin.index,flush binary logs,重启复制
2 对于gtid,先切换为位点复制,使用方法1,然后在切换为gtid复制
change master to master_log_file='',master_log_pos=xxx,master_auto_position=0;
恢复后
stop slave;change master to master_auto_position=1; start slave;
============================从库的gtid多于主库
1 server_uuid属于从库,事务是在从库产生的可以设置super_read_only和read_only
对应的binlog存在,可根据具体操作判断
使用pt_table_checksum检查数据,使用pt_table_sync修复
重建复制
最后在关闭复制的情况下设置gtid_purge
2 server_uuid属于主库,binlog还未落盘,就备发送到从库,主库down机,并且未设置双1
重建复制
============================在插入操作中,提示唯一键冲突
1 最安全是重建复制
2 设置sql_log_bin=off,删除这条记录
3 不能单纯的跳过这个事务
============================删除或更新,提示记录不存在
1 最安全是重建复制
2 根据主键找到对应的记录,插入从库,重启复制
3 不能直接跳过,肯呢个一个gtid包含多个操作
============================主从数据不一致常见原因
1 从库被误写入,建议设置设置super_read_only和read_only
2 二进制日志合适为statement
3 从库未设置relay_log_recovery=ON和relay_log_info_repository=TABLE
4 主库或者从库未设置双一
============================online ddl============================
1 mysql5.6之前所有的ddl都会阻塞dml,仅允许查询
2 引入online ddl很多ddl操作还是会阻塞dml

alter table t1 add primary key(c1),algorithm=inplace,lock=none;

online ddl分类

1 仅修改表的元数据信息

特点:不会阻塞dml

时间短,如果没有查询和事务,瞬间完成

只更新表结构文件

mysql8.0引入instant之前,只修改元数据信息使用inplace+norebuild方式,8.0使用instant,算法类别变化,本质没变

包括:

删除二级索引 drop index ind_name on t1; alter table t1 drop index ind_name

修改索引名(5.7以上支持) alter table t1 rename index oldindex to newindex;5.6只能重建索引

修改字段名 alter table t1 change c1 c2 datatype; 字段类型不变

设置(删除)字段默认值 alter table t1 alter column c1 set default literal;alter table t1 alter column c1 drop default;

增加varchar长度 alter table t1 change column c1 c1 varchar(200);

行上有1-2字节标识字段的长度,字节长度0-255用1个字节表示,大于255用2个字节表示,如果标识字节不变,则只修改元数据,如果改变,则是copy,因为utf8中.1个字符占3个字节,85*3=255,所以从85修改到86,则使用copy,其他字符集和utf8不同

create table t2(c1 varchar(6)) charset=utf8;
insert into t2 values('fffff');
alter table t2 modify c1 varchar(85),algorithm=inplace,lock=none;
alter table t2 modify c1 varchar(86),algorithm=inplace,lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

5.6版本不论增加多少长度,都是copy,5,7引入上述,减少长度只能用copy

修改自增主键的值 alter table t1 AUTO_INCREMENT=next_value 实际修改内存值

修改表的统计信息选项 alter table t1 stats_persistent=1,stats_auto_recalc=1,stats_sample_pages=25;

重命名表 alter table oldname rename to newname; rename table oldname rename to newname;

添加外键约束alter table t1 add constraint fkname(c1) references t2(c2) referential_actions;

如果foreign_key_checks为ON,使用copy算法

如果foreign_key_checks为OFF,只更新元数据信息

删除外键约束

alter table t1 drop foreign key fkname;

2 inplace的rebuild算法

特点:

不会阻塞dml

执行时间和表的大小成正比

执行过程,拷贝原表数据,生成临时idb文件,比原表略大一点,可以根据这点监控进度

包括以下操作

创建索引,实际使用no rebuild方式,但是时间和表大小成正比 create index inxname on t1(col_list)

alter table t1 add index name(col_list)

添加主键 alter table t1 add primary key(id);

删除主键并创建新主键 alter table t1 drop primary key ,add primary key(id1)

新增字段 alter table t1 add c1column_defination [first|after cname];

如果新增字段是自增列,会阻塞dml

从mysql8.0.12 ,新增字段是instant算法

删除字段 alter table t1 drop column c1; 8.0.29开始.使用instant算法

调整字段顺序 alter table t1 modify c1 column_defination [first|after cname];

修改表的属性(ROW_FORMAT和KEY_BLOCK_SIZE) alter table t1 ROW_FORMAT=COMPRESSED,KEY_BLOCK_SIZE=8;

修改字段的null属性 alter table t1 modify c1 varchar(100) null;alter table t1 modify id varchar(100) not null; 修改为not null,sql_mode必须为strict_all_tables或者strict_trans_tables,否则还是copy

OPTIMIZE TABLE:innodb表对应的是alter table ... force.会重建表,回收空闲空间,更新统计信息.碎片多可以使用.如果表有fulltext索引,会使用copy算法

OPTIMIZE TABLE t1;

alter table t1 force

alter table t1 engine=innodb;

3 copy方式

修改字段的定义,如int变big int

alte rtable t1 modify col_name column_definition [first|alter col_name];

alter table t1 change c1 c1 bigint;

删除主键

alter table t1 drop primary key;

修改字符集

alter table t11 convert to character set charset_name [collate collation_name];

============================监控ddl的进度============================
mysql5.6
通过查看临时生成的表文件大小与原表大小比较来估算
mysql5.7
1 开启ddl采集配置项
update performance_schema.setup_instruments set enabled='yes' where name like 'stage/innodb/alter%';
select * from performance_schema.setup_instruments where name like 'stage/innodb/alter%';
2 开启时间状态表
update performance_schema.setup_consumers set enabled='yes' where name like '%stage%';
select * from performance_schema.setup_consumers where name like '%stage%';
3 执行ddl
4 查看状态
select EVENT_NAME,WORK_COMPLETED,WORK_ESTIMATED from performance_schema.events_stages_current;
细分的7个时间针对inplace的ddl
对于copy算法,只有一个时间copy to tmp table

=========================mysql8.0.12引入的秒级加列特性==========================
alter table test.t1 add column c5 varchar(100),algorithm=instant;
判断表中是否存在使用instant算法加入的列
select * from information_schema.innodb_tables where name='test/t1';

select * from information_schema.innodb_tables where name='test/t1';
INSTANT_COLS列
select * from information_schema.innodb_columns where table_id=1088;
HAS_DEFAULT列为1代表使用instant算法加的列

秒级别加列的局限
列只能加到最后,从8.0.29开始,可以加到任何位置
不支持全文索引的表
不支持存储在数据字典表空间的表
不支持临时表

============================online ddl的优缺点
优先使用online ddl
优点:
原生版本支持.无需额外工具
缺点:
不会阻塞dml,但是在prepary阶段和commit阶段都要获取mdl锁,如果正好有未提交的事务或者未结束的查询,DDL就会被阻塞,后续该表的其他操作也会被阻塞
"全量+增量"的方式,并不保证ddl一定成功
大表DDL失败,回滚代价高
大表DDL时,会有主从延迟
不能根据负载调整ddl进度

============================online ddl的注意事项
1 online ddl开始和结束之前,都不能有未提交的事务或者未结束的查询
2 在DDL执行过程中,增量dml保存在row_log,在应用到临时表,增量节点有可能出问题
3 元数据锁的超时时间lock_wait_timeout决定,默认365天
4 inplace和copy算法,临时表和元表在同一个目录
5 一个表有多个ddl,放在一个语句里面执行
6 影响的行数为0是inplace算法,否则为copy算法,可以建表插入少量数据验证
7 show processlist,inplace显示altering table,copy显示copy to tmp table
8 想使用copy,可以设置old_alter_table=on,或者设置algorithm=copy;

=========================pt-online-schema-change=========================
yum install perl-DBD-MySQL
pt-online-schema-change h=10.10.10.10,P=3306,u=user,p=xxxxxx,D=test,t=t1 --alter="add column c999 datetime" --charset utf8 --max-load=Threads_running=50 --critical-load=Threads_running=100 --set-vars lock_wait_timeout=5 --tries swap_tables:30:60 --execute

通过创建临时中专表和触发器实现
--alter
--execute
DSN
--check-slave-lag
--skip-check-slave-lag
--recurse
--recursion-method
--chunk-size和--chunk-time
--max-load和--critical-load
--sleep
--alter-foreign-keys-method
--[no]check-replication-filters
--default-engine
--remove-data-dir
--data-dir
--dry-run
优点:
使用广泛,开源
缺点:
触发器侵入数据库
触发器不会停止,除非删除或禁用

注意事项
创建触发器,rename表都会获取dml锁
update触发器有delete和replace两个操作,3.0.2之前只有一个,对主键或唯一键更新有bug
目标表必须有主键或唯一键
不能添加自增主键

执行失败要先删除触发器,在删除临时表

============================元数据锁============================
如果DDL因获取不到元数据锁而阻塞,后续针对该表的所有操作都会被阻塞,很快会CPU飙升
等待事件为 Waiting for table metadata lock
select也会被阻塞
select加MDL_SHARED_READ
dml加MDL_SHARED_WRITE
ddl加DML_EXCLUSIVE
============================mysql5.7和8.0定位元数据锁

定位DDL被阻塞
select * from sys.schema_table_lock_waits where blocking_lock_type<>'SHARED_UPGRADABLE';

mysql5.7首先要开启元数据锁采集
临时生效
update performance_schema.setup_instruments set enabled='YES',timed='YES' where name='wait/lock/metadata/sql/mdl';
配置文件修改
[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

============================mysql5.6定位元数据锁

定位DDL被阻塞
两个原因
1 慢查询 --通过show processlist
2 事务未提交
SELECT concat('kill ', i.trx_mysql_thread_id,';')
FROM information_schema.innodb_trx i,(
SELECT MAX(time) AS max_time
FROM information_schema.processlist
WHERE state = 'Waiting for table metadata lock'
AND (info LIKE 'alter%'
   OR info LIKE 'create%'
   OR info LIKE 'drop%'
   OR info LIKE 'truncate%'
   OR info LIKE 'rename%'
)) p
WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;