MYSQL_07Mysql杂记
2023-04-13 14:50:45 20 举报AI智能生成
Mysql杂记，知识点
作者其他创作
大纲/内容
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 来筛选语句。
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
子主题
第一个系统表空间数据文件(ibdata1)的最小文件大小取决于innodb_page_size值
innodb_page_size只能在初始化MySQL实例之前配置，不能在之后修改
mysql的存储引擎
InnoDB存储引擎
InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。
特点
DML 操作遵循 ACID 模型，支持事务
行级锁，提高并发访问性能
支持外键(FOREIGN KEY)约束，保证数据的完整性和正确性
MyISAM存储引擎
MyISAM 是 MySQL 早期的默认存储引擎
特点
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快
Memory存储引擎
存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。
特点
存放在内存中，速度快
hash索引（默认）
系统重启的时候会被清空，但是表结构还在
Memory存储引擎的数据和索引是分开的。memory存储引擎的表也可以有主键，主键id上存储的是每个数据的位置，主键id是哈希索引，索引上的key也不是连续的
memory存储引擎不支持varchar blob和text字段
范围查询的时候，memory存储引擎无法支持。因为它的索引类型是哈希索引
自由主题
0 条评论
下一页
