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备份 | 能做一个数据库的完全备份以及单个备份 |
扫一扫
9573
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
