MySQL服务器管理

1.MySQL服务器

1.1服务器选项和变量引用

1.2 服务器配置默认值

1.2.1 服务器默认值的变化

1.2.2使用默认服务器配置文件样本

my.cnf   my-default.cnf

  my.ini

1.3 服务器命令行参数

mysqld  从  [mysqld]  和  [server]  读取配置

mysqld_safe  从  [mysqld] , [server] ,  [mysqld_safe] , 和  [safe_mysqld]  读取配置

mysql.server  从  [mysqld]  和  [mysql.server] 读取配置

嵌入式 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 variables   precede its name by GLOBAL or @@global.. 

Session variables   precede its name by 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;#正确

一些系统变量通过set语句设为 ON 或者1 来启用，设为OFF或者0 禁用

但在命令行或配置文件中，只能设为1或者0 。

on the command line,  --delay_key_write=1  works but  --delay_key_write=ON  does not.

一些通过   SHOW VARIABLES  得到的变量不能使用 SELECT @@var_name ，可以通过SHOW VARIABLES LIKE 'var_name'.

The reason for requiring the  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 and key_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 动态系统变量

enumerated系统变量，第一个值与0相对应，

 ENUM 列，第一个值与1相对应

1.6 服务器状态变量

  SHOW [GLOBAL | SESSION] STATUS  

1,7 服务器SQL模式

sql_mode 系统变量

设置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

mysql.plugin表包含INSTALL PLUGIN注册的插件，但只包含插件名和库文件名

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

*

0.0.0.0 IPv4

::

1.9.3  使用本地主机IPv6地址进行连接

1. 
   

   [mysqld]
   bind-address = *   # before 5.6.6, use :: rather than *
   

   
   

2. mysql> CREATE USER 'ipv6user'@'::1' IDENTIFIED BY 'ipv6pass';
   

   
   

3. 
   

   shell> mysql -h ::1 -u ipv6user -pipv6pass
   

4. 
   

   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

1.  --bind-address 

   [mysqld]
   bind-address = *   # before 5.6.6, use :: rather than *
   

   
   

2. 创建用户

   mysql> CREATE USER 'remoteipv6user'@'2001:db8:0:f101::2' IDENTIFIED BY 'remoteipv6pass';
   

3. 客户端连接

   shell> mysql -h 2001:db8:0:f101::1 -u remoteipv6user -premoteipv6pass
   

4. 
   

   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 服务器端帮助

fill_help_tables.sql   在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 关闭过程

1. 初始化

2. 如有必要，创建关闭进程

3. 拒绝新连接

4. 终止当前活动

5. 服务器关闭或者关闭存储引擎

6. 服务器退出

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; 

execute  mysqladmin  with a  flush-logs  or  refresh  argument; 

execute  mysqldump  with a  --flush-logs  or  --master-data  

2.1 选择General Query and Slow Query 日志输出位置

可能位置：  log files or   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  

运行时控制：

 global  log_output  

global general_log and slow_query_log variables

global general_log_file and slow_query_log_file variables

session sql_log_off variable

SHOW 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.3 General Query日志

记录接收到的顺序

2.4 Binary Log

记录潜在引起数据库改变

 --log-bin[=base_name] 默认 pid-file的值

--log-bin=base_name.extension    extension被忽略

--log-bin-index[=file_name]

sql_log_bin

row-based logging, statement-based logging and mixed-base logging.

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  

  log_queries_not_using_indexes

  --slow_query_log[={0|1}] .

  --slow_query_log_file=file_name . 

  --log-output     默认   host_name-slow.log.

--log-short-format

Administrative statements include  ALTER TABLE ,  ANALYZE TABLE ,  CHECK TABLE ,  CREATE INDEX ,  DROP INDEX ,  OPTIMIZE TABLE , and  REPAIR TABLE .

2.6 The DDL Log

data definition statements

2.7 Server Log Maintenance

mysql-log-rotate  script （ Linux (Red Hat) installation）

expire_logs_days  system variable

PURGE BINARY LOGS  statement

start using new log files：

FLUSH LOGS  statement 

  mysqladmin flush-logs ,  mysqladmin refresh ,  mysqldump --flush-logs , or mysqldump --master-data  command.

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

1. 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
   

2. 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:\> 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

  mysqld --remove  

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

CMAKE_INSTALL_PREFIX  

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

3.4 Using Client Programs in a Multiple-Server Environment

--host=host_name --port=port_number

--host=127.0.0.1   --port=port_number

--host=localhost --socket=file_name

--protocol=TCP  

--protocol=SOCKET

--protocol=PIPE  

--protocol=MEMORY

mysql_real_connect()  

mysql_options()

4 Tracing mysqld Using DTrace

The MySQL Architecture Using Pluggable Storage Engines

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