mysql备份

最新推荐文章于 2024-03-25 21:42:46 发布
最新推荐文章于 2024-03-25 21:42:46 发布
阅读量252 收藏
点赞数
分类专栏： 数据库 常识 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/laosun12/article/details/120256114
版权

环境

mysql8

备份

1.mysqldump备份

#概念

 The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

#备份

##备份
#多库的备份
mysqldump --add-drop-table   --databases lanhu world > /var/lib/lanhuworld.sql
#单库的备份
mysqldump --add-drop-table -uroot -p123456 test > /var/lib/mysql/test.sql
#基于多表的备份
mysqldump --add-drop-table -uroot -p123456 lanhu sys_table sys_user > /var/lib/mysql/testtables.sql
#基于单表的备份
 mysqldump --add-drop-table -uroot -p123456 lanhu sys_user > /var/lib/mysql/sysuser.sql
#只复制表结构
 mysqldump --add-drop-table --no-data -uroot -p123456 lanhu sys_user > /var/lib/mysql/tableOnlyStruct.sql
#只复制表数据 --complete-insert带列名的insert语句
mysqldump    --no-create-info --complete-insert -uroot -p123456 lanhu sys_user > /var/lib/mysql/tableOnlyInsert.sql

##恢复
#多库的恢复
mysql -uroot -p123456 < /var/lib/mysql/lanhuworld.sql
#单库的恢复
mysql -uroot -p123456 < /var/lib/mysql/test.sql
#基于多表的恢复
mysql -uroot -p123456 < /var/lib/mysql/testtables.sql
#基于单表的恢复
mysql -uroot -p123456 < /var/lib/mysql/sysuser.sql
#表结构的恢复
mysql -uroot -p123456 < /var/lib/mysql/tableOnlyStruct.sql

## 参数说明
#--tab Produce tab-separated text-format data files. For each dumped table, mysqldump creates a tbl_name.sql file that contains the CREATE TABLE statement that creates the table, and the server writes a tbl_name.txt file that contains its data. The option value is the directory in which to write the files.
#类似于select into outfile;需要my.inf配置设置--secure-file-priv权限
 mysqldump  -uroot -P8023 -p123456 --databases=lanhu  --tab=D:/soft/mysql/backup 
#以上导入txt文件。use test \r\n load data infile ./sys_user.txt into table lanhu 和mysqlimport   lanhu  ./sys_user.txt  
#--routines 存储过程和函数 
#--events 事件
#--no-tablespaces 不存储表空间
#--insert-ignore 插入语句为 insert ingnore into

MySQL :: MySQL 8.0 Reference Manual :: 7.4 Using mysqldump for Backups

2.mysqlpump备份

#概念

The mysqlpump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.

#特性

mysqlpump features include:

  • Parallel processing of databases, and of objects within databases, to speed up the dump process

  • Better control over which databases and database objects (tables, stored programs, user accounts) to dump

  • Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database

  • Capability of creating compressed output

  • Progress indicator (the values are estimates)

  • For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes a

  • fter rows are inserted

    主要与dump区别1.并行，2压缩 

##备份
#多库的备份
mysqlpump  --add-drop-table lanhu test> ./lanhutest.sql
#带压缩的多库备份 LZ4 and ZLIB
mysqlpump   --add-drop-table --compress-output=ZLIB lanhu> ./lanhu.sql
#多线程备份
mysqlpump  -uroot -P8023 -p123456    --add-drop-table --parallel-schemas=4:lanhu lanhu >./lanhu.sql
#单库的备份
mysqlpump --add-drop-table -uroot -p123456 test > ./test.sql
#基于多表的备份
mysqlpump --add-drop-table -uroot -p123456 lanhu sys_table sys_user > ./testtables.sql
#基于单表的备份
 mysqlpump --add-drop-table -uroot -p123456 lanhu sys_user > ./sysuser.sql
#只复制数据
mysqlpump --add-drop-table --no-create-db --no-create-info -uroot -p123456 lanhu sys_user > ./sysuser.sql 

##恢复
#多库的恢复
mysql -uroot -p123456 < ./lanhutest.sql
#带压缩的多库恢复
#1.通过相关算法软件(mysql自带，eg:zlib_decompress input_file output_file)  解压。
#2.正常导入
mysql -uroot -p123456 < ./lanhutest_unpack.sql
#单库的恢复
mysql -uroot -p123456 <./test.sql
#基于多表的恢复
mysql -uroot -p123456 < ./testtables.sql
#基于单表的恢复
mysql -uroot -p123456 < ./sysuser.sql

##注意，如果导入提示存在 unknow commant "\"。则需要在导入时，强制使用编码--default_character-set=utf8mb4 即可导入。根据自己sql文件编码赋值。

#参数 --extended-insert=N 设置insert中values对应n个值

3.基于表空间迁移表（不受操作系统影响。idb从win迁移linux没问题。linux需要设置文件权限）

#有A,B两个库
#首先两个库备份的表结构要一致
CREATE TABLE `a` (
  `id` varchar(255) NOT NULL,
  `xm` varchar(255) DEFAULT NULL,
  `dsj` date DEFAULT NULL,
  `tsj` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
CREATE TABLE `b` (
  `id` varchar(255) NOT NULL,
  `xm` varchar(255) DEFAULT NULL,
  `dsj` date DEFAULT NULL,
  `tsj` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
#A库的a,b表插入数据

#A库导出。可以看到多了个cfg（架构验证的元数据，如果是加密表空间，必须连同idb文件一起复制到目的文件中）文件。
flush table a,b for export

#B库执行a表去掉表空间，发现a.idb文件已经没有了。
ALTER TABLE a DISCARD TABLESPACE
ALTER TABLE b DISCARD TABLESPACE

#将A的idb复制过来。导入，查询。
ALTER TABLE a import TABLESPACE
ALTER TABLE b import TABLESPACE

#将A库解锁
unlock tables;

4.基本mysqlshell备份

MySQL Shell is an advanced client and code editor for MySQL Server. In addition to the provided SQL functionality, similar to mysql, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. MySQL Shell is a component that you can install separately.

下载路径(从8.0.26使用的python3.9。win7不支持py3.9):MySQL :: Download MySQL Shell

#连接mysql \c：connect  
\c root@localhost:3306
#查看当前连接状态
\s
#进入到sql模式
\sql
#通过source命令恢复sql文件
\source path\backup.sql


#通过js模式导出表 \用于转义,该模式类似于select * from t into outfile。
#参考网址 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-table-export.html
#1.切换为js模式
\js
#2.导出hello表数据
util.exportTable("test2.hello", "D:\\soft\\mysql\\mysql8023\\soft\\bin\\hello.sql")
#3.以csv格式导出
util.exportTable("test2.hello", "D:\\soft\\mysql\\mysql8023\\soft\\bin\\hello.csv",{dialect:'csv'})
#3.1带压缩的导出[gzip ( .gz) 和zstd (.zst)格式]
util.exportTable("test.t5", "E:/mysql/backup/t5.gz",{dialect:'csv',compression:'gzip'})

#导入表
#mysql设置local_infile开启(load data local infile)
SET GLOBAL local_infile = 1;
#导入 (8.0.25版本一直未找到文件，8.0.26版本则没问题)。
util.importTable(["D:\\soft\\mysql\\mysql8023\\soft\\bin\\hello.csv"],{schema: "test2", table: "hello", dialect: "csv", showProgress: true})
#压缩文件的导入
util.importTable(["E:/mysql/backup/t5.gz"],{schema: "test", table: "t5", dialect: "csv", showProgress: true})

#引申select * from t into outfile '';
#需要在mysql配置文件设置 secure_file_priv= 参数。默认为null，则不能执行。不设置值则可以存任何路径。导出类csv格式文件。
select * from test into outfile 'D:/1.sql';
#加载
SET GLOBAL local_infile = 1;
load data  infile 'D:/1.sql' into table test;

5.xtrabackup linux环境，centos7安装

linux环境安装:

Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOSInstalling Percona XtraBackup on Red Hat Enterprise Linux and CentOShttps://www.percona.com/doc/percona-xtrabackup/8.0/installation/yum_repo.html

docker容器安装: Running Percona XtraBackup in a Docker containerRunning Percona XtraBackup in a Docker containerhttps://www.percona.com/doc/percona-xtrabackup/8.0/installation/docker.html

使用说明:

Connection and Privileges NeededConnection and Privileges Neededhttps://www.percona.com/doc/percona-xtrabackup/8.0/using_xtrabackup/privileges.html#操作

1)检测版本

xtrabackup -v

2）执行

#操作系统centos7.9
#mysql8docker容器安装
docker run --restart=always -p 33061:3306 --name mysql8 -v /home/soft/mysql/my.cnf:/etc/my.cnf -v /home/soft/mysql/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD='123456' -d mysql

#my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
datadir  = /var/lib/mysql
server-id = 1
log-bin = mysql-bin
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
lower_case_table_names = 1
open_files_limit=10240
max_connections=5000
max_allowed_packet=32M
thread_cache_size=300
innodb_buffer_pool_size=4096M
innodb_file_per_table=0
slow_query_log=ON

#xtrabackup安装(主要是datadir和log-bin,log-bin-index指向服务器具体路径)
xtrabackup   --defaults-file=/home/soft/mysql/xtrabackup.cnf   --host=192.168.1.110 --port=33060 --user=root --password=123456    --backup  --target-dir=/home/soft/xtrabackup
xtrabackup.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
datadir  = /home/soft/mysql/data
server-id = 1
log-bin =/home/soft/mysql/data/mysql-bin
log-bin-index=/home/soft/mysql/data/mysql-bin.index
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
lower_case_table_names = 1
open_files_limit=10240
max_connections=5000
max_allowed_packet=32M
thread_cache_size=300
innodb_buffer_pool_size=4096M
innodb_file_per_table=0
slow_query_log=ON


#全量备份
xtrabackup   --defaults-file=/home/soft/mysql/xtrabackup.cnf   --host=192.168.1.110 --port=33060 --user=root --password=123456    --backup  --target-dir=/home/soft/xtrabackup/base
#增加备份1
xtrabackup   --defaults-file=/home/soft/mysql/xtrabackup.cnf   --host=192.168.1.110 --port=33060 --user=root --password=123456    --backup  --target-dir=/home/soft/xtrabackup/inc1 --incremental-basedir=/home/soft/xtrabackup/base

#增加备份2
xtrabackup   --defaults-file=/home/soft/mysql/xtrabackup.cnf   --host=192.168.1.110 --port=33060 --user=root --password=123456    --backup  --target-dir=/home/soft/xtrabackup/inc2 --incremental-basedir=/home/soft/xtrabackup/inc1

#基于全量的恢复(即只做了一次备份)
#1.准备
xtrabackup   --prepare    --target-dir=/home/soft/xtrabackup/base
#2.停掉mysql
docker stop mysql8
#3.进入到data目录删除
cd /home/soft/mysql/data
rm -rf ./*
#4.文件恢复
xtrabackup --defaults-file=/home/soft/mysql/xtrabackup.cnf --copy-back --target-dir=/home/soft/xtrabackup/base
#5.启动mysql。验证
docker start mysql8

#基于增量的恢复
#1.首次全量准备
xtrabackup --prepare --apply-log-only --target-dir=/home/soft/xtrabackup/base
#2.增量1恢复--apply-log-only (加上该参数则将非提交的日志也恢复。针对非最后一次增量备份需要增加未提交事务日志，以后后个增量版本会使用)
xtrabackup  --prepare --apply-log-only --target-dir=/home/soft/xtrabackup/base  --incremental-dir=/home/soft/xtrabackup/inc1
#3.增量2恢复
xtrabackup --prepare --target-dir=/home/soft/xtrabackup/base  --incremental-dir=/home/soft/xtrabackup/inc2
#4.停掉mysql
docker stop mysql8
#5.进入到data目录删除
cd /home/soft/mysql/data
rm -rf ./*
#6.文件恢复
xtrabackup --defaults-file=/home/soft/mysql/xtrabackup.cnf --copy-back --target-dir=/home/soft/xtrabackup/base
#7.启动mysql。验证
docker start mysql8

总结

备份分类

按是否停服务划分:冷备、热备。

按是否迁data目录文件:逻辑备份、物理备份。

按数据跟数据的关系:全量备份、增量备份。

备份实现的方式:

逻辑备份:

mysqldump
mysqlpump
select  info outfile（导出文件）
util.exportTable(对outfile的分装)

物理备份:

表空间备份
xtrabackup -->backup

恢复的实现方式:

mysql
source
load data infile '' into table
mysqlimport

util.importTable
xtrabackup -->prepare

特点及使用场景:

mysqldump

  特点:导出sql脚本，或者导出文本格式的数据。导出、导入均为单线程。

  使用场景:少量数据，简单备份。

mysqlpump

   特点:可以并行或压缩sql脚本。导入单线程。

   使用场景:要求快速逻辑备份。

mysqlshell

   特点:除了正常的sql语句外，可以通过js或py模式进行数据的导出，导入。可以压缩。导入时支持多线程导入。

   使用场景:要求快速逻辑备份，并且能够快速导入的场景。

表空间迁移

   特点:针对少量表，表数据量大的数据迁移快，迁移时会锁表但是不停服务。

   使用场景:针对于大表文件物理备份。

xtrabackup

  特点:对文件进行物理备份。备份时不宕机，恢复快但是需要停服务(原因是文件的直接恢复)。

  使用场景:生产环境进行物理备份。

生产环境备份策略:

 1.逻辑和物理均做。

2.定期校验备份的数据。

3.物理备份，一周一全量，每天定时增量备份。

by南坡居士
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
nbu&mysql备份恢复.docx
04-24
NBU 备份mysql
MySQL备份
热门推荐
spring℡
05-11 1万+
一、直接拷贝数据库文件 首先关闭数据库 [root@host1 mysql]# pwd /var/lib/mysql 压缩、拷贝到host2 [root@host1 mysql]# tar czf db.tar.gz * [root@host1 mysql]# scp db.tar.gz 192.168.157.11:/beifen host2 首先关闭数据库，清理/var/lib/mysql/下的内容 [root@host2 beifen]# tar xf db.tar.gz /var/lib/my
MySQL数据库备份的三种方式
咏絮
08-09 1万+
MySQL数据库备份的三种方式 1、直接拷贝物理文件 2、在sqlyog这种可视化工具中手动导出 3、使用命令导出 mysqkdump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
MySQL 数据库的日志管理、备份与恢复
最新发布
qq_57093716的博客
03-25 1447
数据库的备份和恢复
MySQL数据库备份
Mapinyi666的博客
07-27 3110
数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方，以便在数据库丢失或损坏时进行恢复。数据库备份是数据库管理中必不可少的一项工作，通过备份可以保护数据库中的数据和业务。
MySql三种备份方式
weixin_43153747的博客
05-22 3673
对于xtrabackup来讲，它是基于事务日志和数据文件备份的，备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据库文件中的事务，还应该对其做预处理，把已提交的事务同步到数据文件，未提交的事务要回滚。增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。--lock-table 仅对单张表施加读锁，即使是备份整个数据库，它也是在我们备份某张表的时候才对该表施加读锁，因此适用于备份单张表。
Mysql 数据备份（详细教程）
Aprilqxs的博客
05-22 6974
备份的窗口期，通常是业务压力最低点。物理备份(cp、tar、zip)查看binlog日志内容。测试binlog日志。数据是企业生存的命脉。启用binlog日志。操作binlog日志。
mysql备份和恢复方案
06-14
mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份和恢复方案mysql备份...
Mysql备份文件.txt
05-13
mysql 数据库自动备份脚本，可以用Windows计划任务把文件改成.bat文件，定时执行
MySQL备份还原工具
10-10
/// 备份数据库 /// public void bakup_db(string manual = "") { try { //String command = "mysqldump --quick --host=localhost --default-character-set=gb2312 --lock-tables --verbose --force --port...
mysql备份还原
08-03
mysql备份还原资料，mymysql备份还原资料mysql备份还原资料，sql备份还原资料，mysql备份还原资料
MySQL备份脚本MySQL备份脚本
06-14
MySQL备份脚本
易语言mysql备份
08-15
易语言mysql备份源码 系统结构:备份数据库, ======窗口程序集_启动窗口 | | | |------ _备份按钮_被单击 | | | |------ 备份数据库
MYSQL 备份
06-25
mysql数据库的备份和恢复源代码，已经调试过了！
批处理mysql备份
01-31
批处理mysql，定期备份，定期删除旧的备份文件，防止备份文件乱码
mysql常用的三种备份方法
2301_76571514的博客
04-19 4225
支持InnoDB的物理热备份，支持完全备份，增量备份，而且速度非常快，而且支持InnoDB引擎的数据在不同数据库迁移 为了让xtrabackup支持更多的功能扩展，配置InnoDB每表一个文件的功能 在my.cnf的mysqld中加入此项： innodb_file_per_table=1 此项不启用将不支持备份单独的表 但如果之前没有启用这个选项，要实现单表一文件的话，可以用mysqldump导出数据，然后启用该选项，恢复回去后就是单表一文件了 首先下载xtrabackup。
MySQL几种方法的数据库备份
newnazi的专栏
10-25 1586
MySQL几种方法的数据库备份
MySQL_数据库备份
chenhenryjin的博客
04-09 4026
根据备份的方法（是否需要数据库离线）可以将备份分为： 热备（Hot Backup） 冷备（Cold Backup） 温备（Warm Backup）热备：在数据库运行状态下进行备份，备份时不需要停止数据库的服务。但是，由于备份时需要访问数据库文件，因此备份过程中可能会影响数据库的正常运行。这种方式在 MySQL 官方手册中称为 Online Backup （在线备份）。冷备：在关闭数据库的情况下进行备份。这种备份方式不影响数据库的正常运行，但是需要停止数据库的服务。
mysql 备份实战
08-31
备份 MySQL 数据库是非常重要的，以确保数据的安全性和完整性。以下是一个简单的 MySQL 备份实战步骤： 1. 使用命令行登录到 MySQL 服务器： ``` mysql -u your_username -p ``` 2. 输入密码以登录到 MySQL 服务器...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值