在不同的环境中完成mysql备份

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

一、实验环境准备

1、所用的mysql为源码安装的mysql-5.5.33,这里就不演示了,参照http://qidian510.blog.51cto.com/7319882/1282064


2、创建一个新目录存放二进制日志

   #mkdir /binlogdircd

   #chown mysql:mysql /binlogdir


修改二进制文件存放路径为新建目录下,并设定innodb每表一文件

   #vim /etc/my.cnf

log-bin=/binlogdir/mysql-bin   #二进制日志文件存放位置

innodb_file_per_table = 1      #启用InnoDB表每表一文件,默认所有库使用一个表空间

启动服务

   #service mysqld start


3、配置所用的测试库和测试表

   mysql> create database testdb;

   mysql> use testdb;

   mysql> CREATE TABLE tb1 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, Name CHAR(30) NOT NULL UNIQUE KEY, Age TINYINT UNSIGNED);

   mysql> insert into tb1 values (1,'Huchong',24);

   mysql> insert into tb1 values (2,'Huafeng',24);

   mysql> insert into tb1 values (3,'Jim',21);

   mysql> insert into tb1 values (4,'Tom',25);

查看一下生成的表

101315416.png


创建用于存放备份的目录

   #mkdir /backup

   #chown -R mysql:mysql /backup


一、用mysqldump实现热备(逻辑备份)

1进行完全备份

   #mysqldump -uroot -pmypass --single-transaction --events--master-data=2 --all-databases > /backup/all_db_`date +%F`.sql

各选项的意义:


    --single-transaction: 基于此选项能实现热备InnoDB表;mysql> show table status\G;可以查看表的类型,如果表的类型不是全部为InnoDB,则需要使用--lock-all-tables

    --events备份数据的同时,备份事件调度器代码;

    --master-data=2 在备份文件中记录当前二进制日志的位置

        --all-databases 备份所有库


完全备份完成后,对数据库进行修改,模拟增量备份

   mysql> delete from tb1 where id = 4;

此时tb1表中剩下3个用户,这时候我们对其进行增量备份,而增量备份要指定开始位置和结束位置,因而我们先查看其开始位置:

   #less /backup/all_db_2013-09-01.sql


  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=2352;

找到相关的说明可以指定开始位置在2352

接着查看结束位置,也是当前所在的位置

101956505.png


2、增量备份:

   #cd /binlogdir

   # mysqlbinlog --start-position=2352 --stop-position=2542 mysql-bin.000007 > /backup/cactidb_`date +%F_%H`.sql

在服务器上进行其他操作,然后将二进制日志导出

   mysql> create table tb2 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, Name CHAR(30) NOT NULL UNIQUE KEY);

模拟数据库发生意外

   mysql> drop databases testdb

查看二进制日志最终的位置

# mysqlbinlog --start-position=2542 mysql-bin.000007 会出现类似下面的信息:


102046704.png


可以看到在执行drop database testdb的前面的位置#at 2716,表示我们回复到这个位置就是数据库发生意外之前的状态,所以我们取二进制日志最终位置为2716


导出二进制日志

   # mysqlbinlog --start-position=2542 --stop-position=2716 mysql-bin.000007 > /tmp/cadb.sql

也可以不用导出二进制文件,在滚动后直接复制即可。


恢复

恢复过程的二进制日志记录对我们没有意义,所以关闭此功能

      mysql> setsql_log_bin=0;

滚动一下日志

      mysql> flush logs

然后导入文件:

   # mysql -uroot -pmypass < /backup/all_db_2013-09-01.sql

   # mysql -uroot -pmypass < /backup/cactidb_2013-09-01_20.sql

   # mysql -uroot -pmypass < /tmp/cadb.sql


这时候修复完成,我们可以进去查看一下以确认

二、基于lvm的备份,属于几乎热备(属于物理备份)

前提:事务日志必须跟数据文件在同一个逻辑卷上;


1、我们安装mysql时就是将其安装在逻辑卷上,这里我们就不演示了,可以参照http://qidian510.blog.51cto.com/7319882/1282064


如果我们不在逻辑卷上:需要进行操作将其移到逻辑卷上

   #mysqldump -uroot -pmypass --lock-all-tables --all-databases --events > /backup/all_db_`date +%F`.sql      

   #service mysqld stop  

   #rm -rf /mydata/*

然后创建逻辑卷,格式化完成后挂载,可以参照http://qidian510.blog.51cto.com/7319882/1282064

在挂载的目录下创建新目录,属主属组为mysql

  #mkdir /mydata/data

  #chown -R /mydata/data

进行初始化

  #cd /usr/local/mysql

  #scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

启动服务

  #service mysqld start

连接到mysql

   mysql>set session sql_log_bin=0; 关闭二进制日志

   mysql>source /backup/all_db_`date +%F`.sql       #找到all_db_`date +%F`.sql 文件,这里将其导入

   mysql> flush privileges;    #让其生效

   mysql> show binary logs;   #查看二进制日志文件


连接到mysql上,施加全局锁

   mysql> flush tables with read lock;

   mysql> flush logs;

103739773.png


注意:施加锁以后不能退出,我们重新打开一个会话框进行操作

所以我们另起终端进行操作,先将上面的表保存到新创建的目录里

   # mkdir /backup/snapback-`date +%F`

   # mysql -uroot -pmypass -e 'show master status;' > /backup/snapback-2013-09-02/bin.txt


2创建快照卷,这里要强调一下,对于我们创建的快照卷的大小一定要大于备份的时间中数据的变化大小,否则就会使快照卷崩溃,从而失去了创建快照卷的意义

   #lvcreate -L 100M -n mydata-snap -p r -s /dev/myvg/mydata

回到施加锁的终端上释放锁

   mysql> unlock tables;

将创建的快照卷挂载

#mount /dev/myvg/mydata-snap /mnt


3备份

/backup/目录下创建一个新目录,将文件备份至新建目录中

   # mkdir /backup/snapback-2013-09-02_incremental

# cd /binlogdir

   # cp -rp /mnt/* /backup/


然后我们执行新的操作

   mysql> insert into tb2 values (1,'tom');

   mysql> insert into tb2 values (2,'jerry');

备份完成后,删除快照卷

   # mysqlbinlog --start-position=107 mysql-bin.000007 > /mybackup/snapback-2013-09-02/incremental.sql

   #umount /mnt

   #lvremove /dev/myvg/mydata-snap

模拟数据库意外损坏

   # service mysqld stop

   # rm -rf/mydata/data/*


4、恢复数据库

   # cp -a /backup/snapback-2013-09-02/* /mydata/data/

将复制的目录及文件属主属组都改为mysql

   # chown -R mysql:mysql /mydata/data/*

启动服务

   #service mysqld start

连接到mysql上,通过二进制日志进行恢复

   mysql>set session sql_log_bin=0;  #关闭二进制日志

   mysql>source /mybackup/snapback-2013-09-02_incremental/incremental.sql


查看数据库里我们添加的信息

   mysql> use testdb

   mysql> select * from tb2;


确认恢复成功,打开二进制日志功能

   mysql>set session sql_log_bin=0;


三、基于extrabackup完成备份(物理备份)

1下载地址:http://www.percona.com/software/percona-xtrabackup

innodb_file_per_table = 1  # 写入配置文件/etc/my.cnf中实现每表一个表空间

下载相关软件并安装,根据自己的情况选择下载

percona-toolkit-2.2.4-1.noarch.rpm

percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm


   # rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

   # innobackupex --help       #可以查看帮助


为备份建立一个只有备份权限的用户

     mysql> create user 'percona'@'localhost' identified by 'mypass';

     mysql> revoke all privileges,grant option from 'percona'@'localhost';

     mysql> grant reload,lock tables,replication client on *.* to 'percona'@'localhost';

     mysql> flush privileges;


2备份一次MySQL

   #mkdir /mybackup

   #innobackupex --host=localhost --user=percona --password=mypass /mybackup/

如果执行正确,其最后输出的几行信息通常如下:


xtrabackup: Transaction log of lsn (1607784) to (1607784) was copied.

130902 10:13:15  innobackupex: All tables unlocked


innobackupex: Backup created in directory '/mybackup/2013-09-02_10-13-10'

innobackupex: MySQL binlog position: filename 'mysql-bin.000005', position 583

130902 10:13:15  innobackupex: Connection to database server closed

130902 10:13:15  innobackupex: completed OK!


   一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobakupex命令的--apply-log选项可用于实现上述功能


模拟异常损坏并恢复

   #service mysqld stop

   #rm -rf /mydata/data/*

   #innobackupex --apply-log /mybackup/2013-09-02_10-13-10/  

     --apply-log   #已经提交的事务同步至数据文件;没commit的事务撤销

   #innobackupex --copy-back /mybackup/2013-09-02_10-13-10/

          --copy-back       #数据库恢复,后面跟上备份目录的位置

   #chown -R mysql:mysql /mydata/data/*

#service mysqld start


3使用innobackupex进行增量备

需要注意的是,增量备份仅能应用于InnoDBXtraDB表,对于MyISAM表而言,执行增量备份时其实进行的是完全备份。

注意:每一次恢复操作后都要重新进行备份的

   #innobackupex --host=localhost --user=percona --password=mypass /mybackup/

为了演示效果,我们对数据库进行一些操作

   mysql> drop table tb2;

进行增量备

   #innobackupex --user=percona --password=mypass --incremental/mybackup/ --incremental-basedir=/mybackup/2013-09-02_11-17-35/

--incremental-basedir=     #表示基于什么进行增量备

4、对mysql操作来进行第二次增量备份

   mysql> create database backupdb;

   # innobackupex --user=percona --password=mypass --incremental /mybackup/--incremental-basedir=/mybackup/2013-09-02_11-30-51/

如果我们二次增量备份完成后,又执行了新的操作

   mysql> insert into tb1 (id,name,age)  value (4,'julia',22);

mysql> insert into tb1 (id,name,age)  value (5,'kate',26);


5模拟mysql意外损坏

   #service mysqld stop

#rm -rf /mydata/data/*


6、进行恢复

   # innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/

   # innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/--incremental-dir=/mybackup/2013-09-02_11-23-00/

   # innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/--incremental-dir=/mybackup/2013-09-02_11-32-18/

--redo-only 表示将已经提交的事务应用到数据库,还没有提交的则不撤销,防止所恢复的数据不完整

   #innobackupex --copy-back /mybackup/2013-09-02_11-30-51/

   #cd /mydata/data

   #chown -R mysql:mysql *

最后启动服务

   #service mysqld start




本文转自 宋鹏超 51CTO博客,原文链接:http://blog.51cto.com/qidian510/1291804,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
关系型数据库 MySQL Shell
shell学习(十七) 【mysql脚本备份】
shell学习(十七) 【mysql脚本备份】
17 0
|
15天前
|
关系型数据库 MySQL Linux
linux下mysql定时备份
linux下mysql定时备份
35 0
|
15天前
|
关系型数据库 MySQL Linux
mysql 主从同步 实现增量备份
mysql 主从同步 实现增量备份
24 0
|
15天前
|
SQL 关系型数据库 MySQL
轻松入门MySQL:MySQL 备份与恢复,保护数据安全,提高工作效率(20)
轻松入门MySQL:MySQL 备份与恢复,保护数据安全,提高工作效率(20)
|
15天前
|
关系型数据库 MySQL Linux
Linux系统使用定时脚本备份mysql
Linux系统使用定时脚本备份mysql
|
15天前
|
关系型数据库 MySQL Shell
备份 MySQL 的 shell 脚本(mysqldump版本)
【4月更文挑战第28天】
24 0
|
15天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(备份+恢复篇)(二)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(备份+恢复篇)
66 1
|
13天前
|
Prometheus Cloud Native 关系型数据库
实时计算 Flink版产品使用合集之binlog被清理掉的问题,并且binlog有备份,有什么方法来恢复到RDS
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
15天前
|
关系型数据库 MySQL Linux
服务器Linux系统配置mysql数据库主从自动备份
这是一个基本的配置主从复制和设置自动备份的指南。具体的配置细节和命令可能因您的环境和需求而有所不同,因此建议在操作前详细阅读MySQL文档和相关资源,并谨慎操作以避免数据丢失或不一致。
40 3
|
15天前
|
关系型数据库 MySQL 数据库连接
用Navicat备份Mysql演示系统数据库的时候出:Too Many Connections
用Navicat备份Mysql演示系统数据库的时候出:Too Many Connections
48 0