21、MySQL备份还原-mysqldump

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

特定数据库备份

脚本如下

#!/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

分库备份并压缩

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

脚本如下：

#!/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

数据库还原

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

二级制日志还原数据库

#二进制日志独立存放
[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");

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

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

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

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

#二级制日志导出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'报错

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

查看结果数据库恢复

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

恢复误删除表

删除了表，又对其他数据表有更新数据

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

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