mysql数据库篇——完全、增量备份和恢复

一、准备数据库环境

1、搭建安装环境

yum -y install \
gcc \
gcc-c++ \
make \
ncurses \
ncurses-devel \
bison \
Cmake

2、添加数据库用户
useradd -s /sbin/nologin mysql //添加数据库用户
3、安装mysql数据库

tar xf mysql-boost-5.7.20.tar.gz –C /opt/       //解压源码包

cmake \     //检查安装配置环境
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

make && make install        //编译安装

chown -R mysql.mysql /usr/local/mysql/  //将数据库安装目录的使用权限给用户mysql

vi /etc/my.cnf  //将主配置文件清空添加以下内容
[client]
port = 3306 
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

chown mysql:mysql /etc/my.cnf       //更改主配置文件的权限

echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile      //添加系统环境变量
source /etc/profile //刷新立即生效

cd /usr/local/mysql/        //进入安装目录初始化数据库
bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/       //制作数据库系统启动命令
systemctl daemon-reload           //刷新识别启动脚本
systemctl start mysqld      //启动数据库
netstat -anpt | grep 3306       //查看端口是否在监听状态

二、直接备份

停止数据库，直接打包数据库数据目录/usr/local/mysql/data
tar cJvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/

三、完全备份

完全备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。
优点：备份与恢复操作简单方便
缺点：数据库存在大量的重复，占用大量的备份空间，备份时间长。
1、使用mysqldump命令备份

# mysqldump -u root -p --all-databases > all-data-$(date +%F).sql //备份所有数据库
# mysqldump -u root -p --databases auth mysql > auth-mysql.sql //备份多个数据库
# mysqldump -u root -p auth > auth-$(date +%F).sql //备份单个auth数据库
# mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql //备份mysql的库中的user表

#mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql //备份mysql库user表的结构。
2、还原备份的数据库

source /tmp/auth.sql        //要先进入某个具体的数据库再进行恢复，除非是恢复所有数据库。
mysql -u root -p  < all-data-$(date +%F).sql    //恢复所有的数据库
mysql -u root -p mysql < mysql-user-$(date +%F).sql     //恢复mysql的user表，需要指定库名
mysql -uroot -p auth < /tmp/desc-mysql-user.sql     //将数据结构恢复到auth库中，单库备份的时候要指定还原到哪个数据库中，需要建立空库，库名可以任意

3、案列演示

create database client; //创建client库
use client; //进库
create table user_info(××× int(18),姓名 varchar(20),性别 varchar(20),用户ID号 int(20),资费 int(48));     //创建user_info表
desc user_info; //查看表结构

数据库篇——完全、增量备份和恢复
插入一些数据

insert into user_info values('000000001','孙空武','男','011','100');
insert into user_info values('000000002','蓝凌','女','012','98');
insert into user_info values('000000003','姜纹','女','013','12');
insert into user_info values('000000004','关园','男','014','38');
insert into user_info values('000000005','罗中昆','男','015','39');

查看数据
select * from user_info;
数据库篇——完全、增量备份和恢复
a、备份整个client数据库
mysqldump -uroot -p client > client-$(date +%F).sql

删除原有的client库重新建一个new_client空库

drop database client;
create database new_client;

还原client库
mysql -u root -p new_client< client-2018-12-10.sql
进入数据库查看验证
数据库篇——完全、增量备份和恢复
b、备份client库的中的表
mysqldump -uroot -p client user_info > client_user_info-$(date +%F).sql

删除client库中的user_info表

drop tables client.user_info;
show tables in client;

数据库篇——完全、增量备份和恢复
还原client库中的user_info表
mysql -uroot -p client < client_user_info-2018-12-10.sql
进入数据库查看验证

c、备份client库中的表结构（无数据）
mysqldump -uroot -p -d client user_info > desc-client_user_info.sql
数据库篇——完全、增量备份和恢复
新建new_client空数据库
create database new_client;
还原client库中user_info表中的结构
mysql -uroot -p new_client < desc-client_user_info.sql
进入数据库查看验证

desc new_client.user_info;
select * from new_client.user_info;

数据库篇——完全、增量备份和恢复

四、增量备份

增量备份：只有那些在上次完全备份或增量备份后被修改的文件才会被备份。
优点：没有重复数据，备份量不大，时间短。
缺点：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且对所有增量备份进行逐个反推恢复，操作较为繁琐。
为什么使用增量备份？
解决完全备份存在的问题，完全备份每次都把所有的数据备份，存在大量重复数据，备份与恢复的时间长。
增量备份的特点
（1）二进制日志保存了所有更新或者可能更新数据库的操作
（2）二进制日志在启动mysql服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。
（3）只需要定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。
1、开始二进制日志功能

vi /etc/my.cfg
在[mysqld]下添加
log_bin=/usr/local/mysql/data/mysql_bin

数据库篇——完全、增量备份和恢复
systemctl restart mysqld //重启数据库
2、查看生成的二进制文件
cd /usr/local/mysql/data/

3、案列演示
下面的数据库中的操作会被记录在mysql_bin.000001二进制日志中

create database client;
use client;
create table info(××× int(18),姓名 varchar(20),性别 varchar(20),用户ID号 int(20),资费 int(48));
insert into info values('000000001','孙空武','男','011','100');
insert into info values('000000002','蓝凌','女','012','98');
insert into info values('000000003','姜纹','女','013','12');
select * from info;

数据库篇——完全、增量备份和恢复
先做一次全量备份
mysqldump -u root -p client info > client_info-$(date +%F).sql

刷新日志文件
mysqladmin -uroot -p flush-logs

插入文件（该操作记录在mysql_bin.000002二进制日志中）

insert into info values('000000004','关园','男','014','38');
insert into info values('000000005','罗中昆','男','015','39');

数据库篇——完全、增量备份和恢复
刷新日志文件
mysqladmin -uroot -p flush-logs

模拟删除数据（该操作会被记录在mysql_bin.000003二进制日志中）

delete from info where ×××=4;
delete from info where ×××=5;

数据库篇——完全、增量备份和恢复
通过二进制日志恢复删除的文件

mysqlbinlog --no-defaults mysql_bin.000002 | mysql -u root -p       //实为恢复保存在mysql_bin.000002中的插入操作
进入数据库验证是否恢复成功
select * from client.info;

数据库篇——完全、增量备份和恢复
将恢复成功的表再做一次全量备份
mysqldump -u root -p client info > client_info-$(date +%F).sql
刷新日志
mysqladmin -uroot -p flush-logs

模拟数据库操作（其中既有正确的操作又有错误的操作，并且记录在mysql_bin.000004中）
正确操作：

insert into info values('000000006','蓝凌林','女','012','98');
insert into info values('000000007','姜纹文','女','013','12');

错误操作：
delete from info where ×××=1;
正确操作：

insert into info values('000000008','凌林','女','012','98');
insert into info values('000000009','纹文','女','013','12');

数据库篇——完全、增量备份和恢复

断点恢复（为了避开错误的操作）

进行最近一次全量备份还原
mysql -u root -p client < clientinfo-2018-12-05.sql
查看二进制日志内部详细信息：
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000004
获取第一个正确操作的位置变量
数据库篇——完全、增量备份和恢复

获取第二个正确操作的位置变量

开始恢复

mysqlbinlog --no-defaults --start-position='293'  --stop-position='732' mysql_bin.000004 | mysql -uroot -p  //断点恢复第一处
mysqlbinlog --no-defaults --start-position='1160'  --stop-position='1593' mysql_bin.000004 | mysql -uroot -p    //断点恢复第二处

验证端点恢复是否成功
数据库篇——完全、增量备份和恢复
说明：该操作成功避免了日志文件错误操作delete from info where ×××=1;对应的位置偏移量范围，将正确的操作恢复。

附：关于位置变量的筛选方法和时间变量的筛选方法

基于位置：at
跳过错误的
mysqlbinlog --no-defaults --stop-position='871' mysql_bin.000005 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='1160' mysql_bin.000005 | mysql -uroot -p
筛选正确的
mysqlbinlog --no-defaults --start-position='871' --stop-position='1160' mysql_bin.000005 | mysql -uroot -p
基于时间点：time
mysqlbinlog --no-defaults --stop-datetime='2018-03-11 21:30:39' mysql_bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --start-datetime='2018-03-11 21:30:39' mysql_bin.000002 | mysql -u root -p
筛选一段时间
mysqlbinlog --no-defaults --start-datetime='2018-12-05 21:30:39' --stop-datetime='2018-12-05 22:01:20' mysql_bin.000002 | mysql -u root -p
关键记录好时间点：
误操作时间点：2018-12-05 22:01:13
之后新增正确：2018-12-05 22:01:20

(责任编辑：IT)

搜索

热门标签: