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

2024年 7月 4日 66.7k 0

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
  • 未提交可读:READ-UNCOMMITTED,可读取到未提交数据,产生很多数据垃圾,造成脏读,隔离级别最差。AB两个事务,不需要提交,也可以看到对方的操作
  • 提交可读:READ-COMMITTED,可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个提交数据,导致每次读取数据不一致可读。隔离级别次之。
  • 幻读:可重复读, REPEATABLE-READ,此为MySQL默认设置的默认隔离级别。 比如A、B两个事务,A事务所有的操作,包括提交和未提交的,B事务都看不到,B只能看到的都是A的初始状态;只有当B事务提交结束,B事务才可以看到A事务提交的修改。
  • 串读: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】の 锁机制 | 事务 | 二进制日志 | 备份还原(删库也不怕跑路了~)-1

    修改事务日志的大小:

     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】の 锁机制 | 事务 | 二进制日志 | 备份还原(删库也不怕跑路了~)-2

    系统比程序稳定

    级别 0 1 2
    安全性 较高 最高 最高
    性能 最高 最差 较高

    安全选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种格式

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

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

    3.5.3 查看二进制日志的格式

    方法一:

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

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

    方法二:

     select @@binlog_format;
    

    3.5.4 如何开启二进制日志⭐

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

  • log_bin
  •  log_bin=mysql-bin      //指定文件位置。默认是OFF关闭,也就是不启用二进制日志功能
     ​
     select @@log_bin;
     //注意:变量log_bin 和 上面的 log_bin(服务器选项) 是两个不同的意思
    
  • 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 基础命令

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

     格式:
     purge binary logs to '日志名称';
     ​
     示例:
     purge binary logs to 'mysql-bin.000002';  //删除2号之前的日志,也就是只删除1
    
  • 彻底清空二进制日志
  •  reset master;
    
  • 刷新日志:
  •  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 TABLE,DROP TABLE,RENAME TABLE,TRUNCATE 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 增量备份⭐

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

  • 首先开启二进制日志
  •  [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
    
  • 备份文件
  •  [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    //备份加压缩
    
  • 添加数据测试
  •  [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;
     .......................................................................
    
  • 模拟破坏
  •  (root@localhost) [(none)]> drop database hellodb;
    
  • 准备二进制还原文件
  •  [root@localhost data]# mysqlbinlog  --start-position=154 /data/mysql-bin.000001 > /data/binlog.sql     //生成新的二进制日志文件
    
  • 开启数据库
  •  [root@localhost data]#systemctl start mysqld
     #如果启动不了 把 /var/lib/mysql/  文件夹中的文件全部删除
    
  • 还原数据 按顺序还原
  •  [root@localhost data]# mysql  -uroot -pabc123 < /data/all.sql 
    
  • 二进制日志中, 去除掉关于删库的信息
  •  [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开头的行删掉
    
  • 利用二进制 sql语句 还原
  •  mysql> source /data/binlog.sql     //source用来执行脚本命令
    
  • 还原前,可以先把二进制日志关闭
  •  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
    

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论