1.MySQL服务器
1.1服务器选项和变量引用
1.2 服务器配置默认值
1.2.1 服务器默认值的变化
1.2.2使用默认服务器配置文件样本
my.ini
1.3 服务器命令行参数
[mysqld]
和
[server]
读取配置
[mysqld]
,
[server]
,
[mysqld_safe]
, 和
[safe_mysqld]
读取配置
[mysqld]
和
[mysql.server]
读取配置
[server]
, [embedded]
,和[xxxxx
_SERVER]读取配置
--maximum-var_name
=value 变量最大值
1.4 服务器系统变量
mysqld --verbose --help #compiled-in defaults and any option files that it reads,
mysqld --no-defaults --verbose --help # compiled-in defaults, ignoring the settings in any option files
SHOW VARIABLES
1.5 使用系统变量
GLOBAL
or @@global.
.
SESSION
, @@session.
, or @@
. LOCAL
or @@local. or no modifier
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
SET max_join_size=DEFAULT; SET @@session.max_join_size=@@global.max_join_size;
shell>mysql --max_allowed_packet=16M #正确
shell>mysql --max_allowed_packet=16*1024*1024 #错误
mysql>SET GLOBAL max_allowed_packet=16M;#错误
mysql>SET GLOBAL max_allowed_packet=16*1024*1024;#正确
--delay_key_write=1
works but
--delay_key_write=ON
does not.
SHOW VARIABLES
得到的变量不能使用 SELECT @@var_name
,可以通过SHOW VARIABLES LIKE 'var_name
'
.
GLOBAL
keyword when setting
GLOBAL
-only variables but not when retrieving them is to prevent problems in the future. If we were to remove a
SESSION
variable that has the same name as a
GLOBAL
variable, a client with the
SUPER
privilege might accidentally change the
GLOBAL
variable rather than just the
SESSION
variable for its own connection. If we add a
SESSION
variable with the same name as a
GLOBAL
variable, a client that intends to change the
GLOBAL
variable might find only its own
SESSION
variable changed.
1.5.1 结构化系统变量
-
key_buffer_size
-
key_cache_block_size
-
key_cache_division_limit
-
key_cache_age_threshold
instance_name.component_name
format. Examples:
hot_cache.key_buffer_size hot_cache.key_cache_block_size cold_cache.key_cache_block_size
default.key_buffer_size
andkey_buffer_size
指向同一个系统变量
shell> mysqld --key_buffer_size=6M \
--hot_cache.key_buffer_size=2M \
--cold_cache.key_buffer_size=2M
To retrieve the cache size, do this:
mysql> SELECT @@global.hot_cache.key_buffer_size;
However, the following statement does not work. The variable is not interpreted as a compound name, but as a simple string for a LIKE
pattern-matching operation:
mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';
This is the exception to being able to use structured variable names anywhere a simple variable name may occur.
1.5.2 动态系统变量
ENUM 列,第一个值与1相对应
1.6 服务器状态变量
SHOW [GLOBAL | SESSION] STATUS
1,7 服务器SQL模式
设置SQL模式
NO_ENGINE_SUBSTITUTION
--sql-mode="modes
"
sql-mode="modes
"
--sql-mode=""
或者sql-mode=""
i
SET GLOBAL sql_mode = 'modes
'; SET SESSION sql_mode = 'modes
';
SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
重要的SQL模式
-
ANSI
-
STRICT_TRANS_TABLES
-
TRADITIONAL
SQL模式列表
SQL模式的结合
1.8 服务器插件
1.8.1 安装与卸载插件
安装插件
mysql.plugin表记录注册的插件,内置插件不在此列
--skip-grant-tables 将不会查看 mysql.plugin
--plugin-load
或
--plugin-load-add
添加插件(不注册在mysql.plugin)
--plugin-load=x --plugin-load-add=y
等同于
--plugin-load="x;y"
--plugin-load-add=y --plugin-load=x
等同于
--plugin-load=x
[mysqld] plugin-load=myplugin=somepluglib.so
INSTALL PLUGIN
会注册在mysql.plugin
mysql> INSTALL PLUGIN myplugin SONAME 'somepluglib.so';
INFORMATION_SCHEMA.PLUGINS
table and the
SHOW PLUGINS
statement.
控制插件启动
plugin_name
=OFF --disable-plugin_name --skip-plugin_name
plugin_name
[=ON] (如果插件初始化失败,插件处于禁用) --enable-plugin_name
plugin_name
=FORCE (如果插件初始化失败,服务器停止)
plugin_name
=FORCE_PLUS_PERMANENT
[mysqld] csv=ON blackhole=FORCE archive=OFF
卸载插件
UNINSTALL PLUGIN
与 INSTALL PLUGIN
相对应,也可卸载启动时 配置的插件。需重启
INFORMATION_SCHEMA.PLUGINS
or
SHOW PLUGINS
.中library name 为NULL,不可卸载
plugin_name
=FORCE_PLUS_PERMANENT 在运行时,插件不能卸载,这些插件在INFORMATION_SCHEMA.PLUGINS的LOAD_OPTION列进行标识
1.8.2 获得插件信息
mysql> SELECT * FROM information_schema.PLUGINS\G
或者
mysql> SHOW PLUGINS\G
1.9 IPv6支持
shell> mysql -h ::1
使用IPv6连接本地服务器
mysql>CREATE USER 'bill'@'::1' IDENTIFIED BY 'secret';
mysql>GRANT SELECT ON mydb.* TO 'bill'@'::1';
1.9.1 确认系统支持IPv6
shell> ping6 ::1
1.9.2 配置MySQL服务器允许IPv6连接
--bind-address=addr
1.9.3 使用本地主机IPv6地址进行连接
-
[mysqld] bind-address = * # before 5.6.6, use :: rather than *
-
mysql>
CREATE USER 'ipv6user'@'::1' IDENTIFIED BY 'ipv6pass';
-
shell>
mysql -h ::1 -u ipv6user -pipv6pass
-
mysql>
STATUS
... Connection: ::1 via TCP/IP ... mysql>SELECT CURRENT_USER(), @@bind_address;
+----------------+----------------+ | CURRENT_USER() | @@bind_address | +----------------+----------------+ | ipv6user@::1 | :: | +----------------+----------------+
1.9.4 使用非本地主机IPv6地址进行连接
Server host: 2001:db8:0:f101::1 Client host: 2001:db8:0:f101::2
-
--bind-address
[mysqld] bind-address = * # before 5.6.6, use :: rather than *
-
创建用户
mysql>
CREATE USER 'remoteipv6user'@'2001:db8:0:f101::2' IDENTIFIED BY 'remoteipv6pass';
-
客户端连接
shell>
mysql -h 2001:db8:0:f101::1 -u remoteipv6user -premoteipv6pass
-
mysql>
STATUS
... Connection: 2001:db8:0:f101::1 via TCP/IP ... mysql>SELECT CURRENT_USER(), @@bind_address;
+-----------------------------------+----------------+ | CURRENT_USER() | @@bind_address | +-----------------------------------+----------------+ | remoteipv6user@2001:db8:0:f101::2 | :: | +-----------------------------------+----------------+
1.10 服务器端帮助
share
or share/mysql
下
1.11 服务器端对信号的响应
-
SIGTERM
服务器关闭 -
SIGHUP
服务器重新加载 grant tables 刷新tables, logs, the thread cache, and the host cache. 生成 status report to the error log
1.12 关闭过程
-
初始化
-
如有必要,创建关闭进程
-
拒绝新连接
-
终止当前活动
-
服务器关闭或者关闭存储引擎
-
服务器退出
2. MySQL服务器日志
Log Type | Information Written to Log |
---|---|
Error log | Problems encountered starting, running, or stopping mysqld |
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 |
DDL log (metadata log) | Metadata operations performed by DDL statements |
FLUSH LOGS
statement;
flush-logs
or
refresh
argument;
--flush-logs
or
--master-data
2.1 选择General Query and Slow Query 日志输出位置
general_log
and
slow_log
tables in the
mysql
database
启动时控制:
--log-output 指定输出位置 TABLE
(log to tables), FILE
(log to files), or NONE
默认为FILE
general_log
general_log_file
slow_query_log
slow_query_log_file
运行时控制:
log_output
general_log
and slow_query_log
variablesgeneral_log_file
and slow_query_log_file
variablessql_log_off
variableSHOW CREATE TABLE mysql.general_log; SHOW CREATE TABLE mysql.slow_log;
SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; ALTER TABLE mysql.general_log ENGINE = MyISAM; SET GLOBAL general_log = @old_log_state;
2.2 错误日志
2.4 Binary Log
记录潜在引起数据库改变
--log-bin[=base_name
]
默认 pid-file的值
--log-bin=base_name.extension extension被忽略
--log-bin-index[=file_name
]
2.4.1 Binary Logging Formats
--binlog-format=STATEMENT
.--binlog-format=ROW
.--binlog-format=MIXED
.2.4.2 Setting The Binary Log Format
--binlog-format=type
mysql>SET GLOBAL binlog_format = 'STATEMENT';
mysql>SET GLOBAL binlog_format = 'ROW';
mysql>SET GLOBAL binlog_format = 'MIXED';
mysql>SET SESSION binlog_format = 'STATEMENT';
mysql>SET SESSION binlog_format = 'ROW';
mysql>SET SESSION binlog_format = 'MIXED';
2.4.3 Mixed Binary Logging Format
2.4.4 Logging Format for Changes to mysql Database Tables
2.5 The Slow Query Log
long_query_time 0 and 10 seconds
min_examined_row_limit
log_slow_admin_statements
ALTER TABLE
,
ANALYZE TABLE
,
CHECK TABLE
,
CREATE INDEX
,
DROP INDEX
,
OPTIMIZE TABLE
, and
REPAIR TABLE
.
2.6 The DDL Log
2.7 Server Log Maintenance
mysql-log-rotate
script ( Linux (Red Hat) installation)
3 Running Multiple MySQL Instances on One Machine
3.1 Setting Up Multiple Data Directories
-
Create a new data directory.
-
Copy an existing data directory.
3.2 Running Multiple MySQL Instances on Windows
3.2.1 Starting Multiple MySQL Instances at the Windows Command Line
-
Create two option files. For example, create one file named
C:\my-opts1.cnf
that looks like this:[mysqld] datadir = C:/mydata1 port = 3307
Create a second file named
C:\my-opts2.cnf
that looks like this:[mysqld] datadir = C:/mydata2 port = 3308
-
Use the
--defaults-file
option to start each server with its own option file:C:\>
C:\mysql\bin\mysqld --defaults-file=C:\my-opts1.cnf
C:\>C:\mysql\bin\mysqld --defaults-file=C:\my-opts2.cnf
C:\>C:\mysql\bin\mysqladmin --port=3307 --host=127.0.0.1 --user=root --password shutdown
C:\>C:\mysql\bin\mysqladmin --port=3308 --host=127.0.0.1 --user=root --password shutdown
3.2.2 Starting Multiple MySQL Instances as Windows Services
# options for mysqld1 service
[mysqld1]
basedir = C:/mysql-5.5.9
port = 3307
enable-named-pipe
socket = mypipe1
# options for mysqld2 service
[mysqld2]
basedir = C:/mysql-5.6.27
port = 3308
enable-named-pipe
socket = mypipe2
C:\> C:\mysql-5.5.9\bin\mysqld --install mysqld1
C:\> C:\mysql-5.6.27\bin\mysqld --install mysqld2
C:\> NET START mysqld1
C:\> NET START mysqld2
C:\mysql-5.5.9\bin\mysqld --install mysqld1
C:\> C:\mysql-5.6.27\bin\mysqld --install mysqld2
NET START mysqld1
C:\> NET START mysqld2
C:\>C:\mysql-5.5.9\bin\mysqld --install mysqld1
--defaults-file=C:\my-opts1.cnf
C:\>C:\mysql-5.6.27\bin\mysqld --install mysqld2
--defaults-file=C:\my-opts2.cnf
3.3 Running Multiple MySQL Instances on Unix
shell> cmake . -DMYSQL_TCP_PORT=port_number
\
-DMYSQL_UNIX_ADDR=file_name
\
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.27
shell> mysqladmin --host=host_name
--port=port_number
variables
shell> mysqld_safe --socket=file_name
--port=port_number
shell>mysqld_safe --defaults-file=/usr/local/mysql/my.cnf
shell>mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2
4 Tracing mysqld Using DTrace
MySQL DTrace Probes
Group | Probes |
---|---|
Connection | connection-start , connection-done |
Command | command-start , command-done |
Query | query-start , query-done |
Query Parsing | query-parse-start , query-parse-done |
Query Cache | query-cache-hit , query-cache-miss |
Query Execution | query-exec-start , query-exec-done |
Row Level | insert-row-start , insert-row-done |
update-row-start , update-row-done | |
delete-row-start , delete-row-done | |
Row Reads | read-row-start , read-row-done |
Index Reads | index-read-row-start , index-read-row-done |
Lock | handler-rdlock-start , handler-rdlock-done |
handler-wrlock-start , handler-wrlock-done | |
handler-unlock-start , handler-unlock-done | |
Filesort | filesort-start , filesort-done |
Statement | select-start , select-done |
insert-start , insert-done | |
insert-select-start , insert-select-done | |
update-start , update-done | |
multi-update-start , multi-update-done | |
delete-start , delete-done | |
multi-delete-start , multi-delete-done | |
Network | net-read-start , net-read-done , net-write-start , net-write-done |
Keycache | keycache-read-start , keycache-read-block , keycache-read-done , keycache-read-hit , keycache-read-miss , keycache-write-start , keycache-write-block ,keycache-write-done |