MySQL 8.0 Study 003 Tips

WAL指的是对数据文件进行修改前，必须将修改先记录日志。MySQL为了保证ACID中的一致性和持久性，使用了WAL。

Redo log就是一种WAL的应用。当数据库忽然掉电，再重新启动时，MySQL可以通过Redo log还原数据。也就是说，每次事务提交时，不用同步刷新磁盘数据文件，只需要同步刷新Redo log就足够了。相比写数据文件时的随机IO，写Redo log时的顺序IO能够提高事务提交速度。

Redo log的刷盘操作将会是最终影响MySQL TPS的瓶颈所在。为了缓解这一问题，MySQL使用了组提交，将多个刷盘操作合并成一个，如果说10个事务依次排队刷盘的时间成本是10，那么将这10个事务一次性一起刷盘的时间成本则近似于1。

为了保证Redo log和binlog的数据一致性，MySQL使用了二阶段提交，由binlog作为事务的协调者。而 引入二阶段提交 使得binlog又成为了性能瓶颈，先前的Redo log 组提交 也成了摆设。为了再次缓解这一问题，MySQL增加了binlog的组提交，目的同样是将binlog的多个刷盘操作合并成一个，结合Redo log本身已经实现的 组提交，分为三个阶段(Flush 阶段、Sync 阶段、Commit 阶段)完成binlog 组提交，最大化每次刷盘的收益，弱化磁盘瓶颈，提高性能。

innodb_log_buffer_size 是 redo log 的写缓存，如果 buffer 不够大，就会发生多次 IO write，将缓存中的数据刷到磁盘；
innodb_log_buffer_size 越大，IO 次数越少。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

试想一下，如果你现在有一个表 A，需要做空间收缩，为了把表中存在的空洞去掉，你可以怎么做呢？你可以新建一个与表 A 结构相同的表 B，然后按照主键ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。

alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

从5.6开始引入了online DDL，也就是说alter table A engine=InnoDB的时候，允许对表 A 做增删改操作。但是DDL会有短暂的MDL写锁，这个会很快转换为MDL读锁，因此，认为对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。

gtid_mode=on (必选)                    #开启gtid功能
enforce-gtid-consistency=1 (必选)      #也可以将1写为on
log_bin=log-bin=mysql-bin (必选)       #开启binlog二进制日志功能
log-slave-updates=1 (必选)             #也可以将1写为on

gtid_mode=on    (必选)
enforce-gtid-consistency=1  （必选）
log_bin=mysql-bin           （可选）    #高可用切换，最好开启该功能
log-slave-updates=1     （可选）       #高可用切换，最好打开该功能

-  不支持非事务引擎;
-  不支持create table ... select 语句复制(主库直接报错);(原理: 会生成两个sql, 一个是DDL创建表SQL, 一个是insert into 插入数据的sql; 由于DDL会导致自动提交, 所以这个sql至少需要两个GTID, 但是GTID模式下, 只能给这个sql生成一个GTID)
-  不允许一个SQL同时更新一个事务引擎表和非事务引擎表;
-  在一个复制组中，必须要求统一开启GTID或者是关闭GTID;
-  开启GTID需要重启 (mysql5.7除外);
-  开启GTID后，就不再使用原来的传统复制方式;
-  对于create temporary table 和 drop temporary table语句不支持;
-  不支持sql_slave_skip_counter;

1、show slave status里的信息里可以找到在执行Master里的POS:151
2、通过mysqlbinlog找到了GTID：
3、stop slave;
4、set session gtid_next='4e659069-3cd8-11e5-9a49-001c4270714e:1'
5、begin; commit；
6、SET SESSION GTID_NEXT = AUTOMATIC;   #把gtid_next设置回来
7、start slave;  #开启复制

1）对于跳过一个错误，找到无法执行事务的编号，比如是2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10
mysql> stop slave;
mysql> set gtid_next='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10';
mysql> begin;
mysql> commit;
mysql> set gtid_next='AUTOMATIC';
mysql> start slave;
 
2）上面方法只能跳过一个事务，那么对于一批如何跳过？
在主库执行"show master status"，看主库执行到了哪里，比如：2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33，那么操作如下：
mysql> stop slave;
mysql> reset master;
mysql> set global gtid_purged='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33';
mysql> start slave;

先介绍几个重要GTID_MODE的参数:
GTID_MODE = OFF
不产生Normal_GTID,只接受来自master的ANONYMOUS_GTID
 
GTID_MODE = OFF_PERMISSIVE
不产生Normal_GTID,可以接受来自master的ANONYMOUS_GTID & Normal_GTID
 
GTID_MODE = ON_PERMISSIVE
产生Normal_GTID,可以接受来自master的ANONYMOUS_GTID & Normal_GTID
 
GTID_MODE = ON
产生Normal_GTID,只接受来自master的Normal_GTID
 
归纳总结:
1)当master产生Normal_GTID的时候,如果slave的gtid_mode(OFF)不能接受Normal_GTID,那么就会报错
2)当master产生ANONYMOUS_GTID的时候,如果slave的gtid_mode(ON)不能接受ANONYMOUS_GTID,那么就会报错
3)设置auto_position的条件: 当master的gtid_mode=ON时,slave可以为OFF_PERMISSIVE,ON_PERMISSIVE,ON。
   除此之外,都不能设置auto_position = on
 
============================================
下面开始说下如何online 升级为GTID模式？
 
step 1: 每台server执行
检查错误日志，直到没有错误出现，才能进行下一步
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
 
step 2: 每台server执行
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
 
step 3: 每台server执行
不用关心一组复制集群的server的执行顺序，只需要保证每个Server都执行了，才能进行下一步
mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
 
step 4: 每台server执行
不用关心一组复制集群的server的执行顺序，只需要保证每个Server都执行了，才能进行下一步
mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
 
step 5: 在每台server上执行，如果ONGOING_ANONYMOUS_TRANSACTION_COUNT=0就可以
不需要一直为0，只要出现过0一次，就ok
mysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
 
step 6： 确保所有anonymous事务传递到slave上了
#master上执行
mysql> SHOW MASTER STATUS;
 
#每个slave上执行
mysql> SELECT MASTER_POS_WAIT(file, position);
 
或者，等一段时间，只要不是大的延迟，一般都没问题
 
step 7: 每台Server上执行
mysql> SET @@GLOBAL.GTID_MODE = ON;
 
step 8: 在每台server上将my.cnf中添加好gtid配置
gtid_mode=on
enforce-gtid-consistency=1
log_bin=mysql-bin
log-slave-updates=1
 
step 9: 在从机上通过change master语句进行复制
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;

limit offset, rows
limit rows

secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

show global variables like '%secure%';

修改my.ini 在[mysqld]内加入
secure_file_priv=

修改my.cnf 在[mysqld]内加入
secure_file_priv=
MYSQL新特性secure_file_priv对读写文件的影响
然后重启mysql，再查询secure_file_priv

mysql [localhost:8032] {msandbox} (test) > select concat('insert into t1(id,c1,c2) values(',id,',','\'',c1,'\'',',','\'',c2,'\'',')') into outfile '/tmp/t1.txt' from t1;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql [localhost:8032] {msandbox} (test) > show variables like 'secure_file%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > 

mysql [localhost:8032] {msandbox} (test) > show variables like 'secure%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > select concat('insert into t1(id,c1,c2) values(',id,',','\'',c1,'\'',',','\'',c2,'\'',')') into outfile '/tmp/t1.txt' from t1;
Query OK, 2 rows affected (0.00 sec)

mysql [localhost:8032] {msandbox} (test) >