CentOS6.5下MariaDB日志及事物详解和基本操作语句，centos6.5mariadb
MySQL日志类别：
一般查询日志：log，general_log，log_output
慢查询日志：查询执行的时长超过指定的查询，即为慢查询；
错误日志：通常时指错误日志的相关信息,通常用服务器关闭和启动的日志信息，服务器运行过程中的错误信息，还可以记录警告信息。
二进制日志：只是跟修改相关的操作，可以理解为一个重做日志，用于复制的基本凭据；
中继日志：它其实跟复制相关的，与二进制日志几乎相同；
事物日志：随机I/O转换为顺序I/O，一般在两个文件存储，一个存满了就换另外一个存
查询日志：
| log={ON|OFF}：是否记录所有语句的日志信息于一般查询日志文件(general_log) log_output：={TABLE|FILE|NONE}TABLE和FILE可以同时出现，用逗号分隔即可； general_log：是否启用查询日志 general_log_file：定义一般查询日志保存的文件 mysql > SET GOLBAL general_log ={OFF|ON};启用或关闭一般查询日志 mysql > SET GLOGAL log_output=’TABLE’;选项可以控制log的存放方法,table表示以表格的方式存放。 |
慢查询日志：
| long_query_time：10.000000秒,超出这个值的叫慢查询日志 slow_query_log：OFF，设定是否启用慢查询的日志，它的输出位置也取决于log_output={TABLE|FILE|NONE}； slow_query_log_file：www-slow.log：定义日志文件路径及名称 mysql > SET GLOBAL log_output=’FILE,TABLE’;输出以表的形式或文件形式输出。 |
错误日志：
1、服务器启动和关闭过程中的信息；
2、服务器运行过程中的错误信息；
3、事件调度运行一个事件时产生的信息；
4、在复制架构中的从服务器上启动从服务器线程时产生的信息；
log_error = /path/to/error_log_file：指定保存至一个特定目录下的某个文件中
log_warnings = {0|1}：是否记录警告信息于错误日志中
二进制日志：
记录跟修改有关的信息，影响数据潜在的内容的信息，select的是不会被记录到二进制日志的，二进制日志也叫复制日志，默认在数据目录下，专门查看日志的命令是：mysqlbinlog
二进制日志的功用：
1、做时间点恢复，要想从哪个时间点恢复我们可以手动调的，所以这是一个备份恢复的重要工具
2、做复制；
因为我们的MySQL数据是单进程多线程的工作机制，所以他可以同时发起很多们修改的语句，但是我们服务器使用的日志只有一个，那如果同时进行的多个线程的请求都同时往日志文件中写数据，日志文件就成了资源热点，也叫资源征用点，那就会混乱不堪，为了解决这种问题，这里，当我们的线程要往二进制日志文件中写数据的时候，它不是直接写到日志文件中去，还是统一写到日志缓冲区中，由日志缓冲区逐一写入日志文件中去，然而我们的日志文件对于一个很繁忙的服务器来讲，每天都会产生很大的数据量，如果所有的二进制日志数据都写在一个日志文件中，那管理起来就特点的不方便了，也很不合理，如是日志文件丢失，那所有的日志数据信息都丢失了，不应该这么做，那我们就有日志滚动的机制了，二进制日志我们可以自己定义，有两种定义日志滚致力的方法，一种是按大小来定义的，比如说我们定义一个日志文件的大小为1G，那他存储一了大概1G左右就会滚动，使用下一个日志来存放二进制日志数据；另一种可以按时间来定义的，比如说一周滚动一次，或者说一个月，一天滚动一次都可以；或者每次重启服务时或者执行FLUSH
LOGS命令时都会滚动一次日志。
| MariaDB [(none)]> SHOW MASTER STATUS;可以查看当前服务器正在使用的二进制日志文件以及下一下个事件开始时基于的位置Position，如果当前使用的是00004，那么00001、2、3将不会再被使用，因为已经被滚动过去了。 MariaDB [hellodb]> SHOW BINARY LOGS;查看当前系统上所有的的二进制日志文件，其实就是数据目录下mysql-bin.index文件中的信息，这个文件就是保存已经滚动过的日志文件的条目。 MariaDB [hellodb]> flush logs; 清除日志文件的命令：PURGE MariaDB [hellodb]> show binlog events in ‘log_file’; # mysqlbinlog --start-time --stop-time --start- # mysqlbinlog --start-protion=1139 mysql-bin.000001：在命令行中查看某个日志文件的某个位置点往后所记录的数据信息。可以用输出重定向保存到某个文件中去将来用于执行的。 server-id：服务器的身份标识， MariaDB [hellodb]> SELECT VERSION();查看当前数据库的版本 MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 1139;查看某个二进制日志文件中从哪个位置往后发生的事件信息。 MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000006'\G查看某个二进制文件中在所有时间点和结束点等相关的信息。 |
MySQL记录二进制日志的格式有三种：
基于语句：statement，能够保存数据一模一样的就基于语句保存
基于行的：row，比如说插入当前时间，现在插入是当前的时间，过几天插入还是要插入当前的时间吗，当前时间的函数：CURRENT_DATE()，基于行记录的信息更加精确，但有些时候数据理过大，比如更新10000条数据只写一条语句，还基于行记录就要记录1000条。
混合模式：mixed,由MySQL自己判断以什么方式记录日志
二进制日志文件内容格式主要有以下几个记录标志信息：
| # at 1451 #140409 17:40:01 server id 1 end_log_pos 1563 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1397036401/*!*/; /*!40000 ALTER TABLE `classes` DISABLE KEYS */ /*!*/; 记录了事件发生的日期和时间 (140409 17:40:01) 记录了服务器ID server id 1 记录了事件的类型 Query 记录了事件的结束位置，就是事件到哪个地方结束了 (end_log_pos 1563) 记录了原服务器上基于哪个线程生成的ID号 (thread_id=10) MariaDB [hellodb]> SHOW PROCESSLIST;查看某个线程的相关信息及ID号 记录了语句的时间戳和写入二进制日志文件的时间差：单位为秒 exec_time=0，小于1秒为都记为0 记录了错误代码：0表示没有错误 (error_code=0) 记录开始位置，也是结束位置 at 1451 /*!*/:这是注释 |
跟二进制日志文件相关的服务器参数：
| MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%log%';查看服务器日志参数log_bin = {NO|OFF}，还可以指文件路径，指定二进制文件路径存储在什么地方的 sql_log_bin = {NO|OFF} log_bin_trust_function_creators binlog_format = {statement|row|mixed}二进制格式，三种模式的，语句、行、混合模式的。 max_binlog_cache_size =：二进制日志缓冲空间大小，仅用缓冲事物存储引擎相关类的日志。 max_binlog_stmt_cache_size =：跟非事物类的和事物类的共用缓冲区大小。单位是字节 max_binlog_size：二进制日志文件的上限，单位字节，超过上限就会滚动。 |
注意：切勿将二进制日志与数据文件放在同一设备中，这样即提升了性能，又保证数据文件的损坏而导致数据无法恢复的。默认是和数据文件存放在一起的，所以MySQL的很多默认设定并不适合生产环境中的使用，需要我们去调整的。
中继日志：它其实跟复制相关的，与二进制日志几乎相同，只不过它不是用于记录事件的，而是作为读取数据的源并且在本地执行的，当然中继日志是在从服务器上。
| MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%relay%';查看中继日志的相关参数信息 relay_log={空}：是否启用中继日志的 relay_log_purge = {NO|OFF}：做修减的，表示是否自动清理不再需要的中继日志。默认启用 relay_log_space_limit：表示中继日志空间大小是否有限定的，为0是没有限定的 relay_log_recovery：跟中继日志自动恢复相关的 |
MySQL多表查询和子查询：
联结查询：事先将两张或多张表join，根据join的结果进行查询，交叉联结
MariaDB [hellodb]> SELECT * FROM students,classes;交叉联结查询多张表组合成一张表显示。
自然联结：也称为内联结或叫等值联结
等值联结：把两张表的对应字段做等值关联，也叫内联结
条件联结：多表联结后以WHERE为条件的多表联结
外联结：外联络又分为左外联结和右外联结
| 左外联结：只保留出现在左外连接运算之前(就是左边)的关系中的元组；元组就是条目 left_tb LEFT JOIN right_tb ON 连接条件 MariaDB [hellodb]> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID = c.ClassID;：LEFT JOIN左外连接，ON是以什么为条件，左外连接就是以左边的students表为准，只要是左边表存在的都显示，而右表没有的则显示空。 右外联结：只保留出现在右外连接运算之后(就是右边)的关系中的元组； left_tb RIGHT JOIN right_tb ON 右外连接， MariaDB [hellodb]> SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID = c.ClassID; ：RIGHT JOIN左外连接，ON是以什么为条件，右外连接就是以右边的classes表为准，只要是右边表存在的都显示，而左表没有的则显示空。 全外联结：二者左右都有的各自留空，mysql不支持全外联结 |
别名：
表别名在表后面使用AS加使用的别名即可，字段别名在字段后面使用AS加使用的别名即可。
| MariaDB [hellodb]> SELECT s.Name,c.Class FROM students AS s, classes AS c WHERE s.ClassID = c.ClassID;使用别名就全部用别名的方式调用 MariaDB [hellodb]> SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID = c.ClassID ORDER BY Name LIMIT 10;：ORDER BY Name LIMIT 10按照别名排序后再LIMIT 10取出前10行的数据。 |
在查询中嵌套的查询叫做子查询
1、用于比较表达式中的子查询时，返回值只能有一个，用于WHERE中的子查询
2、用于EXISTS中的子查询，判断存在于否
3、用于IN中的子查询，判断存在于指定列表中
用于FROM中的子查询：
SELECT alias.col,……FROM (SELECT clause) AS alias WHERE condition
MYSQL不擅长子查询
MySQL的联合查询：把两个或多个查询语句的结果合并成一个结果输出UNION
SELECT clauase UNION SELECT clause UNION………
视图：其实就是一张虚表,存储下来的SELECT语句：
| MariaDB [mysql]> GRANT ALL ON hellodb.students TO 'openstack'@'172.16.%.%' IDENTIFIED BY 'linux';：授权给openstack用户授权，仅可以查看hellodb这个库中的students这张表 MariaDB [hellodb]> REVOKE ALL ON hellodb.students FROM 'openstack'@'172.16.%.%';收回授权 如果给用户授权时只允许用户查看某几个字段，那就用到视图了，给指定授权用户创建一张虚张，简单也说就是把一个SELECT语句当表来用，而这个SELECT查询的结果保存下来当表来用就叫视图。 MariaDB [hellodb]> CREATE VIEW stu AS SELECT StuID,Name,Age,Gender FROM students;：创建视图； MariaDB [hellodb]> GRANT ALL ON hellodb.stu TO 'openstack'@'172.16.%.%';：给用户openstack只可以查询这个视图中的数据信息。 |
视图有额外的副作用，如果用户被授权可以插入数据，那用户是可以在视图中插入数据的，这样一来，那如果有依赖关系的数据就不完整了，而视图也不能保存数据，数据应该保存在基表中，就是视图所依赖的表，所以视图要做精确规划的，否则可能会导致一些意的情况。
MySQL锁类型：执行操作时施加的锁模式
读锁：也被叫做共享锁，多个读操作可以同时施加的，是非阻塞式的
写锁：独占锁，也叫排它锁，自己完全独立使用的，是阻塞式的
锁粒度：根据锁的粒度可以分为以下几种
表锁：table lock，锁定了整张表
行锁：row lock，锁定需要的行，不一定是一行，也可以是多行
粒度越小，开销越大，但并发性越好，比如说锁需要的行就是开销大
粒度越大，开销越小，但并发性越差，比如说锁整张表就是开销小
因此，不管是在mysql数据库上也好，在其它的关系型数据库上也好，一个最需要完成的做法选择一个合适的锁策略，尽可能在锁的粒度上和其并行性之间找到一个平衡点，所以说并不一定表锁就优于行锁，行锁优于表锁，选择一个好的锁策略至关重要。
基于mysql在哪个级别上实现的，锁的实现位置，锁又分为以下几种：
MySQL锁：在这个级别上的锁可以手动使用，也可以使用显式锁
存储引擎锁：自动进行的，由存储引擎自动使用的叫隐式锁，
显式的实现(表级别的锁)：
mysql > LOCK TABLES锁表
mysql > UNLOCK TABLES解锁表
语法：LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type：锁类型有两类：读锁和写锁
READ [LOCAL] | [LOW_PRIORITY] WRITE
mysql> LOCK TABLES classes READ;读锁
mysql> LOCK TABLES classes WRITE;写锁
InnoDB存储引擎也支持另外一种显式锁(锁定挑选出来的部分行)使用的是行级锁：
| SELECT ..LOCK IN SHARE MODE;显示这个查询语句的时候是以显式的共享锁进行的 SELECT….FOR UPDATE; MariaDB [hellodb] > select * from classes where ClassID <=3 LOCK IN SHARE MODE;这个只有在表的存储引擎为InnoDB上使用，锁定前3行，因为MyISAM不支持行级锁， MariaDB [hello] > ALTER TABLE classes ENGINE ‘InnoDB’;修改表的存储引擎为InnoDB |
事物(Transaction)：就是一组原子性的查询语句，跟数据库对象创建无关，主要指SELECT查询的，将多个查询当作一个独立的工作单元，事物是串形的，只有两个事物压根不会涉及到同一张表时才会同时执行，只要涉及到同一个数据集两个事物是不会执行的。
ACID测试：能满足ACID测试的就表示其支持事物的，或兼容事物
A：Atomicity原子性,所谓原子性是一个事物必须是一个不可分隔的单元，要么都执行，要么都不执行。
C：Consistency一致性,所谓一致性就是一个状态的一致性转换到另一个状态的一致性，比如说银行转帐，转帐之关两个帐户的总金额等于转帐之后两个帐户的总金额就是一致性。
I：Isolation隔离性,一个事物的所作修改操作在提交之前对所有人的所作的操作都是不可见的。注意：隔离性会导致其并发性极大的降低的。
D：Durability持久性，一旦事物提交了，其所做的修改将永久保存在数据中，永久有效，不会因为其它操作还导致数据损失。
总体来讲，数据的安全性越高，其并发性就越差，而隔离会导致其并发性极大的降低的，所以引入了隔离级别的概念：
隔离级别，四个隔离级别：
READ UNCOMMITTED (读未提交)就是读别人尚未提交的数据，其数据安全性最差，但并发性最好，读别人没提交的数据叫脏读；有可以产生不可重复读，就是两次读到的数据不一样叫不可重复读；看到的数据和背后的数据不一样叫幻读；
READ COMMITTED(读提交),只有别人提交事物了才可以看得到，所谓读提交就是一个事物开始的时候只能看见已提交事物的修改，其它未提交的修改统统看不到，不可重读；
REPEATABLE READ(可重读)这个是mysql默认级别，可重读解决了脏读问题，因为每一次读到的结果都是一样的就叫可重读，保证了多次读到的数据都是同一数据。
SERIALIZABLE(可串行化)事物和事物严格隔离，隔离级别很高，性能极低，强制事物的串行执行避免了幻读。只有在确保数据在非常一致性的前提下才可以读到数据。
在一种情况下：我自己的事物启动了，别人的事物也启动了，别人改了一个数据，自己读到的是一个样，在别人改完之后还是读到原来的样子，这叫幻读，看到的数据跟背后的不一样。
| mysql > START TRANSACTION：启动事物 MariaDB[(test)] > COMMIT：提交事物 MariaDB[(test)] > ROLLBACK：回滚，在事物没有提交之前都可以回滚，一旦提交回滚将不会有效 MariaDB[(test)] > SAVEPOINT 保存点，回滚到某个保存点的位置 MariaDB[(test)] > SELECT DATABASE();显示当前使用的数据库 如果没有显式的启动事物，每一个语句都会被当作一个独产的事物， MariaDB[(test)] > SELECT @@lobal.autocommit; MariaDB[(test)] > SHOW GLOBAL VARIABLES LIKE '%commit%'; MariaDB[(test)] > SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; MariaDB [hellodb] > show global variables like ‘tx_iso%’;查看mysql的事物隔离级别。 MariaDB[(test)] > SET GLOBAL autocommit = 0;关闭自提交在启动事物后不要忘手动提交，否则可能会有数据丢失的。 建议：对事物要求不特别严格的场景下，可以使用读提交;要使用功能永久有效改配置文件。 |
MVCC：多版本并发控制
每个事物启动时，InnoDB为会每个启动的事物提供一个当下时刻的快照，
为了实现这种功能，InnoDB会为每个表提供隐藏的字段，一个用于保存行的创建时间，一个保存的是失效时间，里面存储的是系统版本号(system version number)
多版本并发控制只在两个隔离级别下有效：READ COMMITTED和REPEATABLE READ
INSERT INTO：replace
第一种：INSERT INTO tb_name [(clo1,col2……)] {VALUES|VALUE} (val1,val2)
第二种：INSERT INTO tb_name SET col1=val1,col2=val2,……
第三种：INSERT INTO tb_name SELECT clause
REPLACE的工作机制：与INSERT相同，除了在新插入的数据与表中的主键或惟一索引定义的数据相同会替换老的行的数据。
UPDATE更新语句：
UPDATE tb_name SET col_name1=val1,col_name2=val2,……
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...] ：在限定所修改的行数前先做下排序
[LIMIT row_count] ：限定所修改的行数据
UPDATE通常情况下，必须要使用WHERE子句，或者使用LIMIT限制要修改的行数。
--safe-updates
DELETE删除数据的语句：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
对我们而言，只有单表才有意义，它跟UPDATE一样，如果不加WHERE条件限定要删除哪些行的话，将会删除所有的行，from通过某一张表删除什么样的数据，不用指定字段，因为我们删除都是行的数据，还不能删除字段的，字段只能被清空或修改，所以一删除就是一行。
TRUNCATE tb_name：如果我们的表中有自动增长的字段的话，如果删除一数据，新插入的数据不会自动重置为1的，这样我们可以用这个truncate重置自动增长的值。
MySQL查询缓存：
用于保存MySQL查询语句返回的完整结果，被命中时，MySQL会立即返回结果，如何检查缓存：把SELECT语句本身做hash计算，计算的结果作为key，查询结果作为value，什么样的语句不会被缓存呢：查询语句中一些不确定的数据时，不会缓存，例如：NOW()，CURRENT_TIME()、一般来说，如果查询中包含用户自定义函数，存储函数，用户变量，临时表mysql库中的系统表、或者任何包含权限的表，一般都不会缓存。
本文出自 “温水煮青蛙” 博客，请务必保留此出处http://tanxw.blog.51cto.com/4309543/1395568