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; #在查看,两行数据已不再,数据库恢复成功
#################
数据库恢复成功后,首先要做的事是全量备份,并且自己上网找自动热备脚本复现;
网友评论