MySQL 完整和增量备份与恢复

MySQL 完全备份与恢复

1、数据备份的重要性

在企业中数据的价值至关重要，数据保障了企业的业务的运行，因此数据的安全性及
可靠性是运维的重中之重，任何数据的丢失都有可能会对企业产生严重的后果。造成数据
丢失的原因如下：
☆ 程序错误
☆ 人为错误
☆ 运算失败
☆ 磁盘故障
☆ 灾难（如火灾、地震）和盗窃

2、数据库备份的分类

从物理与逻辑的角度

备份可以分为物理备份和逻辑备份。
物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理
备份又可分为脱机备份（冷备份）和联机备份（热备份）。
1. 冷备份：是在关闭数据库的时候进行的
2. 热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件
3. 温备份：数据库锁定表格（不可写入但可读）的状态下进行的
逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份

从数据库的备份策略角度

备份可分为完全备份、差异备份和增量备份
完全备份：每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件
结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。
优点：备份与恢复操作简单方便
缺点：数据存在大量的重复；占用大量的空间；备份与恢复时间长
差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是
从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最
近的一次差异备份。

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

3、MySQL 完全备份操作

MySQL 数据库的备份可以采用多种方式
1、直接打包数据库文件夹，如/usr/local/mysql/data/ 或 /var/lib/mysql/
示例：

[root@localhost ~]# 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.04 sec)
mysql> insert into user values('crushlinux','123');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+------------+------+
| name | ID |
+------------+------+
| crushlinux | 123 |
+------------+------+
1 row in set (0.00 sec)
mysql> exit
Bye

[root@localhost ~]# service mysqld stop
Shutting down MySQL.. [ OK ]
[root@localhost ~]# yum -y install xz
[root@localhost ~]# tar Jcf mysql_all-$(date +%F).tar.xz /usr/local/mysql/data/
tar: Removing leading `/' from member names

模拟数据丢失！

[root@localhost ~]# mkdir bak
[root@localhost ~]# mv /usr/local/mysql/data/* bak/

恢复数据：

[root@localhost ~]# mkdir restore
[root@localhost ~]# tar xf mysql_all-2016-12-08.tar.xz -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/* /usr/local/mysql/data/
[root@localhost ~]# service mysqld start
Starting MySQL.. [ OK ]
[root@localhost ~]# mysql
mysql> select * from auth.user;
+------------+------+
| name | ID |
+------------+------+
| crushlinux | 123 |
+------------+------+
1 row in set (0.00 sec)

2、使用专用备份工具 mysqldump
MySQL 自带的备份工具，相当方便对 MySQL 进行备份。通过该命令工具可以将数据库、
数据表或全部的库导出为 SQL 脚本，在需要恢复时可进行数据恢复。
（1）对单个库进行完全备份
格式：mysqldump -u 用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名
示例：

[root@localhost ~]# mkdir /backup
[root@localhost ~]# mysqldump -uroot -p123123 auth > /backup/auth-$(date +%Y%m%d).sql
[root@localhost ~]# echo $?
0
[root@localhost ~]# cat /backup/auth-20161208.sql

（2）对多个库进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] --databases 库名 1 [库名 2]… > /备份路径/备份
文件名
示例：

[root@localhost ~]# mysqldump -uroot -p123123 --databases mysql auth >
/backup/mysql+auth-$(date +%Y%m%d).sql
[root@localhost ~]# cat /backup/mysql+auth-20161208.sql

（3）对所有库进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
示例：

[root@localhost ~]# mysqldump -uroot -p123123 --opt --all-
databases >/backup/mysql_all.$(date +%Y%m%d).sql

[root@localhost ~]# cat /backup/mysql_all.20161208.sql
//--opt 加快备份速度，当备份数据量大时使用
[root@localhost ~]# cat /backup/mysql_all.20160505.sql

（4）对表进行完全备份
格式：mysqldump -u 用户名 -p [密码] [选项] 数据库名表名 > /备份路径/备份文件名
示例：

[root@localhost ~]# mysqldump -uroot -p123123 auth user >/backup/auth_user-$(date
+%Y%m%d).sql
[root@localhost ~]# cat /backup/auth_user-20161208.sql

（5）对表结构的备份
格式：mysqldump -u 用户名 -p [密码] -d 数据库名表名 > /备份路径/备份文件名
示例：

[root@localhost ~]# mysqldump -uroot -p123123 -d mysql user >/backup/desc_mysql_user-
$(date +%Y%m%d).sql
[root@localhost ~]# cat /backup/desc_mysql_user-20161208.sql

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

1、source 命令
登录到 MySQL 数据库，执行 source 备份 sql 脚本路径
示例

[root@localhost ~]# mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| mysql |
| performance_schema |
| test |
| usr |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database auth;
Query OK, 1 row affected (0.12 sec)
mysql> source /backup/mysql_all.20161208.sql
mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| mysql |
| performance_schema |
| test |
| usr |
+--------------------+
6 rows in set (0.00 sec)

2、mysql 命令
格式：mysql -u 用户名 -p [密码] < 库备份脚本的路径
mysql -u 用户名 -p [密码] 库名 < 表备份脚本的路径
示例：

[root@localhost ~]# mysql -uroot -p123123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| mysql |
| performance_schema |
| test |
| usr |
+--------------------+
[root@localhost ~]# mysql -uroot -p123123 -e 'drop database auth;'
[root@localhost ~]# mysql -uroot -p123123 < /backup/mysql_all.20161208.sql
[root@localhost ~]# mysql -uroot -p123123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| mysql |
| performance_schema |
| test |
| usr |
+--------------------+

[root@localhost ~]# mysql -uroot -p123123 -e 'drop table auth.user;'
[root@localhost ~]# mysql -uroot -p123123 auth< /backup/auth_user-20161208.sql
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from auth.user;'

+------------+------+
| name | ID |
+------------+------+
| crushlinux | 123 |
+------------+------+

　5、MySQL 备份思路

1、定期实施备份，指定备份计划或策略，并严格遵守
2、除了进行完全备份，开启 MySQL 服务器的 binlog 日志功能是很重要的（完全备份加上日
志，可以对 MySQL 进行最大化还原）
3、使用统一和易理解的备份名称，推荐使用库名或者表名加上时间的命名规则，如
mysql_user-20161208.sql，不要使用 backup1 或者 abc 之类没有意义的名字。

6、MySQL 完全备份案例

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

MySQL 完整和增量备份与恢复

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

[root@localhost ~]# mysql -uroot -p123123
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 |
9 / 22
| 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.01 sec)
mysql> insert into user_info values('000000001',' 孙空武',' 男','011','100');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_info values('000000002',' 蓝凌',' 女','012','98');
Query OK, 1 row affected (0.01 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.01 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.00 sec)

完整备份 client.user_info 表：　　

[root@localhost ~]# mysqldump -uroot -p123123 client user_info > /backup/client.user_info-
$(date +%Y%m%d).sql

模拟数据丢失恢复数据：　　

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

[root@localhost ~]# mysql -uroot -p123123 -e 'use client; show tables;'
[root@localhost ~]# mysql -uroot -p123123 client < /backup/client.user_info-20161208.sql
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户 ID 号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 1 | 孙空武 | 男 | 11 | 100 |
| 2 | 蓝凌 | 女 | 12 | 98 |
| 3 | 姜纹 | 女 | 13 | 12 |
| 4 | 关园 | 男 | 14 | 38 |
| 4 | 罗中昆 | 男 | 15 | 39 |
+-----------+-----------+--------+-------------+--------+

定期备份数据：

[root@localhost ~]# which mysqldump
/usr/local/mysql/bin/mysqldump
[root@localhost ~]# vim /opt/bak_client.sh
#!/bin/bash
#  备份 client.user_info  表  脚本
/usr/local/mysql/bin/mysqldump -uroot -p123123 client user_info >/backup/client.user_info-
$(date +%Y%m%d).sql
[root@localhost ~]# chmod +x /opt/bak_client.sh
[root@localhost ~]# crontab -e
0 0 * * * /opt/bak_client.sh // 每天 0:00  备份

7、MySQL 数据库备份脚本

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

MySQL 服务端授权，给予 select 和和 lock tables

[root@localhost ~]# mysql -uroot -p123123
mysql> grant select,lock tables on auth.* to 'admin'@'192.168.200.102' identified by '123123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,lock tables on client.* to 'admin'@'192.168.200.102' 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 -p -h192.168.200.101
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| client |
| test |
+--------------------+
4 rows in set (0.00 sec)

撰写客户端备份脚本　　

[root@client ~]# vim /opt/bakmysql.sh
#!/bin/bash
# MySQL 数据库备份脚本
# 设置登录变量
MY_USER="admin"
MY_PASS="123123"
MY_HOST="192.168.200.101"
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

/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 月日小时分钟. 秒
2016 年 05 月 06 日 星期五 01:59:59 CST
[root@client ~]# ls /backup/
auth-20160505-1805.tar.gz client-20160505-1805.tar.gz
auth-20160506-0200.tar.gz client-20160506-0200.tar.gz
[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.200.101"
BF_DIR="/backup"
mkdir .aaa
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.200.102';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on client.* to 'admin'@'192.168.200.102';
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 增量备份与恢复

1、MySQL 增量备份概念

使用 mysqldump 进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。
而增量备份就是备份自上一次备份之后增加或改变的文件或内容。

增量备份的特点：

　　没有重复数据，备份量不大，时间短
　　恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有
增量备份进行逐个反推恢复。
　　MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志（binary logs）
间接实现增量备份。

MySQL 二进制日志对备份的意义：　　

　　二进制日志保存了所有更新或者可能更新数据库的操作。
　　二进制日志在启动 MySQL 服务器后开始记录，并在文件达到 max_binlog_size 所设置的
大小或者接收到 flush logs 命令后重新创建新的日志文件。
[root@localhost ~]# vim /etc/my.cnf
52 max_binlog_size = 1024000 //二进制日志最大 1M
　　只需定时执行 flush logs 方法重新创建新的日志，生成二进制文件序列，并及时把这些
日志保存到安全的地方就完成了一个时间段的增量备份。
　　要进行 MySQL 的增量备份，首先要开启二进制日志功能，开启 MySQL 的二进制日志
功能。

方法一：MySQL 的配置文件的[mysqld]项中加入 log-bin=文件存放路径/文件前缀，如 log-
bin=mysql-bin，然后重启 mysqld 服务。默认此配置存在。
[root@localhost ~]# awk /log-bin/'{print NR,$0}' /etc/my.cnf
51 log-bin=mysql-bin
114 #log-bin=mysql-bin

方法二：使用 mysqld --log-bin=文件存放路径/文件前缀重新启动 mysqld 服务
每周选择服务器负载较轻的时间段，或者用户访问较少的时间段进行备份。

2、MySQL 增量恢复

应用场景　　

1. 人为的 SQL 语句破坏了数据库
2. 在进行下一次全备之前发生系统故障导致数据库数据丢失
3. 在主从架构中，主库数据发生了故障

增量恢复的方法　　

1. 一般的恢复：备份的二进制日志内容全部恢复
格式：mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码

2. 基于时间点的恢复：便于跳过某个发生错误的时间点实现数据恢复
格式：从日志开头截止到某个时间点的恢复：
mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用
户名 -p 密码
从某个时间点到日志结尾的恢复：
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用
户名 -p 密码
从某个时间点到某个时间点的恢复：
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日小时:分钟:秒’ --stop-datetime=’年-月-日
小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

3. 基于位置的恢复：可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢
复更加精准
格式：
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

3、制定企业备份策略的思路

1. 确定当前 mysql 是处于哪种表类型下工作的，它们支持事物处理还是非事物的，因为我
们需要根据不同的特点来做一些设置。

2. 要选择备份的形式是完全备份还是增量备份，它们各有优缺点。

3. 为了保证恢复的完整性，我们得开启 binary log 功能，同时 binlog 给恢复工作也带来了
很大的灵活性，可以基于时间点或是位置进行恢复。考虑到数据库性能，我们可以将
binlog 文件保存到其他安全的硬盘中。

4. 正如最初所提到的，备份操作和应用服务得到同时运行，这样就十分消耗系统资源了，
会导致数据库服务性能下降，这就要求我们选择一个合适的时间（比如在应用负担很小
的时候）再来进行备份操作。

5. 不是备份完就万事大吉，我们还得确认备份是否可用，所以之后的恢复测试是完全有必
要的。

Δ 根据数据更新频繁，则应该较为频繁的备份
Δ 数据重要，则在有适当更新时进行备份
Δ 在数据库压力小的时段进行备份，如一周一次完全备份，然后每天进行增量备份
Δ 中小公司，全备一般可一天一次
Δ 大公司可每周进行一次全备，每天进行一次增量备份
Δ 尽量为企业实现主从复制架构

4、MySQL 企业备份案例

需求描述：
　　北京移电通信公司的用户信息数据库为 client ，用户资费数据表为 user_info
　　请为该公司每周进行完全备份
　　每天为该公司进行增量备份
　　新增加的用户信息如表所示

MySQL 完整和增量备份与恢复

安装 mysql （yum 方式）

[root@localhost ~]# yum -y install mysql mysql-server
[root@localhost ~]# cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf
cp ：是否覆盖"/etc/my.cnf" ？ y
[root@localhost ~]# vim /etc/my.cnf
[client]
default-character-set = utf8
[mysqld]
default-character-set = utf8
[root@localhost ~]# /etc/init.d/mysqld start
[root@localhost ~]# mysqladmin -uroot password '123123'

添加数据库、表，录入数据　　

[root@localhost ~]# mysql -uroot -p123123
mysql> create database client;
mysql> use client;
mysql> create table user_info( 身份证 char(20) not null, 姓名 char(20) not null, 性别 char(4), 用
户 户 ID  号 char(10) not null, 资费 int(10));
mysql> insert into user_info values('000000006',' 孙空悟',' 男','016','10');
mysql> insert into user_info values('000000007',' 蓝精灵',' 女','017','91');
mysql> insert into user_info values('000000008',' 姜姜',' 女','018','23');
mysql> select * from user_info;

　 MySQL 完整和增量备份与恢复

先进行一次完全备份

[root@localhost ~]# mkdir /mysql_bak
[root@localhost ~]# mysqldump -uroot -p123123 client user_info >/mysql_bak/client_userinfo-
$(date +%F).sql
[root@localhost ~]# mysqldump -uroot -p123123 client >/mysql_bak/client-$(date +%F).sql
[root@localhost ~]# ls /mysql_bak/
client-2016-12-09.sql client_userinfo-2016-12-09.sql

　进行一次日志回滚（生成新的二进制日志）　

[root@localhost ~]# ls /var/lib/mysql/
client ibdata1 ib_logfile0 ib_logfile1 localhost.localdomain.err mysql mysql-
bin.000001 mysql-bin.index mysql.sock test
[root@localhost ~]# mysqladmin -uroot -p123123 flush-logs
Enter password:
[root@localhost ~]# ls /var/lib/mysql/
client ibdata1 ib_logfile0 ib_logfile1 localhost.localdomain.err mysql mysql-
bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test

　　继续录入新的数据

[root@localhost ~]# mysql -uroot -p123123
mysql> use client;
mysql> insert into user_info values('000000009',' 关云长',' 男','019','37');
mysql> insert into user_info values('0000000010',' 罗纲',' 男','020','36');
mysql> select * from user_info;

　 MySQL 完整和增量备份与恢复

进行增量备份

[root@localhost ~]# mysqladmin -uroot -p123123 flush-logs
[root@localhost ~]# ls /var/lib/mysql/
client ib_logfile0 localhost.localdomain.err mysql-bin.000001 mysql-bin.000003
mysql.sock ibdata1 ib_logfile1 mysql mysql-bin.000002 mysql-bin.index test
[root@localhost ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002 // 查看新操
作的日志记录
[root@localhost ~]# cp -p /var/lib/mysql/mysql-bin.000002 /mysql_bak/

模拟误操作删除 user_info 　　

[root@localhost ~]# mysql -uroot -p123123 -e 'drop table client.user_info;'
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

恢复完全备份

[root@localhost ~]# mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2016-12-09.sql
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'

　 MySQL 完整和增量备份与恢复

恢复增量备份

[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -
p123123
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'

　 MySQL 完整和增量备份与恢复

基于时间点的增量备份恢复

[root@localhost ~]# mysql -uroot -p123123 -e 'drop table client.user_info;'
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@localhost ~]# mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2016-12-09.sql
[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161209 12:05:12 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log
created 161209 12:05:12
BINLOG '
eC1KWA8BAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#161209 12:05:53 server id 1 end_log_pos 241 Query  thread_id=7 exec_time=0
error_code=0
use `client`/*!*/;
SET TIMESTAMP=1481256353/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation
_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into user_info values('000000009',' 关云长',' 男','019','37')
/*!*/;
# at 241
#161209 12:06:00 server id 1 end_log_pos 374 Query  thread_id=7 exec_time=0
error_code=0
SET TIMESTAMP=1481256360/*!*/;
insert into user_info values('0000000010',' 罗纲',' 男','020','36')
/*!*/;
# at 374

#161209 12:07:25 server id 1 end_log_pos 417 Rotate to mysql-bin.000003 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

　仅恢复到 12:06:00 　

[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2016-12-09 12:06:00'
/mysql_bak/mysql-bin.000002 |mysql -uroot -p123123
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'

　 MySQL 完整和增量备份与恢复

仅恢复“罗纲”的信息，跳过“关云长”的信息恢复

[root@localhost ~]# mysql -uroot -p123123 -e 'drop table client.user_info;'
[root@localhost ~]# mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2016-12-09.sql
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='2016-12-09 12:06:00'
/mysql_bak/mysql-bin.000002 |mysql -uroot -p123123
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'

MySQL 完整和增量备份与恢复

基于位置的恢复

[root@localhost ~]# mysql -uroot -p123123 -e 'drop table client.user_info;'
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@localhost ~]# mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2016-12-09.sql
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='241' /mysql_bak/mysql-
bin.000002 |mysql -uroot -p123123
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'

　　 MySQL 完整和增量备份与恢复

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

[root@localhost ~]# mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2016-12-09.sql
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='241' /mysql_bak/mysql-
bin.000002 |mysql -uroot -p123123
[root@localhost ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'

MySQL 完整和增量备份与恢复

5、企业数据库备份脚本

[root@localhost ~]# vim /opt/mysql_bak_wanbei.sh // 完全备份脚本
#!/bin/bash
# MySQL  数据库完全备份脚本
#  设置登录变量
MY_USER="root"
MY_PASS="123123"
MY_HOST="localhost"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
#  设置备份的数据库( 或表)
MY_DB="client"
#  定义备份路径、工具、时间、文件名
BF_DIR="/mysql_bak/wanbei"
BF_CMD="/usr/bin/mysqldump"
BF_TIME=$(date +%Y%m%d-%H%M)
NAME="$MY_DB-$BF_TIME"
#  备份为.sql  脚本，然后打包压缩( 打包后删除原文件)
[ -d $BF_DIR ] || mkdir -p $BF_DIR
cd $BF_DIR
$BF_CMD $MY_CONN --databases $MY_DB > $NAME.sql
/bin/tar zcf $NAME.tar.gz $NAME.sql --remove &>/dev/null

[root@localhost ~]# vim /opt/mysql_bak_zengbei.sh // 增量备份脚本
#!/bin/bash
# MySQL 数据库增量备份脚本
# 设置登录变量
MY_USER="root"
MY_PASS="123123"
MY_HOST="localhost"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
# 定义备份路径、工具、二进制日志前缀、二进制日志存放路径

BF_TIME="$(date +%Y%m%d)"
BF_DIR="/mysql_bak/zengbei/$BF_TIME"
CMD="/usr/bin/mysqladmin"
QZ="mysql-bin"
LOG_DIR="/var/lib/mysql"
# 拷贝二进制日志
[ -d $BF_DIR ] || mkdir -p $BF_DIR
$CMD $MY_CONN flush-logs
/bin/cp -p $(ls $LOG_DIR/$QZ.* |awk -v RS="" '{print $(NF-2)}') $BF_DIR
[root@localhost ~]# chmod +x /opt/mysql_bak_*
[root@localhost ~]# crontab -e
0 0 * * 1 /opt/mysql_bak_wanbei.sh // 每周一 0:00 进行完备
0 0 * * 2-7 /opt/mysql_bak_zengbei.sh // 每天 0:00 进行增量备份

秒客网

MySQL 完整和增量备份与恢复

相关文章