mysql书籍摘要_mysql文档摘要

以下是阅读mysql官方文档前五章的笔记：

mysql的batch mode：shell> mysql < batch-file 或者 mysql -e "sql" or "source batch-file".

mysql的四种启动方式：1、 mysqld also known as MySQL Server, is the main program that does most of the work in a MySQL installation

2、mysqld_safe在unix下官方推荐的启动模式,在启动时会读取文件的[mysqld]、[server]、[mysqld_safe]部分选项

3、mysql.server unix环境，它是以mysqld_safe来启动的，在启动时会读取文件的[mysqld]、[mysql.server]部分选项

4、mysqld_multi 启动多个mysqld实例

mysql变量分为global、session两种类型，global针对所有的client(设置后启动的),session针对当前回话的链接

back_log:tcp/ip请求的等待队列长度，主要针对短时间大量请求的实例需要增加数值

To refer to a component of a structured variable instance, you can use a compound name ininstance_name.component_name format. Examples:

hot_cache.key_buffer_size

hot_cache.key_cache_block_size

cold_cache.key_cache_block_size

For each structured system variable, an instance with the name of default is always predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus,default.key_buffer_size and key_buffer_size both refer to the same system variable.

mysql status variables:列出mysql所有的状态变量可以分global、session。

Com_xxx开头的一般是记录xxx语句执行的次数。

key_read_requests:The number of requests to read a key block from the MyISAM key cache.

key_reads:The number of physical reads of a key block from disk into the MyISAM key cache.If Key_reads is large, then yourkey_buffer_size value is probably too small. The cache miss rate can be calculated asKey_reads/Key_read_requests.

opened_tables:The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

Innodb_buffer_pool_reads:The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

Innodb_buffer_pool_wait_free:Normally, writes to the InnoDB buffer pool happen in the background. When InnoDB needs to read or create a pageand no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This counter counts instances of these waits. If innodb_buffer_pool_size has been set properly, this value should be small.

mysql的sql mode:在mysql 5.6.6以前sql_mode的值是空，5.6.6以后是NO_ENGINE_SUBSTITUTION

mysql_install_db启动的时候，在安装目录会生成my.cnf文件，其中包含了sql_mode参数

NO_ENGINE_SUBSTITUTION：在指定的存储引擎不存在时会抛出错误，如果没有这个参数，会采用默认的引擎，并且抛出警告

Combination SQL Modes:包含ANSI、DB2等。

在严格模式下：在not null定义下出现null、超出值范围等都会抛出error。

mysql log:1、general query log 2、error log 3、binary log 4、slow log 5、DDL log

binary log:主要用于复制、恢复数据 log_format包含三种格式statment、row、mixed

statment：基于sql语句 row：基于数据库行 mixed：前面两种的混合

slow log:查询语句超出了long_query_time的设置，或者

log_slow_admin_statements:打开该参数可以针对一些入alter table create table等语句进行根据。

The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE.