MYSQL_07Mysql杂记思维导图模板_ProcessOn思维导图、流程图

SQL语言的分类

数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块

数据操纵语言DML

插入：INSERT

更新：UPDATE

删除：DELETE

数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象

表

视图

索引

数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

GRANT：授权

COMMIT：提交

mysqldump及其命令用法

导出数据库

mysqldump -h HOSTNAME -uUSERNAME -p DBNAME > exported_db.sql

导入数据库

mysql -h HOSTNAME -uUSERNAME -p DBNAME < exported_db.sql

HOSTNAME 是数据库主机名

USERNAME 和 DBNAME 分别是登陆 ID 和数据库名

InnoDB中的文件

日志文件

记录了Mysql数据库各类活动，这些日志文件有着不同的含义和作用

错误日志

错误日志文件对Mysql的启动、运行、关闭过程进行了记

当数据库不能正常启动时，可以查看错误日志文件，从而定位到错误信息

慢查询日志

主要用于记录那些查询时间超过一个阈值的SQL语句

DBA可以通过慢查询日志从而找到出现问题的SQL语句，对其进行优化。但是如果慢查询日志很大，人工查看的方式变得缓慢

可以通过mysqldumpslow命令结构化的查看慢查询日志

同时新版本的Mysql将慢查询log记录在表 slow_log 中

long_query_time的默认值为10,意思是运行10S以上的语句

查询日志

查询日志记录对数据库所有请求信息

二进制日志bin_log

二进制日志bin_log 记录了对Mysql数据执行更改的所有操作，但不包括select show此类操作

主要作用

恢复

可以基于二进制日志进行point-in-time的恢复

复制

通过复制和执行二进制日志使得slave数据库与master数据库进行同步

审计

通过二进制日志进行审计，判断是否有攻击|

延迟写入技术

多个小操作合并成一个整体进行写入，以提高效率

不是在每一次对数据库进行更改时，便立即写二进制文件，而是会将二进制日志先记录到一个缓存中，等事务提交时，才将二进制日志写入文件中。 这个缓存的大小可以由binlog_cache_size进行指定

sync_binlog参数可以指定在将二进制日志写入磁盘时，是否同步写入。同步写入，可以确保高可用性

表空间文件ibdata1

InnoDB将存储的数据按照表空间tablespace的形式进行存放。默认的配置下初始化一个大小为10M名字为ibdata1的文件，即默认的表空间文件

innodb_data_file_path可以指定表空间文件以及其属性：autoextend 自动增长

重做日志文件redolog(ib_logfile1)

在InnoDB存储引擎数据目录下ib_logfile0 以及 ib_logfile1文件即为redo_log重做日志。它记录了InnoDB存储引擎的事务日志

实现事务的持久性

，InnoDB存储引擎基于重做日志使得数据恢复到之前的状态，确保数据的完整性

重做日志文件的大小设定对InnoDB存储引擎性能影响非常大

1.若设置过大，那么每次通过重做日志进行恢复需要很长的时间

2.若设置过小，那么将频繁造成async checkpoint 即发生抖动

重做日志redo log 采取缓冲写入的方式，即先写入缓冲区，而后更新到磁盘文件中。写入的单位为512字节，即一个磁盘扇区的大小

mysql innodb的表由.frm .ibd 组成，frm：存了每个表的元数据，包括表结构的定义等；ibd：存了每个表的元数据，包括表结构的定义等；

xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引

慢查询分析调优工具

mysqldumpslow

mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar)  c:总次数  t:总时间  l:锁的时间  r:获得的结果行数  at,al,ar :指t,l,r平均数 【例如：at = 总时间/总次数】  -s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at  -t NUM just show the top n queries：仅显示前n条查询  -g PATTERN grep: only consider stmts that include this string：通过grep 来筛选语句。

mysql实现主从复制

MySQL 的某一台主机的数据复制到其它主机( Slave )上，并重新执行一遍来实现的。复制过程中一个服务器充当服务器，而一个或多个其它服务器充当从服务器

主服务器将更新写入二进制日志binlog，并维护文件的一个索引以跟踪日志循环

当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置，从服务器接收从那时起发生的任何更新，然后封锁等等主服务器通知新的更新

主从复制实现

修改主(master)服务器

创建复制账号

在主服务器中执行该命令

GRANT REPLICATION SLAVE ON *.* to 'replication'@'%' identified by 'Abc123...'

修改从(slave)服务器

在my.cnf配置文件下添加如下配置

server_id=2

log-bin=mysql-bin #开启二进制日志文件

binlog_do_db=db_test#表示要同步的数据库

binlog-ignore-db=test 表示不同步 test 数据库

binlog_do_db=db_test#表示要同步的数据库

重启MySQL

主服务器和从服务器都重启

service mysqld restart

查看主服务器状态show master status

配置从服务器(连接 Master 服务器)

change master to master_host='192.168.74.129',master_user='replication',master_password='Abc123...',master_log_file='mysql-bin.000001',master_log_pos=2041;

子主题

开始复制

开始start slave

停止stop slave

查看从服务器状态

show slave status

innodb_page_size

Page是MySQL Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位

InnoDB逻辑存储单元

表空间、段、区和页

层级关系为tablespace -> segment -> extent(64个page，1M) -> page

子主题