mysql备份 binlog percona使用

本文详细介绍了MySQL的binlog备份,包括如何启用binlog、进行完整备份、差异备份以及如何利用binlog恢复数据。此外,还讲解了binlog_format的不同模式,并介绍了Percona XtraBackup工具进行物理备份的方法,以及如何恢复单张表的数据。通过实例展示了增量备份的流程。
摘要由CSDN通过智能技术生成

mysql备份 binlog percona使用

完整备份

  • 备份

    mysqldump -uroot -p -B xxxx库 xxx库 > xxx.sql
    
  • 恢复

    mysql -uroot -p xxx库 < xxx.sql
    

binlog差异备份

binlog 二进制日志文件 记录了mysql的所有DML操作 和SQL server数据库开启完整模式的原理一样

select,show这些操作不会记录在binlog (功能决定binlog没有记录查询日志的必要)

可以根据binlog日志来做数据恢复,增量备份,主主复制和主从复制等

  • 启动binlog
vim /etc/my.cnf
==============================
[mysqld]
server_id=1  #指定server_id   防止和其他集群中集群重名的字符串 测试环境就一个机子 就1了
log-bin=/mylog/db50           #指定日志目录及名称
==============================================#上面的简单启动  下面三条指定内容多点
log_bin=ON           #打开日志功能
log_bin_basename=/var/lib/mysql/mysql-bin #binlog的基本文件名,后面会追加标识来表示每一个文件
log_bin_index=/var/lib/mysql/mysql-bin.index#指定binlog文件的索引文件,管理所有binlog文件的目录
===================================================
mkdir /mylog      #创建目录
systemctl restart mysqld.service       #直接重启看报错

journalctl -xe  #查看系统日志
-- Unit mysqld.service has begun starting up.
Jan 22 04:08:26 host50 mysqld[3025]: Initialization of mysqld failed: 0
Jan 22 04:08:26 host50 systemd[1]: mysqld.service: control process exited, code=exited status=1
Jan 22 04:08:26 host50 systemd[1]: Failed to start MySQL Server.
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
.....
#系统尝试了多次启动都失败了   报错日志看不出什么就是启动失败 好像没什么细节提示

#无法读取日志目录
chown mysql /mylog #给权限
systemctl restart mysqld.service  #再启动就成功了    给服务权限排错好像没什么提示

mysql -uroot -p
show variables like '%log_bin%'; #查看是否启动binlog
+---------------------------------+-------------------+
| Variable_name                   | Value             |
+---------------------------------+-------------------+
| log_bin                         | ON                |  #开启
| log_bin_basename                | /mylog/db50       |  #后缀跟
| log_bin_index                   | /mylog/db50.index | #管理目录的文件
| log_bin_trust_function_creators | OFF               |
| log_bin_use_v1_row_events       | OFF               |
| sql_log_bin                     | ON                |
+---------------------------------+-------------------+

cat /mylog/db50.index   #看下怎么管理的
/mylog/db50.000001
/mylog/db50.000002
/mylog/db50.000003
/mylog/db50.000004            #直接保存所有binlog文件的绝对路径
  • 查看binlog日志

**执行flush logs 会关闭当前的binlog文件 并且会打开一个新的binlog文件 日志分割 **

好像还会影响其他日志 还没了解

mysql -uroot -p

show master status;  #查看日志信息
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000001 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+

flush logs;  #刷新日志
show master status;  #再次查看日志内容
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000002 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+

create tables db1.t6(name varchar(10));
show master status;  #再次查看日志内容

+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000002 |      324 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
#Position位置变了   因为创建了一个库所以日志多了操作记录了 后面用mysqlbinlog可以看看

flush logs   #多刷新几次

purge master logs to 'db50.000003'; #删除编号3号日志之前的日志

system ls /mylog/    #mysql命令行下查看日志文件
db50.000003  db50.000004  db50.index

system cat /mylog/db50.index;   #查看索引文件
/mylog/db50.000003
/mylog/db50.000004
  • 使用binlog日志恢复数据
    • 前置操作
vim /etc/my.cnf
=========================
[mysqld]
server_id=1
log-bin=mysql-bin    #定义日志名 不知道绝对路径 默认是在/var/lib/mysql/目录下
binlog_format="mixed"  #定义日志格式
=================================
systemctl restart mysqld    #重启服务

ls /var/lib/mysql/mysql-bin.*  #查看binlog功能是否启动   有没有.index 和编号文件
/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index

mysql -uroot -p
flush logs   #生成新的binlog文件 

create database mydb1;
use mydb1;
create table tb1(
id int(4) not null,
name varchar(24)
);

insert into tb1 values(1,'Jack'),(2,'Kenthy'),(3,'Bob');
select * from tb1;  #查询
+----+--------+
| id | name   |
+----+--------+
|  1 | Jack   |
|  2 | Kenthy |
|  3 | Bob    |
+----+--------+

delete from tb1; #删除内容
  • 使用binlog日志恢复数据

    • 恢复操作

    使用mysqlbinlog程序来完成操作

which mysqlbinlog   #先看下程序位置
/usr/bin/mysqlbinlog

mysqlbinlog /var/lib/mysql/mysql-bin.000002  #查看记录的语句
#可以看见操作语句 时间等信息    还有些隐藏操作

#现在开始恢复操作  看记录里面语句时间节点
mysqlbinlog \
	--start-datetime='开始时间' \
	--stop-datetime='结束时间' \
	/var/lib/mysql/mysql-bin.000002 | mysql -uroot -p  #通过管道给mysql

mysql -uroot -p
select * from mydb1.tb1;  #查看是否恢复了  
+----+--------+
| id | name   |
+----+--------+
|  1 | Jack   |
|  2 | Kenthy |
|  3 | Bob    |
+----+--------+
binlog总结
  • binlog文件会随服务启动创建一个新文件 每次重启服务也会增加
  • 通过flush logs可以手动刷新日志,生成一个新的binlog文件
  • 通过show master status可以查看binlog的状态
  • 通过purge master logs to '尾缀名.00000x’来删除编号前的日志文件
  • 通过reset master可以清空binlog日志文件
  • 通过mysqlbinlog工具来查看binlog日志的内容
  • 通过mysqlbinlog --start-datetime 和 – stop-datetime来恢复
binlog_format参数了解

binlog_format有三种格式, STATEMENT、ROW、MIXED。statement在二进制日志里,记录的是实际的SQL语句,ROW在二进制日志中记录的是实际行的变更

在二进制日志里,MIXED默认还是STATEMENT格式记录,但在下面6中情况下回转化为ROW格式

  • DNB引擎,表的DML操作会以ROW格式记录
  • SQL语句里包含UUID()函数
  • 自增长字段被更新了
  • 包含了INSERT DELAYED语句
  • 使用了用户定义函数(UDF)
  • 使用了临时表

percona软件备份

完整备份

4.1备份 4.51恢复

  • 安装percona备份发送

    rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm #依赖包
    yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
    
    
    #备份数据库
    innobackupex --user root --password 123456 /allbak --no-timestamp 
    
    #确认备份文件
    ls /allback
    backup-my.cnf  db1_B  ib_buffer_pool  mydb   mydb2  performance_schema  xtrabackup_binlog_info  xtrabackup_info
    db1            db3    ibdata1         mydb1  mysql  sys                 xtrabackup_checkpoints  xtrabackup_logfile
    
    scp -r /allback root@192.168.4.51:/root/   #发送数据库备份
    
  • 51恢复

    #4.51
    rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
    yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
    
    systemctl stop mysqld
    rm -rf /var/lib/mysql/*   #清空数据
    
    innobackupex --apply-log --redo-only /root/allbak  #恢复数据
    
    innobackupex --copy-back /root/allbak #拷贝数据
    
    chown -R mysql:mysql   /var/lib/mysql   #修改文件权限给mysql   里面有文件是root的 不修改启动会报错
    
    mysql -uroot -p   #登录查看
    show databases; #成功恢复恢复单张表
    
恢复单张表
mysql -uroot -p
delete from db3.user2       #删除了数据记录

alter table db3.user2 discard tablespace;  #删除表空间
exit

innobackupex --apply-log --export /allbak    #导出表信息

cp /allbak/db3/user2.{cfg,exp,ibd} /var/lib/mysql/db3/  #拷贝表信息到数据库目录下

chown mysql:mysql /var/lib/mysql/db3/user2.*  #给权限

mysql -uroot -p
alter table db3.user2 import tablespace;
exit

rm -rf /var/lib/mysql/db3/user2.{cfg,exp}

mysql -uroot -p
select * from db3.user2
增量备份
#4.1
#完全备份
innobackupex --user root --password 123456 /fullbak --no-timestamp

mysql -uroot -p
insert into db3.user2 values(6,'jack');
exit
#第一次增量备份
innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp

mysql -uroot -p
insert into db3.user2 values(5,'ttt');
exit
#第二次增量备份
innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/newdir1 --no-timestamp

#发送给4.51主机
scp -r /fullbak root@192.168.4.51:/root/
scp -r /new1dir root@192.168.4.51:/root/
scp -r /new2dir root@192.168.4.51:/root/

#4.51
systemctl stop mysqld
rm -rf /var/lib/mysql/*

innobackupex --apply-log --redo-only /root/fullbak #准备恢复

innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new1dir #合并日志

innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new2dir #合并日志

#合并之后   增量备份的就不需要了
rm -rf /root/new1dir
rm -rf /root/new2dir


innobackupex --copy-back /root/fullbak #拷贝文件到数据库目录下
chown -R mysql:mysql /var/lib/mysql  #备份时用的root  mysql没权限使用这些数据库文件 会失败
systemctl restart mysqld
mysql -uroot -p123456
#查看信息即可

备份总结

备份类型说明
物理备份将所有文件保存下来,很直白,但是空间浪费大 使用不多
逻辑备份mysqldump将数据库备份成一个文件,执行SQL语句实现恢复数据操作
binlog备份增量备份,同上,SQL语句恢复,按时间段记录
percona的innobackup备份能做一个数据库的完全备份以及单个备份
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值