专栏首页文渊之博MySQL常见备份方案

MySQL常见备份方案

MySQL常见备份方案有以下三种:

       mysqldump + binlog

       lvm + binlog  

       xtrabackup

本例为方便演示,数据库里面数据为空。下面开始动手

mkdir /opt/backup                                #创建备份目录
mkdir -p /data/3309/{data,binlog}     
cd /usr/local/mysql/
scripts/mysql_install_db --user=mysql --datadir=/data/3309/data/ --basedir=/usr/local/mysql/ 
chown mysql.mysql -R /data/3309/ 
cp support-files/my-small.cnf /data/3309/my.cnf   #提供配置文件

vim /data/3309/my.cnf                             #编辑配置文件

1、利用mysqldump完全备份mysql,配合二进制日志备份实现增量备份

mysqldump 选项请参考http://wangweiak47.blog.51cto.com/2337362/1589304

1.1提供模拟数据

mysql -S /tmp/mysql.sock4   #连接mysql
mysql> use test;
Database changed
mysql> create table test (id int(2),comment char(30));  #创建表
Query OK, 0 rows affected (0.34 sec)
 
mysql> insert into test values (1,'yun zhonghe');  #插入数据
Query OK, 1 row affected (0.16 sec)

1.2全量备份:

mysqldump -S /tmp/mysql.sock4 -A -B -F -x --events --triggers --routines --master-data=2 > /opt/backup/all_data-`date +%F--%U`.sql

1.3 模拟数据发生改变

mysql> insert into test values (2,'yun zhonghe2');    #再插入一条数据。
Query OK, 1 row affected (0.11 sec)

1.4 增量备份

mysqladmin -S /tmp/mysql.sock4 flush-logs   #增量备份前,先滚动一下二进制日志。
cp `cat /data/3309/binlog/mysql-bin.index | tail -n 2 | head -n 1` /opt/backup/   #备份二进制日志

1.5 数据损坏

rm -rf /data/3309/
killall mysqld

1.6 恢复

上面已经提供了步骤,直接复制过来使用。

mkdir -p /data/3309/{data,binlog}
cd /usr/local/mysql/
scripts/mysql_install_db --user=mysql --datadir=/data/3309/data/ --basedir=/usr/local/mysql/
cp /opt/backup/my.cnf /data/3309/
chown mysql.mysql -R /data/3309/
mysqld_safe --defaults-file=/data/3309/my.cnf &    

mysql -S /tmp/mysql.sock4     #连接mysql查看数据
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> source /opt/backup/all_data-2015-10-01--39.sql
 
mysql> select * from test.test;   #还缺一个数据
+------+-------------+
| id   | comment     |
+------+-------------+
|    1 | yun zhonghe |
+------+-------------+
1 row in set (0.00 sec)  
 
mysql> source /tmp/incres-1.sql;
 
mysql> select * from test.test;   #数据已恢复
+------+--------------+
| id   | comment      |
+------+--------------+
|    1 | yun zhonghe  |
|    2 | yun zhonghe2 |
+------+--------------+
2 rows in set (0.00 sec)
 
mysql> set sql_log_bin = 1;  #打开二进制记录开关。

1.7小结,mysqldump适合于数据量较小的场合,它的优点是有众多选项,使用起来非常灵活,缺点是数据量一旦过大,非常耗时耗力。

2、使用lvm进行全备。

lvm快照卷原理参考

http://baike.baidu.com/link?url=iEpO_zm_AbHbk-ijQa8jNcFRoPPG2NdTYb_cRoQ7mjQb_ag9g-fL7yHPXV7Atp2j3J0L5xYynM9KKLIwycW_S_

2.1 数据接着上次继续开始。

2.2 全备。

需保证数据库存放在逻辑卷组上面,才行。

2.2.1首先锁表和滚动日志

mysql> flush table with read lock;
Query OK, 0 rows affected (0.02 sec)
 
mysql -S /tmp/mysql.sock4 -e 'show master status;' > /backup/master.info  #记录当前日志信息
mysqladmin -S /tmp/mysql.sock4 flush-logs   #滚动日志

2.2.2创建快照

lvcreate -s -n snap_data -L 500M /dev/vg_node5/mylv_data
  Logical volume "snap_data" created

2.2.3 解锁表

mysql> unlock tables;  
Query OK, 0 rows affected (0.00 sec)

2.2.4 复制快照卷数据到备份目录下

mkdir -p /backup/lvm
mount /dev/vg_node5/snap_data /mnt/lvm
cp -R /mnt/3309/* /backup/lvm

2.2.5 删除快照

umount /mnt
lvremove /dev/mapper/vg_node5-snap_data
Do you really want to remove active logical volume snap_data? [y/n]: y
  Logical volume "snap_data" successfully removed

2.3增量备份,只需和定时复制binlog到备份目录下面即可

2.4恢复,只需要直接拷贝备份目录下的文件即可

killall mysqld
rm -rf /data/3309/
cp -R /backup/lvm/ /data/3309/
chown mysql.mysql -R /data/3309/
mysqld_safe --defaults-file=/data/3309/my.cnf &
ss -tnl | grep 330
LISTEN     0      50                        *:3309                     *:* 
 
mysql> select * from test.test;   #数据未改变
+------+--------------+
| id   | comment      |
+------+--------------+
|    1 | yun zhonghe  |
|    2 | yun zhonghe2 |
+------+--------------+

2.5小结:

lvm实现物理备份速度相对mysqldump来比较快,实现也比较简单,是不错的选择。

缺点:数据目录必须存放在lvm卷组上面

3、使用xtrabackup实现热备。

请自行到官网下载并安装对应的rpm包。

使用方法参考:详细参考:

http://www.cnblogs.com/Amaranthus/archive/2014/08/19/3922570.html

3.1创建一个具有最小权限的用户

mkdir /backup/xtrabackup
mysql> grant RELOAD, LOCK TABLES, REPLICATION CLIENT on *.* to 'bkuser'@'localhost' identified by '123456';
mysql> flush privileges;

3.2完全热备。

使用innobakupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。

innobackupex --user=bkuser --password=123456 --socket=/tmp/mysql.sock4 --defaults-file=/data/3309/my.cnf /backup/xtrabackup/ #相关选项自行help。
#nnobackupex: completed OK! #出现此选项代表备份完成。
ls /backup/xtrabackup/
2015-10-01_17-00-13

(1)xtrabackup_checkpoints —— 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息;

每个InnoDB页(通常为16k大小)都会包含一个日志序列号,即LSN。LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件;

(5)backup-my.cnf —— 备份命令用到的配置选项信息;

3.3提供改变数据。

mysql> insert into test.test values (3,'yun zhonghe3');
Query OK, 1 row affected (0.03 sec)

3.4增量备份

innobackupex --user=bkuser --password=123456 --socket=/tmp/mysql.sock4 --defaults-file=/data/3309/my.cnf --incremental /backup/xtrabackup/ --incremental-basedir=/backup/xtrabackup/2015-10-01_17-00-13/

3.5 模拟数据损坏

rm -rf /data/3309/
killall mysqld

3.6增量备份恢复

innobackupex --apply-log --redo-only /backup/xtrabackup/2015-10-01_17-00-13/
innobackupex --apply-log --redo-only /backup/xtrabackup/2015-10-01_17-00-13/ --incremental-dir=/backup/xtrabackup/2015-10-01_17-15-00/

#注意,多实例的话,仍然需要备份配置文件,启动的时候需要binlog目录。

mkdir -p /data/3309/{data,binlog}
chown mysql.mysql -R /data/
cp /opt/backup/my.cnf /data/3309/
innobackupex --copy-back /backup/xtrabackup/2015-10-01_17-00-13/ --defaults-file=/opt/backup/my.cnf   #恢复数据。

3.6启动数据库查看。

mysqld_safe --defaults-file=/data/3309/my.cnf &
mysql -S /tmp/mysql.sock4
mysql> select * from test.test;   #数据修复完成。
+------+--------------+
| id   | comment      |
+------+--------------+
|    1 | yun zhonghe  |
|    2 | yun zhonghe2 |
|    3 | yun zhonghe3 |
+------+--------------+
3 rows in set (0.01 sec)

小结:

xtrabacup具有如下特点。

(1)备份过程快速、可靠;

(2)备份过程不会打断正在执行的事务;

(3)能够基于压缩等功能节约磁盘空间和流量;

(4)自动实现备份检验;

(5)还原速度快;

因此建议学会熟练使用xtrabackup进行备份和还原

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Linux安装 Mysql

    背景 前不久开始学习部署ambari,在安装mysql的时候遇到了点问题,现在从网上找了点部署流程记录下来便于以后安装mysql。 Centos 7.2 安装...

    用户1217611
  • 参数化(一):计划缓存

      简介   很多时候,当我执行查询调优的时候,引发查询性能糟糕的问题一般都是与参数化相关的。一方面,参数化是查询处理器核心的基本主题。它能显著影响查询性能。另...

    用户1217611
  • 数据库压缩备份提高备份效率

    背景     在数据库的备份过程中有很多参数,前几日发现公司的备份数据库job运行的很慢,就去研究了一下,发现在备份程序中都没有启用压缩,加上压缩以后有发现效率...

    用户1217611
  • 使用XtraBackup备份MySQL 8.0 Part 8 xtrabackup 命令总结

    大体上差不多,不过8.0版本移除了innobackupex命令且只能备份8.0版本的MySQL

    bsbforever
  • 使用XtraBackup备份MySQL 8.0 Part 4 对数据库进行全备

    大体上差不多,不过8.0版本移除了innobackupex命令且只能备份8.0版本的MySQL

    bsbforever
  • HDU 1728 逃离迷宫(DFS经典题,比赛手残写废题)

    逃离迷宫 Time Limit: 1000/1000 MS (Java/Others)    Memory Limit: 32768/32768 K (Java...

    Angel_Kitty
  • 别找了,这是 Pandas 最详细教程了

    Python 是开源的,它很棒,但是也无法避免开源的一些固有问题:很多包都在做(或者在尝试做)同样的事情。如果你是 Python 新手,那么你很难知道某个特定任...

    昱良
  • 想成为高效数据科学家?不会Pandas怎么行

    Python 是开源的,它很棒,但是也无法避免开源的一些固有问题:很多包都在做(或者在尝试做)同样的事情。如果你是 Python 新手,那么你很难知道某个特定任...

    CDA数据分析师
  • iOS 无弹框换图标,直接用就可以了

            对于iOS,是开发了API可以换图标,但是有规定的,就是你要换的图标是要在APP里面内置的,也就是说你要换的APPicon,是提前放在APP包里...

    菜菜不吃蔡
  • iOS 无弹框换图标,直接用就可以了

            对于iOS,是开发了API可以换图标,但是有规定的,就是你要换的图标是要在APP里面内置的,也就是说你要换的APPicon,是提前放在APP 包...

    菜菜不吃蔡

扫码关注云+社区

领取腾讯云代金券