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的记录都会保存。