mysql ocp 题库部分解析

question 1:

知识点:

(1).set global sql_skip_slave_counter=1   使slave跳过1个错误事务

5.6版本之后会报错，因为是通过GTID来进行复制的，也需要跳过这个事务从而继续复制，这个事务可以到主上的binlog里面查看：因为不知道找哪个GTID上出错，所以也不知道如何跳过哪个GTID。但在show slave status里的信息里可以找到在执行Master里的POS

(2).生成gtid ，

set session gtid_next='4e659069-3cd8-11e5-9a49-001c4270714e:1'; 

注入空事务

begin; commit;

然后改回默认值

gtid_next = "AUTOMATIC" 默认值

question 4：

知识点:

Numeric Type Storage Requirements

Data Type	Storage Required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT ,  INTEGER	4 bytes
BIGINT	8 bytes
FLOAT( p )	4 bytes if 0 <=  p  <= 24, 8 bytes if 25 <=  p  <= 53
FLOAT	4 bytes
DOUBLE [PRECISION] ,  REAL	8 bytes
DECIMAL( M , D ) ,  NUMERIC( M , D )	Varies; see following discussion
BIT( M )	approximately ( M +7)/8 bytes

question 6：

A错，load data infile针对的是select ... into oufile输出的表数据文件，其文件中不含有插入执行语句，仅含有数据。而mysqldump导出的文件包含的数据是以可执行sql语句实现的。

B错, 漏掉了<  正确应该是:

Shell> mysql –u root –p sakila < sakila2013.sql

C错，因为mysqlimport是类似于load data infile语句功能的shell命令行工具，因此对应倒入的文件都应该是非sql语句执行的纯表数据文件。

E错. mysql命令项使用中，短项使用单横杠，长命令项使用双横杠 -silent项应该时候双横杠，因此错。

question 9 :

分析：这是一个读密集型数据库，数据库会在一段时间后刷新，但是其查询的结果集大小波动不大。而所有结果集都在Query Cache中，且网页应用使用一套有限的查询语句。且Query Cache hit rate很高。

因此A，D错，请求通过的应用查询，查询语句数量有限，结果集都能放在Query Cache中，相同查询语句的请求不会增多Query Cache中的资源的占用，因此清理查询并非主要矛盾。

B也错，因此Query_cache_min_res_unit设置过大，仅会造成Query Cache中碎片过多。如果请求的结果集都能在Query Cache中，这就和碎片没什么关系了。

C正确，尽管官方文档中未大量解释Query Cache Mutex争用问题，在线程运行查询语句时，会在Query Cache中先获取Mutex锁，之后开始查询匹配的查询语句和结果集。如果找到后返回结果。

如果未找到匹配，在执行查询后，需要将查询语句和结果集插入Query Cache中，这也会需要获取锁。尽管这个时间所需非常短，但是在读密集的情况下，资源争用会导致线程排队等待现象。

知识点:

(1).查询缓存并没有能起到提升性能的做用

如果何配置查询缓存：

　　query_cache_type 这个系统变量控制着查询缓存工能的开启的关闭。

　　query_cache_type=0时表示关闭，1时表示打开，2表示只要select 中明确指定SQL_CACHE才缓存。

　　这个参数的设置有点奇怪，1、如果事先查询缓存是关闭的然而用 set @@global.query_cache_type=1; 会报错

　　ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

　　2、如果事先是打开着的尝试去闭关它，那么这个关闭也是不完全的，这种情况下查询还是会去尝试查找缓存。

　　最好的关闭查询缓存的办法就是把my.cnf 中的query_cache_type=0然后再重启mysql。

查询缓存相关的系统变量：

　　have_query_cache　　表示这个mysql版本是否支持查询缓存。

　　query_cache_limit　　 表示单个结果集所被允许缓存的最大值。

　　query_cache_min_res_unit　　每个被缓存的结果集要占用的最小内存。

　　query_cache_size　　用于查询缓存的内存大小。

如何监控查询缓存的命中率： 

　　Qcache_free_memory　　查询缓存目前剩余空间大小。

　　Qcache_hits　　　　　     查询缓存的命中次数。

　　Qcache_inserts　　　　　 查询缓存插入的次数。

　　也就是说缓存的命中率为 Qcache_hits/(Qcache_hits+Qcache_inserts)

(2).

mysql使用总内存 = global_buffers + all_thread_buffers

global_buffers ( 全局内存分配总和 ) =

innodb_buffer_pool_size -- InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等

+innodb_additional_mem_pool_size -- InnoDB数据字典额外内存，缓存所有表数据字典

+innodb_log_buffer_size -- InnoDB REDO日志缓冲，提高REDO日志写入效率

+key_buffer_size -- MyISAM表索引高速缓冲，提高MyISAM表索引读写效率

+query_cache_size -- 查询高速缓存，缓存查询结果，提高反复查询返回效率

+table_cahce -- 表空间文件描述符缓存，提高数据表打开效率

+table_definition_cache -- 表定义文件描述符缓存，提高数据表打开效率

all_thread_buffers (会话/线程级内存分配总和) =

max_threads(当前活跃连接数) * (

read_buffer_size -- 顺序读缓冲，提高顺序读效率

+read_rnd_buffer_size -- 随机读缓冲，提高随机读效率

+sort_buffer_size -- 排序缓冲，提高排序效率

+join_buffer_size -- 表连接缓冲，提高表连接效率

+binlog_cache_size -- 二进制日志缓冲，提高二进制日志写入效率

+tmp_table_size -- 内存临时表，提高临时表存储效率

+thread_stack -- 线程堆栈，暂时寄存SQL语句/存储过程

+thread_cache_size -- 线程缓存，降低多次反复打开线程开销

+net_buffer_length -- 线程持连接缓冲以及读取结果缓冲

+bulk_insert_buffer_size ) -- MyISAM表批量写入数据缓冲

(3). 内存碎片

query_cache_min_res_unit    查询缓存分配的最小块的大小(字节)，默认为4k

query_alloc_block_size    为查询分析和执行过程中创建的对象分配的内存块大小

Qcache_free_blocks    代表内存自由块的多少，反映了内存碎片的情况

==========================

1）当查询进行的时候，Mysql把查询结果保存在qurey cache中，但如果要保存的结果比较大，超过query_cache_min_res_unit的值 ，这时候mysql将一边检索结果，一边进行保存结果，所以，有时候并不是把所有结果全部得到后再进行一次性保存，而是每次分配一块 query_cache_min_res_unit 大小的内存空间保存结果集，使用完后，接着再分配一个这样的块，如果还不不够，接着再分配一个块，依此类推，也就是说，有可能在一次查询中，mysql要 进行多次内存分配的操作。

2）内存碎片的产生。当一块分配的内存没有完全使用时，MySQL会把这块内存Trim掉，把没有使用的那部分归还以重 复利用。比如，第一次分配4KB,只用了3KB，剩1KB，第二次连续操作，分配4KB，用了2KB，剩2KB，这两次连续操作共剩下的 1KB+2KB=3KB，不足以做个一个内存单元分配， 这时候，内存碎片便产生了。

3）使用flush query cache，可以消除碎片

4）如果Qcache_free_blocks值过大，可能是query_cache_min_res_unit值过大，应该调小些

5）query_cache_min_res_unit的估计值：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

question 10:

MySQL-mysql_config_editor安全登录工具

mysql_config_editor出现在mysql5.6.6以后的版本，可以给指定的连接和密码生成一个加密文件.mylogin.cnf，默认位于当前用户家目录下。通过该文件可以使用mysql、mysqladmin等直接登录，避免明文密码出现在脚本中。

notice：使用该特性要求当前主机的mysql版本在5.6.6版本及以上，对将要登陆的mysql服务器版本没有要求。

Usage：

生成加密文件：

[root@master ~]# mysql_config_editor set --login-path=jjscj --host=192.168.1.190 --user=jjscj --password 

Enter password:

[root@master ~]# ll ~/.mylogin.cnf 

-rw------- 1 root root 248 Aug 28 14:58 /root/.mylogin.cnf

使用加密文件登录：

[root@master ~]# mysql --login-path=jjscj 

Welcome to the MySQL monitor.

查看当前主机上的加密文件：

[root@master ~]# mysql_config_editor print --all 

[local] 

user = root 

password = ***** 

host = localhost 

[jjscj] 

user = jjscj 

password = ***** 

host = 192.168.1.190

[remote]

user = jjscj

password = *****

host = 192.168.1.190

删除某个加密登陆：

[root@master ~]# mysql_config_editor remove --login-path=remote 

[root@master ~]# mysql_config_editor print --all 

[local] 

user = root 

password = ***** 

host = localhost 

[jjscj] 

user = jjscj 

password = ***** 

host = 192.168.1.190

重置所有：

[root@master ~]# mysql_config_editor reset 

question 11：

PURGE { BINARY | MASTER } LOGS

{ TO 'log_name' | BEFORE datetime_expr }

PURGE BINARY LOGS TO 'mysql-bin.000002';

PURGE BINARY LOGS BEFORE '2014-04-28 23:59:59';

question 12:

知识点:

innodb_autoinc_lock_mode = 0 (传统锁模式)

保持了MySQL 5.1版本中相同的行为, 向后兼容.

在这种锁模式下, 所有"INSERT"语句在插入表AUTO-INCREMENT列时获取表级别的AUTO-INC锁, 该锁会持有到语句执行结束(而非事务结束), 确保auto-increment值以可预期, 可重复, 连续的序列顺序分配给INSERT语句

在SBR主备同步模式下, 可以保证同一条SQL语句复制到备库时可以产生和主库相同的auto-increment值. Multiple-INSRT语句在备库执行产生确定性的结果, 就如在主库上执行的一样. 如果Multiple-INSRT语句产生的auto-increment值是交错的, 那么并发的两条INSERT语句将产生不确定性的结果, 那么也就不能可靠的使用SBR模式复制主备数据同步.

innodb_autoinc_lock_mode = 1 (连续锁模式)

这是InnoDB默认的锁模式.

"Bulk inserts"使用特殊的AUTO-INC table-level lock, 并且持有锁到语句结束. 包括所有的INSERT...SELECT, REPLACE...SELECT, 以及LOAD DATA语句. 同一时刻只有一个语句可以持有AUTO-INC table-level lock.

"Simple inserts"避免le使用table-level AUTO-INC lock, 而是使用互斥锁(mutex, 更轻量级锁)控制获取需要的auto-increment值, 只有在分配auto-increment值期间持有, 并不是语句执行结束. 如果有事务持有table-level AUTO-INC lock, 那么"Simple inserts"将会向"Bulk inserts"一样等待AUTO-INC lock.

这个锁模式确保了所有"INSER-like"语句产生连续的auto-increment值(包括哪些事先不确定插入行数的"INSERT"语句), 这些操作在SBR模式数据复制时都是安全的.

简单来说, 这个锁模式明显的提升了在使用SBR复制时的可扩展性以及安全性. 更深入的, 像"传统锁模式"那样对于特定的SQL语句分配的auto-increment值是连续的.

一个例外是"Mixed-mode inserts", 用户指定了一些auto-increment值, 有些则没有指定, "Simple inserts"插入多行数据. 对这些插入, InnoDB分配了比插入行数更多的auto-increment值. 所有auto-increment自动的连续产生(所有比最近之前执行的语句的auto-increment值大), 剩余没用的auto-increment值就忽略(丢失)不用了.

innodb_autoinc_lock_mode = 2 (交错锁模式)

这个锁模式下, 所有"INSERT-like"语句不使用table-level AUTO-INC lock, 同一时刻SQL语句可以并发执行. 这是最快的, 更高扩展性的锁模式, 但是在使用SBR复制或者恢复场景中回放binary log时却是不安全的.

这个锁模式下, auto-increment值在所有并发执行的"INSERT-like"语句中保持唯一以及单调增长. 同一时刻多条SQL语句产生的交错的auto-increment值.

如果只有"Simple inserts"执行, 那么将不会产生的间隙的auto-increment值(排除"Mixdex-mode inserts"); 当执行"Bulk-inserts"时, 任何执行的SQL都可能产生间隙的auto-increment值.

详细请参考:  http://www.cnblogs.com/renolei/p/5559135.html

question 19:

知识点:

Default-authentication-plugin

mysql_native_password（使用MySQL本地密码）和sha256_password（用SHA-256 密码）

添加该参数后,原有用户不受影响，任何创建新账号的语句，如果没有加密的密码哈希值用于要求hash 格式的默认身份认证插件，该语句执行将失败。

CREATE USER ... IDENTIFIED BY 'encrypted password'; GRANT ... IDENTIFIED BY 'encrypted password';

注意：如果你使用该选项修改默认身份认证方法而不是mysql_native_password，MySQL5.6.6之前版本的客户端不能连接。因为它们不明白身份认证协议的改变。

question 21:

知识点:

mysql_secure_installation

编译安装完mysql5.6，如果用于生产环境，最好执行mysql_secure_installation来做一些常规化安全设置。

需要提前将~mysql/bin加入环境变量

/apps/mysql/bin/mysql_secure_installation

(1).提示输入密码，没有密码就直接回车

(2).提示设置root user密码  Y

(3).生产环境建议删除系统创建的匿名用户  Y

(4).禁止root用户远程登录  Y

(5).删除test数据库  Y

(6).重载权限表  Y

question 24:

知识点:

MEMORY 存储引擎只包含.frm表结构文件。 数据保存在内存中。重启后数据将会丢失。

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。 

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

question 29:

知识点：

(1).flush logs

主要是关闭当前的二进制日志文件并创建一个新文件，新的二进制日志文件的名字在当前的二进制文件的编号上加1。

命令需要reload权限

在mysql中flush logs操作会生成一个新的binlog文件，如果在slave库执行则同时会生成一个新的replay log

当删除slow log或者general log,然后执行flush logs，此时会再重新生成一个新的slow log或者general log

(2).其他flush 命令

flush hosts 主要是用来清空主机缓存表。如果你的某些主机改变IP数字，或如果你得到错误消息Host … isblocked，你应该清空主机表。当在连接MySQL服务器时，对一台给定的主机有多于 max_connect_errors个错误连续不断地发生，MySQL为了安全的需要将会阻止该主机进一步的连接请求。清空主机表允许主机再尝试连接。

flush privileges 主要是每当重新赋权后，为了以防万一，让新权限立即生效，一般都执行一把，目地是从数据库授权表中重新装载权限到缓存中。

flush tables 主要是关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

flush tables with read lock 主要是关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。

flush status 重置大多数状态变量到0。

flush master 删除所有的二进制日志索引文件中的二进制日志文件，重置二进制日志文件的索引文件为空，创建一个新的二进制日志文件,不过这个已经不推荐使用，改成reset master 了。可以想象，以前自己是多土啊，本来一条简单的命令就可以搞定的，却要好几条命令来，以前的做法是先查出来当前的二进制日志文件名，再用purge 操作。

flush query cache 重整查询缓存，消除其中的碎片，提高性能，但是并不影响查询缓存中现有的数据，这点和Flush table 和Reset Query  Cache（将会清空查询缓存的内容）不一样的。

flush slave  类似于重置复制吧，让从数据库忘记主数据库的复制位置，同时也会删除已经下载下来的relay log,与Master一样，已经不推荐使用，改成Reset Slave了。

question 30:

知识点：

frm、MYD、MYI是myisam引擎表的结构文件,数据文件,索引文件

备份过程参考： http://blog.itpub.net/15412087/viewspace-2152876/

question 31:

A  在INNODB共享表空间上统计和总结表页面非常耗时

B  收集信息需要多种多样的磁盘级操作，这个非常耗时

C  查询语句中的聚合函数功能在收集来自各个存储引擎缓存细节从而得到结果的过程非常耗时

D  收集信息需要大量的来自不同schema下的表的锁，从而引发争议点

question 34:

B --skip-networking 开启后将关闭tcp/ip 不能远程访问mysql

D 为安全考虑希望指定的IP访问MySQL，可以在配置文件中增加bind-address=IP，前提是关闭skip-networking

question 39:

知识点：

(1).master.info 和 relay.info 分别记录接收主库binlog的位置，和从库应用binlog的位置

(2).relay index file 记录当前relay-log的文件位置

(3).slave_master_info 和 slave_relay_log_info Table作用等同于（1）的两个文件。可以避免写文件的压力。

question 40:

/etc/init.d/mysql stop

mysqladmin -u root -p shutdown

net stop mysql

question 41:

知识点：

DELAY_KEY_WRITE是指在表关闭之前，将对表的update操作只更新数据到磁盘，而不更新索引到磁盘，把对索引的更改记录在内存。（这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行）

        这样MyISAM表可以使索引更新更快。在关闭表的时候一起更新索引到磁盘。

场景：表有update操作，duang duang的体现出优势了。因为这个参数能延迟更新索引到表关闭。经常更新一个大表的时候，可以用这个参数

小提示：当DELAY_KEY_WRITE使用的时候，如果出现重启或者掉电啥的情况，会导致在cache的索引update没来得及更新，所以必须在启动参数加上 --myisam-recover，这样在你启动mysql的时候会检查你的表并同步表和索引.或者在重启服务器之前运行myisamchk。使用该特性，应用--myisam-recover选项启动服务器，为所有MyISAM表添加自动检查。

question 45:

知识点：

validate-password=ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 决定是否使用该插件(及强制/永久强制使用)。

validate_password_dictionary_file：插件用于验证密码强度的字典文件路径。

validate_password_length：密码最小长度。

validate_password_mixed_case_count：密码至少要包含的小写字母个数和大写字母个数。

validate_password_number_count：密码至少要包含的数字个数。

validate_password_policy：密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG。

validate_password_special_char_count：密码至少要包含的特殊字符数。

其中，关于validate_password_policy-密码强度检查等级：

0/LOW：只检查长度。

1/MEDIUM：检查长度、数字、大小写、特殊字符。

2/STRONG：检查长度、数字、大小写、特殊字符字典文件。

question 47:

直直接运行mysqld程序来启动MySQL服务的方法很少见，mysqld_safe脚本会在启动MySQL服务器后继续监控其运行情况，并在其死机时重新启动它。用mysqld_safe脚本来启动MySQL服务器的做法在BSD风格的unix系统上很常见，非BSD风格的UNIX系统中的 mysql.server脚本其实也是调用mysqld_safe脚本去启动MySQL服务器的。

question 48:

知识点：

SQL_MODE可能是比较容易让开发人员和DBA忽略的一个变量，默认为空。SQL_MODE的设置其实是比较冒险的一种设置，因为在这种设置下可以允许一些非法操作，比如可以将NULL插入NOT NULL的字段中，也可以插入一些非法日期，如“2012-12-32”。因此在生产环境中强烈建议开发人员将这个值设为严格模式，这样有些问题可以在数据库的设计和开发阶段就能发现，而如果在生产环境下运行数据库后发现这类问题，那么修改的代价将变得十分巨大。此外，正确地设置SQL_MODE还可以做一些约束(Constraint)检查的工作。

　　对于SQL_MODE的设置，可以在MySQL的配置文件如my.cnf和my.ini中进行，也可以在客户端工具中进行，并且可以分别进行全局的设置或当前会话的设置。下面的命令可以用来查看当前SQL_MODE的设置情况。

STRICT_ALL_TABLES：对所有引擎的表都启用严格模式。(STRICT_TRANS_TABLES只对支持事务的表启用严格模式)。

question 49:

A 修复innodb存储引擎，参考 https://blog.csdn.net/l1028386804/article/details/77199194

B&C CHECK TABLE & REPAIR TABLE用于修复被破坏的表。默认情况下，REPAIR TABLE与myisamchk --recovertbl_name具有相同的效果。REPAIR TABLE对MyISAM和ARCHIVE表起作用。

question 58:

知识点:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

视图定义服从下述限制：

·SELECT语句不能包含FROM子句中的子查询。

·SELECT语句不能引用系统或用户变量。

·SELECT语句不能引用预处理语句参数。

·在存储子程序内，定义不能引用子程序参数或局部变量。

·在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。

·在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

·在视图定义中命名的表必须已存在。

·不能将触发程序与视图关联在一起。

可选的ALGORITHM子句是对标准SQL的MySQL扩展。ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。

对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果不具有该关系。必须使用临时表取而代之。如果视图包含下述结构中的任何一种，将失去一对一的关系：

·聚合函数（SUM(), MIN(), MAX(), COUNT()等）。

·DISTINCT

·GROUP BY

·HAVING

·UNION或UNION ALL

·仅引用文字值（在该情况下，没有基本表）。

某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：

·聚合函数（SUM(), MIN(), MAX(), COUNT()等）。

·DISTINCT

·GROUP BY

·HAVING

·UNION或UNION ALL

·位于选择列表中的子查询

·Join

·FROM子句中的不可更新视图

·WHERE子句中的子查询，引用FROM子句中的表。

·仅引用文字值（在该情况下，没有要更新的基本表）。

·ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

关于可插入性（可用INSERT语句更新），如果它也满足关于视图列的下述额外要求，可更新的视图也是可插入的

question 64:

默认，所有的临时表都是被复制的，无论是否匹配--replicate-do-db, --replicate-do-table, or --replicate-wild-do-table，复制临时表都会发生。但是，--replicate-ignore-table 和 --replicate-wild-ignore-table 两个选项是用来忽略临时表的。

如果你不想复制某些临时表，请使用--replicate-wild-ignore-table 选项。如：--replicate-wild-ignore-table=foo%.bar%,意思是告诉slave线程不要复制匹配以foo开头和以bar开头的表。

question 66:

select into outfile用法

SELECT ... FROM TABLE_A
INTO OUTFILE "/path/to/file"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

load data infile用法

LOAD DATA INFILE "/path/to/file" INTO TABLE table_name;
注意：如果导出时用到了FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'语句，那么LODA时也要加上同样的分隔限制语句。还要注意编码问题。

mysqlimport用法

mysqlimport -h 172.16.145.125 -u ocetl -pocetl test  --fields-terminated-by='|' '/home/ocetl/tmp_user_info.txt' --columns='user_id,user_name,user_age,user_addr' --local
-h  mysql ip地址
-u 用户
-p 用户名密码
注意：-p与密码之间不能有空格
test 数据库名称
--fields-terminated-by  文件中字段之间的分隔符
/home/ocetl/tmp_uesr_info.txt    文件在linux的本地路径
--columns   要加载文件到表的字段名
--local ：从本地客户端读入输入文件。

question 67:

知识点：

MySQL中内存分为全局内存和线程内存两大部分 ：

per_thread_buffers=(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size
+tmp_table_size)*max_connections
global_buffers=
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size
total_memory=global_buffers+per_thread_buffers

全局缓存：
key_buffer_size： 决定索引处理的速度，尤其是索引读的速度。默认值是16M，通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用'key_read%'获得用来显示状态数据）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值'created_tmp_disk_tables'得知详情。

innodb_buffer_pool_size： InnoDB使用该参数指定大小的内存来缓冲数据和索引，这个是Innodb引擎中影响性能最大的参数。

innodb_additional_mem_pool_size：指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是8M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。

innodb_log_buffer_size： 指定InnoDB用来存储日志数据的缓存大小，如果您的表操作中包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请尽量调高此项值，以提高日志效率。

query_cache_size： 是MySQL的查询缓冲大小。（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。通过检查状态值'Qcache_%'，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

线程缓存
每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了。

read_buffer_size： 是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

sort_buffer_size： 是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。

read_rnd_buffer_size： 是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

tmp_table_size： 是MySQL的临时表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果。

thread_stack ： 主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。 

join_buffer_size： 应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作，然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。

binlog_cache_size： 在事务过程中容纳二进制日志SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存，注意，是每个Client 都可以分配设置大小的binlog cache 空间。如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。当然，我们可以通过MySQL 的以下两个状态变量来判断当前的binlog_cache_size 的状况：Binlog_cache_use 和Binlog_cache_disk_use。“max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是binlog 能够使用的最大cache 内存大小。当我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的错误。
其中需要注意的是：table_cache表示的是所有线程打开的表的数目，和内存无关。

question 69:

知识点：

https://www.jianshu.com/p/926169bbd544

question 70:

知识点：

log_output=‘FILE‘表示将日志存入文件,默认值是‘FILE‘　log_output=‘TABLE‘表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中.

mysql数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output=‘FILE,TABLE‘.日志记录到系统专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查日志,又需要比够获得更高的系统性能,那么建议优先记录到文件.

question 71:

参考： http://blog.itpub.net/15412087/viewspace-2152193/

question 72:

知识点：

页级:引擎 BDB。
表级:引擎 MyISAM 和 MEMORY， 理解为锁住整个表，可以同时读，写不行
行级:引擎 INNODB ， 单独的一行记录加锁

上述三种锁的特性可大致归纳如下：
1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

question 74:

知识点：

Mysql general日志

记录所有执行过的语句，但是开启后对于数据库服务器的压力影响比较严重.不太建议搭建日常开启该日志,在某些情况下,比如统计汇总SQL，审计可以考虑暂时性的开启general log，否则容易出现问题.

Mysql slow 日志

记录执行超过时间阈值的SQL语句,用来判定执行比较慢的sql

binlog

不记录 select show 等查询语句,记录dml ddl等数据库变动的语句.

question 75:

知识点：

DRBD(DistributedReplicatedBlockDevice)是一个基于块设备级别在远程服务器直接同步和镜像数据的软件，用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。它可以实现在网络中两台服务器之间基于块设备级别的实时镜像或同步复制(两台服务器都写入成功)/异步复制(本地服务器写入成功)，相当于网络的RAID1，由于是基于块设备(磁盘，LVM逻辑卷)，在文件系统的底层，所以数据复制要比cp命令更快。DRBD已经被MySQL官方写入文档手册作为推荐的高可用的方案之一。

question 82:

--order-by-primary

如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。

question 83:

知识点：

The  SUPER  privilege enables these operations and server behaviors:

• Enables configuration changes by modifying global system variables. For some system variables, setting the session value also requires the  SUPER  privilege; if so, it is indicated in the variable description. Examples include  binlog_format ,  sql_log_bin , and  sql_log_off .

• Enables changes to global transaction characteristics (see  Section 13.3.6, “SET TRANSACTION Syntax” ).

• Enables starting and stopping replication on slave servers.

• Enables use of the  CHANGE MASTER TO  statement.

• Enables binary log control by means of the  PURGE BINARY LOGS  and  BINLOG  statements.

• Enables setting the effective authorization ID when executing a view or stored program. A user with this privilege can specify any account in the  DEFINER  attribute of a view or stored program.

• Enables use of the  CREATE SERVER ,  ALTER SERVER , and  DROP SERVER  statements.

• Enables use of the  mysqladmin debug  command.

• Enables reading the DES key file by the  DES_ENCRYPT()  function.

• Enables control over client connections not permitted to non- SUPER  accounts:

  • Enables use of the  KILL  statement or  mysqladmin kill  command to kill threads belonging to other accounts. (You can always kill your own threads.)

  • The server accepts one connection from a  SUPER  client even if the connection limit controlled by the max_connections  system variable is reached.

  • Updates can be performed even when the  read_only  system variable is enabled. This applies to table updates and use of account-management statements such as  GRANT  and  REVOKE .

  • The server does not execute  init_connect  system variable content when  SUPER  clients connect.

question 87:

知识点：

tmp_table_size

它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下

max_heap_table_size

这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#

,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。

这个变量和tmp_table_size一起限制了内部内存表的大小。

*你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数（Created_tmp_disk_tables和Created_tmp_tables），一般的比例关系是:

Created_tmp_disk_tables/Created_tmp_tables<5%

question 88:

知识点：

参考： https://www.cnblogs.com/mysql-dba/p/7061300.html

question 92:

知识点：

max_user_connections：限制每个用户的session连接个数，例如max_user_connections=1 ，那么用户u1只能连接的session数为1，如果还有用户u2，还是可以连接，但是连接数仍然为1
max_connections ：是对整个服务器的用户限制，整个服务器只能开这么多session，而不考虑用户！

question 93:

知识点：

参考： https://www.cnblogs.com/yycc/p/7338894.html

question 96:

知识点：

mysqldump --quick

不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项

mysqldump --single-transaction

备份时保持一致性

mysqldump --tab

为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。

question 97:

知识点：

mysqldump --master-data

导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1

当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-2168573/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-2168573/