图片 20

完全和增量备份与回复

  • MySQL 完全备份与还原

MySQL 完整和增量备份与还原,mysql备份与回复

  • MySQL 完全备份与回复

 

  • 1、数据备份的重要

在集团中数量的价值至关心珍爱要,数据保证了小卖部的作业的周转,因而数据的安全性及
可相信性是运营的关键,任何数据的散失都有相当大希望会对公司发生严重的后果。变成数据
丢失的缘由如下:
☆ 程序错误
☆ 人为错误
☆ 运算战败
☆ 磁盘故障
☆ 灾难(如火灾、地震)和盗窃

  • 二、数据库备份的归类

从情理与逻辑的角度

备份能够分成物理备份和逻辑备份。
大要备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。物理
备份又可分为脱机备份(冷备份)和1块备份(热备份)。

  1. 冷备份:是在闭馆数据库的时候进行的
  2. 热备份:数据库处于运转状态,那种备份方法正视于数据库的日记文件
  3. 温备份:数据库锁定表格(不可写入但可读)的事态下举行的
    逻辑备份:对数据库逻辑组件(如表等数据库对象)的备份

 

从数据库的备份战术角度

备份可分为完全备份、差距备份和增量备份
     
完全备份:每一遍对数据开始展览一体化的备份,即对壹切数据库的备份、数据库结议和文书
结构的备份,保存的是备份达成时刻的数据库,是出入备份与增量备份的基础。
可取:备份与回复操作轻便方便
缺陷:数据存在多量的再次;占用大量的空中;备份与回复时间长
     
差距备份:备份那么些自从上次完全备份之后被更动过的享有文件,备份的年月节点是
从上次总体备份起,备份数据量会愈来愈大。苏醒数据时,只需苏醒上次的通通备份与最
近的三遍差距备份。

     
增量备份:唯有那么些在上次完全备份可能增量备份后被退换的文件才会被备份。以上
次完整备份或上次的增量备份的时日为时间点,仅备份那时期的数据变动,由此备份的数
据量小,占用空间小,备份速度快。但恢复生机时,要求从上一遍的总体备份起到最后三次增
量备份依次苏醒,如中间某次的备份数据损坏,将促成数据的散失。

  • 三、MySQL  完全备份操作

MySQL 数据库的备份能够选取八种方法
一、直接打包数据库文件夹,如/usr/local/mysql/data/ 或 /var/lib/mysql/
示例:

[[email protected] ~]# 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

 

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

  

 

依傍数据丢失!

[[email protected] ~]# mkdir bak
[[email protected] ~]# mv /usr/local/mysql/data/* bak/

  

复原数据:

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

贰、使用专项使用备份工具 mysqldump
MySQL 自带的备份工具,优秀便宜对 MySQL
举办备份。通过该命令工具得以将数据库、
数据表或任何的库导出为 SQL 脚本,在急需还原时可举办数据恢复生机。
(一)对单个库进行完全备份
格式:mysqldump -u 用户名 -p[密码] [选项] [数据库名] >
/备份路线/备份文件名
示例:

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

(2)对七个库开始展览完全备份
格式:mysqldump -u 用户名 -p [密码] [选项] –databases 库名 1 [库名
2]… > /备份路线/备份
文件名
示例:

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

(3)对全数库进行完全备份
格式:mysqldump -u 用户名 -p [密码] [选项] –all-databases >
/备份路线/备份文件名
示例:

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

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

(四)对表举办完全备份
格式:mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 >
/备份路线/备份文件名
示例:

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

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

[[email protected] ~]# mysqldump -uroot -p123123 -d mysql user >/backup/desc_mysql_user-
$(date +%Y%m%d).sql
[[email protected] ~]# cat /backup/desc_mysql_user-20161208.sql
  • 肆、使用  mysqldump  备份后,复苏数据库  

1、source 命令
报到到 MySQL 数据库,实行 source 备份 sql 脚本路线
示例

[[email protected] ~]# 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 [密码] 库名 < 表备份脚本的门道
示例:

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

 

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

+————+——+
| name | ID |
+————+——+
| crushlinux | 123 |
+————+——+

  •  5、MySQL  备份思路 

一、定时实践备份,钦命备份安排或宗旨,并严苛遵从
2、除了进行完全备份,开启 MySQL 服务器的 binlog
日志成效是很关键的(完全备份加上日
志,能够对 MySQL 举办最大化还原)
三、使用统壹和易驾驭的备份名称,推荐使用库名或许表名加上岁月的命名规则,如
mysql_user-二〇一六120捌.sql,不要选择 backup一 或然 abc 之类未有意义的名字。

  • 陆、MySQL  完全备份案例 

供给描述:
京城移电通讯公司的用户新闻数据库为 client,用户支出数据表为
user_info,表结构如
下所示。请为该公司钦赐河里的备份战术,依靠所钦命的政策备份数据,模拟数据丢失进行
数据恢复生机。

图片 1

开创数量及表,录入数据:

[[email protected] ~]# 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  表:  

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

依傍数据丢失复苏数据:  

[[email protected] ~]# mysql -uroot -p123123 -e 'drop table client.user_info;' 

[[email protected] ~]# mysql -uroot -p123123 -e 'use client; show tables;'
[[email protected] ~]# mysql -uroot -p123123 client < /backup/client.user_info-20161208.sql
[[email protected] ~]# 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 |
+-----------+-----------+--------+-------------+--------+

按时备份数据:

[[email protected] ~]# which mysqldump
/usr/local/mysql/bin/mysqldump
[[email protected] ~]# 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
[[email protected] ~]# chmod +x /opt/bak_client.sh
[[email protected] ~]# crontab -e
0 0 * * * /opt/bak_client.sh // 每天 0:00  备份
  • 柒、MySQL 数据库备份脚本  

尝试蒙受:
mysql-server :192.168.200.101
mysql-client :192.168.200.102
试验须要:对 mysql-server 的 的 auth 库和 client 库
达成异地备份,每日凌晨 2:00 实行备份,
作文三个数据恢复生机脚本。

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

[[email protected] ~]# 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)

客户端安装客户端软件  

[[email protected] ~]# rpm -qa |grep mysql
mysql-libs-5.1.71-1.el6.x86_64
[[email protected] ~]# yum -y install mysql

连日来测试  

[[email protected] ~]# mysql -uadmin -p -h192.168.200.101
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| client |
| test |
+--------------------+
4 rows in set (0.00 sec)

行文客户端备份脚本  

[[email protected] ~]# 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
[[email protected] ~]# chmod +x /opt/bakmysql.sh
[[email protected] ~]# /opt/bakmysql.sh
[[email protected] ~]# ls /backup/
auth-20160505-1805.tar.gz client-20160505-1805.tar.gz
[[email protected] ~]# tar tvf /backup/auth-20160505-1805.tar.gz
-rw-r--r-- root/root 1967 2016-05-05 18:05 auth-20160505-1805.sql
[[email protected] ~]# tar tvf /backup/client-20160505-1805.tar.gz
-rw-r--r-- root/root 2250 2016-05-05 18:05 client-20160505-1805.sql
[[email protected] ~]# crontab -e
0 2 * * * /opt/bakmysql.sh
改变系统时间,执行任务计划,模拟每天的备份,为之后的恢复脚本做准备
[[email protected] ~]# date 050601592016.59 //MMDDhhmmYY.SS 月日小时分钟. 秒
2016 年 05 月 06 日 星期五 01:59:59 CST
[[email protected] ~]# ls /backup/
auth-20160505-1805.tar.gz client-20160505-1805.tar.gz
auth-20160506-0200.tar.gz client-20160506-0200.tar.gz
[[email protected] ~]# date 050701592016.59
2016 年 05 月 07 日 星期六 01:59:59 CST
[[email protected] ~]# date 050801592016.59
2016 年 05 月 08 日 星期日 01:59:59 CST
[[email protected] ~]# 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

撰写数据复苏脚本  

[[email protected] ~]# 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
[[email protected] ~]# 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)

回复测试:  

[[email protected] ~]# /opt/restore_mysql.sh
请指定要恢复数据库的日期(YYYYMMDD):20160507
搜索到的可恢复数据库如下:
3 auth
7 client
请选择您要恢复数据库的编号: 3
现在开始恢复数据库:auth 到 到 20160507
auth 已经恢复到 20160507
[[email protected] ~]# /opt/restore_mysql.sh
请指定要恢复数据库的日期(YYYYMMDD):20100101

很抱歉, 您恢复数据库的备份日期不再备份日期范围内
  •  MySQL  增量备份与还原 

 

  • 一、MySQL  增量备份概念  

      使用 mysqldump
实行完全备份,备份的数目中有再度数据,备份时间与还原时间过长。
而增量备份就是备份自上一次备份之后增添或改变的文件或内容。

 

增量备份的特点:

  未有再一次数据,备份量十分小,时间短
  复苏麻烦:须要上次完全备份及完全备份之后有所的增量备份技能苏醒,而且要对具备
增量备份实行依次反推恢复生机。
  MySQL未有提供第3手的增量备份办法,能够由此MySQL提供的二进制日志(binary
logs)
直接完成增量备份。

 

MySQL 2进制日志对备份的意义:  

  二进制日志保存了拥有更新也许恐怕更新数据库的操作。
  贰进制日志在起步 MySQL 服务器后早先记录,并在文件达到max_binlog_size 所设置的
高低恐怕收受到 flush logs 命令后再行成立新的日记文件。
[[email protected]
~]# vim /etc/my.cnf
52 max_binlog_size = 10贰陆仟 //2进制日志最大 1M
  只需定时实践 flush logs
方法重复创造新的日记,生成贰进制文件系列,并随即把那几个
日志保存到平安的地点就成功了二个时日段的增量备份。
  要拓展 MySQL 的增量备份,首先要翻开贰进制日志功用,开启 MySQL
的贰进制日志
功能。

 

方法一:MySQL 的陈设文件的[mysqld]项中参与log-bin=文件存放路线/文件前缀,如 log-
bin=mysql-bin,然后重启 mysqld 服务。暗许此布局存在。
[[email protected]
~]# awk /log-bin/'{print NR,$0}’ /etc/my.cnf
51 log-bin=mysql-bin
114 #log-bin=mysql-bin

 

方法二:利用 mysqld –log-bin=文件存放路线/文件前缀 重新开动 mysqld
服务
每一周选取服务器负荷较轻的年华段,或然用户访问较少的大运段进展备份。

  • 二、MySQL  增量恢复  

应用场景  

  1. 人工的 SQL 语句破坏了数据库
  2. 在开始展览下1次全备此前发生系统故障导致数据库数据丢失
  3. 在主导架构中,主库数据发生了故障

增量復苏的方式  

  1. 诚如的重振旗鼓:备份的二进制日志内容总体回复
    格式:mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p
    密码
  1. 依赖时间点的过来:便于跳过有些发生错误的年月点完结数据苏醒
    格式:从日记开首甘休到有些时间点的还原:
    mysqlbinlog [–no-defaults] –stop-datetime=’年-月-日 时辰:秒钟:秒’
    2进制日志 | mysql -u 用
    户名 -p 密码
    从有些时刻点到日志结尾的重作冯妇:
    mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’
    二进制日志 | mysql -u 用
    户名 -p 密码
    从有个别时刻点到有些时间点的过来:
    mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’
    –stop-datetime=’年-月-日
    小时:分钟:秒’ 2进制日志 | mysql -u 用户名 -p 密码

三.
基于地方的回复:可能在同近日间点既有不当的操作也有不易的操作,基于地方张开恢
复更精准
格式:
mysqlbinlog –stop-position=’操作 id’ 2进制日志 |mysql -u 用户名 -p
密码
mysqlbinlog –start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p
密码

  

  • 3、制定公司备份计策的思路 
  1. 规定当前 mysql
    是地处哪一种表类型下办事的,它们帮衬事物管理照旧非事物的,因为笔者
    们须求依附不一样的性状来做一些设置。

  2. 要选拔备份的方式是全然备份如故增量备份,它们各有利弊。

  3. 为了确定保障恢复生机的完整性,我们得开启 binary log 功用,同时 binlog
    给回复专门的学业也推动了
    十分的大的八面后珑,能够依照时间点或是地方张开回复。思虑到数据库质量,大家得以将
    binlog 文件保留到其它安全的硬盘中。

4.
正如最初所关联的,备份操作和应用服务获得同时运行,那样就万分消耗系统财富了,
会促成数据库服务特性下降,那将供给我们选用叁个合适的大运(举个例子在使用担负异常的小
的时候)再来进行备份操作。

5.
不是备份完就顺手,大家还得承认备份是还是不是可用,所以随后的重振旗鼓测试是完全有必
要的。

Δ 依照数量更新往往,则应该相比频繁的备份
Δ 数据主要,则在有适合的数量更新时开始展览备份
Δ 在数据库压力小的时节举行备份,如七日一次完全备份,然后天天举办增量备份
Δ 中型小型公司,全备一般可1天贰次
Δ 大公司可每一周实行贰遍全备,每日举行2遍增量备份
Δ 尽量为公司落到实处主从复制架构

 

  • 肆、MySQL  集团备份案例 

须要描述:
  东京移电通讯集团的用户信息数据库为 client ,用户支出数据表为
user_info
  请为该公司周周实行完全备份
  每日为该集团展开增量备份
  新添的用户信息如表所示

 图片 2

安装 mysql (yum  方式)

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

增进数据库、表,录入数据  

[[email protected] ~]# 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;

 图片 3

 

先进行三次完全备份

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

 举行一回日志回滚(生成新的2进制日志) 

[[email protected] ~]# ls /var/lib/mysql/
client ibdata1 ib_logfile0 ib_logfile1 localhost.localdomain.err mysql mysql-
bin.000001 mysql-bin.index mysql.sock test
[[email protected] ~]# mysqladmin -uroot -p123123 flush-logs
Enter password:
[[email protected] ~]# 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

  继续录入新的多寡

[[email protected] ~]# 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;

 图片 4

拓展增量备份

[[email protected] ~]# mysqladmin -uroot -p123123 flush-logs
[[email protected] ~]# 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
[[email protected] ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002 // 查看新操
作的日志记录
[[email protected] ~]# cp -p /var/lib/mysql/mysql-bin.000002 /mysql_bak/

依样葫芦误操作删除 user_info   

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

过来完全备份

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

 图片 5

恢复生机增量备份

[[email protected]
~]# mysqlbinlog –no-defaults /mysql_bak/mysql-bin.000002 | mysql -u
root –
p123123
[[email protected]
~]# mysql -uroot -p123123 -e ‘select * from client.user_info;’

 图片 6

基于时间点的增量备份苏醒

[[email protected] ~]# mysql -uroot -p123123 -e 'drop table client.user_info;'
[[email protected] ~]# mysql -uroot -p123123 -e 'select * from client.user_info;'
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[[email protected] ~]# mysql -uroot -p123123 client < /mysql_bak/client_userinfo-2016-12-09.sql
[[email protected] ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@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
[email protected]_COMPLETION_TYPE*/;

  

 仅恢复到 12:06:00  

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

 图片 7

仅复苏“罗纲”的音讯,跳过“关羽”的音信过来

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

图片 8

依赖地方的复原

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

  图片 9

[[email protected] ~]# mysql -uroot -p123123 -e 'drop table client.user_info;'

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

图片 10

  • 5、公司数据库备份脚本

[[email protected] ~]# 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

  

[[email protected] ~]# 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
[[email protected] ~]# chmod +x /opt/mysql_bak_*
[[email protected] ~]# crontab -e
0 0 * * 1 /opt/mysql_bak_wanbei.sh // 每周一 0:00 进行完备
0 0 * * 2-7 /opt/mysql_bak_zengbei.sh // 每天 0:00 进行增量备份

完整和增量备份与回复,mysql备份与回复 MySQL
完全备份与还原 一、数据备份的关键性
在百货店中多少的股票总市值至关心注重要,数据保证了商场的…

 

  • 一、数据备份的重中之重

在信用合作社中数量的股票总市值至关心注重要,数据保证了合作社的政工的运行,由此数据的安全性及
可信性是运行的第贰,任何数据的遗失都有相当大只怕会对同盟社爆发严重的后果。产生数据
丢失的来由如下:
☆ 程序不当
☆ 人为不当
☆ 运算退步
☆ 磁盘故障
☆ 灾难(如火灾、地震)和盗窃

  • 二、数据库备份的归类

从物理与逻辑的角度

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

  1. 冷备份:是在关门数据库的时候举行的
  2. 热备份:数据库处于运维情形,那种备份方法注重于数据库的日记文件
  3. 温备份:数据库锁定表格(不可写入但可读)的情景下进展的
    逻辑备份:对数据库逻辑组件(如表等数据库对象)的备份

 

从数据库的备份攻略角度

备份可分为完全备份、差别备份和增量备份
     
完全备份:每一趟对数据开始展览完全的备份,即对任何数据库的备份、数据库结交涉文书
布局的备份,保存的是备份完结时刻的数据库,是出入备份与增量备份的底子。
优点:备份与回复操作简捷方便
缺点:数据存在多量的重新;占用多量的上空;备份与回复时间长
     
差别备份:备份那二个自从上次统统备份之后被涂改过的具备文件,备份的岁月节点是
从上次完整备份起,备份数据量会更为大。恢复生机数据时,只需复苏上次的通通备份与最
近的三回差距备份。

     
增量备份:唯有那多少个在上次统统备份可能增量备份后被涂改的文件才会被备份。以上
次完整备份或上次的增量备份的日子为时间点,仅备份这时期的数额变动,因此备份的数
据量小,占用空间小,备份速度快。但恢复时,供给从上贰遍的完全备份起到最后二遍增
量备份依次复苏,如中间某次的备份数据损坏,将促成数据的不见。

  • 3、MySQL  完全备份操作

MySQL 数据库的备份能够动用多样办法
一、直接打包数据库文件夹,如/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)

二、使用专项使用备份工具 mysqldump
MySQL 自带的备份工具,万分有益对 MySQL
进行备份。通过该命令工具得以将数据库、
数据表或任何的库导出为 SQL 脚本,在须要恢复生机时可进展数据苏醒。
(一)对单个库实行完全备份
格式: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

(二)对七个库举行完全备份
格式: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

(四)对表实行完全备份
格式: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

(伍)对表结构的备份
格式: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
  • 四、使用  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 |
+————+——+

  •  伍、MySQL  备份思路 

1、定时施行备份,钦赐备份安排或政策,并严苛根据
2、除了开展完全备份,开启 MySQL 服务器的 binlog
日志功能是很要紧的(完全备份加上日
志,能够对 MySQL 实行最大化还原)
三、使用统一和易通晓的备份名称,推荐应用库名或然表名加上岁月的命名规则,如
mysql_user-二〇一五120八.sql,不要使用 backup1 或然 abc 之类没有意思的名字。

  • 六、MySQL  完全备份案例 

急需描述:
京师移电通讯公司的用户新闻数据库为 client,用户支出数据表为
user_info,表结构如
下所示。请为该公司钦命河里的备份攻略,依附所钦点的政策备份数据,模拟数据丢失举行
数据复苏。

图片 11

成立数量及表,录入数据:

[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  备份
  • 七、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 2进制日志对备份的含义:  

  二进制日志保存了全体更新或然大概更新数据库的操作。
  二进制日志在运行 MySQL 服务器后早先记录,并在文书达到max_binlog_size 所设置的
大小照旧接到到 flush logs 命令后再一次创制新的日志文件。
[root@localhost ~]# vim /etc/my.cnf
52 max_binlog_size = 拾二陆仟 //2进制日志最大 1M
  只需定时执行 flush logs
方法重新成立新的日志,生成2进制文件体系,并立时把这几个
日记保存到安全的地点就形成了贰个光阴段的增量备份。
  要举办 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
    密码
  1. 依照时间点的恢复:便于跳过某些产生错误的年月点落到实处数据苏醒
    格式:从日记开首停止到某些时间点的回涨:
    mysqlbinlog [–no-defaults] –stop-datetime=’年-月-日 时辰:分钟:秒’
    二进制日志 | mysql -u 用
    户名 -p 密码
    从有些时间点到日志结尾的复原:
    mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’
    2进制日志 | mysql -u 用
    户名 -p 密码
    从有个别时间点到有些时间点的恢复生机:
    mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’
    –stop-datetime=’年-月-日
    小时:分钟:秒’ 2进制日志 | mysql -u 用户名 -p 密码

三.
依据地方的借尸还魂:恐怕在同近期间点既有荒唐的操作也有不错的操作,基于地方进行恢
复越来越精准
格式:
mysqlbinlog –stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p
密码
mysqlbinlog –start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p
密码

  

  • 3、制定集团备份攻略的笔触 
  1. 明确当前 mysql
    是处于哪个种类表类型下专门的学业的,它们援助事物管理仍旧非事物的,因为自身
    们须求依照不一样的特色来做一些装置。

  2. 要挑选备份的花样是截然备份照旧增量备份,它们各有利害。

  3. 为了保障恢复的完整性,大家得开启 binary log 作用,同时 binlog
    给恢复专业也推动了
    十分大的狡滑,可以根据时间点或是地点实行回复。考虑到数据库品质,大家得以将
    binlog 文件保留到别的安全的硬盘中。

4.
正如最初所涉嫌的,备份操作和应用服务获得同时运行,那样就十一分消耗系统能源了,
会促成数据库服务性质下跌,那将供给大家选用一个老少咸宜的年月(比方在运用担负相当的小
的时候)再来举办备份操作。

伍.
不是备份完就顺手,我们还得承认备份是否可用,所以事后的复原测试是截然有必
要的。

Δ 依据数据更新往往,则应该比较频仍的备份
Δ 数据主要,则在有适当更新时进行备份
Δ 在数据库压力小的时节实行备份,如七日叁遍完全备份,然后天天开始展览增量备份
Δ 中型小型公司,全备一般可一天一遍
Δ 大厂家可每一周实行一回全备,每一天举办一次增量备份
Δ 尽量为合作社落到实处主从复制架构

 

  • 4、MySQL  集团备份案例 

须求描述:
  香岛移电通信集团的用户音信数据库为 client ,用户支出数据表为
user_info
  请为该公司周周实行完全备份
  天天为该公司打开增量备份
  新扩大的用户音讯如表所示

 图片 12

安装 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;

 图片 13

 

先举行三回完全备份

[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;

 图片 14

开始展览增量备份

[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;'

 图片 15

光复增量备份

[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;’

 图片 16

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

[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*/;

  

 仅复苏到 1二: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;'

 图片 17

仅苏醒“罗纲”的音信,跳过“美髯公”的音讯过来

[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;'

图片 18

基于地方的死灰复然

[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;'

  图片 19

[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;'

图片 20

  • 伍、公司数据库备份脚本

    [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 进行增量备份