本篇內(nèi)容介紹了“怎么理解TiDB兼容MySQL參數(shù)優(yōu)化”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
默認(rèn) SQL mode 與 MySQL 已相同
TiDB 和 MySQL 5.7 中均為 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
當(dāng)前：
mysql> SELECT @@sql_mode
-> ;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
SET [ SESSION | GLOBAL ] sql_mode='modes'`;
SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
tidb
設(shè)置大連接：
set global max_user_connections=200;
mysql> show variables like '%conn%';
+-----------------------------------------------+-----------------+
| Variable_name | Value |
+-----------------------------------------------+-----------------+
| performance_schema_session_connect_attrs_size | 512 |
| disconnect_on_expired_password | 1 |
| collation_connection | utf8_general_ci |
| character_set_connection | utf8 |
| connect_timeout | 10 |
| max_user_connections | 0 |
| max_connections | 151 |
| init_connect | |
| max_connect_errors | 100 |
+-----------------------------------------------+-----------------+
9 rows in set (0.01 sec)
mysql>
mysql> show variables like '%log%';
+------------------------------------------+------------------------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------------------------+
| relay_log_space_limit | 0 |
| ndb_table_no_logging | |
| relay_log_recovery | 0 |
| log_bin | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_log_buffer_size | 8388608 |
| log_slave_updates | 0 |
| log_error | /usr/local/mysql/data/localhost.err |
| log_bin_use_v1_row_events | 0 |
| innodb_log_files_in_group | 2 |
| tidb_slow_log_threshold | 300 |
| binlog_gtid_simple_recovery | 1 |
| relay_log_info_file | relay-log.info |
| back_log | 80 |
| tidb_general_log | 0 |
| innodb_log_file_size | 50331648 |
| innodb_api_enable_binlog | 0 |
| innodb_log_group_home_dir | ./ |
| tidb_query_log_max_len | 2048 |
| log_output | FILE |
| innodb_locks_unsafe_for_binlog | 0 |
| relay_log_info_repository | FILE |
| innodb_undo_logs | 128 |
| binlog_checksum | CRC32 |
| innodb_flush_log_at_timeout | 1 |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
| binlog_direct_non_transactional_updates | OFF |
| innodb_online_alter_log_max_size | 134217728 |
| log_timestamps | |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| innodb_log_compressed_pages | ON |
| binlog_format | STATEMENT |
| innodb_max_undo_log_size | |
| log_queries_not_using_indexes | OFF |
| relay_log_purge | ON |
| binlog_cache_size | 32768 |
| innodb_log_checksum_algorithm | |
| ndb_log_binlog_index | |
| max_binlog_cache_size | 18446744073709547520 |
| binlog_max_flush_queue_time | 0 |
| binlog_rows_query_log_events | OFF |
| slow_query_log | OFF |
| general_log | 0 |
| max_binlog_size | 1073741824 |
| binlog_stmt_cache_size | 32768 |
| sql_log_bin | 1 |
| general_log_file | /usr/local/mysql/data/localhost.log |
| log_slow_slave_statements | OFF |
| sql_log_off | OFF |
| innodb_log_write_ahead_size | |
| log_backward_compatible_user_definitions | |
| log_syslog | |
| sync_binlog | 0 |
| binlog_group_commit_sync_delay | |
| log_syslog_include_pid | |
| binlog_order_commits | ON |
| innodb_undo_log_truncate | |
| sync_relay_log_info | 10000 |
| log_error_verbosity | |
| log_warnings | 1 |
| log_syslog_facility | |
| binlogging_impossible_mode | IGNORE_ERROR |
| ndb_log_updated_only | |
| ndb_log_empty_epochs | |
| binlog_group_commit_sync_no_delay_count | |
| innodb_flush_log_at_trx_commit | 1 |
| log_bin_trust_function_creators | OFF |
| binlog_error_action | IGNORE_ERROR |
| log_throttle_queries_not_using_indexes | 0 |
| max_relay_log_size | 0 |
| binlog_row_image | FULL |
| sync_relay_log | 10000 |
| log_slow_admin_statements | OFF |
| log_syslog_tag | |
| expire_logs_days | 0 |
+------------------------------------------+------------------------------------------+
75 rows in set (0.01 sec)
mysql>
set global max_connections=1000; #這個參數(shù)需要重啟db
設(shè)置后需要重啟
SET GLOBAL binlog_format = 'ROW'; #這個參數(shù)動態(tài)修改
重啟服務(wù)：
停止tidb服務(wù)：
ansible-playbook stop.yml --tags=tidb
啟動tidb服務(wù)：
ansible-playbook start.yml --tags=tidb
“怎么理解TiDB兼容MySQL參數(shù)優(yōu)化”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)-成都網(wǎng)站建設(shè)公司網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！
重慶分公司
重慶分公司
