自学内容网 自学内容网

mysql存储引擎和备份

索引

事务

存储引擎

概念:存储引擎,就是一种数据库存储数据的机制,索引的技巧,锁定水平。

存储引擎。存储的方式和存储的格式。

存储引擎也属于mysql当中的组件,实际上操作的,执行的就是数据的读写I/O。

mysql的存储引擎的分类:

mysql5.5之后默认开始使用innodb,事务型速记存储引擎。支持ACID,支持行锁定。

MYisam:5.5之前默认的存储引擎,插入的速度和查询速度很快,但是不支持事务。

Memory:内存型存储引擎,数据在写时都保存在内存当中,一旦重启所有数据全部消失。

CSV:逗号分割数据的存储引擎,数据文件.csv文件保存的,execl.保存的文件就是一个普通的文本文件。不支持索引。

innodb存储引擎:

1、读写阻塞(锁表)和事务的隔离级别

2、能够高效的缓存数据支持多种类的索引

3、表的索引的类型默认BTREE

4、支持外键,支持全文索引

5、对硬件的资源要求比较高

6、行级锁定,会把行锁住,禁止操作

模糊查询:

like进行查询时,会进行全表扫描,在扫描的过程中会锁定整个表

没有创建索引的列,进行查询时,也会锁定整个表。

使用的是索引列,锁定条件的行,行锁定。

innoDB行锁和索引的关系

行锁是通过索引来实现的

如果没有索引,innodb会使用默认的隐藏索引来对记录进行加锁。

加了索引就是锁行

不加索引就是锁表

mysql默认就是自动提交写入。

oracle提交才能写入

死锁:事务相互等待对方的资源,最后形成一个环路状态造成的。

发生了死锁,数据会自动选择一个事务作为受害者,回滚事务可以解除死锁。

for update 排他锁,当一个事务的操作未完成时,其他事务可以读取但是不能写入。写锁。

如何避免死锁的情况出现:

1、以固定的顺序访问表和行

2、大事务尽量拆分成小的事务

3、为表添加合理的索引

mysql的备份、恢复和日志管理(配置文件当中的设置)

备份的目的是什么:

备灾。

在生产环境中,数据的安全性非常重要。

造成数据丢失的原因:

1、程序出错

2、人为的问题

3、磁盘故障

备份的分类:

物理备份:对磁盘或者文件直接进行备份。

冷备分:脱机备份,先把指定的程序关闭,然后对资料进行备份

热备份:联机备份,不用关闭程序就可以对资料进行备份


在命令行操作:
mysqldump -u root -p --databases 库名 > /opt/文件名.sql
#备份单个库
mysqldump -u root -p --databases 库1 库2 > /opt/文件名2.sql
#多个备份
mysqldump -u root -p --all-databases > /opt/文件名3.sql
#备份所有库

逻辑备份:

根据数据库文件当中保存的sql语句,表结构,等等,以特定的格式和命令对文件的内容进行还原。

热备份的一种。

只能对表,库没了没有办法恢复。

主从复制可以恢复。

物理备份 全量备份:

把数据库的内容整一个一次性的

数据恢复


只恢复单个表或者多个表:
准备另外一张表插入数据
恢复一张表:
mysqldump -u root -p test1 info1 > opt/test1_info1.sql
 
msql -u root -p -e 'drop table test1.info1;'
#指定库删除表
 
mysql -u root -p test1 < /opt/test1_info1.sql
#指定库名恢复
#刷新一下查看一下
 
恢复多个表:
mysqldump -u root -p test1 info1 info2 > /opt/test1_info1-2.sql
 
msql -u root -p -e 'drop table test1.info1;'
 
msql -u root -p -e 'drop table test1.info2;'
 
mysql -u root -p kgc < /opt/test1_info1-2.sql
#指定库名恢复
#刷新一下查看一下
 
MySQL1全部数据库的逻辑备份文件恢复到MySQL2。
 
scp root@20.0.0.60:/opt/all_database.sql /opt/
 
mysql -u root -p < all_database.sql
#用sql语句的方式热备份直接转换。

mysql自带的备份命令。可以备份库,也可以备份库里面的表

mysqldump

增量备份:

1

2

3

4

开启二进制日志的功能:

binlog 逻辑备份,会生成一个文件,这个里面包含了sql语句,要使用特定的方式和语句才能恢复。

binlog format=MXED

记录二进制的文件·格式

STATEMENT基于sql语句:只是记录用户操作的sql语句,高并发的情况之下,记录操作的sql语句的顺序可能会出错。导入数据时,就会有丢失或者误差。效率高。

ROW 基于行,记录每一行的数据,准确,高并发也不会出错,但是恢复效率底

MIXED 混合模式,正常情况下使用statement,高并发使用row,只能判断

表数据设置多一点
 
select * from info1;
#查看数据是否写入
 
cd /usr/local/mysql/data/
#会生成了两个文件
mysql -bin.index
mysql-bin.000001
表内写入信息后再查看日志文件 mysql-bin.000001
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
#查看新插入表的日志
 
mysqladmin -u root -p flush-logs
#刷新日志.
#此时data目录下会生成一个新的日志文件mysql-bin.000002
 
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
#刷新之后更新的内容会更新在2里面这就是断点
 
如何恢复:
mysqlbinlog --no-defaults mysql-bin00000.1 | mysql -u root -p
#增量备份,恢复之前表内插入的数据。这个叫断点恢复。
 
 
此时再对表插入信息。此时新插入的数据再000002里面。只要没有刷新日志就不会出现断点。会先插入再删除。
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
 
如果需要备份新的数据之前需要再刷新一次。
mysqladmin -u root -p flush-logs
#刷新日志.
 
重新在表内插入数据
此时断点之后数据都在新生成的000003里面
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
 
mysqladmin -u root -p flush-logs
#刷新日志断点
 
再删除表内数据,这时候删除的操作会保存到000004里面
 
mysqlbinlog --no-defaults --base64-out=decode-rows -v mysql-bin.000003

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

查询日志的保存位置

log-error=/usr/local/mysql/data/mysql_error.log

错误的保存位置,错误日志默认是开启的

slow_query_log=ON

开启慢查询日志

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.lgo

设定慢查询日志的位置

long_query_time=5

默认的慢查询时间是10秒。超过5s的记录都会保存。


原文地址:https://blog.csdn.net/yexiaoyex/article/details/140549403

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!