首页 > 数据库 >MySQL的数据备份

MySQL的数据备份

时间：2022-08-18 21:11:39浏览次数：54  
标签：-- root 备份 mysql 数据备份 MySQL db02 backup

MySQL数据备份和恢复

备份原因

1）备份就是为了恢复。

2）尽量减少数据的丢失（公司的损失）

备份类型

# 冷备
需要停机维护，停止服务，备份数据

# 温备
不需要停机，也不需要停服务，但是备份数据的时候，会锁表

# 热备（innodb）
不需要停机，也不需要停服务，备份数据的时候也不会锁表

备份方式

  • 逻辑备份（MySQL的逻辑备份，不支持增备）

    • mysqlbinlog
    • replication（主从复制）
    • into outfile
    secure-file-priv=/tmp
mysql> select * from world.city into outfile '/tmp/world_city.data';
    • mysqldump

  • 物理备份

    • Xtrabackup

备份策略

  • 全量备份
  • 增量备份
  • 差异备份

备份工具

1.mysqldump(逻辑)
mysql原生自带很好用的逻辑备份工具

2.mysqlbinlog（逻辑）
实现binlog备份的原生态命令

3.xtrabackup（物理）
precona公司开发的性能很高的备份工具

备份工具-mysqldump

-u：指定用户
-p：指定密码
-h：指定主机
-S：指定socker
-P：指定端口

备份选项

-A：备份所有库，全备
mysqldump -uroot -p456 -A > /tmp/full.sql

-B：备份指定数据库

指定单库备份

# 备份单库
[root@db02 tmp]# mysqldump -uroot -p123 -B world > /tmp/world1.sql

# 备份多个库
[root@db02 tmp]# mysqldump -uroot -p123 -B world wc > /tmp/world.sql

# 只备份表，不备份库
[root@db02 data]# mysqldump -uroot -p123 world > /tmp/world.sql

# 恢复时，要指定一个数据库
[root@db02 data]# mysql -uroot -p123 world < /tmp/world.sql

# 备份单表world库下的city表
[root@db02 tmp]# mysqldump -uroot -p123 world city > /tmp/city.sql

# 备份时刷新binlog日志
-F：刷新binlog
[root@db02 tmp]# mysqldump -A -F > /tmp/full.sql

# 备份表结构
-d：仅表结构
[root@db02 tmp]# mysqldump -A -d > /tmp/full.sql

# 备份表数据
-t：仅数据
[root@db02 tmp]# mysqldump -A -t > /tmp/full.sql

打点备份

--master-data=[0|1|2]
[root@db02 tmp]# mysqldump -A --master-data=0 > /tmp/full.sql // 关闭打点备份

[root@db02 tmp]# mysqldump -A --master-data=1 > /tmp/full.sql // 可以用来将数据恢复到准备做该库的从库上

[root@db02 tmp]# mysqldump -A --master-data=2 > /tmp/full.sql // 单纯只为了打点备份

快照备份

[root@db02 tmp]# mysqldump -A --master-data=2 --single-transaction > /tmp/full.sql1088

特殊备份选项

-R：备份存储过程和删除
--triggs：备份触发器
-x：锁表备份（温备，不用）

完整的备份语句

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction >/tmp/full.sql

# 优化：压缩备份
[root@db02 data]# mysqldump -uroot -p456 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/zls.sql.gz

# 恢复数据
[root@db02 data]# zcat /tmp/zls.sql.gz|mysql -uroot -p456

# 优化：加上日期
# 精确到天
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F).sql.gz

# 精确到小时
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F-%H).sql.gz

# 精确到分钟
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F-%H-%m).sql.gz

# 精确到秒
[root@db02 data]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /tmp/zls_$(date +%F-%T).sql.gz

恢复数据操作

# 先不记录二进制日志
mysql> set sql_log_bin=0;

# 库内恢复操作
mysql> source /backup/full.sql

# 库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql

注意：

1）mysqldump在备份和恢复时都需要MySQL实例启动为前提

2）一般数据量级100G以内，大约15-30分钟可以恢
复（PB、EB就需要考虑别的方式）

3）mysqldump是以覆盖的形式恢复数据的

#!/bin/bash
num=1
while true;do
mysql -uroot -p123 -e "insert into proc.proc value($num);commit;"
(( num++ ))
sleep 1
done

# 1.准备新环境
[root@m01 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

# 2.发送全备到新环境
[root@db02 tmp]# scp zls_2022-08-18-11-45-16.sql.gz 172.16.1.61:/tmp

# 3.恢复全备到新环境
[root@m01 mysql]# zcat /tmp/zls_2022-08-18-11-45-16.sql.gz |mysql

# 4.停掉连接数据库的程序


# 5.停掉旧的数据库
[root@db02 tmp]# /etc/init.d/mysqld stop

# 6.查看全备文件，找到起始位置点
[root@db02 tmp]# zcat /tmp/zls_2022-08-18-11-45-16.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=137455268;

# 7.查看binlog文件找到结束位置点
第一段结束位置点：137471450
第二段起始位置点：137479557
第二段结束位置点：137567163
mysqlbinlog --start-position=137455268 --stop-position=137471450 mysql-bin.000013 >
/tmp/inc1.sql
mysqlbinlog --start-position=137479557 --stop-position=137567163 mysql-bin.000013 >
/tmp/inc2.sql

# 8.发送到新环境
scp /tmp/inc*.sql 172.16.1.61:/tmp

物理备份

Xtrabackup安装

# 1.下载
[root@db02 ~]# wget http://test.driverzeng.com/MySQL_Package/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

# 2.安装
[root@db02 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

# 重点
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，
属于热备方式。
3）备份时读取配置文件/etc/my.cnf

# 老版本使用Xtrabackup会锁表属于温备
# 新版本，针对innodb有单独的备份命令innobackupex

全量备份

[root@db02 ~]# mkdir /backup

# 全备命令
[root@db02 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full
--user：指定用户
--password：指定密码
--socket：指定socket文件位置
--apply-log：模拟CSR
--copy-back：恢复数据
--no-timestamp：备份时，自定义目录名，不使用时间戳

# binlog信息文件
xtrabackup_binlog_info

# 记录binlog文件名和binlog位置点
xtrabackup_binlog_pos_innodb

# 备份详细信息
xtrabackup_info

# redo log
xtrabackup_logfile

# 备份信息文件
xtrabackup_checkpoints
[root@db01 2022-08-18_20-45-39]# cat xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 5627000
last_lsn = 5627009
compact = 0
recover_binlog_info = 0

# 恢复全备数据
# 手动模拟 CSR，重做redo，回滚undo
[root@db02 mysql]# innobackupex --apply-log /backup/2022-08-18_14-54-23/

前提1：被恢复的目录是空的
前提2：被恢复的数据库实例是关闭的

# 关闭数据库
[root@db01 backup]# systemctl stop mysqld.service

# 保证被恢复的目录不存在（建议MV移走）
[root@db01 backup]# cd /var/lib/
[root@db01 backup]# mv mysql /tmp

# 恢复全备
[root@db01 backup]# innobackupex --copy-back /backup/2022-08-18_20-45-39/

# 修改权限
[root@db01 backup]# chown -R mysql.mysql mysql

增量备份

# 注意
1.基于上一次备份进行增量
2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备中

--incrementtal：开启增量备份
--incrementtal-basedir：指定基于上一次备份目录

# 全备
[root@db02 ~]# innobackupex --user=root --password=456 --no-timestamp /backup/full

[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第一次增备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full/ /backup/inc1

[root@db01 backup]# cat inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7304837
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第二次增备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc1/ /backup/inc2

[root@db01 backup]# cat inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7304837
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第三次增备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc2/ /backup/inc3

[root@db01 backup]# cat inc3/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7304837
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

恢复增备

1.full + inc1 + inc2 + inc3
2.inc1 合并到full中
3.inc2 合并到full中
4.inc3 合并到full中
5.分步骤进行--apply-log

--redo-only：只做redo
--incremental-dir：指定增量数据的目录（要把该目录合并到full中）

6.先把全备模拟CSR，只做redo 不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only /backup/full/

7.将inc1合并到全备，只做redo 不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full

8.将inc2合并到全备，只做redo 不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2 /backup/full

9.将inc3合并到全备，redo和undo都做
[root@db02 mysql]# innobackupex --apply-log --incremental-dir=/backup/inc3 /backup/full

10.将全备目录，整体做一次redo和undo
[root@db02 mysql]# innobackupex --apply-log /backup/full

11.恢复数据
[root@db01 lib]# systemctl stop mysqld.service 
[root@db01 lib]# mv /var/lib/mysql /backup/
[root@db01 lib]# innobackupex --copy-back /backup/full/
[root@db01 lib]# chown -R mysql.mysql mysql
[root@db01 lib]# systemctl start mysqld

差异备份

# 全备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp /backup/full1

[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 7304837
last_lsn = 7304846
compact = 0
recover_binlog_info = 0

# 第一次差异备
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full1 /backup/chayi1

[root@db01 backup]# cat chayi1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 7305806
to_lsn = 7305806
last_lsn = 7305815
compact = 0
recover_binlog_info = 0

# 第二次差异备份
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full1 /backup/chayi2

[root@db02 mysql]# cat /backup/chayi2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335492165
to_lsn = 2335501775
last_lsn = 2335501775
compact = 0
recover_binlog_info = 0

## 第三次差异备份
[root@db02 mysql]# innobackupex --user=root --password=456 --no-timestamp --
incremental --incremental-basedir /backup/full1 /backup/chayi3
[root@db02 mysql]# cat /backup/chayi3/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335492165
to_lsn = 2335505723
last_lsn = 2335505723
compact = 0
recover_binlog_info = 0

差异备份恢复

1.关闭数据库
[root@db02 mysql]# /etc/init.d/mysqld stop

2.清空data目录
[root@db02 backup]# mv mysql /tmp

3.先将全备只做redo不做undo
[root@db02 mysql]# innobackupex --apply-log --redo-only /backup/full1

4.合并最后一次差异备份，redo和undo都做
[root@db02 mysql]# innobackupex --apply-log --incremental-dir=/backup/chayi3 /backup/full1

5.将全备，redo和undo都做一次
[root@db02 mysql]# innobackupex --apply-log /backup/full1

6.恢复数据
[root@db02 mysql]# innobackupex --copy-back /backup/full1

7.授权
[root@db02 mysql]# chown -R mysql.mysql /var/lib/mysql

8.启动数据库
[root@db02 mysql]# systemctl start mysqld

模拟用户写入数据

root@db02 backup]# vim /root/mysql.sh
#!/bin/bash
num=1
while true;do
mysql -uroot -p123 -e "insert into zls.chayi value($num);commit;"
(( num++ ))
sleep 1
done

# 全备
[root@db02 backup]# innobackupex --user=root --password=456 --no-timestamp /backup/full2
[root@db02 backup]# cat /backup/full2/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2335532269
last_lsn = 2335535141
compact = 0
recover_binlog_info = 0

# 第一次增备
[root@db02 backup]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full2 /backup/inc1
[root@db02 backup]# cat /backup/zls_inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335532269
to_lsn = 2335575438
last_lsn = 2335575438
compact = 0
recover_binlog_info = 0

# 第二次增量
[root@db02 backup]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/zls_inc1 /backup/zls_inc2
[root@db02 backup]# cat /backup/zls_inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2335575438
to_lsn = 2335597445
last_lsn = 2335597445
compact = 0
recover_binlog_info = 0

恢复步骤

1.周六全备数据
[root@db01 backup]# innobackupex --user=root --password=456 --no-timestamp /backup/full
2.周一到周五进行增量备份
[root@db01 backup]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full /backup/inc1

[root@db01 lib]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc1 /backup/inc2

3.移走data目录
[root@db01 backup]# mv /var/lib/mysql /backup/

4.将全备只做redo不做undo
[root@db02 backup]# innobackupex --apply-log --redo-only /backup/full

5.将Inc1合并到full中，只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incrementaldir=/backup/inc1 /backup/full

6.将inc2合并到full中，redo和undo都做
[root@db02 backup]# innobackupex --apply-log --incremental-dir=/backup/zls_inc2 /backup/full

7.将full2整体，redo和undo都做
[root@db02 backup]# innobackupex --apply-log /backup/full

8.恢复数据
[root@db02 backup]# innobackupex --copy-back /backup/full

9.授权
[root@db01 backup]# chown -R mysql.mysql /var/lib/mysql

10.启动数据库
[root@db01 backup]# systemctl start mysqld

11.查询数据
mysql> show databases;

12.截取binlog
起始位置点：
[root@db02 full2]# cat /backup/full2/xtrabackup_binlog_pos_innodb
mysql-bin.000001 45318

结束位置点：
[root@db02 data]# mysqlbinlog -vvv --base64-output=decode-row mysql-bin.000001
54432

起始位置点：54524
结束位置点：61778

[root@db02 data]# mysqlbinlog --start-position=45318 --stop-position=54432 mysqlbin.000001 > /backup/binlog1.sql
[root@db02 data]# mysqlbinlog --start-position=54524 --stop-position=61778 mysqlbin.000001 > /backup/binlog2.sql

标签：--,root,备份,mysql,数据备份,MySQL,db02,backup
From： https://www.cnblogs.com/wangchengww/p/16600121.html

相关文章

  • Mysql使用实际02---SQL表连接
    1.表连接的常见类型在SQL语句中，关于表连接，若按照表的数量来划分，可以划分为单表连接、两表连接和两表以上连接。关于表连接有很多种类，主要分为交叉连接，内连接，外连接（左外......
  • Sqoop导入数据到mysql
    1.进入Sqoop安装目录执行bin/sqooplist-databases--connectjdbc:mysql://node1:3306/--usernameroot--passwordhadoop  #启动Sqoop连接mysql2.在虚拟机mysql中......
  • MYSQL练习题（45题）
    建表语句--学生表StudentcreatetableStudent(SIdvarchar(10),Snamevarchar(10),Sagedatetime,Ssexvarchar(10));insertintoStudentvalues('01','赵雷',......
  • MySQL多表查询与python操作MySQL
    一、navicateNavicate是一套可创建多个连接的数据库管理工具，用以方便管理 MySQL、Oracle、PostgreSQL、SQLite、SQLServer、MariaDB 和 MongoDB 等不同类型的数据库......
  • pt-heatbeat 监控 mysql 主从延迟
    pt-heartbeat监控MySQL主从延迟mysql在进行主从同步时，从库需要拉取主库的binlog进行sql回放，因此理论上只要主库存在数据更新，就一定存在主从延迟。mysql提供了Sec......
  • 【MySQL】什么是子查询
    目录1.谁的工资比Abel的高？2.子查询的分类1.谁的工资比Abel的高？方式一：先查询Abel的工资，再查比Abel工资高的员工SELECTsalaryFROMemployeesWHERElast_name='Abel'......
  • Navicat，pymysql，SQL注入问题
    Navicat可视化软件什么是Navicat？pyton操作MySQLSQL注入问题小知识点补充Navicat可视化软件什么是Navicat？1.Navicat是一个可多重连接的数据库管理工......
  • 【MySQL】SQL99版本新特性：自然连接、USING
    1.自然连接NATURALJOIN:它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。不够灵活。SELECTemployee_id,last_name,department_nameFROMemployeese......
  • kepserverEX 5.11通过ODBC连接mysql提示"Failed to automatically generate tags for
    kepserverEX5.11连接mysql，测试连接数据库正常，但是建立设备后提示错误: DateTimeLevelSourceEvent2022-08-1818:24:12ErrorODBCClient......
  • 【MySQL】多表查询7种SQL JOIN的实现
    目录两个表关联有这7种情况1.内连接2.左外连接3.右外连接4.A-A∩B5.B-A∩B6.满外连接7.去除交集部分两个表关联有这7种情况1.内连接SELECTemployee_id,departmen......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99