GreatSQL社区

搜索

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

197 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

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

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

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

  77. #myisam settings
  78. key_buffer_size = 32M
  79. myisam_sort_buffer_size = 128M

  80. #replication settings
  81. relay_log_recovery = 1
  82. slave_parallel_type = LOGICAL_CLOCK
  83. slave_parallel_workers = 64 #可以设置为逻辑CPU数量的2倍
  84. binlog_transaction_dependency_tracking = WRITESET
  85. slave_preserve_commit_order = 1
  86. slave_checkpoint_period = 2
  87. replication_optimize_for_static_plugin_config = ON
  88. replication_sender_observe_commit_only = ON

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

  100. #如果是slave节点,强烈建议设置为read_only模式,避免误操作写入数据
  101. #如果是master节点,则不要设置为read_only模式
  102. #read_only = 1
  103. #super_read_only = 1

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

  138. #innodb monitor settings
  139. innodb_monitor_enable = "module_innodb"
  140. innodb_monitor_enable = "module_server"
  141. innodb_monitor_enable = "module_dml"
  142. innodb_monitor_enable = "module_ddl"
  143. innodb_monitor_enable = "module_trx"
  144. innodb_monitor_enable = "module_os"
  145. innodb_monitor_enable = "module_purge"
  146. innodb_monitor_enable = "module_log"
  147. innodb_monitor_enable = "module_lock"
  148. innodb_monitor_enable = "module_buffer"
  149. innodb_monitor_enable = "module_index"
  150. innodb_monitor_enable = "module_ibuf_system"
  151. innodb_monitor_enable = "module_buffer_page"
  152. #innodb_monitor_enable = "module_adaptive_hash"

  153. #pfs settings
  154. performance_schema = 1
  155. #performance_schema_instrument = '%memory%=on'
  156. loose-performance_schema_instrument = '%lock%=on'

  157. [mysqldump]
  158. 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)
复制代码




全部回复(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群
GMT+8, 2024-12-4 19:30 , Processed in 0.024829 second(s), 15 queries , Redis On.
快速回复 返回顶部 返回列表