Category Archives: MySQL

MySQL OCP-08-事务与锁定

Posted on by
— 事务;
在MySQL中,只有那些使用事务存储引擎(如InnoDB)的表才支持事务;这些语句不会对非事务存储引擎所管理的表产生任何明显影响;
— 事务流程图;
银行转账的流程, 比如由事务来保证;
— ACID;
1.定义:
    1.Atomic(原子性):整个事务事务中的所有的操作要不全都成功,要不全都取消;
    2.Consistent(一致性):在事务开始之前和事务结束以后,数据的完整性约束没有被破坏;
    3.Isolated(隔离性):两个事务的执行是互不干扰的;
    4.Durable(持久性):事务完成以后,该事务对数据库所有的操作便持久的保存在数据库中,不会被回滚;
2.缺点:事务处理能够更有力地保证数据库操作的结果,但同时也需要更多的CPU周期,内存和磁盘空间开销;事务属性对于某些应用程序来说是必不可少的,但并非所有应用程序都如此,您可以选择对您的应用程序最有意义的事务属性;
3.使用场景:
    1.金融类操作通常需要使用事务,保证数据完整性会比增加开销所带来的成本更重要;
    2.另一方面,如果某个应用程序用于记录通过网页访问数据库表的操作,则因服务器主机出现故障而丢失几条记录可能是允许的;
— 事务SQL控制语句;
1.SAVEPOINT:事务中可以回滚到的位置的唯一标识符;
2.ROLLBACK TO SAVEPOINT:回滚之后,savepoint仍然存在,从而可以“重用”它;
3.RELEASE SAVEPOINT:不会删除任何事务语句;
— SQL控制语句流程:示例;
mysql> SET AUTOCOMMIT = ON;
mysql> CREATE TABLE t_trans(id INT, comment VARCHAR(50));
mysql> BEGIN;
mysql> SAVEPOINT sv_bgn;
mysql> INSERT INTO t_trans VALUES(1, ‘first record!’);
mysql> SELECT * FROM t_trans;
mysql> SAVEPOINT sv_ins;
mysql> INSERT INTO t_trans VALUES(2, ‘second record!’);
mysql> UPDATE t_trans SET comment = ‘after update’ WHERE id = 2;
mysql> SAVEPOINT sv_upd;
mysql> SELECT * FROM t_trans;
mysql> ROLLBACK TO sv_ins;
mysql> SELECT * FROM t_trans;
mysql> ROLLBACK TO sv_bgn;
mysql> SELECT * FROM t_trans;
mysql> COMMIT;
TIPS:
1.START TRANSACTION或BEGIN:事务打开,直到通过COMMIT或ROLLBACK显式关闭;
2.事务结束之后/ROLLBACK到保存点之前的保存点都会被自动删除;
— AUTOCOMMIT模式;
1.启用AUTOCOMMIT后,每个语句仍会以原子方式执行(隐式提交);而不是没有事务;
2.可以通过在插入多个行时比较违反约束限制的效果,便可看出启用AUTOCOMMIT和根本不具有事务之间的差别;在非事务表(如MyISAM)中,一旦发生错误,语句就会终止,已经插入的行会保留在该表中;而对于InnoDB表,已经插入的所有行都会从该表中删除,从而不会产生任何实际影响;
    INSERT INTO t VALUES(1),(‘x’);
— 隐式提交;
1.导致隐式提交语句的行为就像在执行实际语句之前发出COMMIT一样;
2.这些语句本身并非事务语句,也就是说,如果成功,则无法回滚;
— 事务存储引擎;
1.使用SHOW ENGINES\G语句可以列出所有已编译到MySQL服务器中的引擎;
2.相关信息:
    1.Engine:引擎名字;
    2.Support:值为YES或NO,用于指示该引擎是否可以使用;如果该值为DISABLED,则表示该引擎存在,但已关闭;值DEFAULT用于指示服务器在默认情况下使用的存储引擎;
    3.Comment:相关的介绍;
    4.Transactions,XA和Savepoints列用于指示该存储引擎是否支持这些功能;
— 事务隔离问题;
1.前提:服务器可能同时具有多个未提交的事务(每个会话最多一个事务),当多个客户机并发访问同一个表的数据时,可能会出现一致性问题;
2.“脏”读:一个事务读取另一个未提交的事务所做的更改;eg:假定事务T1修改了某行,如果事务T2读取该行,并发现修改内容,但T1尚未提交,则会出现“脏”读问题;之所以会成为一个问题,是因为如果T1回滚,所做的更改会被撤消,但T2并不会意识到这一点;
3.不可重复读:指原始数据不可重复读;表示一个事务内的多个相同查询返回了不同的结果集;eg:事务T1内有两次对表t1的查询,但是在查询的间隙事务T2对表做了更新,结果导致第二次查询跟第一次查询的结果不同;(InnoDB使用快照来完成)
4.虚读(幻读):假定事务T1和T2开始,并且在事务T1的一次读取或者更新了某些行,此时如果事务T2插入一个新行,而事务T1读取或者更新操作时发现该行,则会发生虚读问题(新行会成为虚行);
— 隔离级别;
1.如果一个客户机的事务更改了数据,其他客户机的事务是应发现这些更改还是应与其隔离?事务隔离级别可以确定同时进行的事务在访问相同数据时彼此交互的方式;
2.使用存储引擎可实现隔离级别,隔离级别选项在不同的数据库服务器之间是不一样的,因此,InnoDB所实现的级别可能与其他数据库系统所实现的级别并不完全对应;
3.InnoDB可实现四种隔离级别,用于控制事务所做的更改在多大程度上可由其他同时进行的事务注意到:
    1.READ UNCOMMITTED:允许发生“脏”读,不可重复读和虚读;
    2.READ COMMITTED:允许发生不可重复读和虚读;未提交的更改仍不可见;(Oracle默认)
    3.REPEATABLE READ:无论其他事务所做的更改是否已提交,两次都会获得相同的结果;换句话说,也就是不同的事务会对相同的数据产生一致的结果;(InnoDB默认)
    4.SERIALIZABLE:与REPEATABLE READ类似,但其限制性更强,即一个事务所选的行不能由其他事务更改,直到第一个事务完成为止;
4.隔离级别越高,并发性能越差;
— 隔离级别问题;
— 设置隔离级别;
1.在选项文件或者命令行可以设置的参数值为:transaction-isolation = [READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE];
2.对于SET TRANSACTION ISOLATION LEVEL语句,可以设置的参数值为:SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE];
3.此事务级别可以全局设置,也可以按会话设置,如果没有显式指定,则事务隔离级别将按会话进行设置;
4.设置的全局默认事务隔离级别适用于从设置时起所有新建立的客户机连接,现有连接不受影响;
eg:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET @@global.tx_isolation = ‘READ-COMMITTED’;
— 全局隔离级别;
1.客户机始终可以修改其自身会话的事务隔离级别,但要更改全局默认事务隔离级别,则需要SUPER特权;
2.查看当前隔离级别,可使用tx_isolation服务器变量;
    1.查看当前会话:SELECT @@tx_isolation;
    2.查看全局和会话:SELECT @@global.tx_isolation, @@session.tx_isolation;
如果此变量未使用前缀,则会返回会话事务隔离级别。使用 global 和 session 前缀, 可以相应地显式获取全局或会话隔离级别。
也可以使用此服务器变量来设置事务隔离级别。此隔离级别与在SET TRANSACTION ISOLATION LEVEL语法中设置的隔离级别同样有效,其差别在于,它必须以字符串形式 (而不是普通关键字)来表示,并且必须用连字符(短划线或减号)来分隔用于定义隔离 级别的词,而不是使用空格来分隔
— 事务示例:隔离;
mysql> PROMPT s1>
s1> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
s1> SELECT @@global.tx_isolation;
+———————–+
| @@global.tx_isolation |
+———————–+
| READ-COMMITTED        |
+———————–+
mysql> PROMPT s2>
s2> START TRANSACTION;
s2> USE db1;
s2> INSERT INTO t1 VALUES(1);
s1> SELECT * FROM t1;
Empty Set (0.0 sec)
s2> COMMIT;
s1> SELECT * FROM t1;
+——+
| id   |
+——+
|    1 |
+——+
— 锁定概念;
1.锁定机制可以防止因多个客户机同时访问数据而出现的问题;该机制会以某个客户机的身份锁定数据,以限制其他客户机访问该数据,直到释放锁定为止;
2.该锁定允许持有锁的客户机访问数据,而限制与之争用访问权限的其他客户机可以执行的操作,锁定机制的结果是,将对数据的访问序列化,这样,在多个客户机要执行相互冲突的操作时,每个客户机都必须轮流等待;
3.并非所有类型的并发访问都会产生冲突,因此,允许客户机访问数据所需的锁定类型取决于该客户机是希望读取还是希望写入:
    1.如果某个客户机希望读取数据,则希望读取相同数据的其他客户机不会产生冲突,它们可以同时进行读取;但是,如果另一个客户机希望写入(修改)数据,则它必须等待,直到读取完成为止;
    2.如果某个客户机希望写入数据,则所有其他客户机都必须等待,直到写入完成,而无论这些客户机是想读取还是想写入;
    3.通过共享锁和互斥锁来完成;
4.锁定可以禁止并发进行相互冲突的更改并禁止读取正在更改的数据,从而可以防止数据损坏;
— 显式行锁;
1.InnoDB支持两种锁定修饰符,这两种修饰符可以添加到SELECT语句的末尾:
    1.LOCK IN SHARE MODE子句:共享锁,也就是说,虽然任何其他事务都无法获得互斥锁,但其他事务可以同时使用共享锁;由于正常读取不会锁定任何内容,因此它们不会受锁定的影响;
    2.FOR UPDATE子句:使用互斥锁来锁定选定的每一行,以防止其他对象获得这些行上的任何锁,但允许读取这些行;
2.在REPEATABLE READ隔离级别中,可以将LOCK IN SHARE MODE添加到SELECT操作中,这样,如果其他事务想修改选定行,则它们必须等待当前事务完成;这一点与SERIALIZABLE隔离级别的工作方式类似;
3.对于该隔离级别,InnoDB会隐式将LOCK IN SHARE MODE添加到SELECT语句中,而不会包含任何显式锁定修饰符;如果选择了在未提交的事务中修改的行,则会锁定SELECT,直到该事务提交为止;
— 死锁;
1.发生死锁的原因:
    1.事务需要获得多个表上的锁定,但顺序相反;
    2.每个事务因计时问题而仅获取了部分锁定;
2.处理办法:InnoDB会检测并中止(回滚)其中一个事务,并允许另一个事务完成;
    1.如果InnoDB对某个事务执行完整回滚,则该事务所设置的所有锁定都会被释放;
    2.但是,如果因出现错误而仅回滚了一个SQL语句,则该语句所设置的某些锁定可能会保留;(如果SELECT语句在事务中调用一个存储函数,而该函数中的一个语句出现错误,则该语句将回滚;同时,如果此后执行ROLLBACK,则整个事务将回滚)
    3.发生此问题的原因是,InnoDB存储行锁的格式使它此后无法识别锁和语句之间的对应关系;
— 事务示例:死锁;
s1> CREATE TABLE t_dl1(id INT);
s1> CREATE TABLE t_dl2(id INT);
s1> INSERT INTO t_dl1 VALUES(1);
s1> INSERT INTO t_dl2 VALUES(1);
s1> COMMIT;
mysql> PROMPT s1>
s1> START TRANSACTION;
s1> UPDATE t_dl1 SET id = 1 WHERE id = 1;
mysql> PROMPT s2>
s1> START TRANSACTION;
s1> UPDATE t_dl2 SET id = 1 WHERE id = 1;
s1> UPDATE t_dl2 SET id = 1 WHERE id = 1;
s2> UPDATE t_dl1 SET id = 1 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
— 隐式锁;
1.InnoDB表会使用行级别锁定,以满足最高可能的并发;
2.对于InnoDB存储引擎,请避免使用LOCK TABLES语句;它不会提供任何额外的保护,却会减少并发性;利用自动行级别锁定,需对表进行锁定和解锁;
— 课后练习;

MySQL OCP-07-获取元数据

Posted on by
— 元数据访问方法;
1.什么是元数据:数据库是数据的结构化集合,元数据是“有关数据的数据”;
2.MySQL通过以访问元数据的方法:
    1.INFORMATION_SCHEMA:MySQL服务器包含一个被实现为名为INFORMATION_SCHEMA的数据库(模式)的数据字典,其中包含许多显示为表的对象;
    2.SHOW语句:用于获取服务器统计信息,模式和模式对象的相关数据的专用语法;
        1.SHOW DATABASES和SHOW TABLES:返回包含数据库和表名的列表;
        2.SHOW COLUMNS:生成表中列的定义;SHOW COLUMNS FROM table等于DESC table;
        3.需要有SELECT特权才能使用SHOW语句;
    3.DESCRIBE:可用于检查表结构和列属性的SQL语句快捷方式;
    4.mysqlshow:用作指向一些SHOW语句的命令行前端的客户机程序;
— INFORMATION_SCHEMA数据库;
— INFORMATION_SCHEMA表;
1.表信息
    • COLUMNS:表和视图中的列
    • ENGINES:存储引擎
    • SCHEMATA:数据库
    • TABLES:数据库中的表
    • VIEWS:数据库中的视图
2.分区
    • PARTITIONS:表分区
    • FILES:存储 MySQL NDB 磁盘数据表的文件
3.特权
    • COLUMN_PRIVILEGES:MySQL 用户帐户所拥有的列特权
    • SCHEMA_PRIVILEGES:MySQL 用户帐户所拥有的数据库特权
    • TABLE_PRIVILEGES:MySQL 用户帐户所拥有的表特权
    • USER_PRIVILEGES:MySQL 用户帐户所拥有的全局特权
4.字符集支持
    • CHARACTER_SETS:可用的字符集
    • COLLATIONS:每个字符集的排序
    • COLLATION_CHARACTER_SET_APPLICABILITY:适用于特定字符集的排序
5.约束和索引
    • KEY_COLUMN_USAGE:关键列的约束
    • REFERENTIAL_CONSTRAINTS:外键
    • STATISTICS:表索引
    • TABLE_CONSTRAINTS:表的约束
6.服务器设置和状态
    • KEY_COLUMN_USAGE:约束
    • GLOBAL_STATUS:所有 MySQL 连接的状态值
    • GLOBAL_VARIABLES:用于新的 MySQL 连接的值
    • PLUGINS:服务器插件
    • PROCESSLIST:指示哪些线程正在运行
    • SESSION_STATUS:当前 MySQL 连接的状态值
    • SESSION_VARIABLES:当前 MySQL 连接的生效值
7.例程及相关信息
    • EVENTS:预定事件
    • ROUTINES:存储过程和功能
    • TRIGGERS:数据库中的触发器
    • PARAMETERS:存储过程和功能参数以及存储函数
8.InnoDB
    • INNODB_CMP 和 INNODB_CMP_RESET:对压缩的 InnoDB 表的相关操作的状态
    • INNODB_CMPMEM 和 INNODB_CMPMEM_RESET:InnoDB 缓冲池中压缩页面的状态
    • INNODB_LOCKS:InnoDB 事务所请求和持有的每个锁
    • INNODB_LOCK_WAITS:每个阻塞的 InnoDB 事务的一个或多个行锁
    • INNODB_TRX:当前正在 InnoDB 内部执行的所有事务
    • TABLESPACES:活动的表空间
— INFORMATION_SCHEMA表列;
— 对INFORMATION_SCHEMA使用SELECT;
— INFORMATION_SCHEMA示例;
1.显示用于给定数据库中表的存储引擎;
2.查找所有包含SET列的表;
3.显示每个字符集的默认排序规则;
4.显示每个数据库中表的编号;
5.INFORMATION_SCHEMA表是只读的,无法用INSERT/DELETE/UPDATE之类的语句进行修改;如果执行这些类型的语句以尝试更改INFORMATION_SCHEMA表中的数据,服务器将生成错误;
— 使用INFORMATION_SCHEMA表创建Shell命令;
1.本幻灯片中的示例所示,SQL语句将生成一条输出,仅导出world_innodb数据库中那些以单词“Country”开始的的表;
2.输出将生成可以在shell命令行上正确执行的shell脚本;下一步是将此输出存储在一个可
在shell命令行中执行的批处理文件中,这通过添加子句INTO OUTFILE来完成:
    SELECT CONCAT(“mysqldump -uroot -pmysql “, TABLE_SCHEMA, ” “, TABLE_NAME, ” >> “,TABLE_SCHEMA, “.sql”)
    FROM TABLES WHERE TABLE_NAME LIKE ‘Country%’
    INTO OUTFILE ‘\tmp\Country_Dump.sh’;
3.然后可以在命令行中执行此文件,命令行将运行本幻灯片中所示的两个mysqldump命令:
    shell> \tmp\Country_Dump.sh
    shell> \tmp\mysqldump -uroot -pmysql world_innodb Country >> world_innodb.sql
    shell> \tmp\mysqldump -uroot -pmysql world_innodb Country_Language >> world_innodb.sql
— 使用INFORMATION_SCHEMA表创建SQL语句;
1.本幻灯片中的示例使用mysql命令执行了一个语句,以制作world_innodb数据库中所有表的精确副本;
    1.–silent命令在输出中删除列标题;
    2.–skip-column-names命令删除输出中的格式(使输出类似于表的格式);
    3.这两个命令用来确保对命令自身的解释是正确的,没有任何干扰执行的外部格式或标题行问题;
2.添加管道符号[|]并随之执行mysql命令会将这些SQL语句发送到MySQL服务器以便执行:
shell> mysql -uroot -pmysql –silent –skip-column-names -e “SELECT CONCAT(‘CREATE TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘_backup LIKE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘world_innodb’;” | mysql -uroot -pmysql
— MySQL支持的 SHOW 语句;
1.除了INFORMATION_SCHEMA表之外,MySQL还支持SHOW和DESCRIBE语句,作为访问元数据的备选方式;
2.SHOW和DESCRIBE语法不如使用INFORMATION_SCHEMA查询灵活,但是对于大多数用途,SHOW和DESCRIBE语法就足够了;在这些情况下,使用MySQL特定语法通常会更快速,简单;
3.可以通过多种形式使用SHOW语句,如下所示:
    • SHOW DATABASES:列出可用数据库的名称
    • SHOW TABLES:列出默认数据库中的表
    • SHOW TABLES FROM <database_name>:列出指定数据库中的表
    • SHOW COLUMNS FROM <table_name>:显示表的列结构
    • SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息
    • SHOW CHARACTER SET:显示可用的字符集及其默认排序
    • SHOW COLLATION:显示每个字符集的排序
— SHOW语句示例;
mysql> SHOW DATABASES;
mysql> SHOW TABLES;
mysql> SHOW TABLES FROM mysql;
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
mysql> SHOW COLUMNS FROM CountryLanguage;
mysql> SHOW FULL COLUMNS FROM CountryLanguage\G
— 其他SHOW语句示例;
— DESCRIBE语句;
1.DESC table_name等效于SHOW COLUMNS FROM table_name;但是,SHOW COLUMNS支持可选的LIKE和WHERE子句,而DESCRIBE不支持;
2.当指定表名称作为参数时,EXPLAIN等效于DESCRIBE:mysql> EXPLAIN table_name;
— mysqlshow客户机;
1.mysqlshow客户机为各种格式的SHOW语句提供了一个命令行界面,这些语句用于列出数据库的名称,数据库中的表或有关表列或索引的信息;
2.mysqlshow客户机的选项部分可包含任一标准连接参数选项,例如–host或–user;如果默认连接参数不适合,则必须提供选项;mysqlshow也接受特定于其自身运行的选项;
3.使用–help选项调用mysqlshow可查看其选项的完整列表;
4.mysqlshow所执行的操作取决于已提供的非选项参数的数量;
— mysqlshow示例;
1.在没有参数的情况下,mysqlshow将显示类似于SHOW DATABASES的结果;
2.在使用单个参数的情况下,mysqlshow将该参数解释为数据库名称,并针对该数据库显示类似于SHOW TABLES的结果;
3.在有两个参数的情况下,mysqlshow将参数解释为数据库和表名称,并针对该表显示类似于SHOW FULL COLUMNS的结果;
4.在有三个参数的情况下,其输出与两个参数的情况相同,不同之处在于:mysqlshow将第三个参数当做列名称,且仅针对该列显示SHOW FULL COLUMNS输出;
5.如果命令行中最后的参数包含特殊字符,mysqlshow会将该参数解释为模式,且仅显示与该模式匹配的名称;特殊字符包括:%或*(匹配任一字符序列),以及_或?(匹配任一单个字符);本示例中的命令仅显示那些名称始于w的数据库;
TIPS:这些示例要求在执行命令时使用用户和口令作为参数;
— 课后练习;

MySQL OCP-06-数据类型

Posted on by
— 数据类型:概览;
1.在MySQL中,可用的数据类型分为四个主要类别:
    1.数值;
    2.字符;
    3.二进制;
    4.时间;
2.在每个类别中,存在多种特定的数据类型,这些数据类型使用的内存大小和磁盘空间各不相同,因此会对性能产生不同的影响;
3.对于单个记录,为列选择最佳数据类型所产生的性能影响相对较小,但随着数据库的增大,这些较小的影响可能会汇聚成大的影响;应在设计过程中事先考虑这些影响,以免造成性能问题;
4.MySQL还支持“空间扩展”存储引擎功能;通过空间扩展,能够以字符和二进制格式生成,存储和分析地理特征;
5.数据类型的ABC要素:
    1.Appropriate(适当):需要以最适合数据所代表的项的类型来表示数据;
    2.Brief(简洁):选择所用存储空间最少的数据类型。这可节省资源并提高性能;
    3.Complete(完整):选择的数据类型应分配有可存储特定项的最大可能值的充足空间;
— 创建带有数据类型的表;
1.示例1:第一个示例创建了一个名为people的表,该表包含一个名为id的整数值数字列和两个名称分别为first_name和last_name 的30个字符的字符串列;
2.示例2:第二个示例说明如何通过添加UNSIGNED属性来禁止在id列中使用负值;
— 数值数据类型;
1.数值数据类型类:
    1.整数:整数没有小数部分;即,没有小数位的单个整数值;
    2.浮点数(FLOAT/DOUBLE):表示包含整数部分,小数部分或同时包括二者的近似值数值;此类数据类型使用服务器主机的CPU所用的本机二进制浮点格式(IEEE 754)来表示值;该数据类型用于存储和计算会很高效,但值会存在舍入误差;如果列可为空,则默认值为NULL;如果列不可为空,则默认值为0(数值零);
    3.定点数(DECIMAL):包含整数部分,小数部分或同时包括二者;DECIMAL列中的所有值均包含相同的小数位数,并且完全按给定方式进行存储;DECIMAL值存储起来不如浮点数值高效,但DECIMAL值没有舍入误差,因此更加精确;通常用于存储货币值(其中每个值的精度比其存储大小更加重要);
    4.BIT:BIT列规范规定了一个宽度,指明每个值的位数(1至64位);
2.“精度”和“范围”是适用于浮点值和定点值(这两种类型可以同时包含整数部分和小数部分)的术语:
    1.精度:有效位数;
    2.范围:小数点右侧的位数;
TIPS:可以使用=号跟浮点值进行比较;但由于可能会出现舍入误差,因此结果可能并不总是与预期一样;
— 字符串数据类型;
1.这些类型在以下几个方面有所不同:
    1.数据是以固定长度格式还是可变长度格式存储;
    2.可存储的最大长度;
    3.该类型是否支持非结构化字符串值;
2.为存储字符串数据,MySQL提供了以下数值数据类型存储类:
    1.文本:用于表示真实的字符串数据类型;可以使用此类型存储非结构化且格式自由的字符串(如果其长度符合已定义的空间量);
    2.整数(枚举/集合):用于表示结构化字符串类型;称为“结构化”的原因是,存储在这些类型的列中的值必须通过您所提供的值列表构建,从而定义数据类型;
— 字符集和排序支持;
1.字符串具有以下特征:
    1.序列由特定字符集中的字符组成;
    2.多字节字符集中的每个字符所需的字节数可能是固定的,也可能是可变的;
    3.比较基于对字符串所关联的字符集的排序;
    4.多字节字符比较以字符而不是字节为单位执行;
    5.排序将验证字符的大写版本和小写版本是否等效;
    6.排序将决定同一个字符的不同重音标记是否等效;
    7.排序可以为二进制,其中基于数值字符值进行比较;
2.MySQL具有一个包含大量可供选择的字符集和排序的列表;选择正确与否会对性能有很大的影响;要查看可用的字符集:SHOW CHARACTER SET;
3.使用排序选择可以为相同的字符集选择不同的排序顺序;例如,显示所有latin1字符集:SHOW COLLATION LIKE ‘latin1%’;
— 二进制字符串数据类型;
1.与字符串不同,组成此类二进制字符串值的字节不代表字符;因此,二进制字符串没有附加的字符语义,而且缺少字符串类所表示的字符集和整理信息;
2.在MySQL中,BLOB与TEXT类型非常相似,没有附加的字符集和排序;
— 时间数据类型;
1.YYYY,MM,DD,hh,mm,ss和uuuuuu分别表示“年”,“月”,“日”,“小时”,“分钟”,“秒”和可选的“秒的小数位”;
2.通过为该类型提供一个参数,可声明秒的小数位(可选);例如,TIME(3)是TIME类型,其中秒的小数位部分最多可达三位;
3.TIMESTAMP值采用UTC进行存储(将根据需要转换为当地时间或从当地时间进行转换),其范围为从1970-01-01 00:00:00.000000至2038-01-19 03:14:07.999999;
4.可以定义DATETIME和TIMESTAMP以自动记录当前日期和时间,而不管对行执行INSERT或UPDATE操作的时间为何时;
5.TIMESTAMP与DATETIME的对比:
    1.TIMESTAMP列值的范围比DATETIME列值的范围小,因此存储每个值所需的字节数更少;
    2.通过为不允许出现NULL的TIMESTAMP指定NULL值,可以将其设置为当前日期和时间;
例子:
CREATE TABLE t_time
(
    id INT NOT NULL,
    t1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    t2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t3 TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
);
mysql> INSERT INTO t_time(id) VALUES(1);
mysql> COMMIT;
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1                  | t2                  | t3                  |
+—-+———————+———————+———————+
|  1 | 2015-08-19 14:00:42 | 2015-08-19 14:00:42 | 0000-00-00 00:00:00 |
+—-+———————+———————+———————+
mysql> UPDATE t_time SET t1 = ‘2000-01-01 00:00:00’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1                  | t2                  | t3                  |
+—-+———————+———————+———————+
|  1 | 2000-01-01 00:00:00 | 2015-08-19 14:00:42 | 2015-08-19 14:01:43 |
+—-+———————+———————+———————+
1 row in set (0.00 sec)
— 空间数据类型;
MySQL实现了由开放地理空间协会(Open Geospatial Consortium, OGC)提出的“包含几何类型的SQL”环境的子集:
1.用于单个几何体值:
    1.GEOMETRY:层次结构的根类,任何类型的值;
    2.POINT:坐标空间中的单个位置;
    3.CURVE:一维几何体,由点组成的序列;
    4.LINESTRING:点之间具有线性内插的曲线;
    5.SURFACE:二维几何体;
    6.POLYGON:表示多边几何体的平面;
2.用于保存几何体值的集合:
    1.MULTIPOINT:Point元素;
    2.MULTICURVE:Curve元素;
    3.MULTILINESTRING:LineString元素;
    4.MULTISURFACE:Surface元素;
    5.MULTIPOLYGON:Polygon元素;
    6.GEOMETRYCOLLECTION:任意类型的几何体;
— 将数据类型设置为NULL;
1.在SQL中,对表达式求值的结果可以为null;null值是一个特殊的值,表示值无法进行计算或未知;
2.何时使用NULL:在数据库设计的开始阶段,当列出要包含在内的数据时,发现某些数据可能不适用于所有列;
3.何时不应使用NULL:在某些情况下,不应允许列中使用null值;
    1.最常见的情况即该列为主键时;
    2.另一个例子是:当列必须包含值时,数据库设计才有意义;
    3.NULL值不保存到索引中;
— 创建带有列属性的表;
本幻灯片中所显示的示例中的表包含一个不能包含NULL值的UNSIGNED整数列;一个使用utf8字符集的字符串列;以及一个默认值为“2013-01-01”的日期列;
— 列属性;
— 选择数据类型;
— 课后练习;

MySQL OCP-05-客户机和工具

Posted on by
— 命令行客户机程序;
1.这几个客户机都必须运行mysqld服务器程序,客户机才能访问数据库;
2.mysql是通用的命令行客户机,用于向服务器发送SQL语句,其中包括用于管理的SQL语句;
3.mysqladmin是可帮助管理服务器的管理命令行客户机;
4.mysqlimport为LOAD DATA INFILE语句提供了命令行界面;使用该客户机可以将数据文件装入表中,而无需手动发出LOAD DATA INFILE语句;之后讲导入导出数据时会具体再讲;
5.mysqldump是用于转储数据库和表中内容的命令行客户机;使用它可备份数据库或将其复制到其他计算机;
— 调用命令行客户机;
两种常见的选项语法格式:
1.长选项(–<option>):双横线选项后面加等号和参数;
2.短选项(-<option>):单横线选项后加空格和参数; mysql -V;
— 连接参数选项;
1.-h:后跟给定主机的主机名或IP地址,用于连接到服务器(默认为localhost);
2.-C:压缩客户机和服务器之间发送的所有信息(如果两者都支持压缩);
3.–protocol:后跟用于连接到服务器的连接协议:{TCP|SOCKET|PIPE|MEMORY};
4.-P:后跟端口号,用于代替默认值(3306);
5.-S:用于设置UNIX套接字文件或在Windows上使用的命名管道的名称;
6.–shared-memory-base-name:(仅Windows)通过共享内存连接到本地服务器时所使用的共享内存的名称;此选项仅在服务器支持共享内存连接时适用;
eg:mysql -h localhost -P 3306 -u root -p –compress –protocol=TCP -S /var/lib/mysql/mysql.sock
— 测验;
b
— 调用mysql客户机;
1.在命令行中提供凭证:
-u<name>选项后面可带或不带空格;-p<password>选项后面不带空格,如果对该选项使用空值,则系统会提示您输入口令;
2.在登录路径中提供凭证:使用此登录路径(通过mysql_config_editor创建)的凭证;
3.执行语句:mysql –login-path=admin -e “SELECT VERSION()”;
4.使用特定选项文件执行:
5.使用包含SQL语句的文本文件执行:
    1.可以使用[ > | < ]重定向流,用来运行脚本或者批处理文件;
    2.文件必须为纯文本格式,其中每个语句都有语句终结符;
    3.文件必须位于运行 mysql 客户机的主机上;
    4.<和-e是互斥的,不能同时使用;
— mysql 客户机:安全更新;
1.可能会不小心发出一些会修改表中多个行的语句或者会返回特别大的结果集的语句,使用–safe-updates选项可帮助防止这些问题;(可以启动时设置,也可以在选项文件中设置)
2.设置安全更新模式可施加以下SQL语句限制:
    1.UPDATE和DELETE仅在包含WHERE子句(该子句通过键值明确标识了要更新或删除的记录)或LIMIT子句时才允许使用;
    2.将单表SELECT语句中的输出限制为不超过1K行,但语句包含LIMIT子句时除外;
    3.仅当MySQL为处理查询所检查的行不超过100W时,才允许使用多表SELECT语句;
例子:
# mysql
mysql> use db1;
mysql> SELECT * FROM t1;
mysql> UPDATE t1 SET id = id + 1;
mysql> COMMIT;
# mysql -uroot -p –safe-update
mysql> use db1;
mysql> UPDATE t1 SET id = id + 1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
— mysql 客户机:输出格式;
默认情况下,无论是以交互模式还是以批处理模式使用mysql,都会生成输出:
1.交互式:当以交互模式调用mysql时,会以表格格式显示查询输出,其中使用长条和短划线显示在方框列中列出的值;
    1.–table(或 -t):生成表格输出格式,即使在以批处理模式运行时也是如此,这是交互模式的默认格式;
2.批处理:在通过使用文件作为命令行中的输入源来调用mysql时,mysql会以批处理模式运行,并且在显示的查询输出中使用制表符来分隔数据值;
    1.–batch(或 -B):生成批处理模式(用制表符分隔的)输出(即使在以交互模式运行时也是如此),且不使用历史文件,这是批处理模式的默认格式;
    2.在批处理模式下,使用–raw或-r选项可禁止字符转换(例如,将换行符和回车符转换为\n或\r等转义序列),在原始模式下,将按字面值输出字符;
3.使用以下选项可选择不同于以上任一默认格式的输出格式:
    1.–html(或 -H):生成 HTML 格式的输出;
    2.–xml(或 -X):生成 XML 格式的输出;
— mysql 客户机:MySQL客户机命令;
1.列出所有的 MySQL 客户机级别命令:mysql> HELP;
    1.?与help一样,获得帮助信息;
    2.clear:清除当前输入的语句,在错误的语句后面输入\c即可;
    3.connect:重新连接服务器,可以指定主机名和服务器,输入connect dbname host或或者\r dbname host即可;
    4.delimiter:语句分隔符,设置执行语句的符号,执行delimiter $$即可;
    5.edit:调用vi修改sql语句,在要修改的语句后面输入\e即可;
    6.go:发送命令到mysql服务器;
    7.pager:查询内容太多,分页显示,设置:>pager less,禁止:>nopager;
    8.tee:把查询结果输入到一个文件中:>tee /tmp/rst.txt,禁止notee,主要用于数据库的备份脚本输出;
    9.prompt:改变提示符,如:prompt royalwzy>;则每次输入命令是提示符变为royalwzy>字符串(可以在配置文件中定义,prompt=xxx);
    10.quit/exit:都是退出客户端,输入quit,exit和\q都行;
    11.source:执行一个sql脚本文件,文件名为参数,source /tmp/sql.txt;
    12.system:执行操作系统的命令,用法:system ls -l /tmp 或者\! ls -l /tmp;
    13.status:查看服务器信息的状态,输入status或者\s即可;
    14.use:改变使用的数据库,后面跟数据库的名称,eg:use mysql;
    15.charset:修改字符集,可以通过status查看当前使用的字符集,eg:charset latin1;
    16.warnings:开启警告信息,当输入的sql语句出错时,可以通过show warning或者show errors来打印警告或者错误信息,设置:warning或者\W,关闭:nowarning或者\w;
    17.rehash:设置客户端自动补全功能;
        1.在服务器的配置文件中[mysql]节点下,默认使用no-auto-rehash选项;
        2.注释no-auto-rehash选项,添加auto-rehash选项,reboot;
2.显示会话状态信息:mysql> \s;
    Using outfile:就是使用tee命令指定的值,把当前所有的操作和输出都重定向到一个文本;
3.日志会话查询及其输出:tee my_tee_file.txt;
— mysql 客户机:SQL语句;
1.数据定义语言(Data Definition Language, DDL)
    1.CREATE DATABASE/TABLE:用于创建具有给定名称的数据库或表;
    2.ALTER DATABASE/TABLE:可更改数据库或表的整体特性;
    3.DROP DATABASE/TABLE:用于删除数据库中的所有表并删除该数据库,或者用于删除特定的表;
2.数据操纵语言(Data Manipulation Language, DML)
    1.SELECT:用于从一个或多个表中检索所选的行;
    2.INSERT:用于在现有表中插入新行;
    3.DELETE:用于删除现有表中的行;
    4.UPDATE:用于使用新值更新指定表中现有行的列;
    5.JOIN:组合使用多个表以用于SELECT,多表DELETE和UPDATE语句;
— mysql 客户机:有关SQL语句的帮助;
获得服务器端帮助:>help contents;
1.可以通过help cmd获得更详细的信息;
2.获得管理操作的命令:>help administration;
3.获得数据类型:>help data types;
4.获得show命令的帮助:help show; ? show;
5.show variables:打印系统变量,类似oracle中的show parameter;
    1.查询包含某一关键字的变量:show variables like ‘%buffer%’;
    2.设置变量的值用set命令:set global|session key=value;
6.show variables和show status的区别
    1.show variables:当服务器运行之后如果没有人工干预所有的参数不会发生改变;
    2.show status:显示服务器运行过程中的动态信息,值会动态改变;
7.不必逐步浏览目录列表中所列出的项来获取有关特定主题的帮助,只需给出主题作为关键字即可获得一些提示:HELP STATUS;
— mysql 客户机:SQL语句终结符;
1.[;]和[\g]:常见的终结符,二者等效,可互换使用;
2.\G:用于终止查询并以垂直方式显示查询结果,其中显示的每个输出行的每个列值均位于单独的行中;此终结符在查询生成的输出行非常宽的情况下十分有用(因为竖直格式可使结果更易阅读);
3.\c:如果决定放弃正在编写的语句,则可取消该语句并返回到新的mysql>提示符下;
— mysql 客户机:特殊语句终结符;
— mysql 客户机:重新定义提示符;
1.可以更改默认提示符,将当前信息放入提示符中,例如用户(\u),主机(\h)和数据库(\d);
2.PROMPT关键字之后第一个空格后面的所有内容都将成为提示符字符串的一部分,包括其他空格;该字符串可包含特殊序列;
3.要将提示符恢复为默认值,请指定不包含参数的PROMPT或\R;
eg:prompt (\u@\h)[\d]\>
— mysql 客户机:使用脚本文件;
1.脚本文件应该为纯文本文件,其中所包含语句的格式要与以交互模式输入的语句的格式相同;具体来说,每个语句都必须以 终结符结束;
2.SOURCE命令后的文件名无需用引号括起;
— mysqladmin客户机;
查看帮助:
1.create databasename:创建数据库;
2.debug:把bug信息写入日志文件;
3.drop databasename:删除数据库;
4.extended-status:列出服务器的附加信息;
5.flush-hosts:刷新主机缓存;
6.flush-logs:刷新所有的日志;
7.flush-status:清空状态变量;
8.flush-tables:刷新表;
9.flush-threads:刷新线程缓存;
10.flush-privileges:重新加载授权表,相当于reload;
11.kill id,id,…:杀掉mysql的进程;
12.password [new-password]:以当前格式修改密码;
13.old-password [new-password]:以旧的格式修改密码;
14.ping:检查mysqld是否活动;
15.processlist:列出所有活动的进程;
16.reload:重新加载授权表;
17.refresh:刷新所有的表,并重新打开日志文件;
18.shutdown:关闭Server;
19.status:查看服务器的状态信息;
20.start-slave:启动slave;
21.stop-slave:停止slave;
22.variables:打印变量状态;
23.version:查看服务器版本信息;
— 调用mysqladmin客户机;
— 测验;
b
— MySQL工具;
1.MySQL Workbench:一个可视化的下一代数据库设计应用程序,可用于高效设计,管理和记录数据库结构;它有开源和商业两种版本;
2.MySQL Proxy:一个位于客户机与MySQL服务器之间的简单程序,可监视,分析或传输客户机与服务器之间的通信;MySQL Proxy 的灵活性使其具有多种用途,包括负载平衡/故障转移/查询分析/查询过滤和修改以及其他操作;该工具当前尚不可用于生产;
3.MySQL Enterprise Monitor:MySQL的可视化企业监视系统,可用于检测MySQL服务器,通知潜在问题并就如何修复这些问题提供建议;
4.MySQL Enterprise Backup:使用该工具可以执行联机非阻塞“热”备份,从完整备份恢复数据,还支持创建压缩的备份文件;
5.MySQL Cluster Manager:通过自动执行常见的管理任务来简化MySQL Cluster Carrier Grade Edition数据库的创建和管理;
TIPS:345工具仅在MySQL商业版本中提供;
— MySQL Enterprise Monitor;
1.Enterprise Monitor可帮助管理多节点(可水平伸缩)环境中的多台MySQL服务器,调整当前MySQL服务器,发现MySQL数据库应用程序中存在的问题,并在这些问题变成严重问题或代价高昂的故障之前将其修复;
2.此Web GUI应用程序可主动监视企业数据库环境,并就MySQL如何增强MySQL所推动系统的安全性,优化其性能和减少其停机 时间提供专家建议;
3.Enterprise Monitor可监视各种类型的配置,从单个MySQL服务器直到支持繁忙Web站点的大型MySQL服务器群;
— MySQL Enterprise Monitor:系统信息显示板;
— MySQL Enterprise Monitor:访问;
— MySQL Workbench;
MySQL Workbench针对三种主要功能为DBA和开发者提供了基于GUI的跨平台集成工具环境:
1.SQL开发有助于进行以下任务:
    1.编辑和执行SQL查询和脚本;
    2.创建或变更数据库对象;
    3.编辑表数据;
2.数据库设计和建模有助于进行以下任务:
    1.执行增强型实体关系 (enhanced entity relationship, EER)建模;
    2.编辑和执行SQL查询和脚本;
    3.设计,生成和管理数据库;
3.服务器管理(取代了MySQL Administrator)有助于进行以下任务:
    1.启动和停止服务器;
    2.编辑数据库服务器配置;
    3.管理用户;
    4.导入和导出数据;
— MySQL Workbench:GUI窗口;
— MySQL Workbench:访问;
标准版(SE)提供了:
1.OSS版本的商业扩展;
2.高级功能;
3.需要付费;
— MySQL Proxy;
1.MySQL Proxy使用MySQL网络协议连接到网络,并提供一台或多台服务器与一台或多台MySQL客户机之间的通信;
在最基本的MySQL Proxy配置中,可以执行以下操作:
    1.仅将查询从客户机传递到MySQL服务器,然后返回;
    2.对使用该协议的任何与MySQL兼容的客户机,可不经修改地使用MySQL Proxy;这包括mysql命令行客户机,任何使用MySQL客户机库的客户机以及任何支持MySQL网络协议的连接器;
    3.监视和变更客户机和服务器之间的通信;
    4.使用查询拦截添加分析,插入其他查询并删除其他结果;交换信息的拦截可脚本化;
2.使用该代理可以对查询执行额外的监视,过滤或处理,而无需对客户机做出任何修改,客户机甚至不会知道正在与其通信的根本不是真正的MySQL服务器;
3.MySQL Proxy仍在开发中,尚未作为GA软件发行;可以了解OneProxy;
— MySQL连接器;
— 第三方API;
1.大多数第三方API均基于C客户机库,并针对其他某种语言提供绑定;
2.虽然MySQL开发团队的成员经常与这些产品的开发者密切合作,但是这些API尚未得到Oracle的正式支持;
— 测验;
c
— 课后练习;

MySQL OCP-04-服务器配置

Posted on by
— MySQL配置选项;
1.预编译的选项:
    1.在生成RPM包时指定的选项;
    2.在源码安装时指定的选项;
2.命令行选项:
    1.可以在启动服务器(mysqld)时在命令行上指定启动选项;
    2.默认情况下,服务器在运行时使用其配置变量的预编译值;但是,如果默认值不适合环境,则可添加运行时选项,让服务器使用其他值来执行以下操作:
        1.指定重要的目录和文件的位置;
        2.控制服务器写入的日志文件;
        3.覆盖服务器与性能相关的变量的内置值(即,控制最大同时连接数以及缓冲区和高速缓存的大小);
        4.在服务器启动时启用或禁用预编译的存储引擎通过使用命令行选项或选项文件,或者使用两者的组合,可以指定服务器启动时的运行时选项(以更改其配置和行为);
    3.命令行选项优先于选项文件中的任何设置
3.配置文件选项:在配置文件my.cnf中指定的启动选项;最常用的方式;
4.查看相关帮助mysqld –verbose –help;
— 使用选项文件的原因;
1.将选项放在文件中后,不需要每次启动服务器时都在命令行上指定选项;对于复杂的选项(如用于配置InnoDB表空间的选项),这样做更加方便,并且更不容易出错;
2.如果所有服务器选项都在一个选项文件中,则可概览服务器的配置情况;
3.MySQL程序可以访问多个选项文件中的选项,要创建或修改某个选项文件,必须拥有该文件的写入权限;客户机程序仅需要读取访问权限;
— 选项文件组;
1.选项文件中的选项按组进行组织,每个组前面有一个为组命名的[group-name]行,通常,组名称是选项组适用的程序的类别或名称;
选项组示例包括:
2.[client]:用于指定适用于所有客户机程序的选项;[client]组的一个常见用途是指定连接参数,因为在一般情况下,不管使用什么客户机程序,都要建立到同一个服务器的连接;
3.[mysql]和[mysqldump]:分别用于指定适用于mysql和mysqldump客户机的选项;此外,也可以单独指定其他客户机选项;
4.[server]:用于指定同时适用于mysqld和mysqld_safe服务器程序的选项;
5.[mysqld],[mysqld-5.6],[mysqld56]和[mysqld_safe]:用于指定适用于不同服务器版本或启动方法的选项;
— 编写选项文件;
1.要创建或修改某个选项文件,最终用户必须拥有该文件的写入权限;服务器本身仅需要读取访问权限;服务器读取选项文件,但不创建或修改选项文件;
2.如何在选项文件中写入一个选项:
    1.使用长选项格式(像命令行上使用的那样),但省略前导短划线;
    2.如果某个选项取值,则允许在等号两则加空格( = );此规则不适用于在命令行上指定的选项,eg:指定默认选项文件;
3.在幻灯片上的示例中,请注意以下方面:
    1.[client]:此组中的选项适用于所有标准客户机;
        1.host:指定服务器主机名;
        2.compress:指示客户机/服务器协议对通过网络发送的通信使用压缩;
    2.[mysql]:此组中的选项仅适用于mysql客户机;
        1.show-warnings:指示MySQL在每条语句后显示任何当前警告;
    3.mysql客户机同时使用[client]和[mysql]组中的选项,因此将使用显示的全部三个选项;
— 选项文件位置;
1.Linux:
    1./etc/my.cnf;
    2./etc/mysql/my.cnf;
    3./usr/local/mysql/etc/my.cnf;
    4.~/.my.cnf;
    5.如果设置了MYSQL_HOME环境变量,则将搜索$MYSQL_HOME/my.cnf文件;
2.Windows:
    1.C:\目录下的my.ini和my.cnf;
    2.C:\Windows(或C:\WinNT)目录;
    3.C:\Program Files\MySQL\MySQL Server <version number>目录;
3.MySQL命令行程序会在MySQL安装目录中搜索选项文件;
— 选项文件中的启动选项;
要在选项文件中指定服务器选项,需要在[mysqld]或[server]组下指示特定选项;
1.日志记录:可以通过启用所需日志的类型为服务器启用日志记录;比如:
    1.general_log # 常规查询日志;
    2.log-bin       # 二进制日志;
    3.slow_query_log    # 慢速查询日志;
2.默认存储引擎:可以使用–default-storage-engine选项指定不同于InnoDB的默认存储引擎;
3.系统变量:可以通过设置服务器系统变量值来定制服务器;
    1.max_connections=200 # 增加允许的最大连接数;
    2.innodb_buffer_pool_instances=4  # 增加InnoDB缓冲池数的默认值;
4.共享内存:在Windows上默认不启用;可以使用shared-memory选项来启用命名管道支持;
5.命名管道:要启用命名管道支持,使用enable-named-pipe选项;
— 样例选项文件;
1.Linux:
    1.对于RPM安装,样例选项文件在/usr/share/mysql中;
    2.对于TAR文件安装,样例文件在MySQL安装目录下的share目录中;
2.Windows:选项文件位于MySQL安装目录(my.ini)中;
3.如果多次指定一个选项(不管是在同一个选项文件中,还是在多个选项文件中),则最后
出现的选项值优先;
    1.–defaults-file=<file_name>:使用指定位置的选项文件;
    2.–defaults-extra-file=<file_name>:使用指定位置的其他选项文件;
    3.–no-defaults:忽略所有选项文件;
eg:要使用/etc/my-opts.cnf文件而忽略标准选项文件,可以:shell> mysql –defaults-file=/etc/my-opts.cnf;
— 显示选项文件中的选项;
1.# my_print_defaults –defaults-file=/usr/local/mysql/my.cnf client mysql mysqld;
2.# mysql –print-defaults:貌似打印不出来;
— 遮蔽验证选项;
1.建议不要使用mysql -uroot -poracle形式在命令行上指定口令;缺点:可以通过history命令查看到密码;
2.为方便起见,可以将口令放在[client]选项组中,但口令以纯文本方式存储,对选项文件有读取访问权限的任何人都能轻易地看到;
3.利用mysql_config_editor实用程序,可以将验证凭证存储在加密的登录文件.mylogin.cnf中;在Linux和UNIX上,该文件位置是当前用户的主目录;MySQL客户机程序以后可以读取该文件以获取用于连接到MySQL服务器的验证凭证;
TIPS:加密方法是可逆的,因此不应假设凭证对任何有文件读取特权的人都是安全的;相反,该功能使得避免使用纯文本凭证变得更容易;
4.文件说明:
.mylogin.cnf登录文件的未加密格式由选项组组成,类似于其他选项文件;
.mylogin.cnf中的每个选项组称为“登录路径”,仅允许一组有限的选项:主机,用户和口令;可将登录路径视为一组值,可以指示服务器主机以及用于服务器验证的凭证;eg:
    [admin]
    user = root
    password = oracle
    host = 127.0.0.1
— 登录路径;
1.创建登录路径:mysql_config_editor set –login-path=admin –host=localhost –user=root –password;如果调用mysql_config_editor时不使用–login-path选项,则将使用[client]登录路径;默认情况下,所有标准客户机都使用此登录路径;
    1.查看生成的文件:ll ~/.mylogin.cnf;
    2.登录:mysql –login-path=admin;
2.以纯文本格式查看单个登录路径:mysql_config_editor print –login-path=admin;
3.以纯文本格式查看所有登录路径:mysql_config_editor print –all;
4.删除登录路径:mysql_config_editor remove –login-path=admin;
— 服务器系统变量;
1.查看所有参数的默认值和读取选项文件之后的值:mysqld –verbose –help;
2.查看所有参数的默认值和忽略任何选项文件中的设置:mysqld –no-defaults –verbose –help;
3.查看变量值,没有其他启动选项:SHOW GLOBAL VARIABLES;
— 动态系统变量;
1.MySQL维护了两种包含系统变量的作用域:
    1.GLOBAL变量影响服务器的整体操作;
    2.SESSION变量影响其对单个客户机连接的操作;
    3.变量存在于任一作用域中,也可同时存在于两个作用域中;
2.变量及其作用域的示例包括:
    1.仅全局:key_buffer_size,query_cache_size;
    2.全局和会话:sort_buffer_size,max_join_size;
    3.仅会话:timestamp,error_count;
3.在更改变量值时,适用以下几点:
    1.设置会话变量不需要任何特殊特权,但客户机只能更改自己的会话变量,不能更改其他任何客户机的会话变量;
    2.LOCAL和@@local是SESSION和@@session的同义词;
    3.如果不指定GLOBAL或SESSION,则当会话变量存在时,SET将更改会话变量;会话变量不存在时,将产生错误;
TIPS:修改的全局参数不会在选项文件中反应出来,需要手动修改,以便下次启动后生效;与Oracle对比;
— 显示动态系统变量;
设定特定的变量时要注意赋值的类型;
— 结构化系统变量;
1.MySQL支持一种结构化变量类型,该变量类型可以指定控制键高速缓存操作的参数;键高速缓存结构化变量具有以下组件:
    1.key_buffer_size;
    2.key_cache_block_size;
    3.key_cache_division_limit;
    4.key_cache_age_threshold;
2.要引用结构化变量实例的组件,可使用复合名称:instance_name.component_name;
示例:hot_cache.key_buffer_size/hot_cache.key_cache_block_size;cold_cache.key_cache_block_size;
3.相关文档:http://dev.mysql.com/doc/refman/5.6/en/structured-system-variables.html。
— 服务器状态变量;
1.LOCAL是SESSION的同义词;
2.如果没有修饰符,则默认值为SESSION;
3.SHOW STATUS示例:mysql> SHOW GLOBAL STATUS;
— SQL模式;
SQL模式由控制查询处理某些方面的可选值组成,设置了相应的SQL模式后,客户机就可以对以下项目进行某种程度的控制:
    1.输入数据:SQL模式可用于指示服务器对接受输入数据的宽容度;
    2.标准SQL符合性:SQL模式可用于启用或禁用与标准SQL符合性相关的行为;
    3.兼容性:SQL模式可用于改进与其他数据库系统的兼容性;
— 设置SQL模式;
1.可以使用–sql-mode选项设置服务器启动时的默认SQL模式;
2.单个客户机可按自己的要求在选项文件内配置SQL模式;
3.如果没有修饰符,则SET将更改会话SQL模式;调用SET语句时可以带一个空字符串来清除当前SQL模式,也可以带一个或多个模式名称(用逗号分隔);
4.如果值为空或者包含多个模式名称,则必须将值放在引号中;如果值包含一个模式名称,则引号可有可无;SQL 模式值不区分大小写;
5.查看当前的sql_mode模式:
    1.使用SELECT语句检查当前的sql_mode设置:SELECT @@sql_mode;
    2.查看系统变量:SHOW VARIABLES LIKE ‘sql_mode’;
例子:
    1.使用单个模式值设置SQL模式:SET sql_mode = ANSI_QUOTES; SET sql_mode = ‘TRADITIONAL’;
    2.使用多个模式名称设置SQL模式:SET sql_mode = ‘IGNORE_SPACE,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION’;
— 常用SQL模式;
1.STRICT_TRANS_TABLES,STRICT_ALL_TABLES:没有这些模式,MySQL将接受缺少,超出范围或格式不正确的值;启用 STRICT_TRANS_TABLES时将为事务表设置“严格模式”;也可在默认的my.cnf文件中启用;启用STRICT_ALL_TABLES 时将为所有表设置严格模式;
    mysql> CREATE DATABASE db1;
    mysql> USE db1;
    mysql> CREATE TABLE t(id INT);
    mysql> INSERT INTO t VALUES (‘1’);
    mysql> commit;
    mysql> INSERT INTO t VALUES (‘x’);
    mysql> commit;
    mysql> SELECT * FROM t;
    +——+
    | id   |
    +——+
    |    1 |
    |    0 |
    +——+
    mysql> SELECT @@sql_mode;
    +————————+
    | @@sql_mode             |
    +————————+
    | NO_ENGINE_SUBSTITUTION |
    +————————+
    mysql> SET sql_mode=’STRICT_TRANS_TABLES,STRICT_ALL_TABLES’;
    mysql> SELECT @@sql_mode;
    +—————————————+
    | @@sql_mode                            |
    +—————————————+
    | STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
    +—————————————+
    mysql> INSERT INTO t VALUES (‘x’);
    ERROR 1366 (HY000): Incorrect integer value: ‘x’ for column ‘id’ at row 1
2.TRADITIONAL:启用此SQL模式可对输入数据值施加类似于其他数据库服务器的限制;在此模式下,使用GRANT语句可创建要求指定口令的用户;
3.IGNORE_SPACE:默认情况下,必须调用函数名称与后接括号间没有空格的函数;启用此模式后,允许存在此类空格,并使函数名称成为保留字;
4.ERROR_FOR_DIVISION_BY_ZERO:默认情况下,除数为零时将产生结果NULL,在启用此模式的情况下插入数据时,除数为零将导致出现警告,在严格模式下将出现错误;(这个模式之后会被废弃掉)
    mysql> INSERT INTO t VALUES (2/1);
    mysql> INSERT INTO t VALUES (2/0);
    mysql> SET sql_mode=’STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO’;
    mysql> SELECT @@sql_mode;
    mysql> INSERT INTO t VALUES (2/0);
    ERROR 1365 (22012): Division by 0
5.ANSI:使用此组合模式将使MySQL服务器变得更加“类似于ANSI”;即,此模式支持的行为更像标准SQL,如ANSI_QUOTES和PIPES_AS_CONCAT;
6.NO_ENGINE_SUBSTITUTION:如果在创建或更改表时指定了不可用的存储引擎,除非启用了此模式,否则MySQL 将替换默认存储引擎;这是默认的SQL模式;
— 日志文件;
1.错误日志(error log):
    1.记录MySQL启动,关闭和运行时产生的重大的错误的信息;
    2.如果mysqld警告一个表需要自动的检查或者修复,也会记录一个错误日志;
    3.可以使用–log-error=file_name选项来指定错误日志文件,如果没有指定,则系统默认在data目录下生产一个hostname.err的文件;也可以在配置文件中配置log_error变量;
    4.主要是由mysqld_safe脚本可创建错误日志,并在启动服务器时将其输出重定向到该错误日志;
    5.查询log_error的位置:>show variables like ‘log_error’;查看主机名:>system hostname;
2.常规查询日志(general query log):
    1.常规查询日志记录服务器运行期间收到的所有语句.当客户端连接或者断开的时候服务器会记录信息到日志中,并记录所有从客户端接收到的sql语句,它对于在客户端中排错和查看是哪个客户端发送的命令很有帮助;
    2.mysqld是按照接收到命令的方式记录语句的,这可能跟它们执行的顺序不同(这与二进制日志是有区别的,二进制日志是执行后记录);
    3.使用–general_log=[0|OFF|1|ON]来控制是否打开常规查询日志和–general_log_file=file_name来指定生成的常规查询日志文件(在mysql5.1.6中可以使用–log选项启动和使用–log-output选项指定日志输出的位置),也可以输出到”Server Log Tables”表中;如果没有指定file_name,默认是在data目录下生产一个hostname.log文件;
    4.服务器重新启动和log flush不会产生一个新的文件;
    5.默认此功能关闭,通过show variables like ‘general_log%’来查看;
    6.设置打开一般查询日志:set global general_log=1;set global general_log_file=file_name(两个变量会同时打开关闭,打开之后立即生效);
    7.可以在启动的时候指定–general-file选项或者在配置文件中指定general_log=1,general_file_log=/path;
    8.一般不打开此日志功能,数据量太大,如果打开可以放到单独的磁盘中.
    9.log_output变量值:
        1.FILE:保存到文件中;
        2.TABLE:保存到表中;SET GLOBAL log_output = ‘TABLE’;
        3.清空日志表:TRUNCATE mysql.general_log;
    10.备份切换一般日志文件:
        1.先备份:>mv hostname.log hostname.log.bak
        2.切换日志组:mysqladmin flush-logs;
3.慢查询日志(slow query log):
    1.调优时使用,记录超出指定时间的sql语句;
    2.慢查询日志主要记录执行时间超过long_query_time变量指定时间的sql语句,这个时间不包括获得锁的时间,只包含执行时间,系统默认时间是1s(long_query_time=1.0000);
    3.使用–slow-query-log=0|1选项和–slow_query_log_file=file_name选项指定(在mysql5.1.6版本可以指定mysqld的–long-slow-queries=file_name选项启动);
    4.命令行参数:–log-slow-queries=file_name;指定慢查询日志文件
    5.系统变量:
        1.slow_query_log:开启慢查询功能,set global slow_query_log = [0|OFF|1|ON];
        2.slow_query_log_file:指定慢查询日志文件,系统默认是在data目录下的hostname-slow.log文件;
        3.long_query_time:指定查询的最大时间,set global long_query_time=n;
        4.log_queries_not_using_indexes:不用索引的慢查询,默认是功能关闭的;
            1.查看某个表是否有索引:> show index from t;
            2.打开此功能:>set global log_queries_not_using_indexes =1;
            3.查看变量:>show variables like ‘log_queries_not_using_indexes’;
    6.log_output变量值:
        1.FILE:保存到文件中;
        2.TABLE:保存到表中;SET GLOBAL log_output = ‘TABLE’;
        3.清空日志表:TRUNCATE mysql.slow_log;
    7.分析慢日志文件的工具:mysqldumpslow,查看具体的帮助是用mysqldumpslow –help;
4.二进制日志(binary log):
    1.记录所有对数据库更新和潜在的更新语句(一个delete语句,但是没有影响的行),语句以事件(event)的方式存储,同时也包含了更新语句执行的时间信息;
    2.它不记录那些不修改任何数据的语句,如果想要记录所有的语句,可以使用query log;
    3.它主要的目的是在做还原操作时尽可能全的更新数据库,因为它包含在一次备份后的所有更新操作.它同样被用在master replication server中作为一个记录发送给slave servers;
    4.打开二进制日志会损失1%的性能,但是它带来的好处远远超过这些;
    5.查看二进制日志是否打开,需要查看log_bin参数是否是ON:>show variables like ‘log_bin’;
    6.命令行参数
        1.–log-bin=filename:记录二进制日志文件的位置,尽量指定路径名,如果不指定的话则保存在数据目录;
        2.–log-bin-index=file:记录二进制日志文件索引的位置,保存了日志文件名;
        3.–max_binlog_size:单个文件最大多少;
        4.–binlog-do-db=db_name:哪个数据库使用,只有这个数据库使用;
        5.–binlog-ignore-db=db_name:哪个数据库不使用,只有这个数据库不使用;
    7.系统变量
        1.log_bin:日志的位置;
        2.binlog_cache_size:二进制日志缓存大小,是每一个连接进来的线程分配的大小,不是整个服务器的大小;
        3.max_binlog_cache_size:最大缓存大小;
        4.max_binlog_size:单个文件最大大小,超过此大小则再分配一个文件,但是一个事务必须在一个文件中,所以可能会稍大点;
        5.binlog_cache_use:当前连接使用的binlog缓存的事务的数量,使用show status like ‘binlog_cache_use’查看(show status命令显示了所有连接到mysql服务器的状态值);
        6.binlog_cache_disk_use:如果binlog_cache_use不够用,则在磁盘上缓存,应该尽量避免;
        7.binlog_do_db:设置master-slave时使用;
        8.binlog-ignore-db:设置哪个数据库不记录日志;
        9.sync_binlog:缓存与硬盘的同步频率(commit多少下同步一次,0表示服务器自动控制);
        10.binlog_format:二进制日志的格式;
    8.查看当前二进制文件的名称和大小,show binary/master logs;
    9.如果不指定二进制日志文件的位置,默认存放在data文件夹下,日志文件是:mysql-bin.xxxxxx,索引文件是mysql-bin.index;
    10.如果要切换日志的话,执行flush logs命令;
    11.初始化二进制日志系统,从新生成:reset master命令;
    12.删除某个日志文件:purge binary logs [before ‘datetime’ / to ‘log_name’] 删除指定日期之前的和删除指定文件之前的日志文件;
    13.设置日志文件的失效期:参数为–expire_logs_days,set global expire_log_days=n,N天前的日志自动删除;
    14.二进制日志的格式
        1.查看格式:show [global] variables like ‘binlog_format’;
        2.设置日志格式:set [global] binlog_format = statement|row|mixed;
        3.查看binlog中的事件:show binlog events in ‘mysql-bin.000002’ from 0;
        4.使用mysqlbinlog程序打开;
5.审计日志(audit log):
    1.用于记录企业版基于策略的审计信息;审计日志是作为企业版插件提供的;
    2.由–audit-log选项和audit_log_file选项来控制;
    3.审计过程会不断写入审计日志,直到将该插件删除,或者通过audit_log_policy=NONE 选项设置关闭审计;
    4.在服务器启动时使用audit_log=FORCE_PLUS_PERMANENT作为选项,可以防止删除该插件;
补充:
6.InnoDB重做日志(innodb redo log);
    1.与innodb数据引擎相关;
    2.用来实现灾难恢复(crash recovery),突然断电会导致innodb表空间中的数据没有写到磁盘上,通过执行redo log能够重新执行这些操作来恢复数据;
    3.提升innodb的i/o性能,innodb引擎把数据和索引都载入到内存中的缓冲池中,如果每次休息数据和索引都需要更新到磁盘,必定会增加i/o请求,而且因为每次更新的位置都是随机的,磁头需要频繁的定位导致效率很低,所以innodb每处理完一个事务后只添加一条日志log,另外有一个线程负责智能的读取日志文件并批量更新到磁盘上,实现最高效的磁盘写入;
    4.系统变量:
        1.innodb_log_buffer_size:日志缓冲区的大小;
        2.innodb_log_file_size:日志文件的大小;
        3.innodb_log_files_in_group:一组日志中有几个文件:
            1.文件名为ib_logfileX(X从0开始一次增加);
            2.先关闭数据库服务:>mysqladmin shutdown(mysql.server stop);
            3.把data目录下的ib_logfile*文件移动走:>mv ib_logfile* /tmp;
            4.在配置文件中添加innodb_log_files_in_group=n的参数;
            5.启动数据库服务:>mysqld –defaults-file=./my.cnf –user=mysql(mysql.server start);
            6.可以查看error log文件观察启动过程;
        4.innodb_log_group_home_dir:日志存放的性对路径(相对于$MYSQL_HOME/mysql/data目录,即datadir目录);
            1.关闭服务器;
            2.在配置文件中添加此参数,并指定路径;
            3.启动服务器;
        5.innodb_flush_log_at_trx_commit:根据不同的数据安全级别去设定.
            1.0:日志缓冲每秒一次的被写入到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何修改;
            2.1:每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新;
            3.2:每个事务提交后,日志缓冲被写到日志文件,但不对日志文件做到磁盘操作刷新,对日志文件每秒刷新一次;
            4.查看此变量:>show variables like ‘innodb_flush_log_at_trx_commit’;
        6.innodb_os_log_written:写入到文件日志的数据量,使用show status查询;
        7.innodb_os_log_fsyncs:写入到磁盘的次数,使用show status查询;
— 日志文件用法列表;
— 二进制日志记录;
1.二进制日志包含描述数据库更改(如创建数据库或更改表数据)的“事件”;二进制日志 还包含可能做出更改的语句的事件(例如,没有匹配行的DELETE);该日志还包含有关 每条更新语句所用时间的信息;
2.二进制日志有两个重要用途:复制和数据恢复;
3.MySQL使用日志传送复制解决方案;使用日志传送系统时,可以将主系统上发生的所有数据更改存储在二进制日志中,然后通过从系统检索这些数据更改,并根据接收到的这些日志文件执行更改;
4.可以实时下载日志文件并执行内容;即,只要生成日志文件事件,就将其发送到连接的从系统供执行;由于网络传播存在延迟,从系统可能需要几秒到几分钟(最坏的情况)时间来接收更新;在理想的情况下,延迟会在一秒以内;
5.发生以下事件之一时,二进制日志会轮转:
    1.重新启动MySQL服务器;
    2.达到允许的最大大小(max_binlog_size);
    3.发出了FLUSH LOGS SQL命令;
6.二进制日志独立于存储引擎,不管使用的存储引擎是哪个(即InnoDB或MyISAM),MySQL复制都会工作;
— 二进制日志记录格式;
1.基于语句的二进制日志记录:
    1.包含实际SQL语句
    2.包括DDL(CREATE,DROP等)和DML(UPDATE,DELETE等)语句;
    3.相对较小的文件保存磁盘空间和网络带宽;
    4.并非所有复制的语句都会在远程计算机上正确重放;
    5.要求主系统和从系统上复制的表和列完全相同(或者符合多个限制条件);
2.基于行的二进制日志记录:
    1.指示对单个表行的影响情况;
    2.正确重放所有语句,即使对于在使用基于语句的日志记录时未正确复制的功能导致的更改也是如此;
3.按如下方式设置格式:SET [GLOBAL|SESSION] BINLOG_FORMAT=[row|statement|mixed|default];
注:使用mixed选项可让MySQL选取最适合单个事件的格式,MySQL通常会使用基于语句的二进制日志,但在需要时可恢复到基于行的复制;
— 列出二进制日志文件;
1.SHOW BINARY LOGS语句可以列出当前日志文件和文件大小;
2.SHOW MASTER STATUS语句可以显示下一个事件的主状态;需要SUPER或REPLICATION CLIENT特权;
— 查看二进制日志内容;
1.方式1:show binlog events in ‘mysql-bin.000002’ from 0;
2.方法2:mysqlbinlog mysql-bin.xxxxxx;
— 删除二进制日志;
1.默认情况下,不会删除旧的日志文件;
2.根据存在时间删除日志:
    1.要在二进制日志轮转过程中自动删除存在时间多于指定天数的任何二进制日志,可使用expire_logs_days设置;
    2..也可以在选项文件中配置expire_logs_days:
        [mysqld]
        expire_logs_days=7
    3.PURGE BINARY LOGS BEFORE now() – INTERVAL 3 day;
4.根据文件名删除日志:PURGE BINARY LOGS TO ‘mysql-bin.000010’;
— 配置企业审计;
1.要安装audit_log插件:
    1.方法1:使用INSTALL PLUGIN语法:INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
    2.方法2:在服务器启动时设置plugin-load选项:
        [mysqld]
        plugin-load=audit_log.so
2.默认情况下,装入该插件就会启用日志记录;将选项audit-log设置为OFF可禁用日志记录;
3.要防止在运行时删除该插件,可设置以下选项:audit-log=FORCE_PLUS_PERMANENT;
4.日志文件命名为audit.log,默认情况下位于服务器数据目录中;要更改该文件的名称或位置,可在服务器启动时设置 audit_log_file系统变量;
5.要平衡遵从性和性能,可使用audit_log_strategy选项在SYNCHRONOUS,ASYNCHRONOUS,SEMISYNCHRONOUS和 PERFORMANCE之间进行选择;
6.如果将audit_log_rotate_on_size设置为某个大于0的数字,则当日志文件大小超出了该数量的4KB数据块大小时,将轮转日志文件;
— 审计日志文件;
1.每个审计记录的TIMESTAMP采用UTC格式;
2.NAME属性代表事件类型;例如,“Connect”表示登录事件,“Quit”表示客户机断开连接,“Shutdown”表示服务器关闭;
3.“Audit”和“NoAudit”表示审计开始和停止的点;
4.STATUS属性提供命令状态;这与MySQL命令SHOW ERRORS显示的Code值相同;
5.有些属性仅在特定的事件类型中出现;例如,“Connect”事件包括诸如HOST,DB,IP和USER之类的属性;“Query”事件包括SQLTEXT属性;
补充:审计过滤工具;
mysqlauditgrep –users=root –query-type=SELECT  –status=0 /var/lib/mysql/audit.log
— 练习任务;
1.修改访问端口为3309;
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    port=3309
    user=mysql
    …
    [client]
    port=3309
2.查看当前客户会话正在使用的选项状态;
    mysql> status;

MySQL OCP-03-系统管理

Posted on by
— MySQL服务器分发;
1.MySQL可用于多个操作系统,包括Linux/Windows/Mac OS X和Oracle Solaris等;本课程仅讲述Linux;
2.MySQL可以作为二进制文件分发和源代码分发的形式提供:
    1.二进制文件分发:是预编译的,可以运行的程序,可用于Enterprise和Community MySQL Server版本,这些二进制文件是正式的经过Oracle测试的版本;
    2.源代码分发:不保证与商业代码更新一致,它们也不包括Oracle支持;
TIPS:有关可用OS类型的完整列表http://dev.mysql.com/downloads/mysql/;
— MySQL二进制文件分发;
1.用于Linux的二进制文件:
    1.RPM文件可用于基于RPM的Linux分发,例如Oracle Linux;通过使用rpm程序或者通过使用yum等软件包管理器来安装这些文件,每个RPM的安装布局由RPM文件自身内包含的规范文件提供;(使用rpm -qpl <rpm_file>来查看安装时RPM文件内容的位置)
    2.TAR文件可用于多种Linux和UNIX类似系统,要安装这种类型的分发,使用.tar程序在安装目录中解压缩该分发;
2.用于Windows的二进制文件:
    1.完整分发:包含MySQL安装的所有文件以及配置向导。
    2.非安装分发:.zip归档文件,不使用安装或配置向导,您只需解压缩该归档文件并将其移至所需的安装位置;
TIPS:二进制文件分发还可以压缩文件形式用于多个其他操作系统(包括Oracle Solaris);
— MySQL源代码分发;
1.如果需要预编译分发中可能没有的功能(例如完整的调试支持),可以根据源代码编译MySQL;
2.要使服务器在运行时使用较少内存,可能需要禁用不需要的功能;例如,可能需要禁用可选存储引擎,或者仅编译实际需要的那些字符集;
3.二进制文件分发仅可用于已发行的版本,不可用于最新的开发源代码;
4.源代码分发可以安装在任何所需位置;默认Linux安装位置为/usr/local/mysql;
— 用于Linux的MySQL RPM安装文件;
1.Oracle提供两种类型的MySQL RPM:
    1.与分发无关:MySQL提供给社区的RPM,它们应该可以在支持RPM软件包并使用glibc 2.3(GNU C库是标准C库的GNU实现)的所有Linux版本上运行;(查看方式:ldd –version)
    2.特定于分发:面向目标Linux平台,Oracle为许多平台提供了RPM文件;
2.MySQL的RPM安装通常分为不同的软件包,对于标准安装,必须至少安装服务器程序 和客户机程序,标准安装不需要其他软件包;
    1.MySQL-client-advanced-<version>.rpm:客户端命令行工具;
    2.MySQL-devel-advanced-<version>.rpm:包含了编译软件所需要的头文件,需要使用客户端共享库;
    3.MySQL-embedded-advanced-<version>.rpm:嵌入式数据库,为移动智能设备使用;
    4.MySQL-server-advanced-<version>.rpm:服务端(包含mysqld的二进制文件);
    5.MySQL-shared-advanced-<version>.rpm:包含客户端的共享库(libmysqlclient.so*);
    6.MySQL-shared-compat-advanced-<version>.rpm:包含了兼容旧版本的客户端共享库;
    7.MySQL-test-advanced-<version>.rpm:包含了测试套件;
— Linux MySQL RPM安装过程;
1.解压缩包:unizp MySQL-5.6.25-oel6-x86_64.zip;
2.创建用户:useradd mysql;
3.安装RPM包:rpm -ivh MySQL-*-advanced-5.6.25-1.el6.x86_64.rpm;安装在运行时自动执行以下任务:
    1.将RPM文件提取到其默认位置;
    2.在/etc/init.d目录中注册名为mysql的启动脚本;
    3.执行mysql_install_db,即创建系统数据库和默认my.cnf文件的脚本,为root帐户设置随机口令并将该口令保存在安装用户主目录中名为.mysql_secret的文件中;
    4.为mysql设置登录帐户以及用户名和组名称(用于管理和运行服务器);
4.发生与自带版本冲突的话:
    1.删除原来的包:yum remove mysql-libs-5.1.73-3.el6_5.x86_64;缺点是会删除相关的依赖文件,可能其它程序会用;
    2.添加–replacefiles选项:rpm -ivh –replacefiles MySQL-*-advanced*;
5.产生的目录:
    1./usr/bin:客户端程序和脚本;
    2./usr/sbin:mysqld服务程序;
    3./var/lib/mysql:数据库和日志文件,之后讲到数据库结构会讲每个文件的作用;
    4./usr/share/info:info格式的MySQL手册;
    5./usr/share/man:标准的Unix man格式手册;
    6./usr/include/mysql:MySQL所需的头文件;
    7./usr/lib64/mysql:库文件;
    8./usr/share/mysql:其它杂项,包括支持文件,错误信息,字符集文件,示例配置文件和数据库安装的SQL文件等;
    9./usr/share/sql-bench:测试基线;
    10./etc/my.cnf, /usr/my.cnf:缺省配置文件;
    11./etc/init.d/:包含了mysql启动脚本;
    12./var/log:mysqld.log文件;
6.启动数据库:service mysql start;
    1.查看后台进程:ps -ef | grep mysql;
    2.本来是启动的mysqld服务,后台多了一个mysqld_safe服务,之后讲数据库启动的几种方式会提到;
7.客户端登陆:mysql;
    1.之前版本都可以使用空密码登录,现在却报错;
    2.查看安装过程,会提示密码随机了,之后需要修改密码;
        A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
        You will find that password in ‘/root/.mysql_secret’.
        You must change that password on your first connect,
        no other statement but ‘SET PASSWORD’ will be accepted.
        See the manual for the semantics of the ‘password expired’ flag.
        Also, the account for the anonymous user has been removed.
    3.查看到密码后再登录:mysql -uroot -p;
    4.如何使用SET PASSWORD: help SET PASSWORD,发现报错,必须先修改:SET PASSWORD = PASSWORD(“mysql”);
8.卸载数据库:
    1.查看安装了哪些软件:yum list | grep MySQL;
    2.删除软件:yum remove MySQL-*;
— Linux MySQL服务器安装目录;
— 在Linux上启动MySQL服务器;
1.可以使用多种方法在Linux上启动服务器:
    1.mysqld:手动调用服务器来调试MySQL服务器;默认情况下,错误消息传至终端,而不是错误日志;
    2.mysqld_safe:设置错误日志,然后启动mysqld并对其进行监视,如果mysqld异常终止(kill -9 pid),mysqld_safe会将其重新启动;如果服务器未正常启动,请查看错误日志;
    2.mysql.server:用作mysqld_safe的包装,针对使用System V运行级别目录的Linux和Oracle Solaris等系统;
    4.mysqld_multi:该Perl脚本用于简化单台主机上的多个服务器管理,它可以启动或停止服务器,它还可以报告服务器是否正在运行;
    5.其它:mysqladmin -uroot -p start;
2.安装正确的脚本以使服务器在启动时自动运行:
    1.在BSD样式的Linux系统上,最常见的是通过某一个系统启动脚本(例如/etc目
    录中的rc.local脚本)调用mysqld_safe;
    2.在/etc/init.d下具有运行级别目录的Linux和UNIX System V变体使用mysql.server脚本,预先构建的 Linux二进制软件包针对相应的运行级别在名称mysql安装mysql.server;使用chkconfig注册服务;
— 在Linux上停止MySQL服务器;
1.要手动停止服务器,请使用以下方法之一:
    1.mysqladmin:具有关闭命令,它作为客户机连接到服务器并且可以关闭本地或远程服务器;mysqladmin -uroot -p shutdown;
    2.mysql.server:在使用stop参数调用时停止和/或关闭本地服务器;
    3.mysqld_multi:停止和/或关闭其管理的任何服务器,它通过调用mysqladmin来执行此操作;
2.mysqld_safe没有服务器关闭功能;
— 提高安装安全性;
1.从RPM软件包安装MySQL时,将为root帐户设置随机口令并将该口令保存到安装用户主目录中的.mysql_secret文件;对于所有其他安装,初始口令为空白;
2.在不带参数的情况下调用mysql_secure_installation,这将提示您确定要执行的操作;
例子:可以运行一下# /usr/bin/mysql_secure_installation,虽然此处显示mysql_secure_installation 脚本以Linux root用户身份运行,但您可以普通用户身份运行该脚本;
— Windows MySQL服务器安装目录;
1.默认情况下,MySQL5.6安装在目录路径C:\Program Files\MySQL\MySQL 5.6 Server中;
2.\bin包含MySQL服务器和客户机程序:
    1.mysqld.exe:标准服务器;
    2.其他客户机程序,例如mysqladmin.exe;
3.\data是服务器存储数据库和日志文件的位置,此目录是预配置的,可以直接使用;例如,此目录包括mysql子目录(包含授权表)和用于test数据库的test子目录(可用于测试目的);
4.my.ini:配置选项文件指定安装目录的位置以及其他可选设置;
— 在Windows上运行MySQL;
1.通过使用net start MySQL和net stop MySQL命令从命令行手动启动和停止服务;
2.使用mysqld –install命令从命令行调用服务器;在停止服务后将其删除,使用mysqld –remove;
— 数据目录;
1.每个表都具有*.frm文件(包括视图);
2.MySQL服务器开始执行时会将其当前工作目录转到其data目录,必须确保MySQL服务器具有正确的访问权限,以便在data目录中创建文件,该服务器必须可以访问它要在其中创建数据文件或日志文件的所有目录;
3.MySQL服务器将每个数据库映射到MySQL data目录下的一个目录,并且默认情况下,它将数据库中的表映射到数据库目录中的文件名;这具有以下含义:
    1.数据库和表名称仅在具有区分大小写的文件名的操作系统(例如大多数Linux系统)上的MySQL服务器中才区分大小写;
    2.可以通过将数据目录,数据库和/或单个表(具体取决于存储引擎选项)移至不同的物理位置来分割磁盘使用,这可以提高性能;
— MySQL服务器发行版;
1.升级之前还应该查看readme文档:
    1.在关于升级的部分中,一定要阅读与正在执行的升级类型有关的注释,按照建议的过程执行操作;
    2.在关于新版本的更改注释部分中,查看在当前版本与要安装的版本之间发生的所有更改,请注意不与当前版本向后兼容的所有更改;
2.RPM和源代码升级通常不需要进行重新配置,因为它们往往使用相同安装目录位置,而不考虑MySQL版本;
3.需要进行一些重新配置的情况:
    1.如果使用通用Linux二进制文件进行升级,可以选择创建特定于新版本的目录来包含升级的发行版;
    2.此外,Windows安装程序在Program Files下创建特定于版本的文件夹;
4.设置指向旧安装目录的软链接,从而可以轻松删除并重新创建该链接来指向新安装目录,对该符号链接的后续引用将访问新安装;比如:把data目录做成一个软链接;
5.如果您的安装最初是通过安装多个RPM软件包而生成的,则最好升级所有软件包,而不仅是其中的一些;例如,如果先前安装了服务器和客户机RPM,则不要仅升级服务器RPM;
补充:
1.升级MySQL总体上可以简单使用以下步骤进行操作:
    1.备份你的数据库;
    2.关闭Server;
    3.在已存版本上安装新版本MySQL;
    4.启动Server;
2.MySQL当前提供的升级不提供跳级升级,因此,如果你当前的MySQL版本为5.1,那么升级到5.7的流程为:
5.1->5.5->5.6->5.7;
— 检查升级的表;
1.在每次进行MySQL升级时,都需要运行mysql_upgrade程序,主要是执行了以下操作:
    1.检查数据库中的所有表与MySQL服务器当前版本是否兼容性;
    2.修复表中发现的所有问题以及可能的不兼容性;
    3.升级系统表来添加新版本中可用的所有新特权或功能;
    4.使用当前MySQL版本号标记所有已检查和已修复的表;
2.mysql_upgrade会将MySQL版本号保存在数据目录下的一个mysql_upgrade_info的文件中,这个文件被用于快速查看是否所有表针对升级版本已经做了检查,是否可以跳过表检查;当然,运行mysql_upgrade时,你也可以使用–force项来跳过查看此文件;
3.在MySQL 5.7.5之前,为了检查和修正表并进行系统表升级,mysql_upgrade会调用以下2条命令:
    mysqlcheck –check-upgrade –all-databases –auto-repair
    mysql_fix_privilege_tables
  从MySQL 5.7.5以后,mysql_upgrade将直接和MySQL Server交互,发送所需的SQL语句来执行升级;
补充:升级过程;
1.停止服务:service mysql stop;
2.备份文件:
    1.创建临时目录:mkdir /tmp/mbackup;
    2.备份配置文件:cp /etc/my.cnf /tmp/mbackup/;
    3.备份数据文件:cp -rf /var/lib/mysql/ /tmp/mbackup/mysql;也可以用mysqldump/xtrabackup;
3.删除软件:yum -y remove MySQL-*-advanced-5.6.25-1.el6.x86_64;(不推荐)
4.安装软件:rpm -ivh –replacefiles MySQL-server-advanced-5.6.26-1.el6.x86_64.rpm MySQL-client-advanced-5.6.26-1.el6.x86_64.rpm;
5.拷贝回配置文件:cp /tmp/mbackup/my.cnf /etc/my.cnf;
6.尝试重启:service mysql restart;
7.执行更新:mysql_upgrade -uroot -p;
8.查看生成的更新文件并且尝试登录查看版本信息:less $MYSQL_DATADIR/mysql_upgrade_info;
— 使用多个服务器;
1.要在运行生产服务器的同一台计算机上测试MySQL的新发行版时,需要运行多个服务器;假定每个组具有其自己的指定root用户,该用户无法查看属于其他组的数据库,如果所有 客户机将共享同一服务器则可能会这样;
2.不允许任何服务器共享必须由单个服务器独占使用的资源;
3.mysqld_multi脚本设计用来管理多个mysqld进程,这些进程监听不同UNIX套接字文件和TCP/IP端口上的连接,该脚本搜索my.cnf中名为[mysqldN]的组,然后将该N的设置应用于编号的实例;
    例如,要启动两个mysqld实例,它们分别应用来自my.cnf部分[mysqld3]和[mysqld5]的设置,请运行以下命令:
    shell> mysqld_multi start 3, 5
— 多个服务器选项;
使用mysqld或mysqld_multi以及每个服务器函数的相应选项来调用每个MySQL服务器:
    1.数据目录:使用–datadir选项的唯一值启动每个服务器;
    2.网络:通过使用–port,–socket和–shared-memory-basename选项的唯一值启动每个服务器,将每个服务器设置为使用其自己的网络接口;
    3.组名称:使用mysqld_multi时,每个服务器组在Linux或UNIX上必须具有唯一的mysqldN名称;
    4.日志文件:每个服务器必须具有其自己的日志和PID文件;
    5.InnoDB表空间和日志文件:不能由多个服务器共享;
    6.Windows服务名称:每个mysqld Windows服务必须使用唯一的服务名称,通过使用–install设置服务名称;服务器启动时,它们从标准选项文件中的各个相应服务组中读取选项;
— 练习任务:
1.创建和导入world_innodb数据库;
    $ mysql -uroot -p;
    mysql> CREATE DATABASE world_innodb;
    mysql> USE world_innodb
    mysql> SET autocommit=0;
    mysql> SOURCE /labs/world_innodb.sql;
    mysql> SET autocommit=1; // 为了提高运行速度,批量提交;
2.查看本地MySQL服务器数据目录;
    mysql> SHOW VARIABLES LIKE ‘datadir’\G
3.检查MySQL服务器的状态;
    service mysql status;
4.关闭/启动MySQL服务器;
    service mysql stop/start;
— 补充:源码安装MySQL
1.准备安装工具:yum -y install gcc* make* perl*;
    1.cmake:从Mysql5.5以后使用cmake编译,可以从www.cmake.org下载最新版本;
    2.GUN make:操作系统自带;
    3.gcc:操作系统自带;
    4.perl:操作系统自带;
    5.libncurses5-dev(ncurses-devel):运行cmake必须的包,如果没有安装会报错.
2.安装cmake:
    1.解压压缩包:>tar -zxvf cmake-VERSION.tar.gz;
    2.进入到cmake的解压缩目录,执行./configure命令生成makefile;
    3.执行>make;make install;命令生成安装软件并安装cmake;
    4.软件安装到了/usr/local/share/cmake-VERSION/目录下,执行文件在/usr/local/bin目录下;
3.安装ncurses-devel插件
    1.在Debian和Ubuntu上的包名是libncurses5-dev;
    2.在RHEL和其它版本上是ncurses-devel,执行:yum install -y ncurses-devel*;
    3.如果不安装会出现以下错误;
4.创建mysql用户:>useradd mysql;
5.解压缩mysql5.5的源码包:>tar -zxvf mysql-VERSION.tar.gz;
6.进入目录mysql-VERSION目录;
7.执行cmake命令生成makefile(MyISAM,MERGE,MEMBER和CSV四种引擎默认静态编译);
    cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
    -DWITH_MYISAM_STORAGE_ENGINE=1 \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_FEDERATED_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_READLINE=1 \
    -DENABLED_LOCAL_INFILE=1 \
    -DMYSQL_TCP_PORT=3306
8.编译文件:>make;make install;在mysql的安装目录下生成可执行文件,并自动创建了data文件(可以手动再创建一个logs目录,用来存放生成的日志文件,与数据目录不在同一块磁盘上,减小I/O并发),修改目录权限为mysql:>chown mysql:mysql data;
9.编写/etc/my.cnf;
10.在配置文件下添加目录配置,指定数据文件的位置;
    bsedir = /usr/local/mysql/
    datadir = /usr/local/mysql/data
11.数据库的初始化,主要是数据库的创建,帮助文件的填充,用户文件的填充,执行:>./scripts/mysql_install_db –defaults-file=./my.cnf –user=mysql(在my.cnf配置文件中添加user参数,并且拷贝到/etc目录下就不用再加参数,执行>./scripts/mysql_install_db即可)
12.启动服务器:>./bin/mysqld_safe –-user=mysql &;
13.修改MYSQL服务器root用户的密码:>./bin/mysqladmin –u root password ‘pwd’;
14.登录:
    1.如果没有设置root的密码,默认是空密码,使用>./bin/mysql就可以登录;
    2.如果设置了root密码,则登录时要数据密码验证>./bin/mysql –uroot -p;
    3.进入数据库后修改用户密码:update user set password=PASSWORD(‘123456′) where user=’root’;
15.把mysql添加到环境变量:
    1.打开~root/.bash_profile文件;
    2.修改环境变量:>PATH=/usr/local/mysql/bin:$PATH,尽量把mysql的bin目录放在PATH的前面,使用mysql的工具的时候提高优先级,否则可能会使用系统预装的mysql的工具,造成版本不一致的错误;
    3.使环境变量立即生效:>. ./.bash_profile;
16.把配置文件放到默认读取的路径,并在配置文件中指定启动用户为mysql,添加开机启动:
    1.拷贝文件到开机启动目录:cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld;
    2.添加开机启动项：chkconfig –add mysqld;chkconfig mysqld on;
    3.启动/关闭mysql服务:service mysqld start/stop;
— 配置文件my.cnf(RPM安装);
# ————-mysql client profile——————-
[client]
port = 3306
socket = /var/lib/mysql/mysqld.sock
# ————-mysql client profile——————-
# ————-mysql general profile——————
[mysqld]
user = mysql
port = 3306
socket = /var/lib/mysql/mysqld.sock
pid-file = /var/lib/mysql/mysqld.pid
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 16M
table_open_cache = 512
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
thread_cache_size = 128
query_cache_size= 8M
binlog_cache_size = 4M
tmp_table_size = 4M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 2
# ————-mysql general profile——————
# ————-mysql base profile——————
# MYSQL server home directory
basedir = /usr/
# data files directory
datadir = /var/lib/mysql/
# ————-mysql base profile——————
# ————-log files profile——————
# binary log files directory
log_bin = /var/lib/mysql/mysql-bin.log
binlog_format = mixed
log_bin_index = /var/lib/mysql/mysql-bin.index
max_binlog_size = 500M
# mysql’s error log file dir(same as Oracle’s alter_SID.log file)
log_error = /var/lib/mysql/alert_mysql.log
# mysql’s general log file(disabled by default)
general_log_file = /var/lib/mysql/general_mysql.log
# mysql’s slow log file(disabled by default)
long_query_time = 1 # second;
slow_query_log_file = /var/lib/mysql/slow_query_mysql.log
# ————-log files profile——————
# ————-innodb engine profile——————
# InnoDB engine profile
autocommit = 0
innodb_file_per_table = 1
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_files_in_group = 4
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_data_home_dir = /var/lib/mysql/data
# same as oracle’s database buffer cache, the larger the better
innodb_buffer_pool_size = 128M
# same as oracle’s redo log files
innodb_log_file_size = 100M
# same as oracle’s redo log buffer cache, the larger the better
innodb_log_buffer_size = 4M
# turn off indexes statistic when DDL operater,default ON, it’s deprecated
#Innodb_buffer_pool_pages_free = 0
# ————-innodb engine profile——————
# ————-semi-sync profile——————
# master configure
#server-id       = 1
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000
# slave configure
#server-id       = 2
#rpl_semi_sync_slave_enabled=1
# ————-semi-sync profile——————
# ————-identify column——————
#Master-Master generate odd identifier
#auto_increment_offset = 1
#auto_increment_increment = 2
#Master-Master generate even identifier
#auto_increment_offset = 2
#auto_increment_increment = 2
# ————-identify column——————
# ————-others profile——————
max_connections = 1000
innodb_use_sys_malloc = 0
log_slave_updates
# skip ip to host name resolve
skip_name_resolve
# enable event scheduler process
event_scheduler = on
# add federated engine plugin
federated
# open Event Scheduler default
event_scheduler = on
# create functions
# log_bin_trust_function_creators = ON;
# ————-others profile——————
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
[mysqlhotcopy]
interactive-timeout
— 配置文件my.cnf(编译安装);
# ————-mysql client profile——————-
[client]
port = 3306
socket = /usr/local/mysql/log/mysqld.sock
# ————-mysql client profile——————-
# ————-mysql general profile——————
[mysqld]
user = mysql
port = 3306
socket = /usr/local/mysql/log/mysqld.sock
pid-file = /usr/local/mysql/log/mysqld.pid
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 16M
table_open_cache = 512
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
thread_cache_size = 128
query_cache_size= 8M
binlog_cache_size = 4M
tmp_table_size = 4M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 2
# ————-mysql general profile——————
# ————-mysql base profile——————
# MYSQL server home directory
basedir = /usr/local/mysql/
# data files directory
datadir = /usr/local/mysql/data/
# ————-mysql base profile——————
# ————-log files profile——————
# binary log files directory
log_bin = /usr/local/mysql/log/mysql-bin.log
binlog_format = mixed
log_bin_index = /usr/local/mysql/log/mysql-bin.index
max_binlog_size = 500M
# mysql’s error log file dir(same as Oracle’s alter_SID.log file)
log_error = /usr/local/mysql/log/alert_mysql.log
# mysql’s general log file(disabled by default)
general_log_file = /usr/local/mysql/log/general_mysql.log
# mysql’s slow log file(disabled by default)
long_query_time = 1 # second;
slow_query_log_file = /usr/local/mysql/log/slow_query_mysql.log
# ————-log files profile——————
# ————-innodb engine profile——————
# InnoDB engine profile
autocommit = 0
innodb_file_per_table = 1
innodb_log_group_home_dir = /usr/local/mysql/log/
innodb_log_files_in_group = 4
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_data_home_dir = /usr/local/mysql/data
# same as oracle’s database buffer cache, the larger the better
innodb_buffer_pool_size = 128M
# same as oracle’s redo log files
innodb_log_file_size = 100M
# same as oracle’s redo log buffer cache, the larger the better
innodb_log_buffer_size = 4M
# turn off indexes statistic when DDL operater,default ON, it’s deprecated
#Innodb_buffer_pool_pages_free = 0
# ————-innodb engine profile——————
# ————-semi-sync profile——————
# master configure
#server-id       = 1
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000
# slave configure
#server-id       = 2
#rpl_semi_sync_slave_enabled=1
# ————-semi-sync profile——————
# ————-identify column——————
#Master-Master generate odd identifier
#auto_increment_offset = 1
#auto_increment_increment = 2
#Master-Master generate even identifier
#auto_increment_offset = 2
#auto_increment_increment = 2
# ————-identify column——————
# ————-others profile——————
max_connections = 1000
innodb_use_sys_malloc = 0
log_slave_updates
# skip ip to host name resolve
skip_name_resolve
# enable event scheduler process
event_scheduler = on
# add federated engine plugin
federated
# open Event Scheduler default
event_scheduler = on
# create functions
# log_bin_trust_function_creators = ON;
# ————-others profile——————
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
[mysqlhotcopy]
interactive-timeout

MySQL OCP-02-体系结构

Posted on by
— MySQL体系结构;
1.MySQL安装必需的体系结构组件:MySQL服务器,客户机程序以及MySQL非客户机程序;中央程序充当服务器,客户机程序连接到该服务器以发出数据请求;
2.MySQL客户机/服务器通信并不仅限于所有计算机都运行同一操作系统的环境:
    1.客户机程序可以连接到在相同主机或不同主机上运行的服务器;
    2.客户机/服务器通信可以发生在计算机运行不同操作系统的环境中;
TIPS:在本课程中,都会使用在Linux中操作;
— 客户机程序;
1.mysql/mysqldump客户机程序是使用最多的一个客户段工具了;
2.MySQL Workbench是一款GUI工具,可以用来:
    1.为数据库建模;
    2.执行数据库查询;
    3.执行管理任务;
— 管理程序和实用程序;
1.这几款工具都是使用比较多的管理程序;
2.mysqldumpslow是Perl脚本;
TIPS:为了避免数据丢失或损坏,一些程序要求在执行之前关闭服务器和/或对当前表进行备份;
— MySQL服务器;
1.服务器和主机之间的概念差别:
    1.服务器:一个软件程序(mysqld),具有版本号和一系列功能;
    2.主机:服务器程序在其上运行的物理计算机,其中包含:硬件配置,操作系统,网络地址;
2.多个mysqld实例可同时在一台主机上运行;
— 服务器进程;
1.应用程序主要是mysqld进程来访问数据库服务器,由mysqld来操作不同的存储引擎(磁盘:InnoDB,MyISAM;内存:Memory;网络:NDB);
2.mysqld(服务器程序)进程可以划分为以下三个层:
    1.连接层:处理连接,此层存在于所有服务器软件(Web/邮件/LDAP服务器)上;
    2.SQL层:处理所连接的应用程序发送的SQL查询;
    3.存储层:处理数据存储,数据可以按不同格式和结构存储在不同物理介质上;
— 连接层;
1.连接层可通过多种通信协议接受来自应用程序的连接:
    1.TCP/IP;
    2.UNIX套接字;
    3.共享内存;
    4.命名管道;
2.其中TCP/IP适用于整个网络,也是最常用的连接方式;客户机和服务器在同一台计算机上运行时,上面列出的其他协议仅支持本地连接;
3.此层针对每个连接维护一个线程,此线程处理查询执行;在某个连接可以开始发送SQL查询之前,将会通过验证用户名+口令+客户机主机来对该连接进行验证;
— 通信协议;
1.TCP/IP(传输控制协议/Internet协议,Transmission Control Protocol/Internet Protocol):该通信协议套件用于连接Internet上的主机;在Linux操作系统中,TCP/IP是内置的,供 Internet使用,从而使其成为通过网络传输数据的标准;这也是适用于Windows的最佳连接类型;
2.UNIX套接字:一种进程间通信形式,用于在同一台计算机上的进程之间形成双向 通信链路的一端;套接字需要本地系统上的物理文件,这是适用于Linux的最佳连接类型;
3.共享内存:一种在程序之间传递数据的有效方式;一个程序创建其他进程(如果允许)可以访问的内存部分;此Windows显式“被动”模式仅适用于单台(Windows)计算机;默认情况下,共享内存处于禁用状态,要启用共享内存连接,必须使用–shared-memory选项启动服务器;
4.命名管道:命名管道的使用偏向于客户机/服务器通信,其工作方式与套接字非常相似;命名管道支持读/写操作,以及服务器应用程序的显式“被动”模式;此协议仅适用于单台(Windows)计算机;默认情况下,命名管道处于禁用状态,要启用命名管道连接,必须使用–enable-named-pipe选项启动服务器;
— SQL层;
1.解析器:解析器验证语法是否正确;
2.授权:验证是否允许所连接的用户运行特定查询;
3.优化器:创建每个查询的执行计划,这是有关如何以最优化的方式执行查询的分步指令集,确定要使用哪些索引以及采用何种顺序处理表是此步骤的最重要部分;
4.查询执行:完成每个查询的执行计划;
5.查询高速缓存:(可选)可配置的查询高速缓存,可用于存储(并立即返回)执行的查询和结果;
6.查询日志记录:可以启用以跟踪执行的查询;
— SQL语句处理;
第一次的选择主要是查看是否有配置开启[高速缓存查询]的特性;
— 存储层;
1.通过MySQL,可以使用称为“存储引擎”的不同类型的存储,数据可以存储在磁盘,内存和网络中;
2.数据库中的每个表可以使用任何可用的存储引擎,“磁盘”存储便宜且持久,而“内存”存储则要快得多;
3.InnoDB是默认存储引擎,它可提供事务,全文索引和外键约束,因此适用于各种混合查询;它具有多种用途,支持读密集型工作负荷,读/写工作负荷和事务工作负荷;
4.其他存储引擎包括:
    1.MyISAM:适用于频繁读取但很少更新的数据;
    2.MEMORY:在内存中存储所有数据;
    3.NDB:供MySQL Cluster用来为高可用性数据提供冗余的可伸缩拓扑;
注:存储引擎可扩展,超越存储层,而不只包含存储,它们还包括其他结构和实现机制;
— 存储引擎:概览;
1.客户机通过以SQL语句形式向服务器发送请求从表中检索数据或更改表中的数据;
2.服务器通过使用双层处理模型执行每条语句;
3.客户机通常不需要关心哪些引擎参与SQL语句处理,这种独立于引擎的SQL语句的一些例外情况包括:
    1.CREATE TABLE具有ENGINE选项,可基于每个表指定要使用的引擎;
    2.ALTER TABLE具有ENGINE选项,允许将表转换为使用不同的存储引擎;
    3.某些索引类型仅适用于特定存储引擎;例如,仅InnoDB和MyISAM引擎支持全文索引;
    4.COMMIT和ROLLBACK操作仅影响事务存储引擎(例如InnoDB和NDB)管理的表;
— 依赖于存储引擎的功能;
1.存储介质:表存储引擎可以在磁盘上,在内存中或通过网络存储数据;
2.事务功能:某些存储引擎支持全面的ACID事务功能,而其他存储引擎可能不具有事务支持;
3.锁定:存储引擎可能使用不同的锁定粒度(例如表级别锁定或行级别锁定)和机制来提供与并发事务的一致性;
4.备份和恢复:可能会受到存储引擎存储和操作数据的方式的影响;
5.优化:不同的索引实现可能会影响优化,存储引擎以不同的方式使用内部高速缓存,缓冲区和内存以优化性能;
6.特殊功能:某些引擎类型具有提供全文搜索和引用完整性的功能以及处理空间数据的能力;
TIPS:优化器可能需要根据存储引擎进行不同的选择,但这均是通过每种存储引擎支持的标准化接口(API)进行处理的;
— MySQL如何使用磁盘空间;
1.默认情况下,程序文件随数据目录一起存储在服务器安装目录下;执行各种客户机程序,管理程序和实用程序时将创建程序可执行文件和日志文件;
2.首要使用磁盘空间的是数据目录:
    1.服务器日志文件和状态文件包含有关服务器处理的语句的信息,日志可用于进行故障排除/监视/复制和恢复;
    2.InnoDB日志文件(适用于所有数据库)驻留在数据目录级别;
    3.InnoDB系统表空间包含数据字典,撤消日志和缓冲区;
    4.每个数据库在数据目录下均具有单一目录(无论在数据库中创建何种类型的表),数据库目录存储以下内容:
        1.数据文件:特定于存储引擎的数据文件,这些文件也可能包含元数据或索引信息,具体取决于所使用的存储引擎;
        2.格式文件(.frm):包含每个表和/或视图结构的说明,位于相应的数据库目录中;
        3.触发器:与某个表关联并在该表发生特定事件时激活的命名数据库对象;
    5.数据目录的位置取决于配置,操作系统,安装包和分发;典型位置是/var/lib/mysql;
    6.MySQL在磁盘上存储系统数据库(mysql),mysql包含诸如用户/特权/插件/帮助列表/事件/时区实现和存储例程之类的信息;
— MySQL如何使用内存;
1.内存分配可以划分为以下两种类别:
    1.全局(每实例内存):服务器启动时分配一次并在服务器关闭时释放,此内存在所有会话间共享;当所有物理内存用尽时,操作系统开始交换,这会对MySQL服务器性能具有不利影响,可能会导致服务器崩溃;
    2.会话(每会话内存):基于每个会话(有时称为“线程”)动态进行分配;此内存可在会话结束时或不再需要会话时释放,此内存多用于处理查询结果,所使用的缓冲区大小基于每个连接;例如,read_buffer为10MB且具有100个连接意味着可能总共有100*10MB同时用于所有读取缓冲区;
— 内存结构;
服务器在运行时会为许多种类的数据分配内存:
1.查询高速缓存还用于加速处理重复发出的查询;
2.线程高速缓存:在MySQL(和其他程序)中使用线程将应用程序执行划分为两个或更多个同时运行的任务,将会为连接到MySQL服务器的每个客户机创建单独的线程以处理该连接;
3.缓冲区和高速缓存:缓冲区和高速缓存提供数据管理子系统并支持快速访问项目,例如授权表缓冲区,存储引擎缓冲区(如InnoDB的日志缓冲区)和保存开放表说明符的表开放缓冲区;如果使用MEMORY存储引擎,MySQL将使用主内存作为主体数据存储,其他存储引擎也可能使用主内存进行数据存储,但MEMORY是唯一的,未设计为在磁盘上存储数据;
4.连接/会话:
    1.内部临时表:在某些查询执行情况下,MySQL会创建一个临时表来解析查询;可以在内存中或在磁盘上创建临时表,具体取决于其大小或内容或者查询语法;
    2.特定于客户机的缓冲区:专门设计为支持所连接的各个客户机;缓冲区示例包括:
        1.用于交换信息的通信缓冲区;
        2.排序操作:表读取缓冲区(包括支持联接的缓冲区);
— MySQL插件接口;
1.当前,插件API支持:
    1.可用于替换或扩充内置全文解析器的全文解析器插件;例如,某个插件可以使用不同于内置解析器所使用的规则将文本解析为字,要解析具有不同于内置解析器所预期的特征的文本,这很有用;
    2.向服务器提供低级别存储,检索和数据索引的存储引擎;
    3.信息模式插件;信息模式插件作为MySQL INFORMATION_SCHEMA数据库中的表出现,稍后将更详细地讨论INFORMATION_SCHEMA数据库;
    4.守护进程插件启动在服务器内运行的后台进程(例如,定期执行心跳处理);
2.插件接口需要mysql数据库中的PLUGINS表,此表是在MySQL安装过程中创建的;
— 小结;

MySQL OCP-01-MySQL简介

Posted on by
— 课程目标;
MySQL OCP主要是有以下7个方面的内容:
1.MySQL体系架构:
    1.使用MySQL客户端程序和MySQL服务端进行交互和批处理;
    2.描述MySQL如何使用磁盘和内存资源;
    3.描述MySQL标准存储引擎(InnoDB, NDB, MyISAM, MEMORY, FEDERATED等)的关键特征;
2.MySQL服务器的安装,配置和维护:
    1.使用合适的二进制包为Windows和Linux平台选择/部署/启动和关闭MySQL;
    2.使用配置文件,命令行选项和服务端变量来配置MySQL服务端;
    3.介绍MySQL的错误日志/二进制日志/一般日志和慢查询日志的作用/配置/位置和使用场景;
3.MySQL安全:
    1.描述如何在操作系统/文件系统和网络级别安全部署MySQL;
    2.使用合适的权限和配置创建和维护用户账户;
    3.部署和配置MySQL企业审计;
4.在MySQL中诊断数据和元数据:
    1.配置和使用PERFORMANCE_SCHEMA表来定位和诊断性能问题;
    2.从INFORMATION_SCHEMA表中获得MySQL元数据;
5.性能调优:
    1.展示诊断和优化低性能查询语句的能力;
    2.调整MySQL服务端的配置从而达到最大性能;
    3.使用最佳实践来优化对象;
    4.理解MySQL服务器和不同引擎中锁的概念;
    5.创建和使用表分区;
6.备份恢复:
    1.使用mysqldump创建和还原逻辑备份;
    2.创建和还原二进制备份;
    3.使用备份做数据恢复;
7.MySQL的高可用性技术:
    1.描述/配置/部署和排错MySQL复制;
    2.描述和对比常见的MySQL高可用解决方案(MySQL Cluster, DRBD, Windows Failover Clustering, Solaris Cluster, OVM Template for MySQL Enterprise);
— 课程表;
1.一共有19个章节,分5天讲完;
2.除了PPT上面的内容之外,还会有一些补充的习题和练习;
3.所以需要稍微记录一些笔记,然后把相关的内容课堂中和课后做反复练习;
— 简介;
每个人都介绍一下自己的[姓名, 公司, 职位, 学习MySQL的原因和之后的一个期望]吧;
— 到处可见的 MySQL;
1.全球最普及的开源数据库
    1.打开MySQL的官网(http://www.mysql.com/),上面就写到,这是世界上最流行的开源数据库;The world’s most popular open source database;
    2.与之对比的另外一款开源数据库pg,打开pg的官网(http://www.postgresql.org/),上面就写到,这是世界上最先进的开源数据库;The world’s most advanced open source database;
2.据估计有超过 1500 万次有效安装:现在会远远超过这个数字;
3.LAMP 组合中的 M:Linux, Apache/Nginx, MySQL, PHP/Python;
4.全球排名前 10 的 Web 站点中有 9 个使用该数据库:在官网下面有列举;
5.被超过3,000家ISV和OEM内嵌
    1.ISV(Independent Software Vendors,独立软件开发商):特指专门从事软件的开发/生产/销售和服务的企业,如微软,甲骨文,红帽等;
    2.OEM(Original Equipment Manufacturers,OEM生产):也称为定点生产,俗称代工,基本含义为品牌生产者不直接生产产品,而是利用自己掌握的关键的核心技术负责设计和开发新产品,控制销售渠道,具体的加工任务通过合同订购的方式委托同类产品的其他厂家生产;
6.领先的云数据库:基本所有的云服务商都会支持MySQL数据库;
7.在社交媒体(Facebook、Twitter 等)中极为流行
— MySQL 对于 Oracle 的意义;
1.MySQL代表了Oracle所提供的同类产品中最出色的,面向基于Web的应用程序的数据库解决方案,它也是嵌入式数据库的不错选择;因此,MySQL使Oracle的产品更为完整,是对Oracle DB的有力补充;Oracle大力投资MySQL的原因是为了提供可驱动下一代Web,移动和嵌入式应用程序的 MySQL解决方案;
2.几个重点:云数据库方面,Web和嵌入式方面的确是几乎垄断地位;
3.Oracle推动MySQL的创新:在某些程度上是对的,从不断的强调定制化到现在的回归社区,而Oracle也一直在把优秀的功能(google的半同步复制和GTID复制)加入到官方版本中;
— MySQL 正在推动世界;
主要是以下几类:
1.互联网公司:Alibaba,Google,Facebook,YouTube;
2.大型企业:at&t,日本新生银行(Shinsei Bank);
3.OEM和ISV;
4.云:AWS,GoDaddy,Google,阿里;
5.目前国内的银行,电信,券商还都是主流用Oracle;国外的一些公司在不考虑Oracle的前提下也在用pg;
— MySQL 数据库服务器版本;
1.MySQL经典版非常适合于嵌入式的读密集型非OLTP应用程序。
2.MySQL标准版和企业版非常适合于需要高性能,高可用性和一致的故障恢复的读密集型应用程序和OLTP 应用程序;
— MySQL 企业版;
1.MySQL企业版包括很多的高级功能,管理工具和技术支持从而实现最高级别的MySQL可伸缩性/安全性/可靠性和正常运行时间;
2.可以提供GPL版本和商业版本的MySQL Workbench;
3.课程所有的内容都是基于企业版讲的,所以之后会讲到这些高级特性;
— MySQL 连接器和 API;
1.MySQL连接器为客户机程序提供到MySQL服务器的连接;API提供对MySQL协议和MySQL资源的低级别访问;使用连接器和API可以从另一语言或环境连接到MySQL并执行MySQL语句;
2.MySQL支持的第三方连接器包括:
    1.PHP:mysqli,ext/mysqli,PDO_MYSQLND,PHP_MYSQLND;
    2.Perl:DBD::mysql;
    3.Python:MySQLdb;
    4.Ruby:DBD::MySQL,ruby-mysql;
3.还支持嵌入式MySQL服务器库(libmysqld),使用libmysqld可以在客户机应用程序中运行全功能的 MySQL服务器;其主要优点是对于嵌入式应用程序提高了速度并简化了管理;
4.相关地址;http://www.mysql.com/products/connector/;
— MySQL 服务;
— 社区支持;
1.http://lists.mysql.com/:关于服务端的,版本Bug的,参考文档的讨论;还有一些用户组的信息;
2.http://planet.mysql.com/:MySQL Team的一些人会发一些相关的文章;
— Oracle Premier Support for MySQL;
1.Oracle标准支持服务;
2.热修复(维护发行版,错误修复,修补程序和更新);
— MySQL 支持的操作系统;
http://www.mysql.com/support/supportedplatforms/database.html
MySQL.com->Services->Support;
— MySQL Web 站点;
— MySQL 课程覆盖内容;
1.ILT(Instructor-Led Training,由教师授课的培训):教师和学生同时坐在教室中授课;
2.LVC(Live Virtual Class,实时虚拟课堂):用实时视频和音频通过基于Web的交付系统(WebEx)进行授课,位于不同地理位置的教师和学生在虚拟课堂环境中参与课程并进行交互和协作;
3.TOD(Training On Demand,按需培训):对传统的教室培训(包括所有课堂内容,如讲义,白板和实验室视频等)进行先期录音,然后以视频的形式在线提供,以便用户可以在方便时开展定制培训;
— MySQL 认证;
— MySQL 联机文档;
— 示例数据库;
— 小结;

ACOUG Asia Tour – MySQL Taipei

Posted on by

Name of Event: ACOUG Asia Tour – MySQL Taipei
Date & Time: 5/9/2016 13:00-15:00 Asia/Taipei
Address of the Venue: Oracle Education Center, 35F, 66, Sec. 1, Chung Hsiao W. Rd., Taipei 10018, Taiwan, R.O.C
Language: Chinese
Type of the Event: Meeting
Url and other details:https://community.oracle.com/people/royalwzy/blog/2016/02/15/acoug-asia-tour-mysql
Contact:royalwzy, sonne.k.wang@gmail.com
Registration:https://jinshuju.net/f/dB1ZgE

ACOUG Asia Tour – MySQL Tokyo

Posted on by

Name of Event: ACOUG Asia Tour – MySQL Tokyo
Date & Time: 4/6/2016 15:00-17:00 Asia/Tokyo
Address of the Venue: Mitsui Garden Hotel Ginza Premier, 8-13-1 Ginza, Chuo-ku, Tokyo, 104-0061, Japan
Language: English
Type of the Event: Meeting
Url and other details:https://community.oracle.com/people/royalwzy/blog/2016/02/15/acoug-asia-tour-mysql
Contact:royalwzy, sonne.k.wang@gmail.com
Registration:https://jinshuju.net/f/0GZhsZ

ACOUG Asia Tour – MySQL Shanghai

Posted on by

Name of Event: ACOUG Asia Tour – MySQL Shanghai
Date & Time: 3/11/2016 15:00-17:00 Asia/Shanghai
Address of the Venue: Room404, Haitong Securities Tower, 689 Guangdong Road, Shanghai, 200001, P.R.China
Language: Chinese
Type of the Event: Meeting
Url and other details:https://community.oracle.com/people/royalwzy/blog/2016/02/15/acoug-asia-tour-mysql
Contact:royalwzy, sonne.k.wang@gmail.com
Registration:https://jinshuju.net/f/J3Pu6u

MySQL压力测试

Posted on by

主要内容:
1.MySQL压力测试基准值;
2.mysqlslap的介绍及使用;
3.sysbench的介绍及使用;
4.tpcc-mysql的介绍及使用;

MySQL压力测试基准值
1.压力测试的目的:
1.确认新的MySQL版本性能相比之前差异多大;比如从5.6变成5.7,或者从官方版本改成Percona分支版本;
2.确认新的服务器性能是否更高,能高多少;比如CPU升级了,阵列卡cache加大了,从机械盘换成SSD盘了;
3.确认一些新的参数调整后,对性能影响多少;比如innodb_flush_log_at_trx_commit,sync_binlog等参数;
4.确认即将上线的新业务对MySQL负载影响多少,是否能承载得住,是否需要对服务器进行扩容或升级配置;
2.针对不同的压测目的,相应的测试方法也有所不同:
1.先说第四种,需要和线上业务结合起来,这时候就需要自行开发测试工具,或者利用tcpcopy(https://github.com/session-replay-tools/tcpcopy)将线上实际用户请求导向测试环境,进行仿真模拟测试;
2.对于前三种,我们通常采用基准测试就可以;比较常用的MySQL基准压力测试工具有tpcc-mysql,sysbench,mysqlslap等几个;
3.如何避免压测时受到缓存的影响:
1.填充测试数据比物理内存还要大,至少超过innodb_buffer_pool_size值,不能将数据全部装载到内存中,除非你的本意就想测试全内存状态下的MySQL性能;
2.每轮测试完成后,都重启mysqld实例,并且用下面的方法删除系统cache,释放swap(如果用到了swap的话),甚至可以重启整个OS;
1.将脏数据刷新到磁盘:sync;
2.清除OS Cache:echo 3 > /proc/sys/vm/drop_caches
3.释放swap:swapoff -a && swapon -a;
4.如何尽可能体现线上业务真实特点:
1.其实上面已经说过了,就是自行开发测试工具或者利用tcpcopy(或类似交换机的mirror功能)将线上实际用户请求导向测试环境,进行仿真模拟测试;
2.利用http_load或siege工具模拟真实的用户请求URL进行压力测试;
5.压测结果如何解读:
1.压测结果除了tps/TpmC指标外,还应该关注压测期间的系统负载数据,尤其是iops,iowait,svctm,%util,每秒I/O字节数(I/O吞吐),事务响应时间(tpcc-mysql/sysbench打印的测试记录中均有);另外,如果I/O设备能提供设备级IOPS,读写延时数据的话,也应该一并关注;
2.假如两次测试的tps/TpmC结果一样的话,那么谁的事务响应时间,iowait,svctm,%util,读写延时更低,就表示那个测试模式有更高的性能提升空间;
6.如何加快tpcc_load加载数据的效率:
1.tpcc_load其实是可以并行加载的,一方面是可以区分ITEMS,WAREHOUSE,CUSTOMER,ORDERS四个维度的数据并行加载;
2.另外,比如最终想加载1000个warehouse的话,也可以分开成1000个并发并行加载的;
3.github上有并行加载的项目tpcc_load_parallel.sh,加载效率至少提升10倍以上;

mysqlslap的介绍及使用
1.mysqlslap简介:
1.它是MySQL从5.1.4版开始自带的一个基准测试工具,该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较;
2.mysqlslap为MySQL性能优化前后提供了直观的验证依据,从而帮助DBA准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题;
3.查看帮助:mysqlslap –help
4.使用方法:mysqlslap [OPTIONS];
5.默认的读取配置文件的顺序:/etc/my.cnf,/etc/mysql/my.cnf,/usr/local/mysql/etc/my.cnf,~/.my.cnf;
2.参数说明:
–defaults-file:指定从配置文件中读取选项配置;
-?, –help:查看帮助;
-a, –auto-generate-sql:自动生成测试表和数据;
–auto-generate-sql-add-autoincrement:自动生成的表中添加自增列;
–auto-generate-sql-execute-number=#:自动生成的查询的个数;
–auto-generate-sql-guid-primary:自动在表上添加基于GUID的主键;
–auto-generate-sql-load-type=name:指定测试负载的类型,取值包括:mixed(默认,一半插入一半查询),update,write,key(读主键)和read;
–auto-generate-sql-secondary-indexes=#:在自动生成的表上添加第二索引的个数,默认0;
–auto-generate-sql-unique-query-number=#:生成基于唯一键查询的测试语句数量,默认10;
–auto-generate-sql-unique-write-number=#:生成基于唯一键写的测试语句数量,默认100;
–auto-generate-sql-write-number=#:每个线程执行插入的记录数量,默认100;
–commit=#:多少条DML后提交一次;
-C, –compress:如果服务器和客户端支持都压缩,则压缩信息传递;
-c, –concurrency=name:模拟查询的客户端并发数量,多个可以使用逗号分割;
–create=name:创建表的SQL语句或者文本文件;
–create-schema=name:指定测试的数据库,不指定就自动创建一个mysqlslap的schema;
–csv[=name]:生成CSV格式的结果,如果不指定名字就打印到标准输出,指定名字就打印到指定的文件中;
-#, –debug[=#]:指定debug模式;
–debug-check:退出时检查相关的内存和打开文件使用信息;
-T, –debug-info:额外输出CPU以及内存的相关信息;
–default-auth=name:默认的客户端插件验证文件;
-F, –delimiter=name:定义SQL使用的分隔符;
–detach=#:执行N条语句后断开重连;
–enable-cleartext-plugin:开启/禁用明文验证插件,具体查看MySQL几种身份验证方式;
-e, –engine=name:创建测试表使用的引擎,多个引擎用逗号分割;
-h, –host=name:连接到的服务器信息;
-i, –iterations=#:运行测试的次数;
–no-drop:在测试完成之后不删除测试表;
-x, –number-char-cols=name:测试表中VARCHAR类型列的个数,默认1个,仅当指定–auto-generate-sql参数时有效;
-y, –number-int-cols=name:测试表中VARCHAR类型列的个数,默认1个,仅当指定–auto-generate-sql参数时有效;
–number-of-queries=#:总的测试查询次数(并发客户数×每客户查询次数);
–only-print:不连接到数据库,只打印相应的SQL语句,即测试过程的SQL语句;
-p, –password[=name]:密码信息;
–plugin-dir=name:客户端插件目录;
-P, –port=#:连接的端口号;
–post-query=name:在测试脚本运行后执行的脚本,这个过程不影响时间计算;;
–post-system=name:在测试脚本运行后执行的系统命令;
–pre-query=name:在测试脚本运行前执行的脚本;
–pre-system=name:在测试脚本运行前执行的系统命令;
–protocol=name:用户连接时使用的协议,eg:tcp,socket,pipe,memory;
-q, –query=name:用自定义脚本执行测试
–secure-auth:安全验证,拒绝客户端使用旧的协议连接服务器,默认开启,但是可以指定–skip-secure-auth参数来关闭;
-s, –silent:静默模式下运行程序;
-S, –socket=name:连接时指定socket文件;
–ssl:连接时开启SSL;
-u, –user=name:用户名信息;
-v, –verbose:输出信息的详细的级别,eg:-vvv;
-V, –version:版本信息;
3.查看测试过程:mysqlslap -a –only-print;
4.测试实例(多线程测试):
mysqlslap -uroot -p –concurrency=100 –iterations=1 –auto-generate-sql –auto-generate-sql-load-type=mixed –auto-generate-sql-add-autoincrement –engine=InnoDB –number-of-queries=10 –debug-info
说明:测试100个并发线程;测试次数1次;自动生成SQL测试脚本;读,写,更新混合测试;自增长字段;测试引擎为InnoDB;共运行10次查询;输出CPU资源信息;
5.测试实例(迭代测试,多次测试得出平均值):
mysqlslap -uroot -p –concurrency=50,100,200 –iterations=20 –number-int-cols=4 –number-char-cols=35 –auto-generate-sql –auto-generate-sql-load-type=mixed –auto-generate-sql-add-autoincrement –engine=MyISAM,InnoDB –number-of-queries=200 –debug-info -v
说明:测试50,100,200个并发线程;测试次数20次;四个int列和35个varchar列;自动生成SQL测试脚本;读,写,更新混合测试;自增长字段;测试引擎为M有ISAM,InnoDB;共运行200次查询;输出CPU资源信息;想要输出所有信息加-vvv;
TIPS:并发数越多,执行完所有查询的时间越长,为了准确起见,可以多迭代测试几次;
6.总结:
1.无法规定表中测试数据的数量;
2.可以使用压力测试工具super-smack for MySQL,但是却一直没有人维护了;

— 补充:
MySQL的几种身份验证方式:MySQL主要是用自身的User Table作身份认证,但可以加入其它plugin authentication;
1.The Native Authentication Plugins;
2.The PAM Authentication Plugin;
3.The Windows Native Authentication Plugin;
4.The Clear-Text Client-Side Authentication Plugin:在客户端发送明文给服务端验证,http://dev.mysql.com/doc/refman/5.5/en/cleartext-authentication-plugin.html;
5.The Socket Peer-Credential Authentication Plugin;
6.The Test Authentication Plugin;

sysbench的介绍及使用
1.sysbench简介:
1.sysbench是一个模块化的,跨平台,多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况;
2.sysbench的目的是1)不需要设置复杂的数据库基准测试,2)甚至根本不需要安装一个数据库的情况下,可以快速了解系统性能状况;
3.目前sysbench代码托管在github上(https://github.com/akopytov/sysbench),在之前还经历了由sourceforge(http://sysbench.sourceforge.net)到launchpad(https://launchpad.net/sysbench)的迁移;
4.最新版本为0.5;
5.使用C语言写的一个工具;
2.sysbench支持以下几种测试模式:
1.CPU运算性能;
2.磁盘IO性能;
3.调度程序性能;
4.内存分配及传输速度;
5.POSIX线程性能;
6.数据库性能(OLTP基准测试),eg:MySQL,Drizzle(精简版的MySQL分支),PostgreSQL,Oracle等几种数据库;
3.安装sysbench:
1.安装依赖包:sysbench依赖automake和libtool两个包,yum -y install automake libtool;
2.解压缩:unzip sysbench-0.5.zip;
3.自动配置:./autogen.sh;
4.指定编译配置:./configure –prefix=/usr/local/sysbench –with-mysql-includes=/usr/local/mysql/include –with-mysql-libs=/usr/local/mysql/lib;默认支持MySQL,如果需要测试Oracle/PostgreSQL,则在configure时需要加上–with-oracle或者–with-pgsql参数;如果想要默认不支持MySQL,需要添加–without-mysql参数;
5.编译安装:make && make install;
6.修改PATH环境变量;
7.报错修复,[./sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory],是因为sysbench无法找到mysql的库文件,可能是环境变量LD_LIBRARY_PATH没有设置,export LD_LIBRARY_PATH=/usr/local/mysql/lib即可;
4.参数介绍:
1.使用方法:sysbench [general-options]… –test= [test-options]… command;
2.一般选项:
–num-threads=N:使用的测试线程数,默认为1;
–max-requests=N:最大请求数量,默认为1W,0表示不限制;
–max-time=N:最大执行时间,单位是s,默认0为不限制;与–max-requests二选一;
–forced-shutdown=STRING:超过–max-time指定时间后强制中断,默认为off;
–thread-stack-size=SIZE:每个线程使用的栈大小,默认是64K;
–tx-rate=N:target transaction rate (tps) [0];
–report-interval=N:阶段性的报告中间统计信息,单位为s,默认0为禁用此阶段性的报告;
–report-checkpoints=[LIST,…]:在指定的时间点,导出所有的统计信息,并且重置所有的计数器;可以指定用逗号分割的列表,单位是s,默认是关闭的;
–percentile=N:表示设定采样比例,默认是95%;可以设置99%即丢弃1%的长请求,在剩余的99%里取最大值;
–test=STRING:指定测试的项目;
–debug=[on|off]:是否打印调试信息,默认为off;
–validate=[on|off]:在可能的情况下执行验证检查,默认为off;
–help=[on|off]:查看帮助信息;
–version=[on|off]:打印版本信息;
–rand-init=[on|off]:测试开始时是否初始化随机数生成器,默认为off;
–rand-type=STRING:随机数分布模式,eg:uniform(固定),gaussian(高斯),special(特定的,默认),pareto(帕累托);
1.special表示存在热点数据;
2.uniform表示非热点数据模式;
–rand-spec-iter=N:数字生成器中使用迭代器的数量,默认是12;
–rand-spec-pct=N:special类型数据的比率,默认是1;
–rand-spec-res=N:special值使用的比率,默认是75;
–rand-seed=N:随机数生成器的种子,默认为0;
–rand-pareto-h=N:pareto数据分布模式下参数h的值,默认是0.2;
3.日志选项:
–verbosity=N:日志级别,5是debug级别,0是只打印严重错误信息,默认为3;
–percentile=N: percentile rank of query response times to count [95]
4.测试的项目:
1.fileio – File I/O test
2.cpu – CPU performance test
3.memory – Memory functions speed test
4.threads – Threads subsystem performance test
5.mutex – Mutex performance test
6.oltp – OLTP test,在0.5版本之前有,已被替换掉;
5.命令:
1.prepare:执行测试前的准备工作;比如fileio测试时在磁盘上创建必要的文件,或者在oltp测试时填充测试数据库;
2.run:执行真实的测试;
3.cleanup:测试后移除运行时产生的临时数据;
4.help:查看指定测试项目的帮助;eg:sysbench –test=cpu help;
5.CPU测试:
1.相关选项:
–cpu-max-prime=N:最大质数发生器数量,即产生N个素数,默认为10000;
2.测试计算产生2000个素数所需要的时间:sysbench –test=cpu –cpu-max-prime=2000 run;
3.查看CPU相关信息:less /proc/cpuinfo;
6.线程(threads)测试:
1.测试线程调度器的性能,对于高负载情况下测试线程调度器的行为非常有用;
2.相关选项:
–thread-yields=N:每个请求产生多少个线程,默认为1000;
–thread-locks=N:每个线程的锁的数量,默认为8;
3.使用64个线程,每个请求产生100个线程,线程锁数为2:sysbench –test=threads –num-threads=64 –thread-yields=100 –thread-locks=2 run;
7.文件IO性能测试:
1.相关选项:
–file-num=N:创建测试文件的数量,默认为128;
–file-block-size=N:测试时文件块的大小,默认是16384(16K);
–file-total-size=SIZE:测试文件的总大小,默认是2G;
–file-test-mode=STRING:文件测试模式,eg:seqwr(顺序写),seqrewr(顺序读写),seqrd(顺序读),rndrd(随机读),rndwr(随机写),rndrw(随机读写);
–file-io-mode=STRING:文件操作模式,eg:sync(同步,默认),async(异步),fastmmap(快速map映射),slowmmap(慢map映射);
–file-extra-flags=STRING:使用额外的标志来打开文件,eg:sync,dsync,direct;默认为空;
–file-fsync-freq=N:执行fsync()的频率,默认是100,0表示不使用fsync();
–file-fsync-all=[on|off]:每执行完一次写操作就执行一次fsync,默认是off;
–file-fsync-end=[on|off]:在测试结束时才执行fsync,默认是on;
–file-fsync-mode=STRING:使用哪种方法进行同步,eg:fsync(默认),fdatasync;
–file-merged-requests=N:如果可以,合并最多的IO请求数,默认是0表示不合并;
–file-rw-ratio=N:测试时的读写比例,默认是1.5;
2.测试:文件总大小为2G,20个并发线程,随机读写;
1.prepare阶段:sysbench –test=fileio –num-threads=20 –file-total-size=2G –file-test-mode=rndrw prepare
2.run阶段:sysbench –test=fileio –num-threads=20 –file-total-size=2G –file-test-mode=rndrw run
3.清理测试时生成的文件:sysbench –test=fileio –num-threads=20 –file-total-size=2G –file-test-mode=rndrw cleanup
8.互斥锁(Mutex)测试:
1.相关选项:
–mutex-num=N:数组互斥的总大小,默认是4096;
–mutex-locks=N:每个线程互斥锁的数量,默认是50000;
–mutex-loops=N:内部互斥锁的空循环数量,默认是10000;
2.测试互斥锁的性能,方式是模拟所有线程在同一时刻并发运行,并都短暂请求互斥锁:sysbench –-test=mutex –-num-threads=100 –-mutex-num=1000 –-mutex-locks=100000 –-mutex-loops=10000 run
9.内存测试:
1.相关选项:
–memory-block-size=SIZE:测试时内存块大小,默认是1K;
–memory-total-size=SIZE:传输数据的总大小,默认是100G;
–memory-scope=STRING:内存访问范围,eg:global(默认),local;
–memory-hugetlb=[on|off]:从HugeTLB池内存分配,默认是off;
–memory-oper=STRING: 内存操作类型,eg:read,write(默认),none;
–memory-access-mode=STRING:存储器存取方式,eg:seq(默认),rnd;
2.内存测试测试了内存的连续读写性能:sysbench –test=memory –memory-block-size=8k –memory-total-size=1G run
10.MySQL数据库测试:
1.sysbench v0.5通过一系列LUA脚本来替换之前的oltp,从而来模拟更接近真实的基准测试环境;
2.脚本在//sysbench-0.5/sysbench/tests/db/目录下,这些测试脚本包含:insert.lua,oltp.lua,parallel_prepare.lua,select_random_points.lua,update_index.lua,delete.lua,oltp_simple.lua,select.lua,select_random_ranges.lua,update_non_index.lua,脚本使用方式基本类似;
3.sysbench v0.5默认使用sbtest库,但是需要自己手工先创建好,也可以使用–mysql-db指定,其他非默认项指定选项:
–mysql-host=[LIST,…]:MySQL服务器地址,默认为localhost;
–mysql-port=N:MySQL服务器端口号,默认为3306;
–mysql-socket=STRING:MySQL socket;
–mysql-user=STRING:MySQL用户,默认为sbtest;
–mysql-password=STRING:MySQL密码;
–mysql-db=STRING:MySQL数据库名称,默认为dbtest;
–mysql-table-engine=STRING:使用的引擎,eg:myisam,innodb(默认),bdb,heap,ndbcluster,federated;
–mysql-engine-trx=STRING:存储引擎是否使用事务,eg:yes,no,auto(默认);
–mysql-ssl=[on|off]:是否使用ssl连接,默认为off;
–mysql-create-options=:创建脚本是否添加CREATE TABLE,默认为空;
4.准备阶段:生成表并插入数据,可使用parallel_prepare.lua脚本来并行准备数据;
–db-driver:服务器类型,eg:mysql(默认),drizzle;
–mysql-table-engine:表存数引擎;
–myisam-max-rowsMyISAM引擎表的最大记录数,默认为100W;
–oltp-table-count:生成表数量,名字为:sbtest1、sbtest2…;
–oltp-table-size:生成表的行数;
–oltp-secondary:ID列生成二级索引而不是主键;
–oltp-auto-inc=[on|off]:设置ID列是否自增,默认为on;
5.运行阶段:
–oltp-tables-count=N:表的数量;
–oltp-read-only=[on|off]:是否仅执行SELECT测试;默认为off,表示不要进行只读测试,也就是会采用读写混合模式测试;
–oltp-dist-type:随机数分布状态,eg:uniform(均匀分布),gauss(高斯分布),special(特殊分布);
–oltp-dist-pct:特殊分布的百分比值;
–oltp-dist-res:特殊分布的百分比;
–oltp-point-selects:单个事务中指定的selec查询个数;
–oltp-range-size:范围查询的范围大小,该值应比oltp-table-size小;
–oltp-simple-ranges:单个事务中指定的简单范围查询个数;
–oltp-sum-ranges:单个事务中指定的SUM范围查询个数;
–oltp-order-ranges:单个事务中指定的ORDER范围查询个数;
–oltp-distinct-ranges:单个事务中指定的DISTINCT范围查询个数;
–oltp-index-updates:单个事务中指定的使用索引更新的个数;
–oltp-non-index-updates:单个事务中指定的不使用索引更新的个数;
6.清理阶段;
7.测试:
1.准备(方法1):sysbench –mysql-db=test –mysql-user=root –mysql-password= –test=/tools/sysbench-0.5/sysbench/tests/db/oltp.lua –mysql-table-engine=innodb –oltp-table-size=100000 prepare
2.准备(方法2,准备数据后运行):也可使用parallel_prepare.lua脚本并行准备测试数据,线程数应该为运行表的倍数;sysbench –mysql-db=sbtest –mysql-user=root –mysql-password= –test=/tools/sysbench-0.5/sysbench/tests/db/parallel_prepare.lua –mysql-table-engine=innodb –oltp-table-size=100000 –num-threads=10 –oltp-tables-count=10 run
3.运行:sysbench –mysql-db=test –mysql-user=root –mysql-password= –test=/tools/sysbench-0.5/sysbench/tests/db/oltp.lua –num_threads=10 –oltp-tables-count=10 –oltp-table-size=100000 –num-threads=8 –report-interval=10 –rand-type=uniform –max-time=100 –percentile=99 run
4.清理:sysbench –mysql-db=test –mysql-user=root –mysql-password= –test=/tools/sysbench-0.5/sysbench/tests/db/oltp.lua –oltp-table-size=100000 –oltp-tables-count=10 cleanup
8.可以对数据库进行调优后,再使用sysbench对OLTP进行测试,看看TPS是不是会有所提高;
9.TIPS:
1.sysbench的测试只是基准测试,并不能代表实际企业环境下的性能指标;
2.真实测试场景中,数据表建议不低于10个,单表数据量不低于500万行,当然了,要视服务器硬件配置而定;如果是配备了SSD或者PCIE SSD这种高IOPS设备的话,则建议单表数据量最少不低于1亿行;
3.真实测试场景中,建议持续压测时长不小于30分钟,否则测试数据可能不具参考意义;
10.参考文档:https://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua

— 运行报告解读;
Threads started!
— 每10秒钟报告一次测试结果:tps,每秒读,每秒写,99%以上的响应时长统计;
[ 10s] threads: 8, tps: 1111.51, reads/s: 15568.42, writes/s: 4446.13, response time: 9.95ms (99%)
[ 20s] threads: 8, tps: 1121.90, reads/s: 15709.62, writes/s: 4487.80, response time: 9.78ms (99%)
[ 30s] threads: 8, tps: 1120.00, reads/s: 15679.10, writes/s: 4480.20, response time: 9.84ms (99%)
[ 40s] threads: 8, tps: 1114.20, reads/s: 15599.39, writes/s: 4456.30, response time: 9.90ms (99%)
[ 50s] threads: 8, tps: 1114.00, reads/s: 15593.60, writes/s: 4456.70, response time: 9.84ms (99%)
[ 60s] threads: 8, tps: 1119.30, reads/s: 15671.60, writes/s: 4476.50, response time: 9.99ms (99%)
OLTP test statistics:
queries performed:
read: 938224 — 读总数
write: 268064 — 写总数
other: 134032 — 其他操作总数(SELECT,INSERT,UPDATE,DELETE之外的操作,例如COMMIT等)
total: 1340320 — 全部总数
transactions: 67016 (1116.83 per sec.) — 总事务数(每秒事务数)
deadlocks: 0 (0.00 per sec.) — 发生死锁总数
read/write requests: 1206288 (20103.01 per sec.) — 读写总数(每秒读写次数)
other operations: 134032 (2233.67 per sec.) — 其他操作总数(每秒其他操作次数)

General statistics: — 一些统计结果
total time: 60.0053s — 总耗时
total number of events: 67016 — 共发生多少事务数
total time taken by event execution: 479.8171s — 所有事务耗时相加(不考虑并行因素)
response time: — 响应时长统计
min: 4.27ms — 最小耗时
avg: 7.16ms — 平均耗时
max: 13.80ms — 最长耗时
approx. 99 percentile: 9.88ms — 超过99%平均耗时

Threads fairness:
events (avg/stddev): 8377.0000/44.33
execution time (avg/stddev): 59.9771/0.00

tpcc-mysql的介绍及使用
1.tpcc-mysql简介:
1.TPC-C是专门针对联机交易处理系统(OLTP系统)的规范,一般情况下我们也把这类系统称为业务处理系统;
2.tpcc-mysql是percona基于TPC-C(下面简写成TPCC)衍生出来的产品,专用于MySQL基准测试;
3.tpcc-mysql通过模拟一个电商的业务,主要包含的业务有新增订单,库存查询,发货,支付等模块的测试,用来测试典型的复杂OLTP系统的性能;
4.其源码放在launchpad上,用bazaar管理,项目地址:https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql;
2.安装:
1.解压缩:gunzip tpcc-mysql-src.tgz;tar -xvf tpcc-mysql-src.tar;
2.安装:cd /path/tpcc-mysql/src; make; 之后会在/path/tpcc-mysql目录下生成两个tpcc 命令行工具:tpcc_load,tpcc_start;
1.tpcc_load:提供初始化数据的功能;
2.tpcc_start:进行压力测试;
3.移动工具位置:mv /tools/tpcc-mysql/tpcc_start /tools/tpcc-mysql/tpcc_load /usr/local/bin/;
3.查看帮助:
1.tpcc_load –help:
usage: tpcc_load [server] [DB] [user] [pass] [warehouse]
OR
tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]

* [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS
1.Server:服务器名;
2.DB:数据库名;
3.user:用户名;
4.pass:密码;
5.warehouse:测试库下仓库的数量;
6.part:tpcc_load其实是可以并行加载的,可以区分ITEMS/WAREHOUSE/CUSTOMER/ORDERS四个维度的数据并行加载(github上已经实现的并行加载的项目tpcc_load_parallel.sh:https://gist.github.com/sh2/3458844);
2.tpcc_start –help:
Usage: tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file
1.-h server_host:服务器名;
2.-P port:端口号,默认为3306;
3.-d database_name:数据库名;
4.-u mysql_user:用户名;
5.-p mysql_password:密码;
6.-w warehouses:仓库的数量;
7.-c connections:线程数,默认为1;
8.-r warmup_time:预热时间,预热是为了将数据加载到内存,单位s,默认为10s;
9.-l running_time:测试时间,单位s,默认为20s;
10.-i report_interval:指定生成报告间隔时长;
11.-f report_file:测试结果输出文件;
12.-t trx_file:事务文件;
4.测试前准备工作:
1.创建测试数据库:mysql -uroot -p -e “CREATE DATABASE tpcc”;
2.创建表:mysql -uroot -p tpcc < /tools/tpcc-mysql/create_table.sql; 3.添加外键:mysql -uroot -p tpcc < /tools/tpcc-mysql/add_fkey_idx.sql; 4.查看tpcc-mysql的业务逻辑及其相关的几个表作用如下: 1.customer:客户表; 2.district:地区表; 3.history:支付信息表; 4.item:商品信息表; 5.new_orders:新订单表,其它信息还是存在orders表中; 6.order_line:发货信息表; 7.orders:订单表; 8.stock:库存信息表; 9.warehouse:仓库表; 5.测试: 1.单进程加载数据:tpcc_load localhost tpcc root "" 5; 2.对5个数据仓库,预热10秒,100个并发连接,运行60秒:tpcc_start -h localhost -d tpcc -u root -p "" -w 5 -c 100 -r 10 -l 60 -i 10 -f rpt_tpcc -t tr; 6.不足: 1.没有自增列作为主键:如果仅作为基准测试问题不大,但和我们实际生产中的设计模式可能有一定区别,相信大多数人还是习惯使用自增列作为主键的; 2.使用外键:个人认为MySQL对外键支持并不是太好,并且一定程度上影响并发性能,因此建议取消外键,仅保留一般的索引; 7.其它: 1.真实测试场景中,仓库数一般不建议少于100个;视服务器硬件配置而定,如果是配备了SSD或者PCIE SSD这种高IOPS设备的话,建议最少不低于1000个; 2.真实测试场景中,建议预热时间不小于5分钟,持续压测时长不小于30分钟,否则测试数据可能不具参考意义; 3.需要注意的是tpcc默认会读取/var/lib/mysql/mysql.sock这个socket文件,如果你的socket文件不在相应路径的话,可以做个软连接;或者通过TCP/IP的方式连接测试服务器; 4.script目录下的一些脚本主要是一些性能数据采集以及分析的; 5.可以使用gunpolt工具画图; -- 测试结果解读; [root@ocp ~]# tpcc_start -h localhost -d tpcc -u root -p "" -w 5 -c 100 -r 10 -l 60 -i 10 -f rpt_tpcc -t tr; *************************************** *** ###easy### TPC-C Load Generator *** *************************************** option h with value 'localhost' option d with value 'tpcc' option u with value 'root' option p with value '' option w with value '5' option c with value '100' option r with value '10' option l with value '60' option i with value '10' option f with value 'rpt_tpcc' option t with value 'tr'
[server]: localhost
[port]: 3306
[DBname]: tpcc
[user]: root
[pass]:
[warehouse]: 5
[connection]: 100
[rampup]: 10 (sec.)
[measure]: 60 (sec.)

RAMP-UP TIME.(10 sec.)

— 预热结束,开始进行压测;
MEASURING START.

— 为了能够清晰的说明以下内容,首先定义一些变量,便于以下的计算和说明:
1.success:执行成功的记录数;
2.late:执行延迟的记录数;
3.pre_success:上一次执行成功的记录数;
4.pre_late:上一次执行失败的记录数;

— 根据以上定义的变量,计算相应字段的结果和说明相应字段的含义:
1.时间间隔内成功的事务(包括成功和延迟的事务):sl=success+late-pre_success-pre_late;
2.时间间隔内延迟的事务:l=late-pre_late;
3.时间间隔内前90%记录(实际为99%)的平均响应时间:rt90;
4.时间间隔内最大的响应时间:max_rt;

— Count New-Order Payment Order-Status Delivery Stock-Level
— #, sl(l):rt90|max_rt , sl(l):rt90|max_rt , sl(l):rt90|max_rt, sl(l):rt90|max_rt, sl(l):rt90|max_rt
— 新订单成功执行压测的次数(推迟执行压测的次数):90%事务的响应时间|本轮测试最大响应时间;

10, 485(0):2.431|3.536, 505(0):0.709|1.550, 49(0):0.331|0.363, 48(0):3.061|3.162, 49(0):5.823|6.181
20, 511(0):2.544|3.928, 517(0):0.813|1.757, 52(0):0.204|0.235, 52(0):2.543|2.725, 52(0):5.964|6.062
30, 516(0):2.109|3.152, 510(0):0.493|1.421, 51(0):0.257|0.415, 50(0):2.684|2.855, 51(0):5.783|5.875
40, 480(2):3.008|8.578, 479(0):1.688|1.882, 47(0):0.241|2.038, 47(0):2.869|3.197, 48(0):6.349|6.550
50, 478(1):2.926|10.509, 481(0):1.080|1.795, 48(0):0.406|1.721, 47(0):2.810|2.877, 48(0):6.247|7.598
60, 434(4):3.783|7.095, 426(0):1.258|1.724, 44(0):0.398|1.789, 45(0):3.080|3.604, 45(0):7.136|8.169

— 压测结束;
STOPPING THREADS……………………………………………………………………………………….

— 第一次结果统计;
— 成功(success,sc)次数,延迟(late,lt)次数,重试(retry,rt)次数,失败(failure,fl)次数;

[0] sc:2897 lt:7 rt:0 fl:0 — New-Order:新订单业务;
[1] sc:2918 lt:0 rt:0 fl:0 — Payment:支付业务统计;
[2] sc:291 lt:0 rt:0 fl:0 — Order-Status:订单状态业务统计;
[3] sc:289 lt:0 rt:0 fl:0 — Delivery:发货业务统计;
[4] sc:293 lt:0 rt:0 fl:0 — Stock-Level:库存业务统计;
in 60 sec.

— 第二次统计结果;

[0] sc:2897 lt:7 rt:0 fl:0
[1] sc:2918 lt:0 rt:0 fl:0
[2] sc:291 lt:0 rt:0 fl:0
[3] sc:289 lt:0 rt:0 fl:0
[4] sc:293 lt:0 rt:0 fl:0

— 下面所有业务逻辑结果都必须为OK才行;
(all must be [OK])
[transaction percentage]
Payment: 43.58% (>=43.0%) [OK] — 支付成功次数(sc + lt)必须大于43.0%,否则结果为NG,而不是OK;
Order-Status: 4.35% (>= 4.0%) [OK]
Delivery: 4.32% (>= 4.0%) [OK]
Stock-Level: 4.38% (>= 4.0%) [OK]

— 响应耗时指标必须超过90%通过才行;
[response time (at least 90% passed)]
New-Order: 99.76% [OK]
Payment: 100.00% [OK]
Order-Status: 100.00% [OK]
Delivery: 100.00% [OK]
Stock-Level: 100.00% [OK]


2904.000 TpmC
— TpmC结果值(每分钟事务数,该值是第一次统计结果中的新订单事务数除以总耗时分钟数,eg:(2897+7)/1 = 2904.000;

MySQL EXPLAIN

Posted on by

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了：

如：

  1. explain select surname,first_name form a,b where a.id=b.id

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

mysql> explain select * from user where user=’root’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

1 row in set (0.00 sec)

SHOW PROCESSLIST介绍和使用

Posted on by

processlist命令的输出结果显示了有哪些线程在运行，可以帮助识别出有问题的查询语句，两种方式使用这个命令。
1.      进入mysql/bin目录下输入mysqladmin processlist;
2.      启动mysql，输入show processlist;
如果有SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。

mysql> show processlist

-> ;

+—-+——-+———————-+——+———+——+——-+——————+

| Id | User  | Host                 | db   | Command | Time | State | Info             |

+—-+——-+———————-+——+———+——+——-+——————+

| 34 | ucjmh | 192.168.56.101:54031 | NULL | Query   |    0 | NULL  | show processlist |

+—-+——-+———————-+——+———+——+——-+——————+

1 row in set (0.00 sec)

mysql> exit

Bye

[root@ucjmh ~]# mysql -uroot -poracle

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 35

Server version: 5.5.41-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show processlist;

+—-+——+———–+——+———+——+——-+——————+

| Id | User | Host      | db   | Command | Time | State | Info             |

+—-+——+———–+——+———+——+——-+——————+

| 35 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |

+—-+——+———–+——+———+——+——-+——————+

1 row in set (0.00 sec)

各列的含义和用途:

id，标识会话id 类似于oracle中的sid，你要kill一个语句的时候很有用。

user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

host列，显示这个语句是从哪个ip的哪个端口上发出的。用来追踪出问题语句的用户。

db列，显示这个进程目前连接的是哪个数据库。

command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。

time列，此这个状态持续的时间，单位是秒。

state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，

info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。Checking table
正在检查数据表（这是自动的）。
Closing tables
正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
Sending data
正在处理Select查询的记录，同时正在把结果发送给客户端。
Sorting for group
正在为GROUP BY做排序。
Sorting for order
正在为ORDER BY做排序。
Opening tables
这个过程应该会很快，除非受到其他因素的干扰。例如，在执Alter TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
正在执行一个Select DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
正在讲符合条件的记录找出来以备更新。它必须在Update要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求.
System lock
正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
Upgrading lock
Insert DELAYED正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录，并且修改它们。
User Lock
正在等待GET_LOCK()。
Waiting for tables
该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
Insert DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table
正在检查数据表（这是自动的）。
Closing tables
正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
Sending data
正在处理Select查询的记录，同时正在把结果发送给客户端。
Sorting for group
正在为GROUP BY做排序。
Sorting for order
正在为ORDER BY做排序。
Opening tables
这个过程应该会很快，除非受到其他因素的干扰。例如，在执Alter TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
正在执行一个Select DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
正在讲符合条件的记录找出来以备更新。它必须在Update要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求.
System lock
正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
Upgrading lock
Insert DELAYED正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录，并且修改它们。
User Lock
正在等待GET_LOCK()。
Waiting for tables
该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
Insert DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。

在mysql的官方文档中列出了所有可能的状态。

8.14.2 General Thread States

  •  After createThis occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. This state is used even if the table could not be created due to some error.
  •  AnalyzingThe thread is calculating a MyISAM table key distributions (for example, forANALYZE TABLE).
  •  checking permissionsThe thread is checking whether the server has the required privileges to execute the statement.
  •  Checking tableThe thread is performing a table check operation.
  •  cleaning upThe thread has processed one command and is preparing to free memory and reset certain state variables.
  •  closing tablesThe thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.
  •  converting HEAP to MyISAMThe thread is converting an internal temporary table from a MEMORY table to an on-disk MyISAM table.
  •  copy to tmp tableThe thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created but before rows are copied into it.
  •  Copying to group tableIf a statement has different ORDER BY and GROUP BY criteria, the rows are sorted by group and copied to a temporary table.
  •  Copying to tmp tableThe server is copying to a temporary table in memory.
  •  Copying to tmp table on diskThe server is copying to a temporary table on disk. The temporary result set has become too large (see Section 8.4.4, “How MySQL Uses Internal Temporary Tables”). Consequently, the thread is changing the temporary table from in-memory to disk-based format to save memory.
  •  Creating indexThe thread is processing ALTER TABLE ... ENABLE KEYS for a MyISAMtable.
  • Creating sort indexThe thread is processing a SELECT that is resolved using an internal temporary table.
  •  creating tableThe thread is creating a table. This includes creation of temporary tables.
  •  Creating tmp tableThe thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation will be Copying to tmp table on disk.
  •  deleting from main tableThe server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving columns and offsets to be used for deleting from the other (reference) tables.
  •  deleting from reference tablesThe server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.
  •  discard_or_import_tablespaceThe thread is processing an ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement.
  •  endThis occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.
  •  executingThe thread has begun executing a statement.
  •  Execution of init_commandThe thread is executing statements in the value of the init_command system variable.
  •  freeing itemsThe thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.
  •  Flushing tablesThe thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
  •  FULLTEXT initializationThe server is preparing to perform a natural-language full-text search.
  •  initThis occurs before the initialization of ALTER TABLE, DELETE, INSERT,SELECT, or UPDATE statements. Actions taken by the server in this state include flushing the binary log, the InnoDB log, and some query cache cleanup operations.

    For the end state, the following operations could be happening:

    • Removing query cache entries after data in a table is changed
    • Writing an event to the binary log
    • Freeing memory buffers, including for blobs
  •  KilledSomeone has sent a KILL statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.
  •  LockedThe query is locked by another query.
  •  logging slow queryThe thread is writing a statement to the slow-query log.
  •  NULLThis state is used for the SHOW PROCESSLIST state.
  •  loginThe initial state for a connection thread until the client has been authenticated successfully.
  •  Opening tables, Opening tableThe thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or aLOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table_cache value is large enough.
  •  optimizingThe server is performing initial optimizations for a query.
  •  preparingThis state occurs during query optimization.
  •  Purging old relay logsThe thread is removing unneeded relay log files.
  •  query endThis state occurs after processing a query but before the freeing itemsstate.
  •  Reading from netThe server is reading a packet from the network.
  •  Removing duplicatesThe query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.
  •  removing tmp tableThe thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.
  •  renameThe thread is renaming a table.
  •  rename result tableThe thread is processing an ALTER TABLE statement, has created the new table, and is renaming it to replace the original table.
  •  Reopen tablesThe thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.
  •  Repair by sortingThe repair code is using a sort to create indexes.
  •  Repair doneThe thread has completed a multi-threaded repair for a MyISAM table.
  •  Repair with keycacheThe repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
  •  Rolling backThe thread is rolling back a transaction.
  •  Saving stateFor MyISAM table operations such as repair or analysis, the thread is saving the new table state to the .MYI file header. State includes information such as number of rows, the AUTO_INCREMENT counter, and key distributions.
  •  Searching rows for updateThe thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
  • Sending dataThe thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
  •  setupThe thread is beginning an ALTER TABLE operation.
  •  Sorting for groupThe thread is doing a sort to satisfy a GROUP BY.
  •  Sorting for orderThe thread is doing a sort to satisfy a ORDER BY.
  •  Sorting indexThe thread is sorting index pages for more efficient access during a MyISAMtable optimization operation.
  •  Sorting resultFor a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.
  •  statisticsThe server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work.
  •  System lockThe thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).
  •  Table lockThe next thread state after System lock. The thread has acquired an external lock and is going to request an internal table lock.
  •  updateThe thread is getting ready to start updating the table.
  •  UpdatingThe thread is searching for rows to update and is updating them.
  •  updating main tableThe server is executing the first part of a multiple-table update. It is updating only the first table, and saving columns and offsets to be used for updating the other (reference) tables.
  •  updating reference tablesThe server is executing the second part of a multiple-table update and updating the matched rows from the other tables.
  •  User lockThe thread is going to request or is waiting for an advisory lock requested with a GET_LOCK() call. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).
  •  Waiting for release of readlockThe thread is waiting for a global read lock obtained by another thread (withFLUSH TABLES WITH READ LOCK) to be released.
  •  Waiting for tables, Waiting for tableThe thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

    This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLEStbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

  •  Waiting on condA generic state in which the thread is waiting for a condition to become true. No specific state information is available.
  •  Waiting to get readlockThe thread has issued a FLUSH TABLES WITH READ LOCK statement to obtain a global read lock and is waiting to obtain the lock.
  •  Writing to netThe server is writing a packet to the network.

[root@ucjmh ~]# cat kill_lock.sql
kill 43;
kill 55;
[root@ucjmh ~]# cat locked.txt
43 root localhost hi_db Query 3825 Waiting for table metadata lock insert into t values(4)
55 root localhost NULL Query 0 NULL show processlist
[root@ucjmh ~]# cat ger_lock.sh
#!/bin/bash
rm -rf locked.txt
rm -rf kill_lock.sql
mysql -uroot -poracle -e “show processlist”|grep -i “Query” >> locked.txt;
for line in $(awk ‘{printf $1″\n”}’ locked.txt)
do
echo “kill ${line};”>>kill_lock.sql
done

MySQL隐式转换

Posted on by

mysql> create table users(num int not null, id varchar(30) not null, password varchar(30) not null, primary key(num));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into users values(1, ‘admin’, ‘ad1234’);

Query OK, 1 row affected (0.00 sec)

mysql> insert into users values(2, ‘wh1ant’, ‘wh1234’);

Query OK, 1 row affected (0.00 sec)

mysql> insert into users values(3, ‘secuholic’, ‘se1234’);

Query OK, 1 row affected (0.00 sec)

mysql> select * from users where id=0;

+—–+———–+———-+

| num | id        | password |

+—–+———–+———-+

|   1 | admin     | ad1234   |

|   2 | wh1ant    | wh1234   |

|   3 | secuholic | se1234   |

+—–+———–+———-+

3 rows in set, 3 warnings (0.00 sec)

mysql> show warnings

-> ;

+———+——+———————————————–+

| Level   | Code | Message                                       |

+———+——+———————————————–+

| Warning | 1292 | Truncated incorrect DOUBLE value: ‘admin’     |

| Warning | 1292 | Truncated incorrect DOUBLE value: ‘wh1ant’    |

| Warning | 1292 | Truncated incorrect DOUBLE value: ‘secuholic’ |

+———+——+———————————————–+

3 rows in set (0.00 sec)

mysql> select * from users where id=’0′;

Empty set (0.00 sec)

mysql> select * from users where 0=id;

+—–+———–+———-+

| num | id        | password |

+—–+———–+———-+

|   1 | admin     | ad1234   |

|   2 | wh1ant    | wh1234   |

|   3 | secuholic | se1234   |

+—–+———–+———-+

3 rows in set, 3 warnings (0.00 sec)

mysql> insert into users values(‘ucjmh’,’ucjmh’,’ucjmh’);

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

+———+——+————————————————————+

| Level   | Code | Message                                                    |

+———+——+————————————————————+

| Warning | 1366 | Incorrect integer value: ‘ucjmh’ for column ‘num’ at row 1 |

+———+——+————————————————————+

1 row in set (0.00 sec)

mysql> select * from users;

+—–+———–+———-+

| num | id        | password |

+—–+———–+———-+

|   0 | ucjmh     | ucjmh    |

|   1 | admin     | ad1234   |

|   2 | wh1ant    | wh1234   |

|   3 | secuholic | se1234   |

+—–+———–+———-+

4 rows in set (0.00 sec)

如果是在oracle中直接会报ora-01722

MySQL查询当前版本

Posted on by

1.使用Mysql -V
[root@ucjmh ~]# mysql -V
mysql Ver 14.14 Distrib 5.5.41, for Linux (i686) using readline 5.1
2.在mysql中输入命令–>status
mysql> status
————–
mysql Ver 14.14 Distrib 5.5.41, for Linux (i686) using readline 5.1

Connection id: 25
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.5.41 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 3 hours 4 min 36 sec

Threads: 2 Questions: 161 Slow queries: 0 Opens: 78 Flush tables: 3 Open tables: 24 Queries per second avg: 0.014
3.在help中找到
[root@ucjmh ~]# mysql –help | grep Distrib
mysql Ver 14.14 Distrib 5.5.41, for Linux (i686) using readline 5.1

4.使用version()函数
mysql> select version();
+———–+
| version() |
+———–+
| 5.5.41 |
+———–+
1 row in set (0.00 sec)