运行时的系统变量
mysql的系统变量的特性是：对于多数的系统变量都是可以在服务器程序运行的时候动态修改，但是很多时候我们对于运行时的变量修改这个概念十分模糊，到底什么时候修改变量时临时的，什么时候又是全局生效的，这里需要好好来唠叨一下。针对一次客户端端连接我们会有下面的情况：客户端A使用配置1，而客户端B使用配置2但是我们并不想他们私有更改系统的固定配置，这样肯定是不行的，另一种情况是我们想要每一个客户端连接的时候想要可以使用一一些自定义的配置要如何处理？根据上面的描述，我们可以看到系统变量运行时候的配置出现的下面两个问题：
- 连接时的系统变量配置
- 公有参数的私有化问题
为了解决这两个问题，mysql设计了“作用范围”的方式来区分运行时的系统变量和全局的系统变量。
作用范围
根据mysql的规则定义，他将变量分为下面两种：
- GLOBAL :全局变量，影响服务器的整体操作。
- SESSION :会话变量，影响某个客户端连接的操作。(注: SESSION 有个别名叫 LOCAL（本次连接变量或者当前变量，不是本地变量哦）)
服务器启动的时候，他会将所有连接当前服务端程序的客户端默认的变量配置为GLOBAL的全局变量的配置，也就是说每一个客户端连接的时候都会继承一份GLOBAL全局变量为 SESSION的会话变量进行使用，比如我们通过mysqld命令进行设置的变量都是GLOABL全局变量，因为这时候服务器还在启动不可能会有会话变量的存在，而使用mysql命令进行连接才有可能会出现会话变量的调整。
补充：特别强调会话变量的作用范围仅仅限制于一次客户端的连接，当建立一次新的客户端连接的时候又会接着按照继承全局变量的方式重新读取（前提是你的新客户端没有对与配置进行修改），所以需要十分小心当前变量的作用范围
最后，为了防止你头晕，这里我们只需要进行如下的记忆:
Mysqld：服务端启动的相关配置都是全局的变量
Mysql：客户端连接的命令产生的配置，连接前的命令行使用会话变量，在连接时可以进行相关命令操作把全局变量变为临时变量。
全局变量和会话变量的设置
设置系统变量一般有下面两种方法，
SET [GLOBAL|SESSION] 系统变量名 = 值; SET [@@(GLOBAL|SESSION).]var_name = XXX;
比如如果我们想要设置服务端端全局变量的参数：
SET GLOBAL default_storage_engine = MyISAM; SET @@GLOBAL.default_storage_engine = MyISAM;
下面是客户端进行连接的时候，我们可以使用SESSION的变量设置方法设置当前的参数和变量参数。
SET SESSION default_storage_engine = MyISAM; SET @@SESSION.default_storage_engine = MyISAM; SET default_storage_engine = MyISAM;
查看系统变量的作用范围
既然引入了变量的作用范围，那么我们最开始提到的关于系统变量的作用范围，**查看的是全局变量还是当前变量？**仔细思考其实不难理解，答案是：默认查看的是 SESSION 作用范围的系统变量，既然默认看当前的变量值，当然我们也可以在查看系统变量的语句上加上要查看哪个 作用范围 的系统变量，就像这样：
SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];
下面是关于部分操作的演示，当然也可以进个个人的试验：
SHOW SESSION VARIABLES LIKE 'default_storage_engine'; SHOW GLOBAL VARIABLES LIKE 'default_storage_engine'; SET SESSION default_storage_engine = MyISAM; SHOW SESSION VARIABLES LIKE 'default_storage_engine';
由于篇幅有限这里就不展示运行结果了，相信看到语法之后也可以很快的理解含义。
补充：值得注意的是SESSION的变量只会针对设置了变量之后的后续的客户端连接的值，而不会修改之前已经进行连接的客户端的参数，并且我们可以发现修改某个客户端的连接当前配置，并不会影响GLOBAL全局变量的设置。
系统变量注意事项
- 并不是所有系统变量都具有 GLOBAL 和 SESSION 的作用范围。
- 有一些系统变量只具有 GLOBAL 作用范围，比方说 max_connections 。
- 有一些系统变量只具有 SESSION 作用范围，比如 insert_id。
- 有一些系统变量的值既具有 GLOBAL 作用范围，也具有 SESSION 作用范围，比如我们前边用到的 default_storage_engine。
- 有些系统变量是只读的，并不能设置值。
- 比方说 version ，表示当前 MySQL 的版本。修改即没有意义，也不能修改。
启动选项和系统变量的区别
启动选项可以看作是我们启动变量的时候使用--(双划线)或者-(单划线)进行设置的系统变量启动参数，并且大部分的系统变量都是可以使用系统变量参数进行设置的，所以对于系统变量和启动选项有如下的区别：
- 大部分系统变量可以使用启动选项的方式设置
- 部分系统变量是启动启动的时候生成，无法作为启动选项（比如：
character_set_client） - 有些启动选项也不是系统变量，比如
defaults-file
状态变量
服务器也不全是系统变量，为了反应系统的性能，会存在诸如状态变量的参数，比如手当前连接的线程数量，以及连接的错误次数等等，由于这些参数反应的是服务器自身的运行情况，所以不能由程序员设置，而是需要依靠应用程序设置。
查看状态变量
这里可能会好奇为什么状态变量也存在全局和当前变量的参数区别？这里不要被误导了，上面说明的是可以由应用程序设置，也就意味着会存在多个客户端访问的情况，所以也需要考虑区别全局和当前的情况，最后查看状态变量的命令如下：
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];
下面是一个实际的操作案例：
mysql> SHOW STATUS LIKE 'thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 1 | | Threads_running | 2 | +-------------------+-------+ 4 rows in set (0.01 sec)
可以看到所有和thread相关的变量都进行了展示。
总结
本节我们从命令行的格式基础开始，介绍了mysql如何进行配置的读取的，以及在读取配置的时候需要注意哪下情况，这里面的细节还是比较多的，并且操作系统的不同会存在读取顺序的不同，但是基本只需要重点记忆和 **Linux **有关的参数即可。
写在最后
写下来发现写的内容挺多的，需要多回顾和总结才能慢慢消化。
补充资料
My.ini配置文件模板（5.7）
如果觉得内容不明不白的，可以去官方的介绍页面进行了解，地址如下：
网络异常，图片无法展示
|
当然最后如果懒得访问网页的，这里也直接从网页吧模板拷贝过来了。
# # FromDual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server # Location: %MYCNF% # This template is intended to work with MySQL 5.7 and newer and MariaDB 10.3 and newer # Get most recent updated from here: # https://www.fromdual.com/mysql-configuration-file-sample # [client] port = %PORT% # default 3306 socket = %SOCKET% # Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise [mysql] no_auto_rehash max_allowed_packet = 16M prompt = '\u@\h [\d]> ' # 'user@host [schema]> ' default_character_set = utf8 # Possibly this setting is correct for most recent Linux systems [mysqldump] max_allowed_packet = 16M [mysqld] # Connection and Thread variables port = %PORT% # default 3306 socket = %SOCKET% # Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise basedir = %BASEDIR% datadir = %DATADIR% # tmpdir = '%INSTANCEDIR%/tmp' max_allowed_packet = 16M default_storage_engine = InnoDB # explicit_defaults_for_timestamp = 1 # MySQL 5.6 ff. default in MySQL 8.0, test carefully! This can have an impact on application. # disable_partition_engine_check = true # Since MySQL 5.7.17 to 5.7.20. To get rid of nasty message in error log # character_set_server = utf8mb4 # For modern applications, default in MySQL 8.0 # collation_server = utf8mb4_general_ci max_connections = 151 # Values < 1000 are typically good max_user_connections = 145 # Limit one specific user/application thread_cache_size = 151 # Up to max_connections makes sense # Query Cache (does not exist in MySQL 8.0 any more!) # query_cache_type = 1 # Set to 0 to avoid global QC Mutex, removed in MySQL 8.0 # query_cache_size = 32M # Avoid too big (> 128M) QC because of QC clean-up lock!, removed in MySQL 8.0 # Session variables sort_buffer_size = 2M # Could be too big for many small sorts tmp_table_size = 32M # Make sure your temporary results do NOT contain BLOB/TEXT attributes read_buffer_size = 128k # Resist to change this parameter if you do not know what you are doing read_rnd_buffer_size = 256k # Resist to change this parameter if you do not know what you are doing join_buffer_size = 128k # Resist to change this parameter if you do not know what you are doing # Other buffers and caches table_definition_cache = 1400 # As big as many tables you have table_open_cache = 2000 # connections x tables/connection (~2) table_open_cache_instances = 16 # New default in 5.7 # MySQL error log log_error = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_error.log # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld # log_timestamps = SYSTEM # MySQL 5.7, equivalent to old behaviour # log_warnings = 2 # MariaDB equivalent to log_error_verbosity = 3, MySQL does NOT support this any more! # log_error_verbosity = 3 # MySQL 5.7 ff., equivalent to log_warnings = 2, MariaDB does NOT support this! innodb_print_all_deadlocks = 1 # wsrep_log_conflicts = 1 # for Galera only! # Slow Query Log slow_query_log_file = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_slow.log # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld slow_query_log = 0 log_queries_not_using_indexes = 0 # Interesting on developer systems! long_query_time = 0.5 min_examined_row_limit = 100 # General Query Log general_log_file = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_general.log # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld general_log = 0 # Performance Schema # performance_schema = ON # for MariaDB 10 releases performance_schema_consumer_events_statements_history_long = ON # MySQL 5.6/MariaDB 10 and newer # Binary logging and Replication server_id = %SERVERID% # Must be set on MySQL 5.7 and newer if binary log is enabled! log_bin = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld # master_verify_checksum = ON # MySQL 5.6 / MariaDB 10.2 # binlog_cache_size = 1M # For each connection! # binlog_stmt_cache_size = 1M # For each connection! max_binlog_size = 128M # Make bigger for high traffic to reduce number of files sync_binlog = 1 # Set to 0 or higher to get better write performance, default since MySQL 5.7 expire_logs_days = 5 # We will survive Easter holidays # binlog_expire_logs_seconds = 432000 # MySQL 8.0, 5 days * 86400 seconds binlog_format = ROW # Use MIXED if you want to experience some troubles, default since MySQL 5.7, MariaDB default is MIXED # binlog_row_image = MINIMAL # Since 5.6, MariaDB 10.1 # auto_increment_increment = 2 # For Master/Master set-ups use 2 for both nodes # auto_increment_offset = 1 # For Master/Master set-ups use 1 and 2 # Slave variables log_slave_updates = 1 # Use if Slave is used for Backup and PiTR, default since MySQL 8.0 read_only = 0 # Set to 1 to prevent writes on Slave # super_read_only = 0 # Set to 1 to prevent writes on Slave for users with SUPER privilege. Since 5.7, not in MariaDB # skip_slave_start = 1 # To avoid start of Slave thread # relay_log = %UNAME%_%INSTANCE%_relay-bin # relay_log_info_repository = TABLE # MySQL 5.6, default since MySQL 8.0, MySQL only # master_info_repository = TABLE # MySQL 5.6, default since MySQL 8.0, MySQL only # slave_load_tmpdir = '%INSTANCEDIR%/tmp' # defaults to tmpdir # Crash-safe replication Master # binlog_checksum = CRC32 # default # sync_binlog = 1 # default since 5.7.6, but slow! # innodb_support_xa = 1 # default, depracted since 5.7.10 # Crash-safe replication Slave # relay_log_info_repository = TABLE # MySQL 5.6, default since MySQL 8.0, MySQL only # master_info_repository = TABLE # MySQL 5.6, default since MySQL 8.0, MySQL only # relay_log_recovery = 1 # sync_relay_log_info = 1 # default 10000 # relay_log_purge = 1 # default # slave_sql_verify_checksum = 1 # default # GTID replication # gtid_mode = ON # MySQL only, Master and Slave # enforce_gtid_consistency = 1 # MySQL only, Master and Slave # log_bin = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog # In 5.6 also on Slave # log_slave_updates = 1 # In 5.6 also on Slave # Security variables # local_infile = 0 # If you are security aware # secure_auth = 1 # If you are security aware # sql_mode = TRADITIONAL,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO # Be careful changing this afterwards, NO_AUTO_CREATE_USER does NOT exist any more in MySQL 8.0 # skip_name_resolve = 0 # Set to 1 if you do not trust your DNS or experience problems # secure_file_priv = '%INSTANCEDIR%/tmp' # chmod 750, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld # MyISAM variables key_buffer_size = 8M # Set to 25 - 33 % of RAM if you still use MyISAM myisam_recover_options = 'BACKUP,FORCE' # disabled_storage_engines = 'MyISAM,MEMORY' # MySQL 5.7, do NOT during/before mysql_upgrade, good for Galera! # MEMORY variables max_heap_table_size = 64M # Should be greater or equal to tmp_table_size # InnoDB variables innodb_strict_mode = ON # Default since MySQL 5.7, and MariaDB 10.4 innodb_buffer_pool_size = 128M # Go up to 75% of your available RAM innodb_buffer_pool_instances = 8 # Bigger if huge InnoDB Buffer Pool or high concurrency innodb_file_per_table = 1 # Is the recommended way nowadays # innodb_flush_method = O_DIRECT # O_DIRECT is sometimes better for direct attached storage # innodb_write_io_threads = 8 # If you have a strong I/O system or SSD # innodb_read_io_threads = 8 # If you have a strong I/O system or SSD # innodb_io_capacity = 1000 # If you have a strong I/O system or SSD innodb_flush_log_at_trx_commit = 2 # 1 for durability, 0 or 2 for performance innodb_log_buffer_size = 16M # Bigger if innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 256M # Bigger means more write throughput but longer recovery time # Since MariaDB 10.0 and MySQL 5.6 innodb_monitor_enable = all # Overhead < 1% according to PeterZ/Percona # Galera specific MySQL parameter # default_storage_engine = InnoDB # Galera only works with InnoDB # innodb_flush_log_at_trx_commit = 2 # Durability is achieved by committing to the Group # innodb_autoinc_lock_mode = 2 # For parallel applying # binlog_format = row # Galera only works with RBR # query_cache_type = 0 # Use QC with Galera only in a Master/Slave set-up, removed in MySQL 8.0 # query_cache_size = 0 # removed in MySQL 8.0 # log_slave_updates = ON # Must be enabled on ALL Galera nodes if binary log is enabled! # server_id = ... # Should be equal on all Galera nodes according to Codership CTO if binary log is enabled. # WSREP parameter # wsrep_on = on # Only MariaDB >= 10.1 # wsrep_provider = /usr/lib/galera/libgalera_smm.so # Location of Galera Plugin on Ubuntu ? # wsrep_provider = /usr/lib64/galera-3/libgalera_smm.so # Location of Galera v3 Plugin on CentOS 7 # wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so # Location of Galera v4 Plugin on CentOS 7 # wsrep_provider_options = 'gcache.size = 1G' # Depends on you workload, WS kept for IST # wsrep_provider_options = 'gcache.recover = on' # Since 3.19, tries to avoid SST after crash # wsrep_cluster_name = "My cool Galera Cluster" # Same Cluster name for all nodes # wsrep_cluster_address = "gcomm://192.168.0.1,192.168.0.2,192.168.0.3" # Start other nodes like this # wsrep_node_name = "Node A" # Unique node name # wsrep_node_address = 192.168.0.1 # Our address where replication is done # wsrep_node_incoming_address = 10.0.0.1 # Our external interface where application comes from # wsrep_sync_wait = 1 # If you need realy full-synchronous replication (Galera 3.6 and newer) # wsrep_slave_threads = 16 # 4 - 8 per core, not more than wsrep_cert_deps_distance # wsrep_sst_method = rsync # SST method (initial full sync): mysqldump, rsync, rsync_wan, xtrabackup-v2 # wsrep_sst_auth = sst:secret # Username/password for sst user # wsrep_sst_receive_address = 192.168.2.1 # Our address where to receive SST # Group Replication parameter # default_storage_engine = InnoDB # Group Replication only works with InnoDB # server_id = %SERVERID% # Should be different on all 3 nodes # log_bin = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld # binlog_format = ROW # binlog_checksum = NONE # not default! # gtid_mode = ON # enforce_gtid_consistency = ON # master_info_repository = TABLE # relay_log_info_repository = TABLE # log_slave_updates = ON # slave_parallel_workers = <n> # 1-2/core, max. 10 # slave_preserve_commit_order = ON # slave_parallel_type = LOGICAL_CLOCK # transaction_write_set_extraction = XXHASH64 # loose-group_replication_group_name = "$(uuidgen)" # Must be the same on all nodes # loose-group_replication_start_on_boot = OFF # loose-group_replication_local_address = "192.168.0.1" # loose-group_replication_group_seeds = "192.168.0.1,192.168.0.2,192.168.0.3" # All nodes of Cluster # loose-group_replication_bootstrap_group = OFF # loose-group_replication_single_primary_mode = FALSE # = multi-primary
