数据库系统原理与应用教程（019）—— MySQL 的配置文件

目录

数据库系统原理与应用教程（019）—— MySQL 的配置文件一、MySQL 配置文件二、查看 MySQL 服务器参数的当前值（按字母顺序排列）1、查看以字母 a、b、c 开头的参数2、查看以字母 d、e、f 开头的参数3、查看以字母 g、h 开头的参数4、查看以字母 i 开头的参数5、查看以字母 j、k、l 开头的参数6、查看以字母 m、n 开头的参数7、查看以字母 o 开头的参数8、查看以字母 p、q、r 开头的参数9、查看以字母 s、t、u 开头的参数10、查看以字母 v、w、x 、y、z 开头的参数

MySQL 服务器启动时会读取配置文件，可以通过修改配置文件，达到更新配置的目的。如果修改了配置文件，必须重启 MySQL 服务才能使配置生效。

MySQL 读取配置文件的顺序如下。如果服务器中有多个配置文件配置了同一个参数，则以最后一个读取到的参数为准。

[root@mysql mysql]# mysql --help|grep forder of preference, f, $MYSQL_TCP_PORT,/etc/f /etc/mysql/f /usr/local/mysql/etc/f ~/.f

配置文件中中括号括起来的单词为【组】，例如：[client]、[mysqld] 等都是组。其中 [client] 表示对客户端的配置，mysqld] 表示对服务器的配置。

一、MySQL 配置文件

[client] # 配置客户端参数port = 3306 # 设置 MySQL 接端口号socket = /tmp/mysql.sock # 配置 socket 文件的文件名和地址default-character-set = utf8mb4 # 设置客户端字符集编码[mysql]auto-rehash # auto-rehash：自动补全[mysqld] # 配置服务器端参数port = 3306 # 设置 MySQL 接端口号socket = /tmp/mysql.sock # 配置 socket 文件的文件名和地址pid-file = /data/mysql/mysql.pid # 配置 pid 文件的文件名和地址basedir = /usr/local/mysql # 使用该目录作为根目录（安装目录）datadir = /usr/local/mysql/data/ # 数据文件存放的目录tmpdir = /home/mysql/tmp # MySQL 存放临时文件的目录character_set_server = utf8mb4# 服务端默认编码（数据库级别）collation_server = utf8mb4_bin# 服务端默认的比对规则，排序规则user = mysql # MySQL 用户bind-address = 0.0.0.0 # 绑定某个 IP 地址，如果服务器设置了多个 IP 需要配置server-id = 1 # 服务器编号，一般设置为 IP 地址的最后一部分skip-name-resolve# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，# 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求#skip-networkingback_log = 600# MySQL 的连接数量。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，# 以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。max_connections = 1000# MySQL 的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量。max_connect_errors = 6000# 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。open_files_limit = 65535# MySQL打开的文件描述符限制，默认最小1024;# 当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个；# 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。table_open_cache = 128# MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，# 才会去磁盘上读取。默认值64。max_allowed_packet = 4M # 接受的数据包大小binlog_cache_size = 1M# 事务在没有提交的时候，产生的日志记录到Cache中；等事务需要提交时，则把日志持久化到磁盘。默认大小32Kmax_heap_table_size = 8M # 定义了用户可以创建的内存表(memory table)的大小tmp_table_size = 16M# MySQL的临时表大小。可以通过设置tmp_table_size选项来增加临时表的大小。read_buffer_size = 2M # MySQL读入缓冲区大小read_rnd_buffer_size = 8M# MySQL的随机读缓冲区大小sort_buffer_size = 8M # MySQL执行排序使用的缓冲大小join_buffer_size = 8M # 联合查询操作所能使用的缓冲区大小thread_cache_size = 8# （默认8）表示可以重新利用保存在缓存中线程的数量# 当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，# 那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，# 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。# 根据物理内存设置规则如下：# 1G —> 8# 2G —> 16# 3G —> 32# 大于3G —> 64query_cache_size = 8M# MySQL的查询缓冲大小，MySQL将SELECT语句和查询结果存放在缓冲区中，以后对于同样的SELECT语句（区分大小写），# 将直接从缓冲区中读取结果。query_cache_limit = 2M # 指定单个查询能够使用的缓冲区大小，默认1Mkey_buffer_size = 4M# 指定用于索引的缓冲区大小# 增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，# 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads，# 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，# 至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意：该参数值设置的过大反而会是服务器整体效率降低ft_min_word_len = 4 # 分词词汇最小长度，默认4transaction_isolation = REPEATABLE-READ# MySQL支持4种事务隔离级别，他们分别是：# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.# 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTEDlog_bin = mysql-binbinlog_format = mixedexpire_logs_days = 30 # 超过30天的binlog删除log_error = /home/mysql/mysql-error.log # 错误日志路径slow_query_log = 1long_query_time = 1 # 慢查询时间 超过1秒则为慢查询slow_query_log_file = /data/mysql/mysql-slow.logperformance_schema = 0explicit_defaults_for_timestamplower_case_table_names = 1 # 表名不区分大小写skip-external-locking # MySQL 选项以避免外部锁定。该选项默认开启default-storage-engine = InnoDB # 默认存储引擎innodb_file_per_table = 1# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间# 独立表空间优点：# 1．每个表都有自已独立的表空间。# 2．每个表的数据和索引都会存在自已的表空间中。# 3．可以实现单表在不同的数据库中移动。# 4．空间可以回收（除drop table操作处，表空不能自已回收）# 缺点：单表增加过大，如超过100G# 结论：当启用独立表空间时，请合理调整：innodb_open_filesinnodb_open_files = 500# 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300innodb_buffer_pool_size = 64M# InnoDB 使用一个缓冲池来保存索引和原始数据，设置越大，在存取表中数据时所需要的磁盘I/O越少# 在一个独立使用的数据库服务器上，可以设置这个变量到服务器物理内存大小的 80%innodb_write_io_threads = 4innodb_read_io_threads = 4# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4# 注:这两个参数不支持动态改变,需要把该参数加入到f里，修改完后重启MySQL服务,允许值的范围从 1-64innodb_thread_concurrency = 0# 默认设置为 0,表示不限制并发数，推荐设置为0，更好去发挥CPU多核处理能力，提高并发量innodb_purge_threads = 1# 默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1innodb_flush_log_at_trx_commit = 2# 0：log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1# 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。# 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。# 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘# 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。# 设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。# 总结# 设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能innodb_log_buffer_size = 2M# 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。# MySQL 建议设置为1-8Minnodb_log_file_size = 32M # 此参数确定数据日志文件的大小innodb_log_files_in_group = 3 # 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3innodb_max_dirty_pages_pct = 90 # innodb主线程刷新缓存池中的数据，使脏数据比例小于90%innodb_lock_wait_timeout = 120 # InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。默认值是50秒bulk_insert_buffer_size = 8M# 批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的myisam_sort_buffer_size = 8M# MyISAM 设置恢复表时使用的缓冲区的尺寸myisam_max_sort_file_size = 10Gmyisam_repair_threads = 1# 如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引interactive_timeout = 28800# 服务器关闭交互式连接前等待活动的秒数。默认值：28800秒（8小时）wait_timeout = 28800 # 服务器关闭非交互连接之前等待活动的秒数[mysqldump]quick# 强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中max_allowed_packet = 512M # 限制 server 接受的数据包大小net_buffer_length = 16384 # TCP/IP和套接字通信缓冲区大小[isamchk] # isamchk 数据检测恢复工具key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[myisamchk] # myisamchk 实用程序key_buffer_size = 8Msort_buffer_size = 8Mread_buffer = 4Mwrite_buffer = 4M[mysqlhotcopy]interactive-timeout # mysqlhotcopy 使用lock tables、flush tables 和 cp 或 scp 来快速备份数据库# 是备份数据库或单个表最快的途径，属于物理备份，只能用于备份 MyISAM 存储引擎和运行在数据库目录所在的机器上# 与 mysqldump 备份不同，mysqldump 属于逻辑备份，备份时执行 sql 语句，# 使用 mysqlhotcopy 命令前需要要安装相应的软件依赖包.

二、查看 MySQL 服务器参数的当前值（按字母顺序排列）

1、查看以字母 a、b、c 开头的参数

mysql> show variables like 'a%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1|| auto_increment_offset | 1|| autocommit| ON || automatic_sp_privileges | ON || avoid_temporal_upgrade | OFF |+--------------------------+-------+5 rows in set (0.07 sec)mysql> show variables like 'b%';+--------------------------------------------+-------------------+| Variable_name| Value |+--------------------------------------------+-------------------+| back_log| 80|| basedir| /usr/local/mysql/ || big_tables | OFF|| bind_address | * || binlog_cache_size| 32768 || binlog_checksum | CRC32 || binlog_direct_non_transactional_updates | OFF|| binlog_error_action | ABORT_SERVER|| binlog_format| ROW|| binlog_group_commit_sync_delay | 0 || binlog_group_commit_sync_no_delay_count | 0 || binlog_gtid_simple_recovery| ON|| binlog_max_flush_queue_time| 0 || binlog_order_commits | ON|| binlog_row_image | FULL || binlog_rows_query_log_events| OFF|| binlog_stmt_cache_size | 32768 || binlog_transaction_dependency_history_size | 25000 || binlog_transaction_dependency_tracking| COMMIT_ORDER|| block_encryption_mode | aes-128-ecb || bulk_insert_buffer_size| 8388608 |+--------------------------------------------+-------------------+21 rows in set (0.00 sec)mysql> show variables like 'c%';+--------------------------+----------------------------------+| Variable_name | Value |+--------------------------+----------------------------------+| character_set_client| utf8 || character_set_connection | utf8 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server| utf8 || character_set_system| utf8 || character_sets_dir | /usr/local/mysql/share/charsets/ || check_proxy_users | OFF|| collation_connection| utf8_general_ci || collation_database | utf8_general_ci || collation_server | utf8_general_ci || completion_type| NO_CHAIN|| concurrent_insert | AUTO || connect_timeout| 10 || core_file| OFF|+--------------------------+----------------------------------+16 rows in set (0.00 sec)

2、查看以字母 d、e、f 开头的参数

mysql> show variables like 'd%';+--------------------------------+------------------------+| Variable_name | Value |+--------------------------------+------------------------+| datadir | /usr/local/mysql/data/ || date_format| %Y-%m-%d|| datetime_format| %Y-%m-%d %H:%i:%s|| default_authentication_plugin | mysql_native_password || default_password_lifetime| 0 || default_storage_engine | InnoDB || default_tmp_storage_engine| InnoDB || default_week_format | 0 || delay_key_write| ON || delayed_insert_limit | 100|| delayed_insert_timeout | 300|| delayed_queue_size | 1000 || disabled_storage_engines | || disconnect_on_expired_password | ON || div_precision_increment | 4 |+--------------------------------+------------------------+15 rows in set (0.00 sec)mysql> show variables like 'e%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| end_markers_in_json | OFF || enforce_gtid_consistency | OFF || eq_range_index_dive_limit | 200 || error_count | 0|| event_scheduler | OFF || expire_logs_days| 0|| explicit_defaults_for_timestamp | OFF || external_user | |+---------------------------------+-------+8 rows in set (0.00 sec)mysql> show variables like 'f%';+--------------------------+----------------+| Variable_name | Value|+--------------------------+----------------+| flush| OFF || flush_time| 0 || foreign_key_checks | ON || ft_boolean_syntax | + -><()~*:""&| || ft_max_word_len| 84 || ft_min_word_len| 4 || ft_query_expansion_limit | 20 || ft_stopword_file | (built-in)|+--------------------------+----------------+8 rows in set (0.00 sec)

3、查看以字母 g、h 开头的参数

mysql> show variables like 'g%';+----------------------------------+---------------------------------+| Variable_name| Value |+----------------------------------+---------------------------------+| general_log | OFF || general_log_file | /usr/local/mysql/data/mysql.log || group_concat_max_len | 1024 || gtid_executed_compression_period | 1000 || gtid_mode | OFF || gtid_next | AUTOMATIC || gtid_owned | || gtid_purged | |+----------------------------------+---------------------------------+8 rows in set (0.00 sec)mysql> show variables like 'h%';+------------------------+----------+| Variable_name| Value |+------------------------+----------+| have_compress| YES|| have_crypt | YES|| have_dynamic_loading | YES|| have_geometry| YES|| have_openssl | DISABLED || have_profiling | YES|| have_query_cache | YES|| have_rtree_keys | YES|| have_ssl| DISABLED || have_statement_timeout | YES|| have_symlink | YES|| host_cache_size | 279|| hostname| mysql |+------------------------+----------+13 rows in set (0.00 sec)

4、查看以字母 i 开头的参数

mysql> show variables like 'i%';+------------------------------------------+------------------------+| Variable_name | Value |+------------------------------------------+------------------------+| identity | 0 || ignore_builtin_innodb| OFF|| ignore_db_dirs | || init_connect | || init_file | || init_slave | || innodb_adaptive_flushing | ON || innodb_adaptive_flushing_lwm | 10 || innodb_adaptive_hash_index| ON || innodb_adaptive_hash_index_parts | 8 || innodb_adaptive_max_sleep_delay| 150000 || innodb_api_bk_commit_interval | 5 || innodb_api_disable_rowlock| OFF|| innodb_api_enable_binlog | OFF|| innodb_api_enable_mdl| OFF|| innodb_api_trx_level | 0 || innodb_autoextend_increment | 64 || innodb_autoinc_lock_mode | 1 || innodb_buffer_pool_chunk_size | 134217728 || innodb_buffer_pool_dump_at_shutdown| ON || innodb_buffer_pool_dump_now | OFF|| innodb_buffer_pool_dump_pct | 25 || innodb_buffer_pool_filename | ib_buffer_pool || innodb_buffer_pool_instances | 1 || innodb_buffer_pool_load_abort | OFF|| innodb_buffer_pool_load_at_startup | ON || innodb_buffer_pool_load_now | OFF|| innodb_buffer_pool_size | 134217728 || innodb_change_buffer_max_size | 25 || innodb_change_buffering | all|| innodb_checksum_algorithm| crc32 || innodb_checksums| ON || innodb_cmp_per_index_enabled | OFF|| innodb_commit_concurrency| 0 || innodb_compression_failure_threshold_pct | 5 || innodb_compression_level | 6 || innodb_compression_pad_pct_max | 50 || innodb_concurrency_tickets| 5000 || innodb_data_file_path| ibdata1:12M:autoextend || innodb_data_home_dir | || innodb_deadlock_detect | ON || innodb_default_row_format| dynamic|| innodb_disable_sort_file_cache | OFF|| innodb_doublewrite | ON || innodb_fast_shutdown | 1 || innodb_file_format | Barracuda || innodb_file_format_check | ON || innodb_file_format_max | Barracuda || innodb_file_per_table| ON || innodb_fill_factor | 100|| innodb_flush_log_at_timeout | 1 || innodb_flush_log_at_trx_commit | 1 || innodb_flush_method | || innodb_flush_neighbors | 1 || innodb_flush_sync | ON || innodb_flushing_avg_loops| 30 || innodb_force_load_corrupted | OFF|| innodb_force_recovery| 0 || innodb_ft_aux_table | || innodb_ft_cache_size | 8000000|| innodb_ft_enable_diag_print | OFF|| innodb_ft_enable_stopword| ON || innodb_ft_max_token_size | 84 || innodb_ft_min_token_size | 3 || innodb_ft_num_word_optimize | 2000 || innodb_ft_result_cache_limit | 2000000000 || innodb_ft_server_stopword_table| || innodb_ft_sort_pll_degree| 2 || innodb_ft_total_cache_size| 640000000 || innodb_ft_user_stopword_table | || innodb_io_capacity | 200|| innodb_io_capacity_max | 2000 || innodb_large_prefix | ON || innodb_lock_wait_timeout | 50 || innodb_locks_unsafe_for_binlog | OFF|| innodb_log_buffer_size | 16777216|| innodb_log_checksums | ON || innodb_log_compressed_pages | ON || innodb_log_file_size | 50331648|| innodb_log_files_in_group| 2 || innodb_log_group_home_dir| ./ || innodb_log_write_ahead_size | 8192 || innodb_lru_scan_depth| 1024 || innodb_max_dirty_pages_pct| 75.000000 || innodb_max_dirty_pages_pct_lwm | 0.000000|| innodb_max_purge_lag | 0 || innodb_max_purge_lag_delay| 0 || innodb_max_undo_log_size | 1073741824 || innodb_monitor_disable | || innodb_monitor_enable| || innodb_monitor_reset | || innodb_monitor_reset_all | || innodb_old_blocks_pct| 37 || innodb_old_blocks_time | 1000 || innodb_online_alter_log_max_size | 134217728 || innodb_open_files | 2000 || innodb_optimize_fulltext_only | OFF|| innodb_page_cleaners | 1 || innodb_page_size| 16384 || innodb_print_all_deadlocks| OFF|| innodb_purge_batch_size | 300|| innodb_purge_rseg_truncate_frequency| 128|| innodb_purge_threads | 4 || innodb_random_read_ahead | OFF|| innodb_read_ahead_threshold | 56 || innodb_read_io_threads | 4 || innodb_read_only| OFF|| innodb_replication_delay | 0 || innodb_rollback_on_timeout| OFF|| innodb_rollback_segments | 128|| innodb_sort_buffer_size | 1048576|| innodb_spin_wait_delay | 6 || innodb_stats_auto_recalc | ON || innodb_stats_include_delete_marked | OFF|| innodb_stats_method | nulls_equal || innodb_stats_on_metadata | OFF|| innodb_stats_persistent | ON || innodb_stats_persistent_sample_pages| 20 || innodb_stats_sample_pages| 8 || innodb_stats_transient_sample_pages| 8 || innodb_status_output | OFF|| innodb_status_output_locks| OFF|| innodb_strict_mode | ON || innodb_support_xa | ON || innodb_sync_array_size | 1 || innodb_sync_spin_loops | 30 || innodb_table_locks | ON || innodb_temp_data_file_path| ibtmp1:12M:autoextend || innodb_thread_concurrency| 0 || innodb_thread_sleep_delay| 10000 || innodb_tmpdir | || innodb_undo_directory| ./ || innodb_undo_log_truncate | OFF|| innodb_undo_logs| 128|| innodb_undo_tablespaces | 0 || innodb_use_native_aio| OFF|| innodb_version | 5.7.25 || innodb_write_io_threads | 4 || insert_id | 0 || interactive_timeout | 28800 || internal_tmp_disk_storage_engine | InnoDB |+------------------------------------------+------------------------+141 rows in set (0.00 sec)

5、查看以字母 j、k、l 开头的参数

mysql> show variables like 'j%';+------------------+--------+| Variable_name | Value |+------------------+--------+| join_buffer_size | 262144 |+------------------+--------+1 row in set (0.00 sec)mysql> show variables like 'k%';+--------------------------+---------+| Variable_name | Value |+--------------------------+---------+| keep_files_on_create| OFF|| key_buffer_size| 8388608 || key_cache_age_threshold | 300|| key_cache_block_size| 1024 || key_cache_division_limit | 100|| keyring_operations | ON|+--------------------------+---------+6 rows in set (0.00 sec)mysql> show variables like 'l%';+----------------------------------------+----------------------------------+| Variable_name| Value |+----------------------------------------+----------------------------------+| large_files_support| ON || large_page_size | 0 || large_pages | OFF|| last_insert_id| 0 || lc_messages | en_US || lc_messages_dir | /usr/local/mysql/share/|| lc_time_names| en_US || license | GPL|| local_infile | ON || lock_wait_timeout | 31536000|| locked_in_memory | OFF|| log_bin | OFF|| log_bin_basename | || log_bin_index| || log_bin_trust_function_creators | OFF|| log_bin_use_v1_row_events | OFF|| log_builtin_as_identified_by_password | OFF|| log_error| /usr/local/mysql/mysql-error.log || log_error_verbosity| 3 || log_output | FILE || log_queries_not_using_indexes| OFF|| log_slave_updates | OFF|| log_slow_admin_statements | OFF|| log_slow_slave_statements | OFF|| log_statements_unsafe_for_binlog | ON || log_syslog | OFF|| log_syslog_facility| daemon || log_syslog_include_pid | ON || log_syslog_tag| || log_throttle_queries_not_using_indexes | 0 || log_timestamps| UTC|| log_warnings | 2 || long_query_time | 10.000000 || low_priority_updates | OFF|| lower_case_file_system | OFF|| lower_case_table_names | 0 |+----------------------------------------+----------------------------------+36 rows in set (0.00 sec)

6、查看以字母 m、n 开头的参数

mysql> show variables like 'm%';+-----------------------------------+----------------------+| Variable_name | Value|+-----------------------------------+----------------------+| master_info_repository | FILE || master_verify_checksum | OFF || max_allowed_packet| 4194304 || max_binlog_cache_size | 18446744073709547520 || max_binlog_size | 1073741824 || max_binlog_stmt_cache_size | 18446744073709547520 || max_connect_errors| 100 || max_connections | 151 || max_delayed_threads| 20 || max_digest_length | 1024 || max_error_count | 64 || max_execution_time| 0|| max_heap_table_size| 16777216 || max_insert_delayed_threads | 20 || max_join_size | 18446744073709551615 || max_length_for_sort_data| 1024 || max_points_in_geometry | 65536|| max_prepared_stmt_count | 16382|| max_relay_log_size| 0|| max_seeks_for_key | 18446744073709551615 || max_sort_length | 1024 || max_sp_recursion_depth | 0|| max_tmp_tables| 32 || max_user_connections | 0|| max_write_lock_count | 18446744073709551615 || metadata_locks_cache_size | 1024 || metadata_locks_hash_instances| 8|| min_examined_row_limit | 0|| multi_range_count | 256 || myisam_data_pointer_size| 6|| myisam_max_sort_file_size | 9223372036853727232 || myisam_mmap_size | 18446744073709551615 || myisam_recover_options | OFF || myisam_repair_threads | 1|| myisam_sort_buffer_size | 8388608 || myisam_stats_method| nulls_unequal || myisam_use_mmap | OFF || mysql_native_password_proxy_users | OFF |+-----------------------------------+----------------------+38 rows in set (0.00 sec)mysql> show variables like 'n%';+-------------------+-------+| Variable_name| Value |+-------------------+-------+| net_buffer_length | 16384 || net_read_timeout | 30 || net_retry_count | 10 || net_write_timeout | 60 || new| OFF || ngram_token_size | 2|+-------------------+-------+6 rows in set (0.00 sec)

7、查看以字母 o 开头的参数

mysql> show variables like 'o%';+------------------------------+------------------------------------------------| Variable_name| Value | offline_mode | OFF| old| OFF| old_alter_table | OFF| old_passwords| 0| open_files_limit | 5000 | optimizer_prune_level | 1 | optimizer_search_depth | 62 | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on| optimizer_trace | enabled=off,one_line=off| optimizer_trace_features| greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on| optimizer_trace_limit | 1| optimizer_trace_max_mem_size | 16384| optimizer_trace_offset | -1+13 rows in set (0.00 sec)

8、查看以字母 p、q、r 开头的参数

mysql> show variables like 'p%';+----------------------------------------------------------+------------------------------+| Variable_name | Value |+----------------------------------------------------------+------------------------------+| parser_max_mem_size | 18446744073709551615 || performance_schema | ON || performance_schema_accounts_size| -1 || performance_schema_digests_size| 10000 || performance_schema_events_stages_history_long_size | 10000 || performance_schema_events_stages_history_size | 10 || performance_schema_events_statements_history_long_size | 10000 || performance_schema_events_statements_history_size | 10 || performance_schema_events_transactions_history_long_size | 10000 || performance_schema_events_transactions_history_size| 10 || performance_schema_events_waits_history_long_size | 10000 || performance_schema_events_waits_history_size | 10 || performance_schema_hosts_size | -1 || performance_schema_max_cond_classes | 80 || performance_schema_max_cond_instances| -1 || performance_schema_max_digest_length | 1024|| performance_schema_max_file_classes | 80 || performance_schema_max_file_handles | 32768 || performance_schema_max_file_instances| -1 || performance_schema_max_index_stat | -1 || performance_schema_max_memory_classes| 320|| performance_schema_max_metadata_locks| -1 || performance_schema_max_mutex_classes | 210|| performance_schema_max_mutex_instances | -1 || performance_schema_max_prepared_statements_instances| -1 || performance_schema_max_program_instances | -1 || performance_schema_max_rwlock_classes| 50 || performance_schema_max_rwlock_instances | -1 || performance_schema_max_socket_classes| 10 || performance_schema_max_socket_instances | -1 || performance_schema_max_sql_text_length | 1024|| performance_schema_max_stage_classes | 150|| performance_schema_max_statement_classes | 193|| performance_schema_max_statement_stack | 10 || performance_schema_max_table_handles | -1 || performance_schema_max_table_instances | -1 || performance_schema_max_table_lock_stat | -1 || performance_schema_max_thread_classes| 50 || performance_schema_max_thread_instances | -1 || performance_schema_session_connect_attrs_size | 512|| performance_schema_setup_actors_size | -1 || performance_schema_setup_objects_size| -1 || performance_schema_users_size | -1 || pid_file | /usr/local/mysql/mysql.pid || plugin_dir | /usr/local/mysql/lib/plugin/ || port | 3306|| preload_buffer_size | 32768 || profiling | OFF|| profiling_history_size| 15 || protocol_version | 10 || proxy_user ||| pseudo_slave_mode| OFF|| pseudo_thread_id | 6 |+----------------------------------------------------------+------------------------------+53 rows in set (0.00 sec)mysql> show variables like 'q%';+------------------------------+---------+| Variable_name| Value |+------------------------------+---------+| query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit| 4096 || query_cache_size | 1048576 || query_cache_type | OFF|| query_cache_wlock_invalidate | OFF|| query_prealloc_size| 8192 |+------------------------------+---------+7 rows in set (0.00 sec)mysql> show variables like 'r%';+------------------------------+---------------------------------------------+| Variable_name| Value |+------------------------------+---------------------------------------------+| rand_seed1 | 0 || rand_seed2 | 0 || range_alloc_block_size | 4096|| range_optimizer_max_mem_size | 8388608 || rbr_exec_mode| STRICT || read_buffer_size | 131072 || read_only| OFF || read_rnd_buffer_size | 262144 || relay_log||| relay_log_basename | /usr/local/mysql/data/mysql-relay-bin || relay_log_index | /usr/local/mysql/data/mysql-relay-bin.index || relay_log_info_file| relay-log.info|| relay_log_info_repository | FILE|| relay_log_purge | ON || relay_log_recovery | OFF || relay_log_space_limit | 0 || report_host ||| report_password ||| report_port | 3306|| report_user ||| require_secure_transport| OFF || rpl_stop_slave_timeout | 31536000|+------------------------------+---------------------------------------------+22 rows in set (0.00 sec)

9、查看以字母 s、t、u 开头的参数

mysql> show variables like 's%';+--------------------------------+-------------------------------------+| Variable_name | Value|+--------------------------------+-------------------------------------| secure_auth| ON| secure_file_priv| NULL | server_id | 0| server_id_bits | 32| server_uuid| 19bb8d85-f8cb-11ec-bdeb-000c2913d893 | session_track_gtids | OFF | session_track_schema | ON| session_track_state_change| OFF | session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection | session_track_transaction_info | OFF | sha256_password_proxy_users | OFF | show_compatibility_56| OFF | show_create_table_verbosity | OFF | show_old_temporals | OFF | skip_external_locking| ON| skip_name_resolve | OFF | skip_networking| OFF | skip_show_database | OFF | slave_allow_batching | OFF | slave_checkpoint_group | 512 | slave_checkpoint_period | 300 | slave_compressed_protocol| OFF | slave_exec_mode| STRICT | slave_load_tmpdir | /tmp | slave_max_allowed_packet | 1073741824 | slave_net_timeout | 60| slave_parallel_type | DATABASE | slave_parallel_workers | 0| slave_pending_jobs_size_max | 16777216 | slave_preserve_commit_order | OFF | slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN| slave_skip_errors | OFF | slave_sql_verify_checksum| ON| slave_transaction_retries| 10| slave_type_conversions | | slow_launch_time| 2| slow_query_log | OFF | slow_query_log_file | /usr/local/mysql/data/mysql-slow.log | socket| /usr/local/mysql/tmp/mysql.sock| sort_buffer_size| 262144 | sql_auto_is_null| OFF | sql_big_selects| ON| sql_buffer_result | OFF | sql_log_bin| ON| sql_log_off| OFF | 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| sql_notes | ON| sql_quote_show_create| ON| sql_safe_updates| OFF | sql_select_limit| 18446744073709551615 | sql_slave_skip_counter | 0| sql_warnings | OFF | ssl_ca| | ssl_capath | | ssl_cert | | ssl_cipher | | ssl_crl | | ssl_crlpath| | ssl_key | | stored_program_cache | 256 | super_read_only| OFF | sync_binlog| 1| sync_frm | ON| sync_master_info| 10000 | sync_relay_log | 10000 | sync_relay_log_info | 10000 | system_time_zone| CST |+--------------------------------+-------------------------------------67 rows in set (0.00 sec)mysql> show variables like 't%';+----------------------------------+---------------------------+| Variable_name| Value |+----------------------------------+---------------------------+| table_definition_cache | 1400 || table_open_cache | 2000 || table_open_cache_instances | 16 || thread_cache_size| 9|| thread_handling | one-thread-per-connection || thread_stack | 262144|| time_format | %H:%i:%s || time_zone | SYSTEM|| timestamp | 1657594523.491101 || tls_version | TLSv1,TLSv1.1 || tmp_table_size | 16777216 || tmpdir | /tmp || transaction_alloc_block_size| 8192 || transaction_allow_batching | OFF || transaction_isolation | REPEATABLE-READ || transaction_prealloc_size | 4096 || transaction_read_only | OFF || transaction_write_set_extraction | OFF || tx_isolation | REPEATABLE-READ || tx_read_only | OFF |+----------------------------------+---------------------------+20 rows in set (0.01 sec)mysql> show variables like 'u%';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| unique_checks | ON || updatable_views_with_limit | YES |+----------------------------+-------+2 rows in set (0.00 sec)

10、查看以字母 v、w、x 、y、z 开头的参数

mysql> show variables like 'v%';+-------------------------+---------------------+| Variable_name | Value|+-------------------------+---------------------+| version | 5.7.25 || version_comment | Source distribution || version_compile_machine | x86_64 || version_compile_os| Linux|+-------------------------+---------------------+4 rows in set (0.00 sec)mysql> show variables like 'w%';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout | 28800 || warning_count | 0|+---------------+-------+2 rows in set (0.00 sec)mysql> show variables like 'x%';Empty set (0.00 sec)mysql> show variables like 'y%';Empty set (0.00 sec)mysql> show variables like 'z%';Empty set (0.01 sec)