— 影响性能的因素;
1.环境问题,MySQL的性能受主机的性能特征影响:
1.CPU速度和数量;
2.磁盘I/O;
3.网络性能;
4.操作系统争用;
2.MySQL配置:
1.数据库设计:索引,数据类型(合适且尽可能小),标准化;
2.应用程序性能:特定请求(仅请求特定的行和列),短时事务(根据主键访问的短事务);
3.配置变量:调整缓冲区,高速缓存,InnoDB设置;

— 监视;
1.要调节服务器的性能,必须了解其性能特征;可以针对整体性能进行基准测试,也可以使用日志和EXPLAIN逐个分析事件或者使用ERFORMANCE_SCHEMA按组分析事件;
2.基准测试:
1.mysqlslap:是标准MySQL分发的一部分,是一个基准测试工具,用来模拟MySQL服务器实例上客户机负载,并显示每个阶段的计时信息;
2.sql-bench:是MySQL源码分发的一部分,它是一系列Perl脚步,用于执行多个语句和手机状态计时数据;
3.分析:
1.日志:
1.一般查询日志;
2.慢查询日志;
2.语句:
1.EXPLAIN;
2.PROCEDURE ANALYSE;
3.SHOW STATUS:还可以使用mysqladmin extended-status查看;
4.PERFORMANCE_SCHEMA数据库;

— 性能模式;
“性能模式”是在较低级别监视MySQL服务器执行情况的功能:
1.该功能是使用PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现的;
2.性能模式监视并允许您检查MySQL服务器中的被检测代码的性能特征:
1.开发者将检测函数和其他编码事件以收集计时信息;
2.公开的性能数据对以下人员或任务很有帮助:
1.MySQL代码库的贡献者;
2.插件开发者;
3.识别低级别的性能瓶颈,如日志文件I/O等待或缓冲池互斥;
3.从Oracle下载的所有二进制版本的MySQL中都提供了性能模式;默认情况下,将启用性能模式,并在服务器启动时使用performance_schema变量对其进行控制;
mysql> SHOW VARIABLES LIKE ‘performance_schema’;
mysql> SHOW TABLE STATUS FROM performance_schema\G

— 检测,实例,事件和使用者;
性能模式数据库包含配置和事件信息:
1.性能模式中的“检测”是服务器代码中引发要监视的事件的点;
1.每个检测由其类型/所属的模块/该特定检测的变量或类组成,通过查performance_schema.setup_instruments表可查看所有可用的检测;
2.性能模式将记录实例表中的每个检测实例;例如,以下查询显示检测wait/io/file/sql/FRM记录文件实例/var/lib/mysql/mem/tags.frm上的事件;
mysql> SELECT file_name, event_name FROM file_instances LIMIT 1\G *************************** 1. row ***************************
FILE_NAME: /var/lib/mysql/mem/tags.frm
EVENT_NAME: wait/io/file/sql/FRM
2.每个被检测的对象都是该检测的一个“实例”,记录在一系列实例表中;
3.当线程执行检测实例中的代码时,MySQL将识别所发生的“事件”,将其记录在事件和汇总表中;
4.每个“使用者”都是性能模式中表的名称,用于记录和查询事件以及事件的摘要,在SETUP_CONSUMERS表中进行配置;每个使用者的NAME是性能模式中用于查询事件和摘要的表的名称,被禁用的使用者不记录信息,从而节省了系统资源;
mysql> SELECT * FROM setup_consumers;
5.当MySQL识别了发生在检测实例中的事件后,会将其记录在事件表中:
1.主事件表为events_waits_current,该表中存储了每个线程最近的事件;
2.events_waits_history存储每个线程的最近10个事件;
3.events_waits_history_long共存储10,000个最近事件;
6.当使用性能模式识别瓶颈或其他问题时,请执行以下操作:
1.确保已针对适用于您所遇到的问题类型的一系列检测和使用者启用了性能模式;例如,如果您确定问题出在I/O限制上,请使用wait/io/file/*检测;如果不确定根本原因,请使用更广范围的检测;
2.运行用于产生该问题的测试用例;
3.查询events_waits_*表等使用者,尤其是使用适用的WHERE子句过滤器查询events_waits_history_long,以便进一步缩小问题原因的范围;
4.禁用那些用于评估已排除的问题的检测;
5.重试该测试用例;

— 一般数据库优化;
1.对数据进行标准化可以:
1.标准化是移除数据库中的冗余和不当依赖关系(以避免将相同的数据存储在多个地方以及出现异常的风险)的行为;
2.标准化通常会产生以下结果:许多表的列变少,整体存储要求降低,I/O需求降低以及单次插入,更新和删除操作加快;
3.这提高了频繁执行小更新的事务性工作负荷的性能,但会使检索大量数据的查询变得复杂;
2.选择正确的数据类型和大小可以:
1.选择正确的数据类型是表设计中一个很重要却常常被忽视的部分,数据类型的大小可能会对表操作产生较大的影响;例如,选择将SMALLINT数字存储为INT会使该列所需的空 间翻倍,在包含一百万个行的表中,该决策将导致浪费额外的2MB存储空间,并且磁盘操作速度会变慢,缓冲区和高速缓存将需要使用更多内存;
2.使用INSERT…COMPRESS(field_name)…和SELECT…UNCOMPRESS(column_name)…可以在存储和检索字符串数据时对其进行压缩和解压缩;尽管也可以使用CHAR或VARCHAR字段来实现此目的,但是通过使用VARBINARY或BLOB列存储压缩数据可以避免字符集转换出现问题;
3.创建最佳索引可以:
1.如果您通过在WHERE子句中指定一个字段来查询表中的特定行,并且该表没有为该字段创建索引,MySQL将读取该表中的每一行以找到每个匹配的行;这将导致很多不必要的磁盘访问,并且对于大型表性能将显著降低;
2.索引是有序的成组数据,通过索引,MySQL可以更容易地找到查询行的正确位置;默认情况下,InnoDB将按主键排列表的顺序,该有序表称为群集索引;
3.InnoDB表上的每个附加索引或辅助索引会在文件系统中占用额外的空间,因为索引包含索引字段的额外副本以及主键的副本;
4.每次使用INSERT/UPDATE/REPLACE/DELETE操作修改数据时,MySQL也必须更新所有包含修改字段的索引,因此,向表中添加多个索引会降低影响该表的数据修改操作的性能;
5.不过,如果对索引进行了适当设计,依赖于索引字段的查询便会在性能上有较大的获益;如果查询无法使用索引找到特定行,则必须执行全表扫描;即,必须读取整个表来找到该行;

— PROCEDURE ANALYSE;
1.PROCEDURE ANALYSE分析给定查询中的列,并提供对每个字段的调节反馈:mysql> SELECT CountryCode, District, Population FROM City PROCEDURE ANALYSE(250,1024)\G
2.提供参数来调节如何建议ENUM值:
1.处理列时所使用的最大元素数和最大内存;
2.示例:…PROCEDURE ANALYSE(100,256);
3.用于最大程度地减小字段大小:
1.MySQL通常按最大的字段大小分配内存;
2.隐式MEMORY临时表,排序缓冲区等;
4.用于确定字段是否允许NULL;

补充:
1.默认设置通常建议使用ENUM类型来优化表的设计,如果确定不想在分析列时使用PROCEDURE ANALYSE()所建议的ENUM值,请使用非默认参数:
1.第一个参数是分析ENUM值是否适当时要考虑的不同元素数,此参数的默认值为256;
2.第二个参数是用于收集不同的值以供分析的最大内存量,此参数的默认值为8192,表示8KB;如果为此参数设置值0,则PROCEDURE ANALYSE()无法检查不同的值以建议使用ENUM类型;
2.如果PROCEDURE ANALYSE()无法存储可接受范围内的候选ENUM值(在参数设置的限制内),则不会建议对该列使用ENUM类型;
3.本幻灯片中的示例建议对City.CountryCode列使用CHAR(3)类型;另一方面,如果使用默认参数,则PROCEDURE ANALYSE()将建议ENUM(‘ABW’,’AFG’,…,’ZMB’,’ZWE’),这是一种包含超过200个元素的ENUM类型,其中针对每个相应的CountryCode值都包含一个不同值;

— EXPLAIN;
1.EXPLAIN命令:
1.描述MySQL打算如何执行特定的SQL语句;
2.不返回数据集的任何数据;
3.提供有关MySQL打算如何执行该语句的信息;
2.使用EXPLAIN可检查SELECT/INSERT/REPLACE/UPDATE/DELETE语句;
3.将EXPLAIN置于语句之前:
1.EXPLAIN SELECT …;
2.EXPLAIN UPDATE…;

补充:
1.EXPLAIN将为语句中使用的每个表生成一行输出,该输出包含以下列:
1.id:编号;若没有子查询和联合查询,id则都是1;MySQL会按照id从大到小的顺序执行query,在id相同的情况下,则从上到下执行;
2.select_type:查询中使用的选择类型;
3.table:输出的行所引用的表;有时看到的是,其中N对应的是id列的值;
4.partitions:输出行所对应的表分区;
5.type:访问类型,连接的方式;
6.possible_keys:在查询过程中可能用到的索引;在优化初期创建该列,但在以后的优化过程中会根据实际情况进行选择,所以在该列列出的索引在后续过程中可能没用;该列为NULL意味着没有相关索引,可以根据实际情况看是否需要加索引;
7.key:访问过程中实际用到的索引;有可能不会出现在possible_keys中(这时可能用的是覆盖索引,即使query中没有where);possible_keys揭示哪个索引更有效,key是优化器决定哪个索引可能最小化查询成本,查询成本基于系统开销等总和因素,有可能是“执行时间”矛盾;如果强制mysql使用或者忽略possible_keys中的索引,需要在query中使用FORCE INDEX,USE INDEX或者IGNORE INDEX;
8.key_len:显示使用索引的字节数;由根据表结构计算得出,而不是实际数据的字节数;如ColumnA(char(3)) ColumnB(int(11)),在utf-8的字符集下,key_len=3*3+4=13;计算该值时需要考虑字符列对应的字符集,不同字符集对应不同的字节数;
9.ref:显示了哪些字段或者常量被用来和key配合从表中查询记录出来;显示那些在index查询中被当作值使用的在其他表里的字段或者constants
10.rows:估计为返回结果集而需要扫描的行;不是最终结果集的函数,把所有的rows乘起来可估算出整个query需要检查的行数,有limit时会不准确;
11.filtered:根据条件过滤的行百分比;
12.Extra:优化程序提供的每个查询的附加信息;
2.有关输出列的完整论述:http://dev.mysql.com/doc/refman/5.6/en/explain-output.html;
3.使用EXPLAIN EXTENDED…可查看优化程序提供的其他信息:http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html;

select_type列:
1.SIMPLE:简单查询,没有使用UNION和子查询;
EXPLAIN SELECT * FROM actor WHERE actor_id = 1;
2.PRIMARY:包含子查询或联合查询的query中,最外层的select查询;
3.UNION:在联合查询中第二个及其以后的select对应的类型;
EXPLAIN SELECT actor_id, last_update FROM film_actor UNION ALL SELECT actor_id, last_update FROM actor;
4.DEPENDENT UNION:子查询中的union,且为union中第二个select开始的后面所有select,同样依赖于外部查询的结果集;
EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor UNION ALL SELECT actor_id FROM film_actor);
5.UNION RESULT:从UNION临时表获取结果集合;
6.SUBQUERY:子查询在SELECT的目标里,不在FROM中,子查询中的第一个SELECT;
EXPLAIN SELECT (SELECT actor_id FROM actor) FROM film_actor;
7.DEPENDENT SUBQUERY:子查询中的第一个查询,依赖于外部查询的结果集;
8.DERIVED:子查询在from子句中,执行查询的时候会把子查询的结果集放到临时表(衍生表);
EXPLAIN SELECT * FROM (SELECT * FROM actor) AS a;
9.MATERIALIZED:物化子查询;
10.UNCACHEABLE SUBQUERY:表示子查询,但返回结果不能被cache,必须依据外层查询重新计算;
11.UNCACHEABLE UNION:表示union第二个或以后的select,但结果不能被cache,必须依据外层查询重新计算;

type列:访问路径(效率依次降低):
0.NULL:在优化过程中就已得到结果,不用再访问表或索引;
EXPLAIN SELECT * FROM actor WHERE actor_id = -1;
1.system:当表只有一行时就会出现system类型;是const join类型的特例;在没有任何索引的情况下,只有一条数据,MyISAM会显示system,InnoDB会显示ALL;
use test;
CREATE TABLE t_sys1(id INT) ENGINE = InnoDB;
CREATE TABLE t_sys2(id INT) ENGINE = MyISAM;
INSERT INTO t_sys1 VALUES(1);
COMMIT;
INSERT INTO t_sys2 VALUES(1);
EXPLAIN SELECT * FROM t_sys1;
EXPLAIN SELECT * FROM t_sys2;
2.const:最多会有一条记录匹配,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数;const表很快,因为它们只读取一次,发生在有一个unique key或者主键,并且where子句给它设定了一个比较值;
eg:EXPLAIN SELECT * FROM actor WHERE actor_id = 1;
3.eq_ref:在做关联查询中,前一个表返回的值在此表中只有一条记录对应,这是关联查询的最好选择;所以,唯一性索引(Primary or UNIQUE NOT NULL)才会出现eq_ref(非唯一性索引会出现ref),因为唯一,所以最多只返回一条记录,找到后无需继续查找,因此比ref更快;
CREATE TABLE t_eq_ref(actor_id SMALLINT(5) PRIMARY KEY);
INSERT INTO t_eq_ref VALUES(1);
COMMIT;
EXPLAIN SELECT * FROM actor, t_eq_ref WHERE actor.actor_id = t_eq_ref.actor_id;
4.ref:这是一种索引访问;只有当使用一个非唯一性索引或者唯一性索引的非唯一性前缀(换句话说,就是无法根据该值只取得一条记录)时才会发生,将索引和某个值相比较,这个值可能是一个常数,也可能是来自前一个表里的多表查询的结果值;如果使用的键仅仅匹配少量行,该联接类型是不错的;
EXPLAIN SELECT * FROM film_actor, actor WHERE film_actor.actor_id=actor.actor_id AND film_actor.actor_id = 1;
5.ref_or_null:类似ref,不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录,他意味着MySQL必须进行二次查找,在初次查找的结果中找出NULL条目;
6.index_merge:查询中使用两个或多个索引,然后对索引结果进行合并;在这种情况下,key列包含所有使用的索引,key_len包含这些索引的最长的关键元素;
7.unique_subquery:用来优化有子查询的in,并且该子查询是通过一个unique key选择的,子查询返回的字段组合是主键或者唯一索引;
value IN (SELECT primary_key FROM single_table WHERE some_expr)
EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM actor);
8.index_subquery:与unique_subquery类似,子查询中的返回结果字段组合是一个索引或索引组合,但不是一个主键或者唯一索引;
value IN (SELECT key_column FROM single_table WHERE some_expr)
EXPLAIN SELECT * FROM film_actor WHERE film_id IN (SELECT film_id FROM film_actor);
9.range:使用索引扫描,只查询给定范围的记录;比如出现=, <>, >, >=, < , <=, IS NULL, <=>, BETWEEN, or IN()操作符时:
EXPLAIN SELECT * FROM actor WHERE id < 10; 10.index:按索引次序扫描数据,因为按照索引扫描所以会避免排序,但也会扫描整表数据,若随机读取开销会更大;如果extra列显示using index,说明使用的是覆盖索引(覆盖索引:包含所有满足查询需要的数据列的索引);对于InnoDB表特别有用,此时只访问索引数据即可,不用再根据主键信息获取原数据行,避免了二次查询;而MyISAM表优化效果相对InnoDB来说没有那么的明显; 11.ALL:全表扫描;一般情况下,应该添加索引来避免全表扫描;出了使用limit子句或者Extra列有distinct信息; Extra列: 1.Using index:此查询使用了覆盖索引(Covering Index),即通过索引就能返回结果,无需访问表;若没显示"Using index"表示读取了表数据; EXPLAIN SELECT id FROM City WHERE id = 1890; 2.Using where:表示MySQL服务器从存储引擎收到行后再进行“后过滤”(Post-filter);所谓“后过滤”,就是先读取整行数据,再检查此行是否符合where句的条件,符合就留下,不符合便丢弃;因为检查是在读取行后才进行的,所以称为“后过滤”; EXPLAIN SELECT id FROM City WHERE id < 10; 3.Using temporary:使用到临时表 EXPLAIN SELECT DISTINCT Continent FROM Country; 4.Using filesort:若查询所需的排序与使用的索引的排序一致,因为索引是已排序的,因此按索引的顺序读取结果返回;否则,在取得结果后,还需要按查询所需的顺序对结果进行排序,这时就会出现Using filesort; EXPLAIN SELECT id, CountryCode FROM City WHERE id < 10 ORDER BY CountryCode; latin1/utf8/gbk字符数/字节数/汉字的对应关系: latin1:1character=1byte,1汉字=2character;一个字段定义成varchar(200),可以存储100个汉字或者200个字符,占用200个字节;尤其是当字段内容是字母和汉字组成时,尽量假设字段内容都是由汉字组成,据此来设置字段长度; utf8:1character=3bytes,1汉字=1character;一个字段定义成varchar(200),则它可以存储200个汉字或者200个字母,占用600个字节; gbk:1character=2bytes,1汉字=1character一个字段定义成varchar(200),则它可以存储200个汉字或者200个字母,占用400个字节; -- EXPLAIN格式; EXPLAIN输出也提供其他格式: 1.可视化EXPLAIN:在MySQL Workbench中提供图形格式的输出; 2.EXPLAIN FORMAT=JSON: 1.JSON格式的输出;JSON(JavaScript Object Notation,JavaScript对象表示法)是一种简单的数据交换格式; 2.当要将EXPLAIN输出传递给程序以供进一步处理/分析时十分有用; mysql> EXPLAIN FORMAT=JSON SELECT * FROM City\G

— 检查服务器状态;
MySQL提供了多种查看服务器状态变量的方法:
1.在mysql提示符下:
1.STATUS;
2.SHOW STATUS;
2.在终端上:
1.mysqladmin –login-path=login-path status
2.mysqladmin -u user -p extended-status
3.对mysqladmin使用选项可提供附加功能;例如,–sleep(或-i)选项可指定在迭代之间等待的秒数,并在等待该时间后自动重新执行命令;–relative(或-r)选项显示自上次迭代后每个变量的差异,而不是变量值;
shell> mysqladmin -i 5 -r status

— 主要状态变量;
1.Created_tmp_disk_tables:显示磁盘上的内部临时表的数量;获取执行语句时服务器所创建的临时表数;如果该数值较高,则服务器已在磁盘上(而不是在内存中)创建多个临时表,从而导致查询执行较慢;
2.Handler_read_first:显示索引中第一个条目的读取次数;如果该数值较高,则服务器已执行多次完整索引扫描以完成查询请求;
3.Innodb_buffer_pool_wait_free:显示服务器等待干净页面的次数;等待InnoDB缓冲池中的页面刷新后才可以完成查询请求;如果该数值较高,则未正确设置InnoDB缓冲池的大小,因而查询性能受到影响;
4.Max_used_connections:显示自服务器启动以来的最大并发连接数;此变量提供非常有用的信息来确定服务器必须支持的并发连接数;
5.Open_tables:显示给定时间内打开的表的数量;将此变量与服务器系统变量table_cache比较,可提供有关应该为表高速缓存预留多少内存量的有用信息;如果Open_tables状态变量的值通常很低,请减小服务器系统变量table_cache的大小;如果该值很高(接近服务器系统变量table_cache的值),请增加分配给表高速缓存的内存量来缩短查询响应时间;
6.Select_full_join:显示执行表扫描而不是使用索引的联接数量;如果该值不是0,则应该仔细检查表的索引;
7.Slow_queries:显示用时比long_query_time系统变量所指定的秒数长的查询数;此状态变量取决于对long_query_time变量(默认值为10秒)设置的了解;如果Slow_queries状态变量不是0,请检查long_query_time的值和慢速查询日志,并改进所捕获的查询;
8.Sort_merge_passes:显示排序算法所执行的合并传递次数,排序操作需要内存中的缓冲区;此状态变量计算排序操作所需的经过排序缓冲区的传递次数;如果该值较高,则可能表明排序缓冲区大小不足以执行查询的一次通过排序;请考虑增大sort_buffer_size系统变量的值;
9.Threads_connected:显示当前打开的连接数;定期捕获该值可提供有关服务器何时最活跃的有用信息,使 用此变量可确定执行服务器维护的最佳时间,或者可将其作为为服务器分配更多资源的依据;
10.Uptime:显示服务器持续运行的秒数;该值可以提供有关服务器运行状况的有用信息,例如服务器需要重新启动的频率;

— 调节系统变量;
1.首先调节查询,模式和索引:
1.一个常见的误区是认为服务器变量配置是服务器调节中最重要的部分;
2.事实上,优化模式,常见查询和典型数据库的索引可获得比调节变量更多的好处;
2.针对服务器大小进行调节内存和I/O:
1.Oracle的MySQL工程师选择默认设置来适应大多数生产系统,这些系统常常要处理频繁的小事务,许多更新和少数大型慢速查询(如用于生成报告的查询);
2.然而,由于MySQL在从小型设备(如销售点系统和路由器)到具有大量内存和快速磁盘阵列的大型Web服务器等各种系统上都在使用,可能会发现,对于您的特定环境和工作负荷,可以从更改服务器的某些默认设置中获益;
3.针对应用程序配置进行调节:存储引擎设置
1.将物理RAM的70%–85%提供给InnoDB缓冲池;在仅使用InnoDB用户表的MySQL专用服务器上,可以将innodb_buffer_pool_size的值增大到占服务器总内存的较大比例(70%–85%),同时要记住操作系统的需要,如cron作业,备份,病毒扫描以及管理连接和任务;如果有几GB的RAM,则还可以通过使用多个innodb_buffer_pool_instances而获益,该设置可启用多个缓冲池,从而避免争用;
2.最小化MyISAM高速缓存和缓冲区;在不将MyISAM用作用户表的系统上,减小仅适用于MyISAM的选项的值(例如将key_buffer_size的值减小为16MB等较小值),同时要记住某些内部MySQL操作将使用MyISAM;
4.调节工作负荷类型:
1.连接数;
1.当为每个查询或每个连接的高速缓存和缓冲区设置较大的值时,会减少缓冲池的可用大小;
2.调节服务器的配置变量是一个平衡过程,需要从默认值开始,提供尽可能多的内存给缓冲池,然后调节与以下项最紧密相关的变量:调节目标,通过检查服务器状态识别出的问题以及通过查询性能模式识别出的瓶颈;
2.事务服务器:
1.在用于支持许多反复断开并重新连接的快速并发事务的服务器上,请将thread_cache_size的值设置为足够大的值,以便大多数新连接可以使用高速缓存的线程;
2.这可避免创建和断开每个连接的线程时的服务器开销;在支持多写入操作的服务器上,请提高innodb_log_file_size和innodb_log_buffer_size等日志设置,因为数据修改操作的性能在很大程度上依赖 于InnoDB日志的性能;
3.请考虑更改innodb_flush_log_at_trx_commit的值以提高每次提交的性能,但风险是:如果服务器出现故障,可能会丢失某些数据;
4.如果您的应用程序反复执行相同的查询(或多个相同的查询),请考虑启用查询高速缓 存,并根据常见查询的结果调节其大小,方法是为query_cache_type和query_cache_size设置适当的值;
3.报表服务器:在用于运行少数大型慢速查询(例如用于业务智能报表的查询)的服务器上,使用join_buffer_size和sort_buffer_size等设置增加专用于缓冲区的内存量;虽然默认服务器设置更适合事务系统,但默认的my.cnf文件包含这些变量适用于报表服务器的替代值;

— 主要服务器系统变量;
1.innodb_buffer_pool_size:定义InnoDB用于缓存表数据和索引的内存缓冲区大小;要获得最佳性能,请将此值设置为尽可能大,同时要记住值过高会导致操作系统交换页面,从而大大降低性能;如果在专用数据库服务 器上仅使用了InnoDB用户表,请考虑将此变量设置为介于物理RAM的70%到85%之间的值;
2.innodb_flush_log_at_trx_commit:定义InnoDB将日志缓冲区写入日志文件的频率,以及对日志文件执行刷新到磁盘操作的频率;此变量有三种可能的设置:
1.0:每秒将日志缓冲区写入磁盘一次;
2.1:每次提交时将日志刷新到磁盘;如果未发生提交,则每秒刷新一次;
3.2:将日志刷新到操作系统高速缓存中,并且每隔innodb_flush_log_at_timeout秒(默认为一秒)刷新到磁盘一次;
3.innodb_log_buffer_size:定义InnoDB用于写入磁盘上的日志文件的缓冲区的大小;此变量的默认值为8MB;事务超过此大小会导致InnoDB在事务提交之前将日志刷新到磁盘,从而降低性能;对于使用大量BLOB或者在更新活动中具有较大峰值的应用程序,可通过增大该值提高事务性能;
4.innodb_log_file_size:定义日志组中每个日志文件的大小;对于大型数据集上的写入密集型工作负荷,请设置此变 量以便所有日志文件的最大总大小(通过innodb_log_files_in_group设置)小于或等于缓冲池的大小;大型日志文件会减缓故障恢复,但可以通过减少检查点刷新活动来提高整体性能;
5.join_buffer_size:定义用于使用表扫描的联接的最小缓冲区大小;对于包含无法使用索引的联接的查询,请以默认值(256 KB)为起点增大该值;运行此类查询时请更改每个会话的值,以避免设置全局设置而使无需这么大值的查询浪费内存;
6.query_cache_size:定义为缓存查询结果而分配的内存量;通过使用查询高速缓存,提高针对极少更改的数据发出重复查询的应用程序的性能;作为基线,请根据重复查询的数量和所返回数据的大小将此变量设置为介于32MB和512MB之间的值,请监视高速缓存命中率以确定此变量的有效性,并根据您的观察调节其值;
7.sort_buffer_size:定义分配给需要进行排序的会话的最大内存量;如果Sort_merge_passes状态变量的值很高,请增大该值以提高ORDER BY和GROUP BY操作的性能;
8.table_open_cache:定义所有线程打开的表的数量;请设置该值以使其大于N * max_connections,其中,N是在应用程序的所有查询中所使用的最大表数量;该值过高会导致出现错误“Too many open files(打开的文件太多)”;Open_tables状态变量的值较高表示MySQL频繁打开和关闭表,因此应该增大table_open_cache;
9.thread_cache_size:义服务器应缓存以供重用的线程数;默认情况下,此变量将自动调节大小;评估Threads_created状态变量可确定是否需要更改thread_cache_size的值;

— 准备调节环境;
1.尽可能地复制生产系统;
1.要减小与正在调节的变量无关的已更改因素的影响;
2.请在停机期间对生产服务器执行调节,或者最好在复制的系统上进行调节;
2.决定调节目标:
1.每秒处理更多事务;
2.更快生成复杂报表;
3.通过并发连接的峰值提高性能;
3.选择适当的变量进行调节:缓冲区,高速缓存,日志设置;
4.为了最准确地模拟正在针对其进行调节的工作负荷,需要收集一组有代表性的语句:
1.从应用程序中选择查询和修改操作比例正确的语句序列;
2.在要优化的每天或每周期间内,使用一般查询日志从生产服务器收集实际语句;

— 练习调节;
1.查找每个变量的最佳值的基准测试:
1.mysqlslap或mysql来运行工作负荷并获取平均执行时间;
2.sql-bench来运行更一般的基准测试;
3.mysqladmin extended-status来获取工作负荷前后的状态变量的值;
4.top等操作系统工具或/proc文件系统来访问过程度量;
2.将变量设置为低于其默认值的设置;
3.进行基准测试,测量相关度量:
1.虚拟内存使用;
2.所花费的平均时间;
3.相关状态变量;
4.增大变量值并重复基准测试:如果需要,刷新状态变量;
5.将结果绘制成图:
1.查找收益的下降点和性能的高峰;
2.根据所用资源和性能之间的最佳平衡来决定最终变量值;
6.如果要针对特定变量使用多个不同值运行微调基准测试,或者如果要在很长一段时间内反复运行相同的基准测试,请考虑使用脚本语言来自动化基准测试中所使用的步骤;

— 调节示例:排序缓冲区大小;
1.本幻灯片中的示例显示了一系列针对具有繁重排序工作负荷的数据库的测试结果,其中,运行测试时更改了sort_buffer_size变量;
2.图表显示:
1.在sort_buffer_size从32KB增大到512KB时,Sort_merge_passes状态变量的值(可使用mysqladmin extended_status-r查看)急剧下降,在此之后又缓慢降低;
2.测试工作负荷所花的平均时间(可使用mysqlslap查看)在sort_buffer_size为512KB时降低,在4MB时达到极大峰值,然后在8MB时下降,最终在32MB时达到最佳性能;
3.mysqld进程的总虚拟内存(可使用top查看)在sort_buffer_size为512KB时最小,此后一直到16MB都稳步上升,在32MB时急剧上升;
3.查询的平均时间最短时,sort_buffer_size为32MB,该设置使用了大量内存,而缓冲池本来可以更好地利用这些内存;在本示例中,针对测试中所使用的工作负荷,服务器和数据库的特定组合,512KB设置可在性能和所用内存之间提供最佳平衡;

— 课后练习;

— 补充:Extra其它信息;
1.distinct:当MySQL找到第一条匹配的结果值时,就停止该值的查询,然后继续该列其他值的查询;
2.not exists:在左连接中,优化器可以通过改变原有的查询组合而使用的优化方法;当发现一个匹配的行之后,不再为前面的行继续检索,可以部分减少数据访问的次数;
3.const row not found:涉及到的表为空表,里面没有数据;
4.Full scan on NULL key:是优化器对子查询的一种优化方式,无法通过索引访问NULL值的时候会做此优化;
5.Impossible Having:Having子句总是false而不能选择任何列;例如having 1=0;
6.Impossible WHERE:Where子句总是false而不能选择任何列;例如where 1=0;
7.Impossible WHERE noticed after reading const tables:MySQL通过读取“const/system tables”,发现Where子句为false;也就是说:在where子句中false条件对应的表应该是const/system tables;这个并不是MySQL通过统计信息做出的,而是真的去实际访问一遍数据后才得出的结论;当对某个表指定了主键或者非空唯一索引上的等值条件,一个query最多只可能命中一个结果,MySQL在explain之前会优先根据这一条件查找对应记录,并用记录的实际值替换query中所有用到来自该表属性的地方;
8.No matching min/max row:没有行满足如下的查询条件;
9.no matching row in const table:对一个有join的查询,包含一个空表或者没有数据满足一个唯一索引条件;
10.No tables used:查询没有From子句,或者有一个From Dual(dual:虚拟表,是为了满足select…from…习惯)子句;
EXPLAIN SELECT VERSION()
11.Range checked for each record (index map: N):MySQL发现没有好的index,但发现如果进一步获取下一张join表的列的值后,某些index可以通过range等使用;MySQL没找到合适的可用的索引;取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引（如果有的话）,并且使用这个索引来从表里取得记录;这个过程不会很快,但总比没有任何索引时做表连接来得快;
12.Select tables optimized away:当我们使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询;在使用某些聚合函数如min, max的query,直接访问存储结构(B树或者B+树)的最左侧叶子节点或者最右侧叶子节点即可,这些可以通过index解决;Select count(*) from table(不包含where等子句),MyISAM保存了记录的总数,可以直接返回结果,而Innodb需要全表扫描;Query中不能有group by操作;
13.unique row not found:对于SELECT … FROM tbl_name,没有行满足unique index或者primary key;从表中查询id不存在的一个值会显示Impossible WHERE noticed after reading const tables;
14.Using filesort:指MySQL将用外部排序而不是按照index顺序排列结果;数据较少时从内存排序,否则从磁盘排序;Explain不会显示的告诉客户端用哪种排序;
15.Using index:表示MySQL使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据;注意不要和type中的index类型混淆;
16.Using index for group-by:类似Using index,所需数据只需要读取索引,当query中有group by或distinct子句时,如果分组字段也在索引中,extra就会显示该值;
17.Using temporary:MySQL将创建一个临时表来容纳中间结果;在group by和order by的时,如果有必要的话;例如group by一个非键列,优化器会创建一个临时表,有个按照group by条件构建的unique key,然后对于每条查询结果（忽略group by）,尝试insert到临时表中,如果由于unique key导致insert失败,则已有的记录就相应的updated;例如,name上没有索引,SELECT name,COUNT(*) FROM product GROUP BY name,为了排序,MySQL就需要创建临时表;此时一般还会显示using filesort;
18.Using where:表示MySQL将对storage engine提取的结果进行过滤;例如,price没有index,SELECT * FROM product WHERE price=1300.00;有许多where的条件由于包含了index中的列,在查找的时候就可以过滤,所以不是所有带where子句的查询会显示Using where;
19.Using join buffer:5.1.18版本以后才有的值;join的返回列可以从buffer中获取,与当前表join;
例如:explain select * from t1,t2 where t1.col < 10 and t2.col < 10 20.Scanned N databases:指在处理information_schema查询时,有多少目录需要扫描; EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES 21.Open_full_table:指示从information_schema查询信息时有关文件开启的优化; Skip_open_table:表信息已经获得,不需要打开; 22.Open_frm_only:只打开.frm文件; Open_trigger_only:只打开.trg文件; Open_full_table:没有优化;.frm,.myd和.myi文件都打开; 23.Using sort_union(…), Using union(…), Using intersect(…):都出现在index_merge读取类型中; 1.Using sort_union:用两个或者两个以上的key提取数据,但优化器无法确保每个key会提取到一个自然排好序的结果,所以为了排除多余的数据,需要额外的处理; 2.例如,customer的state,（lname,fname）是key,但lname不是key,SELECT COUNT(*) FROM customer WHERE (lname = ‘Jones') OR (state = ‘UT'),由于lname上面没有key,所以使用（lname,fname）,使得结果可能不按照顺序,优化器需要额外的一些工作; 3.Using union:用两个或者两个以上的key提取数据,分别取得结果是已排序,通过合并就可以获得正确结果;例如,customer中的state和（lname,fname）是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') OR (state = ‘UT'); Using intersect:用两个或者两个以上的key提取数据,分别取得结果是已排序,通过求交就可以获得正确结果; 4.例如,customer中的state和（lname,fname）是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') AND (state = ‘UT'); 24.Using where with pushed condition:仅用在ndb上;MySQL Cluster用Condition Pushdown优化改善非索引字段和常量之间的直接比较;condition被pushed down到cluster的数据节点,并在所有数据节点同时估算,把不合条件的列剔除避免网络传输;

— MySQL复制;
复制是MySQL的一项功能,允许服务器将更改从一个实例复制到另一个实例:
1.MySQL中的复制功能用于将更改从一个服务器(主服务器)复制到一个或多个从属服务器;
2.主服务器将所有数据和结构更改记录到二进制日志中;
3.从属服务器从主服务器请求该二进制日志并在本地应用其内容;
4.日志文件的格式影响从属服务器应用更改的方式,MySQL支持基于语句的,基于行的以及混合格式的日志记录;
5.从属服务器数量:
1.一个主服务器可以具有的从属服务器数量没有限制,但是,每个额外从属服务器使用主服务器上的较少资源,所以您应该仔细考虑生产设置中每个从属服务器的好处;
2.给定环境中主服务器的最佳从属服务器数量取决于许多因素:模式大小,写入次数,主服务器和从属服务器的相对性能以及CPU和内存可用性等因素;
3.一般准则是将每个主服务器的从属服务器数量限制为不超过30;
6.网络故障:
1.MySQL中的复制功能在网络故障时继续工作;
2.每个从属服务器跟踪其已经处理了多少日志并在网络连接恢复时自动继续处理,此行为是自动的,不需要特殊配置;

— 复制主服务器和从属服务器;
主/从属服务器关系是一对多关系:
1.每个从属服务器从一个主服务器读取日志;
2.一个主服务器可以将日志传送给许多从属服务器;
3.中继从属服务器:
1.最顶层主服务器的直接从属服务器请求并应用该主服务器处发生的更改,而该从属服务器将更改向下中继到其从属服务器,以此类推,直到复制到达该链的末尾;
2.这样可以通过多个级别的复制来传播更新,允许更复杂的拓扑,每个额外级别会向系统添加更多传播延迟,从而较浅设置遇到的复制滞后要比较深设置少;
3.每个从属服务器仅能具有一个主服务器,一个从属服务器不能从多个主服务器进行复制;
4.如果一个从属服务器用作其他服务器的主服务器,该从属服务器常常称为中继从属服务器;
4.使用BLACKHOLE存储引擎进行复制:
1.BLACKHOLE存储引擎无提示地放弃所有数据更改,而不发出警告,二进制日志继续成功记录这些更改;
2.当中继从属服务器将所有更改复制到深一层的从属服务器,但是自身不需要将数据存储在特定表中时,将BLACKHOLE用于这些表;
3.例如,如果您具有的中继从属服务器单独用来对少量表执行经常长时间运行的业务智能报表,您可以将其他所有复制的表配置为使用 BLACKHOLE,从而服务器不会存储其不需要的数据,同时将所有更改复制到其从属服务器;

— 复杂拓扑;
可以使用更复杂的拓扑:
1.双向拓扑具有两个主服务器,每个主服务器是另一个主服务器的从属服务器;
2.循环拓扑具有任意数量的服务器:
1.每个服务器是一个主服务器并且是另一个主服务器的从属服务器;
2.对任何主服务器的更改将复制到所有主服务器;
3.并非每个从属服务器都必须是主服务器;
4.MySQL复制不执行冲突解析;
1.在典型配置中,客户机仅将更改写入主服务器,但是从属服务器读取更改,在服务器允许数据进行并发更新的环境中,数据在多个服务器上的最终状态可能变得不一致;
2.应用程序负责防止或管理冲突操作,MySQL复制不执行冲突解决解析;包括多个主服务器的所有拓扑中都可能发生冲突,这包括诸如前面幻灯片中显示的简单分层结构(如果中继从属服务器接受客户机的更改);冲突在循环拓扑中特别常见
3.eg:程序的两个进程想要把一个价值600块的商品上涨20%和下降50块,就会因为执行的顺序不同产生670块和660块;

— 复制用例;
复制的常见用法:
1.水平向外扩展:在多个从属服务器中分布查询工作负荷;实现复制的最常见原因是在一个或多个从属服务器中分布查询工作负荷,从而提高整个应用程序中读取操作的性能,并通过减少主服务器的读取工作负荷来提高其上的写入操作性能;
2.业务智能和分析:业务智能报表和分析处理会使用大量资源,需要大量时间来执行;在复制的环境中,可以在从属服务器上运行此类查询,从而主服务器可以继续处理生产工作负荷,而不受长时间运行的I/O密集型报表的影响;
3.地理数据分布:具有分布式地理位置的公司可以受益于复制,在每个区域具有服务器,用于处理本地数据并在组织中复制该数据;这样可以向客户和员工提供地理相邻性的性能和管理优势,同时还使公司了解整个公司的数据;

— 高可用性复制;
1.受控切换:在硬件或系统升级期间使用副本来代替生产服务器;
2.服务器冗余:在系统故障时执行故障转移到副本服务器;
3.联机模式更改:在具有多个服务器的环境中执行滚动升级来避免整个系统故障;
4.软件升级:在环境升级过程中在不同版本的MySQL之间进行复制;
1.从属服务器运行的版本必须比主服务器新;
2.在升级过程中发出的查询必须受升级过程中使用的所有版本的支持;

— 配置复制;
1.为每个服务器配置唯一server-id;
1.复制拓扑中的每个服务器必须具有唯一的server-id,一个无符号的32位整数,值从0(默认)到4,294,967,295;
2.server-id为0的服务器(无论是从属服务器还是主服务器)拒绝使用其他服务器进行复制;
2.配置每个主服务器:
1.启用二进制日志并启用TCP/IP网络;
1.每个主服务器必须启用二进制日志记录,因为在复制过程中,每个主服务器将其日志内容发送到每个从属服务器;
2.每个主服务器必须分配有IP地址和TCP端口,因为复制无法使用UNIX套接字文件;
2.每个从属服务器必须登录到主服务器中才能从中进行复制,所以创建具有REPLICATION SLAVE特权的新用户;
1.GRANT REPLICATION SLAVE ON *.* TO @;
3.备份主数据库,并且如果需要则记录日志坐标;
1.如果您正使用已经包含已填充数据库的主服务器创建复制拓扑,必须首先为从属服务器创建该数据库的副本;
2.如果您使用全局事务标识符,则不需要记录日志坐标;
3.配置每个从属服务器:
1.从主服务器恢复备份;
2.在每个从属服务器上发出CHANGEMASTER TO语句,包含:
1.主服务器的网络位置;
2.复制帐户用户名和口令;
3.开始复制操作的日志坐标(如果需要);
3.使用START SLAVE开始复制;

— CHANGE MASTER TO;
1.在从属服务器上发出CHANGE MASTER TO…语句来配置复制主服务器连接详细信息:
mysql> CHANGE MASTER TO
-> MASTER_HOST = ‘host_name’,
-> MASTER_PORT = port_num,
-> MASTER_USER = ‘user_name’,
-> MASTER_PASSWORD = ‘password’,
-> MASTER_LOG_FILE = ‘master_log_name’,
-> MASTER_LOG_POS = master_log_pos;
2.CHANGE MASTER TO的后续调用保留每个未指定选项的值:
1.更改主服务器的主机或端口还会重置日志;
2.更改口令,但是保留所有其他设置: mysql> CHANGE MASTER TO MASTER_PASSWORD=’newpass’;
3.注意事项:
1.要提高安全性,还可以在启用SSL的服务器上使用MASTER_SSL和相关选项加密复制期间从属服务器和主服务器之间的网络通信;
2.可以通过执行SHOW MASTER STATUS语句从主服务器获取文件和位置:mysql> SHOW MASTER STATUS;
3.如果使用mysqldump执行主服务器的数据库备份作为从属服务器的起点,可以使用–master-data选项在备份中包括日志坐标:
mysqldump -uroot -p –master-data -B world_innodb > backup.sql;
4.如果您使用GTID,则指定MASTER_AUTO_POSITION=1,而不是日志坐标;

— 使用日志坐标进行故障转移;
1.当主服务器变为不可用后进行故障转移,从而选择某个从属服务器作为主服务器;需要查找每个从属服务器的新主服务器和正确的日志坐标,严密检查二进制日志:
1.查找应用于每个从属服务器的最近事件;
2.选择最新从属服务器作为新的主服务器;
1.如果新主服务器位于特定从属服务器后面(即,如果该从属服务器已经应用了该新主服务器的日志末尾的事件),则该从属服务器会重复那些事件;
2.如果新主服务器在特定从属服务器的前面(即,如果该新主服务器的二进制日志包含该从属服务器尚未应用的事件),该从属服务器将跳过那些事件;
3.确定新主服务器上的日志坐标来匹配每个其他从属服务器上最新应用的事件;
4.在每个从属服务器上发出正确的CHANGE MASTER TO…;
2.在循环拓扑中,查找每个二进制日志中的事件源变得非常困难:
1.因为每个从属服务器使用与其他从属服务器不同的顺序应用操作;
2.要避免此困难,请使用全局事务标识符(Global Transaction Identifier, GTID);MySQL实用程序还包括有助于使用GTID进行故障转移的工具;

— 全局事务标识符(Global Transaction Identifier, GTID);
1.全局事务标识符(Global Transaction Identifier, GTID)唯一地标识复制的网络中的每个事务;
1.UUID(universally unique identifier,通用唯一标识符)是每个事务的源服务器的UUID;
2.每个服务器的UUID存储在数据目录中的auto.cnf文件中,每次重启时会读取这个文件;如果该文件不存在,MySQL会创建该文件并生成新的UUID,将其放在该新文件中;
3.使用server_uuid变量查询服务器的UUID:mysql> SELECT @@server_uuid\G
4.客户机在主服务器上执行事务时,MySQL将创建新GTID并记录事务及其唯一GTID,从属服务器从主服务器读取并应用该事务时,该事务保持其原始GTID;即,复制到从属服务器的事务的服务器UUID是主服务器的UUID,而不是从属服务器的;复制链中的每个后续从属服务器都将记录该事务及其原始GTID,因此,复制拓扑中的每个从属服务器 可以确定第一个执行事务的主服务器;
2.每个GTID的形式为::0ed18583-47fd-11e2-92f3-0019b944b7f7:338;
3.GTID集包含一系列GTID:0ed18583-47fd-11e2-92f3-0019b944b7f7:1-338;
4.使用以下选项启用GTID模式:
1.gtid-mode=ON:与每个事务一起记录唯一的GTID;
2.enforce-gtid-consistency:禁止无法以事务安全方式记录的事件;
3.log-slave-updates:将复制的事件记录到从属服务器的二进制日志;
4.gtid_executed:记录事务的GTID;在全局上下文中,此变量包含记录到服务器的二进制日志的所有GTID集(表示此服务器和其他上游主服务器的所有事务);mysql> SELECT @@global.gtid_executed\G
5.gtid_purged:变量包含已经从二进制日志中清除的GTID集;在服务器上执行RESET MASTER时,gtid_purged和全局gtid_executed都将重置为空字符串;

— 使用GTID进行复制;
使用CHANGE MASTER TO…启用GTID复制:
1.告知从属服务器通过GTID标识事务:CHANGE MASTER TO MASTER_AUTO_POSITION=1;
2.您不需要提供日志坐标(eg:MASTER_LOG_FILE,MASTER_LOG_POS);
1.启用基于GTID的复制时,不需要指定主服务器的日志坐标,因为从属服务器将@@global.gtid_executed的值发送给主服务器;
2.因此,主服务器知道从属服务器已经执行了哪些事务,从而仅发送从属服务器尚未执行的那些事务;
3.不能在同一CHANGE MASTER TO…语句中提供MASTER_AUTO_POSITION和日志坐标;

— 使用GTID进行故障转移;
1.使用GTID时,循环拓扑中的故障转移很简单:
1.在发生故障的主服务器的从属服务器上,通过发出单个CHANGE MASTER TO语句绕过该主服务器;
2.每个服务器忽略或应用从拓扑中的其他服务器复制的事务,具体取决于是否看到了该事务的GTID;
2.非循环拓扑中的故障转移同样简单:
1.临时将新主服务器配置为最新从属服务器的从属服务器,直到该新主服务器变为最新;
3.虽然GTID可以防止源自单个服务器上的事件重复,但是它们不会防止源自不同服务器上的冲突操作,如标题为“复杂拓扑”的幻灯片中所述;

— 复制过滤规则;
1.过滤器是应用于主服务器或从属服务器的服务器选项:
1.主服务器写入二进制日志时应用binlog-*过滤器;
2.从属服务器读取中继日志时应用replicate-*过滤器;
3.当环境中的不同服务器用于不同目的时,使用过滤规则;例如,专用于显示Web内容的服务器不需要从主服务器复制重新进货信息或工资记录,而专用于生成关于销售量的管理 报表的服务器不需要存储Web内容或市场营销副本;
2.基于以下各项选择要复制的事件:
1.数据库:
1.replicate-do-db, binlog-do-db
2.replicate-ignore-db, binlog-ignore-db
2.表:
1.replicate-do-table, replicate-wild-do-table
2.replicate-ignore-table, replicate-wild-ignore-table
3.过滤规则具有按顺序应用的复杂优先级规则:
1.数据库过滤器先于表过滤器应用;
2.表通配符过滤器*-wild-*在不使用通配符的那些过滤器之后应用;
3.*-do-*过滤器先于各个*-ignore-*过滤器应用;
4.使用多个过滤器时要谨慎,由于应用过滤器的顺序复杂,非常容易出错;因为过滤器控制要复制的数据,所以很难从此类错误中恢复,因此,不要混用不同类型的过滤器;

— MySQL实用程序;
MySQL实用程序是提供许多有用功能的命令行工具:
1.随MySQL Workbench提供;
2.用于维护和管理MySQL服务器;
3.以Python编写,Python编程人员很容易使用提供的库对其进行扩展;
4.对于配置复制拓扑和执行故障转移非常有用;

— 用于复制的MySQL实用程序;
1.mysqldbcopy:将数据库以及复制配置从源服务器复制到目标服务器;
1.mysqldbcopy实用程序接受选项–rpl,其在目标服务器上运行CHANGE MASTER TO语句;它接受以下值:
1.master:目标服务器变为源的从属服务器;
2.slave:源已经是其他主服务器的从属服务器,目标服务器从源复制该主服务器信息并变为同一主服务器的从属服务器;
2.mysqldbcompare:比较两个数据库来查找区别并创建脚本来同步这两个数据库;
3.mysqlrpladmin:管理复制拓扑:
1.在主服务器故障后故障转移到最佳从属服务器;
2.切换以升级指定的从属服务器;
3.启动,重置或停止所有从属服务器;
4.mysqlfailover:持续监视主服务器,并执行故障转移到最佳可用从属服务器:
1.mysqlfailover实用程序通过ping操作定期检查主服务器状态,期间间隔和ping操作都是可配置的;
2.它使用GTID确保新的主服务器在变为主服务器时是最新的,通过以下操作实现此项:
1.从候选列表中选择新主服务器(如果列表中没有可行的候选项,则从所有从属服务器中选择),将其配置为所有其他从属服务器的从属服务器来收集所有未完成事务,最后使其成为新主服务器;
2.mysqlrpladmin的failover命令在选择新主服务器时执行相似的临时重新配置;
3.如果主服务器失败,您还可以执行运行状况监视而不执行自动重新配置;
5.mysqlrplcheck:检查主服务器和从属服务器之间进行复制的先决条件,包括:
1.二进制日志记录;
2.具有适当特权的复制用户–server_id冲突;
3.可能导致复制冲突的各种设置;
6.mysqlreplicate:在两个服务器之间启动复制,报告不匹配警告消息;
7.mysqlrplshow:显示主服务器与从属服务器之间的复制拓扑或者递归显示整个拓扑;
mysqlrplshow 实用程序显示如下所示的复制拓扑:
# Replication Topology Graph localhost:3311 (MASTER)
|
+— localhost:3312 – (SLAVE + MASTER)
|
+— localhost:3313 – (SLAVE + MASTER)
|
+— localhost:3311 < --> (SLAVE)
端口3311处的服务器出现两次:一次作为主服务器,一次作为从属服务器;< -->符号指示拓扑内的循环;

— 异步复制;
1.从属服务器请求二进制日志并应用其内容,从属服务器通常滞后于主服务器;
2.主服务器不关注从属服务器何时应用日志,主服务器继续运行而不等待从属服务器;
3.在单独的线程中,主服务器将二进制日志流处理到连接的从属服务器,因为主服务器提交更改而不等待任何从属服务器的响应,所以这称为异步复制;
4.最重要的是,这意味着在主服务器向应用程序报告成功时从属服务器尚未应用事务;通常,这不是个问题,但是,如果在主服务器提交事务之后而该事务复制到任何从属服务器之前,该主服务器出现故障并且数据丢失,则该事务将丢失,即使应用程序已经向用户报告成功也是如此;
5.如果主服务器在提交事务之前等待所有从属服务器应用其更改,则复制称为是同步的;虽然MySQL复制不是同步的,但MySQL Cluster在内部使用同步复制来确保整个群集中的数据一致性,并且MySQL客户机请求是同步的,因为客户机在向服务器发出查询后等 待服务器响应;

— 半同步复制;
半同步复制:
1.在主服务器和至少一个从属服务器上需要插件和启用选项:
1.插件:
1.rpl_semi_sync_master(在主服务器上)
2.rpl_semi_sync_slave(在从属服务器上)
2.选项:
1.rpl_semi_sync_master_enabled(在主服务器上)
2.rpl_semi_sync_slave_enabled(在从属服务器上)
3.如果在主服务器上启用半同步复制,它的行为是异步的,直到至少一个半同步从属服务器连接;
2.阻止每个主服务器事件,直到至少一个从属服务器接收该事件:
1.这意味着在主服务器向应用程序报告成功时至少一个从属服务器已经收到了每个事务;
2.如果主服务器在提交事务后出现故障且数据丢失并且应用程序已经向用户报告了成功,则该事务还存在于至少一个从属服务器上;
3.如果发生超时则切换到异步复制;
1.半同步复制需要您在性能和数据完整性之间进行权衡,使用半同步复制时事务速度比使用异步复制时慢,因为主服务器在提交之前等待从属服务器响应;
2.每个事务花费的额外时间至少是它为以下项花费的时间:
1.TCP/IP往返以将提交发送到从属服务器;
2.从属服务器在其中继日志中记录提交;
3.主服务器等待从属服务器确认该提交;
3.这意味着对于物理上位于同一位置,通过快速网络通信的服务器,半同步复制最有效;
4.如果主服务器在超时期间内没有收到半同步从属服务器的响应,该主服务器仍提交该事务,但恢复为异步模式;可以使用rpl_semi_sync_master_timeout变量配置超时,其包含以毫秒为单位的值,默认值是10000,表示十秒;

— 测验;
b

— 查看二进制日志记录;
二进制日志:
1.包含数据和模式更改及其时间戳:基于语句或基于行的日志记录;
2.用于从备份的时间点恢复,从备份的完全恢复以及复制;
3.在下列情况下轮转:
1.MySQL重新启动;
2.其达到max_binlog_size设置的最大大小;
3.您发出FLUSH LOGS语句;
4.可以各种方式进行检查:
1.元数据:SHOW BINARY LOGS,SHOW MASTER STATUS;
2.内容:mysqlbinlog;

— 复制日志;
从属服务器维护有关复制事件的信息:
1.中继日志集:
1.包括中继日志和中继日志索引文件;
2.包含主服务器的二进制日志事件的副本;
3.MySQL自动管理中继日志文件集,在其已经重放了所有事件时删除这些文件并在当前文件超过最大中继日志文件大小时创建新文件;
4.中继日志使用与二进制日志相同的格式存储;可以使用mysqlbinlog查看那些日志;
5.默认情况下,中继日志文件名为-relay-bin.,索引文件名为-relay-bin.index;要使服务器配置不受将来可能的主机名更改影响,请通过设置以下选项来进行这些更改:–relay-log和–relay-log-index;
2.从属服务器状态日志:
1.包含执行复制所需的信息:
1.存储关于如何连接到主服务器的信息;
2.主服务器的二进制日志和从属服务器的中继日志的最近复制的日志坐标;
2.存储在文件或表中:
1.master.info和relay-log.info文件(默认情况下);
2.mysql数据库中的slave_master_info和slave_relay_log_info表;
1.主服务器信息:此日志包含关于主服务器的信息,包括主机名和端口,用于连接的凭证以及主服务器二进制日志的最近下载的日志坐标等信息;
2.中继日志信息:此日志包含中继日志的最近执行的坐标以及从属服务器的已复制事件落后于主服务器的那些事件的秒数;

— 故障安全(Crash-Safe)复制;
1.二进制日志记录是故障安全的:
1.MySQL仅记录完成事件或事务;
2.使用sync-binlog提高安全性:
1.默认情况下,值是0,表示操作系统根据其内部规则向文件写入;
2.将sync-binlog设置为1,强制操作系统在每个事务之后写入文件,或者将其设置为任何较大数值以在该数量的事务 之后写入;
2.将从属服务器状态日志存储在表中以进行故障安全复制:
1.选项:master-info-repository和relay-log-info-repository;
2.可能值为FILE(默认值)和TABLE;
3.TABLE是故障安全的:复制使用事务存储引擎(例如InnoDB)的数据时,通过将master-info-repository和relay-log-info-repository的值从FILE(默认值)更改为TABLE,来将状态日志存储在事务表中以提高性能并确保故障安全复制;该表称为slave_master_info和slave_relay_log_info,都存储在mysql数据库中,并且都使用InnoDB引擎确保事务完整性和故障安全行为;

— 复制线程;
MySQL在主服务器和从属服务器上创建线程来执行复制工作:
1.主服务器创建Binlog转储线程:
1.从二进制日志读取事件并将其发送到从属服务器I/O线程;
2.从属服务器成功连接到主服务器时,主服务器启动称为Binlog转储线程的复制主服务器线程;
3.如果从属服务器配置为使用自动定位协议(CHANGE MASTER TO MASTER_AUTO_POSITION),则该线程显示为“Binlog转储GTID”;在从属服务器已连接时,此线程会在二进制日志内的事件到达时将其发送到从属服务器;
4.主服务器为每个连接的从属服务器创建一个Binlog转储线程;
2.从属服务器至少创建两个线程:
1.从属服务器I/O线程:从主服务器的Binlog转储线程读取事件并将其写入从属服务器的中继日志;
2.从属服务器SQL线程:
1.在单线程从属服务器上应用中继日志事件:
1.默认配置会导致从属服务器滞后;
2.如果主服务器具有多个客户机连接则并行应用更改,但是串行执行其二进制日志中的所有事件;
从属服务器在单个线程中顺序执行这些事件,在高流量环境中或者当从属服务器的硬件不足以处理单个线程中的通信流量时,这会成为瓶颈;
2.在多线程从属服务器上的工作线程之间分配中继日志事件;
3.从属服务器工作线程:在多线程从属服务器上应用中继日志事件;
1.MySQL支持多线程从属服务器以避免单线程从属服务器引起的一些滞后;
2.如果在从属服务器上将slave_parallel_workers变量设置为大于零的值,它会创建该数量的工作线程;
3.在这种情况下,从属服务器SQL线程不直接执行事件,相反,它按数据库将事件分配给工作线程,这使多线程从属服务器在要在多个数据库中复制数据的环境中特别有用;
4.如果工作线程执行并行操作的顺序与其在主服务器上的执行顺序不同,此选项可能导致数
据库之间不一致,所以您必须确保不同数据库中的数据是独立的,由单个线程复制的一个数据库中的数据将保证是一致的;
5.如果将slave_parallel_workers变量设置为大于复制中所用数据库数量的值,一些工作线程将保持空闲;

— 控制从属服务器线程;
1.控制从属服务器线程:
START SLAVE;
STOP SLAVE;
2.单独控制线程:
START SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
3.启动线程直到指定的条件:
START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
START SLAVE IO_THREAD UNTIL SQL_AFTER_GTIDS = 0ed18583-47fd-11e2-92f3-0019b944b7f7:338;

— 监视复制;
mysql> SHOW SLAVE STATUS\G
***************** 1. row *********************
Slave_IO_State: Queueing master event to the relay log
…
Master_Log_File: mysql-bin.005
Read_Master_Log_Pos: 79
Relay_Log_File: slave-relay-bin.005
Relay_Log_Pos: 548
Relay_Master_Log_File: mysql-bin.004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Exec_Master_Log_Pos: 3769
…
Seconds_Behind_Master: 8

1.Slave_*_Running:Slave_IO_Running和Slave_SQL_Running列标识从属服务器的I/O线程和SQL线程当前正在运行,未运行还是正在运行但尚未连接到主服务器;可能值分别为Yes,No或Connecting;
2.主服务器日志坐标:Master_Log_File和Read_Master_Log_Pos列标识主服务器二进制日志中I/O线程已经传输的最近事件的坐标;这些列可与您在主服务器上执行SHOW MASTER STATUS时显示的坐标进行比较,如果Master_Log_File和Read_Master_Log_Pos的值远远落后于主服务器上的那些值,这表示主服务器与从属服务器之间事件的网络传输存在延迟;
3.中继日志坐标:Relay_Log_File和Relay_Log_Pos列标识从属服务器中继日志中SQL线程已经执行的最近事件的坐标;这些坐标对应于Relay_Master_Log_File和Exec_Master_Log_Pos列标识的主服务器二进制日志中的坐标;如果Relay_Master_Log_File和Exec_Master_Log_Pos列的输出远远落后于Master_Log_File和Read_Master_Log_Pos列(表示I/O线程的坐标),这表示SQL线程(而不是I/O线程)中存在延迟,即,它表示复制日志事件快于执行这些事件;在多线程从属服务器上,Exec_Master_Log_Pos包含任何未提交事务之前最后一点的位置,这并不始终与中继日志中的最近日志位置相同,因为多线程从属服务器在不同数据库上执行事务的顺序可能与二进制日志中显示的顺序不同;
4.Seconds_Behind_Master:此列提供中继日志中SQL线程执行的最近事件的时间戳(在主服务器上)与从属服务器的实际时间之间的秒数;当主服务器并行处理大量事件而从属服务器必须串行处理这些事件时,或者当高通信流量期间从属服务器的硬件不足以处理与主服务器可以处理的相同量的事件时,通常会发生这种类型的延迟;如果从属服务器未连接到主服务器,此列为NULL;

— 复制从属服务器I/O线程状态;
从属服务器I/O线程的SHOW PROCESSLIST输出的State列中所显示的最常见状态,这些状态还显示在SHOW SLAVE STATUS显示的Slave_IO_State列中;
1.Connecting to master:线程正尝试连接到主服务器;
2.Waiting for master to send event:线程已经连接到主服务器并且正在等待二进制日志事件到达;如果主服务器处于空闲状态,此状态可能持续很长时间;如果等待持续slave_read_timeout秒,则发生超时,此时,线程考虑断开连接并尝试重新连接;
3.Queueing master event to the relay log:线程已经读取事件并且正在将其复制到中继日志,从而SQL线程可以处理该事件;
4.Waiting to reconnect after a failed binlog dump request:如果二进制日志转储请求失败(由于断开连接),线程在其休眠时转入此状态,然后定期尝试重新连接;可以使用–master-connect-retry选项指定重试之间的时间间隔;
5.Reconnecting after a failed binlog dump request:线程正尝试重新连接到主服务器;
6.Waiting to reconnect after a failed master event read:读取时出错(由于断开连接),线程在尝试重新连接之前休眠master-connect-retry秒;
7.Reconnecting after a failed master event read:线程正尝试重新连接到主服务器;重新建立连接后,状态变为Waiting for master to send event;
8.Waiting for the slave SQL thread to free enough relay log space:该状态显示正在使用非零relay_log_space_limit值,并且中继日志已经增加得足够大,以至其合并大小超过了此值;I/O线程正在等待,直到SQL线程通过处理中继日志内容以便可以删除一些中继日志文件来释放足够空间;

— 复制从属服务器SQL线程状态;
从属服务器SQL线程和工作线程的State列中显示的最常见状态:
1.Waiting for the next event in relay log:这是Reading event from the relay log之前的初始状态;
2.Reading event from the relay log:线程已经从中继日志中读取了事件,从而可以处理该事件;
3.Making temp file:线程正在执行 LOAD DATA INFILE 语句,并且正在创建包含数据的临时文件,从属服务器从该数据中读取行;
4.Slave has read all relay log; waiting for the slave I/O thread to update it:线程已经处理了中继日志文件中的所有事件,现在正在等待I/O线程将新事件写入中继日志;
5.Waiting until MASTER_DELAY seconds after master executed event:SQL线程已经读取事件,但是正等待从属服务器延迟结束;通过CHANGE MASTER TO的MASTER_DELAY选项设置此延迟;
6.Waiting for an event from Coordinator:在多线程从属服务器上,工作线程正等待协调线程向工作队列分配作业;

— 排除MySQL复制故障;
1.查看错误日志:错误日志可以为您提供足够信息来确定和更正复制中的问题;
2.在主服务器上发出SHOW MASTER STATUS语句:如果位置值非零则启用日志记录;
3.确认主服务器和从属服务器都具有唯一的非零服务器ID值:主服务器和从属服务器必须具有不同的服务器ID;
4.在从属服务器上发出SHOW SLAVE STATUS命令:
1.如果从属服务器运行正常,Slave_IO_Running和Slave_SQL_Running显示Yes;
2.Last_IO_Error和Last_SQL_Error:分别导致I/O线程或SQL线程停止的最新错误的错误消息;在正常复制过程中,这些字段是空的,如果发生错误并导致消息显示在以上任一字段中,则错误值也显示在错误日志中;
3.Last_IO_Errno和Last_SQL_Errno:与分别导致I/O线程或SQL线程停止的最新错误关联的错误编号,在正常复制过程中,这些字段包含编号0;
4.Last_IO_Error_Timestamp和Last_SQL_Error_Timestamp:分别导致I/O线程或SQL线程停止的最新错误的时间戳,格式为YYMMDD HH:MM:SS,在正常复制过程中,这些字段是空的;

— 课后练习;

补充:
— Master-Slave模式:
1.修改master端的配置文件my.cnf,如果需要指定只复制某几个数据库,使用binlog-do-db参数;
server-id = 1 # 必须是一个1 － 2^32-1的值,默认是1;
log-bin = mysql-bin # 这个参数在备库不需要设置,但是推荐设置;
2.设置slave端的配置文件my.cnf,如果需要指定只复制某几个数据库,使用replicate-do-db参数;不推荐使用master-host这几个参数,实验中总是起不来mysql服务,貌似已经弃用了;
server-id = 2
log-bin = mysql-bin
3.启动master端和slave端的mysql服务;
4.在master端创建用户复制的用户:grant replication slave on *.* to ‘repl_slave’@’192.168.10.56’ identified by ‘mysql’;
5.查看master端二进制日志的状态:show master status;(如果有业务在写数据,可以先flush tables with read lock,把数据dump出来,然后unlock tables解锁,把数据同步到slave端);
6.在slave端执行CHAGE MASTER TO命令,然后开启slave模式:CHANGE MASTER TO MASTER_HOST=’192.168.10.66′, MASTER_PORT=3306, MASTER_USER=’repl_slave’, MASTER_PASSWORD=’mysql’, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=265;
7.启动slave复制:start slave;然后查看slave端的状态:show slave status;(如果发生uuid冲突,删除$MYSQL_DATADIR/auto.cnf文件,MySQL会创建该文件并生成新的UUID,将其放在该新文件中;)
8.在slave端会生成保存master端状态的master.info文件,以及中继日志文件(mysql-relay-bin.NNNNNN);
9.查看slave端的进程,发现多了系统两个进程:show processlist;
10.在master端写入数据,slave端马上可以查看到,测试成功;

— Master-Master模式:
1.在Master-Slave基础上;
2.在slave端创建用户复制的用户:grant replication slave on *.* to ‘repl_slave’@’192.168.10.66’ identified by ‘mysql’;
3.查看slave端二进制日志的状态:show master status;
4.在master端执行CHAGE MASTER TO命令,然后开启slave模式:CHANGE MASTER TO MASTER_HOST=’192.168.10.56′, MASTER_PORT=3306, MASTER_USER=’repl_slave’, MASTER_PASSWORD=’mysql’, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=265;
5.启动slave复制:start slave;然后查看slave端的状态:show slave status;(如果发生uuid冲突,手动修改auto.cnf中uuid的值)
6.在master端会生成保存slave端状态的master.info文件,以及中继日志文件(mysql-relay-bin.NNNNNN);
7.查看slave端的进程,发现多了系统两个进程:show processlist;
8.其实Master-Master模式就是把Master-Slave反向再做一次而已;

— MySQL半同步复制(Semi-Synchronous Replication)
1.配置Master服务器端:
1.安装插件:install plugin rpl_semi_sync_master soname ‘semisync_master.so’;(通过视图查看:SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME=’rpl_semi_sync_master’\G);
2.设置开启半同步复制:set global rpl_semi_sync_master_enabled=1;
3.设置延迟时间,即Master等待Slave响应的时间:set global rpl_semi_sync_master_timeout=1000;默认为10s,修改为1s;
4.并在配置文件my.cnf中添加rpl_semi_sync_master_enabled=1,rpl_semi_sync_master_timeout=1000设置,以后重启服务就不用重新配置;
5.查看设置的变量:show variables like ‘rpl%’;
2.配置Slave服务器端:
1.安装插件:install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
2.设置开启版同步复制:set global rpl_semi_sync_slave_enabled=1;
3.在配置文件my.cnf中添加rpl_semi_sync_slave_enabled=1之后重启服务就不用重新配置;
4.查看设置的变量:show variables like ‘rpl%’;
3..取消插件:uninstall plugin rpl_semi_sync_master;

— 设置GTID复制:
1.设置数据库只读:SET @@global.read_only = ON;
2.停止停止所有的slave:stop slave;并关闭所有的数据库;
3.修改配置文件;
在my.cnf中添加配置:
[mysqld]
gtid_mode=ON # 开启gtid模式;
log-slave-updates=ON
enforce-gtid-consistency=ON # 强制GTID的一致性;
4.从库指向主库;
CHANGE MASTER TO MASTER_HOST=’192.168.10.66′, MASTER_PORT=3306, MASTER_USER=’repl_slave’, MASTER_PASSWORD=’mysql’, MASTER_AUTO_POSITION = 1;

START SLAVE;
5.设置数据库读写:SET @@global.read_only = OFF;

— 备份基础知识;
1.最重要的备份原因:
1.完整系统恢复:如果系统发生故障,则拥有系统的备份至关重要,因为可以恢复系统;实施怎样的备份和恢复策略取决于被恢复数据要达到的完整性和时效性;
2.审计功能:对于某些系统及关联的流程,可能需要审计或分析独立于主生产环境的环境中的数据;可以使用备份创建这样一个独立的环境;
3.常见DBA任务:在需要执行常见DBA任务(例如将数据从一个系统传输到另一个系统,根据特定的生产服务器状态创建开发服务器,或者将系统的特定部分恢复到用户出错前的某个状态)时使用备份;
2.备份类型:
1.热备份:这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能;使用热备份时,系统仍可供读取和修改数据的操作访问;
2.冷备份:这些备份在用户不能访问数据时进行,因此无法读取或修改数据;这些脱机备份会阻止执行任何使用数据的活动,这些类型的备份不会干扰正常运行的系统的性能;但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据;
3.温备份:这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身;这种中途备份类型的优点是不必完全锁定最终用户,但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序;在备份过程中无法修改数据可能产生性能问题;
3.磁盘:
1.可以使用复制或RAID镜像之类流程,或者使用DRBD之类的外部应用程序,将数据直接备份到其他磁盘;
2.这些技术提供实时(或几乎实时)备份,以及快速恢复数据的方法;
4.二进制日志:
1.二进制日志记录对数据的修改;因此,二进制日志对恢复自上次完整备份以来发生的事件很有用;
2.备份二进制日志的优点是其中包含了各个时间对数据所做的所有更改的记录,而不是数据的快照;
3.可以按顺序创建多个二进制日志备份,根据修改的数据量以及完成完整备份的频率,决定要在备份之间创建的二进制日志备份的数量;
4.二进制日志的不足之处是必须恢复自序列中最后一次完整备份以来创建的所有按顺序的二进制日志;此外,从系统故障中恢复的速度可能会很慢,具体取决于必须恢复的二进制日志的数量;
5.逻辑/文本备份:
1.可以使用mysqldump进行完整数据转储,这些数据转储基于特定的时间点,但是是所有备份副本中速度最慢的;
2.使用mysqldump的优点是所创建的文件是简单的SQL脚本,其中包含可在MySQL服务器上运行的语句;
3.不足之处在于mysqldump会在转储过程中锁定表,这会阻止用户在备份过程中读写文件;

— 使用MySQL进行备份;
MySQL备份可以是下列备份之一:
1.逻辑备份:逻辑备份会产生一个文本文件,其中包含重构数据库的SQL语句;
2.物理备份:这是MySQL数据库文件的二进制副本;
3.基于快照的备份;
4.基于复制的备份;
5.增量备份:通过刷新MySQL二进制日志创建的备份;

— 测验;
b

— 逻辑(文本)备份;
1.逻辑备份:
1.将数据库和表的内容转换为SQL语句;
2.可移植:这些SQL语句包含重建MySQL数据库和表所需的全部信息,可以使用该文本文件在运行不同体系结构的其他主机上重新装入数据库;
3.要求MySQL服务器在备份期间运行:因为服务器在创建文件时要读取备份的表的结构和内容,然后将结构和数据转换为SQL语句;
4.可以备份本地和远程MySQL服务器:其他类型的备份(原始备份)只能在本地MySQL服务器上执行;
5.通常比原始(二进制)备份的速度慢:
1.因为MySQL服务器必须读取表并解释其内容,然后,将表内容转换成磁盘文件,或者将语句发送给客户机程序,由客户机程序将语句写出;
2.在恢复过程中,逻辑备份速度比原始备份慢;这是因为恢复的方法执行单个CREATE和INSERT语句来重新创建每个备份表和行;
2.逻辑备份文件的大小可能会超过备份的数据库大小;

— 物理(原始或二进制)备份;
1.物理备份:
1.生成数据库文件的完整二进制副本,这些副本以完全相同的格式保留数据库;可以使用标准命令,如tar/cp/cpio/rsync/xcopy;
2.必须恢复到同一个数据库引擎:因为原始备份是数据库文件位的完整表现形式;
3.可以在不同的计算机体系结构间恢复;
4.比逻辑备份和恢复的速度快:因为该过程是简单的文件复制,不需要了解文件的内部结构;
2.数据库文件在备份期间不能有更改:
1.实现这一点的方法取决于存储引擎;
2.对于InnoDB:需要关闭MySQL服务器;
3.对于MyISAM:锁定表以允许读取,但不允许更改;
4.可以使用快照,复制或专有方法:最大限度地减小对MySQL和应用程序的影响;

— 基于快照的备份;
1.创建数据的时间点“副本”;
2.提供一个逻辑上冻结的文件系统版本,可从中进行MySQL备份;
3.显著减少数据库和应用程序不可用的时间原始备份通常针对快照副本进行;
4.外部快照功能:
1.基于快照的备份使用MySQL外部的快照功能;
2.例如,如果MySQL数据库和二进制日志在具有相应文件系统的LVM2逻辑卷上,则可创建快照副本;
3.基于快照的备份最适合InnoDB之类的事务引擎,可以为InnoDB表执行热备份;对于其他引擎,可以执行温备份;
5.可伸缩:
1.快照备份是可伸缩的,因为执行快照所需的时间不会随数据库大小的增长而增加;
2.事实上,从应用程序的角度来看,备份期限几乎是零,但是,创建快照后,基于快照的备份几乎总是包括一个原始备份;

— 基于复制的备份;
1.MySQL复制可用于备份:
1.主服务器用于生产应用程序;
2.从属服务器用于备份目的;
2.这样可避免影响生产应用程序;
3.从属服务器的备份为逻辑备份或原始备份;
4.较高的成本:必须有另一台服务器和存储设备用于存储数据库的副本;
5.基于异步复制:
1.从属服务器相对于主服务器可能会有延迟;
2.如果在从属服务器读取二进制日志之前未清除二进制日志,这是可接受的;

— 测验;
a

— 二进制日志记录和增量备份;
1.在会话级别控制二进制日志记录,必须拥有SUPER特权:SET SQL_LOG_BIN = {0|1|ON|OFF};
2.执行逻辑备份或原始备份时刷新二进制日志:将二进制日志同步到备份;
3.逻辑备份和原始备份是完整备份:将备份所有表的所有行;
4.要执行增量备份,需复制二进制日志;
5.二进制日志可用于细粒度级恢复:可以标识导致损坏的事务并在恢复过程中跳过这些事务;

— 备份工具:概述;
1.逻辑备份的SQL语句;
2.执行SQL语句(用于锁定)与操作系统命令(用于生成二进制副本)组合的原始备份;
3.MySQL的其他原始备份工具:
1.MySQL Enterprise Backup:执行MySQL数据库热备份操作;该产品的设计目的就是为了高效且可靠地备份由InnoDB存储引擎创建的表,为完整起见,该产品还能备份其他存储引擎中的表;
2.mysqldump:该实用程序执行逻辑备份,可与任何数据库引擎一起使用;可以使用crontab(在Linux和UNIX中)和Windows任务调度程序(在Windows中)自动运行该实用程序;mysqldump没有任何跟踪或报告工具;
3.mysqlhotcopy:该实用程序执行原始备份,仅用于使用MyISAM或ARCHIVE数据库引擎的数据库;名称暗指mysqlhotcopy执行“热”备份,即不中断数据库可用性,但是,由于已对数据库进行了读取锁定,无法在备份过程中更改,因此最好将其描述为“温”备份;
4.第三方工具;
1.本课程主要是讲Oracle商业和社区工具;
2.补充Percona的Xtrabackup;

— MySQL Enterprise Backup;
1.热备份:
1.热备份是在数据库运行期间执行的,这种类型的备份不阻止正常的数据库操作,甚至能捕获备份进行期间发生的更改;
2.mysqlbackup是MySQL Enterprise Backup产品的基本命令行工具,对于InnoDB表,此工具可执行热备份操作;
2.温备份:
1.对于非InnoDB存储引擎,MySQL Enterprise Backup执行温备份;运行非InnoDB备份时,可以读取数据库表,但不能修改数据库;
3.增量备份:
1.备份自上一次备份以来有变化的数据;
2.主要用于InnoDB表或者只读或很少更新的非InnoDB表;
4.单文件备份:因为可以将单文件备份传输给其他进程(如磁带备份或scp之类的命令),因此可使用此技术将备份放在其他存储设备或服务器上,不会在原始数据库服务器上产生显著的存储开销;

— mysqlbackup;
使用mysqlbackup备份的原始文件
1.InnoDB数据:
1.ibdata*文件:共享表空间文件;
2..ibd文件:基于每个表的数据文件;
3.ib_logfile*文件:日志文件,从ib_logfile*文件提取的数据(代表在备份期间发生的更改的重做日志信息),存储在新备份文件ibbackup_logfile中;
2.要包括的数据目录中的所有文件:
1..opt文件:数据库配置信息;
2..TRG文件:触发器参数;
3..MYD文件:MyISAM数据文件;
4..MYI文件:MyISAM索引文件;
5..FRM文件:表数据字典文件;
6.TIPS:默认情况下,mysqlbackup备份数据目录中的所有文件,如果指定–only-known-file-types选项,则备份仅包括具有MySQL公认扩展名的其他文件;
3.mysqlbackup是一种易于使用的工具,适用于所有备份和恢复操作:
1.可以使用mysqlbackup联机备份InnoDB表以及生成对应于与InnoDB备份相同的binlog位置的MyISAM表的快照;
2.除了创建备份以外,mysqlbackup还可以将备份数据打包和解包,将在备份操作过程中对InnoDB表所做的任何更改应用于备份数据,以及将数据/索引和日志文件复制回其原始位置;
4.备份过程:
1.mysqlbackup打开到要执行备份的MySQL服务器的连接;
2.然后,mysqlbackup对InnoDB表执行联机备份;
3.当mysqlbackup运行几乎完成时,执行SQL命令FLUSH TABLES WITH READ LOCK,然后将非InnoDB文件(如MyISAM表和.frm文件)复制到备份目录;
1.如果此时未在数据库中长时间运行SELECT或其他查询,则MyISAM表很小,锁定阶段仅持续几秒钟;
2.否则,包括InnoDB类型表在内的整个数据库都会锁定,直到在备份之前开始的所有长时间查询完成;
4.mysqlbackup运行完成,并对表执行UNLOCK解锁;
5.基本用法:mysqlbackup -u -p –backup_dir= backup-and-apply-log
1.backup:执行备份初始阶段;
2.backup-and-apply-log:包括备份的初始阶段以及第二个阶段,即将InnoDB表放到最新的备份中,其中包括在备份运行期间对数据所做的任何更改;

— 使用mysqlbackup恢复备份;
基本用法:mysqlbackup –backup-dir= copy-back
1.:指定备份文件的存储位置;
2.copy-back:指示mysqlbackup执行恢复操作;
1.恢复操作将的内容(包括InnoDB和MyISAM索引)以及.frm文件恢复到其原始位置(由文件定义);
2.使用copy-back选项必须先关闭数据库服务器,然后才能使用mysqlbackup与copy-back选项;使用此
3.选项时,可将数据文件,日志及其他备份文件从备份目录复制回到其原始位置,并对其执行任何必需的后期处理;
4.在copy-back过程中,mysqlbackup无法从服务器查询其设置,因此从标准配置文件中读取datadir之类选项;
5.如果要恢复到不同的服务器,则可使用–defaults-file选项提供非标准默认设置文件;

补充:
— 使用mysqlbackup备份恢复:
1.完全备份还原:
1.备份数据库:
1.创建目录:mkdir -p /mysqlbackup; chown -Rf mysql:mysql /mysqlbackup;
2.创建备份:mysqlbackup –user=root –password= –with-timestamp –backup-dir=/mysqlbackup/ backup-and-apply-log;
2.还原数据库:
1.要把mysql服务关掉;
2.使用的备份必须是数据一致性的,即指定了–bakcup-and-apply-log参数的;
3.使用copy-back选项,这个操作会拷贝表/索引/元数据和其它恢复需要的文件恢复到原来的位置(定义在参数文件中);
4.还原数据库:mysqlbackup –defaults-file=/etc/my.cnf –backup-dir=/mysqlbackup/2015-08-25_17-20-34/ copy-back;
5.还原之后检查文件的权限(chown -Rf mysql:mysql data/ logs/),并开启mysql服务即可;
6.如果是需要还原到不同的目录的话,只需要修改–defaults-file参数指定的my.cnf文件即可;
2.增量备份还原:
1.增量备份分两种情况:
1.–incremental-base:使用这种方式,不需要知道两次备份的LSN(Log Sequence Number),只需要指定上一次备份(完全备份或者差异备份)的目录即可,mysqlbackup工具会从metadata文件中找到备份开始的位置.缺点是必须指定一系列目录的名称,可以使用应编码或者用shell实现,用–with-timestamp时需要制定规则;
2.–start-lsn:使用这种方式,就必须记录上次备份的LSN号,不需要关心上次备份的目录.缺点是需要知道上次备份的后的LSN,可以通过shell去获取,备份的目录可以使用–with-timestamp选项;
2.–incremental-base方式增量备份:mysqlbackup –defaults-file=/usr/local/mysql/my.cnf –incremental –incremental-base=dir:/mysqlbackup/2012-07-15_17-31-55/ –incremental-backup-dir=/mysqlbackup/incremental/sunday –with-timestamp backup;
3.–start-lsn方式增量备份:
1.查看上次备份结束的LSN号码:/path/meta/backup_variables.txt文件中end_lsn表示;
2.命令:mysqlbackup –defaults-file=/usr/local/mysql/my.cnf –incremental –start-lsn=xxxxx –with-timestamp –incremental-backup-dir=/mysqlbackup/incremental/sunday/ backup;
3.压缩备份功能:
1.数据压缩的功能只适用于InnoDb引擎的表;
2.压缩选项–compress只能用于完全备份,不能用于增量备份;
3.执行完全备份的命令:>mysqlbackup –compress –backup-dir=/mysqlbackup –with-timestamp backup;
然后执行apply-log选项;
4.部分备份(支持三种部分备份):
1.Leaving out files that are present in the MySQL data directory but not actually part of the MySQL instance. This operation involves the –only-known-file-types option;
2.Including certain InnoDB tables but not others. This operation involves the –include, –only-innodb, and –only-innodb-with-frm options;
3.Including certain database directories but not others. This operation involves the –databases and –databases-list-file options;
5.单文件备份:
1.可以通过指定backup-to-image参数来创建单文件备份,所有的源数据文件都必须在同一个目录下,所以尽量配置datadir, innodb_log_group_home_dir, and innodb_data_home_dir参数相同.
2.备份单一文件到绝对目录:mysqlbackup –backup-image=/backups/sales.mbi –backup-dir=/backup-tmp backup-to-image;
6.备份内存中数据:
1.mysqlbackup命令的–exec-when-locked选项可以在备份结束之前指定命令或者参数执行,而此时数据库是锁住的.这个命令可以创建或者拷贝一个附加的文件到备份目录;
2.可以用此选项来调用mysqldump命令备份MEMORY类型的表;

— mysqlbackup单文件备份;
1.基本用法:
mysqlbackup -u -p –backup-image= –backup_dir= backup-to-image
2.其他情形
1.标准输出:
… –backup-dir= –backup-image= -backup-to-image
2.将现有的备份目录转换为单个文件:
… –backup-dir= –backup-image= backup-dir-to-image

— 恢复mysqlbackup单个文件备份;
1.提取选择的文件:
mysqlbackup -u -p –backup-image= –backup_dir= image-to-backup-dir
2.其他情形:
1.列出内容:
… –backup-image= list-image
2.将现有的备份目录转换为单个文件:
… –backup-image= –src-entry= –dst-entry= extract
1.–src-entry:确定要从单文件备份中提取的文件或目录;
2.–dst-entry:与单文件备份配合使用,将单个文件或目录提取到用户指定的路径;

— 测验;
d

— mysqlhotcopy;
1.Perl脚本:
1.备份MyISAM和ARCHIVE表;
2.使用FLUSH TABLES,LOCK TABLES以及cp或scp可以进行数据库备份;
3.在数据库目录所在的同一台计算机上运行:以便在表锁定期间复制表文件;
4.仅限Unix;
5.MySQL服务器必须处于运行状态:以便连接到服务器;
6.mysqlhotcopy的操作速度很快,因为它直接复制表文件,而不是通过网络备份表文件;
2.基本用法:mysqlhotcopy -u -p
3.选项:
1.–flush-log:在所有表都锁定后刷新日志;
2.–record_log_pos=db_name.tbl_name:在指定的数据库db_name和表tbl_name中记录主从服务器状态;

— 原始InnoDB备份;
1.备份过程:
1.在复制操作期间停止服务器;
2.验证服务器是否正常关闭,没有出错;
3.生成每个组件的副本:
1.每个InnoDB表一个.frm文件;
2.表空间文件;
1.系统表空间;
2.基于每个表的表空间;
3.InnoDB日志文件;
4.my.cnf文件;
4.重新启动服务器;
TIPS:所有数据库中的所有InnoDB表必须一起备份,因为InnoDB会在系统表空间中集中维护某些信息;
2.恢复:
1.要使用原始备份恢复InnoDB表,请停止服务器;
2.替换其副本在备份过程中生成的所有组件;
3.然后重新启动服务器;
TIPS:需要替换服务器上的所有现存的表空间文件,不能使用原始备份将一个表空间添加到另一个表空间;

— 原始MyISAM和ARCHIVE备份;
1.要生成MyISAM或ARCHIVE表,需复制MySQL用于代表该表的文件:
1.对于MyISAM,这些文件是.frm,.MYD和.MYI文件;
2.对于ARCHIVE表,这些文件是.frm和.ARZ文件;
3.在此复制操作过程中,其他程序(包括服务器)不能使用该表;
4.为了避免服务器交互问题,要在复制操作过程中停止服务器;
5.注:锁定表而不关闭服务器的做法在Linux系统上有效,在Windows上,文件锁定行为会导致可能无法复制被服务器锁定的表的表文件,在这种情况下,需要停止服务器后再复制表文件;
2.在服务器运行期间,锁定要复制的表:
mysql> USE mysql
mysql> FLUSH TABLES users WITH READ LOCK;
3.执行文件系统复制;
4.启动新的二进制日志文件:FLUSH LOGS;
1.新二进制日志文件包含在备份之后更改了数据的所有语句(以及所有后续的二进制日志文件);
5.在文件系统复制后解除锁定:UNLOCK TABLES;
6.恢复:要从原始备份中恢复MyISAM或ARCHIVE表,应停止服务器,将备份表文件复制到相应的数据库目录中,然后重新启动服务器;

— LVM快照;
1.在以下情况下,使用LVM快照执行原始备份:
1.主机支持LVM:例如,Linux支持LVM2;
2.包含MySQL数据目录的文件系统在逻辑卷上;
2.备份过程:
1.生成包含MySQL数据目录的逻辑卷的快照:在备份非InnoDB表时,使用FLUSH TABLES WITH READ LOCK;
2.从快照执行原始备份;
3.删除快照;

补充:LVM快照原理;
1.在支持LVM的系统(如Linux)上,可以创建要包含MySQL数据目录的逻辑卷;
2.可以创建该卷的快照,该快照的行为就像是逻辑卷的即时副本,LVM使用称为“写入时复制”(copy-on-write)的机制创建最初不含数据的快照;
3.在从新创建的快照读取文件时,LVM会从原始卷读取这些文件,当原始卷发生变化时,LVM会在原始卷上的数据发生变化之前,立即将其复制到快照,因此,在生成快照以来发生变化的任何数据都以其原始形式存储在快照中;
这样做的结果是,当从快照读取文件时,将获得在创建快照时存在的数据版本;
4.因为快照几乎是即时的,因此可以假定在生成快照过程中底层数据没有发生任何变化,这使得快照对于在不关闭服务器的情况下备份InnoDB数据库非常有用;
5.要创建快照,可使用以下语法:lvcreate -s -n -L ;选项-s指示lvcreate创建快照,其他选项指定新快照的名称和大小以及原始卷的位置;

1.例如,假定有一个卷组VG_MYSQL和一个逻辑卷lv_datadir:lvcreate -s -n lv_datadirbackup -L 2G /dev/VG_MYSQL/lv_datadir;前一条语句创建快照lv_datadirbackup,其保留大小为2GB;
2.如果只是短时间需要该快照,则保留大小可以比原始卷的大小少很多,因为快照的存储仅包含在原始卷中发生 更改的数据块;
3.例如,如果要使用快照执行备份,则保留大小仅存储在执行备份以及删除快照的时间内所做的更改,可以像挂载标准卷一样挂载快照;挂载了快照后,就像处理其他任何原始备份一样,从该卷执行原始备份(例如,通过使用tar或cp);
4.从快照备份时,数据库在备份过程中不能有更改,您肯定会获得与备份时一样的一致数据文件版本,无需停止服务器;
5.随着时间的推移,快照的空间要求通常会增长到原始卷的大小,此外,对原始卷上数据块的每项初始数据更改将导致两次向卷组写入数据:请求的更改和对快照的写入时复制;这可能会影响快照保留期间的性能;
6.由于以上原因,应在执行了备份之后尽快删除快照,要删除由前一条语句创建的快照,可使用以下语句:lvremove VG_MYSQL/lv_datadirbackup;

— 原始二进制可移植性;
1.可在MySQL服务器之间复制二进制数据库:当将一台计算机上生成的二进制备份拿到具有不同体系结构的另一台计算机上时,二进制可移植性会很有用;
2.InnoDB:数据库的所有表空间和日志文件都可直接复制,源系统与目标系统上的数据库目录名称必须相同;
3.MyISAM/ARCHIVE:单个表的所有文件都可直接复制;
4.Windows兼容性:
1.在Windows系统上,MySQL服务器在内部存储小写的数据库和表名称;
2.对于区分大小写的文件系统,可使用选项文件语句:lower_case_table_names=1;

— mysqldump;
1.将表内容转储到文件:
– 所有数据库,特定数据库或特定表;
– 允许备份本地服务器或远程服务器;
– 与存储引擎无关;
– 以文本格式写入:包含用于重新创建表的CREATE TABLE和INSERT语句的SQL格式转储文件;
– 可移植;
– 卓越的复制/移动策略;
– 适用于小规模导出,但不适用于完整备份解决方案;
2.基本用法:mysqldump –user= –password= –opt db_name > backup.file

— 与mysqldump保持一致;
1.仅限–master-data选项:
1.在备份过程中锁定表;
2.在备份文件中记录binlog位置;
2.–master-data和–single-transaction选项一起使用:不锁定表,仅保证InnoDB表一致性;
3.–lock-all-tables:通过锁定表实现一致性;
4.–flush-logs:启动新的二进制日志;

— mysqldump输出格式选项;
1.删除选项:
1.–add-drop-database:将一条DROP DATABASE语句添加到每条CREATE DATABASE之前;
2.–add-drop-table:将一条DROP TABLE语句添加到每条CREATE TABLE语句之前;
2.创建选项:
1.–no-create-db:不生成CREATE DATABASE语句;
2.–no-create-info:不生成CREATE TABLE语句;
3.–no-data:创建数据库和表结构,但不转储数据;
4.–no-tablespaces:指示MySQL服务器不写入任何CREATE LOGFILE GROUP或CREATE TABLESPACE语句到输出;
3.MySQL编程组件:
1.–routines:从已转储的数据库中转储存储例程(过程和函数);
2.–triggers:转储每个已转储表的触发器;
4.一个选项中的最高选项(–opt):这是用于创建高效完整的备份文件的最常用选项的快捷方式;

— 恢复mysqldump备份;
1.使用mysql重新装入mysqldump备份:mysql –login-path= < backup_file.sql 1.如果备份文件不存在,则指定数据库:USE db_name; 2.如果通过调用mysqldump与--database或--all-databases选项创建转储文件,则在从转储文件重新装入时,不需要指定目标数据库名称; 2.从一个数据库复制到另一个数据库(管道技术):mysqldump -u -p

— 存储例程;
1.存储例程是存储在服务器中的一组指定的SQL语句:
1.客户机无需不断重新发出单个语句,而可以改为引用存储例程;
2.存储例程类型:
1.存储过程:通过CALL语句调用过程,这些过程可以使用输出变量或结果集传回值;
2.存储函数:在语句中调用函数,这些函数可返回标量值;

— 存储例程的使用;
1.集中式客户机功能:通过存储例程,您可以在数据库中集中创建一个语句或一系列语句,以供使用不同编程语
言编写或在不同平台上运行的多个客户机应用程序使用;
2.安全性:
1.存储例程为需要最高安全级别的应用程序提供了一个解决方法;
2.例如,银行针对所有常用操作均使用存储过程和函数,这提供了一致,安全的环境;
3.可对例程进行编码,以确保正确记录了每个操作,在此类设置中,应用程序和用户无法直接访问数据库表,只能执行特定的存储例程;
4.在Oracle中还可以针对存储过程进行加密;
3.性能改进:
1.客户机按名称调用存储例程,而不发送例程中所包含的整组语句;
2.因为服务器和客户机之间需要发送的信息变少了,所以存储例程可提升性能;
3.哪岂不是数据库服务器端的压力变重了?
4.函数库:通过存储例程,可以在数据库服务器中使用函数库,这些库用作数据库的API;

— 存储例程:问题;
1.增加了服务器负载:
1.在数据库自身中执行存储例程可增加服务器负载并降低应用程序的性能;
2.可以运行测试并运用常识来确保在数据库本身中包含逻辑所带来的方便比可能引发的性能问题更为显著;
2.开发工具有限:
1.MySQL中支持存储例程的开发工具不像在更通用的编程语言中那样成熟和明确;
2.此局限性会使存储例程的编写和调试过程更加困难,在决策过程中需要加以考虑;
3.语言功能和速度有限:
1.虽然在许多情况下在数据库本身中包含逻辑具有很大的优势,但是与其他编程语言相比,在可实现的内容方面仍有局限;
2.存储例程在数据库上下文中执行,与客户机应用程序中的例程相比,在处理大量数据时性能较好,但是客户机应用程序语言可能具有更强大,更通用的处理,集成或其他库功能;
4.调试和概要分析功能有限;

— 执行存储例程;
1.执行过程:使用CALL语句来调用存储过程,存储过程使用输出变量或结果集传回值;
2.执行函数:从语句内部调用函数(即,通过调用相应函数的名称),函数返回标量值;
3.每个存储例程均与特定数据库相关联,有多重含义:
1.USE :调用例程时,MySQL会在该例程运行期间执行隐式USE ,不能在存储例程内发出USE语句;
2.限定名称:可使用例程的数据库名称限定例程名称;执行此操作可引用当前数据库以外的例程;例如,要调用与test数据库相关联的存储过程p或函数f,请使用CALL test.p()或test.f();
3.删除数据库时,也会删除与其关联的所有存储例程;
4.SELECT语句:
1.仅限存储过程:函数不可以使用;
2.直接将结果集发送到客户机;

— 存储过程:示例;
mysql> USE world;
mysql> DELIMITER //
mysql> CREATE PROCEDURE record_count ()
BEGIN
SELECT (SELECT COUNT(*) FROM Country) ‘CountryCount’, (SELECT COUNT(*) FROM City) ‘CityCount’, (SELECT COUNT(*) FROM CountryLanguage) ‘CLCount’;
END//
mysql> DELIMITER ;

mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1(IN v_name varchar(20))
BEGIN
SELECT t1.Name, sum(t2.population) FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode AND t1.name = v_name GROUP BY t1.Name;
END$$
mysql> DELIMITER ;
mysql> CALL p1(‘China’);

mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(IN v_name varchar(20), OUT v_population INT)
BEGIN
SELECT sum(t2.population) INTO v_population FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode AND t1.name = v_name GROUP by t1.Name;
SELECT 0;
END$$
mysql> call p2(‘China’, @v_1);
mysql> select @v_1;

1.复合语句:
1.通过在存储例程中使用BEGIN…END语法并使用触发器,可以创建复合语句;
2.BEGIN…END块可包含零个或多个语句,空复合语句是合法的,而且复合语句中的语句数量没有上限;
2.分隔符:
1.在BEGIN…END语法中,必须使用分号[;]终止每个语句;
2.由于MySQL客户机使用分号作为SQL语句的默认终止字符,在以交互方式或针对批处理使用MySQL命令行客户机时,必须使用DELIMITER语句更改此设置;
3.此更改可确保客户机不会将复合语句中的分号解释为语句分隔符,并确保客户机不会过早地将CREATEPROCEDURE语句发送到服务器;当创建存储例程的语句以[//]终止时,客户机会先将该语句发送到服务器,然后再发出第二个DELIMITER语句将语句分隔符重置为分号;

— 存储函数:示例;
mysql> USE world;
mysql> DELIMITER //
mysql> CREATE FUNCTION pay_check (gross_pay FLOAT(9,2), tax_rate FLOAT (3,2))
RETURNS FLOAT(9,2) NO SQL
BEGIN
DECLARE net_pay FLOAT(9,2) DEFAULT 0;
SET net_pay=gross_pay – gross_pay * tax_rate;
RETURN net_pay;
END//
mysql> DELIMITER ;

1.RETURNS子句:用于确定此函数要返回的值的类型;
2.特征:通过多个特征,可确定有关例程所使用的数据的性质;
1.CONTAINS SQL表示例程不包含用于读取或写入数据的语句(为默认值);
2.NO SQL表示例程不包含任何SQL语句;
3.READS SQL DATA表示例程包含用于读取数据的语句(例如,SELECT)而不包含用于写入数据的语句;
4.MODIFIES SQL DATA表示例程包含用于写入数据的语句(例如,INSERT/DELETE/UPDATE);
5.在启用了二进制日志记录后,如果创建函数时未指定特征项,则MySQL会产生一个错误;
3.DECLARE语句:
1.在存储例程中使用DECLARE语句来声明本地变量并初始化用户变量;
2.可将DEFAULT子句添加到DECLARE语句的结尾,以便为用户变量指定初始值;如果省去DEFAULT子句,则用户变量的初始值为NULL;
4.SET语句:通过SET语句,您可以使用=或:=作为赋值运算符来向定义的变量赋值;
5.RETURN语句:用于终止存储函数的执行,并将值表达式返回给函数调用方;

— 检查存储例程;
1.SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION:
1.这些语句为MySQL扩展,类似于SHOW CREATE TABLE;
2.这些语句返回可用于重新创建指定例程的具体字符串;
3.这些语句的主要限制之一是您必须知道过程或函数的名称,并且必须确定其为过程或函数,然后才能尝试查看相应信息;
2.SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS:
1.这些语句特定于MySQL;
2.它们可返回例程的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.这些语句有一个优点:可基于LIKE模式显示特定例程,如果未指定任何模式,则会根据所使用的语句,列出所有存储过程或所有存储函数的信息;
3.INFORMATION_SCHEMA.ROUTINES:包含存储例程(过程和函数)的相关信息,并返回可同时在SHOW CREATE …和SHOW … STATUS语句中找到的大部分详细信息,以包 含用于创建存储例程的实际语法;
mysql> SELECT routine_name, routine_schema, routine_type, definer
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_name LIKE ‘film%’;
4.MySQL系统数据库中与编程组件关联的表
1.mysql.event表,包含MySQL服务器中所存储事件的相关信息;
2.mysql.proc表,包含MySQL服务器中的存储过程和函数的相关信息;
3.mysql.procs_priv表,为引用存储过程的用户提供访问控制授予详细信息;

— 存储例程和执行安全性;
1.存储过程和函数的使用涉及多个特权:
1.CREATE ROUTINE:创建存储例程;
2.ALTER ROUTINE:更改或删除存储例程;
3.EXECUTE:执行存储例程;
4.GRANT OPTION:将特权授予其他帐户;
2.默认操作:
1.创建存储例程时,MySQL会自动向您的帐户授予对该例程的EXECUTE和ALTER ROUTINE特权;
2.拥有撤消特权以及GRANT OPTION特权的用户稍后可撤消或删除这些特权;在创建例程后,可以通过发出SHOW GRANTS语句来验证这些特权;
3.授予特权:
1.当在全局级别或数据库级别授予所有特权时,GRANT ALL语句包括除GRANT OPTION之外的所有存储例程特权;要授予GRANT OPTION特权,请在该语句结尾包含WITH GRANT OPTION子句;
2.可以在单个例程级别授予EXECUTE,ALTER ROUTINE和GRANT OPTION特权,但仅限于已经存在的例程;
3.要授予对单个例程的特权,可使用其数据库名称限定例程,并提供关键字PROCEDURE或FUNCTION以指示例程类型;
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO ‘magellan’@’localhost’ WITH GRANT OPTION;

— 测验;
A

— 触发器;
1.据库触发器是数据库中所维护的命名数据库对象,将在修改表中数据时被激活;
2.功能:
1.在插入或更新数据之前对数据进行检查并验证删除和更新;
2.充当数据过滤器,在插入或更新之前修改超出范围的数据;
3.修改INSERT/UPDATE/DELETE的行为方式;
4.对于不支持外键的存储引擎,模仿外键的行为;
5.提供日志记录功能;
6.自动创建汇总表;
3.功能概括:
1.触发器可以提高表中数据的功能和安全性级别;
2.可以使用触发器控制对特定数据的访问权限,执行特定日志记录或对数据本身进行审计;

— 创建触发器;
1.CREATE TRIGGER语句;
CREATE TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } ON table_name
FOR EACH ROW triggered_statement
2.例子:
CREATE TRIGGER City_AD AFTER DELETE
ON City FOR EACH ROW
INSERT INTO DeletedCity(ID, Name) VALUES (OLD.ID, OLD.Name);

1.trigger_name是为触发器指定的名称;
2.table_name是要与触发器关联的表的名称;
3.BEFORE和AFTER指示激活触发器的时间(是在触发事件之前或是之后);
4.而INSERT/UPDATE/DELETE指示具体的事件;
5.表名OLD和NEW是指触发器可查看的虚拟表,这些表分别包含UPDATE或DELETE语句所修改的数据的旧版本,或INSERT或UPDATE语句所添加的数据的新版本;

补充:模拟物化视图的例子;
1.创建一个汇总表;
CREATE TABLE ps(name char(52), population BIGINT);
INSERT INTO ps
SELECT t1.NAME, sum(t2.population) FROM Country t1, City t2 WHERE t1.Code = t2.CountryCode GROUP by t1.Name order by 2 desc;
2.创建更新触发器,一种动作时间的触发器只能创建一个;
DELIMITER $$
CREATE TRIGGER City_AU AFTER UPDATE
ON City FOR EACH ROW
UPDATE ps SET population = population + new.population – old.population;$$
DELIMITER ;
ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’
3.更新记录用来模拟记录发生变化;
SELECT * FROM City WHERE id = 1890;
UPDATE City SET population = population + 1 WHERE id = 1890;
4.查看汇总表反应变化结果;
SELECT * FROM ps WHERE name = ‘China’;
SELECT sum(population) FROM City WHERE CountryCode=’CHN’;

— 触发器事件;
1.BEFORE和AFTER关键字是指触发器的激活时间,相对于数据修改语句(INSERT/UPDATE/DELETE)将更改写入底层数据库的时间;
2.BEFORE关键字可使触发器在涉及的数据修改之前执行,可使用BEFORE触发器捕获无效数据条目并在写入表之前对其进行更正或拒绝;
3.AFTER关键字定义在数据修改成功后执行的触发器,可使用AFTER触发器记录或审计数据库中的数据修改;

— 触发器错误处理;
MySQL按如下方式处理触发器执行期间发生的错误:
1.BEFORE触发器失败:包含相应行操作的事务将回滚;
2.AFTER触发器执行:BEFORE触发器事件和行操作必须成功执行;
3.对于非事务表,事务不可用:只有触发了触发器的语句会回滚,但在发生错误之前执行的所有更改仍然会生效;

— 检查触发器;
1.SHOW CREATE TRIGGER trigger_name:
1.此语句返回可用于重新创建指定触发器的具体字符串;
2.您必须知道触发器的名称才能运行此语句;
3.对于SHOW CREATE TRIGGER语句,不存在LIKE或WHERE语法;
2.SHOW TRIGGERS:
1.此语句为MySQL扩展;
2.它可返回触发器的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.此语句有一个优点:可基于LIKE模式或WHERE子句中提供的条件来显示特定触发器;如果未指定条件,则此语句会显示所有触发器的信息;
3.INFORMATION_SCHEMA.TRIGGERS:
1.包含SHOW命令所显示的所有数据;
2.完整地呈现在所有数据库中可用的触发器;

— 删除触发器;
1.使用如下语法可显式删除触发器:
1.DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
2.使用IF EXISTS可防止因尝试删除不存在的触发器而出现的错误;
2.通过删除以下项,可隐式删除触发器:
1.针对其定义触发器的表;
2.包含触发器的数据库;

— 对触发器的限制;
1.不允许使用的语句包括:
1.SQL预处理语句;
2.显示或隐式COMMIT和ROLLBACK;
3.返回结果集的语句(无法返回结果集,请通过使用SELECT …INTO var_list,或者通过使用光标和FETCH语句方法在触发器中处理结果集);
4.FLUSH语句;
5.用于修改要应用触发器的表的语句;
2.以下更改不会触发触发器:
1.级联外键所导致的更改;
2.在基于行的复制过程中导致的更改;

— 触发器特权;
1.要执行CREATE TRIGGER和DROP TRIGGER命令,需要有TRIGGER特权;
2.需要有其他特权才能在触发器中使用OLD和NEW:
1.要使用SET NEW.col_name = value为列赋值,需要对该列拥有UPDATE特权;
2.要在表达式中使用NEW.col_name以引用新的列值,需要对该列拥有SELECT特权;

— 测验;
b

— 事件;
1.事件:
1.MySQL事件是按调度表运行的任务,这些事件可称为“调度事件”;
2.创建事件时,会将其创建为命名数据库对象,其中包含在特定时间执行或定期重复发生的SQL语句(或存储过程);
3.与UNIX crontab或Windows任务调度程序类似;
CREATE EVENT event_name
ON SCHEDULE schedule DO sql_statement
4.必须选项:
1.event_name:事件是模式对象,与表,存储过程和触发器一样;event_name必须是有效的标识符,且可以按正常方式通过模式名称进行引用和/或限定;
2.schedule:调度表是一种规则,用于指定MySQL执行与事件相关联的操作的时间;
3.sql_statement:必须包含按调度表执行的有效SQL语句或存储过程语句;此语句受到的限制与适用于存储函数和动态SQL的限制相同;通常会使用CALL语句调用过程来执行实际操作;
2.事件调度程序
1.创建事件后,该事件将存储在数据库中,以便按调度表执行;
2.event_scheduler线程会监视所有事件的调度表,在到达调度表中的时间时,该线程会启动一个新的线程来执行每个事件;
3.默认情况下,event_scheduler线程设置为OFF,您必须显式启用该线程,方法为修改全局event_scheduler服务器变量的值,将其设置为ON;还可以通过将服务器变量添加到选项文件中(以便更改在启动时生效)或动态使用SET语法来实现;
4.如果在event_scheduler设置为DISABLED的情况下启动服务器,则无法在MySQL正在运行时通过SET语句将其启用;而必须停止MySQL并在启用该选项的情况下重新启动MySQL;
5.启用event_scheduler线程后,您可以在SHOW PROCESSLIST的输出(以及INFORMATION_SCHEMA等效项PROCESSLIST)中看到该线程;
3.事件语句:
1.SET GLOBAL event_scheduler = {ON | OFF};
2.CREATE EVENT:创建事件;
3.ALTER EVENT:更改事件;
4.DROP EVENT:删除事件;

— 调度表;
1.调度表是用于指定事件执行时间的规则;
2.有两种类型的调度操作:
1.执行一次(使用AT关键字);
2.重复执行(使用EVERY关键字):必须定义事件的重复频率;还可以定义一个时间段,以确定应重复执行事件的期限;
3.由事件调度程序执行调度事件:
1.事件调度程序是mysqld进程中的一个单独线程,负责执行调度事件;
2.调度程序会检查是否应执行事件,如果应执行事件,则会创建新的连接来执行操作;
4.使用事件来自动地定期执行任务;
5.示例:
1.表数据的常规CSV转储:加载数据仓库或者将数据导出到文件中;
2.ANALYZE TABLE;
3.自动地定期执行(维护)任务,如更新汇总表或刷新查询中的表(物化视图仿真);

补充:
1.创建一个一次性执行的作业,2分钟10秒后插入一条数据到test.t中;
CREATE EVENT e_at_insert_to_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL ‘2:10’ MINUTE_SECOND
DO INSERT INTO test.t values(NOW());
2.创建一个重复执行的作业,每天3点执行一次;
CREATE EVENT e_every_insert_to_table
ON SCHEDULE EVERY 1 DAY STARTS date_add(curdate(), INTERVAL 3 HOUR)
DO INSERT INTO test.t values(NOW());
3.DO子句中使用BEGIN…END关键字;
DELIMITER $$
CREATE EVENT e_event_do
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
INSERT INTO test.t values(NOW());
COMMIT;
END$$
DELIMITER ;

补充:
1.SCHEDULE子句的语法:
AT timestamp [+ INTERVAL interval] | EVERY interval [STARTS timestamp [+ INTERVAL interval]] [ENDS timestamp [+ INTERVAL interval]]
2.SCHEDULE子句可包含以下变量元素:
1.timestamp:DATETIME或TIMESTAMP类型的表达式;
2.interval:用于指定持续时间,持续时间的表示方法为,指定一个整数数量,后跟用于定义特定种类持续时间的关键字;
– YEAR
– QUARTER
– MONTH
– DAY
– HOUR
– MINUTE
– WEEK
– SECOND
– YEAR_MONTH
– DAY_HOUR
– DAY_MINUTE
– DAY_SECOND
– HOUR_MINUTE
– HOUR_SECOND
– MINUTE_SECOND

— 事件调度程序和特权;
1.必须拥有SUPER特权才能设置全局event_scheduler变量;
2.必须拥有EVENT特权才能创建,修改或删除事件;
3.使用GRANT分配特权(仅限在模式级别):
mysql> GRANT EVENT ON myschema.* TO user1@srv1;
mysql> GRANT EVENT ON *.* TO user1@srv1;
4.使用REVOKE取消事件特权:
1.REVOKE EVENT ON myschema.* FROM user1@srv1;
2.撤消用户帐户的EVENT特权不会删除或禁用该帐户已创建的任何事件;
5.mysql表:
1.用户的EVENT特权存储在mysql.user和mysql.db表的Event_priv列中;
2.在这两种情况下,该列均存储值“Y”或“N”之一,“N”是默认值;仅当给定用户拥有全局EVENT特权时,该用户的mysql.user.Event_priv值才会设为“Y”;
3.对于模式级别的EVENT特权,GRANT会在mysql.db中创建一个行,并按如下所示设置该行的列值:
1.Db:模式的名称;
2.User:用户的名称;
3.Event_priv:“Y”;
4.不必直接操作这些表,因为GRANT EVENT和REVOKE EVENT语句会对其执行所需的操作;

— 事件执行特权;
1.事件使用事件定义者的特权进行执行:如果定义者无权执行某任务,则事件无法执行该任务;
2.示例
1.user1@srv1只能为myschema创建SELECT事件:
CREATE EVENT e_store_ts
ON SCHEDULE EVERY 10 SECOND
DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
2.由于缺少INSERT特权,该事件不会在表中创建任何行;
3.可以通过错误日志查看对应的信息;

— 检查事件;
1.SHOW CREATE EVENT event_name:
1.此语句显示重新创建给定事件所需的CREATE EVENT语句;
2.必须提供事件名称才能查看该事件的相关信息;
2.SHOW EVENTS:
1.此语句为MySQL扩展;
2.它可返回事件的特征,如数据库/名称/类型/创建者以及创建和修改日期;
3.此语句有一个优点:可基于LIKE模式或WHERE子句中提供的条件来显示特定事件
3.INFORMATION_SCHEMA.EVENTS;

— 删除事件;
1.使用如下语法可显式删除事件:DROP EVENT [IF EXISTS] [schema_name.]event_name;
2.使用IF EXISTS可防止因尝试删除不存在的事件而出现的错误;
3.您必须对包含要删除的事件的数据库拥有EVENT特权;

— 测验;
b

— SIGNAL和RESIGNAL;
1.MySQL支持使用SIGNAL和RESIGNAL:
1.可用于引发特定的SQLSTATE;
2.与其他语言中的异常处理类似;
3.在处理错误时用于高级流控制;
4.这些命令为存储例程和触发器提供了一种将错误返回给应用程序或最终用户的方法;
2.使用SIGNAL抛出错误或警告状态:
1.SIGNAL用于向处理程序,应用程序的外侧部分或客户机提供错误信息;
2.例如,在存储过程中使用SIGNAL向调用该存储过程的代码抛出错误;该代码可以以适当的方式处理该错误;
3.使用RESIGNAL转发先前的SIGNAL所引发的错误或警告状态;
1.RESIGNAL用于在条件处理程序执行期间传递可用的错误条件信息;
2.可在存储过程或函数,触发器或事件内的复合语句中使用RESIGNAL;
3.例如,如果存储过程p调用了存储过程q,且q使用SIGNAL抛出一个错误,则p可声明一个处理程序,以处理来自q的信号;p 中的处理程序可使用RESIGNAL语句向调用p的代码抛出相同的错误信息;

— 课后练习;

补充:动态执行sql语句:
1.在ORACLE中动态执行sql语句使用:EXECUTE IMMEDIATE ‘sql_statement’;
2.在MySQL中动态执行sql的语法:
1.PREPARE stmt_name FROM preparable_stmt;
2.EXECUTE stmt_name [USING@var_name [,@var_name ]…];
3.{DEALLOCATE | DROP} PREPARE stmt_name;
3.例子:
1.准备sql语句:SET v_sql= concat(‘SELECT’, ‘ * ‘, ‘ FROM ‘, ‘ DUAL;’);
2.SET @v_sql=v_sql; — 注意很重要,将连成成的字符串赋值给一个变量,可以之前没有定义,但要以@开头;
3.PREPARE stmt FROM @v_sql; — 预处理需要执行的动态SQL,其中stmt是一个变量;
4.EXECUTE stmt; — 执行SQL语句,如果sql语句中有变量的话,可以使用USING子句后面跟变量的值;
5.DEALLOCATE PREPARE stmt; — 释放掉预处理段;

— 导出和导入数据;
1.可用的导出/导入操作类型:
1.使用SELECT…INTO OUTFILE将数据导出到文件;
2.使用LOAD DATA INFILE语句从文件中导入数据;
2.数据导出操作的用途:
1.将数据库从一个服务器复制到另一个服务器:
1.从同一主机;
2.复制到另一主机;
2.使用实际数据测试新MySQL版本;
3.将数据从一个RDBMS传输到另一个RDBMS,异构数据库间同步;

— 通过同时使用SELECT和INTO OUTFILE来导出数据;
1.将SELECT与INTO OUTFILE一起使用:
1.将结果集直接写入文件;
2.除非另有说明,否则,假定文件路径位于数据库数据目录下:SELECT * INTO OUTFILE ‘/tmp/City.txt’ FROM City;
2.输出文件:
1.写入服务器主机,而不是通过网络写入客户机;文件不能已存在;
2.使用文件系统访问权限进行创建:用户必须要有创建文件的权限,生成的文件属于该用户,文件对所有用户可读;
3.针对语句所选的每一行,都包含对应的一行;默认情况下,列值由制表符分隔,而行在换行符处终止;
补充:
1.从服务器端导出文本文件到服务器:SELECT * FROM tbname INTO OUTFILE ‘/path’;
2.从客户端导出文本到客户端;mysql -e ‘SELECT * FROM tbname’ > ‘/path’;

— 使用数据文件格式说明符;
1.默认情况下,将SELECT和INTO OUTFILE一起使用将输出TSV(Tab-Separated Values);
1.“制表符分隔值”文件用制表符分隔列,用换行符分隔记录;
2.CSV(Comma-Separated Values):使用逗号[,]符号分割列,用换行符分隔记录;
2.您可以选择替代的分隔符:
FIELDS
TERMINATED BY ‘string’
ENCLOSED BY ‘char’
ESCAPED BY ‘char’
LINES TERMINATED BY ‘string’
– FIELDS子句指定如何显示列:
1.TERMINATED BY指定字段分隔符,默认情况下是制表符;
2.ENCLOSED BY指定如何引住列值,默认设置为不使用引号(即,默认值为空字符串);
3.ESCAPED BY指明当表示换行符或制表符之类的非打印字符时要使用的转义符,默认转义符是反斜杠(\)字符;
– LINES TERMINATED BY子句指定行分隔符,默认情况下是换行符;

— 转义序列;
1.行终结符说明符:
1.默认为换行符:命令行终结符包括换行符和回车/换行符对;(默认的换行符终结符常见于Linux系统,而回车/换行符对常见于Windows系统)
2.幻灯片中所显示的所有序列单独使用或者在较长的字符串中使用,但\N除外,该序列只有在单独出现时才用作NULL;
2.ESCAPED BY选项:
1.仅控制数据文件中值的输出,它不会更改MySQL解释语句中特殊字符的方式;
2.如果通过写入ESCAPED BY ‘@’指定数据文件转义符为“@”,并不表示必须使用“@”来转义语句中其他的特殊字符;您必须使用 MySQL的转义符(反斜杠:\)来转义语句中的特殊字符,使用LINES TERMINATED BY ‘\r\n'(而不是LINES TERMINATED BY ‘@r@n’)之类的语法;

— 使用LOAD DATA INFILE导入数据;
1.查看帮助:HELP LOAD DATA;
2.说明:
1.将文件中的行值读入表;
2.是SELECT…INTO OUTFILE的逆向操作;
3.使用相似的子句和格式说明符:
LOAD DATA INFILE ‘/tmp/City.txt’ FIELDS TERMINATED BY ‘\t’
INTO TABLE City;
4.假定文件位于服务器主机上的数据库数据目录中
补充:
1.从服务器端导入文本文件服务器:LOAD DATA INFILE ‘/path’;
2.从客户端导入文本文件到服务器:LOAD DATA LOCAL INFILE ‘/path’;

— 跳过或变换输入数据;
1.要忽略数据文件中的行,请使用IGNORE n LINES:
1.mysql> LOAD DATA INFILE /tmp/City.txt’ INTO TABLE City IGNORE 2 LINES;
2.忽略数据文件的开始部分;当文件以列名行(而不是数据值行)开始时,请使用此子句;
2.要忽略或变换列值,请执行以下操作:
1.在语句列的列表中指定用户变量(而不是列名称);
2.(可选)通过使用SET子句变换列(该子句的语法类似于UPDATE语句中的SET子句):
LOAD DATA INFILE ‘/tmp/City.txt’
INTO TABLE City ( @skip, @Name, CountryCode, @District, Population)
SET name=CONCAT(@Name,’ ‘,@District);
3.该语句将忽略SET表达式中未使用的变量的值;
4.在将从文件中读取的数据值插入表中之前,LOAD DATA INFILE将对其进行变换,处理用户变量中所包含的值;

eg:
1.创建表;CREATE TABLE City1 LIKE City;
2.修改列长度:ALTER TABLE City MODIFY COLUMN name char(100);
3.导入数据:
LOAD DATA INFILE ‘/tmp/City1.txt’
INTO TABLE City1 ( @skip, @Name, CountryCode, @District, Population)
SET name=CONCAT(@Name,’ ‘,@District);
4.查看数据:SELECT * FROM City1 LIMIT 10;

— 重复记录;
1.要控制LOAD DATA INFILE对包含重复的主键或唯一键的行的处理方式,请执行以下操作:
1.使用IGNORE关键字放弃包含重复键的行;
2.使用REPLACE关键字将这些行替换为文件中包含相同键的版本;
2.这与使用INSERT和REPLACE语句控制重复项的方法类似:
1.IGNORE;
2.ON DUPLICATE KEY UPDATE;
3.其重复项处理行为根据数据文件是位于服务器主机上还是位于客户机主机上而稍有不同,所以使用LOAD DATA INFILE时,必须考虑数据文件的位置:
1.从服务器主机装入文件:
1.默认情况下,输入记录造成重复键违规将产生一个错误,不会装入数据文件的剩余部分,该点之前的已处理记录将被装入表中;
2.如果在文件名后提供IGNORE关键字,将忽略造成重复键违规的新记录,并且语句不会生成错误;LOAD DATA INFILE将处理整个文件,装入所有不包含重复键的记录,并放弃剩余记录;
3.如果在文件名后提供REPLACE关键字,造成重复键违规的新记录将替换表中现存的包含重复键值的任何记录;LOAD DATA INFILE将处理整个文件,将文件中的所有记录装入表中;
2.从客户机主机装入文件:
1.默认情况下LOAD DATA INFILE将忽略包含重复键的记录;即,默认行为与指定IGNORE选项时相同;
2.这是因为客户机/服务器协议不允许在传输开始后中断从客户机主机到服务器的数据文件传输,因此不方便在操作过程中中止操作;

— 补充:
1.mysqldump:会在备份恢复时候讲;
2.mysqlimport:就是LOAD DATA INFILE的客户端版;
2.mysqlexp:就是SELECT INTO OUTFILE的客户端版;

— 课后练习;

— 表维护的实施;
1.表维护操作对于确定和更正数据库问题十分有用,如以下问题:
– 由于服务器崩溃而导致表损坏;
– 对表的查询处理速度较慢;
2.可使用多种工具执行表维护:
– MySQL Workbench;
– MySQL Enterprise Monitor;
– SQL(DML)维护语句;
– 实用程序:
— mysqlcheck;
— myisamchk;
– 服务器自动恢复;

— 用于表维护操作的SQL;
1.有多个SQL语句可用于执行表维护:
– ANALYZE TABLE:更新索引统计信息;
– CHECK TABLE:彻底检查完整性;
– CHECKSUM TABLE:彻底检查完整性 ;
– REPAIR TABLE:修复;
– OPTIMIZE TABLE:优化;
2.每个语句均包含一个或多个表名称和可选的关键字;
3.维护语句和输出的示例:
• Table:指示对其执行操作的表;
• Op:指出操作(检查,修复,分析或优化);
• Msg_type:指示成功或失败;
• Msg_text:提供其他信息;
mysql> CHECK TABLE City, City1;
+————+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+——-+———-+———-+
| world.City | check | status | OK |
+————+——-+———-+———-+

— ANALYZE TABLE语句;
1.分析并存储表的键分布统计信息:在对多个对象执行联接操作时,MySQL 使用所存储的键分布统计信息来确定优化程序联接表的顺序;
2.用于更好地进行查询执行选择:键分布确定了MySQL用于查询中的特定表的索引;
3.执行ANALYZE TABLE语句来分析并存储统计信息,或者配置InnoDB,以便在大量数据发生更改之后或者在查询表或索引元数据时自动收集统计信息:
1.在分析过程中,对于InnoDB和MyISAM,MySQL使用读取锁来锁定表;
2.此语句等效于使用mysqlcheck –analyze;
3.需要对表有SELECT和INSERT特权;
4.支持分区表,还可以使用ALTER TABLE…ANALYZE PARTITION检查一个或多分区;
5.如果自从运行上一个ANALYZE TABLE语句后表未发生任何更改,则MySQL不会分析该表;
4.默认情况下,MySQL会将ANALYZE TABLE语句写入二进制日志并将这些语句复制到复制从属角色中,禁止使用可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL执行日志记录;
5.控制MySQL收集和存储键分布统计信息方式的选项:
1.innodb_stats_persistent:此选项为ON时,MySQL将对新创建的表启用STATS_PERSISTENT设置;
1.使用CREATE TABLE或ALTER TABLE语句时,还可以对表设置STATS_PERSISTENT;
2.默认情况下,MySQL不会将键分布统计信息持久保留在磁盘上,因此有时必须生成这些信息(如服务器重新启动后);
3.对于启用了STATS_PERSISTENT的表,MySQL会将其键分布统计信息存储在磁盘上,从而不需要频繁地为这些表生成统计信息;
4.随着时间推移,通过此操作优化程序可以创建更一致的查询计划;
2.innodb_stats_persistent_sample_pages:MySQL通过读取STATS_PERSISTENT表的索引页样例(而并非整个表)重新计算统计信息;
1.默认情况下,将读取20页样例;
2.增大此数字可提高所生成的统计信息和查询计划的质量;
3.降低此数字可减少用于生成统计信息的I/O成本;
3.innodb_stats_transient_sample_pages:此选项用于控制对没有STATS_PERSISTENT设置的表的抽样索引页数量;
以下选项用于控制MySQL自动收集统计信息的方式:
4.innodb_stats_auto_recalc:启用此选项时,如果STATS_PERSISTENT表中10%的行自前一次重新计算后有所变化,则MySQL将自动为该表生成统计信息;
5.innodb_stats_on_metadata:启用此选项可在执行元数据语句(如SHOW TABLE STATUS)或查询INFORMATION_SCHEMA.TABLES时更新统计信息;默认情况下,此选项处于禁用状态;
6.ANALYZE TABLE 正常结果的示例;

— CHECK TABLE语句;
1.检查表结构的完整性,并检查内容中是否包含错误;对于MyISAM表,还将更新键统计信息;
2.验证视图定义:例如视图定义中引用的表不再存在;
3.支持分区表:支持分区表。还可以使用ALTER TABLE…CHECK PARTITION检查一个或多个分区;
4.处理InnoDB,CSV,MyISAM和ARCHIVE表:
5.CHECK TABLE选项:
1.FOR UPGRADE:服务器将检查每个表以确定表结构是否与当前的MySQL版本兼容,可能会因为某种数据类型的存储格式或排序顺序发生变化而出现不兼容的情况;如果出现潜在的不兼容情况,则服务器将对表运行全面检查,如果全面检查成功,则服务器会使用当前的MySQL版本号标记表的.frm文件;对.frm文件进行标记可以确保以后对于与服务器版本相同的表进行检查的速度会加快;
2.QUICK:不扫描行来检查错误链接;
6.如果CHECK TABLE发现InnoDB表出现问题:
1.服务器将关闭,以防止错误扩散;
2.MySQL会将错误写入错误日志;
7.如果CHECK TABLE的输出表明某个表出现问题,请修复该表;
1.可以先使用CHECK TABLE语句检测硬件问题(如内存故障或磁盘扇区损坏),然后再修复表;
2.Msg_text输出列通常为OK,如果输出不是OK或Table is already up to date,请对该表运行修复;
3.如果该表被标记为corrupted或not closed properly,但CHECK TABLE在表中未发现任何问题,则会将该表标记为OK;

— CHECKSUM TABLE语句;
1.报告表checksum:
1.CHECKSUM TABLE需要对表有SELECT特权;
2.用于验证表的内容在备份,回滚或其他操作前后是否相同;
3.对于不存在的表,CHECKSUM TABLE将返回NULL并生成警告;
2.逐行读取整个表以计算校验和:
1.默认的EXTENDED选项提供了此行为(将逐行读取整个表,并计算checksum);
2.QUICK选项对MyISAM表可用;
1.将报告实时表checksum(如果可用);否则将报告NULL,此操作非常快;
2.通过在创建表时指定CHECKSUM=1 表选项,对MyISAM表启用了实时checksum;
3.checksum值取决于表中的行格式,如果行格式发生了变化,则checksum也会更改;例如,VARCHAR的存储格式在MySQL 4.1之后的版本中有所变化,因此,在将4.1表升级到更高版本后,如果表中包含 VARCHAR字段,则checksum值将发生变化;
3.CHECKSUM TABLE语句的示例;
TIPS:如果两个表的checksums不同,则很可能这两个表存在某方面的差异;不过,因为CHECKSUM TABLE使用的散列函数无法保证不冲突,所以存在两个不同的表生成相同checksum的微弱可能性;

— OPTIMIZE TABLE语句;
1.通过对表进行碎片整理来清理表
1.通过重新构建表并释放未使用的空间对表进行碎片整理;
2.合并被分隔开的记录和以非连续方式存储的记录;
3.需要对表有SELECT和INSERT特权;
2.在优化过程中锁定表;
3.更新索引统计信息:
1.例如,修改大量行之后,可以使用OPTIMIZE TABLE语句在InnoDB中重构一个FULLTEXT索引;
2.对于InnoDB表,OPTIMIZE TABLE将映射到ALTER TABLE,后者将重构表以更新索引统计信息并释放群集索引中未使用的空间;
3.InnoDB不会像其他存储引擎一样受碎片影响,因此不需要经常使用OPTIMIZE TABLE;
4.最适用于完全填充的永久表:
5.处理InnoDB,MyISAM和ARCHIVE表:
1.对使用ARCHIVE存储引擎的表使用OPTIMIZE TABLE可以压缩该表;
2.由SHOW TABLE STATUS所报告的ARCHIVE表中的行数始终比较准确;
3.优化操作过程中可能会出现一个.ARN文件;
6.支持分区表:还可以使用ALTER TABLE…OPTIMIZE PARTITION检查一个或多个分区;
7.OPTIMIZE TABLE选项:
1.NO_WRITE_TO_BINLOG或LOCAL:禁用二进制日志;
8.已删除的行将保留在链接的列表中,而后续的INSERT操作将重用之前行的位置,OPTIMIZE TABLE对完全填充的表使用时效果最佳并且不会发生很大更改;如果数据更改较多并经常需要优化,则优化的优势将会大大降低;

— REPAIR TABLE语句;
1.修复可能已损坏的MyISAM或ARCHIVE表;
1.不支持InnoDB;
2.支持分区表;
3.REPAIR TABLE选项:
1.QUICK:尝试仅修复索引文件,而不修复数据文件;此类型的修复与myisamchk –recover –quick所执行的修复相似;
2.EXTENDED:MySQL将逐行创建索引,而不是一次性创建有序索引;此类型的修复与myisamchk –safe-recover所执行的修复相似;
3.USE_FRM:使用.FRM文件重新创建.MYI文件;选项不能用于分区表
4.NO_WRITE_TO_BINLOG或LOCAL:禁用二进制日志;
4.TIPS:
1.在执行表修复操作之前,最好对表进行备份;在某些情况下,该操作可能导致数据丢失;可能的原因包括(但不仅限于)文件系统错误;
2.如果服务器在REPAIR TABLE操作过程中崩溃,则为避免进一步的损坏,重启之后应立即执行另一REPAIR TABLE,然后再执行其他任何操作;
3.如果经常需要使用REPAIR TABLE从损坏的表进行恢复,请尝试找出根本原因,以防止相应损坏并避免使用REPAIR TABLE;

— mysqlcheck客户机程序;
1.是用于检查,修复,分析和优化表的命令行客户机;
2.比发出SQL语句更加方便;
1.例如,如果提供数据库名称作为其参数,则mysqlcheck将确定该数据库所包含的表,并发出语句处理所有这些表;不需要提供明确的表名称作为参数;
2.由于mysqlcheck是命令行程序,因此可以在执行计划维护的操作系统作业中轻松使用该程序;
3.处理InnoDB,MyISAM和ARCHIVE表;
4.三种检查级别:
1.特定于表;
2.特定于数据库;
3.所有数据库;
5.部分mysqlcheck维护选项:
1.–analyze:执行ANALYZE;
2.–check:执行CHECK TABLE(默认);
3.–optimize:执行OPTIMIZE TABLE;
4.–repair:执行REPAIR TABLE;
6.Oracle建议首先在不使用任何选项的情况下运行mysqlcheck,如果需要修复再重新运行;
7.部分mysqlcheck修改选项:
1.–repair –quick:尝试快速修复;
2.–repair:正常修复(如果快速修复失败);
3.–repair –force:强制修复;
8.mysqlcheck示例:
shell> mysqlcheck –login-path=admin world_innodb
shell> mysqlcheck -uroot -p mysql user –repair
shell> mysqlcheck -uroot -p –all-databases
shell> mysqlcheck –login-path=admin –analyze –all-databases
9.默认情况下,mysqlcheck将其第一个非选项参数解释为数据库名称,并检查该数据库中的所有表;如果数据库名称后面有其他任何参数,则会将这些参数视为表名称,从而只检查这些表;
本幻灯片中显示的 mysqlcheck 示例演示了以下内容:

— myisamchk实用程序;
1.是用于检查MyISAM表的非客户机实用程序;
2.myisamchk与mysqlcheck具有相似的用途,其差异包括:
1.可以启用或禁用索引;
2.不与MySQL服务器通信,直接(而不是通过服务器)访问表文件;
3.在使用myisamchk执行表维护的同时避免并发表访问:
1.确保服务器不会访问正在进行处理的表,一种实现方法是锁定表或停止服务器;
2.在命令提示符中,将位置更改为表所在的数据库目录;这是服务器数据目录的子目录,该目录的名称与要检查的表所在的数据库名称相同;(更改位置是为了更加便于引用表文件,可以跳过此步骤,但myisamchk必须包含表所在的目录;)
3.调用myisamchk,使用选项指示要执行的操作,后跟参数以指定myisamchk应对其执行操作的表;这些参数可以是表名称,也可以是表的索引文件的文件名(索引文件名与表名称相同,包含.MYI后缀);因此,可以通过table_name或table_name.MYI引用表;
4.重新启动服务器:请首先尝试–recover,因为–safe-recover比较慢;
3.部分myisamchk 选项:
1.–recover:修复表;
2.–safe-recover:修复–recover无法修复的表;
4.myisamchk示例:
shell> myisamchk /var/lib/mysql/mysql/help_topic
shell> myisamchk help_category.MYI
shell> myisamchk –recover help_keyword

— mysqlcheck和myisamchk的选项;
1.mysqlcheck和myisamchk均使用多个选项来控制所执行的表维护操作的类型;
2.两者都适用:
• –analyze:分析表中键值的分布,通过加快基于索引的查找,这可以提高查询的性能;
• –check 或 -c:检查表中是否存在问题,如果未指定其他任何操作,则为默认操作;
• –check-only-changed 或 -C:跳过表检查(自上一次检查后已更改的表或未正常关闭的表除外),如果服务器在表打开时崩溃,则会出现后一种情况;
• –fast 或 -F:跳过表检查(未正常关闭的表除外);
• –medium-check 或 -m:运行中等表检查;
• –quick 或 -q:对于mysqlcheck,不包含修复选项的–quick会导致只检查索引文件,而不检查数据文件;对于这两个程序,将–quick与修复选项结合使用都会导致程序只修复索引文件,而不修复数据文件;
3.mysqlcheck & myisamchk:
• –auto-repair:如果检查操作发现了问题,则自动修复出现问题的表;
• –repair、–recover 或 -r:运行表修复操作;
• –extended、–extend-check 或 -e:运行扩展表检查,对于mysqlcheck,将此选项与修复选项结合使用时,将执行比单独使用修复选项时更彻底的修复;即,–repair –extended执行的修复操作比–repair执行的操作更彻底;

— InnoDB表维护;
1.出现故障之后,InnoDB将自动恢复;
2.使用CHECK TABLE或客户机程序可找出不一致,不兼容和其他问题;
3.如果表检查表明存在问题,可以通过使用mysqldump对表进行转储来恢复该表:
1.备份表内容:shell> mysqldump > ;
2.然后,删除该表并从转储文件重新创建:shell> mysql < ;
4.如果MySQL服务器或其运行主机崩溃,则某些InnoDB表可能处于不一致状态,在InnoDB的启动序列中,会执行自动恢复;服务器很少因为自动恢复故障而无法启动,如果出现此情况,则可以:
1.重新启动服务器,将–innodb_force_recovery选项的值设置为1到6之间的值;这些值表示增加警告级别以避免崩溃,以及针对已恢复的表中可能存在的不一致状况增加容错级别,最好从值4开始,该值设置为只读模式;
1.1(SRV_FORCE_IGNORE_CORRUPT):即便检测到坏页也让服务正常运行,可以尝试使用SELECT * FROM tbl_name来跳过损坏的索引记录和页,对于导出数据比较有帮助;
2.2(SRV_FORCE_NO_BACKGROUND):阻止master线程和任何的purge线程运行;
3.3(SRV_FORCE_NO_TRX_UNDO):实例恢复后不运行事务回滚;
4.4(SRV_FORCE_NO_IBUF_MERGE):阻止插入缓存合并操作;不会统计表的统计信息;这个值会永久性的破坏数据文件,在使用这个值之后需要导出数据,删掉对象然后再重新创建对象;在MySQL 5.6.15之后,会设置InnoDB为只读模式;
5.5(SRV_FORCE_NO_UNDO_LOG_SCAN):在数据库启动时不会去查看UNDO日志,InnoDB会把未提交事务处理为已提交;这个值会永久性的破坏数据文件,在MySQL 5.6.15之后,会设置InnoDB为只读模式;
6.6(SRV_FORCE_NO_LOG_REDO):在实例恢复时不会前滚REDO日志;这个值会永久性的破坏数据文件,使数据库的页为过期状态;在MySQL 5.6.15之后,会设置InnoDB为只读模式;
2.当在–innodb_force_recovery设置为非零值的情况下启动服务器时,InnoDB将阻止INSERT,UPDATE或DELETE操作;因此,您应转储InnoDB表,然后在该选项生效时将这些表删除,再在不使用–innodb_force_recovery选项的情况下重新启动服务器;服务器启动之后,将从转储文件恢复InnoDB表;
3.如果前述步骤失败,则从前一个备份恢复表;
5.使用ALTER TABLE进行优化时,将重构表并释放群集索引中未使用的空间;

— MyISAM表维护;
1.使用CHECK TABLE…MEDIUM(默认选项):
1.对于动态格式表和静态格式表,默认的CHECK TABLE检查类型均为MEDIUM;如果将静态格式表类型设置为CHANGED或FAST,则默认选项为QUICK;对于CHANGED和FAST,将跳过行扫描,因为这些行很少损坏;
1.如果表被标记为“已损坏”或“未正常关闭”,则CHECK TABLE将更改表;
2.如果未在表中发现任何问题,则会将表的状态标记为“最新”;
3.如果表已损坏,则问题最有可能存在于索引而不是数据中;
2.使用客户机程序运行myisamchk:shell> myisamchk –medium-check ;
3.表损坏通常发生在索引中,因而执行这些检查很有帮助;
4.如果表已损坏,则实用程序将修复该表;
2.设置服务器以运行检查并自动修复表:
1.使用–myisam-recover选项启用自动修复;选项值可以包含以逗号分隔的值列表,由以下一个或多个值组成:
1.DEFAULT:默认检查;
2.BACKUP:指示服务器对必须进行更改的所有表进行备份;
3.FORCE:执行表恢复,即使可能导致多行数据丢失也是如此;
4.QUICK:执行快速恢复;恢复将跳过一些不包含因删除或更新而产生的行间隔(也称为“洞”)的表;
2.服务器将在启动之后第一次访问每个MyISAM表时进行检查,以确保这些表前一次正确关闭;
3.强制从config文件恢复MyISAM表;
eg:要指示服务器对发现问题的MyISAM表执行强制恢复,但同时要备份其更改的所有表,请向选项文件中添加以下内容:
[mysqld] myisam-recover=FORCE,BACKUP;

补充:
MySQL在创建表的时候定义表的性质,共有三种状态:静态表,动态表,压缩表;
1.默认是静态表;
2.动态表:如果存在varchar,blob,text字段,表类型就是动态表;
3.压缩表:只读,使用很少的空间,使用myisampack工具创建;

— MEMORY表维护;
1.使用DELETE…WHERE语句删除多个行时,MEMORY表不会释放内存;
2.要释放内存,必须执行空值ALTER TABLE操作;

— ARCHIVE表维护;
1.ARCHIVE压缩问题:
1.插入表行时将对其进行压缩;
2.检索时,将根据需要对行进行解压缩;
3.一些SELECT语句可能会减弱压缩功能;
2.使用OPTIMIZE TABLE或REPAIR TABLE可以实现更好的压缩;
3.在未对表进行访问(读或写)时,OPTIMIZE TABLE有效;

— 课后练习;

— 安全风险;
• 当多个用户同时访问MySQL服务器,尤其当这些用户通过Internet进行连接时,MySQL服务器将有安全风险;
• 不仅MySQL服务器处于危险中,整个服务器主机也可能受到损害;
• 有多种类型的安全攻击:
– 窃听
– 更改
– 播放
– 拒绝服务
• 对于所有连接,查询和其他操作,MySQL使用基于ACL(访问控制列表,access control list)的安全;
• ACL也支持MySQL客户机和服务器之间的SSL加密连接;

— MySQL安装安全风险;
最常见的安装安全风险为:
1.网络安全
1.MySQL服务器允许客户机通过网络进行连接并发出请求;
2.如果未限制特权,则用户可以查看客户机帐户信息:为每个帐户设置的特权应该仅提供对该帐户需要查看或修改的数据的访问权限;
3.任何没有口令的帐户都很容易受到攻击;
2.操作系统安全:通常使用专用的登录帐户管理MySQL;但是,该帐户所在的主机上可能还有其他登录帐户,将与MySQL不相关的帐户数量减至最少可以尽可能地降低此风险;
3.文件系统安全:服务器上的目录,数据库文件和日志文件可能会被不应具有访问权限的用户打开;

— 网络安全;
风险预防任务:
1.投资设置防火墙;
2.确保仅供授权的客户机进行访问;
3.限制服务器所使用的网络接口;
4.使用安全安装脚本:mysql_secure_installation;
5.遵守常规特权安全事项;
6.不通过Internet传输明文(未加密)数据:未加密的数据可被任何有时间和能力拦截该数据并使用它来达到自己目的的用户访问,因此,您应该使用SSL或SSH等加密协议;

— 口令安全;
风险预防任务:
1.使用强口令保护初始MySQL帐户;
2.不在数据库中存储任何纯文本口令:
1.mysql数据库将口令存储在user表中;
2.最好使用单向散列存储这些口令:MD5(),SHA1(),SHA2()。
3.不从字典中选择口令:有一些特殊程序可破译口令,应对这些程序的方法之一是使用由句子中每个单词的第一个字符组成的口令(例如,“Mary had a little lamb”可以生成口令“Mhall”);这种口令易于记忆和输入,但不知道句子的人很难猜到;

— 操作系统安全;
1.与配置的复杂程度有关:
1.将服务器主机上与MySQL不相关的任务数减至最少;
2.当为主机配置的任务较少时,与运行复杂配置以支持多个服务的主机相比,前一个主机更易于确保安全;
2.其他使用会导致潜在风险:所分配的MySQL服务器最好是主要用于MySQL或专供其用;
3.登录帐户对于专用于MySQL的计算机不是必需的;
4.完全专用于MySQL的系统可以在性能方面获益;

— 文件系统安全;
MySQL安装(目录和文件)风险预防任务:
1.更改所有权和访问权限后,再启动服务器:
1.将多用户系统的所有权设置为具有管理特权的帐户。
2.将与MySQL相关的目录和文件以及user和group表所有权设置为mysql,其中包括:
1.MySQL程序;
2.数据库目录和文件;
3.日志,状态和配置文件;
2.不要在保护文件之前设置口令,否则,将允许未经授权的用户替换文件;
3.设置一个专用于MySQL管理的帐户:
1.对于Linux之类的多用户系统,请将MySQL安装的所有组件的所有权设置为具有正确管理特权的专用登录帐户,这将保护安装免受不负责数据库管理的用户的访问;
2.设置此帐户的另外一个好处是,可以使用该帐户运行MySQL服务器,而不是从Linux root帐户运行服务器;具有root登录帐户特权的服务器拥有不必要的文件系统访问权限,从而成为一个安全风险;

— 保护数据;
用户可通过多种方法来损坏数据,必须采取措施以保护数据免受如SQL注入等攻击:
1.请勿信任应用程序用户输入的任何数据:
1.用户可以使用具有特殊意义的字符(如引号或转义序列)获取应用程序代码;
2.如果用户输入类似于DROP DATABASE mysql;的内容,请确保应用程序保持安全;
3.如果用户输入值234时,应用程序生成一个类似于SELECT * FROM table WHERE ID=234的查询,则用户可以输入值234 OR 1=1使应用程序生成查询SELECT * FROM table WHERE ID=234 OR 1=1;使用不会将值解释为SQL表达式的存储例程或预处理语句;
2.保护数值和字符串数据的值:
1.在数值字段中输入字符,空格和特殊符号而不是数字;应用程序应删除这些内容再将其传递给MySQL;
2.否则,用户可以获取对安全数据的访问权限,然后提交可以销毁数据或导致服务器负载过高的查询;
3.甚至需要保护公开可用的数据:
1.攻击会浪费服务器资源;
2.保护Web表单(在客户端校验),URL名称,特殊字符等;

— 使用安全连接;
1.默认情况下,MySQL使用未加密的客户机/服务器连接;
2.未加密连接无法满足在网络上安全传输数据的要求:
1.网络通信容易受到监视和攻击;
2.在客户机和服务器之间传输的数据可能会被更改;
3.使用加密算法可抵御大多数威胁:
1.这些算法会使所有类型的数据无法读取;
2.这可抵御多种类型的攻击;
4.某些应用程序需要加密连接所提供的安全性,对于这些连接,必须进行额外的计算(数据加密是CPU密集型操作,需要计算机执行额外工作);

— SSL协议;
1.MySQL支持MySQL客户机和服务器之间的SSL(secure sockets layer,安全套接字层)连接:MySQL可以针对单个连接启用加密,根据各个应用程序的需要,您可以选择常规的未加密连接或安全的加密SSL连接;
2.SSL连接协议具有以下特点:
1.使用不同的加密算法确保公共网络上数据的安全;
2.检测任何数据更改,丢失或重放;
3.结合了使用X509标准提供身份验证的算法;
3.需要额外安全保护(加密)的应用程序应使用SSL。
4.安全连接基于OpenSSL API。

补充:
X509使得在Internet上进行身份识别成为可能,这主要用于电子商务应用程序中;基本上,应该有一个受信任的证书颁发机构(Certificate Authority, CA),该机构将电子证书分配给任何需要它们的人;证书依赖于包含两个加密密钥(公钥和私钥)的非对称加密算法;

— 对MySQL服务器使用SSL;
1.SSL使用要求:
1.系统必须支持yaSSL(已随MySQL提供)或OpenSSL(http://www.openssl.org);
2.使用的MySQL版本必须包含SSL支持;
2.要获取安全连接以便使用MySQL和SSL,必须首先执行以下操作:
1.装入OpenSSL(如果使用的不是预编译的MySQL);
2.为MySQL配置SSL支持,要将MySQL源代码分发配置为使用SSL,请调用CMake: shell> cmake . -DWITH_SSL=bundled;
1.该操作会将分发配置为使用附带的yaSSL库;
2.要改用系统SSL库,请将相应选项指定为-DWITH_SSL=system;
3.确保MySQL数据库中的user表包含与SSL相关的列(ssl_*和x509_*),否则必须使用mysql_upgrade程序对其进行升级;
4.使用–ssl选项检查服务器二进制文件是否使用SSL支持进行编译;
shell> mysqld –ssl –help
060525 14:18:52 [ERROR] mysqld: unknown option ‘–ssl’
3.使用允许客户机使用SSL的选项启动服务器;

— 使用SSL启动MySQL服务器;
通过以下选项,使mysqld服务器客户机可以使用SSL进行连接:
1.–ssl-ca:确定要使用的证书颁发机构(Certificate Authority, CA)证书(加密必需);
2.–ssl-key:确定服务器公钥;
3.–ssl-cert:确定服务器私钥;
eg:shell> mysqld –ssl-ca=ca-cert.pem –ssl-cert=server-cert.pem –ssl-key=server-key.pem

— 要求SSL加密连接;
1.使用GRANT语句的REQUIRE SSL选项可仅允许某个帐户使用SSL加密连接:
GRANT ALL PRIVILEGES ON test.* TO ‘root’@’localhost’ IDENTIFIED BY ‘goodsecret’ REQUIRE SSL;
2.通过使用–ssl-ca选项启动mysql客户机可获得加密连接;
3.(可选)可以为X509连接指定–ssl-key和–ssl-cert选项:
shell> mysql –ssl-ca=ca-cert.pem –ssl-cert=server-cert.pem –ssl-key=server-key.pem

补充:
有很多方法可限制给定帐户的连接类型:
1.REQUIRE NONE:指示帐户没有SSL或X509要求;如果未指定与SSL相关的REQUIRE选项,则这是默认选项;如果用户名和口令有效,则允许未加密的连接;但是,如果客户机具有相应的证书和密钥文件,则客户机也可以通过指定一个选项来要求加密连接,即,客户机无需指定任何SSL命令选项,在这种情况下,连接是未加密的;
2.REQUIRE SSL:指示服务器仅允许帐户使用SSL加密连接;
3.REQUIRE X509:客户机必须具有有效的证书,但具体的证书,颁发者和主题无关紧要;唯一的要求是,应该可以使用其中一个CA证书验证其签名;

— 检查SSL状态;
1.检查正在运行的mysqld服务器是否支持SSL:mysql> SHOW VARIABLES LIKE ‘have_ssl’;
1.如果have_ssl的值为YES,则服务器支持SSL连接;
2.如果值为DISABLED,则服务器支持SSL连接,但在启动时未提供相应的–ssl-*选项;
2.使用ssl_cipher状态变量的值检查当前服务器连接是否使用了SSL:mysql> SHOW STATUS LIKE ‘ssl_cipher’;

例子:
1.Create clean environment
shell> /tmp
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts
2.Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
3.Create server certificate, remove passphrase, and sign it server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
4.Create client certificate, remove passphrase, and sign it client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
5.After generating the certificates, verify them:
shell> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
6.修改my.cnf并且重新启动数据库服务器;
[mysqld]
ssl-ca=/tmp/newcerts/ca-cert.pem
ssl-cert=/tmp/newcerts/server-cert.pem
ssl-key=/tmp/newcerts/server-key.pem
7.检查是否支持SSL:mysql> SHOW VARIABLES LIKE ‘have_ssl’;
8.创建用户:GRANT ALL PRIVILEGES ON *.* TO ‘ssl_user’@’localhost’ IDENTIFIED BY ‘mysql’ REQUIRE SSL;
9.客户端连接:
shell> mysql -ussl_user -pmysql
ERROR 1045 (28000): Access denied for user ‘ssl_user’@’localhost’ (using password: YES)
shell> mysql -ussl_user -pmysql –ssl-ca=/tmp/newcerts/ca-cert.pem

— 使用SSL的优点和缺点;
1.优点:
1.提高了有需求的应用程序的安全性;
2.可以针对单个连接启用;
3.可用于复制操作;
2.缺点:
1.占用大量CPU资源;
2.降低了客户机/服务器协议的速度;
3.可能会推迟其他SQL任务;

— 与MySQL的安全远程连接;
1.MySQL支持与远程MySQL服务器的SSH(secure shell,安全shell)连接,这一功能要求:
– 客户机上存在 SSH 客户机;
– 通过 SSH 隧道进行从客户机到服务器的端口转发;
– 具有 SSH 客户机的计算机上存在客户机应用程序;
2.设置完成后,将有一个本地端口托管到 MySQL 的 SSH 连接并使用 SSH 进行加密;

— MySQL安全常见问题;
file:///Users/royalwzy/Applications/refman-5.6-en.html-chapter/faqs.html#faqs-security

— 课后练习;

— 用户管理的重要性;
在MySQL中管理用户时,可以控制允许或不允许用户执行的操作;
• 创建具有与用户的职能相应的不同特权的用户帐户;
• 避免使用root帐户:
– 限制受损害的应用程序;
– 防止在例行维护期间出错;
• 通过恰当调整单个用户特权确保数据完整性:
– 允许授权用户完成其工作;
– 阻止未经授权用户访问超出其特权的数据;

— 用户帐户验证;
在连接到MySQL服务器并执行查询时,会验证您的身份并为您的活动授权;
• 验证:验证用户的身份;这是访问控制的第一个阶段,每次连接时都必须成功验证身份,如果验证失败,则无法连接,您的客户机将断开连接;
• 授权:验证用户的特权;这是访问控制的第二个阶段,面向针对成功验证了身份的活动连接的每个请求;对于每个请求,MySQL将确定您要执行的操作,然后检查您是否有执行此操作所需的恰当特权;

— 查看用户帐户设置;
mysql数据库包含服务器上所有用户帐户的信息:
1.查询mysql数据库以查看user标识信息:mysql> SELECT user, host, password FROM mysql.user WHERE user=’root’;
2.查看包括特权在内的所有user信息:SELECT * FROM mysql.user\G
1.*_priv字段中的值Y表示已启用相应特权;
2.root帐户具有完全访问权限,该帐户的所有特权列的值均为Y;
3.除了特权以外,该用户表还包含在验证过程中有用的其他信息;
4.Password列中以加密格式显示密码;password_expired:表示口令是否过期;
5.max_*:资源限制,0表示无限制;
6.ssl_*和x509_*:SSL或x509设置;
7.plugin:列出了密码插件名称(eg:mysql_native_password插件验证身份);

— 本机验证;
• 在使用mysql_native_password插件连接过程中,MySQL将使用以下各项与mysql.user表中的行进行匹配验证帐户:
– 用户名
– 口令
– 客户机主机
• 在指定主机名时,要注意恰当的方面:
– 在使用客户机连接时指定服务器的主机名;
– 在向服务器添加用户时指定客户机的主机名;
补充:
要使用mysql客户机连接到本地服务器:shell> mysql -u -p 要使用mysql客户机连接到远程服务器:shell> mysql -u -p -h

— 创建用户帐户;
1.提供每个用户帐户的用户和主机;
2.使用CREATE USER…IDENTIFIED BY语句可以:
– 为用户 jim 建立一个帐户;
– 该帐户从 localhost 发出连接;
– 该帐户使用口令 Abc123:
CREATE USER ‘jim’@’localhost’ IDENTIFIED BY ‘Abc123’;
3.在创建帐户时避免可能的安全风险:
– 不创建没有口令的帐户;
– 不创建匿名帐户;
– 在可能的情况下,避免在指定帐户主机名时使用通配符;
4.创建一个匿名账户,不需要用户名和密码即可登录,比较危险.eg:insert into user(host, user, password) values(‘%’, ”, ”);
TIPS:
1.帐户名称的格式为’user_name’@’host_name’;
2.用户名长度最多可以有16个字符,如果用户名和主机名包含特殊字符(如短划线),则必须将它们放在单引号中;如果某个值在不带引号时也有效,则引号是可选的,但是,任何情况下都可使用引号;

— 主机名模式;
1.允许的主机名格式示例:
• 主机名:localhost;
• 合格的主机名:’hostname.example.com’;
• IP编号:192.168.9.78;
• IP地址:10.0.0.0/255.255.255.0;
• 模式或通配符:%或_;
2.使用包含%或_通配符字符的主机模式设置的帐户允许用户从整个域或子网中的任何主机连接;
3.省略帐户名称的主机部分,MySQL将假定主机名为%;
1.主机值%.example.com可匹配example.com域中的任何主机;
2.主机值 192.168.%可匹配192.168子网中的任何主机;
3.主机值%可匹配任何主机,允许用户从任何主机连接;
4.用户名和主机名示例:
• john@10.20.30.40;
• john@’10.20.30.%’;
• john@’%.ourdomain.com’;
• john@’10.20.30.0/255.255.255.0′;
5.指定匿名用户帐户(即匹配任何用户名的帐户):mysql> CREATE USER ”@’localhost’;要避免创建匿名帐户,尤其是没有口令的匿名帐户,这有助于避免由于开放对MySQL安装的访问权限带来的安全风险;
6.如果某个主机匹配两个或更多模式,则MySQL将选择最具体的模式;

— 设置帐户口令;
1.设置MySQL用户口令的方法有多种:
– CREATE USER…IDENTIFIED BY:自动为提供的口令加密;
– GRANT…IDENTIFIED BY
– SET PASSWORD
– mysqladmin password:shell> mysqladmin -u root password ‘rootpass’;
– UPDATE 授权表(不推荐)
2.SET PASSWORD语句是设置或更改帐户口令的最常用方法:
1.更改现有帐户的口令但不更改其任何特权的最常用方法;
2.语法:SET PASSWORD FOR ‘jim’@’localhost’ = PASSWORD(‘NewPass’);
3.使用SET PASSWORD语句但不带FOR子句仅能更改您自己的口令;

— 确认口令;
为所有用户帐户分配唯一的强口令:
• 避免可以轻易猜测到的口令;
• 使用以下SELECT语句可列出没有口令的所有帐户:SELECT Host, User FROM mysql.user WHERE Password = ”;
• 确定重复口令:SELECT User FROM mysql.user GROUP BY password HAVING count(user)>1;
• 让口令失效:ALTER USER jim@localhost PASSWORD EXPIRE;如果密码失效,则在下次登录时必须使用SET PASSWORD语句更改口令;所有不以SET开头的执行语句将返回错误,直到更改口令为止;

— 处理用户帐户;
• 使用RENAME USER语句可重命名用户帐户:RENAME USER ‘jim’@’localhost’ TO ‘james’@’localhost’;
– 更改现有帐户的帐户名称
– 更改帐户名称的用户名或主机名部分,或同时更改两者;
• 使用DROP USER语句可删除用户帐户:DROP USER ‘jim’@’localhost’:
– 撤消现有帐户的所有特权,然后删除该帐户;
– 从存在帐户的任何授权表中删除帐户的所有记录;
• 在用户访问要求变化时,重命名或删除用户;

— 可插入验证;
MySQL支持多种通过可插入验证提供的验证机制:
1.插件是内置的,也可作为外部库提供;
2.默认的服务器端插件是内置的,始终可用,其中包括:
– mysql_native_password:这是默认机制,插件实施标准口令格式:41字节宽的散列;
– mysql_old_password:此插件的实现验证的方式与MySQL4.1.1之前的版本相同;安全性较低,16字节宽;
– sha256_password:此插件可启用口令的SHA-256散列;
3.old_passwords系统变量的值指定PASSWORD()函数用于创建口令的算法,如下所示:
• 0:标准算法,与MySQL 4.1.1及更高版本中所用的算法相同;
• 1:旧算法,与MySQL 4.1.1之前版本中所用的算法相同;
• 2:SHA-256算法;
4.启动服务器时将default-authentication-plugin选项设置为sha256_password,可将SHA-256口令用于所有新用户,也可使用CREATE USER和IDENTIFIED WITH sha256_password子句为特定用户指定SHA-256口令;

— 客户端明文验证插件;
MySQL客户机库中有一个内置的明文验证插件mysql_clear_password,该插件:
1.用于将纯文本口令发送给服务器:口令通常经过散列处理;
2.通过以下方式启用:
– LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN:环境变量;
– 在运行MySQL客户机应用程序(如mysql和mysqladmin)时指定–enable-cleartext-plugin;
– mysql_options() C API函数的MYSQL_ENABLE_CLEARTEXT_PLUGIN选项;
3.有些验证方法(如PAM(Pluggable Authentication Module,可插入验证模块)验证)要求客户机向服务器发送纯文本口令,以便服务器处理普通形式的口令,mysql_clear_password插件支持此行为;

— 可装入验证插件;
1.测试验证插件(test_plugin_server):使用本机或旧口令验证进行身份验证,适用于测试和开发;此插件使用auth_test_plugin.so文件;
2.套接字对等凭证(auth_socket):仅允许通过UNIX套接字从具有相同名称的UNIX帐户登录的MySQL用户;此插件使用auth_socket.so文件;
3.PAM验证插件(authentication_pam):是一个企业版插件,允许使用外部验证机制登录,MySQL不存储您的口令,但使用PAM机制传输客户机提供的用户名和口令供操作系统进行验证;此插件使用authentication_pam.so文件;
4.要装入其中的某个插件,可在服务器启动时在命令行中或在my.cnf文件中使用plugin-load选项,并将该选项设置为插件的文件名;
[mysqld]
plugin-load=authentication_pam.so
5.可以开发自己的验证插件,测试验证插件适用于开发者创建自己的插件;

— PAM验证插件;
1.PAM验证插件是一个企业版插件,可向操作系统验证MySQL帐户;
2.PAM定义配置验证的服务:这些服务存储在/etc/pam.d中;
3.该插件针对以下各项进行验证:
– 操作系统用户和组;
– 外部验证(如LDAP,Active Directory),因此可以使用PAM向网络中的单个存储验证许多服务(包括MySQL);
4.要创建使用PAM验证的MySQL用户,请执行以下操作:CREATE USER user@host IDENTIFIED WITH authentication_pam AS ‘pam_service, os_group=mysql_user’;

eg:
1.加载PAM验证插件,在/etc/my.cnf中添加配置,然后重启MySQL服务;
[mysqld]
plugin-load=authentication_pam.so
2.创建PAM服务mysql-pam,可在创建文件/etc/pam.d/mysql-pam:
#%PAM-1.0
auth include password-auth
account include password-auth
3.创建操作系统账户:
shell> useradd bob
shell> passwd bob
4.要创建直接映射到某个操作系统用户的MySQL用户,可使用如下语句:CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS ‘mysql-pam’;
5.当bob登录时,MySQL会将从客户机接收的用户名和口令传递到PAM,后者向操作系统验证;客户机必须以明文形式发送口令,启用客户端明文验证插件以实现此目的:
shell> mysql –enable-cleartext-plugin -ubob -p
Enter password: bob’s_OS_password

1.要使用PAM验证插件启用基于组的登录,可创建一个启用PAM的匿名代理帐户,该帐户不与任何用户匹配,但指定一组从操作系统组到MySQL用户的映射:CREATE USER ”@” IDENTIFIED WITH authentication_pam AS ‘mysql-pam, sales=m_sales, finance=m_finance’;
2.上例假定您拥有sales和finance操作系统组以及m_sales和m_finance MySQL用户;然后,必须向该匿名代理帐户授予 PROXY特权,使其能以m_sales和m_finance MySQL用户身份登录:GRANT PROXY ON m_sales@localhost TO ”@”; GRANT PROXY ON m_finance@localhost TO ”@”;
3.现在,作为sales和finance组成员的用户可以在mysql命令行提示符处提供其操作系统凭证,以m_sales或 m_finance MySQL用户身份登录,从而拥有授予这些帐户的所有特权;例如,如果peter是sales组的成员,则可通过以下方式登录:
shell> mysql –enable-cleartext-plugin -upeter -p
Enter password: peter’s_OS_password
Welcome to the MySQL monitor. Commands end with ; or \g. …
mysql> SELECT CURRENT_USER();
+——————-+
| CURRENT_USER() |
+——————-+
| m_sales@localhost |
+——————-+
1 row in set (0.01 sec)

— 授权;
1.MySQL授权系统的主要功能是将经过验证的用户与数据库特权(如SELECT,INSERT,UPDATE和DELETE)关联起来;
2.授权系统的功能包括:可以拥有匿名用户,可以启用特定功能(如LOAD DATA INFILE和各种管理操作);
3.授权可确保用户只能执行已向其授予了相应特权的操作;
4.在用户验证之后,MySQL将提出以下问题以验证帐户特权:
– 用户是谁?
– 用户拥有哪些特权?
– 这些特权适用于哪些方面?
5.必须设置恰当的帐户和特权才能让授权生效;

— 确定相应的用户特权;
1.可以在不同级别向MySQL帐户授予多种类型的特权:全局级别,数据库级别,表级别,列级别,存储过程级别;
2.根据用户的访问要求授予用户特权:
1.只读用户:全局,数据库或表级别特权,例如SELECT;
2.修改数据库的用户:全局,数据库或表级别特权,例如INSERT/UPDATE/DELETE/CREATE/ALTER/DROP;
3.管理用户:全局级别特权,例如FILE/PROCESS/SHUTDOWN/SUPER;

— 授予管理特权;
1.FILE:允许用户指示MySQL服务器在服务器主机文件系统中读取和写入文件;
2.PROCESS:允许用户使用SHOW PROCESSLIST语句查看客户机正在执行的所有语句;
3.SUPER:管理特权允许用户执行额外任务,其中包括设置全局变量和终止客户机连接;
4.ALL/ALL PRIVILEGES:授予所有特权(但不能向其他用户授予特权)要尽可能少地授予管理特权,因为管理特权可能会被恶意用户或粗心用户滥用;
5.USAGE:允许连接到服务器,但没有任何特权;可以使用帐户访问服务器用于有限的目的,例如发出SHOW VARIABLES或SHOW STATUS语句;

补充:MySQL可以支持的权限类型:
1.管理权限:
CREATE TEMPORARY TABLES:创建临时表;
CREATE USER:创建/删除/重命名账户;
FILE:在sql语句中读写操作系统的文件;
LOCK TABLES:锁表;
PROCESS:查看进程的活动状态;
RELOAD:重新加载,FLUSH OR RESET;
REPLICATION CLIENT:作为复制功能的主机;
REPLICATION SLAVE:作为复制功能的备机;
SHOW DATABASES:查看数据库名称;
SHUTDOWN:关闭服务器;
SUPER:各种管理操作;
2.数据访问权限:
ALTER:修改表结构,alter table;
ALTER ROUTINE:修改或者删除存储过程和函数;
CREATE:创建数据库或者表;
CREATE ROUTINE:创建存储过程或者函数;
CREATE VIEW:创建视图;
DELETE:删除表中数据;
DROP:删除数据库或者表;
EXECUTE:执行函数或者过程;
GRANT OPTION:授权给其它账户;
INDEX:创建/删除索引;
INSERT:向表中插入记录;
SELECT:查询表的记录;
SHOW VIEW:查看视图定义,show create view;
UPDATE:更新表的记录:
3.特殊的权限:
1.ALL and ALL PRIVILEGES:即’all privileges except grant option’,授予账户除了把权限赋予其它帐号外的所有权限;
2.USAGE:即没有任何权限,但是它可以连接到数据库上,也在user表中生成一条记录.这类账户的存在就是为了执行向’show variables’ or ‘show status’这类的语句,也不能查看表的内容;
4.权限的级别
1.所有的权限都可以被授予全局的,一个账户拥有全局权限,它可以在任何数据库任何时间使用此权限,一般只授予管理员这样的权限;
2.一些权限可以被授予特定的数据库:ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, GRANT OPTION, IDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW and UPDATE.一个数据库级别的权限可以应用于所有的tables和routines;
3.一些权限可以被授予特定的表:ALERT, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT and UPDATE.一个标记别的权限可以应用于表中所有的列;
4.一些权限可以被授予表中的列:INSERT, SELECT and UPDATE;
5.一些权限可以被授予函数和存储过程:EXECUTE, ALTER ROUTINE and GRANT OPTION;

— GRANT语句;
1.GRANT语句可创建新帐户或者修改现有帐户;
2.GRANT语法:GRANT SELECT ON world_innodb.* TO ‘kari’@’localhost’ IDENTIFIED BY ‘Abc123’;
3.该语句的子句:
1.GRANT关键字:指定一个或多个指示要授予的特权的特权名称;特权名称不区分大小写,要列出多个特权,可用逗号分隔特权;;
2.ON子句:指定要授予的特权的级别:
1.全局:*.*;
2.数据库:.*;
3.表:.;
4.存储例程:.;
3.TO子句:指定要向其授予特权的帐户;如果该帐户尚不存在,则该语句将创建此帐户;;
4.IDENTIFIED BY子句:(可选)将指定口令分配给帐户;如果帐户已存在,则该口令将替换旧口令,省略IDENTIFIED BY子句会有以下效果:
1.如果TO子句中的帐户已存在,则其口令保持不变;
2.如果TO子句中的帐户不存在,则将创建该帐户,并使用空白口令;
4.作为一项安全措施,启用NO_AUTO_CREATE_USER SQL模式可防止GRANT语句在未指定IDENTIFIED BY子句时创建新帐户;

eg:
1.授权语句:grant privileges on objects to ‘user’@’host’ identified by ‘password’;
1.on *.*:全局权限,所有的对象,eg:grant all privileges on *.* to ‘u1’@’%’ identified by ‘u1’ with grant option;
2.on db_name.*:对数据库的权限,eg:grant select, update on test.* to ‘u1’@’localhost’;
3.on db_name.tbl_name:数据库中表的权限,eg:grant select on mysql.user to ‘u1’@’localhost’;
4.on db_name.tbl_name.col_name:数据库中表的列的权限,eg:grant select(host, user) on mysql.user to ‘u1’@’%’;
5.on db_name.routine_name:数据库中routine的权限;
2.要添加用户并授权一般使用:grant 权限1,权限2,… on 数据库.表 to “username”@”host” identified by “password”;(创建用户的同时授予权限)
1.grant all privileges on *.* to “user1″@”%” identified by “pwd”:表示对user1授予所有数据库中所有表的所有权限,并可以从任意客户端连接进来;
2.grant select,insert,delete,update on mysql.user to “user1″@”192.168.0.1” identified by “pwd”:表示对用户user1授予mysql数据库的user表增删改查权限,并且只能从192.168.0.1客户端连接进来;
3.host列为%表示可以从任意的客户端连接到服务器端;
5.一般在开发的时候使host为%,在生产环境修改host的地址;

— 显示GRANT特权;
1.使用SHOW GRANTS语句显示常规帐户特权:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();
2.指定帐户名称:mysql> SHOW GRANTS FOR ‘kari’@’myhost.example.com’;
3.口令以加密形式存储和显示:如果帐户有口令,则SHOW GRANTS将在GRANT语句末尾显示一条IDENTIFIED BY PASSWORD子句,该子句可列出帐户的全局特权;由于口令是 使用单向加密存储的,因此MySQL无法显示未加密的口令;

— 用户特权限制;
1.不能显式拒绝访问特定用户;
2.不能将口令与特定对象(如数据库,表或例程)关联;

— 授权表;
1.MySQL服务器在启动时将授权表从mysql数据库读取到内存中,并使所有访问控制决策都以这些表为依据;授权表还指示有关帐户的其他信息,例如:
1.应遵守的任何资源限制;
2.使用帐户的客户机连接是否必须通过使用SSL的安全连接进行;
2.表与特权级别对应:
1.user:每一个账户在user表包含一条记录,它记录了用户拥有的全局的权限,也包含了一些其它信息,比如:使用资源的限制,客户端连接进来是否需要使用SSL链接;
2.db:列出了账户对指定数据库的权限;
3.tables_priv:列出了账户对指定表的权限;
4.columns_priv:列出了账户对指定列的权限;
5.procs_priv:列出了账户对存储过程和函数的权限;
3.这些表指示了每个帐户的有效性和特权,并且每个级别的安全程度是递进的;

— 使用授权表;
1.每个授权表有host列和user列,用于标识其记录适用的帐户:
1.在连接尝试过程中,服务器会确定客户机是否能连接:将用户表中的某个记录与客户机发起连接的主机,客户机提供的用户名 以及匹配记录中列出的口令匹配;
2.在连接后,服务器会确定每条语句的访问特权;
2.MySQL安装过程会创建授权表:
1.授权表使用MyISAM存储引擎;
2.MyISAM保证可用。

— 影响特权更改;
1.MySQL会维护授权表的内存中副本以避免访问磁盘上表的开销:
1.应避免直接在授权表中修改用户帐户,原因;
1.帐户管理语句的语法设计清晰,简单明了;
2.如果在某个帐户管理语句中犯错,该语句就会失败,不会更改任何设置;
3.如果在直接更改授权表时犯错,则可能会将所有用户锁在系统外面;
2.如果直接修改授权表,应通过发出FLUSH PRIVILEGES语句(或者执行mysqladmin flush-privileges或mysqladmin reload命令显式重新装入了表)显式重新装入授权表;
2.帐户修改语句(如GRANT/REVOKE/SET PASSWORD和RENAME USER)会将更改同时应用于授权表及其内存中表副本;
3.对全局特权和口令的更改仅应用于该帐户的后续连接;
4.对数据库级别特权的更改在客户机的下一条USE db_name语句后应用;
5.对表和例程特权的更改会立即应用;

— 撤消帐户特权;
1.使用REVOKE语句可以撤消特定的SQL语句特权:
1.REVOKE关键字:指定要撤消的特权列表;
2.ON子句:指示要撤消特权的级别;
3.FROM子句:指定帐户名称;
eg:REVOKE DELETE, INSERT, UPDATE ON world_innodb.* FROM ‘Amon’@’localhost’;
2.撤消特权以便将特权授予其他用户:REVOKE GRANT OPTION ON world_innodb.* FROM ‘Jan’@’localhost’;
3.撤消所有特权(包括向他人授权):REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘Sasha’@’localhost’;
4.在发出REVOKE之前使用SHOW GRANTS语句确定要撤消的特权,随后再次确认结果;

eg:回收权限:revoke prilileges on objects from ‘user’@’host’;
1.回收全局权限:revoke all privileges, grant option from ‘u1’@’%’;
2.回收数据库的权限:revoke select, grant option on mysql.* from ‘u1’@’%’;
3.回收表的权限:revoke select on mysql.user from ‘u1’@’%’;
4.回收列的权限:revoke select(host, user) on mysql.user from ‘u1’@’%’;

— SHOW PROCESSLIST;
1.SHOW PROCESSLIST(查看INFORMATION_SCHEMA.PROCESSLIST表或mysqladmin processlist命令获取线程信息)显示了正在运行的进程线程;
2.SHOW PROCESSLIST将生成以下列:
– Id:连接标识符;
– User:发出语句的MySQL用户;
– Host:发出语句的客户机的主机名;
– db:选择默认数据库,否则为NULL;
– Command:线程正在执行的命令类型;
– Time:线程处于当前状态的时间(秒);
– State:指示线程正在执行的内容的操作,事件或状态;
– Info:线程正在执行的语句;否则为NULL;
3.PROCESS特权允许查看所有线程,否则只能查看自己的线程;
4.如果出现了“连接数太多”错误,但希望确定正在执行的语句,则使用SHOW FULL PROCESSLIST语句会非常有用;
5.MySQL额外保留了一个连接,供拥有SUPER特权的帐户使用,这样可确保即使达到连接数限制,管理员也始终可以连接并检查系统(前提是应用程序用户没有SUPER特权);
6.使用KILL语句可中止进程;

— 禁用客户机访问控制;
要指示服务器不读取授权表并禁用访问控制,可使用–skip-grant-tables选项:
1.每个连接都成功:
1.可以提供任何用户名及任何口令,并且可以从任何主机连接;
2.该选项将禁用整个特权系统:会禁用帐户管理语句,如CREATE USER,GRANT,REVOKE和SET PASSWORD;
3.连接的用户实际上拥有所有特权;
2.阻止客户机连接:
1.使用–skip-networking选项可阻止网络访问,并且仅允许在本地套接字,命名管道或共享内存上访问;
2.使用–socket选项可在非标准套接字上启动服务器以防止本地应用程序或用户随便访问;

补充:忘记用户密码:
1.Linux中的处理方法:
1.关闭服务器或者kill掉mysqld的进程;
2.使用–skip-grant-tables选项登录,启动服务器并跳过授权表
3.使用mysql登录,可以不用使用用户名和密码,并且有全部权限;
4.登录之后直接更新mysql.user表密码字段为空即可;
2.Windows中的处理方法:
1.停止mysql的服务:net stop mysql;
2.到mysql的bin目录下执行mysqld -nt –skip-grant-tables;
3.到新的命令行下执行mysqladmin -uroot flush-privileges password ‘pwd’;
4.然后关闭mysql服务:mysqladmin -uroot -p shutdown,此时输入刚刚设置的密码;
5.启动mysql服务:net start mysql;

— 设置帐户资源限制;
1.通过将全局变量MAX_USER_CONNECTIONS设置为非零值,限制使用服务器资源:这将限制任何一个帐户的同时连接数量,但不会限制客户机在连接后能执行的操作;
2.限制单个帐户的以下服务器资源:
1.MAX_QUERIES_PER_HOUR(max_queries):一个帐户每小时可发出的查询数量;
2.MAX_UPDATES_PER_HOUR(max_updates):一个帐户每小时可发出的更新数量;
3.MAX_CONNECTIONS_PER_HOUR(max_connections):一个帐户每小时可连接到服务器的次数;
4.MAX_USER_CONNECTIONS(max_user_connections):允许的同时连接数量;
3.每个限制的默认值是零,表示没有限制;
4.查看:SELECT * FROM mysql.user\G
eg:
mysql> GRANT ALL ON *.* TO ‘francis’@’localhost’
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;

— 课后练习;

— 分区;
1.分区就是将数据库或其构成元素划分为不同的独立部分;
1.预先组织表存储的方法;
2.当大型表占用大量可用磁盘空间并且需要其他空间来存储表数据时,可以使用物理分区;
3.表文件可以放在多个位置,而不是将数据集中于一个过载的磁盘;
4.此技术与存储引擎有关;
2.MySQL支持水平分区;
1.将特定表行分配为行的子集;
2.有水平分区和垂直分区,本课着重讲述称为“水平分区”的MySQL分区技术;

— 分区类比;
1.“搬家”的类比:
1.假定您正搬出家外,您可以将客厅中的所有物品都放到一个大盒子中;
2.但是,这不是很实际;您将很难找到单个物品,并且这将是一个非常大并且非常重的盒子;
3.最好使用多个盒子来存放您的所有物品,并且根据类型对这些盒子分类;
2.“Home”数据库示例:
1.可以将标签为“书籍”的盒子用于所有书籍;
2.标签为“CD”的盒子用于CD,标签为“影片”的盒子用于影片等等;
3.您现在已将您的物品“分区”到特定盒子中;

— 特定于MySQL的分区;
1.分区的分布是跨物理存储进行的:
1.根据用户在需要时设置的指定规则:MySQL分区允许你设置的规则将各个表的部分分布在整个物理存储中;
2.每个分区存储为单独的单元,非常像一个表;
2.数据的划分:
1.根据分区功能将数据划分为子集:数据的划分通过分区功能来完成,这在MySQL中可以是针对一组范围或值列表,内部散列函数或线性散列函数的简单匹配;
2.分区类型和表达式是表定义的一部分;使用RANGE/LIST COLUMNS时,表达式可以是INT/DATE/DATETIME/CHAR/VARCHAR/BINARY/VARBINARY等数据类型;
3.表达式可以是整数或返回整数值的函数,表达式必须返回NULL或整数值;
4.此值根据定义确定将每条记录存储在哪个分区中;

— 分区类型;
MySQL支持多种类型的分区:
1.RANGE:根据属于指定范围的列值将行分配到分区;范围应该连续但不重叠,使用VALUES LESS THAN运算符进行定义;
2.LIST:根据与离散值集之一匹配的列将行分配到分区;像在由RANGE进行分区一样,必须显式定义每个分区;
3.HASH:基于由用户定义的表达式返回的值而选择的分区,对要插入表中的行的列值进行操作;
4.KEY:与HASH类似,不同之处在于仅提供要评估的一个或多个列并且MySQL服务器提供散列函数;它适用于所有允许的列类型;
5.COLUMNS:RANGE和LIST分区上的变体;COLUMNS分区允许在分区键中使用一个或多个列;
1.RANGE COLUMNS和LIST COLUMNS分区支持使用非整数列(以及前面列出的其他数据类型)来定义值范围或列表成员;
6.LINEAR:MySQL还支持线性散列,其不同于常规散列,线性散列使用线性2的幂算法,而常规散列使用散列函数值的模;

— 分区支持;
1.确定服务器分区支持状态:SHOW PLUGINS\G
1.如果MySQL二进制文件构建有分区支持,您不需要执行任何进一步操作来启用该二进制文件(例如,my.cnf文件中不需要任何特殊条目);
2.如果您没有看到partition插件以及Status值ACTIVE列在SHOW PLUGINS的输出中,则您的MySQL版本不支持分区;
2.禁用分区支持:shell> mysqld –skip-partition
1.partition插件现在具有值DISABLED;

— 使用分区改善性能;
1.特定于大型表的地址问题:
1.将数据分为较小的组以使查询更有效:将数据分割为逻辑分组的表中时,查询搜索较少记录,这样可以大大提高速度;
2.遵守文件系统的文件大小限制;
3.加快维护和删除行运行时速度:例如维护操作运行时速度缓慢(例如,OPTIMIZE和REPAIR)和删除不需要的行时运行时速度缓慢;
2.使用较小的索引来允许“最热”分区具有内存中的整个索引;
3.删除分区来更方便地删除数据;
4.根据日期和时间部署表:例如,您可能希望按年,季度或月部署店铺订单表;

— 使用分区改善性能:删改(分区排除);
1.分区排除以将查询范围限制为确切范围:
1.MySQL优化器可以为查询过滤不需要的分区,它仅搜索符合查询标准的分区;
2.使用WHERE子句进行整表扫描可以作为非匹配删改掉分区;
3.示例所示,假定您有一个书店并且您具有一个数据库,其中包含书店的整个书籍库存;此数据库具有针对每种书籍类别的表,一个客户来到书店,要求提供1990年和2007年之间出版的医学类别的所有书籍列表;因为您使用了分区并且按年份对医学表进行了分区,所以您可以运行仅包括这些年份的查询;优化器“删改”该搜索来仅扫描1990–2007分区,节省了大量时间和工作;
SELECT title FROM book
WHERE category = ‘medicine‘
AND published > 1990 AND published < 2007; 2.执行显式分区选择; 1.例如,如果您知道p0包括2007年以前出版的所有书籍,可以显式指出该分区,从而允许优化器忽略其他分区;可以通过提供列表来选择多个分区和子分区,例如PARTITION(p0, p2sp1); SELECT title FROM book PARTITION(p0) WHERE category = 'medicine‘ AND published > 1990;
3.还将显式分区选择用于INSERT/REPLACE/UPDATE/DELETE/LOAD,即使您在分区中添加或更改数据,也必须确保新数据满足该分区的分区标准;

— 分区的性能挑战;
1.打开分区表时必须打开所有分区:这是一个小问题,因为打开的表缓存在表高速缓存中;
2.删改具有特定限制:
1.删改在索引列中效率较低,因为它每个分区仅避免一次索引查找;
2.删改对于非索引列的作用要大得多:
1.其WHERE子句中包含非索引列的查询可能需要进行完整表扫描;
2.分区扫描花费的时间是表扫描花费时间的一小部分;

— 基本分区语法;
1.通过在CREATE TABLE语句中使用PARTITION BY子句来创建分区表;PARTITION BY跟在CREATE TABLE的所有部分后面:
CREATE TABLE () ENGINE=
PARTITION BY ();
2.分区类型后面是括号括起来的分区表达式;根据使用的分区类型以及您的要求,该表达式可以是表列的名称,涉及一个或多个列的简单数学表达式或者列名称的逗号分隔列表;
3.PARTITION BY类型包括在其相应语法中:
• PARTITION BY RANGE …
• PARTITION BY RANGE COLUMNS …
• PARTITION BY LIST …
• PARTITION BY LIST COLUMNS …
• PARTITION BY HASH …
• PARTITION BY LINEAR HASH …
• PARTITION BY KEY …
• PARTITION BY LINEAR KEY …

— RANGE分区;
1.使用PARTITION BY RANGE对包含一组指定范围内的指定值的行进行分区;
2.例如,假定您有一个表来存储与其订单相关的数据,并且您希望指定id号小于10,000的订单存储在一个分区中,id号介于10,000和19,999之间的订单存储在另一个分区中,id号介于20,000和29,999之间的订单存储在另一个分区中等等;换句话说,您基于连续值范围来对表进行分区;RANGE分区非常适合于此类分区模式;
CREATE TABLE orders_range
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);

— LIST分区;
1.使用PARITION BY LIST对包含指定值的行进行分区;
2.为每个分区指定值列表,从而包含匹配列值的行将存储在相应分区中;例如,假设您的每个订单都放在组织到五个地区中的多个不同店铺中的一个店铺,并且您希望店铺订单放在相同分区中的每个地区中;使用这种类型的分区,您可以任意分隔店铺;假定在这五个地区中有19个店铺,可以创建orders_list表,其按如下所示将来自相同地区的店铺的订单分配到相同分区;
CREATE TABLE orders_list
(id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
INDEX idx (id)
) ENGINE = InnoDB
PARTITION BY LIST(store_id)
(
PARTITION p0 VALUES IN (1, 3, 4, 17),
PARTITION p1 VALUES IN (2, 12, 14),
PARTITION p2 VALUES IN (6, 8, 20),
PARTITION p3 VALUES IN (5, 7, 9, 11, 16),
PARTITION p4 VALUES IN (10, 13, 15, 18)
);

— HASH分区;
1.PARTITION BY HASH确保在分区中平均分布数据;
2.与RANGE或LIST不同,HASH不需要单独的分区定义;
3.该分布基于创建表时提供的表达式,使用关键字PARTITIONS,后跟所需的(整数)分区数量;
CREATE TABLE orders_hash
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY HASH(id) PARTITIONS 4;

— KEY分区;
1.PARTITION BY KEY类似于HASH,只是MySQL服务器使用其自己的散列表达式;
2.不要求分区表达式返回整数或NULL;
3.[LINEAR] KEY后面的表达式仅包含一组零或更多列名称,多个名称由逗号分隔;幻灯片中的示例创建orders_key表,其按已放置订单的日期进行分区;
CREATE TABLE orders_key
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY KEY(order_date) PARTITIONS 4;

— 子分区;
1.RANGE和LIST分区表可以进行子分区:子分区(也称为复合分区)是对分区表中的每个分区进行进一步划分;
2.子分区可以使用HASH,LINEAR HASH,KEY或LINEAR KEY分区;
3.在此“店铺订单”数据库示例中,根据orders_range表,可以进一步划分表,这样表可以将每个分区拆分为两个子分区,一共5*2=10个子分区;
CREATE TABLE orders_range_hash
(id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
PRIMARY KEY(id, store_id)
) ENGINE = InnoDB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(store_id) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);
TIPS:ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function

— 获取分区信息;
MySQL提供了多种方法来确定表的分区状态:
• SHOW CREATE TABLE:查看用于创建分区表的分区子句;
• SHOW TABLE STATUS:确定表是否进行了分区;
• INFORMATION_SCHEMA.PARTITIONS:查询INFORMATION_SCHEMA.PARTITIONS表;
• EXPLAIN PARTITIONS SELECT:显示给定SELECT语句使用的分区;

— 获取分区信息:SHOW CREATE TABLE;
SHOW CREATE TABLE orders_hash\G

— 获取分区信息:SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE ‘orders_hash’\G
1.Create_options列包含字符串partitioned;
2.Engine列包含表的所有分区使用的存储引擎的名称;但是如果指定不同的引擎会报错;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL;

— 获取分区信息:INFORMATION_SCHEMA;
1.可以在INFORMATION_SCHEMA.PARTITIONS表中查询分区详细信息;
2.获取所有数据库表及其分区的列表:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_list’ AND TABLE_SCHEMA=’orders’;
+—————-+——————+———————-+———————–+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+—————-+——————+———————-+———————–+
| p0 | LIST | store_id | 1,3,4,17 |
| p1 | LIST | store_id | 2,12,14 |
| p2 | LIST | store_id | 6,8,20 |
| p3 | LIST | store_id | 5,7,9,11,16 |
| p4 | LIST | store_id | 10,13,15,18 |
+—————-+——————+———————-+———————–+
5 rows in set (0.00 sec)

— 获取分区信息:EXPLAIN PARTITIONS;
1.显示MySQL处理分区的方式;
2.判断查询将访问哪些分区以及使用这些分区的方式;
3.只能用在包含行数据的表中:EXPLAIN PARTITIONS SELECT * FROM orders_range\G

— 更改分区;
1.数据库通常要求对现有分区设置进行动态更改;
2.MySQL允许多种类型的分区更改:
– 添加;
– 删除;
– 合并;
– 拆分;
– 重组;
– 重新定义;
3.使用ALTER TABLE语句的简单扩展进行这些更改;

— 重新定义分区类型;
1.将非分区表更改为分区表;
2.完全重新定义现有分区,包括类型;
3.将ALTER TABLE与PARTITION BY配合使用,将分区类型从RANGE更改为HASH:
1.查看分区类型:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
2.在线重定义:
ALTER TABLE orders_range PARTITION BY HASH(id) PARTITIONS 4;
3.再次查看;
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;

— 交换分区;
1.将表分区或子分区与表交换:
1.将分区或子分区中的所有现有行移至非分区表;
2.将非分区表中的所有现有行移至表分区或子分区;
3.交换分区不会在分区表或交换表上调用触发器;
4.将重置交换表中的所有AUTO_INCREMENT列;
2.要交换的表不能进行分区:
1.它必须与分区表具有相同表结构:
2.交换之前,非分区表中的行必须位于为分区或子分区定义的范围内;
例子:
1.把表修改回范围分区;
ALTER TABLE orders_range
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);
2.查看表信息:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
3.创建测试表并插入数据;
CREATE TABLE orders_ex
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB;
INSERT INTO orders_ex VALUES(1, ‘test’, 1, 1, sysdate(), ”);
INSERT INTO orders_ex VALUES(10001, ‘test’, 1, 1, sysdate(), ”);
COMMIT;
INSERT INTO orders_range VALUES(1, ‘xxx’, 1, 1, sysdate(), ”);
INSERT INTO orders_range VALUES(10001, ‘xxx’, 1, 1, sysdate(), ”);
COMMIT;
4.分区交换;
mysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
ERROR 1737 (HY000): Found a row that does not match the partition
5.修正数据;
mysql> UPDATE orders_ex SET id = 9999 WHERE id = 10001;
mysql> COMMIT;
mysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
6.查看结果;
mysql> SELECT * FROM orders_ex;
mysql> SELECT * FROM orders_range PARTITION(p0);

— 删除分区;
1.可以删除一个或多个分区;
1.删除分区与删除行不一样;虽然删除分区确实可以删除分区内的所有数据,但是使用DROP,PARTITION的主要目的是删除分区指定自身,以及不再允许将数据传递到该分区;
2.可以使用ALTER TABLE … TRUNCATE PARTITION语句删除分区表的一个或多个分区中的所有行(子句中指定的分区不必是连续的);
2.RANGE或LIST表允许此操作;
3.将ALTER TABLE与DROP PARTITION配合使用:
mysql> ALTER TABLE orders_range TRUNCATE PARTITION p0, p1;
mysql> SELECT * FROM orders_range;
mysql> ALTER TABLE orders_range DROP PARTITION p0;
mysql> SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;

— 使用DROP PARTITION的问题;
1.需要DROP特权才能使用DROP PARTITION;
2.删除分区会删除该分区中的所有数据;
3.DROP PARTITION不返回所删除的行数;
4.DROP PARTITION更改表定义;
5.可以按任何顺序删除分区;
6.将根据新分区处理插入到所删除分区中的行;
1.删除LIST分区会禁止已经匹配该分区的INSERT和UPDATE操作;
2.删除RANGE分区将删除该分区及其数据,来自INSERT和UPDATE操作的新数据将存储在范围中的下一个更大的分区,如果没有范围大于已删除分区的范围的分区,无法对已删除分区的范围中的数据执行INSERT和UPDATE操作;
7.可以用单个语句删除多个分区:ALTER TABLE orders_range DROP PARTITION p1, p3;

— 删除分区;
1.要删除表中的所有分区从而将其恢复为非分区表,请将ALTER TABLE与REMOVE PARTITIONING配合使用;
1.此操作不会删除任何表数据;
2.REMOVE PARTITIONING执行完整表复制,与普通ALTER TABLE ALGORITHM=COPY一样;
例子:
1.使orders_range表恢复到非分区状态:ALTER TABLE orders_range REMOVE PARTITIONING;
2.查看orders_range表信息:
SHOW TABLE STATUS LIKE ‘orders_range’;

— 更改分区对性能的影响;
1.根据分区的数量,与创建非分区表相比,创建分区表的速度稍微慢一些;
2.分区操作处理速度比较:
1.对于大型事务表,DROP PARTITION比DELETE快得多;
2.ADD PARTITION在RANGE和LIST表上相当快;
3.对于在KEY或HASH表上运行的ADD PARTITION,速度取决于已经存储的行数;
1.如果存在更多数据,需要花费更长时间来添加新分区;
2.在KEY/HASH上运行的ADD PARTITION将所有行重新分布到新数量的分区,有效地执行完整表复制;
4.对超大型表运行COALESCE PARTITION,REORGANIZE PARTITION和PARTITION BY时,它们的执行速度可能会很慢;
3.在此类操作过程中,硬件I/O的开销比分区引擎的开销高得多;

— 测验;
d

— 分区:存储引擎功能;
1.分区存储在与InnoDB表相同位置中的文件中:
1.可以提供DATA DIRECTORY选项来重定位分区;
2.每个PARTITION子句可以包括一个[STORAGE] ENGINE选项,该选项没有作用,每个 分区使用的存储引擎与表作为一个整体而使用的存储引擎相同;
2.每个分区在以下数据目录中具有其自己的文件:#P#.ibd
1.如果禁用innodb_file_per_table,分区将存储在共享表空间中;
3.所有数据和索引都将进行分区:
1.不能仅对数据或仅对索引进行分区;
4.分区可用于其他存储引擎:
1.不可用于MERGE,FEDERATED,CSV;
2.是在存储引擎级别实现的;
eg:
CREATE TABLE entries
(id INT,
entered DATE
)
PARTITION BY RANGE(YEAR(entered))
(
PARTITION p0 VALUES LESS THAN (2000) DATA DIRECTORY = ‘/data/p0’,
PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = ‘/data/p1’
);

— 分区与锁定;
1.MySQL表将在执行服务器代码的过程中锁定;
1.锁定操作在执行每个语句过程中由表存储引擎来处理;
2.每个存储引擎处理锁的方式均不相同;
2.每个分区有一个存储引擎实例;
1.将要求锁定每个非删改分区/存储引擎实例;
3.锁定分区表时,将锁定非删改分区;
1.仅在执行语句过程中保持该锁定;
4.ALTER…PARTITION语句通常在表上使用写入锁;
1.从此类表进行的读取相对来说不受影响,待定INSERT和UPDATE操作在分区一完成后就会执行;优先级的问题;
2.执行ALTER TABLE t PARTITION类型语句时,其在复制阶段需要在表上使用写入锁,除非该命令通过ALGORITHM=INPLACE支持联机操作;复制阶段完成后,该命令需要对表进行独占访问来更改表定义;

— 分区限制;
1.常规:
– 每个表的最大分区数为8192;
– 不支持空间类型;
– 不能对临时表进行分区;
– 不能对日志表进行分区;
2.外键和索引:
– 不支持外键;
– 不支持FULLTEXT索引;
– 无全局索引:每个分区都有各自的索引;
3.仅可能在以下情况下进行子分区:
– 通过RANGE和LIST进行分区时;
– 通过LINEAR HASH或LINEAR KEY进行时;

— 分区表达式限制;
1.用于RANGE/LIST/HASH分区的表达式的结果必须为整数:
1.RANGE COLUMNS和LIST COLUMNS分区允许更大范围的数据类型;
2.RANGE COLUMNS和LIST COLUMNS分区类型不需要整数列或表达式;
3.RANGE COLUMNS和LIST COLUMNS分区可以在分区键中使用多个列;
4.该列可以是任何整数类型/DATE/DATETIME/CHAR/VARCHAR/BINARY/VARBINARY;
2.不能在分区表达式中使用TEXT或BLOB;
3.不允许使用UDF,存储函数,变量,某些运算符和某些置函数:
1.运算符:|,&,^,< <,>>,~;
4.不应在创建表后更改SQL模式;
5.分区表达式中不支持子查询;
6.分区表达式中使用的所有列都必须是表的所有唯一索引的一部分:
1.因为索引必须与数据一起进行分区;
2.要保持“唯一性”,具有相同唯一键的两行必须转至相同分区;否则,它们将违反唯一键限制;

— 课后练习;

— 存储引擎和MySQL;
1.每个存储引擎具有一组特定运行特征:
1.这些特征包括用于管理查询争用的锁的类型以及该存储引擎是否支持事务;
2.这些引擎属性对查询处理性能,并发性以及死锁预防具有一定影响;
2.虽然可以使用许多其他存储引擎,但对于大多数用例,InnoDB都是最适合的;

— 可用存储引擎;
1.MySQL提供并维护多个存储引擎,MySQL服务器还可以与许多第三方存储引擎兼容;
2.MySQL存储引擎是数据库服务器内的低级别引擎,它负责存储和检索数据,并且可以通过内部MySQL API进行访问,在某些情况下,可以由应用程序直接访问;
3.当前支持的存储引擎:
• InnoDB
• MyISAM
• MEMORY
• ARCHIVE
• FEDERATED
• EXAMPLE
• BLACKHOLE
• MRG_MYISAM
• NDBCLUSTER
• CSV
TIPS:
1.请注意,InnoDB和NDBCLUSTER是具有事务性的唯一两个MySQL存储引擎;
2.请注意,一个应用程序可以在任何给定时间使用多个存储引擎;

— InnoDB存储引擎;
InnoDB是MySQL的默认存储引擎,具有高可靠性和高性能的特点,同时也被认为是效率最高的引擎之一:
• 事务安全(遵从 ACID):通过事务提交,回滚以及用于保护用户数据的故障恢复功能,可以实现ACID遵从性;
• MVCC(Multi-Versioning Concurrency Control,多版本并发控制)
– InnoDB行级别锁定;
– Oracle样式一致非锁定读取;
• 表数据进行整理来优化基于主键的查询;
• 支持外键引用完整性约束:包括级联删除和更新;
• 大型数据卷上的最大性能;
• 将对表的查询与不同存储引擎混合:在同一语句内,可以将InnoDB表与来自其他MySQL存储引擎的表混合;
• 出现故障后快速自动恢复:支持一致而联机的逻辑备份;
• 用于在内存中缓存数据和索引的缓冲区池;
• 全文索引:可以在文本列中有效地搜索单词或短语;

— InnoDB作为默认存储引擎;
1.查看默认引擎:SHOW VARIABLES LIKE ‘%engine%’;SELECT @@default_storage_engine;
2.设置默认引擎:SET default_storage_engine = MyISAM;

— InnoDB功能;
1.[a]需要InnoDB Barracuda文件格式;
2.[b]在服务器中(通过加密功能)而不是在存储引擎中实现;
3.[c]在服务器中而不是在存储产品中实现;使用MEB进行备份时例外,其不是在服务器级别进行;

— 显示存储引擎设置;
1.查看默认引擎:
• SHOW VARIABLES LIKE ‘%engine%’;
• SELECT @@default_storage_engine;
2.使用SHOW确认每个表的存储引擎:
• SHOW CREATE TABLE City\G
• SHOW TABLE STATUS LIKE ‘CountryLanguage’\G
3.使用INFORMATION_SCHEMA确认每个表的存储引擎:
SELECT TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘City’ AND TABLE_SCHEMA = ‘world_innodb’\G

— 设置存储引擎;
1.在启动配置文件中设置服务器存储引擎:
[mysqld]
default-storage-engine=
2.设置默认引擎:
• SET default_storage_engine = MyISAM;
• SET @@storage_engine=;
3.使用CREATE TABLE语句指定:CREATE TABLE t (i INT) ENGINE = ;

— 将现有表转换为InnoDB;
1.使用ALTER TABLE更改存储引擎:
1.语法:ALTER TABLE t ENGINE = InnoDB;
2.使用ALTER TABLE更改引擎是一项成本很高的操作,因为它在内部将所有数据从一个引擎复制到另一个引擎;
3..不要将mysql数据库(例如user或host)中的MySQL系统表转换为InnoDB,此操作不受支持,系统表使用MyISAM引擎;
2.从其他存储引擎克隆表:
1.语法INSERT INTO SELECT * FROM ;
2.创建作为使用其他存储引擎的表的克隆的InnoDB表时,还可以在插入数据后创建索引;
3.以较小的块插入大型表以获得更大的控制:通过添加Key的范围,多次插入表;
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey < = somethingelse; 补充:插入了所有记录后,可以重命名表; 1.在转换大型表的过程中,增加InnoDB缓冲区池的大小来减少磁盘I/O; 2.还可以增加InnoDB日志文件的大小; -- InnoDB系统表空间; 1.InnoDB使用两个主要的基于磁盘的资源来运行: 1.表空间:在单个逻辑存储区域中存储表内容(数据行)和索引; 2.日志文件(ib_logfileN文件):记录重做和恢复的事务活动; 2.InnoDB在表空间中存储数据,索引,元数据,日志和缓冲区; 1.默认情况下(5.6之后),数据和索引存储在基于表的表空间中; 2.InnoDB使用共享表空间(ibdataN文件)来包含元数据(数据字典),撤销日志,更改缓冲区以及两次写缓冲区; 3.共享表空间是逻辑概念,可以包含多个文件;可以将共享表空间中的最后一个文件配置为自动扩展,在这种情况下,如果表空间装满,InnoDB会自动扩展该表空间; 4.默认情况下,共享表空间还包含回滚段,事务修改行时,将在回滚段中存储撤消日志信息;此信息用于回滚失败的事务,通过将innodb_undo_logs选项设置为非零值并配置innodb_undo_tablespaces的值,来将回滚段移出共享表空间; 1.innodb_undo_logs:定义在InnoDB引擎使用的系统表空间中,一个事务可以使用的回滚段的数量; 2.innodb_undo_tablespaces:当innodb_undo_logs参数为非零值时,undo日志使用的表空间文件个数;默认情况下所有的重做日志都在系统表空间中;可以通过innodb_undo_directory参数来指定文件的目录; TIPS:除了系统表空间可以包含多个文件,其它的数据表空间只能包含一个文件; -- 数据表空间; 1.除了系统表空间之外,InnoDB还在数据库目录中创建另外的表空间,用于每个InnoDB表的.ibd文件; 1.InnoDB创建的每个新表在数据库目录中设置一个.ibd文件来搭配表的.frm文件; 2..ibd文件用作表自己的表空间文件,存储表内容和索引; 3.InnoDB数据字典和回滚段存放在共享表空间; 4.可以使用innodb_file_per_table选项控制此设置; 5.您需要默认设置来使用一些其他功能,例如表压缩和快速截断; 2.基于表的表空间具备以下优势: 1.表压缩 2.空间回收(通过 TRUNCATE) 3.动态行格式 3.(可选)在共享表空间中存储数据: 1.使用skip_innodb_file_per_table选项; 2.在启动时或者会话过程中进行设置,将innodb_file_per_table选项设置为OFF;禁用该选项不会影响已经创建的任何InnoDB表的可访问性;如果要修改这个值的话要关闭服务器,修改配置文件,启动服务这几步; 4.使用共享表空间可获得以下优势: 1.移除大量数据的语句使用较少文件系统开销,例如DROP TABLE或TRUNCATE TABLE; 2.避免冗余临时数据文件; TIPS:可以混合同一数据库中不同表中的表空间类型,更改该设置仅会更改已创建的新表的默认值; -- 表空间目录结构; MySQL服务器使用磁盘空间有几种方式: 1.数据库目录:每一个数据库对应一个数据目录(datadir)下的目录,不管你创建哪种类型的表.比如,一个数据库目录可以包含MYISAM引擎的表,INNODB引擎的表或者混合的表; 1..ibd文件:用作表自己的表空间文件,存储表内容和索引; 2..frm文件(Table Format Files):包含了表结构的描述信息,每一个表都有一个.frm文件在对应的数据库目录下.它与表使用的哪种引擎没有关系; 3..MYD/.MYI:由MYISAM存储引擎在适当的数据库目录下创建的数据库数据文件和索引文件; 2.InnoDB存储引擎: 1.表空间:在单个逻辑存储区域中存储表内容(数据行)和索引; 1.默认情况下,数据和索引存储在基于表的表空间中; 2.InnoDB使用共享表空间(ibdata1文件)来包含元数据(数据字典),撤销日志,更改缓冲区以及两次写缓冲区; 2.日志文件(ib_logfileN文件):记录回滚和恢复的事务活动; 3.服务器日志文件和状态文件:这些文件包含服务器上执行过的语句信息,日志被用于复制和数据恢复,获得优化查询性能的信息和误操作信息; 1.auto.cnf:保存服务器的server_id; 2.mysql.sock:套节字文件; 3.hosname.err:错误日志; 4.hostname.pid:运行进程id; 5.mysql_upgrade_info:升级信息; -- 共享表空间配置; 1.通过添加数据文件增加共享表空间大小; 1.在my.cnf文件中使用innodb_data_file_path选项,值应该是一个指定项列表: [mysqld] innodb_data_file_path=datafile_spec1[;datafile_spec2]... 2.如果命名了多个数据文件,则通过分号[;]符号分隔这些文件; 3.增加InnoDB系统表空间的一种更容易(但是不太可取)的方式是从一开始就将其配置为自动扩展;在表空间定义中指定最后一个数据文件的autoextend属性,然后,InnoDB在用完空间时会以64MB增量自动增加该文件的大小; 4.可以通过设置innodb_autoextend_increment系统变量的值来更改该增量大小,以MB为单位; 5.如果使用关键字autoextend定义了最后一个数据文件,重新配置表空间的过程必须考虑最后一个数据文件已经增长到的大小;获取数据文件的大小,将其向下舍入到1024×1024字节(=1MB)的最近倍数,并在innodb_data_file_path中显式指定舍入的大小;然后,可以添加其他数据文件(使用尚不存在的文件名); 6.仅innodb_data_file_path中的最后一个数据文件可以指定为自动扩展; 2.配置示例:创建一个表空间,其中包含一个名为ibdata1且大小为50MB(固定)的数据文件和一个名为ibdata2且大小为50MB(自动扩展)的数据文件: [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 3.默认情况下将文件放置在data目录中,如果需要,使用innodb_data_home_dir参数显式指定文件位置; -- 日志文件和缓冲区:图表; 1.日志缓冲区: 1.客户机执行事务时,其进行的更改存放在InnoDB日志中,最新日志内容缓存在内存中(日志缓冲区); 2.主要包括:事务记录和重做日志记录等; 3.缓存的日志信息会在事务提交时写入并刷新到磁盘上的日志文件中,虽然也可能提前发生该操作; 4.如果修改表时发生故障,将使用日志文件进行自动恢复; 5.MySQL服务器重新启动时,它会重新应用日志中记录的更改,以确保表中反映所有已提交事务; 6.更改InnoDB日志文件的数量和大小的信息: 1.innodb_log_buffer_size 2.innodb_log_compressed_pages 3.innodb_log_file_size 4.innodb_log_files_in_group 5.innodb_log_group_home_dir 2.缓冲区池: 1.InnoDB维护其自己的缓冲区池用于在主内存中缓存常用的数据和索引;InnoDB在表空间中存储其表和索引;InnoDB表可以非常大,甚至在文件大小限制为2GB的操作系统上也是如此; 2.主要是包括插入插销日志和更新撤销日志; 3.可以启用多个缓冲区池来最大程度地降低争用; 4.此高速缓存应用于如此多种类型的信息并且将处理速度提高如此多,应该将多达80%的数据库服务器的物理内存分配给InnoDB缓冲区池; 5.由innodb_buffer_pool_%参数控制; -- 配置缓冲区池以获得良好性能; InnoDB缓冲区池很大时,可以从内存(而不是硬盘)快速检索许多数据请求: 1.MySQL使用多个缓冲区池实例来最大程度地降低线程之间的争用:用来改进大型缓冲区池的局限; 2.存储在缓冲区池中或者从缓冲区池读取的每个页将基于散列值分配给一个缓冲区池:使用散列函数将缓冲区池中的每个页随机分配给一个缓冲区池 3.每个缓冲区池管理其自己的数据:每个缓冲区池管理其自己的空闲列表,刷新列表,LRU以及连接到缓冲区池的所有其他数据结构,并由其自己的缓冲区池互斥锁进行保护; 4.配置有下列选项: 1.innodb_buffer_pool_instances:默认情况下,MySQL配置八个缓冲区池实例,范围为1-64;仅当innodb_buffer_pool_size的值大于等于1GB时才生效; 2.innodb_buffer_pool_size:缓冲池的总大小,要保证每个缓冲池实例都大于1G; 5.在启动时预装入缓冲区池来提高性能: 1.innodb_buffer_pool_load_at_startup; 2.innodb_buffer_pool_dump_at_shutdown; -- NoSQL Memcached API; 1.在相同进程空间中运行:InnoDB支持具有集成的Memcached守护进程的插件,其在相同进程空间中运行,从而以较低开销进行高效数据通信; 2.使用Memcached API:对于某些操作,通过使用较简单的Memcached API(基于文本的协议和二进制协议),而不是使用SQL层,应用程序可以在提交SQL语 句时绕过所需的解析和优化阶段,并避免检查强类型数据的开销;此类型的应用程序称为NoSQL(Not Only SQL,不仅仅是SQL); 3.使用InnoDB存储:除了传统的Memcached环境(其在重新启动Memcached时会丢失键/值存储),InnoDB集成表示可以将Memcached存储配置为持久存储到InnoDB;因为InnoDB支持该存储,所以可以受益于InnoDB的其他功能; 1.自动持久存储到磁盘; 2.缓冲区和故障恢复的优势:进行保护以免于故障,中断和损坏;可以使用内存存储的Memcached存储(由强大而持久的InnoDB表支持)来获得速度和简化; 4.仍可以通过SQL访问基础表以进行报告,分析,即席查询,批量装入,集合运算(例如并集和交集)以及非常适合于SQL的表达性和灵活性的其他运算(例如汇总和聚合); 5.因为Memcached消耗相对较少的CPU并且易于控制其内存容量,所以可以与MySQL实例在同一系统上一起顺利运行; -- 配置Memcached; 1.配置Memcached表:SOURCE /scripts/innodb_memcached_config.sql(/usr/share/mysql/innodb_memcached_config.sql);
1.innodb_memcached_config.sql脚本设置Memcached配置数据库及其表;
1.创建innodb_memcache数据库;
2.cache_policies表:存放每个Memcached操作的后备存储策略;本地RAM高速缓存,InnoDB或两者;
3.Containers表:存放用作Memcached后备存储的每个InnoDB表的配置;
4.config_options表:存放separator和table_map_delimiter配置选项的值;
2.默认InnoDB后备存储是test数据库中的demo_test表,可以通过修改innodb_memcache数据库中的容器表来更改此项;
3.该插件启用许多可配置变量:SHOW VARIABLES LIKE ‘daemon_memcached%’;
2.安装Memcached插件:
INSTALL PLUGIN daemon_memcached SONAME “libmemcached.so”;
3.配置Memcached变量:
1.daemon_memcached_option:在启动时传递给Memcached守护进程的空格分隔的选项;
2.daemon_memcached_enable_binlog:启用Memcached操作的二进制日志记录,其允许复制Memcached API操作;
3.daemon_memcached_r_batch_size:控制读取批处理大小,默认值为1,出于性能考虑;
4.daemon_memcached_w_batch_size:控制写入批处理大小,默认值为1,所以会立即提交每个Memcached写入操作;
例子:可以从test.demo_test中查看;
— Key (c1) must be VARCHAR or CHAR type, memcached supports key up to 255
— Bytes
— Value (c2) must be VARCHAR or CHAR type
— Flag (c3) is a 32 bits integer
— CAS (c4) is a 64 bits integer, per memcached define
— Exp (c5) is again a 32 bits integer

— 键/值数据;
1.使用Memcached的主要好处是读取或写入键/值数据时效率较高;
2.Memcached协议支持add/delete/set/get/incr和decr等操作;
3.每个操作处理高速缓存中的键/值对:对于键/值查找,Memcached件使用的直接低级别数据库访问路径比等效SQL查询高效得多;
1.键和值作为字符串来传递;
2.键不能包含空格,回车符,换行符,制表符等空白字符:由于Memcached协议的规定;
3.键类似于表中的主键,值类似于同一表中的另一列:在概念上,这类似于一个包含两列的表,其中键列是主键(因此是唯一的);
4.高速缓存是平面名称空间:
1.确保每个键都是唯一的;
2.例如,如果您提供其他表中的键/值,则在键前面附加该表名称;
5.值可以是简单字符串(或存储为字符串的数值)或序列化复杂数据:值存储为字符串,可以表示简单值,例如数值,字符串或已经序列化的复杂值(即,置于可以重新构造原始指针和嵌套的文本形式);必须在API接口的Memcached外部执行此序列化(例如,通过在代码中实现JavaSerializable接口)或者通过将复杂数据格式化为JSON或XML;
类似地,必须在访问memcached表的SQL语句中将简单数值从字符串转换为数值(例如,通过使用CAST()函数);

— 将NoSQL用于MySQL数据库;
1.使用InnoDB表作为Memcached操作的容器(后备存储);
1.Memcached 协议不支持简单聚合,联接数据或SQL协议支持的其他功能;
2.使用InnoDB表作为Memcached键/值对的容器,可以运行功能强大的SQL语句,来执行使用Memcached协议创建和修改的数据分组,聚合和联接;
2.键和值是字符串:
1.键最大大小为250字节;对于更长的键:
1.将键值散列到小于250字节的值;
2.为Memcached键重新配置允许的大小;
3.通过指定不同类的Memcached数据来使用多个InnoDB表作为Memcached容器;
1.在containers表中配置此项;
2.指定容器表的名称,模式和键列,值,标志以及其他设置;
3.每个容器具有一个Memcached名称,可以用来标识Memcached操作的容器;操作定位于名为“default”的容器,直到被要求更改容器;
4.例如,以get @@newcontainer形式发出Memcached请求,将后续请求重定向到名为“newcontainer”的容器;

— 引用完整性;
将相关的数据分割到单独表中时,可以使用主键和外键来加强表关系;
1.一个表的主键被另一个表中的键引用:
1.引用键称为外键;
2.每个表都需要主键:通过使用最常查询的列为每个表指定主键,如果没有明显主键则指定自动增量值;
2.关系确定SQL语句的结果:
1.INSERT到辅助表(而没有指向主表的外键)不会完成;
2.通过在联接的列上使用外键将提高JOIN性能:而且外键列需要添加索引;
3.UPDATE或DELETE数据时,可以使用CASCADE选项自动更新或删除辅助表中的任何相关数据;
3.可以查看示例数据库中的City表;
TIPS:一般不会使用外键;

— 多版本控制;
1.保存关于已更改行的旧版本的信息:
1.支持并发和回滚等事务功能;
2.InnoDB使用回滚段中的信息执行事务回滚中所需的撤消操作;
3.它还使用该信息来构建行的早期版本以实现一致读取;
2.使用称为回滚段的数据结构在表空间中存储信息:回滚段是包含撤消日志的存储区域,其默认情况下是系统表空间的一部分;
3.InnoDB向数据库中存储的每行添加三个字段:
1.DB_TRX_ID:六个字节的字段,指示插入或更新该行的最后一个事务的事务标识符;此外,删除在内部被视为更新处理,其中设置行的特殊位来将其标记为删除;
2.DB_ROLL_PTR:称为滚动指针的七个字节的字段;它指向写入回滚段的撤消日志记录,如果更新了行,撤消日志记录包含重建行被更新前的内容所需的信息;
3.DB_ROW_ID:包含行ID的六个字节的字段,随着插入新行而单调增加;如果InnoDB自动生成群集索引,该索引包含行ID值;否则,DB_ROW_ID列不显示在任何索引中;
4.仅当行放弃针对删除写入的更新撤消日志记录时,才物理地删除该行(及其索引):
1.这是快速清除操作;
2.清除线程可能需要更多资源,因为它可能会变慢并占用磁盘空间;
3.如果在表中按相同速度以小批量插入和删除行,清除线程会滞后,表会因为所有“死”行而变得越来越大,使所有操作受到磁盘限制并且非常慢;在这种情况下,通过使用innodb_max_purge_lag系统变量限制新行操作并向清除线程分配更多资源;

— 锁定;
InnoDB具有下列一般锁定属性:
1.InnoDB无需设置锁即可实现一致读取,因为它使用多版本控制,从而不需要锁;修改行的事务可以查看其自己版本的那些行,撤消日志使其他事务可以查看原始行;要强制SELECT语句锁定数据,可以向该语句添加锁定修饰符(FOR UPDATE, LOCK IN SHARE MODE);
2.需要锁时,InnoDB使用行级别锁定,与多版本控制一起,这将产生良好的查询并发性,因为给定表可以由不同客户机同时读取和修改;
3.InnoDB在发现需要锁时可以获取行锁,从不通过将锁转换为页锁或表锁来提升该锁;这样可以最大程度降低锁争用,提高并发性(虽然其对DDL操作使用表级别锁定);
4.可能会出现死锁,因为InnoDB在事务过程中直到需要锁时才获取锁,InnoDB可以删除死锁并回滚一个事务来解决死锁;
5.失败的事务最终开始超时,InnoDB将回滚这些事务;

— 下一键锁定;
1.InnoDB使用一种称为下一键锁定的算法:
1.使用行级别锁定;
2.搜索/扫描表索引并在索引记录上设置共享锁或互斥锁;
3.因此,行级别锁实际是索引记录锁;
2.InnoDB在索引记录上设置的下一键锁定会影响索引记录前面的“间隙”:
1.如果用户在索引中的记录上具有共享锁或互斥锁,其他用户无法按索引顺序正好在锁定记录前面(间隙)插入新记录;
3.锁定间隙可防止出现“幻影问题”:
1.同一查询在不同时间产生不同行集时,事务内会出现幻影问题;
2.在下面的示例中,第一个事务锁定大于10的任何值,即使该值不存在也是如此:
事务 1:
SET autocommit = ON;
START TRANSACTION;
SELECT c FROM t WHERE c > 10 FOR UPDATE;
事务 2:
INSERT INTO t(c) VALUES (50);

— 一致非锁定读取;
1.一致读取表示InnoDB使用多版本控制对查询提供某个时间点的数据库快照;查询看见在时间点之前提交的事务所进行的更改,不会看见后面或未提交事务进行的更改;
2.在幻灯片示例中,仅当会话2已提交了插入并且会话1也已提交(从而时间点超过会话2的提交)时,会话1才看见会话2插入的行;

— 减少死锁;
1.要减少死锁的可能性,使用事务,而不是LOCK TABLE语句(不升级锁):
1.保持事务较小并经常提交:从而其不会保持打开状态很长时间;
2.不同事务更新多个表时使用相同顺序的操作;
3.在WHERE子句中使用的列上创建索引;
2.如果事务由于死锁而失败则重新发出该事务:
1.死锁并不危险,只需要重试;
2.如果出现死锁,InnoDB会检测到该情况并回滚一个事务(牺牲者);
3.按固定顺序访问表和行。
4.向表添加精心选择的索引。
5.不要设置隔离级别以避免死锁。
1.隔离级别更改读取操作的行为,而死锁是由于写入操作而出现的;
6.监视死锁出现的频率;
1.使用SHOW ENGINE INNODB STATUS\G命令;

— 外键锁定;
InnoDB支持外键CONSTRAINT设置:
• 需要检查约束条件
• 用于 INSERT、UPDATE 和 DELETE
• 在要检查的记录上设置共享记录级别锁
• 在约束失败的情况下也将设置锁

1.约束就是在表中的一个或多个列值上放置的限制,从而积极强制执行完整性规则;约束使用索引来实现;
2.如果在表上定义了外键约束,将在用于引用外键约束的插入,更新或删除操作中的任何记录上放置共享记录级别锁;
3.InnoDB逐行检查外键约束;执行外键检查时,InnoDB在其必须查看的子或父记录上设置共享行级别锁;InnoDB立即检查外键约束;该检查不会延迟到事务提交时;
4.InnoDB在约束失败的情况下也设置这些锁;
5.幻灯片中的约束示例显示City表的CountryCode列与Country表的Code列相关;

— MyISAM存储引擎;
1.在硬盘上MyISAM引擎用三种文件管理表,这些文件都在数据目录中:
1..frm文件:存储表结构的定义;
2..MYD文件:存储表行的内容;
3..MYI文件:存储表的索引;
2.在操作系统上支持链接文件的特性,可以把数据文件和索引文件存放在不同的磁盘上以减小I/O:
1.查看当前服务器指定的默认存储引擎:show variables like ‘storage_engine’;
2.通过查看CREATE TABLE的帮助(? CREATE TABLE),我们可以看到,可以通过指定DATA DIRECTORY和INDEX DIRECTORY选项来把数据文件和索引文件存放在不同的目录;
1.创建存放数据文件和索引文件的目录:mkdir -p /tmp/mysql/data /tmp/mysql/index;
2.修改文件的权限:chown -R mysql:mysql /tmp/mysql;
3.创建表,并指定数据文件和索引文件的目录:CREATE TABLE t (id INTEGER) ENGINE=MyISAM DATA DIRECTORY=’/tmp/mysql/data’ INDEX DIRECTORY=’/tmp/mysql/index’;
4.此时就会在数据文件下创建真正数据文件和索引文件的链接;
5.有没有参数可以指定缺省的目录,每次创建表都去指定太费劲了吧;??????
6.分区表指定数据文件和索引文件;???????
3.MyISAM与其它引擎相比具有最灵活的AUTO_INCREMENT COLUMN功能,自动增长列:
1.创建自动增长列:create table t(id integer auto_increment primary key, name varchar(50));
2.使用自动增长列(auto_increment),此列必须是主键或者是主键中的一列;
3.插入时可以指定id列也可以不指定,如果指定的话就插入指定的值,如果不指定则插入比当前最大值大1的值,即max(id)+1;
4.获得上次插入的id值使用last_insert_id()函数:select last_insert_id();
5.设置自动增长id的初始值:alter table table_name auto_increment=n;但是如果这是的n小于max(id)则不生效;
6.为一个已存在的表增加一个自增长列:alter table table_name add column col_name integer auto_increment not null, add primary key(id);
4.不支持事务:
1.查看自动提交事务的选项:show variables like ‘autocommit’;
2.修改此选项:set session autocommit=0|1;
3.因为不支持事务,所以不管此选项为何值,都不能commit和rollback;
5.MyISAM的表可以被转换成一个fast,compressed,read-only的表从而节省空间:
1.使用myisampack工具:mysqlpack –help;
2.进入到数据文件目录:myisampack table_name;
3.观察发现压缩比例为90%,只剩下10%的数据量大小;对指定DATA DIRECTORY和INDEX DIRECTORY选项的表同样使用,亲测;
4.重启之后检测表(check table table_name)就会发生错误,表内数据丢失,对只读的性能支不好,如果使用的话,压缩完之后一定要备份;
6.MyISAM支持FULLTEXT索引和spatial数据类型,全文索引和空间数据类型:
1.对全文索引支持不够好,可以使用instr()函数去实现;
2.也可以使用第三方的插件去实现;
7.MyISAM引擎对锁的支持:
1.MySQL管理使用MyISAM引擎的表之间的查询竞争使用表级的锁,这使得查询的性能非常快,多个查询可以同时访问一张表.对于写操作,会使用一个exclusive的表级锁去阻止其它读写操作.虽然表级锁会影响点性能,但是不会发生死锁;
2.显式的对表加锁:lock table table_name lock_type; lock tables table_name1 lock_type, table_name2 lock_type;
3.解锁:unlock tables;
4.如果在session中加write锁,则本session可以进行读写操作,其它的session对表进行读和写就需要等待锁释放;
5.如果在session中加read锁,则本session和其它session都可以进行读操作,本session无法进行写操作,其它的session对表进行读和写就需要等待锁释放;
6.如果在session中加read local锁,即只锁住加锁前一时刻的表的数据,则本session可以进行读操作(查到的只是加锁前的数据),但是不能进行写操作,其它session可以进行插入操作(查到的是所有的数据),但是删除和更新操作需要等待锁释放;
8.可以通过LOW_PRIORITY和HIGHT_PRIORITY来影响MyISAM表的内部调度机制;可以通过INSERT DELAYED先把表中数据缓存到服务端,等到表不忙的时候再插入;
1.一般情况下在OLTP系统中,更新操作优先于查询操作,因为更新操作时间比较短,查询操作时间比较长(所以一般要提升查询操作的优先级,降低更新操作的优先级);要修改查询的优先级使用:SELECT HIGH_PRIORITY * FROM t;修改更新语句的优先级使用:INSERT INTO t VALUES(1);可以使用read local锁做实验,优先级低的无法插入,优先级高可以插入;
2.数据延迟插入:INSERT DELAYED INTO t VALUES(1);可以使用write锁来模拟这种情况,不实用delayed则等待,使用就马上返回成功,但是真正插入要等表不忙的时候;当有延迟操作时会产生一个延迟进程,它是一个共用的线程,只有一个;
3.查看当前链接到服务器的进程列表:show processlist;杀掉进程:kill id;
4.与延迟插入有关的全局变量:show global variables ‘%delayed%’;
1.delayed_insert_limit:延迟插入时,插入多少条数据后检查是否有查询操作,如果有查询操作,则查询操作先执行;
2.delayed_insert_timeout:限制延迟操作的等待时间;
3.delayed_queue_size:定义延迟线程队列的大小,以行为单位;
5.与延迟插入有关的全局状态:show global status ‘%delayed%’;
1.delayed_errors:记录延迟插入错误的次数;
2.delayed_insert_threads:当前有多少线程在使用延迟操作;
3.delayed_writes:使用延迟线程插入的记录行数;
9.数据表的存储格式是非常的轻便的,因此可以通过直接拷贝表所在的目录到其它的主机以实现对表的备份额迁移:
1.拷贝时需要注意文件的权限和拥有者,一般指定cp -a选项;
2.拷贝表时可能会有缓存,最好能在关闭服务器的情况系进行,以保证数据完整性;
10.可以指定一个MyISAM表最少存储多少条记录,这允许MyISAM表去调整表内部行指针的大小,也可以配置缺省的表内部行指针大小供服务器使用:
1.在创建表时使用MAX_ROWS和MIN_ROWS选项(? CREATE TABLE),这个值只是一个参考值,实际的数据行数可以大于也可以小于这个值;
2.配置系统默认大小使用myisam_data_pointer_size选项:show variables like ‘myisam_data_pointer_size’,这个选项是当创建表时没有使用MAX_ROWS选项时使用,默认为6,不能小于2也不能大于7.它代表可以用几个字节(一个字节是8位)去寻址,指定n,表示2的8n次方,即指定为2,3,4,5,6时依次代表可以存放64K,16M,4G,1T,256T的数据;
3.把max_rows设为5,myisam_data_pointer_size范围是2~7,2个字节就足够了(2^16),所以其实就是让myisam是用2个字节的指针,并不是说最多5行.如果把max_rows设置为大于2^16的值,就要用3个字节表示,所以myisam_data_pointer_size会设为3,此时最多存放2^24行.
4.如果创建表时指定了max_rows,那么表至受限于最大的行数;如果没有max_rows,则表受限于最大大小;
11.导入数据时,可以先禁用掉索引,等到导入数据后再打开索引,这样会加快导入数据的速度.当使用LOAD DATA [LOCAL] INFILE导入数据时,它会自动的禁用和启用索引,以加快导入速度
1.查看LOAD DATA [LOCAL] INFILE的帮助:? load data; load data [local] infile file_name into table table_name;
2.与LOAD DATA INFILE相对应的是SELECT … INTO OUTFILE,把表中的数据导入到文件中;
3.语法:select * from t into outfile ‘/tmp/t.txt’ fields terminated by ‘,’ enclosed by ‘”‘; 字段以[“]包围,字段之间以[,]号分割,默认每行之间以换行分割;
4.语法:load data infile ‘/tmp/t.txt’ into table t fields terminated by ‘,’ enclosed by ‘”‘;
12.向MyISAM表中添加数据时,如果磁盘空间不足时服务器会挂起操作,直到空间变为可用状态,然后继续完成操作;
13.MyISAM表的行存储格式
1.查看表使用哪种行存储格式:与查看表使用引擎的方式一样,表信息的Row_format字段.
1.表中包含有可变长度的列,则表就是Dynamic的;
2.表中没有包含可变长度的列,表就是Fixed的;
2.固定行存储格式:
1.所有的行有固定的大小;
2.行存储的位置是在行长度的整数倍的位置,方便查找;
3.占用更多的存储空间;
3.动态行存储格式:
1.行占用动态的长度;
2.查看起来不是很高效;
3.节省空间;
4.更容易产生碎片;
4.压缩行存储格式:
1.表被压缩以节省空间;
2.优化的存储以加快检索;
3.表是只读的;
5.做实验时可以使用hexdump工具,查看数据文件中实际存放的数据,可以加上-C选项;
6.手动像数据表文件中添加数据后不可用,需要经历check table table_name; optimize table table_name; repair table table_name;三个过程,应该是在information_schema库中写入统计信息;

— MEMORY存储引擎;
1.MEMORY表的.frm文件在数据库目录下,数据和索引都存储在内存中;
2.对MEMORY表的操作性能都很高;
3.在服务器重启之后,MEMORY表中的数据就不存在了,但是他的表结构还是存在的;
4.因为MEMORY表使用的是内存,所以不适用于大表;
5.MEMORY表使用表级锁来处理查询竞争,所以不会发生死锁;
6.MEMORY表不支持TEXT和BLOB类型;
7.它支持两种索引:HASH和BTREE
8.缺省使用HASH索引,这种索引算法使用唯一索引会非常高效,然而HASH索引只能用于比较运算符(=, <>);
1.BTREE索引算法更适合于范围查找,例如>,< 或者between; 2.可以使用创建表时的max_rows和服务器参数max_heap_table_size来限制MEMORY表的大小; 9.设置索引: 1.hash:alter table table_name add index idx_name using hash(col_name); 2.btree:alter table table_name add index idx_name using btree(col_name); 10.当不需要MEMORY表的内容时,要释放被MEMORY表使用的内存,使用DELETE FROM, TRUNCATE TABLE或者删除整个表DROP TABLE; 11.通过指定--max-heap-table-size选项来限制表的最大大小; -- ARCHIVE存储引擎; 1.ARCHIVE引擎被用来以非常小的空间存储大量无索引数据,从而占用更少的资源; 2.要使用此引擎需要在configure时添加--with-archive-storage-engine选项;可以通过show variables like 'have_archive'查看; 3.创建一个ARCHIVE表会有一个保存表结构的.frm文件,保存数据和元数据的.ARZ和.ARM文件,如果有优化操作的话还有一个.ARN文件; 4.ARCHIVE引擎仅仅支持SELECT和INSERT操作,以及除了几何数据类型外的所有数据类型; 5.存储:当inesrt数据时,archive引擎使用zlib无损数据压缩的方式压缩,optimize table可以分析表,并打包为更小的格式; 6.查询:在查询数据时,记录根据需要被加压缩,没有行缓存.SELECT操作执行完全表格扫描,当一个SELECT发生时,它找出当前有多少行可用,并读取行的数量; 7.不支持索引; 8.使用行级别锁定; 9.支持AUTO_INCREMENT列; 10.支持ORDER BY操作和BLOB列; -- BLACKHOLE存储引擎; 1.BLACKHOLE引擎就像黑洞一样,它接收数据但是是丢弃它而不是存储它,查询时总返回NULL; 2.创建BLACKHOLE引擎后会在数据库目录创建一个.frm文件,没有其它文件与之关联; 3.它支持所有的索引; 4.要想使用此引擎在configure时使用--with-blackhole-storage-engine选项; 5.用途: 1.BLACKHOLE表不记录任何数据,如果二进制日志被允许,SQL语句被写入日志,可以用作重复器或者过滤器机制; 2.转储文件语法的验证; 3.来自二进制记录的开销测量,通过比较允许二进制日志功能的BLACKHOLE的性能与禁止二进制功能的BLACKHOLE的性能; 4.因为BLACKHOLE本质是一个no-op存储引擎,可以用来查找与引擎自身不相关的性能瓶颈; -- 课后练习; 补充: -- BDB存储引擎; 1.需要下载包含BDB版本的MYSQL(MySql-Max分发版支持BDB); 2.安装时在configure加入--with-berkeley-db选项; 3.BDB启动选项 --bdb-home:指定BDB表的基础目录,应该和--datadir相同; --bdb-lock-detect:BDB锁定检测方式,DEFAULT,OLDEST,RANDOM,YOUNGEST; --bdb-logdir=path:BDB日志文件目录; --bdb-no-recover:不在恢复模式启动Berkeley DB; --bdb-no-sync:不同步刷新BDB日志,使用--skip-sync-bdb-logs代替; --bdb-shared-data:以多处理模式启动Berkeley DB(初始化Berkeley DB之时,不要使用DB_PRIVATE); --bdb-tmpdir=path:BDB临时文件目录; --skip-bdb:禁止BDB存储引擎; --sync-bdb-logs:同步刷新BDB日志.这个选项默认被允许,请使用--skip-sync-bdb-logs来禁止它; 4.创建一个BDB表会有两个文件,一个是.frm文件,一个是存放表数据和索引的.db文件; 5.支持事务; 6.每一个BDB表都需要一个primary key,如果创建时不指定则会隐式创建一个; 7.SELECT COUNT(*) FROM tbl_name对BDB表很慢,因为在该表中没有行计数被维持; 8.使用页面级别的锁; 9.使用mysql客户端是,应该使用--no-auto-rehash选项; 10.BDB表的限制: 1.每个BDB表在.db文件里存储文件被创建之时到该文件的路径,这个被做来允许在支持symlinks的多用户环境里检测锁定.因此,从一个数据库目录往另一个目录移动BDB表是不能的; 2.当制作BDB表的备份之时,你必须使用mysqldump要么做一个包含对每个BDB表的文件(.frm和.db文件)及BDB日志文件的备份.BDB存储引擎在它的日志文件存储未完成的事务以及要求它们在mysqld启动的时候被呈出来.BDB日志在数据目录里,具有log.XXXXXXXXXX(10位数字)形式名字的文件; 3.如果允许NULL值的列有唯一的索引,只有单个NULL值是被允许的,这不同于其它存储引擎; -- Federated存储引擎; 1.它访问的是在远程数据库表中的数据,而不是本地的表,仅在-MAX版的MySql可用; 2.如果要使用需要在在configure时添加--with-federated-storage-engine选项; 3.创建一个FEDERATED表时,服务器在数据库目录创建一个.frm文件,没有表数据文件,因为实际数据在远程数据库上; 4.操作表的内容时需要MYSQL客户端API,读取数据通过SELECT * FROM table_name来初始化,然后通过mysql_fetch_row()的c函数去一行行读取; 5.创建一个FEDERATED表 1.假设在远程服务器上有一个表为tbl; 2.在本地创建表:create table federated_tbl(id int, name varchar(10)) engine=federated 3.connection='mysql://root@remote_host:3306/federated/tbl'; 4.其他CONNECTION的格式: 1.CONNECTION='mysql://username:password@hostname:port/database/tablename'; 2.CONNECTION='mysql://username@hostname/database/tablename'; 3.CONNECTION='mysql://username:password@hostname/database/tablename'; 6.局限性: 1.远程服务器必须是一个MYSQL服务器; 2.不支持事务; 3.支持增删改查的操作和索引,但是不支持ALTER TABLE和DROP TABLE; 7.用途:可以跨服务器访问,不用创建DB LINK了; -- MRG_MYISAM存储引擎; 1.MGR_MYISAM表是一组MyISAM表的集合,每一个MERGE表在磁盘上都有2个文件,一个是.frm文件和一个包含组成MERGGE表的MyISAM表的名称的.MRG文件.这两个文件都存放在数据库目录下; 2.当对一个MERGE表操作时,相当于对组成MERGE表的所有的MyISAM表的操作; 3.一个MERGE表可以突破MyISAM表的最大大小的限制; 4.MYSQL管理MERGE表的查询竞争使用表级锁,即锁住组成它的MyISAM表,所以不会产生死锁; 5.一个MERGE表是很轻便的,因为.MRG文件是一个文本文件; 6.可以进行增删改查操作,在插入操作时可以指定是往哪个表中插入数据; 7.当MERGE引擎要锁住一个MERGE表时,就会对组成它的所有MyISAM表加锁; 8.对MERGE表执行SEELCT操作时,对底层的表加read lock; 9.对MERGE表执行更新操作(delete, update)时,对底层的表加write lock; 10.对MERGE表的操作: 1.创建MyISAM表m1:create table m1(id int, name varchar(10)) engine=myisam; 2.创建MyISAM表m2:create table m2(id int, name varchar(10)) engine=myisam; 3.创建MERGE表:create table m(id int, name varchar(10)) engine=merge union=(m1, m2); 4.插入数据:insert into m1 values(1, 'a');insert into m1 values(1, 'a'); 5.查询:select * from m; 6.创建MyISAM表m3:create table m3(id int, name varchar(10)) engine=myisam; 7.加入merge表:alter table m union=(m1, m2, m3);很灵活,可以互相组合; 8.修改表使得merge表可以插入数据,create table m(id int, name varchar(10)) engine=mgr_myisam union=(m1, m2) insert_method=last;method_method=0:不允许插入;first:插入到union中的第一个表;last:插入到union中最后一个表; 11.可以通过直接修改.MRG文件来修改MERGE表,修改后使用flush tables;来刷新表缓存; -- CSV存储引擎; 1.CSV引擎使用逗号分隔值格式的文本文件存储数据(eg:["1","aaa"]); 2.要想使用此引擎在configure时使用--with-csv-storage-engine选项; 3.CSV引擎不支持null值,所以在创建时应加上not null选项; 4.CSV引擎不支持索引; 5.创建CSV表会在数据库目录创建一个.frm文件,一个.CSV的文本文件用来存储数据和一个.CSM文件; -- EXAMPLE存储引擎; 1.EXAMPLE引擎是一个不做适合事情的存储引擎,主要用于MySql源码中一个例子用来演示如何开始编写一个新的存储引擎; 2.需要在configure时添加--with-example-storage-engine选项; 3.EXAMPLE引擎不支持编译索引;