likes
comments
collection
share

【mysql】の 锁机制 | 事务 | 二进制日志 | 备份还原(删库也不怕跑路了~)

作者站长头像
站长
· 阅读数 19

1、并发控制

mysql 数据库 同一时间会有多个用户一起使用

1.1 锁机制

加锁是为了限制别人的操作,不会影响自己。

锁类型:

  • 读锁:共享锁,也称为 S 锁只读不可写(包括当前事务) ,多个读互不阻塞 只能读 不能写 别人也能看
  • 写锁:独占锁,排它锁,也称为 X 锁,写锁会阻塞其它事务(不包括当前事务)的读和写 写锁 别人不可读也不可写
  • S 锁和 S 锁是兼容的,X 锁和其它锁都不兼容

锁粒度:

  • 表级锁:MyISAM 存储引擎 mysql
  • 行级锁:InnoDB。两个都能操作,但是只会对其中一个进行修改。

1.2 手动加锁

一般进行数据备份时,会手动加锁

 格式:
 lock tables 表名 type;
 //type类型分为:
     read
     write

整个数据库加锁:

 flush tables with read lock;

示例:

 lock tables students read;

1.3 解锁

 unlock tables;    //解锁,只能把所有表都解锁

1.4 测试

 drop table students;

2、事务(transactions)

  • 事务是一次成功的、完整的操作过程。有了事务,可以保障数据的完整性和安全性。
  • 事务可以是一条,可以是多条指令的集合。
  • 事务可以减少i/o,也就是减少输入输出的过程。

2.1 A C I D 特性

A:atomicity 原子性:整个事务中的所有操作要么全部成功执行,要么失败后回滚。只要中间有一步失败,就是失败。

C:consistency 一致性:数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)

I: Isolation 隔离性 :一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,实现并发.(不最后提交看不到,脏数

D:durability 持久性:一旦事务提交,其所做的修改会永久保存于数据库中

2.2 基础命令

 `人为开启事务:`
 begin
 begin work
 start transaction     //这三个命令都是人为开启事务
 ​
 commit     //保存、执行、提交。永久保存到硬盘上,如果没提交就是存到内存中。
 ​
 rollback   //回滚,也就是撤销,但是重大级的修改,比如drop、新建表等,没办法撤销

是否需要自动提交:

 set autocommit=0    //是否自动提交事务  退出后要再加,改成1,就是自动提交。

2.3 死锁

两个或多个事务在同一资源相互占用,并请求锁定对方占用的资源的状态

 show processlist;
 kill 13    //13是进程列表

2.4 事务隔离级别

MySQL 支持4种隔离级别,事务隔离级别 从上至下更加严格

 select @@tx_isolation;    //系统隔离级别,是系统自带的变量
 +-----------------+
 | @@tx_isolation  |
 +-----------------+
 | REPEATABLE-READ |
 +-----------------+
 ​
 SET tx_isolation='READ-UNCOMMITTED未提交可读|READ-COMMITTED提交可读|REPEATABLE-READ可重复读|SERIALIZABLE'串读
 vim /etc/my.cnf
 [mysqld]
 transaction-isolation=READ-UNCOMMITTED
 systemctl restart mysqld
  1. 未提交可读:READ-UNCOMMITTED,可读取到未提交数据,产生很多数据垃圾,造成脏读,隔离级别最差。AB两个事务,不需要提交,也可以看到对方的操作
  2. 提交可读:READ-COMMITTED,可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致可读。隔离级别次之。
  3. 幻读:可重复读, REPEATABLE-READ,此为MySQL默认设置的默认隔离级别。 比如A、B两个事务,A事务所有的操作,包括提交和未提交的,B事务都看不到,B只能看到的都是A的初始状态;只有当B事务提交结束,B事务才可以看到A事务提交的修改。
  4. 串读:SERIALIZABLE,可串行化,隔离级别最高,但是一般不使用该隔离级别。 未提交的读事务阻塞修改事务(加读锁,但不阻塞读事务),或者未提交的修改事务阻塞其它事务的读写(加写锁,其它事务的读,写都不可以执行)。会导致并发性能差 会互相影响,加锁
隔离级别脏读可重复读幻读加读锁
读未提交可以出现可以出现可以出现
读提交不允许出现可以出现可以出现
可重复读不允许出现不允许出现可以出现
序列化不允许出现不允许出现不允许出现

MVCC和事务的隔离级别:

MVCC(多版本并发控制机制)只在READ COMMITTED(提交可读)和REPEATABLE READ(可重复读) 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

3、日志

MySQL 支持丰富的日志类型,如下:

  • 事务日志:transaction log

     事务日志的写入类型为"追加",因此其操作为"顺序IO";通常也被称为:预写式日志 write ahead logging事务日志文件:ib_logfile0、ib_logfile1
    
  • 错误日志 error log

  • 通用日志 general log

  • 慢查询日志 slow query log

  • 二进制日志 binary log,比较重要,备份时使用

  • 中继日志 reley log,在主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件 主做了什么修改,把修改的的内容放到中继日志里,发给备,备读取了之后,也同步进行修改。

3.1 事务日志

事务日志:transaction log

  • redo log:实现 WAL(Write Ahead Log) ,数据更新前先记录redo log
  • undo log:保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用,建议和数据文件分开存放

Innodb事务日志相关配置:

 show variables like '%innodb_log%';
 innodb_log_file_size   50331648 #每个日志文件大小  字节
 innodb_log_files_in_group 2     #日志组成员个数
 innodb_log_group_home_dir ./    #事务文件路径
 ll -h /var/lib/mysql

【mysql】の 锁机制 | 事务 | 二进制日志 | 备份还原(删库也不怕跑路了~)

修改事务日志的大小:

 vim /etc/my.cnf
 [mysq1d]
 innodb_log_file_size=503316480    //500M
 innodb_1og_fi1es_in_group=3       //3个

事务日志性能优化:

 innodb_flush_log_at_trx_commit=0|1|2
 ​
 select @@innodb_flush_log_at_trx_commit;    //查看默认值

双1操作:

  • 0:每秒写1次。 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务
  • 1:此为默认值, 立即写入磁盘。日志缓冲区将写入日志文件,并在每次事务完成后执行刷新到磁盘。 这是完全遵守ACID特性
  • 2:每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些, 但操作系统或停电 可能导致最后一秒的交易丢失

事务提交了,意味着操作存储在了磁盘上。

【mysql】の 锁机制 | 事务 | 二进制日志 | 备份还原(删库也不怕跑路了~)

系统比程序稳定

级别012
安全性较高最高最高
性能最高最差较高

安全选1,性能选2,尽量不要选0

3.2 错误日志

错误日志

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

 SHOW GLOBAL VARIABLES LIKE 'log_error' ;
 ​
 yum安装:
 cat /var/log/mysqld.log    //错误日志路径

过滤错误日志:

 grep -i error /var/log/mysqld.log

3.3 通用日志

通用日志:localhost.log ,名字和主机名有关。

通用日志:记录对数据库的通用操作,包括:错误的SQL语句。保存在:file(默认值)或 table(mysql.general_log表)

通用日志相关设置:

 general_log=ON|OFF
 general_log_file=HOSTNAME.log
 log_output=TABLE|FILE|NONE

示例:启用通用日志并记录至文件中

 select @@general_log;        //默认没开启
 set global general_log=1;   //1是开启,0是关闭
 ​
 SHOW GLOBAL VARIABLES LIKE 'log_output';   //默认通用日志存放在文件中
 select @@general_log_file;     //通用日志存放的文件路径
 ll -h /var/lib/mysql

3.4 慢查询日志

慢查询日志:记录执行查询时长超出指定时长的操作

慢查询相关变量:

 slow_query_log=ON|OFF   //开启或关闭慢查询,支持全局和会话,只有全局设置才会生成慢查询文件
 long_query_time=N      //慢查询的阀值,单位秒,默认为10s
 slow_query_log_file=HOSTNAME-slow.log   //慢查询日志文件
 ​
 log_queries_not_using_indexes=ON    //不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语句是否记录日志,默认OFF,即不记录
 log_slow_rate_limit = 1   //多少次查询才记录,mariadb特有
 log_slow_verbosity= Query_plan,explain    //记录内容,和explain配合
 log_slow_queries = OFF      //同slow_query_log,MariaDB 10.0/MySQL 5.6.1 版后已删除
 ​
 //开启
 set  global slow_query_log=1;
 set long_query_time=1;
 ​
 //测试
 select sleep(10)

修改配置文件:

 vim /etc/my.cnf
 slow_query_log=ON
 systemctl restart mysqld
 cat /var/lib/mysql/localhost-slow.log

3.5 二进制日志⭐⭐⭐

  • 记录导致数据改变或潜在导致数据改变的SQL语句
  • 记录已提交的日志
  • 不依赖于存储引擎类型

功能:通过"重放"日志文件中的事件来生成数据副本

注意:建议二进制日志和数据文件分开存放

3.5.1 二进制日志 的3种格式

  1. statement 语句:基于语句记录,记录语句(增删改查等语句)。默认模式( MariaDB 10.2.3 版本以下 ),日志量较少
  2. row 行:基于行记录,记录数据,日志量较大,更加安全,建议使用的格式是MySQL8.0默认格式。所有改动的数据都会被记录。
  3. mixed 混合模式:系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上),但尽量不要用mixed

3.5.2 二进制日志文��� 的2种格式

  1. 日志文件
 mysql|mariadb-bin.文件名后缀,二进制格式,如: on.000001,mariadb-bin.000002
  1. 索引文件
 mysql|mariadb-bin.index,文本格式,记录当前已有的二进制日志文件列表

3.5.3 查看二进制日志的格式

方法一:

 show variables like 'binlog_format';    //5.5之前的老版本默认的格式是statement语句,新版本默认的都是row行。

【mysql】の 锁机制 | 事务 | 二进制日志 | 备份还原(删库也不怕跑路了~)

方法二:

 select @@binlog_format;

3.5.4 如何开启二进制日志⭐

有两个变量需要同时开启:

  1. log_bin
 log_bin=mysql-bin      //指定文件位置。默认是OFF关闭,也就是不启用二进制日志功能
 ​
 select @@log_bin;
 //注意:变量log_bin 和 上面的 log_bin(服务器选项) 是两个不同的意思
  1. sql_log_bin
 sql_log_bin=ON   //是否记录二进制日志,默认ON开启。支持动态修改,系统变量,而非服务器选项
 ​
 select @@sql_log_bin;

示例:开启二进制日志

 [root@7-3 ~]# vim /etc/my.cnf
 log_bin=/data/mysql-bin   //指明二进制日志的位置,后面的路径只是前缀,正常后面还要有.000001、.000002等
 server-id=1              //指明数据库的编号,1可以自己定义
 ​
 [root@7-3 ~]# chown -R mysql.mysql /data/      //修改权限
 [root@7-3 ~]# systemctl restart mysqld
 [root@7-3 ~]# cd /data/
 [root@7-3 data]# ll
 总用量 12
 -rw-r----- 1 mysql mysql 454 7月   3 15:22 mysql-bin.000001
 -rw-r----- 1 mysql mysql 154 7月   3 15:22 mysql-bin.000002     //每重启一次,就会多出来一个编号
 -rw-r----- 1 mysql mysql  46 7月   3 15:22 mysql-bin.index     //索引
 [root@7-3 data]# cat mysql-bin.index    //查看索引,显示二进制文件的个数
 /data/mysql-bin.000001
 /data/mysql-bin.000002

3.5.5 基础命令

  1. 查看二进制文件的大小
 show master logs;
 +------------------+-----------+
 | Log_name         | File_size |
 +------------------+-----------+
 | mysql-bin.000001 |       431 |
 +------------------+-----------+
  1. 查看二进制文件的节点:⭐
 show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000001 |      431 |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+
  1. 删除二进制日志文件:

注意: 在mysql软件里面删

 格式:
 purge binary logs to '日志名称';
 ​
 示例:
 purge binary logs to 'mysql-bin.000002';  //删除2号之前的日志,也就是只删除1
  1. 彻底清空二进制日志
 reset master;
  1. 刷新日志:
 flush logs;

3.5.6 mysqlbinlog 离线查看日志

二进制日志的客户端命令工具,支持离线查看二进制日志

选项含义
-v显示详细信息
--start-position指定开始的位置
--stop-position指定结束的位置
--start-datetime开始时间
--stop-datetime结束时间
 insert into teachers values(null,'wxy',22,'M');     //先人为对数据库进行操作
 ​
 [root@7-3 data]# mysqlbinlog -v /data/mysql-bin.000001    //再查看日志
 ### INSERT INTO `hellodb`.`teachers`
 ### SET
 ###   @1=5
 ###   @2='wxy'
 ###   @3=22
 ###   @4=2
 # at 400     //操作节点、时间点,第400个位置。

备份还原的命令是: mysqldump

4、备份还原

mysql面试题:

  • 主从复制
  • 读写分离

4.1 备份类型

  • 完全备份:备份整个数据集

  • 部分备份:只备份数据子集,如部分库或表

    • 增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂

    增量备份的还原规则是:先备先还,后备后还

    • 差异备份:仅备份最近一次完全备份以来变化的数据或者增长的数据,备份较慢,还原简单,直接还原最新的备份

注意:二进制日志文件(对应增量备份),不应该与完全备份的文件,放在同一磁盘

4.2 冷|温|热备份

  • 冷备:读、写操作 均不可进行,数据库停止服务
  • 温备:可读不可写, 读操作可执行, 但写操作不可执行
  • 热备:读、写操作均可执行 用的最多

MyISAM:温备,不支持热备 不支持 事务

InnoDB:都支持

备份数据

  • cp, tar 等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
  • LVM的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
  • mysqldump:逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备;支持完全或部分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份
  • xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
  • MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
  • mysqlbackup:热备份, MySQL Enterprise Edition 组件
  • mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

4.3 完全备份

4.3.1 物理冷备份

通过打包备份 数据库文件夹的方式备份(一般用于数据库迁移)

备份方式: 冷备份,一定要先停数据库

备份工具: cp tar scp rsync(同步软件)等

InnoDB存储引擎的数据库在磁盘上存储成三个文件:db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

示例: 冷备份还原数据库

 7-3:
 [root@7-3 ~]# systemctl stop mysqld   //首先关闭数据库⭐
 [root@7-3 ~]# scp -r /var/lib/mysql 192.168.125.120:/opt   //把数据库备份到远程主机⭐
 [root@7-3 ~]# rm -rf /var/lib/mysql     //模拟删库故障
 ​
 7-2:
 [root@7-2 ~]# scp -r /opt/mysql 192.168.125.130:/var/lib/
 ​
 7-3:
 [root@7-3 ~]# chown -R mysql:mysql /var/lib/mysql    //z注意权限⭐
 [root@7-3 ~]# systemctl start mysqld     //开启
 [root@7-3 ~]# mysql -uroot -pabc123     //登录

4.3.2 mysqldump 备份还原

热备份, 使用时 mysql软件需要保持开启

mysqldump 选项含义
-A备份所有数据库, -A里包含-E和-R
-B指定备份的数据库
-E备份相关的所有event scheduler
-R备份所有存储过程和自定义函数
--master data ='0或1或2'从哪一个节点开始备份的,2是注释,1是开启,0是关闭。非常重要(此选项需启用二进制日志)
-F生成新的二进制日志

mysqldump的MyISAM存储引擎相关的备份选项��

MyISAM不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作

 -x,--lock-all-tables #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库
 -l,--lock-tables #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
 #注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项:

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用

 --single-transaction
 #此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
 #此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLEDROP TABLERENAME TABLETRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用

生产环境实战备份策略

4.3.2.1 备份表

 格式:
 mysqldump [选项] database [表名]   
 #支持指定数据库和指定多表的备份,但数据库本身定义不备份

示例:

 mysqldump -uroot -pabc123 hellodb students    //指定表

案例实操:

 `备份`
 [root@7-3 ~]# mysqldump -uroot -p hellodb > /data/hellodb.sql    //重定向,不能把密码暴露在命令行
 Enter password: abc123
 `模拟删库`
 (root@localhost) [(none)]> drop database hellodb;
 `创建新库`
 (root@localhost) [(none)]> create database hellodb2;     //只能备份还原表,所以需要手动创建数据库
 `输入重定向`
 [root@7-3 data]# mysql -uroot -pabc123 hellodb2 < /data/hellodb.sql
 `免交互查看是否还原成功`
 [root@7-3 ~]# mysql -uroot -pabc123 -e "select * from hellodb2.students;"

4.3.2.2 备份数据库

 格式:
 mysqldump [选项] -B DB1 [DB2 DB3...]
 #支持指定数据库备份,包含数据库本身定义也会备份

案例实操:

 [root@7-3 ~]# mkdir /backup
 [root@7-3 ~]# mysqldump -uroot -pabc123 -B hellodb > /backup/hellodb.sql
 (root@localhost) [(none)]> drop database hellodb;
 [root@7-3 ~]# mysql -uroot -pabc123 < /backup/hellodb.sql    //恢复备份
 [root@7-3 ~]# mysql -uroot -pabc123 -e"show databases;"
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | hellodb            |    //已还原
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+

4.3.2.3 备份数据库和表

 格式:
 mysqldump [选项] -A [OPTIONS]       
 #备份所有数据库,包含数据库本身定义也会备份

备份:

  • performance_schema 内存中的数据没必要备份
  • information_schema 同上
  • sys 同上
  • mysql 存放了用户信息 需要备份

案例实操:

 [root@7-3 ~]# mysqldump -uroot -pabc123 -A > /backup/all.sql
 [root@7-3 ~]# rm -rf /var/lib/mysql
 [root@7-3 ~]# systemctl stop mysqld
 [root@7-3 ~]# systemctl start mysqld
 [root@7-3 ~]# grep password /var/log/mysqld.log
 [root@7-3 ~]# mysql -uroot -p'-(PLoDiR:8e('
 *****设置密码策略,并修改简单密码*****
 [root@7-3 ~]# mysql -uroot -pabc123 < /backup/all.sql    //还原

4.4 增量备份⭐

实验目的: 采用完全备份+二进制日志, 将数据还原到删库前的状态

  1. 首先开启二进制日志
 [root@localhost ~]# vim /etc/my.cnf
 [mysqld]
 log_bin=/data/mysql-bin      
 server-id = 1
 [root@localhost ~]# mkdir /data
 [root@localhost ~]# chown  mysql.mysql /data/ -R
 [root@localhost ~]# systemctl restart mysqld
  1. 备份文件
 [root@localhost ~]# mysqldump -uroot -pabc123  -A  --master-data=2 > /data/all.sql
 ​
 [root@localhost ~]# mysqldump -uroot -pabc123  -A  --master-data=2 |gzip > /data/all.sql.gz    //备份加压缩
  1. 添加数据测试
 [root@localhost ~]#mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test',20,'M')"
 [root@localhost ~]#mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test1',21,'M')"
 [root@localhost ~]#mysql -uroot -pabc123 -e "insert hellodb.teachers  values(null,'test2',20,'M')"
 [root@localhost ~]#mysql -uroot -pabc123 -e "select * from hellodb.teachers;"
 +-----+---------------+-----+--------+
 | TID | Name          | Age | Gender |
 +-----+---------------+-----+--------+
 |   1 | Song Jiang    |  45 | M      |
 |   2 | Zhang Sanfeng |  94 | M      |
 |   3 | Miejue Shitai |  77 | F      |
 |   4 | Lin Chaoying  |  93 | F      |
 |   6 | wxy           |  22 | M      |
 |   7 | test          |  20 | M      |
 |   8 | test1         |  21 | M      |
 |   9 | test2         |  22 | F      |
 +-----+---------------+-----+--------+
 ​
 ​
 [root@localhost ~]#vim /data/all.sql
 ................................................................
 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
 .......................................................................
  1. 模拟破坏
 (root@localhost) [(none)]> drop database hellodb;
  1. 准备二进制还原文件
 [root@localhost data]# mysqlbinlog  --start-position=154 /data/mysql-bin.000001 > /data/binlog.sql     //生成新的二进制日志文件
  1. 开启数据库
 [root@localhost data]#systemctl start mysqld
 #如果启动不了 把 /var/lib/mysql/  文件夹中的文件全部删除
  1. 还原数据 按顺序还原
 [root@localhost data]# mysql  -uroot -pabc123 < /data/all.sql 
  1. 二进制日志中, 去除掉关于删库的信息
 [root@localhost data]# grep -in drop /data/binlog.sql
 94:drop database hellodb      //第94行存在drop删库的信息    如果存在多行, 删除时注意先删除大数
 [root@localhost data]# sed -i.bak '/^drop/d' /data/binlog.sql    //把以drop开头的行删掉
  1. 利用二进制 sql语句 还原
 mysql> source /data/binlog.sql     //source用来执行脚本命令
  1. 还原前,可以先把二进制日志关闭
 set sql_1og_bin=0;    //0是关闭,1是开启,都是临时性的

4.5 生产环境实战 备份策略

InnoDB建议备份策略

 mysqldump -uroot -pabc123 -A -F -E -R --triggers --single-transaction --master-data=1  >${BACKUP}/fullbak_${BACKUP_TIME}.sql

脚本例子:

 #!/bin/bash
 TIME=`date +%F_%H-%M-%S`
 DIR=/backup
 DB=hellodb
 PASS=test
 [ -d $DIR ] || mkdir $DIR
 mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

生产例子分表分库

 for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
 ​
 ​
 crontab  -e
 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin 
 0 1 * * *   /bin/bash    /data/backup
 ​
 ​
 vim     backup.sh
 for  db  in   `mysql -uroot -pabc123 -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`
 do mysqldump -B $db | gzip > /backup/$db.sql.gz
 done
 ​
 ​
 ​
 mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -
 B $db | gzip > /backup/$db.sql.gz;done
 ​
 ​
 ​
 mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
转载自:https://juejin.cn/post/7387244075127423011
评论
请登录