Mysql数据库完全备份与恢复

原创

sdfgsdf89 2023-02-23 13:12:10 ©著作权

文章标签 mysql sql 数据库 文章分类 运维

©著作权归作者所有：来自51CTO博客作者sdfgsdf89的原创作品，谢绝转载，否则将追究法律责任

一、数据备份的重要性

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果。造成数据丢失的原因如下:

程序错误
人为错误
运算错误
磁盘失败
灾难(如火灾、地震)和盗窃

二、数据库备份的分类

1、从物理与逻辑的角度，备份可以分为物理备份和逻辑备份。

(1)物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。物理备份又可分为脱机备份(冷备份)和联机备份(热备份)。

1>冷备份:是在关闭数据库的时候进行的

2>热备份:数据库处于运行状态，这种备份方法依赖于数据库的日志文件

3>温备份:数据库锁定表格(不可写入但可读)的状态下进行的

(2)逻辑备份:对数据库逻辑组件(如表等数据库对象)的备份

2、从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份

(1)完全备份:每次对数据进行完整的备份

对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

优点:备份与恢复操作简单方便
缺点:数据存在大量的重复;占用大量的空间;备份与恢复时间长

(2)差异备份:备份那些自从上次完全备份之后被修改过的所有文件备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

(3)增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

三、MySQL 完全备份操作

MySQL 数据库的备份可以采用多种方式

1、直接打包数据库文件夹如/usr/local/mysql/data/或/var/lib/mysql/

示例:

mysql> create database auth;

Query OK, 1 row affected (0.00 sec)

mysql> use auth;

Database changed
mysql> create table user(name char(10) not null,ID int(48));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values ('linuxyao','123');

Query OK, 1 row affected (0.00 sec)

mysql> select * from user;

+---------+------+
| name    | ID   |
+---------+------+
| linuxyao |  123 |
+---------+------+
1 row in set (0.00 sec)

[root@mysql-master ~]# /etc/init.d/mysqld stop

Shutting down MySQL. SUCCESS!

[root@mysql-master ~]# yum -y install xz

[root@mysql-master ~]# tar Jcf mysql_all-$(date +%F).tar.xz /usr/local/mysql/data/

tar: 从成员名中删除开头的“/”

[root@mysql-master ~]# mkdir bak

#模拟数据丢失
[root@mysql-master ~]# mv /usr/local/mysql/data/* bak/

#恢复数据

[root@mysql-master ~]# tar -xf mysql_all-2019-04-22.tar.xz

[root@mysql-master ~]# cd usr/local/mysql/data/

[root@mysql-master data]# mv /root/bak/* /usr/local/mysql/data/

[root@mysql-master data]# /etc/init.d/mysqld start

Starting MySQL.. SUCCESS!

[root@mysql-master data]# mysql -uroot -p123456

mysql> select * from auth.user;

+---------+------+
| name    | ID   |
+---------+------+
| linuxyao |  123 |
+---------+------+
1 row in set (0.01 sec)

2、使用专用备份工具

MySQL 自带的备份工具，相当方便对 MySQL 进行备份。通过该命令工具可以将制定的库、表或全部的库导出为 SQL 脚本，在需要恢复时可进行数据恢复。

Mysql目录：（1）源码安装

（2）YUM安装/var/lib/mysql

(1)对单个库进行完全备份

格式:

mysqldump -u 用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名

示例:

[root@mysql-master ~]# mkdir /backup

[root@mysql-master ~]# mysqldump -uroot -p123456 auth > /backup/auth-$(date +%F).sql

[root@mysql-master ~]# echo $?

0

[root@mysql-master ~]# cat /backup/auth-2019-04-22.sql

(2)对多个库进行完全备份

格式:

mysqldump -u 用户名 -p [密码] [选项] --databases 库名 1 [库名 2]… > /备份路径/ 备份文件名

示例:

[root@mysql-master ~]# mysqldump -uroot -p123456 --database mysql auth > /backup/mysql+auth-$(date +%F).sql

[root@mysql-master ~]# cat /backup/mysql+auth-2019-04-22.sql

(3)对所有库进行完全备份

格式:

mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名

示例:

[root@mysql-master ~]# mysqldump -uroot -p123456 --opt --all-databases > /backup/mysql_all.$(date +%F).sql     //--opt 加快备份速度，当备份数据量大时使用

[root@mysql-master ~]# cat /backup/mysql_all.2019-04-22.sql

(4)对表进行完全备份格式:

mysqldump -u 用户名 -p [密码] [选项] 数据库名表名 > /备份路径/备份文件名

示例:

[root@mysql-master ~]# mysqldump -uroot -p123456 auth user >/backup/auth_user-$(date +%F).sql

[root@mysql-master ~]# cat /backup/auth_user-2019-04-22.sql

(5)对表结构的备份

格式:

mysqldump -u 用户名 -p [密码] -d 数据库名表名 > /备份路径/备份文件名

示例:

[root@mysql-master ~]# mysqldump -uroot -p123456 -d mysql user >/backup/desc_mysql_user-$(date +%F).sql

[root@mysql-master ~]# cat /backup/desc_mysql_user-2019-04-22.sql

四、使用 mysqldump 备份后，恢复数据库

1、source 命令

登录到 MySQL 数据库

执行 source 备份 sql 脚本路径

示例:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| imployee_salary    |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database auth;

Query OK, 1 row affected (0.00 sec)

mysql> source /backup/mysql_all.2019-04-22.sql

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| imployee_salary    |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

2、mysql 命令

格式:

mysql -u 用户名 -p [密码] < 库备份脚本的路径 //恢复库

mysql -u 用户名 -p [密码] 库名 < 表备份脚本的路径 //恢复表

注意：mysql命令恢复表时，当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在

示例:

[root@mysql-master ~]# mysql -uroot -p123456 -e 'show databases;'

+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| imployee_salary    |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[root@mysql-master ~]# mysql -uroot -p123456 -e 'drop database auth;'

[root@mysql-master ~]# mysql -uroot -p123456 < /backup/mysql_all.2019-04-22.sql

[root@mysql-master ~]# mysql -uroot -p123456 -e 'show databases;'

+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| imployee_salary    |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[root@mysql-master ~]# mysql -uroot -p123456 -e 'drop table auth.user;'

[root@mysql-master ~]# mysql -uroot -p123456 auth </backup/auth_user-2019-04-22.sql

[root@mysql-master ~]# mysql -uroot -p123456 -e 'select * from auth.user;'

+---------+------+
| name    | ID   |
+---------+------+
| linuxli |  123 |
+---------+------+

五、MySQL 备份思路

1、定期实施备份，指定备份计划或策略，并严格遵守

2、除了进行完全备份，开启 MySQL 服务器的日志功能是很重要的(完全备份加上日志，可以对 MySQL 进行最大化还原)

3、使用统一和易理解的备份名称，推荐使用库名或者表名加上时间的命名规则，如 mysql_user-20160505.sql，不要使用 backup1 之类没有意义的名字。

六、MySQL 完全备份案例

需求描述:

北京移电通信公司的用户信息数据库为 client，用户资费数据表为 user_info，表结构如下所示。请为该公司指定合理的备份策略，依据所指定的策略备份数据，模拟数据丢失进行数据恢复。

身份证	姓名	性别	用户ID号	资费
000000001	孙空武	男	011	100
000000002	蓝凌	女	012	98
000000003	姜纹	女	013	12
000000004	关园	男	015	38
000000005	罗中昆	男	015	39

创建数据及表，录入数据:

[root@mysql-master ~]# mysql -uroot -p123456

mysql> create database client;

Query OK, 1 row affected (0.00 sec)

mysql> use client;

Database changed

mysql> show variables like 'character_set_%';

+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

mysql> create table user_info(身份证 int(20),姓名 char(20),性别 char(2),用户ID号 int(110),资费 int(10));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into user_info values('000000001','孙空武','男','011','100');

Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values('000000002','蓝凌','女','012','98');

Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values('000000003','姜纹','女','013', '12');

Query OK, 1 row affected (0.00 sec)

mysql>  insert into user_info values('000000004','关园','男','014','38');

Query OK, 1 row affected (0.01 sec)

mysql>  insert into user_info values('000000004','罗中昆','男','015','39');

Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;

+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 性别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
|         1 | 孙空武    | 男     |          11 |    100 |
|         2 | 蓝凌      | 女     |          12 |     98 |
|         3 | 姜纹      | 女     |          13 |     12 |
|         4 | 关园      | 男     |          14 |     38 |
|         4 | 罗中昆    | 男     |          15 |     39 |
+-----------+-----------+--------+-------------+--------+
5 rows in set (0.01 sec)

完整备份 client.user_info 表:

[root@mysql-master ~]# mysqldump -uroot -p123456 client user_info >/backup/client.user_info-$(date +%F).sql

模拟数据丢失恢复数据:

[root@mysql-master ~]# mysql -uroot -p123456 -e 'drop table client.user_info;'

[root@mysql-master ~]# mysql -uroot -p123456 -e 'use client;show tables;'

[root@mysql-master ~]# mysql -uroot -p123456 client </backup/client.user_info-2019-04-22.sql

[root@mysql-master ~]# mysql -uroot -p123456 -e 'select * from client.user_info;'

+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 性别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
|         1 | 孙空武    | 男     |          11 |    100 |
|         2 | 蓝凌      | 女     |          12 |     98 |
|         3 | 姜纹      | 女     |          13 |     12 |
|         4 | 关园      | 男     |          14 |     38 |
|         4 | 罗中昆    | 男     |          15 |     39 |
+-----------+-----------+--------+-------------+--------+

定期备份数据:

[root@mysql-master ~]# which mysqldump

/usr/local/mysql/bin/mysqldump

[root@mysql-master ~]# vim /opt/bak_client.sh

#!/bin/bash
# 备份client.user_info表脚本

/usr/local/mysql/bin/mysqldump -uroot -p123456 client user_info >/backup/client.user_info-$(date +%F).sql

[root@mysql-master ~]# chmod +x /opt/bak_client.sh

[root@mysql-master ~]# crontab -e

0 0 * * * /opt/bak_client.sh

[root@mysql-master ~]# systemctl enable crond

[root@mysql-master ~]# systemctl start crond

七、MySQL 数据库备份脚本

实验环境:

mysql-server:192.168.1.108

mysql-client:192.168.1.48

实验要求:对 mysql-server 的 auth 库和 client 库，实现异地备份，每天凌晨 2:00 进行备份，撰写一个数据恢复脚本。

MySQL 服务端授权，给予 select 和 lock tables（锁表）权限，以备份

注意：使用msqldump备份时，数据库必须锁表


[root@mysql ~]# mysql -uroot -p123123

mysql> grant select,lock tables on auth.* to 'admin'@'192.168.1.48' identified by '123123';    

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,lock tables on client.* to 'admin'@'192.168.1.48' identified by '123123'; 

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

客户端安装客户端软件

[root@client ~]# rpm -qa |grep mysql

mysql-libs-5.1.71-1.el6.x86_64

[root@client ~]# yum -y install mysql

连接测试

[root@client ~]# mysql -uadmin -p123123 -h192.168.1.108 

mysql> show databases;

+--------------------+
| Database       |
+--------------------+
| information_schema |
| auth         | 
| client         |
| test         |
+--------------------+

撰写客户端备份脚本

[root@client ~]# vim /opt/bakmysql.sh

#!/bin/bash
# MySQL 数据库备份脚本
# 设置登录变量
MY_USER="admin"
MY_PASS="123123"
MY_HOST="192.168.1.108"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
# 设置备份的数据库
MY_DB1="auth"
MY_DB2="client"
# 定义备份路径、工具、时间、文件名
BF_DIR="/backup"
BF_CMD="/usr/bin/mysqldump"
BF_TIME=$(date +%Y%m%d-%H%M) 
NAME_1="$MY_DB1-$BF_TIME" 
NAME_2="$MY_DB2-$BF_TIME"
# 备份为.sql 脚本，然后打包压缩(打包后删除原文件)
[ -d $BF_DIR ] || mkdir -p $BF_DIR
cd $BF_DIR
$BF_CMD $MY_CONN --databases $MY_DB1 > $NAME_1.sql 
$BF_CMD $MY_CONN --databases $MY_DB2 > $NAME_2.sql 
# tar –-remove 选项:备份压缩完文件之后删除源文件
/bin/tar zcf $NAME_1.tar.gz $NAME_1.sql --remove &>/dev/null
/bin/tar zcf $NAME_2.tar.gz $NAME_2.sql --remove &>/dev/null

[root@client ~]# chmod +x /opt/bakmysql.sh

[root@client ~]# /opt/bakmysql.sh

[root@client ~]# ls /backup/

auth-20160505-1805.tar.gz client-20160505-1805.tar.gz

[root@client ~]# tar tvf /backup/auth-20160505-1805.tar.gz

-rw-r--r-- root/root 1967 2016-05-05 18:05 auth-20160505-1805.sql 

[root@client ~]# tar tvf /backup/client-20160505-1805.tar.gz

-rw-r--r-- root/root 2250 2016-05-05 18:05 client-20160505-1805.sql

[root@client ~]# crontab -e

0 2 * * * /opt/bakmysql.sh

改变系统时间，执行任务计划，模拟每天的备份，为之后的恢复脚本做准备


[root@client ~]# date 050601592016.59 //MMDDhhmmYY.SS 月日小时分钟.秒

恢复系统时间（同步网络时间）：安装 ntp 工具 ，然后执行 ntpdate ntp.aliyun.com 即可

[root@client ~]# ls /backup/        

auth-20160505-1805.tar.gz client-20160505-1805.tar.gz

auth-20160506-0200.tar.gz client-20160506-0200.tar.gz 
若没有备份文件，需重启crond 服务

[root@client ~]# date 050701592016.59

2016 年 05 月 07 日 星期六 01:59:59 CST

[root@client ~]# date 050801592016.59

2016 年 05 月 08 日 星期日 01:59:59 CST

[root@client ~]# ls /backup/

auth-20160505-1805.tar.gz auth-20160508-0200.tar.gz client-20160507-0200.tar.gz auth-20160506-0200.tar.gz client-20160505-1805.tar.gz client-20160508-0200.tar.gz auth-20160507-0200.tar.gz client-20160506-0200.tar.gz

撰写数据恢复脚本

[root@client ~]# vim /opt/restore_mysql.sh

#!/bin/bash
# 恢复 MySQL 数据库数据脚本
# 设置变量

MY_USER="admin"

MY_PASS="123123"

MY_HOST="192.168.1.108"

BF_DIR="/backup"

mkdir .aaa
# colimn -t :
ls $BF_DIR |column -t > .aaa/db_list

awk -F'-' '{print $2}' .aaa/db_list > .aaa/dt.txt

read -p "请指定要恢复数据库的日期(YYYYMMDD):" dt 

if [ $dt -ge 20160501 ] && [ $dt -le 20160601 ];then

  grep "$dt" .aaa/dt.txt &>/dev/null 

if [ $? -ne 0 ];then

  echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内" 

else
  echo "搜索到的可恢复数据库如下:"
  awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list
  read -p "请选择您要恢复数据库的编号: " nb
  nm=$(awk -F'-' /$dt/'{print NR,$1}' .aaa/db_list |awk /$nb/'{print $2}')  
  echo "现在开始恢复数据库:$nm 到$dt"
  cd $BF_DIR
  onm=$(ls |grep "$nm-$dt")
  mkdir .bbb
  tar xf $onm -C .bbb
  mysql -u$MY_USER -p$MY_PASS -h$MY_HOST $nm < .bbb/*
  echo "$nm 已经恢复到$dt"
    rm -rf .bbb 
  cd - &>/dev/null
  rm -rf .aaa
fi 

else

echo "很抱歉,您恢复数据库的备份日期不再备份日期范围内" 

fi

[root@client ~]# chmod +x /opt/restore_mysql.sh

如在客户端恢复数据，需要开放权限

mysql> grant all on auth.* to 'admin'@'192.168.1.48';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on client.* to 'admin'@'192.168.1.48';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

恢复测试:

[root@client ~]# /opt/restore_mysql.sh

请指定要恢复数据库的日期(YYYYMMDD):20160507 搜索到的可恢复数据库如下:
3 auth
7 client
请选择您要恢复数据库的编号: 3 
现在开始恢复数据库:auth 到 20160507 
auth 已经恢复到 20160507

[root@client ~]# /opt/restore_mysql.sh

请指定要恢复数据库的日期(YYYYMMDD):20100101 
很抱歉,您恢复数据库的备份日期不再备份日期范围内

上一篇：Mysql数据库操作

下一篇：Mysql数据库增量备份与恢复

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯