逻辑备份与恢复

逻辑备份与恢复

备份与恢复名词解释

• 冷备：关闭数据库,数据库没有任何变更的情况下,进行备份数据.

• 热备：在数据库正常业务时备份数据

• 全量备份：备份所有数据

• 增量备份：备份某一段时间内变化了的数据

逻辑备份与恢复

逻辑备份

特点：

  1. 备份出来的是SQL语句，可读性高，便于处理

  2. 备份和恢复粒度较为灵活，可以是库、表

  3. 逻辑备份必须是热备

  4. 备份/恢复速度通常慢于物理备份

 

主流工具：

mysqldump

  1. 客户端通用参数 　　　

    • 本地备份 : mysqldump -uroot -p -S /mysql/3306/tmp/mysql.sock

    • 远程备份 : mysqldump -uroot -p -h 192.168.2.25 -P3306

  2. 基本参数应用

    • -A：全备参数

    • -B：备份多个单库

    • -R：备份存储过程及函数

    • --triggers：备份触发器

    • --default-character-set：设置字符集

    • • 例子:

      • 　　#全库备 mysqldump -uroot -p123456 -S /tmp/mysql.sock -A -R --triggers --default-character- set=utf8 > /home/hidden/Tools/mysql/back/full$(date "+%Y%m%d%H%M%S").sql

      • 　　#单库备 mysqldump -uroot -p123456 -S /mysql/3306/tmp/mysql.sock -B baiding_demo mysql -R --triggers -- default-character-set=utf8 > /mysql/backup/baiding$(date "+%Y%m%d%H%M%S").sql

  3. MySQL\MySQL Server 5.7\bin>mysqldump.exe -hlocalhost -P3306 -uroot -p  -B springboot  > d:/sql/sql

  4. 高级参数应用

    --single-transaction

    • 在不加--single-transaction：所有被dump的表都会被锁(--lock-tables 默认开启)，

    • 加上--single-transaction：对innodb进行一致性备份,对非innodb表可 以实现自动锁表功能

    --master-data

    1. 　在备份时，会自动记录，二进制日志文件名和位置号　　

      •  0 默认值

      • 1 以change master to命令形式，可以用作主从复制 

      • 2以注释的形式记录，备份时刻的文件名+postion号（备份到此）

    2. 会产生全局锁(--lock-all-tables)

    3. 联合--single-transaction，则只对非InnoDB表进行锁表备份，InnoDB表进行热备

    4. 使用该参数的用户必须具有reload权限

      • 例子:

        mysqldump -uroot -p123456 -S /mysql/3306/tmp/mysql.sock -B baiding_demo -R --triggers --master-data=2 --single-transaction --default-character-set=utf8 > /mysql/backup/baiding$(date "+%Y%m%d%H%M%S").sql

mysqlbinlog

show binlog ebents in 'mysql-bin.000001';
 ​

 mysqlbinlog的基本参数使用

--base64-output 解码处理

 

 

-v：重组伪SQL语句的输出

 

 

--set-charset：设置字符集

 

 

--database：只处理指定的数据库

 

 

--start-position：指定分析事件的起始位置

 

 

--stop-position：指定分析事件的结束位置

 

 

--start-datetime：指定分析的起始时间

 

--stop-datetime：指定分析的结束时间

 

 

例子:

一般情况下，会读取 my.cny中的 default-character-set的参数配置

所以如果不加 --no-defaults参数时会报错：

 

--默认读取 mysqlbinlog --no-defaults mysql-bin.000017 |more

遇到row格式时

 

--输出肉眼可以识别的文本 mysqlbinlog --no-defaults --base64-output=decode-row -v -v mysql-bin.000017 |more

解析行格式后

 

--指定字符集 mysqlbinlog --no-defaults --base64-output=decode-row -v -v --set-charset=utf8 mysql- bin.000017 |more

--从指定定位置提取数据 mysqlbinlog --no-defaults --base64-output=decode-row -v -v --start-position=154 --stop- position=986490 --set-charset=utf8 mysql-bin.000017 |more

--转出MySQL可使用的SQL文件 mysqlbinlog --no-defaults --start-position=154 --stop-position=986490 --set-charset=utf8 mysql-bin.000017 > /mysql/backup/bin.sql


 

常用的binlog日志操作命令

 mysqlbinlog mysql-bin.000001

1、查看所有binlog日志列表

　　　　show master logs;

2、查看master状态，即最后（最新）一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值。

　　　　show master status;

3、flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;

　　　　flush logs;

　　　　　　注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加-F选项也会刷新binlog日志；

4、重置（清空）所有binlog日志

　　　　reset master;

查看binlog日志内容，常用有两种方式：

　　1、使用mysqlbinlog自带查看命令法

　　　　注意：

　　　　　　a、binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看。

　　　　　　b、binlog日志与数据库文件在同目录中。

　　　　　　c、在Mysql5.5以下版本使用mysqlbinlog命令时如果报错，就加上"--no-defaults"选项

　　　　　　d、使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析分析

 

解释：

　　　　　　　　　　server id 1:数据库主机的服务号

　　　　　　　　　　end_log_pos 796 :sql结束时的pos节点

　　　　　　　　　　thread_id=11:线程号

　　　　　　e、也可根据时间点查看

　　　　　　　

 mysqlbinlog --no-defaults mysql-bin.000720 --start-datetime="2018-09-12 18:45:00" --stop-datetime="2018-09-12:18:47:00"

　　2、上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

　　

 　　mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

　　　　参数解释：

　　　　　　a、IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）

　　　　　　b、FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）

　　　　　　c、LIMIT【offset】：偏移量(不指定就是0)

　　　　　　d、row_count :查询总条数（不指定就是所有行）

 

　2、上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！

　　　　a、查询第一个最早的binlog日志：

　　　　　　show binlog events\G;

　　　　b、指定查询mysql-bin.000002这个文件

　　　　　　show binlog events in 'mysql-bin.000002'\G;

　　　　c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：

　　　　　　show binlog events in 'mysql-bin.000002' from 624\G;

　　　　d、指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）

　　　　　　show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

　　　　e、指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。

　　　　　　show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

远程备份binlog：

远程备份时需要赋予权限

mysqlbinlog --no-defaults --read-from-remote-server --raw --result- file=/mysql/backup/IP55- --host=192.168.2.55 --port=3306 --user=td --password=123456 --stop-never mysql-bin.000001

  • read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找 本地的binlog。

  • raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保 存。

  • user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。

  • stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的 binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开， mysqlbinlog就会不断的复制远程服务器上的binlog。

  • mysql-bin.000001：代表从哪个binlog开始复制。

  • result-file：用于设置远程服务器的binlog，保存到本地的前缀。譬如对于mysql- bin.000001，如果指定--result-file=/test/backup-，则保存到本地后的文件名为 /test/backup-mysql-bin.000001。

  • 物理备份与恢复

      • 详细见XtraBackup
posted @ 2023-08-31 20:12  hidden-T  阅读(32)  评论(0)    收藏  举报