mysql备份 binlog percona使用

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备份	能做一个数据库的完全备份以及单个备份