- MySQL 主键与唯一索引的一些问题
- MySQL validate_password插件
- MySQL Connection Control Plugins
- MySQL 保留字
- MySQL slave 状态说明
- mysqldump导出 timestamp 和 datetime 列的一些tips
- MySQL从5.6到8.0并行复制的演进
- Referece
MySQL 8.0 Study 011 Tips
学习系列
- MySQL 主键与唯一索引的一些问题
- MySQL validate_password 插件
- MySQL Connection Control 插件
- MySQL 保留字
- MySQL slave 状态说明
- MySQL mysqldump导出 timestamp 和 datetime 列的一些tips
- MySQL从5.6到8.0并行复制的演进
MySQL 主键与唯一索引的一些问题
唯一索引可能产生冗余数据
参考如下
唯一索引示例:
mysql [localhost:8032] {msandbox} (test) > create table t1(id int, col1 varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql [localhost:8032] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int DEFAULT NULL,
`col1` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) > alter table t1 add unique key(id,col1);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8032] {msandbox} (test) > insert into t1 values (1,'aaa');
Query OK, 1 row affected (0.03 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t1 values (1,'aaa');
ERROR 1062 (23000): Duplicate entry '1-aaa' for key 't1.id'
mysql [localhost:8032] {msandbox} (test) > insert into t1 values (1,'bbb');
Query OK, 1 row affected (0.00 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t1 values (1,null);
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t1 values (1,null);
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t1 values (1,null);
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t1 values (1,null);
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8032] {msandbox} (test) > select * from t1;
+------+------+
| id | col1 |
+------+------+
| 1 | NULL |
| 1 | NULL |
| 1 | NULL |
| 1 | NULL |
| 1 | aaa |
| 1 | bbb |
+------+------+
6 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) >
主键示例:
mysql [localhost:8032] {msandbox} (test) > create table t2(id int,col1 varchar(20),primary key idx_t2_pk(id,col1));
Query OK, 0 rows affected (0.04 sec)
mysql [localhost:8032] {msandbox} (test) > show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int NOT NULL,
`col1` varchar(20) NOT NULL,
PRIMARY KEY (`id`,`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t2 values(1,'aaa');
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t2 values(1,'aaa');
ERROR 1062 (23000): Duplicate entry '1-aaa' for key 't2.PRIMARY'
mysql [localhost:8032] {msandbox} (test) > insert into t2 values(1,'bbb');
Query OK, 1 row affected (0.02 sec)
mysql [localhost:8032] {msandbox} (test) > insert into t2 values(1,null);
ERROR 1048 (23000): Column 'col1' cannot be null
mysql [localhost:8032] {msandbox} (test) >
null的一些思考
- 1.null与空串是不同处理方式，虽然表示为没有字符串，这两个是不同对象
- 2.count 的差异，如下示例
- 3.组合索引中null的处理方式不同
- 4.创表时候能用not null约束尽量使用，能用主键约束，就不要使用唯一索引约束
mysql [localhost:8032] {msandbox} (test) > select * from t1;
+------+------+
| id | col1 |
+------+------+
| 1 | NULL |
| 1 | NULL |
| 1 | NULL |
| 1 | NULL |
| 1 | aaa |
| 1 | bbb |
+------+------+
6 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) > select count(id) from t1;
+-----------+
| count(id) |
+-----------+
| 6 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) > select count(col1) from t1;
+-------------+
| count(col1) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) >
MySQL validate_password插件
在MySQL 8.0之前，MySQL使用的是validate_password插件（plugin）检测、验证账号密码强度，保障账号的安全性，而到了MySQL 8.0，引入了服务器组件（Components）这个特性，validate_password插件已用服务器组件重新实现
运行时命令安装MySQL密码强度审计插件:validate_password
mysql [localhost:8032] {msandbox} ((none)) > SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION FROM INFORMATION_SCHEMA.PLUGINS ORDER BY 1;
+----------------------------------+----------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_LIBRARY | PLUGIN_STATUS | LOAD_OPTION |
+----------------------------------+----------------+---------------+-------------+
| ARCHIVE | NULL | ACTIVE | ON |
| binlog | NULL | ACTIVE | FORCE |
| BLACKHOLE | NULL | ACTIVE | ON |
| caching_sha2_password | NULL | ACTIVE | FORCE |
| CSV | NULL | ACTIVE | FORCE |
| daemon_keyring_proxy_plugin | NULL | ACTIVE | FORCE |
| FEDERATED | NULL | DISABLED | OFF |
| InnoDB | NULL | ACTIVE | FORCE |
| INNODB_BUFFER_PAGE | NULL | ACTIVE | FORCE |
| INNODB_BUFFER_PAGE_LRU | NULL | ACTIVE | FORCE |
| INNODB_BUFFER_POOL_STATS | NULL | ACTIVE | FORCE |
| INNODB_CACHED_INDEXES | NULL | ACTIVE | FORCE |
| INNODB_CMP | NULL | ACTIVE | FORCE |
| INNODB_CMPMEM | NULL | ACTIVE | FORCE |
| INNODB_CMPMEM_RESET | NULL | ACTIVE | FORCE |
| INNODB_CMP_PER_INDEX | NULL | ACTIVE | FORCE |
| INNODB_CMP_PER_INDEX_RESET | NULL | ACTIVE | FORCE |
| INNODB_CMP_RESET | NULL | ACTIVE | FORCE |
| INNODB_COLUMNS | NULL | ACTIVE | FORCE |
| INNODB_FT_BEING_DELETED | NULL | ACTIVE | FORCE |
| INNODB_FT_CONFIG | NULL | ACTIVE | FORCE |
| INNODB_FT_DEFAULT_STOPWORD | NULL | ACTIVE | FORCE |
| INNODB_FT_DELETED | NULL | ACTIVE | FORCE |
| INNODB_FT_INDEX_CACHE | NULL | ACTIVE | FORCE |
| INNODB_FT_INDEX_TABLE | NULL | ACTIVE | FORCE |
| INNODB_INDEXES | NULL | ACTIVE | FORCE |
| INNODB_METRICS | NULL | ACTIVE | FORCE |
| INNODB_SESSION_TEMP_TABLESPACES | NULL | ACTIVE | FORCE |
| INNODB_TABLES | NULL | ACTIVE | FORCE |
| INNODB_TABLESPACES | NULL | ACTIVE | FORCE |
| INNODB_TABLESTATS | NULL | ACTIVE | FORCE |
| INNODB_TEMP_TABLE_INFO | NULL | ACTIVE | FORCE |
| INNODB_TRX | NULL | ACTIVE | FORCE |
| INNODB_VIRTUAL | NULL | ACTIVE | FORCE |
| MEMORY | NULL | ACTIVE | FORCE |
| MRG_MYISAM | NULL | ACTIVE | FORCE |
| MyISAM | NULL | ACTIVE | FORCE |
| mysqlx | NULL | ACTIVE | ON |
| mysqlx_cache_cleaner | NULL | ACTIVE | ON |
| mysql_native_password | NULL | ACTIVE | FORCE |
| ndbcluster | NULL | DISABLED | OFF |
| ndbinfo | NULL | DISABLED | OFF |
| ndb_transid_mysql_connection_map | NULL | DISABLED | OFF |
| ngram | NULL | ACTIVE | ON |
| PERFORMANCE_SCHEMA | NULL | ACTIVE | FORCE |
| sha256_password | NULL | ACTIVE | FORCE |
| sha2_cache_cleaner | NULL | ACTIVE | FORCE |
| TempTable | NULL | ACTIVE | FORCE |
+----------------------------------+----------------+---------------+-------------+
48 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
mysql [localhost:8032] {msandbox} ((none)) > INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.16 sec)
mysql [localhost:8032] {msandbox} ((none)) > SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION FROM INFORMATION_SCHEMA.PLUGINS ORDER BY 1;
+----------------------------------+----------------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_LIBRARY | PLUGIN_STATUS | LOAD_OPTION |
+----------------------------------+----------------------+---------------+-------------+
| ARCHIVE | NULL | ACTIVE | ON |
| binlog | NULL | ACTIVE | FORCE |
| BLACKHOLE | NULL | ACTIVE | ON |
| caching_sha2_password | NULL | ACTIVE | FORCE |
| CSV | NULL | ACTIVE | FORCE |
| daemon_keyring_proxy_plugin | NULL | ACTIVE | FORCE |
| FEDERATED | NULL | DISABLED | OFF |
| InnoDB | NULL | ACTIVE | FORCE |
| INNODB_BUFFER_PAGE | NULL | ACTIVE | FORCE |
| INNODB_BUFFER_PAGE_LRU | NULL | ACTIVE | FORCE |
| INNODB_BUFFER_POOL_STATS | NULL | ACTIVE | FORCE |
| INNODB_CACHED_INDEXES | NULL | ACTIVE | FORCE |
| INNODB_CMP | NULL | ACTIVE | FORCE |
| INNODB_CMPMEM | NULL | ACTIVE | FORCE |
| INNODB_CMPMEM_RESET | NULL | ACTIVE | FORCE |
| INNODB_CMP_PER_INDEX | NULL | ACTIVE | FORCE |
| INNODB_CMP_PER_INDEX_RESET | NULL | ACTIVE | FORCE |
| INNODB_CMP_RESET | NULL | ACTIVE | FORCE |
| INNODB_COLUMNS | NULL | ACTIVE | FORCE |
| INNODB_FT_BEING_DELETED | NULL | ACTIVE | FORCE |
| INNODB_FT_CONFIG | NULL | ACTIVE | FORCE |
| INNODB_FT_DEFAULT_STOPWORD | NULL | ACTIVE | FORCE |
| INNODB_FT_DELETED | NULL | ACTIVE | FORCE |
| INNODB_FT_INDEX_CACHE | NULL | ACTIVE | FORCE |
| INNODB_FT_INDEX_TABLE | NULL | ACTIVE | FORCE |
| INNODB_INDEXES | NULL | ACTIVE | FORCE |
| INNODB_METRICS | NULL | ACTIVE | FORCE |
| INNODB_SESSION_TEMP_TABLESPACES | NULL | ACTIVE | FORCE |
| INNODB_TABLES | NULL | ACTIVE | FORCE |
| INNODB_TABLESPACES | NULL | ACTIVE | FORCE |
| INNODB_TABLESTATS | NULL | ACTIVE | FORCE |
| INNODB_TEMP_TABLE_INFO | NULL | ACTIVE | FORCE |
| INNODB_TRX | NULL | ACTIVE | FORCE |
| INNODB_VIRTUAL | NULL | ACTIVE | FORCE |
| MEMORY | NULL | ACTIVE | FORCE |
| MRG_MYISAM | NULL | ACTIVE | FORCE |
| MyISAM | NULL | ACTIVE | FORCE |
| mysqlx | NULL | ACTIVE | ON |
| mysqlx_cache_cleaner | NULL | ACTIVE | ON |
| mysql_native_password | NULL | ACTIVE | FORCE |
| ndbcluster | NULL | DISABLED | OFF |
| ndbinfo | NULL | DISABLED | OFF |
| ndb_transid_mysql_connection_map | NULL | DISABLED | OFF |
| ngram | NULL | ACTIVE | ON |
| PERFORMANCE_SCHEMA | NULL | ACTIVE | FORCE |
| sha256_password | NULL | ACTIVE | FORCE |
| sha2_cache_cleaner | NULL | ACTIVE | FORCE |
| TempTable | NULL | ACTIVE | FORCE |
| validate_password | validate_password.so | ACTIVE | ON |
+----------------------------------+----------------------+---------------+-------------+
49 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
mysql [localhost:8032] {msandbox} ((none)) > SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'validate_password';
+-------------------+----------------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_LIBRARY | PLUGIN_STATUS | LOAD_OPTION |
+-------------------+----------------------+---------------+-------------+
| validate_password | validate_password.so | ACTIVE | ON |
+-------------------+----------------------+---------------+-------------+
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
mysql [localhost:8032] {msandbox} ((none)) > show status like 'validate_pass%';
+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| validate_password_dictionary_file_last_parsed | 2023-02-13 23:29:39 |
| validate_password_dictionary_file_words_count | 0 |
+-----------------------------------------------+---------------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
mysql [localhost:8032] {msandbox} ((none)) >
mysql [localhost:8032] {msandbox} ((none)) > set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 4 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
参数说明:
MySQL Connection Control Plugins
Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。
数次登陆失败后，会延迟下次登陆的时间，这也有点类似于多次密码输入错误，会被冻结一会的意思。
可防止客户端暴力破解或者DDOS攻击。云上mysql默认是安装了这插件。
CONNECTION_CONTROL：用来控制登录失败的次数及延迟响应时间
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS：该表将登录失败的操作记录至IS库中
Chapter 9 Installing Connection Control Plugins
检查是否有 connection_control.so
[root@ol8mysql01 msb_8_0_32]# ps -ef | grep mysql | grep 8.0.32
root 26158 1 0 07:07 pts/2 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/root/sandboxes/msb_8_0_32/my.sandbox.cnf
root 26545 26158 0 07:07 pts/2 00:00:37 /opt/mysql/8.0.32/bin/mysqld --defaults-file=/root/sandboxes/msb_8_0_32/my.sandbox.cnf --basedir=/opt/mysql/8.0.32 --datadir=/root/sandboxes/msb_8_0_32/data --plugin-dir=/opt/mysql/8.0.32/lib/plugin --user=root --log-error=/root/sandboxes/msb_8_0_32/data/msandbox.err --pid-file=/root/sandboxes/msb_8_0_32/data/mysql_sandbox8032.pid --socket=/tmp/mysql_sandbox8032.sock --port=8032
[root@ol8mysql01 msb_8_0_32]# find /opt/mysql/8.0.32 -name connection_control.so
/opt/mysql/8.0.32/lib/plugin/connection_control.so
[root@ol8mysql01 msb_8_0_32]#
安装:
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%';
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
mysql [localhost:8032] {msandbox} ((none)) > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%';
+------------------------------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+------------------------------------------+---------------+
| CONNECTION_CONTROL | ACTIVE |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE |
+------------------------------------------+---------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > show variables like 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
3 rows in set (0.10 sec)
mysql [localhost:8032] {msandbox} ((none)) >
mysql [localhost:8032] {msandbox} ((none)) > set global connection_control_failed_connections_threshold=4;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > set global connection_control_min_connection_delay=1500;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
mysql [localhost:8032] {msandbox} ((none)) > show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0 |
+------------------------------------+-------+
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
说明:
connection_control_failed_connections_threshold ：连续失败最大次数3次,0表示不开启
connection_control_max_connection_delay :超过最大失败次数之后阻塞登录最大时间（毫秒）默认约25天
connection_control_min_connection_delay :超过最大失败次数之后阻塞登录最小时间（毫秒）
测试
- 测试
TIMEFORMAT=%R
for i in `seq 1 10`
do
time mysql -ulin -h192.168.56.130 -P8032 -pmysql1 2>&1 >/dev/null | grep meh
done
- 服务器端变化
mysql [localhost:8032] {msandbox} ((none)) > select * from information_schema.connection_control_failed_login_attempts;
+---------------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+---------------------+-----------------+
| ''@'192.168.56.130' | 2 |
| 'root'@'localhost' | 1 |
+---------------------+-----------------+
2 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > select * from information_schema.connection_control_failed_login_attempts;
+---------------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+---------------------+-----------------+
| 'lin'@'%' | 10 |
| ''@'192.168.56.130' | 2 |
| 'root'@'localhost' | 1 |
+---------------------+-----------------+
3 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
- 客户端
[root@ol8mysql01 ~]# TIMEFORMAT=%R
[root@ol8mysql01 ~]# for i in `seq 1 10`
> do
> time mysql -ulin -h192.168.56.130 -P8032 -pmysql1 2>&1 >/dev/null | grep meh
> done
0.014
0.011
0.011
0.013
1.513
2.019
3.015
4.020
5.016
6.023
[root@ol8mysql01 ~]#
前面四次都是0.0秒之后尝试连接，之后都是延迟时间尝试登录，在connection_control_failed_login_attempts表中可以看到记录10次
如果重新设置 connection_control_failed_connections_threshold 参数后，表中记录会清零
如果用正确密码登录之后，对应记录会清零
-- 服务器端清零
mysql [localhost:8032] {msandbox} ((none)) > SET GLOBAL connection_control_failed_connections_threshold = 4;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) > select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)
mysql [localhost:8032] {msandbox} ((none)) >
-- 客户端
-- 安装nc
# dnf install -y nc
for i in `seq 1 10`
do
time echo meh | nc 192.168.56.130 8032 2>&1 >/dev/null | grep meh
done
客户端nc命令端口连接:
nc命令 全称netcat，用于设置路由器。 它能通过 TCP 和 UDP 在网络中读写数据。
服务器端的信息:
MySQL 保留字
9.3 Keywords and Reserved Words
MySQL 8.0 9.3 Keywords and Reserved Words
MySQL 5.7 9.3 Keywords and Reserved Words
MySQL 8.0 下面创建不了,5.7是OK的
-- 8.0.32版本
mysql [localhost:8032] {msandbox} (test) > create table test1(groups int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups int)' at line 1
mysql [localhost:8032] {msandbox} (test) >
-- 5.7.26版本
mysql [localhost:5726] {msandbox} (test) > create table test1(groups int);
Query OK, 0 rows affected (0.03 sec)
mysql [localhost:5726] {msandbox} (test) >
只能加引号创建
mysql [localhost:8032] {msandbox} (test) > create table test1(groups int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups int)' at line 1
mysql [localhost:8032] {msandbox} (test) >
mysql [localhost:8032] {msandbox} (test) > create table test1(`groups` int);
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:8032] {msandbox} (test) > show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`groups` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) >
MySQL slave 状态说明
dbdeployer 创建 5.7 一主两从环境
[root@ol8mysql01]# dbdeployer deploy replication 5.7
# 5.7 => 5.7.26
Installing and starting master
. sandbox server started
Installing and starting slave1
. sandbox server started
Installing and starting slave2
. sandbox server started
$HOME/sandboxes/rsandbox_5_7_26/initialize_slaves
initializing slave 1
initializing slave 2
Replication directory installed in $HOME/sandboxes/rsandbox_5_7_26
run 'dbdeployer usage multiple' for basic instructions'
[root@ol8mysql01]#
cd /root/sandboxes/rsandbox_5_7_26/node1
./use
状态说明:
slave1 [localhost:19328] {msandbox} ((none)) > show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event ### 表示IO线程的状态
Master_Host: 127.0.0.1 ### Master的IP地址
Master_User: rsandbox ### 主从复制的用户
Master_Port: 19327 ### Master的端口
Connect_Retry: 60 ### IO thread重连次数
Master_Log_File: mysql-bin.000001 ### IO thread正在读取的master上的binlog文件
Read_Master_Log_Pos: 4089 ### 正在读取的master上的binlog文件位置
Relay_Log_File: mysql-relay.000002 ### 执行的本地relay log文件名
Relay_Log_Pos: 4302 ### 执行的本地relay log位置
Relay_Master_Log_File: mysql-bin.000001 ### SQL线程正在执行的master binlog file
Slave_IO_Running: Yes ### IO thread状态
Slave_SQL_Running: Yes ### SQL thread状态
Replicate_Do_DB: ### 指定复制DB
Replicate_Ignore_DB: ### 指定复制忽略
Replicate_Do_Table: ### 指定复制表
Replicate_Ignore_Table: ### 指定忽略表
Replicate_Wild_Do_Table: ### 指定复制表，解决跨库的问题
Replicate_Wild_Ignore_Table: ### 指定忽略表，解决跨库的问题
Last_Errno: 0 ### 最近的复制错误
Last_Error: ### 最近一次错误信息
Skip_Counter: 0 ### 跳过复制数
Exec_Master_Log_Pos: 4089 ### 执行masterbinlog的位置
Relay_Log_Space: 4505 ### 所有relay log字节数总和
Until_Condition: None ### 指定复制条件
Until_Log_File: ### 指定复制到某个文件
Until_Log_Pos: 0 ### 指定复制到某个位置
Master_SSL_Allowed: No ### SSL配置
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 ### 从库落后主库时间，单位秒，不准确
Master_SSL_Verify_Server_Cert: No ### MASTER SSL配置
Last_IO_Errno: 0 ### 最近一次IO thread错误
Last_IO_Error: ### 最近一次IO thread错误详细信息
Last_SQL_Errno: 0 ### 最近一次sql thread错误
Last_SQL_Error: ### 最近一次sql thread错误详细信息
Replicate_Ignore_Server_Ids: ### 复制忽略server_id为xxx的实例
Master_Server_Id: 100 ### master server_id
Master_UUID: 00019327-1111-1111-1111-111111111111 ### master uuid
Master_Info_File: /root/sandboxes/rsandbox_5_7_26/node1/data/master.info ### 记录master信息文件
SQL_Delay: 0 ### sql延迟多少时间，延迟复制会用到
SQL_Remaining_Delay: NULL ### 当sql thread状态为Waiting until MASTER_DELAY seconds after master executed event，那么该值表示剩下延迟多少时间
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates ### sql thread状态
Master_Retry_Count: 86400 ### 主从复制断开时slave最多能尝试重新连接的次数
Master_Bind: ### 绑定网卡
Last_IO_Error_Timestamp: ### io thread 最近一次的错误时间
Last_SQL_Error_Timestamp: ### sql thread 最近一次的错误时间
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0 ### 使用auto_position，建议还是开启比较方便
Replicate_Rewrite_DB: ### 复制DB对应关系
Channel_Name: ### 多源复制的channel name
Master_TLS_Version:
1 row in set (0.00 sec)
slave1 [localhost:19328] {msandbox} ((none)) >
mysqldump导出 timestamp 和 datetime 列的一些tips
准备数据
create table t1(
id int not null auto_increment,
c1_timestamp timestamp not null,
c2_datetime datetime not null,
primary key(id)
) engine=innodb;
insert into t1(c1_timestamp,c2_datetime) values(now(),now());
mysql [localhost:8032] {msandbox} (test) > select * from t1;
+----+---------------------+---------------------+
| id | c1_timestamp | c2_datetime |
+----+---------------------+---------------------+
| 1 | 2023-02-21 19:46:17 | 2023-02-21 19:46:17 |
| 2 | 2023-02-21 19:46:19 | 2023-02-21 19:46:19 |
| 3 | 2023-02-21 19:46:21 | 2023-02-21 19:46:21 |
| 4 | 2023-02-21 19:46:23 | 2023-02-21 19:46:23 |
| 5 | 2023-02-21 19:46:26 | 2023-02-21 19:46:26 |
| 6 | 2023-02-21 19:46:27 | 2023-02-21 19:46:27 |
| 7 | 2023-02-21 19:46:29 | 2023-02-21 19:46:29 |
| 8 | 2023-02-21 19:46:30 | 2023-02-21 19:46:30 |
| 9 | 2023-02-21 19:46:33 | 2023-02-21 19:46:33 |
+----+---------------------+---------------------+
9 rows in set (0.00 sec)
mysql [localhost:8032] {msandbox} (test) >
mysqldump 参数说明
--user, -u
指定连接的用户名
--password, -p
连接数据库密码
--socket,-S
指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
--default-character-set
设置默认字符集，默认值为utf8
--quick, -q
不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。
--master-data
该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master status)。
如果为1，将会输出CHANGE MASTER 命令；
如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开
--single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
它只适用于多版本存储引擎，仅InnoDB。
本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。
要想导出大表的话，应结合使用--quick选项。
--triggers
导出触发器。该选项默认启用，用--skip-triggers禁用它。
--routines, -R
导出存储过程以及自定义函数。
--events, -E
导出事件。
--databases, -B
导出几个数据库。参数后面所有名字参量都被看作数据库名。
--tables
覆盖--databases (-B)参数，指定需要导出的表名。
--where, -w
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
测试: 通过where条件过滤大于'2023-02-21 19:46:23'的数据
mysqldump -ulin -pmysql -S /tmp/mysql_sandbox8032.sock --default-character-set=utf8 --source-data=2 --single-transaction --set-gtid-purged=off --triggers --routines --events --databases test --tables t1 --where "c1_timestamp>'2023-02-21 19:46:23'" > c1_timestamp.sql
mysqldump -ulin -pmysql -S /tmp/mysql_sandbox8032.sock --default-character-set=utf8 --source-data=2 --single-transaction --set-gtid-purged=off --triggers --routines --events --databases test --tables t1 --where "c2_datetime>'2023-02-21 19:46:23'" > c2_datetime.sql
[root@ol8mysql01 ~]# diff c1_timestamp.sql c2_datetime.sql
42c42
< -- WHERE: c1_timestamp>'2023-02-21 19:46:23'
---
> -- WHERE: c2_datetime>'2023-02-21 19:46:23'
45a46
> INSERT INTO `t1` VALUES (5,'2023-02-21 11:46:26','2023-02-21 19:46:26'),(6,'2023-02-21 11:46:27','2023-02-21 19:46:27'),(7,'2023-02-21 11:46:29','2023-02-21 19:46:29'),(8,'2023-02-21 11:46:30','2023-02-21 19:46:30'),(9,'2023-02-21 11:46:33','2023-02-21 19:46:33');
66c67
< -- Dump completed on 2023-02-21 20:11:57
---
> -- Dump completed on 2023-02-21 20:12:13
[root@ol8mysql01 ~]#
通过datetime列过滤的c2_datetime.sql文件比timestamp列过滤的c1_timestamp.sql多了数据(INSERT INTO…)
所以在相同条件下，使用timestamp列作为过滤条件进行备份时，备份文件中并没有备份数据； 在使用datetime列作为过滤条件进行备份的情况下，可以发现是有数据的。
原因:
对于timestamp这种数据类型，会把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。
查询时，将其又转化为客户端当前时区进行返回。而对于datetime这种数据类型，不会做任何改变，就是原样输入和输出。
也就是timestamp数据类型的记录导出会以utc时间格式导出，导入库中自动由UTC格式转为系统默认时区，
所以看到导出文件timestamp内容和实际存储的不相符。
解决办法
在使用mysqldump备份时，加入参数–skip-tz-utc用于禁止timestamp时区转换。
因为使用–skip-tz-utc选项后，导出sql文件中开头不会设置的/*!40103 SET TIME_ZONE=’+00:00’ */。
mysqldump -ulin -pmysql -S /tmp/mysql_sandbox8032.sock --default-character-set=utf8 --skip-tz-utc --source-data=2 --single-transaction --set-gtid-purged=off --triggers --routines --events --databases test --tables t1 --where "c1_timestamp>'2023-02-21 19:46:23'" > c1_timestamp_1.sql
mysqldump -ulin -pmysql -S /tmp/mysql_sandbox8032.sock --default-character-set=utf8 --skip-tz-utc --source-data=2 --single-transaction --set-gtid-purged=off --triggers --routines --events --databases test --tables t1 --where "c2_datetime>'2023-02-21 19:46:23'" > c2_datetime_1.sql
[root@ol8mysql01 ~]# diff c1_timestamp_1.sql c2_datetime_1.sql
40c40
< -- WHERE: c1_timestamp>'2023-02-21 19:46:23'
---
> -- WHERE: c2_datetime>'2023-02-21 19:46:23'
64c64
< -- Dump completed on 2023-02-21 20:20:31
---
> -- Dump completed on 2023-02-21 20:20:32
[root@ol8mysql01 ~]#
其他:
[root@ol8mysql01 ~]# mysqldump -ulin -pmysql -S /tmp/mysql_sandbox8032.sock --default-character-set=utf8 --master-data=2 --single-transaction --triggers --routines --events --databases test --tables t1 --where "c1_timestamp>'2023-02-21 19:46:23'" > c1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@ol8mysql01 ~]#
导出时就不使用master-data，改为source-data替代。这属于新版本8.0.26 mysqldump 的改变，新引入的参数。
关于GTID是5.6以后，加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。
官方给的：A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).
所以可能是因为在一个数据库里面唯一，但是当导入其他的库就有可能重复。所有会有一个提醒。
可以通过添加--set-gtid-purged=off 或者–gtid-mode=OFF这两个参数设置。
在导入库中重新生产GTID，而不用原来的。
MySQL从5.6到8.0并行复制的演进
MySQL的主从复制
1.1 主从复制基本原理
MySQL的主从架构依赖于MySQL Binlog功能，Master节点上产生Binlog并且写入到文件中。
Master节点上启动一个DUMP线程：当Slave节点I/O线程连接Master时，Master创建这个线程，DUMP线程负责从Master的binlog文件读取记录，然后发送给Slave。每个连接到Master的Slave都有一个DUMP线程。
Slave节点上启动两个线程：IO线程和SQL线程，IO线程从MySQL上拉取Binlog日志并写入到本地的RelayLog日志；SQL线程不断从RelayLog日志中读取日志并解析执行，这样就可以保证所有在主服务器上执行过的SQL语句都在从服务器上一模一样的执行过一遍。
_
1.2 复制延迟
复制延迟，指的就是一个事务在Master执行完成以后，要多久以后才能在Slave上执行完成。
由于对Binlog文件以及RelayLog文件的读写均为顺序操作，在生产环境中，Slave上的IO线程对Binlog文件的Dump操作是很少产生延迟的。实际上，从MySQL5.5开始，MySQL官方提供了半同步复制插件，每个事务的Binlog需要保证传输到Slave写入RelayLog后才能提交，这种架构在主从之间提供了数据完整性，保证了主服务器在发生故障后从服务器可以拥有完整的数据副本。因此，复制延迟通常发生在SQL线程执行的过程中。
在上面的架构图上可以看到，最早的主从复制模型中，只有一个线程负责执行Relaylog，也就是说所有在主服务器上的操作，在从服务器上是串行回放的。这就带来一个问题，如果主服务器上写入压力比较大，那么从服务器上的回放速度很有可能会一直跟不上主。既然主从延迟的问题是单线程回放RelayLog太慢，那么减少主从延迟的方案自然就是提高从服务器上回放RelayLog的并行度。
MySQL5.6的并行复制
2.1 MySQL5.6并行复制简介
MySQL从5.6版本开始支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的。如果用户的MySQL数据库实例中存在多个schema且schema下表数量较少，对于从服务器复制的速度的确可以有比较大的帮助。
2.2 MySQL5.6并行复制存在的问题
基于schema级别的并行复制存在一个问题，schema级别的并行复制效果并不高，如果用户实例有很少的库和较多的表，那么并行回放效果会很差，甚至性能会比原来的单线程更差，而单库多表是比多库多表更为常见的一种情形
MySQL5.7的并行复制
3.1 MySQL5.7并行复制简介
MySQL5.6基于库的并行复制出来后，基本无人问津，在沉寂了一段时间之后，MySQL 5.7出来了，它的并行复制以一种全新的姿态出现在了DBA面前。MySQL5.7中slave服务器的回放与master是一致的，即master服务器上是怎么并行执行的，那么slave上就怎样进行并行回放。不再有库的并行复制限制。
下面来看看MySQL 5.7中的并行复制究竟是如何实现的？
组提交：通过对事务进行分组，优化减少了生成二进制日志所需的操作数。当事务同时提交时，它们将在单个操作中写入到二进制日志中。如果事务能同时提交成功，那么它们就不会共享任何锁，这意味着它们没有冲突，因此可以在Slave上并行执行。所以通过在二进制日志中添加组提交信息，实现Slave可以并行地安全地运行事务。
Group Commit技术在MySQL5.6中是为了解决事务提交的时候需要fsync导致并发性不够而引入的。简单来说，就是由于事务提交时必须将Binlog写入到磁盘上而调用fsync，这是一个代价比较高的操作，事务并发提交的情况下，每个事务各自获取日志锁并进行fsync会导致事务实际上以串行的方式写入Binlog文件，这样就大大降低了事务提交的并发程度。
Group Commit技术将事务的提交阶段分成了Flush、Sync、Commit三个阶段，每个阶段维护一个队列，并且由该队列中第一个线程负责执行该步骤，这样实际上就达到了一次可以将一批事务的Binlog fsync到磁盘的目的，这样的一批同时提交的事务称为同一个Group的事务。
Group Commit虽然是属于并行提交的技术，但是却意外解决了从服务器上事务并行回放的一个难题——即如何判断哪些事务可以并行回放。如果一批事务是同时Commit的，那么这些事务必然不会有互斥的持有锁，也不会有执行上的相互依赖，因此这些事务必然可以并行的回放。
为了标记事务所属的组，MySQL5.7版本在产生Binlog日志时会有两个特殊的值记录在 Binlog Event 中，last_committed 和 sequence_number，其中 last_committed指的是该事务提交时，上一个事务提交的编号，sequence_number是事务提交的序列号，在一个Binlog文件内单调递增。如果两个事务的last_committed值一致，这两个事务就是在一个组内提交的。
_
为了兼容MySQL5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：DATABASE（默认值，基于库的并行复制方式）、LOGICAL_CLOCK（基于组提交的并行复制方式）。
3.2 MySQL5.7并行复制存在的问题
在上文可以看到，MySQL主从复制的SQL线程回放在5.6实现了库级别的并行，在5.7实现了组提交级别的并行，但是在MySQL5.7中，基于Logical_Clock的并行复制仍然有不尽人意的地方，比如必须是在主服务器上并行提交的事务才能在从服务器上并行回放，如果主服务器上并发压力不大，那么就无法享受到并行复制带来的好处。MySQL5.7中引入了binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count两个参数，通过让Binlog在执行fsync前等待一小会来提高Master上组提交的比例。但是无论如何，从服务器上并行回放的速度还是取决于主服务器上并行提交的情况。
MySQL8.0的并行复制
4.1 基于WriteSet的并行复制
在MySQL8.0中引入了一种新的机制来判断事务能否并行回放，通过检测事务在运行过程中是否存在写冲突来决定从服务器上的回放顺序，这使得从服务器上的并发程度不再依赖于主服务器。
事实上，该机制在MySQL5.7.20版本中就已经应用了。MySQL在5.7.20版本引入了一个重要的特性：Group Replication(俗称MGR)，通过Paxso协议在多个MySQL节点间分发binlog，使得一个事务必须在集群内大多数节点((N/2)+1)上提交成功才能提交。为了支持多主写入，MGR在Binlog分发节点完成后，通过一个Certify阶段来决定Binlog中的事务是否写入RelayLog中。这个过程中，Certify阶段采用的就是WriteSet的方式验证事务之间是否存在冲突，同时，在写入RelayLog时会将没有冲突的事务的last_committed值设置为相同的值。在MySQL8.0中，MySQL的并行复制引用了这种机制，通过基于WriteSet的冲突检测，在主服务器上产生Binlog的时候，不再基于组提交，而是基于事务本身的更新冲突来确定并行关系。
4.2 相关参数
在MySQL 8.0中，引入了参数binlog_transaction_dependency_tracking用于控制如何决定事务的依赖关系。
该值有三个选项：
COMMIT_ORDERE:表示继续使用5.7中的基于组提交的方式决定事务的依赖关系(默认值)；
WRITESET：表示使用写集合来决定事务的依赖关系；
WRITESET_SESSION：表示使用WriteSet来决定事务的依赖关系，但是同一个Session内的事务不会有相同的last_committed值。
在代码实现上，MySQL采用一个vector的变量存储已经提交的事务的HASH值，所有已经提交的事务的所修改的主键和非空的UniqueKey的值经过HASH后与该vector中的值对比，由此来判断当前提交的事务是否与已经提交的事务更新了同一行，并以此确定依赖关系。该向量的大小由参数binlog_transaction_dependency_history_size控制，取值范围为1-1000000 ，初始默认值为25000，该值越大可以记录更多的已经提交的事务信息，不过需要注意的是，这个值并非指事务大小，而是指追踪的事务更新信息的数量。同时参数transaction_write_set_extraction控制检测事务依赖关系时采用的HASH算法有三个取值OFF|XXHASH64|MURMUR32，如果binlog_transaction_depandency_tracking取值为WRITESET或WRITESET_SESSION，那么该值取值不能为OFF，且不能变更。
4.3 WriteSet依赖检测条件
WriteSet是基于主键的冲突检测(binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION,修改的row的主键或非空唯一键没有冲突，即可并行)。在开启了WRITESET或WRITESET_SESSION后，MySQL按以下的方式标识并记录事务的更新：
如果事务当前更新的行有主键，则将HASH（DB名、TABLE名、KEY名称、KEY_VALUE1、KEY_VALUE2……）加入到当前事务的vector write_set中。
如果事务当前更新的行有非空的唯一键，同样将HASH（DB名、TABLE名、KEY名、KEY_VALUE1）……加入到当前事务的write_set中。
如果事务更新的行有外键约束且不为空，则将该外键信息与VALUE的HASH加到当前事务的 write_set中。
如果事务当前更新的表的主键是其它某个表的外键，则设置当前事务has_related_foreign_key = true。
如果事务更新了某一行且没有任何数据被加入到write_set中，则标记当前事务 has_missing_key = true。在执行冲突检测的时候，先会检查has_related_foreign_key和has_missing_key ， 如果为true，则退到COMMIT_ORDER模式；否则，会依照事务的write_set中的HASH值与已提交的事务的write_set进行比对。如果没有冲突，则当前事务与最后一个已提交的事务共享相同的last_commited，否则将从全局已提交的write_set中删除那个冲突的事务之前提交的所有write_set，并退化到COMMIT_ORDER计算last_committed。
在每一次计算完事务的last_committed值以后，需要去检测当前全局已经提交的事务的write_set是否已经超过了binlog_transaction_dependency_history_size设置的值，如果超过，则清空已提交事务的全局write_set。
从检测条件上看，该特性依赖于主键和唯一索引，如果事务涉及的表中没有主键且没有唯一非空索引，那么将无法从此特性中获得性能的提升。除此之外，还需要将Binlog格式设置为Row格式。
总结
在MySQL8.0中，开启了基于WriteSet的事务依赖后，Slave上的 RelayLog回放速度将不再依赖于Master上提交时的并行程度，使得Slave上可以发挥其最大的吞吐能力，这个特性在Slave上复制停止一段时间后恢复复制时尤其有效。
这个特性使得Slave上可能拥有比Master上更大的吞吐量，同时可能在保证事务依赖关系的情况下，在Slave上产生Master上没有产生过的提交场景，事务的提交顺序可能会在Slave上发生改变。虽然在5.7的并行复制中就可能发生这种情况，不过在8.0中由于Slave上更高的并发能力，会使该场景更加常见。通常情况下这不是什么大问题，不过如果在Slave上做基于Binlog的增量备份，可能就需要保证在Slave上与Master上一致的提交顺序，这种情况下可以开启slave_preserve_commit_order，这是一个5.7就引入的参数，可以保证Slave上并行回放的线程按RelayLog中写入的顺序Commit。
Referece
参考:
Have a good work&life! 2023/02 via LinHong