Mysql-MysqlDump

mysqldump备份还原详讲

一、前期环境查看和准备

1、数据库的字符集查看

查看数据库的字符集

mysql> show variables like '%character%';

查看库的字符集(以下范例为test库)

mysql> show create database test\G

修改库的字符集（以下范例为test库,一般不需要修改字符集）

mysql> alter database test default character set utf8;

2、创建备份的目录

mkdir /mysql-backup
chown mysql:mysql /mysql-backup/

二、单个库的备份还原

备份参考语句如下：

mydqldump -u 用户 –p’密码’ –default-character-set=Iatin1 数据库名 [|gzip] > 备份文件名（数据库默认字符集为Iatin1）注意：-B 作用：创建数据库和切换到数据库，恢复时不用创建数据库和删表。	备份多个库，-B 数据库1 数据库2 ...

1、备份数据库

mysqldump -uroot -p123456 --default-character-set=utf8 -B test > /mysql-backup/test.$(date +%F).sql

查看备份内容：

egrep -v "\*|--|^$"  /mysql-backup/test.$(date +%F).sql

2、恢复数据库测试

1）MySQL中用source命令

删除：mysql -uroot -p -e 'drop database test;'
查看：mysql -uroot -p -e 'show databases;' | grep test
恢复：mysql> source /mysql-backup/test.2020-07-13.sql;
查看： mysql -uroot -p -e 'select * from test.t1;'

2）mysql命令恢复

删除：mysql -uroot -p -e 'drop database test;'
查看：mysql -uroot -p -e 'show databases;' | grep test
恢复：mysql -uroot -p test < /mysql-backup/test.2020-07-13.sql 
查看： mysql -uroot -p -e 'select * from test.t1;'

注：备份指定的数据库的表，那么还原的时候要指向指定的数据库

三、单个表的备份还原

备份单张表的语句如下：

备份单个表：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名备份多个表：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名只备份数据库结构，不备份数据的数据：mysqldump -u 用户名 -p -d 数据库名 > 备份的文件名-d只备份库结构，不包含数据内容（备份数据库的表结构和数据库结构）

1、备份单个表

mysqldump -uroot -p test  t1> /mysql-backup/test.t1.$(date +%F).sql

查看备份内容：

egrep -v "\*|--|^$"  /mysql-backup/test.t1.$(date +%F).sql

2、恢复单个表

mysql -uroot -p -e ‘drop table test.t1;’
mysql -uroot -p -e ‘use school;show tables;’ | grep t1
mysql -uroot -p school < /mysql-backup/test.t1.2020-07-13.sql
mysql -uroot -p -e ‘select * from test.t1;’

四、增量备份

1、前提条件

1)是要开启MySQL log-bin日志功能，重启MySQL

mkdir /var/lib/mysql/mysql-bin
chown mysql:mysql /var/lib/mysql/mysql-bin
echo 'log-bin=/var/lib/mysql/mysql-bin' >> /etc/my.cnf 
echo 'server-id=1' >> /etc/my.cnf 

注：配置log-bin参数，一定要配置server-id。若是集群环境按照集群环境配置server-id。

2)存在一个完全备份

生产环境一般凌晨某个时刻进行全备

mkdir /mysql-backup
chown mysql:mysql /mysql-backup/
mysqldump -uroot -p --default-character-set=utf8 --single-transaction -F -B test |gzip > /mysql-backup/test_$(date +%F).sql.gz

注：InnoDB 表在备份时，通常启用选项 –single-transaction 来保证备份的一致性

2、备份脚本

用全备+binlog做基于时间点的恢复的时候，这个参数记录的数据给你提供了binlog恢复的起点。mysqldump 的这2个参数 –single-transaction –master-data

---

#!/bin/bash
#全备方式，一般在从机上执行，适用于小中型mysql数据库#删除15天以前备份
source /etc/profile        #加载系统环境变量
source ~/.bash_profile    #加载用户环境变量
set -o nounset             #引用未初始化变量时退出
#set -o errexit            #执行shell命令遇到错误时退出
user="root"
password="123456"
host="localhost"
port="3306"
#需备份的数据库，数组
db=("test")
#备份时加锁方式，
#MyISAM为锁表--lock-all-tables，
#InnoDB为锁行--single-transaction
lock="--single-transaction"
backup_path="/mysql-backup"
date=$(date +%Y-%m-%d_%H-%M-%S)
day=15
backup_log="${backup_path}/backup.log"
#建立备份目录
if [ ! -e $backup_path ];then    
    mkdir -p $backup_path
fi
#删除以前备份
find $backup_path -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1
echo "开始备份数据库：${db[*]}"
#备份并压缩
backup_sql(){    
    dbname=$1    
    backup_name="${dbname}_${date}.sql"    
    #-R备份存储过程，函数，触发器    
    mysqldump -h $host -P $port -u $user -p$password  $lock --master-data--default-character-set=utf8 --flush-logs -R -B $dbname > $backup_path/$backup_name
    if [[ $? == 0 ]];then
        cd $backup_path        
        tar zcpvf $backup_name.tar.gz $backup_name        
        size=$(du $backup_name.tar.gz -sh | awk '{print $1}')        
        rm -rf $backup_name        
        echo "$date 备份 $dbname($size) 成功 "    
    else        
        cd $backup_path        
        rm -rf $backup_name        
        echo "$date 备份 $dbname 失败 "    
    fi
}
#循环备份
length=${#db[@]{
for (( i = 0; i < $length; i++ )); do        
    backup_sql ${db[$i]} >> $backup_log 2>&1
done
echo "备份结束,结果查看 $backup_log"
du $backup_path/*$date* -sh | awk '{print "文件:" $2 ",大小:" $1}'

---

3、备份恢复测试（使用test库作为测试）

1）使用上述脚本备份test库

2）备份完成后继续给test.t1表插入数据

3）模拟用户破坏数据库

mysql> drop database test;

4）查看binlog日志

ls -lrt /var/lib/mysql/mysql-bin.*

5）立即刷新并备份出binlog，这个binlog是刷新之前最新的一个binlog日志

mysqladmin -uroot -p flush-logs
ls -lrt /var/lib/mysql/mysql-bin.*

cp mysql-bin.000010  /mysql-backup/

注：根据时间点及前一个binlog可以知道发现问题时刻前binlog日志为mysql-bin.000010

6）恢复binlog 生成sql语句

cd /mysql-backup
mysqlbinlog mysql-bin.000010 > bin.log

查看日志文件

egrep -v "^#|^$|\*" bin.log 

7）还原数据库

cd /backup
mysql -uroot -p < test_2020-07-13_10-29-02.sql

8）查看数据库的情况，是否在备份后插入的数据没有了。

9）使用binlog日志文件将数据库恢复到损坏的前一刻

注意：恢复增量是将binlog恢复的sql语句中drop database语句删除

mysql -uroot -p < bin.log

4、增量恢复基于位置或者时间点恢复

mysqlbinlog增量恢复方式

基于时间点恢复(可以从mysqlbinlog获取日志的时间点)

1）指定开始时间到结束时间

myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ --stop-datetime=’2014-10-45 03:10:46’-r time.sql

2）指定开始时间到文件结束

myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ -d esen -r time.sql

3）从文件开头到指定结束时间

myslbinlog mysqlbin.000008 --stop-datetime=’2014-10-45 03:10:46’ -d esen -r time.sql	

基于位置点的增量恢复(可以从mysqlbinlog获取日志的位置点)

1）指定开始位置到结束位置

myslbinlog mysqlbin.000008 --start-position=510 --stop-position=1312 -r pos.sql

2）指定开始位置到文件结束

myslbinlog mysqlbin.000008 --start-position=510 -r pos.sql

3）从文件开始位置到指定结束位置

myslbinlog mysqlbin.000008 --stop-position=1312 -r pos.sql

基于时间点实例：

恢复全备：

mysql -uroot -p < mysql_backup_2020-7-13.sql 
mysql -uroot -p -e 'select * from test.t1;'