MySQL备份是指将MySQL数据库中的数据进行备份,以便在需要的时候能够恢复数据。备份是数据安全性和可靠性的保证,也是数据库管理的重要组成部分。

MySQL备份的方法主要有以下几种:

1、使用mysqldump命令进行备份:mysqldump是MySQL提供的备份工具,可以将数据库备份成SQL文件。使用该命令可以备份整个数据库、单个表或多个表。例如,备份test数据库的test_table表可以使用如下命令:

2、使用MySQL Workbench进行备份:MySQL Workbench是一个MySQL数据库图形化管理工具,可以通过该工具的备份功能对MySQL数据库进行备份。在MySQL Workbench的菜单中选择“Server”->“Data Export”->“Advanced Options”可以设置备份的方式和数据过滤等选项。

3、直接复制数据文件:MySQL数据存储在磁盘上的数据文件中,可以直接复制这些文件来进行备份。但是,这种备份方式有风险,因为MySQL在运行过程中可能有一些缓存数据没有及时写入磁盘,直接复制数据文件可能会导致数据丢失或不完整。

mysqldump说明

命令格式

mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份,但数据库本身定义不备份
mysqldump [OPTIONS] –B DB1 [DB2 DB3...] #支持指定数据库备份,包含数据库本身定义也会备份
mysqldump [OPTIONS] –A [OPTIONS] #备份所有数据库,包含数据库本身定义也会备份

mysqldump [options] [db_name [tbl_name ...]]

其中,options为可选项。如果没有指定特定数据库或表名,则默认备份所有数据库和表。

以下是常用的一些选项:

-u:MySQL用户名。
-p:MySQL用户密码。
-h:MySQL主机名。
--databases:备份多个数据库,中间用空格隔开。
--tables:备份多个表,中间用空格隔开。
-d:只备份表结构,而不包括数据。
-t:仅备份表,而不包括创建数据库。
-c:使用压缩备份文件。
-e:添加为SQL语句注释。
-r:指定备份文件的输出路径和文件名。
-A, --all-databases #备份所有数据库,含create database
-B, --databases db_name… #指定备份的数据库,包括create database语句
-E, --events:#备份相关的所有event scheduler
-R, --routines:#备份所有存储过程和自定义函数
--hex-blob #使用十六进制符号转储二进制列,当有包括BINARY, VARBINARY,
BLOB,BIT的数据类型的列时使用,避免乱码
-q, --quick #不缓存查询,直接输出,加快备份速度
--triggers:#备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]: #此选项须启用二进制日志
#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 一起使用实现,此时只刷新一次二进制日志
-x,--lock-all-tables #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--
skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用
--single-transaction
#此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储
文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP
TABLE,RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选
项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用
生产环境备份建议

InnoDB建议备份策略

mysqldump –uroot -p –A –F –E –R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyIsam

mysqldump –uroot -p –A –F –E –R –x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql
特定数据库备份

21、MySQL备份还原-mysqldump_mysqldump

21、MySQL备份还原-mysqldump_mysql备份恢复_02

脚本如下

#!/bin/bash
#备份mysql指定数据库
TIME=`date +%F_%H:%M:%S`
BKDIR="/data/backup/"
PASS="1234.com"
mysqldump -uroot -p"$PASS" -B $1 -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q > ${BKDIR}${1}_${TIME}.sql

21、MySQL备份还原-mysqldump_mysqldump_03

分库备份并压缩
for i in `mysql -uroot -p"1234.com" -e'show databases;'| grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -uroot -p"1234.com" -B $i | gzip >/data/backup/${i}.sql.gz; done
或者
mysql -uroot -p"1234.com" -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -uroot -p"1234.com" -B \1 | gzip > /data/backup/\1.sql.gz#p' |bash

21、MySQL备份还原-mysqldump_mysqldump_04

21、MySQL备份还原-mysqldump_mysqldump_05

脚本如下:

#!/bin/bash
#备份mysql指定数据库
TIME=`date +%F_%H:%M:%S`
BKDIR="/data/backup/"
PASS="1234.com"
[ -d "$DIR" ] || mkdir -p $BKDIR
for i in `mysql -uroot -p"$PASS" -e "show databases;"| grep -Ev "^Database|.*schema$"`;do mysqldump -uroot -p"$PASS" -B $i -F --single-transaction --master-data=2 -q | gzip > $BKDIR${i}_$TIME.sql.gz;done

21、MySQL备份还原-mysqldump_mysql备份恢复_06

21、MySQL备份还原-mysqldump_mysql备份恢复_07

数据库还原
#将备份文件拷贝到远程服务器
scp hellodb.sql 10.0.7.201:/data/backup
#登录远程服务器,临时关闭二进制文件
set sql_log_bin=0;
#执行source hellodb.sql进行还原

21、MySQL备份还原-mysqldump_mysqldump_08

21、MySQL备份还原-mysqldump_mysqldump_09

21、MySQL备份还原-mysqldump_mysqldump_10

二级制日志还原数据库
#二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin
#完全备份,并记录备份的二进制位置
mysqldump -uroot -p"1234.com"-B  -F --default-character-set=utf8 --single-transaction --master-data=2 | gzip > /data/backup/mysql/all_`date +%F`.sql.gz
#在hellodb中插入两条数据
insert into students (name,age,gender)value("lgw",18,"M"),("hr",18,"F");

21、MySQL备份还原-mysqldump_mysqldump_11

21、MySQL备份还原-mysqldump_mysql备份恢复_12

#执行数据库删除
drop database hellodb;

21、MySQL备份还原-mysqldump_mysql备份恢复_13

临时关闭bin日志
mysql> set sql_log_bin=0;

#使用备份恢复数据库
source /data/backup/mysql/hellodb.sql

21、MySQL备份还原-mysqldump_mysql备份恢复_14

#查找备份时,二进制日志的pos
cat /data/backup/mysql/hellodb.sql | grep "CHANGE"

21、MySQL备份还原-mysqldump_mysqldump_15

#二级制日志导出sql语句
mysqlbinlog --no-defaults mysql-bin.000005 --start-position=154 >>/data/backup/mysql/inc.sql
--no-defaults 解决mysqlbinlog命令执行出现[ERROR] unknown variable 'default-character-set=utf8mb4'报错

21、MySQL备份还原-mysqldump_mysql备份恢复_16

#执行二进制生成的sql脚本
 source /data/backup/mysql/inc.sql

查看结果数据库恢复

21、MySQL备份还原-mysqldump_mysql备份恢复_17

mysqldump 和二进制日志结合实现增量备份
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2
|gzip > /backup/all-`date +%F`.sql.gz
#将二进制日志拷贝到其他位置
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup
[root@centos8 ~]#mysqlbinlog --start-position=389 /backup/mariadb-bin.000003 >/backup/inc.sql
恢复误删除表
删除了表,又对其他数据表有更新数据

21、MySQL备份还原-mysqldump_mysql备份恢复_18

#使用上方的完全备份和二进制日志进行恢复,找到的inc.sql中删除drop tables,并注释掉
#使用sed命令删除drop行
sed -i.bak '/^DROP TABLE/d' inc.sql

21、MySQL备份还原-mysqldump_mysql备份恢复_19

21、MySQL备份还原-mysqldump_mysql备份恢复_20

#执行恢复
source /data/backup/mysql/inc.sql

21、MySQL备份还原-mysqldump_mysqldump_21

21、MySQL备份还原-mysqldump_mysql备份恢复_22