1. skip-slave-start
有时mysql启动的时候，不希望自动开启同步，则可以在my.cnf添加skip-slave-start
2. change replication filter
mysql 5.7.3版本之后开始支持在线更改同步过滤配置，不用重启mysql。 引用官网的release公告：
Replication: Replication filtering rules can now be set dynamically on the slave using the SQL statement CHANGE REPLICATION FILTER introduced in this release.
复制：现在可以使用此版本中引入的SQL语句CHANGE REPLICATION FILTER在从属服务器上动态设置复制过滤规则。
注意:如果原来字段已经有过滤配置，此时要新增，需要把之前的过滤加上，否则会被覆盖！！
例子：在已有同步db1.tb1的基础上，添加同步过滤db1.tb2表
- mysql> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.0.101
- Master_User: slave
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000001
- Read_Master_Log_Pos: 4
- Relay_Log_File: relaylog.000001
- Relay_Log_Pos: 2068
- Relay_Master_Log_File: mysql-bin.000001
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table: db1.tb1
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- mysql> STOP SLAVE sql_thread; #需要先停止sql_thread;
- mysql> CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (db1.tb1,db1.tb2);
- mysql> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.0.101
- Master_User: slave
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000001
- Read_Master_Log_Pos: 4
- Relay_Log_File: relaylog.000001
- Relay_Log_Pos: 2068
- Relay_Master_Log_File: mysql-bin.000001
- Slave_IO_Running: Yes
- Slave_SQL_Running: No
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table: db1.tb1,db1.tb2
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- mysql> start slave sql_thread;
yaml另，使用wild过滤，需要加单引号，如：
- CHANGE REPLICATION FILTER
- REPLICATE_WILD_DO_TABLE = ('db1.t1%','db2.t1%'),
- REPLICATE_WILD_IGNORE_TABLE = ('db1.t2%','db2.t2%');
3. mysql5.7配置pid-file
配置my.cnf的时候一直以为中横线跟下划线一样，所以配置pid_file=mysqld.pid一直无效，查启动脚本mysql.service才发现要用pid-file
4. interactive_timeout和wait_timeout
wait_timeout：非交互式连接超时时间（例如jdbc连接）
interactive_timeout：交互式连接超时时间（mysql客户端连接）
引申出一个mysql的error问题：
- [Note] Aborted connection 25442 to db: 'test' user: 'root' host: 'localhost' (Got timeout reading communication packets)
当连接空闲时间超过上面的timeout就会被mysql服务端断开。可修改interactive_timeout=10，然后用客户端连接mysql，10s后日志就会出现上面的提醒，再次执行的时候显示“MySQL server has gone away”
Got timeout reading communication packets很可能mysql服务端主动断开（调大wait_timeout）Got an error reading communication packets很可能是客户端主动断开
5. 异构同步
库名不一样的主从同步，在从库配置replicate-rewrite-db = old_db -> new_db
6.临时表
mysql临时表分为磁盘临时表和内存临时表。
磁盘临时表使用引擎：Innodb、MyISAM、Temptable的文件map表（8.0）
内存临时表使用引擎：Memory、Temptable（8.0）
5.6及以前版本：磁盘临时表(.frm/.ibd)存放在临时目录（variables.tmpdir），undo log存放在共享表空间，无redo log；会话断开，临时文件自动释放；
5.7版本：磁盘临时(.frm)存放在临时目录，但数据文件和undo log存放在ibtmp1单独表空间中，只有重启数据库才会回收ibtmp1中的空间；
8.0以后：磁盘临时表的数据单独放在Session临时表空间池(#innodb_temp目录下的ibt文件)里面，临时表的undo放在global的表空间ibtmp1里面。
磁盘临时表文件形态
#sql固定开头，b12 mysql进程id的16进制，15ea6线程id的16进制，0 自增id，可用于分析哪个线程使用了临时表。
MySQL · 引擎特性 · 临时表那些事儿
注意到里面有句话
在5.6中，隐式临时表依然在tmpdir下，在复杂SQL执行的过程中，就能看到这临时表，一旦执行结束，就被删除。值得注意的是，5.6中，这种隐式创建的临时表，只能用MyISAM引擎，即没有internal_tmp_disk_storage_engine这个参数可以控制。所以，当我们的系统中只有innodb表时，也会看到MyISAM的某些指标在变动
7.“双1”配置
2021-04-15
经常看到有些数据库培训机构在说双1，一开始没明白这是什么简称。（感觉也没必要说得这么神秘）
“双1”指mysql配置中sync_binlog和innodb_flush_log_at_trx_commit设置为1，以此保证数据库的一致性
sync_binlog：控制binlog刷新到磁盘的数量。
- 0 性能最高，取消实时刷新到磁盘，依靠操作系统，mysql crash可能丢数据；
- 1 最安全，实时刷新到磁盘，影响写入性能；
- N 收集N个提交就刷新，也是可能丢数据和影响写入性能；
innodb_flush_log_at_trx_commit：控制innodb刷新模式
- 0 每秒一次写入和刷新到磁盘，mysql崩溃会丢数据
- 1 事务提交时写入磁盘，数据不会丢失
- 2 事务提交后写入磁盘，linux崩溃后才会丢数据
安全模式：sync_binlog 1 ，innodb_flush_log_at_trx_commit 1
高性能：sync_binlog 0 ， innodb_flush_log_at_trx_commit 2
8.mysql和mysqld区别
mysql ：客户端连接程序
mysqld：服务端启动程序
在修改/usr/lib/systemd/system/mysqld.service里的启动路径ExecStart时，需要指定的就是mysqld的路径
9.元数据锁MDL
DML都会对表加上MDL读锁，DDL则对表加MDL写锁，当出现锁等待时，进程显示Waiting for table metadata lock。
即使是对表进行新增字段时，也可能导致数据库崩溃。
复现：
- // session 1
- begin;
- select * from t_test;
- // 一直不提交
- // session 2
- // 此时新增字段
- alter table t_test add column val varchar(10); // blocked状态
- // session 3
- // 此时其它DML操作都被阻塞，处于blocked
- select * from t_test;
sql10. 无符号类型下的Error
20210917
最近在审个表结构，有个字段是剩余报名人数，本身是想避免出现超卖现象，建议开发把类型改为无符号int unsigned，没想到上线RC后出现错误： Data truncation: BIGINT UNSIGNED value is out of range in '(test.t_course.plan_limit - 4)'
SQL大概如下：
- update t_course set xxx='yyy' where id=10 and plan_limit - 4 > 0
mysql 当两个字段相减时，如果其中一个或两个字段类型是unsigned无符号类型，如果相减的值小于0则会报错（BIGINT UNSIGNED value is out of range），需要调整SQL顺序，如上面改成：
- update t_course set xxx='yyy' where id=10 and plan_limit > 4
11. strace问题追踪
链路追踪排查：
- strace -o /tmp/test.log -f -tt mysql --login-path=local -e "show databases"
12. timestamp default null
alter table xxx add column ts timestamp default null可能提示Invalid default value for 'ts'
原因在于参数配置：explicit_defaults_for_timestamp = off
解决办法就是将其改为on
字面上意思：是否明确timestamp的默认值
在mysql5.6.6之前，timestamp的默认行为，如果没有明确声明null，默认为not null（其它类型相反，没有显示声明not null,默认null）
13. max_connections
最大连接数，计算公式={MIN(DBInitMemory/4+500,100000)}（参考腾讯云CDB，DBInitMemory单位M）。
例如：MySQL实例是4核8000M
- max_connections=MIN(8000/4+500,100000)=2500
14. 快速安全关闭mysql
- # 设置最大脏页比例为0,
- set global innodb_max_dirty_pages_pct=0;
- # 查询当前脏页数
- select count(1) from information_schema.innodb_buffer_page_lru where oldest_modification>0;
- #等到上面脏页数为0时，表示脏页都刷新到磁盘，可以重启mysql了
csharp15. mysqldump参数建议
目前表的字符集规范都为utf8mb4，但导出的时候没有指定的话，可能出现emoji表情导出失效，建议参数如下：
- mysqldump --login-path=dev --single-transaction --set-gtid-purged=off --default-character-set=utf8mb4
16. binlog2sql
建议直接使用michael-liumh版本的binlog2sql，有对json和emoji进行修复。michael-liumh_binlog2sql
17. SSL
mysql版本关闭SSL,my.cnf配置ssl=0，或者jdbc连接串增加useSSL=false
Comments