你不知道的MySQL备份和还原技巧,速来学习!

在这里插入图片描述

01、mysql备份数据库

1、mysql备份单个数据库

#mysql备份某个库格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --database 数据库名 > 文件名.sql


#实例:mysql备份某个库:
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --single-transaction --master-data=2 --set-gtid-purged=OFF --database -B db_mystorage > mysqldump_db_mystorage_`date +%Y%m%d-%H%M`.sql

2、mysql备份多个数据库

#mysql备份某个库格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --database 数据库名1 数据库名2 数据库名3 > 文件名.sql


#实例:mysql备份某个库:
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --single-transaction --master-data=2 --set-gtid-purged=OFF --database -B db_mystorage db_myblogs db_myOA > mysqldump_db_mystorage_db_myblogs_db_myOA_`date +%Y%m%d-%H%M`.sql

3、mysql备份所有的库

#mysql备份所有的库格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --all-databases > 文件名.sql


#实例:mysql备份所有的库:
mysqldump -h10.*.*.9 -P3306 -u用户名 -p"密码" --single-transaction --master-data=2 --set-gtid-purged=OFF --all-databases > mysqldump_all_databases_`date +%Y%m%d-%H%M`.sql

注意:如上命令 -A 或者 --all-databases 是备份该用户名拥有的全部数据库。

4、mysql备份特定表

#mysql备份某个库格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" 数据库名 表名 > 文件名.sql


#实例:备份db_plus_core库中的tb_doc_permission表
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --single-transaction --master-data=2 --set-gtid-purged=OFF db_plus_core tb_doc_permission > mysqldump_tb_doc_permission_`date +%Y%m%d-%H%M`.sql

5、mysql备份特定多个表

#mysql备份某个库格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" 数据库名 表名1 表名2 > 文件名.sql


#实例:备份db_plus_core库中的tb_doc_permission表
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --single-transaction --master-data=2 --set-gtid-purged=OFF db_plus_core tb_1 tb_2 > mysqldump_tb_1_tb_2_`date +%Y%m%d-%H%M`.sql

备份命令的 -B 参数

执行备份语句的时候,其中可以加上很多的参数,用来添加一些备份的时候的特殊要求的,其中有一个-B参数。执行备份语句时,如果加上了-B参数,那么将来再执行数据还原的时候,就不需要自己到数据库里面去先创建一个目标库了,并且执行数据还原语句的时候也不需要指定目标库了。如果没有加-B参数,就需要自行到数据库中先创建一个目标库,并且执行恢复语句时要指定将数据恢复到目标库。

原因是-B参数导出的文件中自带创建数据库和连接数据库的功能:(使用-B参数备份出来的内容自带create database 库名和use 库名的功能)。

备份命令的 | gzip 管道符

【压缩备份】:实际生产上导出的数据有可能比较大,不好备份到远程,这时候就需要进行压缩。

mysql压缩备份:

#mysql压缩备份格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --database 数据库名 | gzip > 文件名.sql.gz


#实例一:mysql压缩备份
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --single-transaction --master-data=2 --set-gtid-purged=OFF --database db_plus_core | gzip > mysqldump_db_plus_core_`date +%Y%m%d-%H%M`.sql.gz


#实例二:mysql压缩备份全部数据库:
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --single-transaction --master-data=2 --all-databases | gzip > mysqldump_alldatabases_`%Y%m%d-%H%M`.sql.gz


#实例三:如果mysql开了GTID模式就要用这个命令
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --single-transaction --master-data=2 --set-gtid-purged=OFF --all-databases | gzip > mysqldump_alldatabases_`%Y%m%d-%H%M`.sql.gz

mysql备份数据带出删除数据库或者表的sql备份

备份时,可以带出删除库的参数–add-drop-database,带出删除表的参数–add-drop-table

#格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --add-drop-table --add-drop-database 数据库名 > 文件名.sql
#示例:
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --add-drop-table --add-drop-database cmdb > /data/backup/mysqldump_cmdb_`%Y%m%d-%H%M`.sql

仅备份数据库结构,不包含数据

有时候我们仅仅只想备份出环境,数据库/表/字段结构,可以加命令参数–no-data

#格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --no-data 数据库名1 数据库名2 > 文件名.sql
#示例:
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --no-data db1 db2 > /data/backup/mysqldump_no_data_db1_db2_`%Y%m%d-%H%M`.sql

02、mysqldump恢复数据备份

#格式:
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --no-data 数据库名1 数据库名2 > 文件名.sql


#示例:还原db_plus_core库中的tb_doc_permission表的备份
mysqldump -uroot -p"密码" db_plus_core < mysqldump_20180114_tb_doc_permission.sql


#示例:还原所有的库(保底操作,按需执行)
mysqldump -uroot -p"密码" < mysqldump_20240114_all_databases.sql

通过mysql默认的终端执行恢复命令

【通过source命令恢复数据库】进入mysql数据库控制台,mysql -uroot -p3306登陆后mysql>use 数据库;然后使用source命令,后面参数为脚本文件(如这里用到的是.sql文件,如果你备份的是.txt文件,那这里写.txt文件)

mysql>source crm2.sql #这个文件是系统路径下的,默认是登陆mysql前的系统路径,在mysql中查看系统路径的方法是通过system+系统命令来搞的

mysql>system ls【利用mysql命名恢复(标准)】

如下:
mysql -root -p3306 -e “use crm2;drop table student;show tables;” 必须是双引号mysql -uroot -p3306 crm2<f:\数据库备份练习\crm2.sqlmysql -uroot -p3306 -e “use crm2;show tables;”

注:如果sql文件里面没有use db这样的字样时,在导入时就要指定数据库名了。

mysql -uroot -p3306 crm2<.sql文件

建议备份数据库时都指定上-B参数,效果好。说明:mysql不光可以恢复mysqldump的备份,只要文件中是sql语句,都可以通过mysql命令执行到数据库中

mysql 带 -e 参数实现非交互式对话,就是不需要到mysql里面去,在外面执行里面的指令的方法,例如:mysql -uroot -p3306 -e "use crm2;show tables;"但是语句必须是双引号包裹。

03、mysql备份表及部分数据

有时候我们只需要备份某个表里面的部分数据,快速恢复关键业务。【原理】:使用 like 命令快速复制一个表(结构一模一样),将要备份的部分数据查询出来备份到新表里面,使用完了以后再恢复回去。

#示例:
mysql -h10.*.*.9 -P3306 -uroot -p"密码"
use db_mystorage;
#step1:拷贝一份新的表结构
create table tb_staff_bak like tb_staff;
#step2:将要备份的部分数据查询出来备份到新表里面
insert into tb_staff_bk select * from tb_staff where name in ('xiong','zhang');
#step3:清理目标数据
delete from tb_staff where name in ('xiong','zhang');


#回滚与恢复:
insert into tb_staff select * from tb_staff_bk;


drop table tb_staff_bk;

实例:mysql自动备份脚本

备份方式:通过mysqldump的方式备份数据库,并保留7天备份数据。

#!/bin/bash
backupdir=/data/mysqlbak #备份文件存放路径
time=`date +%Y%m%d-%H%M` #备份文件名称时间标记


mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --all-databases --single-transaction --default-character-set=utf8 |gzip > $backupdir/mysql$time.sql.gz #备份所有数据库并打包


find $backupdir -name "mysql*.sql.gz" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&1 #删除7天之外的备份文件

创建备份目录

mkdir -pv /data/mysqlbak

给备份脚本授权

chmod +x /data/mysqlbak/mysqlbak.sh

添加计划任务

crontab -e
00 3 * * * /data/mysqlbak/bakmysql.sh

【恢复使用】

解压

gzip -d mysql2020703.sql.gz

非压缩备份文件恢复:

mysql -u用户名 -p < mysql2020703.sql

从压缩文件直接恢复:

gzip < mysql22020703.sql.gz | mysql -u 用户名 -p
关注 工 仲 好:IT运维大本营,获取60个G的《网工系统大礼包》

链接:https://www.cnblogs.com/xiongzaiqiren/p/17965612/mysqldump

(版权归原作者所有,侵删)
  • 28
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

GLAB-Mary

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值