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

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

mysql备份数据库

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

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

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

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

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天之外的备份文件