Mysql事务、日志、数据库备份-白红宇

Mysql事务、日志、数据库备份

阅读量：5298 次

发布时间：2019-06-14

本文共 3110 字，大约阅读时间需要 10 分钟。

一：事务

一：事务介绍

保证交易的完整性

ACID特性：

Atomic（原子性）

　　所有语句作为一个单元全部成功执行或全部取消。不允许出现中间过程.

Consistent（一致性）

　　如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

Isolated（隔离性）

　　事务之间不相互影响。

　　两个方面: 修改同一行 , 一致性读

　　行级锁:事务修改行,会锁定这行(持有这行的锁)

Durable（持久性）

　　事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

隔离级别(一致性读):

RU （read-uncommitted）读未提交：A可以读到B未提交的数据比如B修改了age的值但未提交，A可以查到修改后的值

RC （read-committed）　不可重复读：A只能读到B修改后commit的值，B修改值但未commit，A只能读到修改前的值

RR （repeatable-read）mysql默认的隔离级别可重复读：A的会话先开启，事务B修改了age值并且commit，但是A读取的仍然是修改前的值，除非A重新开始会话，select的才是正确的值，但是

　　　　　　　　　　　该模式下的insert、update和delete 会使用最新的数据进行操作，保证数据的一致性

S （serializable）串行化

事务控制语句

begin;

xxx

commit; 正常情况

begin;

xxx

xxx 半路死亡

begin;

xxxxx

xxx

rollback; 回滚

出现隐式提交的情况：

隐式提交不做commit 自动提交了

mysql> show variables like 'auto%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| autocommit | ON |

| automatic_sp_privileges | ON |

+--------------------------+-------+

4 rows in set (0.01 sec)

可以看到mysql默认设置了autocommit 一条sql语句为一个事务，一般涉及金钱交易的业务会将autocommit 关闭

临时设置：

　　set autocommit=0;

永久:

　　my.cnf

　　autocommit=0;

二：Mysql日志

一：错误日志默认是开启的

错误日志位置可在配置文件/etc/my.cnf中进行配置：

log_error=/var/log/mysql.log

分析日志找[error]就行了

二：二进制日志 (binlog,逻辑型日志) 默认不开启

1：作用：

　　记录所有变更类的语句

　　DDL,DCL :以语句方式(statement)记录

　　DML(已提交的事务语句insert，update，delete):默认是以行模式记录(row模式,数据行的变化)

　　可以做数据恢复和操作的审计

2：配置方法：

　　log_bin=/opt/mysql/data/mysql-bin

　　binlog_format=row

　　server_id=6

　　sync_binlog=1

3：查看日志文件

查看能识别的日志文件

　　mysql> show binary logs;

查看正在使用的日志文件

　　mysql> show master status;

4: 日志内容查看

按事件查看日志内容

　　mysql> show binlog events in 'mysql-bin.000012';

直接查看日志内容

　　mysqlbinlog --base64-output=decode-rows -vvv /opt/mysql/data/mysql-bin.000012 |more

5 截取二进制日志

[root@standby data]# mysqlbinlog --start-position=219 --stop-position=186613 /opt/mysql/data/mysql-bin.000012 >/tmp/binlog.sql

三：慢日志(slow-log) 默认是关闭的

功能：记录慢语句的日志文件，可以根据需要，开启一段时间来进行统计

配置文件：
slow_query_log=1
slow_query_log_file=/opt/mysql/data/standby-slow.log
long_query_time=0.1 #记录查询时间超过0.1s的
log_queries_not_using_indexes=1 #记录查询不使用索引的语句

可以使用Box Anemometer基于pt-query-digest将MySQL慢查询可视化

三：Mysql的备份与恢复

一：备份的种类

逻辑备份:SQL语句的备份

物理备份:数据页备份

二：逻辑备份工具的介绍

mysql数据导入到文件中，首先得在配置文件中授权tmp为信任文件

select xxxx from t1 into outfile '/tmp/redis.txt'

将mysql中数据导入到redis中

mysql -uroot -p123 -e "select concat('hmset city_',id,' id ', id,' name ',name,' countrycode ',countrycode,' district ',district,' population ',population) from world.city limit 10 "|redis-cli

如何备份：

mysqldump

参数，和mysql命令通用，用来登录数据库

-u

-p

-S

-h

-P

其他备份参数

-A 全库备份

　　mysqldump -uroot -p123 -A >/backup/full.sql

-B 备份一个或多个指定库