mysql> show variables like '%skip%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | skip_external_locking | ON |
在配置文件[mysqld]下開啟這個參數OK。 key_buffer_size = 256M：為MYISAM數據表開啟供線程共享的索引緩存。我們的項目中數據表基本上用的是INNODB引擎，所以這個參數暫時不進行調整 max_allowed_packet = 16M：服務端最大允許接收的數據包大小。在沒有調整該配置項的時候，服務端默認是4M。當然這個參數和mysql（默認16M）和mysqldump（默認為24M，我已經調整為16M）中的數據包大小有關系，一般情況下1M就可以，官方建議如果使用了blog或者更大的字符串時進行該參數的調整，一般情況下，數據庫會被初始化為net_buffer_length（最小1024byte，最大是1M，默認是16KB）的大小。 table_open_cache = 512：所有線程打開表的數目（默認設置大小為1000）。如果opened_tables很大並且不經常使用flush tables，官方建議我們增加該參數的大小。這個值並不是越大越好，需要根據實際情況下open_tables和opened_tables的綜合進行調整 sort_buffer_size = 512K：需要排序會話的緩存大小，是針對每一個connection的，這個值也不會越大越好，默認大小是256kb，過大的配置會消耗更多的內存。我個人還沒有測試 read_buffer_size = 512K：為需要全表掃描的MYISAM數據表線程指定緩存，也是針對每個connection的，這個參數暫時我也不需要太關注。
Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB. This option is also used in the following context for all search engines: • For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY. • For bulk insert into partitions. • For caching results of nested queries. and in one other storage engine-specific way: to determine the memory block size for MEMORY tables. The maximum permissible setting for read_buffer_size is 2GB. For more information about memory use during different operations, see Section 8.11.4.1, “How MySQL Uses Memory”.
read_rnd_buffer_size = 1M：首先，該變量可以被任何存儲引擎使用，當從一個已經排序的鍵值表中讀取行時，會先從該緩沖區中獲取而不再從磁盤上獲取。默認為256K。
This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization. When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. See Section 8.2.1.15, “ORDER BY Optimization”. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer Server System Variables 627 allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries. The maximum permissible setting for read_rnd_buffer_size is 2GB.
thread_cache_size = 18：有多少線程供服務緩存使用。
How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. For details, see Section 5.1.6, “Server Status Variables”. The default value is based on the following formula, capped to a limit of 100: 8 + (max_connections / 100) This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer visible within the embedded server.
query_cache_size= 8M：分配給查詢緩存的內存大小。要配合query_cache_type使用，默認是不開啟的。只從該參數的表面介紹來看，似乎值設置的越大，帶來的效果會更好，但是請注意，查詢緩存的工作原理，一個select語句過來後，數據庫將查詢結果緩存到cache中，等同樣的select查詢過來後，如果這段時間內該查詢結果沒有發生變化時，數據庫將cache中將緩存結果返回，那麼假如查詢的相關數據表增刪改特別多的話，數據表變更的這段時間內，要將cache失效，然後再更新數據，對於增刪改來說，花費的時間就很多了，所以要有所權衡，這個參數我會在將來進行相關測試數據整理。
By default, the query cache is disabled. This is achieved using a default value of 1M, with a default for query_cache_type of 0. (To reduce overhead significantly if you set the size to 0, you should also start the server with query_cache_type=0. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0. See Section 8.9.3.3, “Query Cache Configuration”, for more information. The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, a warning will occur, as described in Section 8.9.3.3, “Query Cache Configuration”.query_cache_type = 1：1表示緩存所有查詢，2表示緩存select sql_cache的查詢，看如下內容。
0 or OFF Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0. 1 or ON Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE. 2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE.
set global max_connections = 500：注意這個是通過命令行設置最大連接數，不是配置在配置文件的，因為我在配置文件裡面嘗試修改，重啟mysql服務後並沒有起效，通過該參數設置以後，重啟服務後，依然沒有起效，如果有朋友知道這個原因的話，請告知。如果說你的項目使用的是spring的連接池的時候，我認為spring個connection就對應的這個連接。根據你項目的需求而定。 log-bin=mysql-bin：開啟二進制日志，並且日志的名稱會按照mysql-bin***之類的依次生成。但是我一直有一些疑問，就是這個二進制日志是否是innodb的事務日志，是傳統所說的事務日志嗎？ binlog_format=mixed：二進制日志的格式為mixed，該中模式是statement和row模式的結合體裡面講解了我們項目在二進制日志設置上遇到的問題和解決辦法，如果遇到類似的問題後，會有所幫助。
In MySQL 5.7, the default format is STATEMENT. You must have the SUPER privilege to set either the global or session binlog_format value. The rules governing when changes to this variable take effect and how long the effect lasts are the same as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information. When MIXED is specified, statement-based replication is used, except for cases where only row-based replication is guaranteed to lead to proper results. For example, this happens when statements contain user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always uses statement-based replication for stored functions and triggers.
innodb_buffer_pool_size = 512M：innodb緩存表和索引的內存空間，官網建議我們設置為物理內存的50-80%，但是請注意是專注於數據服務的機器，如果你的操作系統包含數據庫和其他服務，請考慮其他服務占用的內存，對於Linux來說，這個參數會占用swap文件的大小。
The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB. The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server. The larger you set the innodb_buffer_pool_size value, the less disk I/O is needed to access the same data in tables more than once. On a dedicated database server, you might set this to up to 80% of the machine physical memory size.
innodb_additional_mem_pool_size = 20M：用來設置innodb存儲數據目錄信息和其他內部數據結構的內存池大小。
The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default value is 8MB. This variable relates to the InnoDB internal memory allocator, which is unused if innodb_use_sys_malloc is enabled.
innodb_log_file_size = 128M：用來設置每個日志文件在內存池中的大小，但是請注意，其他資料說“一旦設置了該參數，你就需要把原來的ib_logfile備份刪除掉，否則mysql服務在重啟的時候會出錯"，我個人還沒有進行測試。
The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit InnoDB System Variables 1945 but not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.
innodb_log_buffer_size = 16M：為innodb向磁盤刷新日志文件的緩存大小，可以相對應減少磁盤的IO。
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.
innodb_flush_log_at_trx_commit = 2：設置二進制日志刷新磁盤的時間點。
The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB InnoDB System Variables 1941 transaction waits at most this many seconds for write access to the row before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option. See also Section 14.19.4, “InnoDB Error Handling”.
我在做Linux上mysql配置設置時，mysql服務就啟動不起來了，非常奇怪，沒有找到原因。
