mysql备份还原之mysqldump使用

mysqldump

基本语法

mysqldump [OPTIONS] database [tables]  #备份数据库里的某一个库或者某一个库中的某个表,一般不用这一种
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]  #备份数据库里的多个库,库与库之间用空格隔开
mysqldump [OPTIONS] –A [OPTIONS]  #备份数据库里的所有数据
#-B 等同于--databases  -A等同于--all-databases

mysqldump常见选项

-A, --all-databases 备份所有数据库,含create database
-B , --databases db_name… 指定备份的数据库,包括create database语句
-E, --events:备份相关的所有event scheduler
-R, --routines:备份所有存储过程和自定义函数
–triggers:备份表相关触发器,默认启用,用–skip-triggers,不备份触发器
–default-character-set=utf8 指定字符集 备份前查看数据库用的是哪种字符集,需要匹配
–master-data[=#]: 此选项须启用二进制日志 若数据库没有主从,用2即可
1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1
2:记录为注释的CHANGE MASTER TO语句此选项会自动关闭–lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启–single-transaction)

-F, --flush-logs :备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合 -A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和–single-transaction或-x,–master-data 一起使用实现,此时只刷新一次日志
–compact 去掉注释,适合调试,生产不使用
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,–no-create-db 不备份create database,可被-A或-B覆盖
–flush-privileges 备份mysql或相关时需要使用
-f, --force 忽略SQL错误,继续执行
–hex-blob 使用十六进制符号转储二进制列,当有包括BINARY,VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码
-q, --quick 不缓存查询,直接输出,加快备份速度

MyISAM备份选项

支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
锁定方法如下:
-x,–lock-all-tables:加全局读锁,锁定所有库的所有表,同时加–singletransaction或–lock-tables选项会关闭此选项功能
注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,–lock-tables:对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,–skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用

InnoDB备份选项

支持热备,可用温备但不建议用

 --single-transaction

此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,
DROP TABLE,RENAME TABLE,TRUNCATE TABLE
此选项和–lock-tables(此选项隐含提交挂起的事务)选项是相互排斥
备份大型表时,建议将–single-transaction选项和–quick结合一起使用

示例

直接备份hello库,若原数据库中这个库损坏了可以直接导入,环境和之前备份出来的一模一样

mysqldump -B hello >/data/hello.sql
mysql < /data/hello.sql

完整备份整个数据库,并对二进制日志位置进行标记,确认备份时的二进制日志记录点

mysqldump -A --master-data=2 > /data/all.sql

系统完全删库后如何恢复

首先关闭二进制日志

set sql_log_bin=off;

把备份时到故障前的二进制日志导出

mysqlbinlog --start-position=5261480 mysql-bin.000001 >/data/inc.sql
 mysqlbinlog  mysql-bin.000002 >> /data/inc.sql
 mysqlbinlog  mysql-bin.000003 >> /data/inc.sql
 mysqlbinlog  mysql-bin.000004 >> /data/inc.sql
 mysqlbinlog  mysql-bin.000005 >> /data/inc.sql

最后按次序,先执行上一次完全备份,在执行完全备份后产生的二进制日志,并重新打开二进制日志记录

source /data/all.sql;
source /data/inc.sql;
set sql_log_bin=on;

系统中其中一张表被删除,10分钟后发现,怎么恢复数据

首先刷新日志

flush logs;

把备份时到发现问题前的二进制日志导出

 mysqlbinlog --start-position=521816 mysql-bin.000005 >/data/inc.sql

把删表的操作注释或者删除掉

vim /data/inc.sql
#DROP TABLE `students` /* generated by server */

删库并重启数据库服务

rm -rf /var/lib/mysql/*
systemctl restart mariadb

先关闭二进制日志功能,然后导入完全备份的数据,以及剔除删表操作的二进制日志数据,再重新开启二进制日志功能即可

set sql_log_bin=0
source /data/all_2020-07-01.sql
source /data/inc.sql
set sql_log_bin=1
©️2020 CSDN 皮肤主题: 游动-白 设计师: 上身试试 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值