MySQL 8.0.30新特性GIPK

最新推荐文章于 2024-03-27 21:17:06 发布

蒙其

最新推荐文章于 2024-03-27 21:17:06 发布

阅读量505

点赞数 1

文章标签： mysql adb android

本文链接：https://blog.csdn.net/m0_66522805/article/details/132597880

版权

从MySQL8.0.30开始，开始支持generated invisible primary keys(GIPK) ，局限于innodb引擎的表，即当开启GIPK特性后，创建无主键innodb表时，默认会在表中创建一个主键`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT，PRIMARY KEY (`my_row_id`)字段。通过sql_generate_invisible_primary_key 控制是否开启GIPK特性，默认关闭。生成的主键不能更改，可以在 visible 和 invisible 之间进行转换，如果要使自动创建的显示主键invisible，可以执行：alter table table_name alter column my_row_id set invisible，此期间会加MDL写锁（dml语句会加MDL读锁，alter会加MDL写锁，读写，写写排斥，隔离DML语句与DDL语句之间的操作的影响）

ps：开启新特性后，创建无主键的innodb表时，不支持创建或添加列、索引的命名为my_row_id

新特性测试：

mysql> show global variables like 'sql_generate_invisible_primary_key';     默认GIPK特性关闭状态
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)
 
mysql> set global sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)
 
重新连接
 
mysql> create database rbac;
Query OK, 1 row affected (0.01 sec)
 
 
mysql> use rbac
Database changed
 
mysql> create table bj(id int);
Query OK, 0 rows affected (0.04 sec)
 
mysql> show create table bj;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bj    | CREATE TABLE `bj` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
###解析binlog同样有my_row_id字段信息###
/usr/local/mysql/bin/mysqlbinlog  -v mysql-binlog.000004|less
 
CREATE TABLE `bj` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
 
###插入测试数据
mysql> insert into bj values(1);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from bj;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

结论：8.0.30版本及更新版本，开启GIPK特性后，创建无主键的innodb表，会自动创建一列pki：`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT，PRIMARY KEY (`my_row_id`)，默认不可见。

部署mysql测试环境

[root@vm10-0-0-99 ~]# rz -re mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
[root@vm10-0-0-99 ~]# tar xf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
[root@vm10-0-0-99 ~]# mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql
[root@vm10-0-0-99 ~]# mv mysql /usr/local/
[root@vm10-0-0-99 ~]# mkdir -p /data/3306/data
[root@vm10-0-0-99 ~]# useradd mysql
[root@vm10-0-0-99 ~]# chown -R mysql.mysql /data/
[root@vm10-0-0-99 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data/
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
[root@vm10-0-0-99 ~]# yum install -y libaio
[root@vm10-0-0-99 ~]# cat /data/3306/my.cnf
[mysqld]
user=mysql
server_id=33061
basedir=/usr/local/mysql
datadir=/data/3306/data
port=3306
socket=/data/3306/mysql.sock
log_error=/data/3306/data/mysql-error.log
log-bin=/data/3306/data/mysql-binlog
[root@vm10-0-0-99 ~]#/usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf
[root@vm10-0-0-99 ~]#/usr/local/mysql/bin/mysql  -S /data/3306/mysql.sock
[root@vm10-0-0-99 ~]# ss -lntup
Netid  State      Recv-Q Send-Q                                              Local Address:Port                                                             Peer Address:Port             
udp    UNCONN     0      0                                                               *:68                                                                          *:*                   users:(("dhclient",pid=4764,fd=6))
udp    UNCONN     0      0                                                               *:111                                                                         *:*                   users:(("systemd",pid=1,fd=37))
tcp    LISTEN     0      128                                                             *:22                                                                          *:*                   users:(("sshd",pid=4837,fd=3))
tcp    LISTEN     0      128                                                             *:111                                                                         *:*                   users:(("systemd",pid=1,fd=36))
tcp    LISTEN     0      128                                                            :::22                                                                         :::*                   users:(("sshd",pid=4837,fd=4))
tcp    LISTEN     0      70                                                             :::33060                                                                      :::*                   users:(("mysqld",pid=16852,fd=20))
tcp    LISTEN     0      128                                                            :::3306                                                                       :::*                   users:(("mysqld",pid=16852,fd=23))

###同版本搭建主从###

#创建主从复制用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123';    --低版本mysql报错把密码插件去掉执行
grant replication slave on *.* to repl@'10.0.0.%';
#主库查看binlog pos点信息
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-binlog.000001 |      674 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
#从库执行连接主库命令
change master to master_host='10.0.0.99',master_user='repl',master_password='123',master_port=3306,master_log_file='mysql-binlog.000001',master_log_pos=674,master_connect_retry=10;
start slave;
show slave status\G;

###8.0.32从库### port 3306

###8.0.30从库### port 3307

###8.0.20从库### port 3308

###5.7.30从库### port 3309

架构图：

8.0.32及8.0.30开启此特性，8.0.20及5.7.30无此特性
mysql> show global variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+
1 row in set (0.00 sec)
 
 
主库创建无主键表
mysql> create database rbac;
Query OK, 1 row affected (0.01 sec)
 
mysql> use rbac
Database changed
mysql> create table test(a int);
Query OK, 0 rows affected (0.03 sec)
 
mysql> create table dig_a(id int);
Query OK, 0 rows affected (0.05 sec)
 
#各版本从库正常回放
mysql> show create table dig_a;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dig_a | CREATE TABLE `dig_a` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

结论1：高版本-->低版本复制，需要注意版本兼容性问题，字符集、排序规则、插件。

8.0.32 --> 5.7.30
master binlog ：
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255  255为utf8mb4
从库不支持SET @@session.character_set_client=255，所以每个event前执行环境变量时会报错，导致主从中断,通过set global character_set_client无法持久化参数
master  my.cnf配置文件修改utf8mb3，重启生效。
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
[mysql]
default-character-set=utf8mb3
[client]
default-character-set=utf8mb3
 
255对应的utf8mb4，33对应的是mb3,5.7支持设置为utf8mb4，但是不支持将此变量设置为255

复制功能测试

Master插入测试数据：  my_row_id为不可见状态
mysql> insert into dig_a values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into dig_a values(2);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dig_a values(3);
Query OK, 1 row affected (0.01 sec)
 
 
mysql> select * from dig_a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
 
mysql> delete from dig_a where my_row_id=1;
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from dig_a;
+------+
| id   |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)
 
 
mysql> update  dig_a set id=22 where my_row_id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from dig_a;
+------+
| id   |
+------+
|   22 |
|    3 |
+------+
2 rows in set (0.00 sec)
 
mysql> insert into dig_a values(4);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dig_a(my_row_id,id) values(5,5);
Query OK, 1 row affected (0.01 sec)
 
mysql> update dig_a  set id=44 where my_row_id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> delete from dig_a where my_row_id=3;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from dig_a;
+-----------+------+
| my_row_id | id   |
+-----------+------+
|         1 |    1 |
|         2 |    2 |
|         4 |   44 |
+-----------+------+
3 rows in set (0.00 sec)
 
 
 
 
 
my_row_id visible可见状态：
清空表数据
mysql> truncate dig_a;
Query OK, 0 rows affected (0.05 sec)
 
mysql> alter table dig_a alter column my_row_id set visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> insert into dig_a values(1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1                        ---visible可见状态下不指定列插入报错
mysql> insert into dig_a(id) values(1);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dig_a(id) values(2);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dig_a(id) values(3);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dig_a(my_row_id,id) values(4,4); 
Query OK, 1 row affected (0.01 sec)
 
mysql> update dig_a  set id=44 where my_row_id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> delete from dig_a where my_row_id=3;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from dig_a;
+-----------+------+
| my_row_id | id   |
+-----------+------+
|         1 |    1 |
|         2 |    2 |
|         4 |   44 |
+-----------+------+
3 rows in set (0.00 sec)

结论2：执行alter ...... visible使my_row_id隐藏/显示的命令无gipk特性版本会报错

my_row_id 为visible可见状态下：insert 语句必须指定列插入，否则报错；update delete可以通过my_row_id进行查询、更新、删除操作

my_row_id为invisible不可见状态下，DML语句正常，且可以通过my_row_id进行查询、更新和删除操作

使用自定义列代替my_row_id：
 
mysql> alter table dig_a drop column my_row_id;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.'
 
在设置sql_generate_invisible_primary_key=ON的情况下，必须要有一个主键（存量表除外）
 
mysql> set session sql_generate_invisible_primary_key=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)
 
mysql> alter table dig_a drop column my_row_id;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> alter table dig_a add a int primary key auto_increment;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> select * from dig_a;
+------+---+
| id   | a |
+------+---+
|    1 | 1 |
|    2 | 2 |
|   44 | 3 |
+------+---+
3 rows in set (0.00 sec)
mysql> desc dig_a;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | YES  |     | NULL    |                |
| a     | int  | NO   | PRI | NULL    | auto_increment |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

结论2：对于已经自动添加my_row_id的表，如需要使用自定义列代替my_row_id作为主键，可以先将set session sql_generate_invisible_primary_key=OFF;然后进行表结构变更

DDL加列测试
mysql> set session sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)
 
mysql> create table dig_b(id int not null default 0);
Query OK, 0 rows affected (0.03 sec)
 
mysql> show create table dig_b;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dig_b | CREATE TABLE `dig_b` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> alter table dig_b add  a int default 0 not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> select * from dig_b;
+----+---+
| id | a |
+----+---+
|  1 | 0 |
|  2 | 0 |
|  3 | 0 |
+----+---+
3 rows in set (0.00 sec)
 
mysql> alter table dig_b add index i_a(a);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

结论3：其他非对列my_row_id的DDL语句，从库可以正常回放

集群2：测试mysql5.7.30 -→mysql8.0.32版本同步

架构图：

###从库开启GIPK特性###
mysql> set global sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.31 sec)
 
###主库创建表###
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)
mysql> create database  rbac_2;
Query OK, 1 row affected (0.01 sec)
mysql> use rbac_2;
Database changed
mysql> create table dic(id int);
Query OK, 0 rows affected (0.03 sec)
###从库查看表结构###
mysql> show create table dic;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| dic   | CREATE TABLE `dic` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------+
从库开启gipk，主库(5.7.30)创建的表无主键的情况下，下游gipk特性失效，完全按照主库的表结构同步
 
###主库创建带有my_row_id字段的无主键innodb表###
 
mysql> create table dic_2(my_row_id int,id int);
Query OK, 0 rows affected (0.02 sec)
 
从库查询
mysql> show create table dic_2;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| dic_2 | CREATE TABLE `dic_2` (
  `my_row_id` int DEFAULT NULL,
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
从库不影响创建带有my_row_id敏感列名的表

结论4：在主库是没有此特性的情况下，从库(8.0.32)通过回放主库的binlog进行同步，gipk特性失效，且主库创建带有my_row_id关键字的列，从库回放不会报错

测试：

用户由当前8.0.27升级至8.0.32版本后，之前无主键表是否可以进行ddl变更 MySQL8.0.32 GIPK开启无主键表DDL变更

测试：

由mysql 8.0.27 升级到mysql 8.0.32，测试功能是否正常

#主从环境
Master 8.0.27   10.0.0.99:3311
Slave  8.0.27   10.0.0.115:3311
###升级Slave版本   8.0.27-->8.0.32###
#安装新版本mysql 8.0.32
rz -re mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
#解压安装包
tar -xf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
#mv到/usr/local/下
mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql8032
mv mysql8032/ /usr/local/
#修改用户
chown -R mysql.mysql /usr/local/mysql8032
#关闭从库
ps -ef|grep 3311
kill pid
#使用新版本mysqld_safe 指定 旧版本  配置文件（数据目录） 启动
/usr/local/mysql8032/bin/mysqld_safe  --defaults-file=/data/3311/my.cnf  &
[root@vm10-0-0-115 ~]# ps -ef|grep 3311
root     35481 31967  0 11:12 pts/1    00:00:00 grep --color=auto 3311
root     81003     1  0 Jul27 ?        00:00:00 /bin/sh /usr/local/mysql8032/bin/mysqld_safe --defaults-file=/data/3311/my.cnf
mysql    81167 81003  0 Jul27 ?        00:02:02 /usr/local/mysql8032/bin/mysqld --defaults-file=/data/3311/my.cnf --basedir=/usr/local/mysql8027 --datadir=/data/3311/data --plugin-dir=/usr/local/mysql8027/lib/plugin --user=mysql --log-error=/data/3311/data/mysql-error.log --pid-file=vm10-0-0-115.pid --socket=/data/3311/mysql.sock --port=3311
#启动mysql，验证是否升级成功
/usr/local/mysql8032/bin/mysql -S /data/3311/mysql.sock
Server version:     8.0.32 MySQL Community Server - GPL
mysql> show global variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)
#升级成功#

蒙其

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 8.0.30新特性GIPK

结论2：对于已经自动添加my_row_id的表，如需要使用自定义列代替my_row_id作为主键，可以先将set session sql_generate_invisible_primary_key=OFF;结论4：在主库是没有此特性的情况下，从库(8.0.32)通过回放主库的binlog进行同步，gipk特性失效，且主库创建带有my_row_id关键字的列，从库回放不会报错。my_row_id为invisible不可见状态下，DML语句正常，且可以通过my_row_id进行查询、更新和删除操作。
复制链接

扫一扫