mysql备份详解

于 2024-08-26 19:47:37 发布
阅读量306 收藏 6
点赞数 4
文章标签： mysql 数据库 linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43527128/article/details/141563118
版权

一、逻辑备份

1.mysqldump基础语法

[root@hw-yyx ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

2.常用场景

输入密码，然后导出指定数据库：

[root@master mysqlback]# mysqldump -u root -p testback > testback.sql
Enter password: 
[root@master mysqlback]# ls
dump_file.sql  testback.sql
[root@master mysqlback]# vim testback.sql

导出特定表:

[root@master mysqlback]# mysqldump -u root -p  testback --tables tab_testbak > test_bakTab.sql
Enter password: 
[root@master mysqlback]# ls
dump_file.sql  testback.sql  test_bakTab.sql
[root@master mysqlback]# vim test_bakTab.sql

导出多个表：

[root@master mysqlback]# mysqldump -u root -p testback table1 table2 > test_morebakTab.sql

导出所有表和结构：

[root@master mysqlback]# mysqldump -u root -p --all-databases > all_dbs.sql
Enter password: 
[root@master mysqlback]# ls
all_dbs.sql  dump_file.sql  testback.sql  test_bakTab.sql

导出指定数据，不含表结构：
–no-create-info

#--no-create-info：不包括创建表结构的 SQL 语句。
#--skip-add-locks：在导出数据时不添加锁表语句。
#--skip-disable-keys：在导出数据时不包括禁用外键检查的语句。
#--skip-lock-tables：在导出数据时不锁定表。
[root@master mysqlback]# mysqldump -u root -p --no-create-info --skip-add-locks --skip-disable-keys --skip-lock-tables testback tab_testbak > data_only.sql
Enter password: 
[root@master mysqlback]# ls
all_dbs.sql  data_only.sql  dump_file.sql  testback.sql  test_bakTab.sql
[root@master mysqlback]# vim data_only.sql

在这里插入图片描述
导出表结构不含数据：
–no-data

[root@master mysqlback]# mysqldump -u root -p --no-data testback tab_testbak > structure_only.sql
Enter password: 
[root@master mysqlback]# ls
all_dbs.sql  data_only.sql  dump_file.sql  structure_only.sql  testback.sql  test_bakTab.sql
[root@master mysqlback]# vim structure_only.sql

导出并压缩：

[root@master mysqlback]# mysqldump -u root -p --no-data testback tab_testbak | gzip > structure_only.sql.gz
Enter password: 
[root@master mysqlback]# ls
all_dbs.sql    dump_file.sql       structure_only.sql.gz  test_bakTab.sql
data_only.sql  structure_only.sql  testback.sql

恢复库

#先模拟删除库
#登录
[root@master mysqlback]# mysql -uroot -p
Enter password:
#删除
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testback           |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database testback;
Query OK, 2 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> \q
Bye

##开始恢复数据
[root@master mysqlback]# mysql -u root -p'123456' -e "CREATE DATABASE testback;" && mysql -u root -p testback < testback.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password: 
[root@master mysqlback]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testback           |
+--------------------+
5 rows in set (0.00 sec)

mysql>

恢复表

#模拟丢失表
[root@master mysqlback]# mysql -uroot -p  testback -e  "drop table  wh_student02;"
Enter password: 
# 方式一
#testback 数据库名  
#test_bakTab.sql表备份文件
[root@master mysqlback]# mysql -u root -p  testback  < test_bakTab.sql 
Enter password: 
# 方式二
#先登录
[root@master mysqlback]# mysql -uroot -p
#之前做了主从复制 这里关掉二进制日志 实际根据业务选择操作
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
# 进库
mysql> use testback;
Database changed
#恢复
mysql> source /mysqlback/test_bakTab.sql ;
# 查看
mysql> show tables;
+--------------------+
| Tables_in_testback |
+--------------------+
| tab_testbak        |
+--------------------+
1 row in set (0.00 sec)

恢复表结构：

[root@master mysqlback]# mysql -u root -p -D testback  < structure_only.sql
Enter password:

恢复数据

[root@master mysqlback]# mysql -u root -p -D testback < data_only.sql 
Enter password:

导出数据为自定义文档

参数 into outfile
# 配置导出数据安全目录
secure_file_priv=/mysqlback
# 授权
[root@master mysqlback]# chown mysql:mysql /mysqlback
# 完成后冲去mysql服务
[root@master mysqlback]# systemctl restart mysqld
# 导出数据
[root@master mysqlback]# mysql -u root -p  -D testback -e "select * from tab_testbak into outfile '/mysqlback/t6.bak';"
Enter password: 
# 导入数据
[root@master mysqlback]# mysql -u root -p -D testback -e "LOAD DATA INFILE '/mysqlback/t6.bak' INTO TABLE tab_testbak  CHARACTER SET utf8mb4;"
Enter password: 

# into outfile可加参数
fields terminated by ',' ：字段以逗号分割
lines terminated by '\n'：结尾换行

二、Mysql bin-log日志恢复数据

开启：

[root@master mysqlback]# vim /etc/my.cnf
log-bin=/var/log/mysql/mysql-bin
server-id=1

查看：

语法：
mysqlbinlog binlog_file > output_file.sql

例：
[root@master mysqlback]# mysqlbinlog /var/log/mysql/mysql-bin.000001  > mysql-bin000001.sql
[root@master mysqlback]# ls
all_dbs.sql    dump_file.sql        structure_only.sql     testback.sql     test_exclude.sql
data_only.sql  mysql-bin000001.sql  structure_only.sql.gz  test_bakTab.sql
[root@master mysqlback]# vim mysql-bin000001.sql

条件过滤

[root@master ~]# mysqlbinlog --start-datetime="2024-08-01 00:00:00" --stop-datetime="2024-09-02 23:59:59"  /var/log/mysql/mysql-bin.000001  > filtered_output.sql
[root@master ~]# ls
anaconda-ks.cfg  filtered_output.sql

常用参数
--help：
显示帮助信息。

--base64-output：
控制是否输出以 base64 编码的事件数据。

--start-position=#：
从指定的位置开始读取日志文件。

--stop-position=#：
在指定的位置停止读取日志文件。

--start-datetime="YYYY-MM-DD HH:MM:SS"：
从指定的日期和时间开始读取日志文件。

--stop-datetime="YYYY-MM-DD HH:MM:SS"：
在指定的日期和时间停止读取日志文件。

--start-transaction=#：
从指定的事务开始读取日志文件。

--stop-transaction=#：
在指定的事务停止读取日志文件。

--server-id=#：
只显示来自特定服务器 ID 的事件。

--short-form：
显示简短格式的日志信息。

--host=#：
指定远程服务器的地址。

--port=#：
指定远程服务器的端口号。

--user=#：
指定远程服务器的用户名。

--password：
提示输入远程服务器的密码。

--result-file=#：
将输出保存到指定的文件。

--force-read：
即使日志文件不完整也强制读取。

--force-if-open：
即使日志文件被其他进程打开也强制读取。

--to-last-log：
读取到最后一个日志文件。

--offset=#：
从指定的字节偏移量开始读取。

--debug-info：
显示调试信息。

--debug：
显示详细调试信息。

--verbose：
显示更详细的输出。

--raw：
以原始格式输出日志事件。

--show-charset：
显示字符集信息。

--show-warnings：
显示警告信息。

--event：
只显示指定类型的事件。

--regex：
使用正则表达式过滤事件。

--databases：
只显示指定数据库的事件。

--read-from-remote-server：
从远程服务器读取 binlog。

恢复

例： 根据时间过滤出可读数据sql文件 然后导入即可
[root@master mysqlback]# mysqlbinlog --start-datetime="2024-08-26 16:23:00" --stop-datetime="2024-08-26 16:59:59"  /var/log/mysql/mysql-bin.000005  > /mysqlback/filtered_output3.sql
[root@master mysqlback]# mysql -u root -p   testback  < /mysqlback/filtered_output3.sql  
Enter password: 

这里的恢复本质上是将binlog根据参数截取出丢失的数据，并转化为可读且mysql能直接导入的sql文件

三、物理备份

1.完全备份

# 创建备份目录
[root@master mysqlback]# mkdir /xtrabackup/full -p
# 完全备份数据库
[root@master mysqlback]# innobackupex --user=root --password='123456' /xtrabackup/full
[root@master mysqlback]# cd /xtrabackup/full/
[root@master full]# ls
2024-08-26_18-29-59
[root@master full]# cd 2024-08-26_18-29-59/
[root@master 2024-08-26_18-29-59]# ls
backup-my.cnf   ibdata1  performance_schema  testback                xtrabackup_checkpoints  xtrabackup_logfile
ib_buffer_pool  mysql    sys                 xtrabackup_binlog_info  xtrabackup_info




# 恢复数据
# 先模拟删除部分数据 或者表
# 然后关闭mysql服务 
[root@master 2024-08-26_18-29-59]# systemctl stop mysqld

# 清理环境删除或者移走mysql存放的数据目录下的数据。
# 查看目录在哪里
[root@master 2024-08-26_18-29-59]# cat /etc/my.cnf
datadir=/var/lib/mysql

[root@master 2024-08-26_18-29-59]# cd /var/lib/mysql
[root@master mysql]# ls
auto.cnf         client-key.pem  ib_logfile1              master-relay-bin.000003  mysql               relay-log.info   testback
ca-key.pem       ib_buffer_pool  master.info              master-relay-bin.000004  performance_schema  server-cert.pem
ca.pem           ibdata1         master-relay-bin.000001  master-relay-bin.000005  private_key.pem     server-key.pem
client-cert.pem  ib_logfile0     master-relay-bin.000002  master-relay-bin.index   public_key.pem      sys
[root@master mysql]# mkdir -p /tmp/test
[root@master mysql]# mv * /tmp/test  
[root@master mysql]# ls
[root@master mysql]# ll
总用量 0
[root@master mysql]#  rm -rf /var/log/mysqld.log			#可选操作
[root@master mysql]#  rm -rf /var/log/mysql-slow/slow.log  #可选操作


# 恢复
# 先验证
[root@master mysql]# innobackupex --apply-log /xtrabackup/full/2024-08-26_18-29-59
# 再执行
[root@master mysql]# innobackupex --copy-back /xtrabackup/full/2024-08-26_18-29-59/
# 修改数据目录权限
[root@master mysql]# chown mysql.mysql  /var/lib/mysql -R
# 重启并查看数据已恢复
[root@master mysql]# systemctl start mysqld

2.增量备份

基于完全备份后新增数据

# 创建新增备份文件目录
[root@master incremental]# mkdir /xtrabackup/incremental -p
# 基于全量备份进行第一次新增备份  第一次新增  这里新增数据的操作就省略掉了 
[root@master incremental]# innobackupex --user=root --password='123456' --incremental /xtrabackup/incremental --incremental-basedir=/xtrabackup/full/2024-08-26_18-29-59
[root@master incremental]# ls
2024-08-26_19-07-12 
# 基于第一次新增备份的第二次新增备份  同样省略了插入数据操作
[root@master incremental]# innobackupex --user=root --password='123456' --incremental /xtrabackup/incremental --incremental-basedir=/xtrabackup/incremental/2024-08-26_19-07-12
[root@master incremental]# ls
2024-08-26_19-07-12  2024-08-26_19-10-33
# 模拟数据修饰
# 省略

# 恢复数据
# 关闭mysql服务 
# 清理环境
# 上述操作同上面全量备份一致
# 恢复 先全量恢复 然后第一次 第二次  要一层一层迭代恢复
# 第一次全量 --redo-only  类似于预恢复文件  最后基于这个文件进行恢复
[root@master incremental]# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-26_18-29-59/
# 指向第一次增量备份文件
[root@master incremental]# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-26_18-29-59/ incremental-dir=/xtrabackup/incremental/2024-08-26_19-07-12
# 指向第二次增量备份文件
[root@master incremental]# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-26_18-29-59/   --incremental-dir=/xtrabackup/incremental/2024-08-26_19-10-33

# 通过--redo-only 指向的文件进行正式恢复
[root@master incremental]# innobackupex --copy-back /xtrabackup/full/2024-08-26_18-29-59/
[root@master incremental]# chown -R mysql.mysql /var/lib/mysql
[root@master incremental]# systemctl start mysqld
# 查看验证

3.差异备份

主要用到incremental-basedir这个参数  其他操作一致
语法如下：
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/  #备份目录基于周一的备份

差异备份：备份自上次完全备份之后的改变。所以，如果有2个连续差异备份，两个差异备份会有重复。恢复时，只需一个差异备份和最后一次完全备份。
增量备份：备份自上次（低一级）备份之后的改变。通常分级别1到9，0级即为完全备份。假设备份次序是0，2，3，1，5，则备份5会备份自3之后的改变。恢复时，通常需要多个增量备份和最后一次完全备份。差异备份可说是增量备份的一个特例，即级别1。

在这里插入图片描述

打败404
关注
  • 4
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL日志管理、备份与恢复
sukapulai的博客
05-29 350
目录 MySQL备份 数据备份的重要性 数据库备份的分类 常见的备份方法 MySQL完全备份 数据库完全备份分类 MySQL物理冷备份及恢复 mysqldump逻辑备份与恢复 MySQL完全恢复 MySQL增量备份 MySQL 增量备份与恢复 #二进制日志(binlog)有3种不同的记录格式: MySQL 增量恢复 一般恢复 基于位置恢复 基于时间点恢复 MySQL.的日志默认保存位置为/usr/local/mysql/data vim /etc/my.cnf [
mysql的常用命令备份表
08-30 4012
 1连接数据库：进入mysql的安装路径bin如：C:>cd C:Program FilesMySQLMySQL Server 5.0bin输入用户名密码： C:Program FilesMySQLMySQL Server 5.0bin>mysql -uroot -p1234562退出mysqlmysql>exit3. 修改密码:C:Program FilesMySQLMySQL Server 5
MySQL 备份与恢复
最新发布
2301_76414216的博客
07-29 432
备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。之前已经学习过如何安装 MySQL，本小节将从生产运维的角度了解备份恢复的分类与方法。与完全备份不同，增量备份没有重复数据，备份量不大，时间短;但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。MySQL 没有提供直接的增量备份办法，可以通过 MSQL 提供的二进制日志(binary logs)间接实现增量备份。
mysql文件备份
chinesesword的专栏
06-27 342
做成如下批处理文件.定时运行rem *******************************BACKUP Start*****************************@echo offC:chdir /d "D:\Program Files\MySQL\MySQL Server 5.5\bin"set filename=%date:~0,4%-%date:~5,2%-%date:~8...
mysql备份表
gxxielan的专栏
04-17 1213
1、用mysqldump命令备份：    mysql备份单个表，直接在终端输入： root@xxx:# mysqldump 数据库名 表名 > 文件路径 如： root@390:~# mysqldump mydatabase mytable > /root/mysql.sql 则mysql.sql中则有你创建表的语句还有表的数据； 2、用备份的表的数据覆盖另一个表，另一个表与备份表有
Mysql备份表
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
11-17 312
mysql整个数据完全备份。 可不可以把mysql的 data目录下的数据文件复制出来等于备份？ 备份方法，如下参考： 1.首先打开mysql数据库软件，进入软件主界面，如下图。 2.然后在左侧树中打开数据库，如下图。 3.然后需要指定要备份的数据库的名称，如下图。 4.打开数据库后的界面如图所示。 5.然后需...
MySQL备份原理详解
12-15
MySQL备份是确保数据安全的重要措施，它涉及到多种备份方法，每种都有其特定的适用场景和优缺点。本文主要探讨了MySQL的备份原理，包括冷备份、快照备份、逻辑备份，尤其是逻辑备份中的Mysqldump和Mydumper工具。 ...
Navicat异地自动备份MySQL方法详解(图文)
01-19
启动navicat软件，使用“链接” ，连接mysql数据库，使用前请保证防火墙可以通过。 Navicat For MySQL下载地址： 下载地址 //www.jb51.net/database/2223.html　　随后弹出，随意起一个“连接名”;输入“主机名/ip...
mysql常用的备份命令与备份策略
冰刀(skate)
02-10 4478
author:skate time:2012/02/10   mysql常用的备份命令 mysqldump备份常用方法： 　1.导出一个数据库     mysqldump -u用户名 -p密码 数据库名 > 导出的文件名     例如      # mysqldump -hhostname -uroot -ppwd dataname >dataname.sql    2.备份
Mysql 备份一张表
张腾飞的博客
03-23 3277
mysqldump -u用户名 数据库 表名称 > /home/temp/aaa.sql
mysql命令行备份及恢复数据库
06-03
mysql命令行备份及恢复数据库
mysql 表备份
Richy的博客
08-24 164
序 好记性不如烂笔头，几种数据库老混淆。还用错。。 mysql 表备份： CREATE table t_swcas_db_customer20200824 as SELECT * from t_swcas_db_customer
mysql 备份命令
weixin_30407099的博客
04-18 1158
mysqldump --socket=/home/work/mysql/var/mysql.sock -u用户名 -p密码 -P端口 -h10.28.4.64 feedback > feedback.sql https://www.cnblogs.com/nancyzhu/p/8511389.html 转载于:https://www.cnblogs.com/itniwota/p/1073...
mysql——数据库备份——使用mysqldump命令备份所有数据库
小白龙白龙马的博客
12-26 7616
mysqldump命令备份所有数据库； 语法格式：mysqldump -u username -ppassword --all-databases> backupname.sql --all-databases：这个选项表示后面跟一个或者多个数据库。注意前面不是一个横杆，是两个小横杆。后面是一个小横杠。 backupname.sql：表示路径和备份的文件名称； ...
MySQL备份
fancy106的博客
06-28 177
开启二进制日志 在 /etc/my.cnf 的 [mysqld] 配置域中添加如下配置内容 log-bin=/var/log/mysql/mysql-bin /var/log/mysql/mysql-bin 是二进制日志文件的前缀。 例如完整的日志可能是 /var/log/mysql/mysql-bin-000001 备份用户需要的完整权限 select reload # 允许使用该 FLUSH 语句，告诉服务器将授权表重新加载到内存中 lock tables show view even
mysql 用sql备份表
qq_15058425的博客
05-27 1601
现在有一张表a,需要将这张表备份： 1.新建一张表，复制表a的表结构： create table b like a; 2.将表a的数据复制到表b中：insert into b select  * from a  ; 完成
mysql备份表数据
dingjun1
03-19 496
MYSQL5 ： 使用下面的语句导出表里的记录，结果出错：Can't create\write to file * >mysql -u root -p >use jlx >select * into outfile 'c:\tbl_line.txt' from tbl_line; 结果是root用户没有权限创建文件,我想MYSQL\DATA目录下总有权限创建吧 改成：>sele...
mysql sql 备份表_SQL语句之备份表
热门推荐
weixin_29132813的博客
01-19 2万+
SELECT INTO 语句：表示从一个表中选取数据，然后把数据插入另一个表中，常用来备份一张表1.全表结构备份：SELECT * INTO new_table_nameFROM old_tablename;示例：备份student表，备份表取名为student_backupselect * intostudent_backupfrom student ;则会生成一张与student表结构及数据...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值