实现MySQL数据备份与恢复实例

最新推荐文章于 2023-03-24 21:42:49 发布

-KamMinG

最新推荐文章于 2023-03-24 21:42:49 发布

阅读量430

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/Kammingo/article/details/106636895

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

数据备份与恢复

数据备份方式：冷备（cp、tar…）、逻辑备份（mysqldump/mysql）

完全备份：备份所有数据

增量备份：备份上次备份后，所有新产生的数据

差异备份：备份完全备份后，所有新产生的数据

物理备份

例：（冷备完全备份虚拟机50—>虚拟机51）

50：

# cp -r /var/lib/mysql /root/mysqlall.bak 
# scp -r /root/mysqlall.bak 192.168.4.51:/root

51：

# systemctl stop mysqld 
# rm -rf /var/lib/mysql 
# cp -r /root/mysqlall.bak/ /var/lib/mysql 
# chown -R mysql:mysql /var/lib/mysql 
# systemctl start mysqld 
# mysql -uroot -p123456 
mysql> show databases;

局限性：数据库版本要一致，操作系统要一致；如果只备份一个库，而且存储引擎是innodb，
要把日志文件也备份；只适用数据库量小的

逻辑备份及操作

备份：mysqldump -uroot -p密码库名 > 路径/xxx.sql

恢复：mysql -uroot -p密码库名 < 路径/xxx.sql

库名表示方式：–all-databases 或 -A 所有库

数据库名单个库
数据库名表名单张表
-B 数据库1 数据库2 多个库

无论备份还是恢复，都要验证用户权限

例：

数据备份

# mysqldump -uroot -p123456 -A > /mydata/alldb.sql 
# mysqldump -uroot -p123456 db4 > /mydata/db4.sql 
# mysqldump -uroot -p123456 db4 t2 > /mydata/db4_t2.sql 
# mysqldump -uroot -p123456 -B db3 db2 > /mydata/twodb.sql

只备份一个库是没有SQL命令的

完全恢复数据（删除对应的备份数据，然后使用备份文件恢复数据）

删除单个表恢复：

# mysql -uroot -p123456 
mysql> use db4; 
mysql> show tables; 
mysql> drop table db4.t2; 
mysql> show tables; 
mysql> exit 
# mysql -uroot -p123456 db4 < /mydata/db4_t2.sql  
# mysql -uroot -p123456 
mysql> use db4; 
mysql> show tables;

删除单个库恢复：

mysql> show databases; 
mysql> drop database db4; 
mysql> create database db4; 
mysql> use db4; 
mysql> show tables; 
mysql> exit 
# mysql -uroot -p123456 db4 < /mydata/db4.sql  
# mysql -uroot -p123456
mysql> show databases; 
mysql> use db4; 
mysql> show tables;

可以把备份数据恢复到任意一个空的数据库里

mysql> create database test; 

# mysql -uroot -p123456 test < /mydata/db4.sql

数据增量备份

启用binlog日志实现数据实时增量备份

binlog日志：二进制日志，记录所有更改数据的操作

配置：log_bin[=dir/name] (不指定位置默认保存在/var/lib/mysql目录下

名字默认为主机名-bin…)

server_id=数字 (范围：1-255)

max_binlog_size=数字m (不定义的话默认500m)

若要指定保存目录，需要把目录创建出来，并且mysql用户要有写权限

三种记录格式：

Statement：每一条修改数据的sql命令都会记录在binlog日志中

Row：不记录sql语句上下文相关信息，仅保存哪条记录被修改

Mixed：以上混合

例：

查看日志当前记录格式

mysql> show  variables like "binlog_format"; 

默认为ROW 

修改为MIXED 

# systemctl stop mysqld 
# vim /etc/my.cnf 
在[mysqld]下添加 
log_bin=/logdir/kam 
server_id=50 
binlog_format="mixed" 
# mkdir /logdir 
# chown mysql /logdir 
# systemctl restart mysqld 
# ls /logdir/

.000001：第一个产生的binlog日志文件

.index：索引文件，记录已有的binlog日志文件

分析binlog日志

mysqlbinlog [选项] binlog 日志文件名

常用选项：

–start-datetime=“yyyy-mm-dd hh:mm:ss”

–stop-datetime=“yyyy-mm-dd hh:mm:ss”

–start-position=数字（偏移量）

–stop-position=数字

例：

mysql> use db4; 

mysql> select * from t1; 

mysql> insert into  t1 values( 

    -> "kk",555,555 

    -> ); 

mysql> update t1 set uid=2 where name="root"; 

# mysqlbinlog /logdir/kam.000001 | grep -i select 

# mysqlbinlog /logdir/kam.000001 | grep -i update 

# mysqlbinlog /logdir/kam.000001 | grep -i insert

可以另开终端，然后在数据库进行增删改查操作，在另一边查看验证实时性

选项：

mysql> use db4; 
mysql> show tables; 
mysql> desc t2; 
mysql> insert into t2 values( "tom",156,"/bin/bash" ); 
mysql> insert into t2 values( "xixi",156,"/bin/bash" ); 
# mysqlbinlog kam.000005 
# mysqlbinlog --start-position=735 --stop-position=931 kam.000005 
# mysqlbinlog --start-datetime="18/11/21 14:38:32" --stop-datetime="18/11/21 15:41:35" kam.000005

手动生成新的日志文件

重启mysqld服务

# systemctl stop mysqld 
# systemctl start mysqld 
# ls /logdir/ 


执行SQL操作 mysql > flush logs； 

# mysql -uroot -p123456 

mysql> insert into db4.t1 values( "bob",123,123); 

mysql> insert into db4.t1 values( "bob",123,123); 

mysql> flush logs; 

mysql> quit 

# ls /logdir/ 

 

mysqldump  --flush-logs 

# mysqldump -uroot -p123456 db4 t2 > /mydata/db4_t2.sql --flush-logs 

# ls /logdir/ 

mysql -uroot -p密码 -e "flush logs" 

# mysql -uroot -p123456 -e "flush logs" 

# ls /logdir/

如果日志文件没有满就创建了另外一个日志文件，则记录会保存在数字最大的日志文件中

删除日志文件

不建议用系统命令rm删除，删除某个日志文件，.index里面还有

mysql> show master status;            //显示当前使用的日志文件

1.删除早于指定版本的binlog日志文件

mysql> purge master logs to "kam.000003"; 

# ls /logdir/

2.删除所有binlog日志，重建新日志

mysql> reset master; 

# ls /logdir/

利用binlog恢复数据

使用mysqlbinlog提取历史SQL操作；通过管道交给mysql命令执行

例：

mysqldump -uroot -p123456 db4 t3 > /root/t3.sql 

mysql> use db4; 

mysql> desc t3; 

mysql> reset master; 

mysql> insert into t3 values( "bob",55,"/home/bob" ); 

mysql> insert into t3 values( "tom",55,"/home/bob" ); 

mysql> select * from t3; 

mysql> drop table t3; 

mysql> show tables; 

mysql -uroot -p123456 db4 < /root/t3.sql 

mysql> select * from t3; 

# mysqlbinlog kam.000001            //查看日志文件记录新数据范围，记录偏移量 

# mysqlbinlog --stop-position=893 kam.000001 | mysql -uroot -p123456 

mysql> select * from t3;               //日期、偏移量按实际情况填写

安装第三方软件提供的innobackupex命令实现数据的增量备份与恢复

物理备份缺点：跨平台性差；备份时间长，冗余备份，浪费存储空间

Mysqldump备份缺点：效率较低，备份和还原速度慢；备份过程中，数据插入和更新操作
（会被挂起）

Xtrabackup：C程序，支持InnoDB/XtraDB

Innobackupex：perl脚本封装xtrabackup，还支持MyISAM，但是如果存储引擎
（为MyISAM，还是完全备份，不是增量备份）

步骤一：安装XtraBackup软件包
链接：https://pan.baidu.com/s/1t-adQjGAe66wKl2fZFwAnQ
提取码：8pyv
percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

链接：https://pan.baidu.com/s/1ITM7jX_NMbJqM4Rm-SHyEg
提取码：epk3
libev-4.15-1.el6.rf.x86_64.rpm

# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm  
# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 
# rpm -ql percona-xtrabackup-24

mysql> show engines; 
mysql> show databases; 
删库，剩下 information_schema、mysql、performance_schema、sys  
mysql> quit 
# systemctl stop mysqld 
# vim /etc/my.cnf 
[mysqld] 
#default-storage-engine=myisam          //添加注释 
# systemctl start mysqld 
# mysql -uroot -p123456 
mysql> show engines; 
mysql> create database db5; 
mysql> create table db5.a(id int); 
mysql> insert into db5.a values(555);          //输入6次

步骤二：innobackupex完整备份、增量备份操作

格式：innobackupex <选项>
在这里插入图片描述
完全备份与恢复
备份：

# innobackupex --user root --password 123456 /allbak --no-timestamp 

# ls /allbak/                      //备份文件夹不用事先创建

不仅有原来库的数据库文件夹，还有备份这个库的备份文件夹，恢复数据的时候可以以此为标准做增量备份

恢复：
1.准备恢复数据

# cat /allbak/xtrabackup_checkpoints 
# innobackupex --user root --password 123456 --apply-log /allbak/ 
# cat /allbak/xtrabackup_checkpoints

2.拷贝备份文件到数据库目录

# systemctl stop mysqld 

# rm -rf /var/lib/mysql/* 

# innobackupex --user root --password 123456 --copy-back /allbak/

3.修改用户权限

# chown -R mysql:mysql /var/lib/mysql

4.重启服务

# systemctl start mysqld

增量备份与恢复

备份：

1.首先做完全备份（第一次备份要把数据全备份）

# rm -rf /allbak/                                //清理原来环境 

# innobackupex --user root --password 123456 /fullbak --no-timestamp

2.完全备份完成后，向表里写新数据，做增量备份（写新数据可以另开终端）

mysql> use db5; 

mysql> show tables; 

mysql> insert into a values("666");             //输入任意次数 

mysql> select * from a; 

# innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp 

# cd /new1dir/ 

# ls

3.再往表里写新数据，做增量备份（注意指定上次备份数据的目录名）

mysql> insert into db5.a values("777");          //输入任意次数 

# innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp

恢复：

（模拟数据已丢失）

# systemctl stop mysqld 

# rm -rf /var/lib/mysql 

# mkdir /var/lib/mysql

1.准备恢复数据

# innobackupex  --apply-log --redo-only /fullbak

2.合并日志文件（数据库服务已关，可以不输用户名、密码）

# innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/new1dir 

# innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/new2dir 

# cat /fullbak/xtrabackup_checkpoints 

# cat /new1dir/xtrabackup_checkpoints 

# cat /new2dir/xtrabackup_checkpoints

合并后/fullbak/xtrabackup_checkpoints文件中：

序列号from_lsn=0，to_lsn=/new2dir/xtrabackup_checkpoints文件中的to_lsn

体现完全备份+增量备份

# innobackupex --copy-back /fullbak/

4.修改用户权限

# chown -R mysql:mysql /var/lib/mysql

5.重启服务验证

# systemctl start mysqld 

mysql> select * from db5.a; 

当前所有数据都在/fullbak里 

# rm -rf /new1dir/ 

# rm -rf /new2dir/

执行过程

往innodb存储引擎写数据—>支持事务回滚—>有事务日志—>每添加一次数据—>会用一个序列号记录起来

xtrabackup_checkpoints文件

lsn:日志序列号

from_lsn参考上一次备份文件的to_lsn序列号，所以能识别哪些数据没有备份

恢复完全备份的单个表

备份单个库的时候，还要把数据库服务默认的三个库mysql、performance_schema、sys也备份

例：

mysql> use db5; 

mysql> show tables; 

mysql> create table b (name char(10)); 

mysql> insert into b values("bob"); 

mysql> insert into b values("tom"); 

mysql> insert into b values("jack"); 

mysql> insert into b values("jerry"); 

mysql> insert into b values("lili");

完全备份b表：

# innobackupex --user root --password 123456 --database="db5" /db5bak --no-timestamp 

mysql> drop table db5.b; 

# ls /var/lib/mysql/db5/ 

# ls /db5bak/

恢复：

1.创建删除的b表（建的空表表结构一定要和备份的时候一样，不然恢复不了）

mysql> create table db5.b (name char(10)); 

mysql> select * from db5.b;

2.删除b表的表空间文件

# ls /var/lib/mysql/db5 

b.ibd：表空间文件（现在为空） 

mysql> alter table db5.b discard tablespace;     //删除表空间文件，表结构还在 

# ls /var/lib/mysql/db5

导出表信息

# cd /db5bak/db5 

# ls b.* 

# innobackupex --user root --password 123456 --apply-log --export /db5bak/

把导出的表信息文件拷贝到数据库目录下

# ls /var/lib/mysql/db5 

# cd /db5bak/db5 

# ls b.* 

# cp b.{cfg,exp,ibd} /var/lib/mysql/db5/ 

# chown -R mysql:mysql /var/lib/mysql/db5/*

导入表空间

mysql> select * from db5.b; 

mysql> alter table db5.b import tablespace; 

mysql> select * from db5.b; 

# rm -rf /var/lib/mysql/db5/b.cfg  

# rm -rf /var/lib/mysql/db5/b.exp

实验案例：
虚拟机：192.168.4.57
1)192.168.4.57主机做网站服务器
部署LAMP环境
运行网站服务并编写网页测试文件test.html 网页内容自定义

2)192.168.4.50主机做mysql数据库服务器
创建一个新库名gamedb 用来存储网站服务器192.168.4.57的数据
添加新的授权用户要求如下：允许192.168.4.57使用userweb用户连接数据库服务器50 连接密码123456 连接后对gamedb库仅有查询记录和插入记录的权限

3)在数据库服务器的gamedb库里创建存储网站注册用户信息的表regtab

4)程序员编写用户注册网页页面（.html）

<h1> reg login user info :</h1> 

<h3>LoginName: </h3><input type="text" name="name" size="20" maxlength="255"/> 

<br /> 

<br /> 

<h3>LoginPass: </h3><input type="text" name="password" size="20" maxlength="255"/> 

<br /> 

<br /> 

<h3><input type="submit" name="submit" value="注册" /></h3></p>

5)程序员编写存储注册帐号到数据库的脚本（.php）

<?php $servername = "192.168.4.50"; $username = "userweb"; $password = "123456"; $dbname = "gamedb"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("link fail: " . $conn->connect_error); }else {echo "link mysql Ok"; echo " ";} $username=$_POST['name']; $userpassword=$_POST['password']; $sql="insert into regtab (username,password) values ('$username','$userpassword')"; if($conn->query($sql)){echo "insert data ok"; } $conn->close(); ?>

虚拟机57：

# yum -y install httpd mariadb mariadb-server php php-mysql 

# systemctl restart httpd 

# systemctl enable httpd 

# vim /var/www/html/test.html

虚拟机50：

mysql> show databases; 

mysql> create database gamedb; 

mysql> grant select,insert on gamedb.* to userweb@"192.168.4.57" identified by "123456";

虚拟机57：

mysql -h192.168.4.50 -uuserweb -p123456 

mysql> show grants;

虚拟机50：

mysql> create table gamedb.regtab( 

    -> username char(15), 

    -> password char(6) 

    -> ); 

mysql> select * from gamedb.regtab;

虚拟机57：

# vim /var/www/html/index.html 
<html>
  <form action="reg.php" method="post">
    <h1> reg login user info :</h1> 
    <h3>LoginName: </h3><input type="text" name="name" size="20" maxlength="255"/>
    <br />
    <br />
    <h3>LoginPass: </h3><input type="text" name="password" size="20" maxlength="255"/>
    <br />
    <br />
    <h3><input type="submit" name="submit" value="注册" /></h3></p>
  </form>
</html>

# vim /var/www/html/reg.php 
<?php
$servername = "192.168.4.50";
$username = "userweb";
$password = "123456";
$dbname = "gamedb";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("link fail: " . $conn->connect_error);
}else {echo "link mysql Ok"; echo "       ";}

$username=$_POST['name'];
$userpassword=$_POST['password'];


$sql="insert into regtab (username,password) values ('$username','$userpassword')";

if($conn->query($sql)){echo "insert data ok"; }
$conn->close();

?>

# firefox 192.168.4.57

注册一个账户

50：

mysql> select * from gamedb.regtab;

-KamMinG

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
实现MySQL数据备份与恢复实例

数据备份与恢复数据备份方式：冷备（cp、tar…）、逻辑备份（mysqldump/mysql）完全备份：备份所有数据增量备份：备份上次备份后，所有新产生的数据差异备份：备份完全备份后，所有新产生的数据物理备份例：（冷备完全备份虚拟机50—>虚拟机51）50：# cp -r /var/lib/mysql /root/mysqlall.bak # scp -r /root/mysqlall.bak 192.168.4.51:/root 51：# systemctl stop mys
复制链接

扫一扫