Mysql

数据库的分类:

关系型数据库(是建立在关系模型基础上的数据库):

Mysql:开放源代码
MariaDB:Mysql的替代品
Oracle:闭源
SQL Server:微软的
DB2:IBM
Access:微软公司

非关系型数据库(与关系型数据库最明显的区别:不再使用SQL作为查询语言):nosql

MongoDB、Redis、Memcached、hadoop

Mysql的结构:

C/S结构:客户端/服务器结构(客户端和服务器端都需要装软件)

Mysql的三种安装方式:

1、源码安装:
(5.5版本之前用的是./configure安装,默认存储引擎是myisam;5.5之后用的是cmake安装,默认引擎是innodb)
优点:可根据实际环境需求指定编译参数、灵活性好、一台服务器可以安装多个Mysql;
缺点:安装步骤复杂、编译时间长;
2、rpm安装
优点:安装简单;
缺点:需要单独下载客户端和服务器端,安装路径不灵活,默认路径不能更改,一台服务器只能安装一个;
3、二进制包安装:
优点:安装简单,可以安装到任何路径下,灵活性好,一台服务器可以安装多个Mysql;
缺点:已经编译好,性能不如源码编译

SQL语句的功能以及分类:

功能:

增删改查

分类:
DML:数据库操纵语言
  INSERT    插入数据
  DELETE    删除数据
  UPDATE    更新数据
DDL:数据库定义语言(数据库、表、索引、存储过程、函数)
  CREATE    创建
  DROP      删除
  ALTER     修改
DCL:数据库控制语言
  GRANT    授权
  REVOKE   撤销
DQL:数据库查询语言
  SELECT    查询

常用存储引擎

查看当前数据库软件支持的所有存储引擎命令:

MariaDB [(none)]> show engines\G;

innodb:

在事务上具有优势,即支持提交、回滚及崩溃恢复能力等事务特性,所以比myisam存储引擎占用更多的磁盘空间。当需要频繁更新、删除操作,同时还对事务的完整性要求比较高,需要实现并发控制,建议选择。

myisam:

不支持事务、外键,所以访问速度快。当对事务的完整性没有要求并以访问为主的应用适合使用myisam引擎。

memory:

memory存储数据的位置是内存,因此访问速度最快,但是在安全上没有保障,适合用于需要快速访问的临时表,重启服务之后,数据丢失。

innodb支持的事务(dml操作):

事务满足四个特性:ACID
A:原子性
一个事务是一个整体,它里面的操作要么都做,要么都不做。
C:一致性
事务开始前和结束后,数据库的完整性约束是一致的。
I:隔离性、独立性
两个事务之间是互不相干的。
D:持久性
事务完成之后,对数据库的所有更改都会持久的保存在数据中,不会被回滚。

事务结束的标志:

1、明确执行commit提交,表示确认更改;
2、明确执行rollback回滚,表示取消更改;
3、遇到DDL语句,自动提交。

二进制日志:

开启二进制日志:

(编译安装的一般在mysql/data目录下,yum安装的一般在/var/lib/mysql目录下)
#vim /etc/my.cnf
log-bin=log_bin.log //=后面的日志文件名可以自定义
log-bin-index=log_bin.index

查看二进制文件:

#mysqlbinlog log_bin.000001

使用二进制日志进行数据恢复:

使用位置点进行恢复:
#mysqlbinlog --start-position=起始位置点 --stop-position=结束位置点 log_bin.000001 | mysql -u root -p2
使用事件发生时间:
#mysqlbinlog --start-datetime=起始时间 --stop-datetime=结束时间 log_bin.000001 | mysql -u root -p2

查看日志信息:

MariaDB [(none)]> show master logs;

查看当前使用的二进制日志文件及所处的位置:

MariaDB [(none)]> show master status\G;

查看二进制日志里的操作记录:

MariaDB [(none)]> show binlog events;

备份与恢复:

备份的分类:

1、根据服务是否在线可分为:
热备份:读写都不受影响;
温备份:仅可以执行读操作;
冷备份:也叫离线备份,读写操作都终止;
2、根据备份时是否之间复制数据可分为:
物理备份:直接复制数据文件,速度快;
逻辑备份:通常是将数据导出到文本 文件中,速度慢、丢失浮点精度,方便使用文本根据直接进行处理,可移植能力强;
3、根据备份数据内容可分为:
完全备份:备份全部数据;
增量备份:仅备份上次完全备份或增量备份之后变化的数据;
差异备份:仅备份上次完全备份以来变化的数据;

备份工具:

mysqldump、lv、innodb、mysqlbackup

AB复制:

工作原理:

1) master上必须开启二进制日志;
2) slave通过I/O线程向master请求二进制日志文件;
3) master收到请求后,会从相应的位置点开始向salve传送二进制日志文件;
4) salve收到二进制日志后,会写入本地的中继日志中;
5) salve通过sql进程读取中继日志中的内容,然后会在数据库执行相应的操作,最后master和salve的数据一致之后,salve进入等待状态,等待master的更新。

相关的线程:

salve:
I/O线程:负责从主服务器上请求二进制日志文件,并且在收到日志之后写入到本地的中继日志;
sql线程:负责把中继日志中的操作写入到数据库中;
master:
binlog dump线程:负责发送二进制日志文件;

锁库以及锁表:

全局锁定只读:flush tables with read lock;
表级锁定:lock tables 表名 read;
解锁:unlock tables;

慢查询:

显示当前数据库中与版本号相关的东西:
showvariables like ‘%version%’;

当前的通用日志查询是否开启,如果general_log的值为ON则为开启,为OFF则为关闭:
showvariables like ‘%general%’;

查看当前慢查询日志输出的格式,可以是FILE(存储在数数据库的数据文件中的hostname.log),也可以是TABLE(存储在数据库中的mysql.general_log):
showvariables like ‘%log_output%’;

如何开启MySQL通用查询日志,以及如何设置要输出的通用日志输出格式呢:

开启通用日志查询: set global general_log=on;
关闭通用日志查询: set globalgeneral_log=off;
设置通用日志输出为表方式: set
globallog_output=’TABLE’;
设置通用日志输出为文件方式: set globallog_output=’FILE’;
设置通用日志输出为表和文件方式:set global
log_output=’FILE,TABLE’;
(注意:上述命令只对当前生效,当MySQL重启失效,如果要永久生效,需要配置my.cnf)

my.cnf文件的配置如下:
general_log=1 #为1表示开启通用日志查询,值为0表示关闭通用日志查询
log_output=FILE,TABLE #设置通用日志的输出格式为文件和表

如何查看当前慢查询日志的开启情况:

showvariables like ‘%quer%’;


image.png

主要掌握以下的几个参数:

(1)slow_query_log的值为ON为开启慢查询日志,OFF则为关闭慢查询日志。

(2)slow_query_log_file 的值是记录的慢查询日志到文件中(注意:默认名为主机名.log,慢查询日志是否写入指定文件中,需要指定慢查询的输出日志格式为文件,相关命令为:show variables like ‘%log_output%’;去查看输出的格式)。

(3)long_query_time 指定了慢查询的阈值,即如果执行语句的时间超过该阈值则为慢查询语句,默认值为10秒。

(4)log_queries_not_using_indexes 如果值设置为ON,则会记录所有没有利用索引的查询(注意:如果只是将log_queries_not_using_indexes设置为ON,而将slow_query_log设置为OFF,此时该设置也不会生效,即该设置生效的前提是slow_query_log的值设置为ON),一般在性能调优的时候会暂时开启。

设置MySQL慢查询的输出日志格式为文件还是表,或者两者都有:

show variables like ‘%log_output%’;


image.png
关于慢查询日志的表中的数据个文本中的数据格式分析:

慢查询的日志记录myql.slow_log表中,格式如下:


image.png

可以看到,不管是表还是文件,都具体记录了:是那条语句导致慢查询(sql_text),该慢查询语句的查询时间(query_time),锁表时间(Lock_time),以及扫描过的行数(rows_examined)等信息。

如何查询当前慢查询的语句的个数:

在MySQL中有一个变量专门记录当前慢查询语句的个数:
输入命令:show global status like ‘%slow%’;


image.png

(注意:上述所有命令,如果都是通过MySQL的shell将参数设置进去,如果重启MySQL,所有设置好的参数将失效,如果想要永久的生效,需要将配置参数写入my.cnf文件中)。

补充知识点:

如何利用MySQL自带的慢查询日志分析工具mysqldumpslow分析日志?
perl mysqldumpslow –s c –t 10 slow-query.log
具体参数设置如下:
-s 表示按何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
-t 表示top的意思,后面跟着的数据表示返回前面多少条;
-g 后面可以写正则表达式匹配,大小写不敏感。


image.png
实际在学习过程中,如何得知设置的慢查询是有效的:

很简单,我们可以手动产生一条慢查询语句,比如,如果我们的慢查询log_query_time的值设置为1,则我们可以执行如下语句:
select sleep(1);
该条语句即是慢查询语句,之后,便可以在相应的日志输出文件或表中去查看是否有该条语句。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 215,012评论 6 497
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,628评论 3 389
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 160,653评论 0 350
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,485评论 1 288
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,574评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,590评论 1 293
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,596评论 3 414
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,340评论 0 270
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,794评论 1 307
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,102评论 2 330
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,276评论 1 344
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,940评论 5 339
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,583评论 3 322
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,201评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,441评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,173评论 2 366
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,136评论 2 352

推荐阅读更多精彩内容