常用的MySQL备份/还原 的方法

admin 2024-06-16 226 阅读 0评论

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
  1. 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
  1. 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 是备份该用户名拥有的全部数据库。

  1. 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
  1. 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

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.sql
mysql -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;",但是语句必须是双引号包裹。
详情参考链接:https://www.cnblogs.com/yanghongtao/p/10897282.html



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

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

发表评论

快捷回复: 表情:
Addoil Applause Badlaugh Bomb Coffee Fabulous Facepalm Feces Frown Heyha Insidious KeepFighting NoProb PigHead Shocked Sinistersmile Slap Social Sweat Tolaugh Watermelon Witty Wow Yeah Yellowdog
提交
评论列表 (有 0 条评论, 226人围观)