![首頁 Logo Keith ]p !!](/themes/mytheme/logo.png){kind=logo}
最後更新: 2020-07-29
目錄
- 設定奪用的次序
- 查看即時設定值
- Networking Settings
- Debug log
- Case Sensitive
- mysql bind-address multiple ip
- symbolic-links
- Other Useful options
- utf8
- Select without lock
- Connection timeout
- max_connections
-
Log
- Log login fail - HOST_CACHE
- Folder / Table Name with "@002d"
- Session Settings
- sql_mode
- CPU
設定奪用的次序
- 伺服器
- 資料庫
- 資料表
- 資料欄位
- 連線
查看即時設定值
mysql> show variables like "%char%";
+--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
設定 mysqld 的 my.cnf 的位置
--defaults-file=/path/to/my.cnf
Toubleshoot
... [ERROR] unknown variable 'validate_password=OFF' ... [ERROR] Aborting
"--defaults-file" 必須是第一個參數 !!
Networking Settings
/etc/my.cnf
#skip-networking bind-address=0.0.0.0 port=3306 socket=
Debug log
- Error log Problems encountered starting, running, or stopping mysqld
- ISAM log Changes to the ISAM tables (used only for debugging the ISAM code)
- General query log Established client connections and statements received from clients
- Binary log Statements that change data (also used for replication)
- Relay log Data changes received from a replication master server
- Slow query log Queries that took more than long_query_time seconds to execute
Log File Location
/var/log/mysql/mysql.log
Log flushing occurs when you issue a X
FLUSH LOGS statement
OR
mysqladmin flush-logs
Change Logs level when MYSQL Running CMD:
server> change log-level warning;
server> change log-level --hosts=localhost
MySQL Slow Query
# 舊 Version
long_query_time=3 log-slow-queries=/var/log/mysql/mysqld-slow.log
# Version 5.6
# 啟用 slow query log slow_query_log=1 # 多過 2 秒的 query 才 log long_query_time=2 # 準備 log file # mkdir /var/log/mysqld # chown mysql: /var/log/mysql # chmod 770 /var/log/mysql slow_query_log_file=/var/log/mysql/slow-queries.log
# Checking
mysqladmin variables -p | grep slow
| log_slow_queries | ON | slow_launch_time | 2 | slow_query_log | ON | slow_query_log_file | /var/log/mysqld/slow-queries.log
OR
SHOW VARIABLES like '%slow%';
| log_slow_queries | ON | slow_launch_time | 2 | slow_query_log | ON | slow_query_log_file | /var/log/mysqld/slow-queries.log
# Format
# Apache access log
n.n.n.n - - [16/May/2018:16:08:58 +0800] "GET /api/v1/... HTTP/1.1" 200 5531 "-" "-"# Mysql slow log
# Time: 2018-05-16T08:09:11.878403Z # User@Host: USER[DB] @ localhost [127.0.0.1] Id: 38611 # Query_time: 13.344661 Lock_time: 0.000045 Rows_sent: 25 Rows_examined: 6084101 SET timestamp=1526458151; Select distinct(`X`) From `Y` where (F(code)) and (Z='002');
* Time - Query_time ~ Apache time
09:11.878 - 13.344 ~ 8:58
Log all Query
準備 log file
mkdir /var/log/mysqld touch /var/log/mysqld/general.log chown mysql. /var/log/mysqld/general.log chmod 660 /var/log/mysqld/general.log
Settings
/etc/my.cnf
[mysqld] # log general_log=1 general_log_file=/var/log/mysqld/general.log
service mysqld restart
Checking
SHOW VARIABLES like 'general%';
| general_log | ON | general_log_file | /var/log/mysqld/general.log
Case Sensitive
# case in-sensitive
# Linux Default: 0
lower_case_table_names=1
0: table names are stored as specified and comparisons are case sensitive.
1: table names are stored in lowercase on disk and comparisons are not case sensitive.
2: table names are stored as given but compared in lowercase.
* You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names
* On Windows the default value is 1
* On Mac OS X, the default value is 2.
If you are using InnoDB or MySQL Cluster (NDB) tables,
you should set this variable to 1 on all platforms to force names to be converted to lowercase.
mysql bind-address multiple ip
Version: 5.1
mysql 不支援 bind 多個 IP address !!
symbolic-links
[mysqld] ........ # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
Other Useful options
設定在 /etc/my.cnf
max_allowed_packet
tmp_table_size
# maximum size of internal in-memory temporary tables
# If exceeds the limit, MySQL automatically converts it to an on-disk "MyISAM" table.
# if you do many advanced "GROUP BY" queries and you have lots of memory.
# variables Created_tmp_disk_tables and Created_tmp_tables 用來比較成效
# tmp_table_size/max_heap_table_size 應該要 equal
# Default: 16777216
tmp_table_size = 256M
max_heap_table_size
# Default: 16777216 (16MB)
# maximum size to which user-created MEMORY tables are permitted to grow.
# The value of the variable is used to calculate MEMORY table MAX_ROWS values.
# This variable has no effect on any existing MEMORY table
# (unless "CREATE TABLE", "ALTER TABLE" or "TRUNCATE TABLE")
P.S.
# my.ini
max_heap_table_size=128M
tmp_table_size=128M
# remark
max_heap_table_size is used in conjunction with tmp_table_size
to limit the size of internal in-memory tables. (會是此兩個 value 最小的那個生效)
Temporary tables can be created under conditions such as these:
- "UNION" queries
- an "ORDER BY" clause and a different "GROUP BY" clause
- "DISTINCT" combined with "ORDER BY"
- Multiple-table UPDATE statements.
- COUNT(DISTINCT)
# Checking
mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
mysql> show global variables like 'max_heap_table_size';
wait_timeout 與 interactive_timeout
Aborted_clients & Aborted_connects
skip_name_resolve
[mysqld] # Skip reverse DNS lookup of clients skip-name-resolve
# The only limitation is that the GRANT statements must then use IP addresses only
thread_cache_size
# Default 0
# 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
# only when the cache is empty is a new thread created.
SHOW GLOBAL STATUS like 'Connections';
SHOW GLOBAL STATUS like 'Threads_created';
UTF8
#utf8# old version
character-sets-dir=utf8 default-character-set=utf8 init_connect='SET NAMES utf8'
# mysql 5.7
skip-character-set-client-handshake character_set_client=utf8 character_set_server=utf8
--skip-character-set-client-handshake
when a client connects, it sends to the server the name of the character set that it wants to use.
However, the server ignores this request from the client.
Select without lock
usage:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SELECT * FROM TABLE_NAME ; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
TRANSACTION ISOLATION LEVEL:
Output format
Vertical format ( '\G' )
SHOW CREATE TABLE t\G
Connection timeout
# Default timeout setting
# for activity on a noninteractive connection # CLIENT_INTERACTIVE connect option to mysql_real_connect() wait_timeout = 28800 # for activity on an interactive connection # uses the CLIENT_INTERACTIVE option to mysql_real_connect() interactive_timeout = 28800
# Checking
show variables like "%timeout%";
Or
mysql> SHOW PROCESSLIST;
當 Command 是 Sleep 時, Time 代表 sleep 了多久
心得
If your connections are persistent (opened via mysql_pconnect)
you could lower these numbers to something reasonable like 600 (10 minutes)
max_connections
max_connections
# Total = max_connections+1( +1 是留給有 SUPER privilege 的帳戶使用)
/etc/my.cnf
# default: 151
max_connections = 150# 查看
SELECT @@MAX_CONNECTIONS AS 'Max Connections';
+-----------------+ | Max Connections | +-----------------+ | 151 | +-----------------+
OR
show variables like 'max_connections';
+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+
threads_connected 與 Connections
* Threads_connected 是當前的 connection. 它必須小於或等於 max_connections
show status like 'Threads_connected';
+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 2 | +-------------------+-------+
show status like 'Connections';
+---------------+--------+ | Variable_name | Value | +---------------+--------+ | Connections | 584371 | +---------------+--------+
open file
limits(open_files_limit)
當 over 時
/var/log/mysqld.log
... Error in accept: Too many open files
Checking
mysqladmin variables | grep -i open_files_limit
1024
修改
/etc/my.cnf
[mysqld] open_files_limit = 6144
service mysqld restart
OR
/etc/security/limits.conf
mysql soft nofile 4096 mysql hard nofile 4096
OR
systemctl list-unit-files | grep mysqld
systemctl edit mysqld
[Service] LimitNOFILE=4096 LimitMEMLOCK=4096
systemctl daemon-reload
systemctl restart mysqld
# Too many open files
open-files-limit=count
# 查看現在 open file 的設定
cat /proc/sys/fs/file-max
當 MySQL 在行時的情況
cat /proc/$(cat /var/run/mysqld/mysqld.pid)/limits
Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 10485760 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 15033 15033 processes Max open files 1024 4096 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 15033 15033 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us
Centos 7
Package: mysqld 5.5.56-MariaDB
181025 16:22:52 [Warning] Changed limits: max_open_files: 1024 max_connections: 214 table_cache: 400
The effective open_files_limit value is based on the value specified at system startup (if any) and
the values of max_connections and table_open_cache, using these formulas: 1) 10 + max_connections + (table_open_cache * 2) 2) max_connections * 5 3) operating system limit if positive 4) if operating system limit is Infinity: open_files_limit value specified at startup, 5000 if none
所以實際上係
show variables like "max_connections";
+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 214 | +-----------------+-------+ 1 row in set (0.00 sec)
原因
open-files-limit 限制了它
ulimit -n
1024
show global variables like '%open_files_limit%';
修正
mkdir /etc/systemd/system/mariadb.service.d
/etc/systemd/system/mariadb.service.d/limits.conf
[Service] LimitNOFILE=65535 LimitNPROC=65535
systemctl daemon-reload
systemctl restart mariadb
Remark
/etc/my.cnf
[mysqld] # Default Value (>= 5.6.8) 5000 # Default Value (<= 5.6.7) 0 open-files-limit=4096
Log & debug
Shell
# log_warnings is a dynamic variable
SELECT @@log_warnings; -- (Default: 1)
SELECT @@log_error; -- log 的位置
SET GLOBAL log_warnings=2; -- setting above 1 increases output (see server version)
my.cnf
[mysqld] log_error = /path/to/CurrentError.log log_warnings = 2
Log login fail
/etc/my.cnf
[mysqld] log-warnings=2
out
200723 17:01:43 [Warning] Access denied for user 'root '@'localhost' (using password: YES)
HOST_CACHE
Host Cache 應用在
- By caching the results of IP-to-host name lookups
- The cache contains information about errors that occur during the connection process.
(Some errors are considered "blocking" (max_connect_errors ))
For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache.
If the host is not in the cache, the server attempts to resolve the host name.
The thread takes the IP address and resolves it to a host name (using gethostbyaddr()).
It then takes that host name and resolves it back to the IP address (using gethostbyname())
and compares to ensure it is the original IP address.
Checking Cache
Performance Schema: host_cache
HOST_CACHE_SIZE
* Default value: 128
* Introduced: MariaDB 10.0
[mysqld] # 0 => Disable host_cache_size=256
To clear the host name cache:
By Statement:
FLUSH HOSTS
By mysqladmin:
mysqladmin flush-hosts
More. Info.
- To disabling DNS lookups: -skip-name-resolve
- To disable the host name cache: --skip-host-cache option
- To disallow TCP/IP: --skip-networking
Folder / Table name with "@002d"
* Any character is legal in database or table identifiers except ASCII NUL (X'00').
* Basic Latin letters (a..zA..Z), digits (0..9) and underscore (_) are encoded as is.
* Consequently, their case sensitivity directly depends on file system features.
* 其餘的都會 encode
# db, table, view, etc
@002d # '-' could be converted by '@002d'
Session Settings
@@ - System Vaiable
Using different suffix with @@, you can get either session or global value of the system variable.
When you refer to a system variable in an expression as @@var_name
(that is, when you do not specify @@global. or @@session.),
MySQL returns the session value if it exists and the global value otherwise.
(This differs from SET @@var_name = value, which always refers to the session value.)
@ - User Defined Variable
while @ is used for user defined variables.
Set a global system variable:
SET GLOBAL max_connections = 1000;
Or
SET @@GLOBAL.max_connections = 1000;
Persist a global system variable to the mysqld-auto.cnf file (and set the runtime value):
SET PERSIST max_connections = 1000;
Or
SET @@PERSIST.max_connections = 1000;
Set a session system variable:
# 256M
SET join_buffer_size = 268435456;
Checking
show variables like 'join_buffer_size';
Or
select @@join_buffer_size;
sql_mode
Checking
select @@GLOBAL.sql_mode;
Default
MySQL 5.7
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Config
my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION
STRICT_TRANS_TABLES # Default: On
Controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE.
* produces an error for attempts to create a key that exceeds the maximum key length.
* Strict mode affects handling of division by zero, zero dates, and zeros in dates
在 Strict SQL Mode 下, 即使 Column 的 Default 是 "None", 以下 Statement 仍會 Error
INSERT INTO levelone ( `id` ) VALUES (NULL);
NO_ENGINE_SUBSTITUTION # Default: On
On => an error occurs and the table is not created or altered if the desired engine is unavailable.
Control automatic substitution of the default storage engine
when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.
CPU
mysqld is a single-process program, multithreaded program.
