工具分享-从ibd文件中恢复数据的神器ibd2sql，假如备份和Binlo(3)

p0.ibd --sql ibd2sql /mysql57/db1/xxx.ibd --sdi-table /mysql80/db1/xxx.ibd --sql --mysql5

3.3.4修改lower_case_table_names

lower_case_table_names 是 MySQL 设置大小写是否敏感的一个参数。

lower_case_table_names = 0 表名存储为给定的大小和比较是区分大小写的
lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names = 2 表名存储为给定的大小写但是比较的时候是小写的

通常我们在数据库初始化的时候就已经确定这个参数，想要修改这个参数只能导出重新初始化再导入。

但是ibd2sql号称可以直接修改lower_case_table_names，请各位看官老爷往下看。

lower_case_table_names 由0改成1，对象中已经有大小写混合，可以启动但是原本大小写混合对象读写会有问题

CREATE TABLE IF NOT EXISTS `test`.`TMst`(
    `id` int NOT NULL AUTO_INCREMENT,
    `creator` varchar(64) NULL,
    `updated_by` varchar(64) NULL,
    `end_at` datetime(6) NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
INSERT INTO test.TMst (creator) VALUES ('user2');
INSERT INTO test.TMst (creator) VALUES ('user1');
--查看原来的lower_case_table_names为1还是0
$ python3 modify_lower_case_table_names.py /greatsql/dbdata/data3306/data/mysql.ibd 
lower_case_table_names: 0
--停止数据库
$ systemctl stop greatsql
--修改lower_case_table_names为1
$ python3 modify_lower_case_table_names.py /greatsql/dbdata/data3306/data/mysql.ibd ./mysql.ibd 1
set lower_case_table_names=1 into new file(./mysql.ibd) finish.
--对比文件权限属主
$ ls -la ./mysql.ibd 
-rw-r--r-- 1 root root 26214400 Apr 29 10:58 ./mysql.ibd
$ ls -la /greatsql/dbdata/data3306/data/mysql.ibd
-rw-r----- 1 greatsql greatsql 26214400 Apr 29 10:56 /greatsql/dbdata/data3306/data/mysql.ibd
--修改文件属主
$  chmod 640 ./mysql.ibd
$  chown greatsql:greatsql ./mysql.ibd  
-- 覆盖原来的mysql.ibd文件
$ mv ./mysql.ibd /greatsql/dbdata/data3306/data
mv: overwrite '/greatsql/dbdata/data3306/data/mysql.ibd'? y
--修改配置文件 lower_case_table_names=1
$ vi /greatsql/conf/greatsql3306.cnf 
--启动数据库
$ systemctl start greatsql
--查询表，插入数据报错
greatsql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| TMst           |
+----------------+
1 row in set (0.01 sec)

greatsql> SELECT * FROM TMst;
ERROR 1146 (42S02): Table 'test.tmst' doesn't exist

greatsql> INSERT INTO test.TMst (creator) VALUES ('user3');
ERROR 1146 (42S02): Table 'test.tmst' doesn't exist


greatsql> DROP database test;
greatsql> CREATE database test;
ERROR 3678 (HY000): Schema directory './test' already exists. This must be resolved manually (e.g. by moving the schema directory to another location).

lower_case_table_names 由1改成0，对象读写正常

create database test1;
CREATE TABLE IF NOT EXISTS `test1`.`tmst`(
    `id` int NOT NULL AUTO_INCREMENT,
    `creator` varchar(64) NULL,
    `updated_by` varchar(64) NULL,
    `end_at` datetime(6) NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
INSERT INTO test1.tmst (creator) VALUES ('user2');
INSERT INTO test1.tmst (creator) VALUES ('user1');
--查看原来的lower_case_table_names为1还是0
$ python3 modify_lower_case_table_names.py /greatsql/dbdata/data3306/data/mysql.ibd 
lower_case_table_names: 1
--停止数据库
$ systemctl stop greatsql
--修改lower_case_table_names为1
$ python3 modify_lower_case_table_names.py /greatsql/dbdata/data3306/data/mysql.ibd ./mysql.ibd 0
set lower_case_table_names=0 into new file(./mysql.ibd) finish.
--对比文件权限属主
$ ls -la ./mysql.ibd 
-rw-r--r-- 1 root root 26214400 Apr 29 10:58 ./mysql.ibd
$ ls -la /greatsql/dbdata/data3306/data/mysql.ibd
-rw-r----- 1 greatsql greatsql 26214400 Apr 29 10:56 /greatsql/dbdata/data3306/data/mysql.ibd
--修改文件属主
$  chmod 640 ./mysql.ibd
$  chown greatsql:greatsql ./mysql.ibd  
-- 覆盖原来的mysql.ibd文件
$ mv ./mysql.ibd /greatsql/dbdata/data3306/data
mv: overwrite '/greatsql/dbdata/data3306/data/mysql.ibd'? y
--修改配置文件 lower_case_table_names=1
$ vi /greatsql/conf/greatsql3306.cnf 
--启动数据库
$ systemctl start greatsql
--查询表，新建带有大写的表均正常
greatsql> CREATE TABLE IF NOT EXISTS `test1`.`TMst`(
    ->     `id` int NOT NULL AUTO_INCREMENT,
    ->     `creator` varchar(64) NULL,
    ->     `updated_by` varchar(64) NULL,
    ->     `end_at` datetime(6) NULL,
    ->     PRIMARY KEY  (`id` )
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected (0.01 sec)

greatsql> INSERT INTO test1.TMst (creator) VALUES ('user2');
Query OK, 1 row affected (0.02 sec)

greatsql> INSERT INTO test1.TMst (creator) VALUES ('user1');
Query OK, 1 row affected (0.01 sec)

greatsql> SELECT * FROM `test1`.`TMst`;
+----+---------+------------+--------+
| id | creator | updated_by | end_at |
+----+---------+------------+--------+
|  1 | user2   | NULL       | NULL   |
|  2 | user1   | NULL       | NULL   |
+----+---------+------------+--------+
2 rows in set (0.00 sec)

greatsql> SELECT * FROM `test1`.`tmst`;
+----+---------+------------+--------+
| id | creator | updated_by | end_at |
+----+---------+------------+--------+
|  1 | user2   | NULL       | NULL   |
|  2 | user1   | NULL       | NULL   |
+----+---------+------------+--------+
2 rows in set (0.01 sec)

结论

1. 修改lower_case_table_names

2. 由0改成1，对象中已经有大小写混合，可以启动但是原本大小写混合对象读写会有问题。

3. 由1改成0，对象读写正常，操作名字大小混合的对象也正常。

参考文章

1. GreatSQL 二进制安装：https://greatsql.cn/docs/8.0.32-27/3-quick-start/3-2-quick-start-with-tarball.html
2. ibd2sql项目README_zh.md https://github.com/ddcw/ibd2sql/blob/main/README_zh.md

Enjoy GreatSQL