点击参与 GreatSQL 8.0.32-26 文档捉虫~~~

GreatSQL社区

搜索

[已解决] GreatSQL 8.0.32-26 二进制包关于 lower_case_table_names 的疑问

180 2 2024-10-3 22:41



安装包下载地址：
  1. wget https://gitee.com/link?target=https%3A%2F%2Fproduct.greatdb.com%2FGreatSQL-8.0.32-26%2FGreatSQL-8.0.32-26-Linux-glibc2.28-x86_64.tar.xz
复制代码




使用my.cnf参数如下：
  1. [client]
  2. port    = 3306
  3. socket  = /data/mysql-data/mysql.sock

  5. [mysql]
  6. prompt = "\u@mysqldb1 \R:\m:\s [\d]> "
  7. no_auto_rehash
  8. loose-skip-binary-as-hex
  9. socket  = /data/mysql-data/mysql.sock

  11. [mysqld]
  12. user    = mysql
  13. port    = 3306
  14. #主从复制或MGR集群中，server_id记得要不同
  15. #另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同
  16. #server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以
  17. #server_id 这里记得修改
  18. server_id = 30137
  19. basedir = /usr/local/GreatSQL-8.0.32-26/
  20. datadir = /data/mysql-data/data
  21. socket  = /data/mysql-data/mysql.sock
  22. pid_file = mysqldb1.pid
  23. character_set_server = UTF8MB4
  24. skip_name_resolve = 1
  25. lc_messages=en_US
  26. lc_messages_dir=/usr/local/GreatSQL-8.0.32-26-Linux-glibc2.28-x86_64/share/english/
  27. #若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
  28. default_time_zone = "+8:00"
  29. #启用admin_port，连接数爆满等紧急情况下给管理员留个后门
  30. admin_address = '127.0.0.1'
  31. admin_port = 33062
  32. #report_host这里记得修改
  33. report_host = 10.1.30.137

  35. #performance setttings
  36. lock_wait_timeout = 3600
  37. open_files_limit    = 65535
  38. back_log = 1024
  39. max_connections = 3000
  40. max_connect_errors = 1000000
  41. table_open_cache = 100000
  42. table_definition_cache = 100000
  43. thread_stack = 512K
  44. sort_buffer_size = 16M
  45. join_buffer_size = 16M
  46. read_buffer_size = 8M
  47. read_rnd_buffer_size = 16M
  48. bulk_insert_buffer_size = 64M
  49. thread_cache_size = 4500
  50. interactive_timeout = 600
  51. wait_timeout = 600
  52. tmp_table_size = 96M
  53. max_heap_table_size = 96M
  54. innodb_numa_interleave = ON
  55. #log settings
  56. log_timestamps = SYSTEM
  57. log_error = /data/mysql-data/log/error.log
  58. log_error_verbosity = 3
  59. slow_query_log = 1
  60. log_slow_extra = 1
  61. slow_query_log_file = /data/mysql-data/log/slow.log
  62. long_query_time = 0.1
  63. log_queries_not_using_indexes = 1
  64. log_throttle_queries_not_using_indexes = 60
  65. min_examined_row_limit = 100
  66. log_slow_admin_statements = 1
  67. log_slow_slave_statements = 1
  68. log_bin = /data/mysql-data/binlogs/mybinlog
  69. binlog_format = ROW
  70. sync_binlog = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
  71. binlog_cache_size = 4M
  72. max_binlog_cache_size = 2G
  73. max_binlog_size = 1G
  74. binlog_rows_query_log_events = 1
  75. binlog_expire_logs_seconds = 604800
  76. #MySQL 8.0.22前，想启用MGR的话，需要设置binlog_checksum=NONE才行
  77. binlog_checksum = CRC32
  78. gtid_mode = ON
  79. enforce_gtid_consistency = TRUE

  81. #myisam settings
  82. key_buffer_size = 32M
  83. myisam_sort_buffer_size = 128M

  85. #replication settings
  86. relay_log_recovery = 1
  87. slave_parallel_type = LOGICAL_CLOCK
  88. slave_parallel_workers = 64 #可以设置为逻辑CPU数量的2倍
  89. binlog_transaction_dependency_tracking = WRITESET
  90. slave_preserve_commit_order = 1
  91. slave_checkpoint_period = 2
  92. replication_optimize_for_static_plugin_config = ON
  93. replication_sender_observe_commit_only = ON

  95. #loss-less semi-sync replication settings
  96. skip_slave_start
  97. #同时启用半同步复制的master和slave plugin
  98. #semi-sync(master)
  99. loose-rpl_semi_sync_master_timeout = 99999999
  100. loose-plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  101. loose-rpl_semi_sync_master_enabled = 1
  102. loose-rpl_semi_sync_master_wait_for_slave_count = 1
  103. loose-rpl_semi_sync_master_wait_point = AFTER_SYNC
  104. #semi-sync(slave)
  105. loose-rpl_semi_sync_slave_enabled = 1

  107. #如果是slave节点，强烈建议设置为read_only模式，避免误操作写入数据
  108. #如果是master节点，则不要设置为read_only模式
  109. #read_only = 1
  110. #super_read_only = 1

  112. #innodb settings
  113. transaction_isolation = REPEATABLE-READ
  114. innodb_buffer_pool_size = 22528M
  115. innodb_buffer_pool_instances = 4
  116. innodb_data_file_path = ibdata1:12M:autoextend
  117. innodb_flush_log_at_trx_commit = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
  118. innodb_log_buffer_size = 32M
  119. innodb_log_file_size = 4G #如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小
  120. innodb_log_files_in_group = 3
  121. loose-innodb_redo_log_capacity = 3G
  122. innodb_max_undo_log_size = 4G
  123. # 根据您的服务器IOPS能力适当调整
  124. # 一般配普通SSD盘的话，可以调整到 10000 - 20000
  125. # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
  126. innodb_io_capacity = 50000
  127. innodb_io_capacity_max = 80000
  128. innodb_open_files = 65535
  129. innodb_flush_method = O_DIRECT
  130. innodb_lru_scan_depth = 4000
  131. innodb_lock_wait_timeout = 10
  132. innodb_rollback_on_timeout = 1
  133. innodb_print_all_deadlocks = 1
  134. innodb_online_alter_log_max_size = 4G
  135. innodb_print_ddl_logs = 1
  136. innodb_status_file = 1
  137. #注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快
  138. innodb_status_output = 0
  139. innodb_status_output_locks = 1
  140. innodb_sort_buffer_size = 67108864
  141. innodb_adaptive_hash_index = OFF
  142. #提高索引统计信息精确度
  143. innodb_stats_persistent_sample_pages = 500
  144. innodb_adaptive_hash_index = 0
  145. loose-sql_generate_invisible_primary_key = ON

  147. #innodb monitor settings
  148. innodb_monitor_enable = "module_innodb"
  149. innodb_monitor_enable = "module_server"
  150. innodb_monitor_enable = "module_dml"
  151. innodb_monitor_enable = "module_ddl"
  152. innodb_monitor_enable = "module_trx"
  153. innodb_monitor_enable = "module_os"
  154. innodb_monitor_enable = "module_purge"
  155. innodb_monitor_enable = "module_log"
  156. innodb_monitor_enable = "module_lock"
  157. innodb_monitor_enable = "module_buffer"
  158. innodb_monitor_enable = "module_index"
  159. innodb_monitor_enable = "module_ibuf_system"
  160. innodb_monitor_enable = "module_buffer_page"
  161. #innodb_monitor_enable = "module_adaptive_hash"

  163. #pfs settings
  164. performance_schema = 1
  165. #performance_schema_instrument = '%memory%=on'
  166. loose-performance_schema_instrument = '%lock%=on'

  168. [mysqldump]
  169. quick
复制代码


启动后查询大小写敏感参数：

  1. root@mysqldb1 22:34:  [(none)]> show variables like '%case%';
  2. +------------------------+-------+
  3. | Variable_name          | Value |
  4. +------------------------+-------+
  5. | lower_case_file_system | OFF   |
  6. | lower_case_table_names | 0     |
  7. +------------------------+-------+
  8. 2 rows in set (0.01 sec)

复制代码



疑问： linux上  lower_case_table_names  参数应该默认不配置的情况下要是1 的吧？为什么greatsql 是0 呢？


找了一套mysql8.0.35的作对比：


  1. (Thu Oct  3 22:40:03 2024)[itadmin@MySQL][(none)]>show variables like '%case%';
  2. +------------------------------------+-------+
  3. | Variable_name                      | Value |
  4. +------------------------------------+-------+
  5. | lower_case_file_system             | OFF   |
  6. | lower_case_table_names             | 1     |
  7. | validate_password.mixed_case_count | 1     |
  8. +------------------------------------+-------+
  9. 3 rows in set (0.33 sec)
复制代码




GreatSQL, MySQL
全部回复(2)
只看楼主
yejr 2024-10-4 23:08:02
MySQL 8.0.x默认也是0的，详见

https://dev.mysql.com/doc/refman ... er_case_table_names
回复 采纳

举报

起飞小宇 2024-10-5 22:19:46
yejr 发表于 2024-10-4 23:08
MySQL 8.0.x默认也是0的，详见

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html# ...

感谢叶老师的解答
回复 采纳

举报

起飞小宇

9

主题

0

博客

42

贡献

注册会员

Rank: 2

积分
72

助人为乐（铜）

发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-11-21 21:02 , Processed in 0.024483 second(s), 13 queries , Redis On.
快速回复 返回顶部 返回列表