mysql数据库详解

mysql数据库5.7之前默认使用MyISAM引擎,在5.7上使用的是InnoDB引擎。

MyISAM引擎特性

支持全文索引、压缩、空间函数；
不支持事务
表级锁
崩溃无法保证表安全恢复

使用场景：只读或读多写少的场景，较小的表；

文件：每个表有三个文件，存储于数据库目录中
tbl_name.frm：表格式定义；
tbl_name.MYD：数据文件；
tbl_name.MYI：索引文件；

特性：
加锁和并发：表级锁；
修复：手动或自动修复、但可能会丢失数据；
索引：非聚集索引；
延迟索引更新；
表压缩；

InnoDB引擎特性

事务型存储引擎，适合对事务要求较高的场景中；但较适用于处理大量短期事务；
基于MVCC支持高并发；支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读；
使用聚集索引（主键索引）；
支持”自适应Hash索引“；
锁粒度：行级锁；间隙锁；

总结：
数据存储：表空间；
并发：MVCC，间隙锁，行级锁；
索引：聚集索引、辅助索引；
性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；
备份：支持热备；

表空间

InnoDB引擎多了表空间的功能,数据都存储于表空间中

所有数据库中的所有类型为InnoDB的表的数据和索引存储于同一个表空间中
如果要让每个表都单独使用一个表空间,设置innodb_file_per_table=ON

[root@node4 mysql]# vim /usr/local/mysql/etc/my.cnf    #可以把参数写这里
[root@node4 mysql]# vim /usr/local/mysql/etc/my.cnf.d/server.cnf    #也可写在这里开启
[mysqld]
innodb_file_per_table=ON  #开启单独表空间
skip_name_resolve=ON  #禁止域名反解

备份

冷备，对数据库备份的时候，别人不能读也不能写
温备，对数据库备份的时候，别人能读，但不能写
热备，对数据库备份的时候，别人能读也能写

锁

mysql> LOCK TABLE tbl2 read;    #在终端1上,锁住tbl2只读
mysql> LOCK TABLE tbl2 write;    #这时终端2上,可以读取,但是运行写或加锁会阻塞
mysql> UNLOCK TABLES;    #终端1解锁后,终端2的写锁命令离开阻塞模式,执行成功
mysql> SELECT * FROM tbl2;    #这时终端1读取时阻塞,因伪终端2的写锁是独占锁
mysql> FLUSH TABLES WITH READ LOCK;    #把所有表缓存中的数据刷到磁盘去,并把表关闭
mysql> UNLOCK TABLES;

事务

事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元；

事务日志：把随机IO转换成顺序IO,并能支持回滚,以及持久功能的一个辅助工具.
innodb_log_files_in_group #要分组,至少要两个
innodb_log_group_home_dir #日志存储地址
innodb_log_file_size #不能过大,否则启动修复时间过长
innodb_mirrored_log_groups #镜像日志组

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb%log%';#查询事务的一些参数配置

ACID测试：(数据库事务正确执行的四个基本要素的缩写)
A：原子性；事务中所有操作要么全部成功执行，要么全部失败后回滚；
C：一致性；数据库总是应该从一个一致性状态转为另一个一致性状态；
I ：隔离性；一个事务做出操作在提交之前，是否其它事务可见；隔离分四种级别
D：持久性；事务一旦提交，其所做出的修改会永久保存；

单语句事务自动提交，SET @@session.autocommit=0；

手动控制事务：
启动：START TRANSACTION
提交：COMMIT
回滚：ROLLBACK

事务支持savepoints： #保存一个状态
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

事务四个隔离级别：
READ-UNCOMMITTED：读未提交 --> 脏读；
READ-COMMITTED：读提交--> 不可重复读；
REPEATABLE-READ：可重复读 --> 幻读；
SERIALIZABLE：串行化；

查看InnoDB存储引擎的状态信息：
SHOW ENGINE innodb STATUS;

#操作实例
mysql> SHOW SESSION VARIABLES LIKE '%commit%';
mysql> SELECT @@session.autocommit;    #为1时每句结束都刷新磁盘,每句都是事务
mysql> SET @@session.autocommit=0;    #为0是关闭事务,之后要手动启动事务
mysql> START TRANSACTION;    #手动启动事务
mysql> USE mydb;
mysql> SHOW TABLE STATUS LIKE 'tbl2';    #查看表的引擎是Innodb;
mysql> INSERT INTO tbl2 VALUES (2,'jerry');    #增加
mysql> Update tbl2 SET Name='Tom' WHERE id=1;    #修改
mysql> SELECT * FROM tbl2;
+------+-------+
| id   | Name  |
+------+-------+
|    1 | Tom   |
|    2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
mysql> ROLLBACK;    #不运行事务，回滚操作
mysql> SELECT * FROM tbl2;
+------+------+
| id   | Name |
+------+------+
|    1 | tom  |
+------+------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tbl2 VALUES (2,'jerry');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tbl2;
+------+-------+
| id   | Name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
mysql> SAVEPOINT first;    #创建第一个保存点
mysql> Update tbl2 SET Name='Tom' WHERE id=1;
mysql> SAVEPOINT second;    #保存第二个保存点
mysql> DELETE FROM tbl2 WHERE ID=1;    #删除第一行
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tbl2;
+------+-------+
| id   | Name  |
+------+-------+
|    2 | jerry |
+------+-------+
1 row in set (0.00 sec)
mysql> ROLLBACK TO second;    #回滚到第二个保存点
mysql> SELECT * FROM tbl2;
+------+-------+
| id   | Name  |
+------+-------+
|    1 | Tom   |
|    2 | jerry |
+------+-------+
mysql> ROLLBACK TO first;    #回滚到第一个保存点
mysql> SELECT * FROM tbl2;
+------+-------+
| id   | Name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
+------+-------+
mysql> COMMIT;    #生效,确定事务结束;

MySQL用户和权限管理

用户账号：user@host
user：账户名称；
host：此账户可通过哪些客户端主机请求创建连接线程；
%：任意长度牟任意字符；
_：任意单个字符；

skip_name_resolve=ON #关闭mysql反解IP

MySQL权限类别：
库级别：
表级别：
字段级别：
管理类：
程序类：

管理类：
CREATE USER #创建用户账号
RELOAD #重载各种组件
LOCK TABLES #锁定表
REPLICATION CLIENT， REPLICATION SLAVE #复制功能
SHUTDOWN #关闭数据库服务器
FILE #从文件中加载内容
SHOW DATABASES #查看数据库
PROCESS #跟进程相关
SUPER #仅次于root,拥有其他管理权限的用户

程序类：
FUNCTION，PROCEDURE，TRIGGER #存储函数,存储过程,触发器
操作：CREATE，ALTER，DROP，EXECUTE #可以组合成12个权限

库和表级别：
CREATE，ALTER，DROP #创建,修改,删除
INDEX #索引
CREATE VIEW #创建视图
SHOW VIEW #查看创建视图的语句的权限
GRANT：能够把自己获得的权限生成一个副本转赠给其它用户；
OPTION #其他权限的一些选项

数据操作：
表： INSERT/DELETE/UPDATE/SELECT #增删改查
字段： #增改查
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
所有权限：ALL， ALL PRIVILEGES
元数据数据库（数据字典）：mysql
授权：
db, host, user
tables_priv, column_priv, procs_priv, proxies_priv

MySQL用户管理：

创建用户：
CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...]
重命名：
RENAME USER RENAME USER old_user TO new_user[, old_user TO new_user] ...
删除用户：
DROP USER 'user'@'host' [, 'user'@'host'] ...

让MySQL重新加载授权表：FLUSH PRIVILEGES

修改用户密码：
(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password');
(2) UPDATE mysql.user SET Password=PASSWORD('cleartext password') WHERE User='USERNAME' AND Host='HOST';
(3) mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS'

生效：FLUSH PRIVILEGES

忘记管理员密码的解决办法：
(1) 启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项；
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2) 通过UPDATE命令修改管理员密码；
(3) 以正常方式启动mysqld进程；

#找回密码实例
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mageedu');
]# systemctl stop mysqld
]# vim /usr/local/mysql/etc/my.cnf    #修改环境配置
[mysqld]    #添加两句,登录mysql时跳过验证
skip-grant-tables
skip-networking
]# systemctl daemon-reload    #重新载入 systemd，扫描新的或有变动的单元
]# systemctl start mysqld
]# mysql
mysql> UPDATE mysql.user SET password=PASSWORD('112233') WHERE user='root';
#mysql5.7版本之前
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('112233') WHERE user='root';
#mysql5.7版本中password改成了authentication_string
#之后,停止服务于,删除环境配置,重新载入,开启服务,用新密码就可以登录

授权：GRANT
查看授权：SHOW GRANTS
取消授权：REVOKE

#授权实例
mysql> GRANT SELECT ON hidb.students TO 'lsh'@'192.168.152.%' IDENTIFIED BY '112233';
#授权账户为lsh，密码为112233的账户可以访问hidb,students的权限

[root@node4 ~]# mysql -h192.168.152.40 -ulsh -p112233    #另一个终端登录
mysql> SHOW DATABASES;    #只能看见授权访问的表
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hidb               |
+--------------------+
mysql> DELETE FROM students WHERE id=102;    #此时没有权限删除
ERROR 1142 (42000): DELETE command denied to user 'lsh'@'192.168.152.40' for table 'students'

mysql> GRANT DELETE ON hidb.students TO 'lsh'@'192.168.152.%' IDENTIFIED BY '112233';
#此时授权账号lsh对表hidb.students有删除权限,授权账号权限是追加的。

mysql> SHOW GRANTS;    #此时查看账号权限,拥有SELECT和DELETE
+--------------------------------------------------------------------+
| Grants for lsh@192.168.152.%                                       |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lsh'@'192.168.152.%'                        |
| GRANT SELECT, DELETE ON `hidb`.`students` TO 'lsh'@'192.168.152.%' |
+--------------------------------------------------------------------+

mysql> DELETE FROM students WHERE id=102;    #此时账号可以删除数据
Query OK, 1 row affected (0.01 sec)

mysql> GRANT UPDATE(major) ON hidb.students TO 'lsh'@'192.168.152.%' IDENTIFIED BY '112233';
#授权只能修改表其中一个字段的权限

mysql> UPDATE students SET name='ooooo' WHERE id=100;    #修改name字段没有权限
ERROR 1143 (42000): UPDATE command denied to user 'lsh'@'192.168.152.40' for column 'name' in table 'students'

mysql> UPDATE students SET major='Hamo gong' WHERE id=100;    #修改major字段成功
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL的索引

索引：提取索引的创建在的表上字段中的数据，构建出一个独特的数据结构；
索引的作用：加速查询操作；副作用：降低写操作性能；
表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；
某个字段或某些字段：WHERE子句中用到的字段；

索引类型：B+ TREE，HASH
B+ TREE：顺序存储，每一个叶子结点到根结点的距离相同；左前缀索引，适合于范围类型的数据查询；

适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；
全值匹配：精确匹配某个值；WHERE COLUMN = 'value';
匹配最左前缀：只精确起头的部分；WEHRE COLUMN LIKE 'PREFIX%';
匹配范围值：精确匹配某一列，范围匹配另一列；#a=?,b>?前面精确,后面范围
只用访问索引的查询：覆盖索引；
index(Name)
SELECT Name FROM students WHERE Name LIKE 'L%';

不适用B+ TREE索引：
如果查条件不是从最左侧列开始，索引无效；
index(age,Fname), WHERE Fname='Jerry'; , WHERE age>30 AND Fname='Smith';
不能跳过索引中的某列；
index(name,age,gender)
WHERE name='black' and age > 30;
WHERE name='black' AND gender='F';
如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；
WHERE age>30 AND Fname='Smith';

Hash索引：基于哈希表实现，特别适用于值的精确匹配查询；
适用场景：
只支持等值比较查询，例如=, IN(), <=>
不用场景：
所有非精确值查询；MySQL仅对memory存储引擎支持显式的hash索引；
index(name)
WHERE name="jerry" #只适用于精确值比较

索引优点：
降低需要扫描的数据量，减少IO次数；
可以帮助避免排序操作，避免使用临时表；
帮助将随机IO转为顺序IO；

索引中的索引项每一次命中的时候,索引项背后会有一个指针,这个指针指向了这个索引索引的行所在磁盘文件中的数据块的编号,而后加载这个行的时候,只需要把磁盘块加载入内存,就能找到这一行,而不需要把整个文件加载入内存.这样一来,极大减少了需要加载或扫描的数据块的数量.

高性能索引策略：
(1) 在WHERE中独立使用列，尽量避免其参与运算；
WHERE age+2 > 32 ;
(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
索引选择性：不重复的索引值和数据表的记录总数的比值；
(3) 多列索引：
AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；
WHERE gender="F" AND age>18;
index(gender),index(age)
index(gender,age) #使用这种更高效
(4) 选择合适的索引列次序：选择性最高的放左侧；

EXPLAIN来分析索引有效性：

复杂的查询的类型主要三种：
简单子查询
用于FROM中的子查询
联合查询
注意：联合查询的分析结果会出现一个额外的匿名临时表；

#实例:
mysql> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('F','M'),major VARCHAR(200)
);    #创建表
mysql> INSERT INTO students VALUES (1,"Yang Guo",17,'M','Anran Xiaohun Zhang'),
(2,"Ding Dian",37,'M','Shenzhao Jing');
#添加一个数据
[root@node4 ~]# for i in {1..100}; do mysql -e "INSERT INTO hidb.students(name,age)
 VALUES ('stu$i','$[$RANDOM%80+18]');"; done
#批量添加数据
mysql> EXPLAIN SELECT name FROM students WHERE age>90;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  102 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
#        简单查询    执行表                获取数据类型(没有索引,所以把整个表载入内存)            装入了102行数据        并使用了where条件额外过滤
mysql> CREATE INDEX age ON students(age);    #在age上创建索引
mysql> SHOW INDEXES FROM students\G    #查看索引
mysql> EXPLAIN SELECT name FROM students WHERE age>90;    #再次分析语句
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | students | NULL       | range | age           | age  | 2       | NULL |    8 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
mysql> CREATE INDEX age_and_name ON students(age,name);#创建多列索引
mysql> SHOW INDEXES FROM students;
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY      |            1 | id          | A         |         102 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age          |            1 | age         | A         |          62 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | age_and_name |            1 | age         | A         |          62 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | age_and_name |            2 | name        | A         |         102 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT name FROM students WHERE age>90;
+----+-------------+----------+------------+-------+------------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys    | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+------------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | students | NULL       | range | age,age_and_name | age_and_name | 2       | NULL |    8 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+------------------+--------------+---------+------+------+----------+--------------------------+
#因为要显示的是name,所以查询age_and_name索引,直接从索引中就可以拿到name,这种叫做覆盖索引,Using index
mysql> DROP INDEX age_and_name ON students;
#删除索引
mysql> CREATE INDEX age_and_name ON students(name,age);
#添加索引,和上面的位置相反
mysql> EXPLAIN SELECT name FROM students WHERE age>90;
#此时只查询age索引,age_and_name是左前缀索引,只查找符合左边的.
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | students | NULL       | range | age           | age  | 2       | NULL |    8 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

mysql> EXPLAIN SELECT name FROM students WHERE name LIKE 'stu1%';
#符合左前缀索引,使用age_and_name
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | students | NULL       | range | age_and_name  | age_and_name | 90      | NULL |   12 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

mysql> EXPLAIN SELECT name FROM students WHERE name LIKE 'stu1%' AND age>90;
#此查询符合两种索引方式,最匹配的是age_and_name
+----+-------------+----------+------------+-------+------------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys    | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+------------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | students | NULL       | range | age,age_and_name | age_and_name | 92      | NULL |   12 |     7.84 | Using where; Using index |
+----+-------------+----------+------------+-------+------------------+--------------+---------+------+------+----------+--------------------------+

mysql> EXPLAIN SELECT name,age FROM students WHERE age > (SELECT avg(age) FROM students);
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY     | students | NULL       | index | age           | age_and_name | 92      | NULL |  102 |    50.98 | Using where; Using index |
|  2 | SUBQUERY    | students | NULL       | index | NULL          | age          | 2       | NULL |  102 |   100.00 | Using index              |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
#1.PRIMARY主查询,2.SUBQUERY简单子查询

mysql> EXPLAIN SELECT name FROM students WHERE id=3;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
#const，system：与某个常数比较，且只返回一行；基于这种是最高效的

查询缓存：

缓存：k/v
key：查询语句的hash值
value：查询语句的执行结果

如何判断缓存是否命中：
通过查询语句的哈希值判断：哈希值考虑的因素包括
查询本身、要查询数据库、客户端使用的协议版本、...
SELECT Name FROM students WHERE StuID=3;
Select Name From students where StuID=3;
要程序员采用形同风格的查询语句

哪些查询可能不会被缓存？
查询语句中包含UDF
存储函数
用户自定义变量
临时表
mysql系统表或者是包含列级别权限的查询
有着不确定结果值的函数(now())；

查询缓存相关的服务器变量：
query_cache_limit：能够缓存的最大查询结果；（单语句结果集大小上限）
有着较大结果集的语句，显式使用SQL_NO_CACHE，以避免先缓存再移出；
query_cache_min_res_unit：内存块的最小分配单位；缓存过小的查询结果集会浪费内存空间；
较小的值会减少空间浪费，但会导致更频繁地内存分配及回收操作；
较大值的会带来空间浪费；
query_cache_size：查询缓存空间的总共可用的大小；单位是字节，必须是1024的整数倍；
query_cache_strip_comments
query_cache_type：缓存功能启用与否；
ON：启用；
OFF：禁用；
DEMAND：按需缓存，仅缓存SELECT语句中带SQL_CACHE的查询结果；
query_cache_wlock_invalidate：如果某表被其它连接锁定，是否仍然可以从查询缓存中返回查询结果；默认为OFF，表示可以；ON则表示不可以；

缓存实例:
[root@localhost tmp]# vim /usr/local/mysql/etc/my.cnf.d/server.cnf 
query_cache_type=1  #添加这一行开启缓存

mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 67108864 |   
| query_cache_type             | ON      |   #启用了缓存
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

mysql> SELECT SQL_NO_CACHE Name FROM students WHERE id=7;    #此查询不会被缓存

mysql> SELECT SQL_CACHE Name FROM students WHERE id=7;    #设置为DEMAND时,需加上参数才能缓存

#如果缓存一句语句大于设置的最大缓存,mysql也会先缓存,再删除缓存,所以,可以此命令可以带上参数不缓存;

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |    #已经分配了但还是没使用的空间
| Qcache_free_memory      | 16759688 |    #还剩多少
| Qcache_hits             | 0        |    #命中多少次
| Qcache_inserts          | 0        |    #被插入多少次
| Qcache_lowmem_prunes    | 0        |    #空间不够时,删除的次数
| Qcache_not_cached       | 0        |    #没能缓存进来的缓存项
| Qcache_queries_in_cache | 0        |    #当前缓存空间中缓存了多少语句
| Qcache_total_blocks     | 1        |    #共分配了多少块
+-------------------------+----------+      
mysql> SHOW GLOBAL STATUS LIKE 'Com_select';#查看状态变量
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 10    |
+---------------+-------+
 #查询执行了多少次,和上面的命中一起计算,得到命中率

命中率：
Qcache_hits/Com_select

日志：

查询日志：general_log,记录查询语句，日志存储位置：
慢查询日志：log_slow_queries,运行时间超出指定时长的查询；
错误日志：log_error， log_warnings
二进制日志：binlog ,用于记录引起数据改变或存在引起数据改变的潜在可能性的语句
中继日志：relay_log,从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；
事务日志：innodb_log,事务型存储引擎innodb用于保证事务特性的日志文件

mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

[root@node4 etc]# vim /usr/local/mysql/etc/my.cnf
[mysqld]    #需要添加这两行才能启动二进制日志
log_bin=master-log
server-id=1
mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |    #查看状态,二进制日志启动
+---------------+-------+

[root@node4 etc]# cd /data//mysql/
[root@node4 mysql]# ls    #目录中有三个日志文件
auto.cnf        ibdata1      ibtmp1             master-log.index  node4.log             performance_schema
hidb            ib_logfile0  master-log.000001  mydb              node4.magedu.com.pid  sys
ib_buffer_pool  ib_logfile1  master-log.000002  mysql             node4-slow.log

mysql> FLUSH LOGS;    #设置二进制日志滚动
[root@node4 mysql]# ls
auto.cnf        ibdata1      ibtmp1             master-log.000003  mysql                 node4-slow.log
hidb            ib_logfile0  master-log.000001  master-log.index   node4.log             performance_schema
ib_buffer_pool  ib_logfile1  master-log.000002  mydb               node4.magedu.com.pid  sys
[root@node4 mysql]# cat master-log.index   
#每滚动一次都会记录下来,在index能看见对应的记录
./master-log.000001
./master-log.000002
./master-log.000003
mysql> SHOW MASTER LOGS;    
#在数据库里也能查看
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |       177 |
| master-log.000002 |       202 |
| master-log.000003 |       154 |
+-------------------+-----------+

mysql> CREATE TABLE tbl4(id INT,Name VARCHAR(200),Gender ENUM('F','M'));
mysql> INSERT INTO tbl4 VALUES (1,'tom','M');
mysql> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |       177 |
| master-log.000002 |       202 |
| master-log.000003 |       623 |    #此时日志从154增长到623
+-------------------+-----------+
mysql> SHOW MASTER STATUS;    #当前正在用哪个,记录到哪个位置
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-log.000003 |      623 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

[root@node4 mysql]# mysqlbinlog master-log.000002
#可以用这个命令查看二进制文件
mysql> SHOW BINLOG EVENTS IN 'master-log.000002';
#在数据库查看二进制文件
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+
| master-log.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.18-log, Binlog ver: 4 |
| master-log.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| master-log.000002 | 154 | Rotate         |         1 |         202 | master-log.000003;pos=4               |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------+

mysql> mysql> SHOW BINLOG EVENTS IN 'master-log.000002' FROM 123;    #只显示123后的
+-------------------+-----+----------------+-----------+-------------+-------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                    |
+-------------------+-----+----------------+-----------+-------------+-------------------------+
| master-log.000002 | 123 | Previous_gtids |         1 |         154 |                         |
| master-log.000002 | 154 | Rotate         |         1 |         202 | master-log.000003;pos=4 |
+-------------------+-----+----------------+-----------+-------------+-------------------------+
mysql> mysql> SHOW BINLOG EVENTS IN 'master-log.000002' FROM 123 LIMIT 3;

mysql> SET @@session.sql_log_bin=OFF;    #在当前会话中关闭二进制日志

[root@node4 mysql]# mysqlbinlog -j 154 master-log.000001    #从154显示
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180420 11:15:46 server id 1  end_log_pos 123 CRC32 0x370382b3     Start: binlog v 4, server v 5.7.18-log created 180420 11:15:46 at startup
ROLLBACK/*!*/;
BINLOG '
IgTaWg8BAAAAdwAAAHsAAAAAAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAiBNpaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbOCAzc=
'/*!*/;
# at 154
#180420 11:15:48 server id 1  end_log_pos 177 CRC32 0x78872ea1     Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node4 mysql]# mysqlbinlog --start-datetime='2018-04-20 11:34:05' master-log.000003 > a.sql
#把按时间查找的日志保存

备份和恢复（数据）：

备份：存储的数据副本；原始数据：持续改变；
恢复：把副本应用到线上系统；仅能恢复至备份操作时刻的数据状态；

时间点恢复：binary logs;

为什么备份？
    灾难恢复：硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、...
    测试；

备份时应该注意事项：
    能容忍最多丢失多少数据；
    恢复数据需要在多长时间内完成；
    需要恢复哪些数据；

     做恢复演练：
        测试备份的可用性；
        增强恢复操作效率；
        ...

备份类型：
    备份的数据集的范围：
        完全备份和部分备份
            完全备份：整个数据集；
            部分备份：数据集的一部分，比如部分表；

    全量备份、增量备份、差异备份：
        完全备份
        增量备份：仅备份自上一次完全备份或 增量备份以来变量的那部数据；
        差异备份：仅备份自上一次完全备份以来变量的那部数据；

    物理备份、逻辑备份：
        物理备份：复制数据文件进行的备份；
        逻辑备份：从数据库导出数据另存在一个或多个文件中；

    根据数据服务是否在线：
        热备：读写操作均可进行的状态下所做的备份；
        温备：可读但不可写状态下进行的备份；
        冷备：读写操作均不可进行的状态下所做的备份；

备份需要考虑因素：
    锁定资源多长时间？
    备份过程的时长？
    备份时的服务器负载？
    恢复过程的时长？

备份策略：
    xtrabackup:
        全量+差异 + binlogs
        全量+增量 + binlogs
    完全+binlog:mysqldump

    备份手段：物理、逻辑

备份什么？
    数据
    二进制日志、InnoDB的事务日志；
    代码（存储过程、存储函数、触发器、事件调度器）
    服务器的配置文件


备份工具：
    mysqldump：mysql服务自带的备份工具；逻辑备份工具；
        完全、部分备份；
        InnoDB：热备；
        MyISAM：温备；
    cp/tar
        lvm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份；
        注意：不能仅备份数据文件；要同时备份事务日志；
            前提：要求数据文件和事务日志位于同一个逻辑卷；
    xtrabackup：
        由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具；
            完全备份、部分备份；
            完全备份、增量备份；
            完全备份、差异备份；
    mysqlhotcopy
    select：
        备份：SELECT cluase INTO OUTFILE 'FILENAME';
        恢复：CREATE TABLE
        导入：LOAD DATA

[root@node4 ~]# mysqldump -uroot -p112233 --single-transaction -R -E --triggers --master-data=2 --flush-logs --databases hidb > /root/hidb-fullback-$(date +%F).sql
#命令 - 用户名 - 密码 - InnoDB热备 - 存储过程 - 事件调度 - 触发器 - 注释记录 - 刷新事务 - 数据库 > 表名

#备份恢复数据库实例
[root@node4 ~]# mysqldump -uroot -p112233 --single-transaction -R -E --triggers --master-data=2
--flush-logs --all-databases > /root/alldb-fullback-$(date +%F).sql
mysql> SHOW MASTER LOGS;    #查看二进制日志初始大小,0008 154
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |       177 |
| master-log.000002 |       202 |
| master-log.000003 |       671 |
| master-log.000004 |       177 |
| master-log.000005 |       202 |
| master-log.000006 |       202 |
| master-log.000007 |       686 |
| master-log.000008 |       154 |
+-------------------+-----------+
mysql> use hidb;    #删除两行
mysql> DELETE FROM students WHERE id=10;
mysql> DELETE FROM students WHERE id=20;
mysql> use mydb;    #删除mydb数据库tbll表
mysql> DROP TABLE tbll;
mysql> SHOW MASTER LOGS;    #继续查看二进制日志,增大到0008 889
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-log.000001 |       177 |
| master-log.000002 |       202 |
| master-log.000003 |       671 |
| master-log.000004 |       177 |
| master-log.000005 |       202 |
| master-log.000006 |       202 |
| master-log.000007 |       686 |
| master-log.000008 |       889 |
+-------------------+-----------+

[root@node4 ~]# less alldb-fullback-2018-04-22.sql   
 #查看之前备份的二进制日志,最后位置是0008 154
-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000008', MASTER_LOG_POS=154;
[root@node4 ~]# cp /data/mysql/master-log.000008 /root/   
 #拷贝0008二进制日志文件

[root@node4 ~]# systemctl stop mysqld;    
#停止mysql服务,并删除/data/mysql下所有数据库数据
[root@node4 ~]# cd /data/mysql/
[root@node4 mysql]# rm -rf ./*
###############
[root@node4 mysql]# cd /usr/local/mysql
#如果启动失败，到mysql安装目录再编译一次程序,就能正常启动了
[root@node4 mysql]# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql
###############
[root@node4 mysql]# systemctl start mysqld;    #重新启动数据库
[root@node4 mysql]# ls
#被删除的空文件中自动生成了一些文件,我们现在来恢复数据库,1.恢复数据库备份文件,2.二进制日志文件转成脚本运行
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  master-log.000001  master-log.000002  master-log.index  mysql  node4.magedu.com.pid  performance_schema  sys

[root@node4 ~]# cp alldb-fullback-2018-04-22.sql /tmp
#进入备份的文件夹拷贝文件到临时目录
[root@node4 ~]# mysqlbinlog master-log.000008 > /tmp/binlog.sql
#二进制日志生成脚本
mysql> SET @@session.sql_log_bin=OFF;
#登录数据库关闭二进制日志,不需记录恢复的过程
mysql> \. /tmp/alldb-fullback-2018-04-22.sql    #用备份恢复数据
mysql> USE hidb;    #查看刚刚删除两行数据的表,两行数据都在
mysql> SELECT * FROM students;
mysql> \. /tmp/binlog.sql    #读取二进制日志生成的脚本
mysql> use hidb;
mysql> SELECT * FROM students;    #在查看,两行数据已不再,数据库恢复成功
#################
数据库恢复成功后,首先要做的事是全量备份,并且自己上网找自动热备脚本复现;

最后编辑于：2018.05.03 09:27:33

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,098评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,213评论 2赞 380
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,960评论 0赞 336
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,519评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,512评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,533评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,914评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,574评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,804评论 1赞 296
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,563评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,644评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,350评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,933评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,908评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,146评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,847评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,361评论 2赞 342