Mysql备份系列(4)--lvm-snapshot备份mysql数据(全量+增量)操作记录

Mysql最常用的三种备份工具分别是mysqldump、Xtrabackup(innobackupex工具)、lvm-snapshot快照。 前面分别介绍了: Mysql备份系列(1)--备份方案总结性梳理 Mysql备份系列(2)--mysqldump备份(全量+增量)方案操作记录 Mysql备份系列(3)--innobackupex备份mysql大数据(全量+增量)操作记录

lvm-snapshot:基于LVM快照的备份 1.关于快照: 1)事务日志跟数据文件必须在同一个卷上; 2)刚刚创立的快照卷,里面没有任何数据,所有数据均来源于原卷 3)一旦原卷数据发生修改,修改的数据将复制到快照卷中,此时访问数据一部分来自于快照卷,一部分来自于原卷 4)当快照使用过程中,如果修改的数据量大于快照卷容量,则会导致快照卷崩溃。 5)快照卷本身不是备份,只是提供一个时间一致性的访问目录。

2.基于快照备份几乎为热备: 1)创建快照卷之前,要请求MySQL的全局锁;在快照创建完成之后释放锁; 2)如果是Inoodb引擎, 当flush tables 后会有一部分保存在事务日志中,却不在文件中。 因此恢复时候,需要事务日志和数据文件 但释放锁以后,事务日志的内容会同步数据文件中,因此备份内容并不绝对是锁释放时刻的内容,由于有些为完成的事务已经完成,但在备份数据中因为没完成而回滚。 因此需要借助二进制日志往后走一段

3.基于快照备份注意事项: 1)事务日志跟数据文件必须在同一个卷上; 2)创建快照卷之前,要请求MySQL的全局锁;在快照创建完成之后释放锁; 3)请求全局锁完成之后,做一次日志滚动;做二进制日志文件及位置标记(手动进行);

4.为什么基于MySQL快照的备份很好? 原因如下几点: 1)几乎是热备 在大多数情况下,可以在应用程序仍在运行的时候执行备份。无需关机,只需设置为只读或者类似只读的限制。 2)支持所有基于本地磁盘的存储引擎 它支持MyISAM, Innodb, BDB,还支持 Solid, PrimeXT 和 Falcon。 3)快速备份 只需拷贝二进制格式的文件,在速度方面无以匹敌。 4)低开销 只是文件拷贝,因此对服务器的开销很细微。 5)容易保持完整性 想要压缩备份文件吗?把它们备份到磁带上,FTP或者网络备份软件 -- 十分简单,因为只需要拷贝文件即可。 6)快速恢复 恢复的时间和标准的MySQL崩溃恢复或数据拷贝回去那么快,甚至可能更快,将来会更快。 7)免费 无需额外的商业软件,只需Innodb热备工具来执行备份。

快照备份mysql的缺点: 1)需要兼容快照 -- 这是明显的。 2)需要超级用户(root) 在某些组织,DBA和系统管理员来自不同部门不同的人,因此权限各不一样。 3)停工时间无法预计,这个方法通常指热备,但是谁也无法预料到底是不是热备 -- FLUSH TABLES WITH READ LOCK 可能会需要执行很长时间才能完成。 4)多卷上的数据问题 如果你把日志放在独立的设备上或者你的数据库分布在多个卷上,这就比较麻烦了,因为无法得到全部数据库的一致性快照。不过有些系统可能能自动做到多卷快照。

下面即是使用lvm-snapshot快照方式备份mysql的操作记录,仅依据本人实验中使用而述.

操作记录: 如下环境,本机是在openstack上开的云主机,在openstack上创建一个30G的云硬盘挂载到本机,然后制作lvm逻辑卷。

一、准备LVM卷,并将mysql数据恢复(或者说迁移)到LVM卷上: 1) 创建一个分区或保存到另一块硬盘上面 2) 创建PV、VG、LVM 3) 格式化 LV0 4) 挂载LV到临时目录 5) 确认服务处于stop状态 6) 将数据迁移到LV0 7) 重新挂载LV0到mysql数据库的主目录/data/mysql/data 8) 审核权限并启动服务 [root@test-huanqiu ~]# fdisk -l ......... Disk /dev/vdc: 32.2 GB, 32212254720 bytes 16 heads, 63 sectors/track, 62415 cylinders Units = cylinders of 1008 * 512 = 516096 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x00000000

[root@test-huanqiu ~]# fdisk /dev/vdc                            //依次输入p->n->p->1->回车->回车->w ......... Command (m for help): p

Disk /dev/vdc: 32.2 GB, 32212254720 bytes 16 heads, 63 sectors/track, 62415 cylinders Units = cylinders of 1008 * 512 = 516096 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x343250e4

Device Boot Start End Blocks Id System

Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-62415, default 1): Using default value 1 Last cylinder, +cylinders or +size{K,M,G} (1-62415, default 62415): Using default value 62415

Command (m for help): w The partition table has been altered!

Calling ioctl() to re-read partition table. Syncing disks.

[root@test-huanqiu ~]# fdisk /dev/vdc

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to switch off the mode (command 'c') and change display units to sectors (command 'u').

Command (m for help): p

Disk /dev/vdc: 32.2 GB, 32212254720 bytes 16 heads, 63 sectors/track, 62415 cylinders Units = cylinders of 1008 * 512 = 516096 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0x343250e4

Device Boot Start End Blocks Id System /dev/vdc1 1 62415 31457128+ 5 Extended

Command (m for help):

[root@test-huanqiu ~]# pvcreate /dev/vdc1 Device /dev/vdc1 not found (or ignored by filtering). [root@test-huanqiu ~]# vgcreate vg0 /dev/vdc1 Volume group "vg0" successfully created [root@test-huanqiu ~]# lvcreate -L +3G -n lv0 vg0 Logical volume "lv0" created. [root@test-huanqiu ~]# mkfs.ext4 /dev/vg0/lv0 [root@test-huanqiu ~]# mkdir /var/lv0/ [root@test-huanqiu ~]# mount /dev/vg0/lv0 /var/lv0/ [root@test-huanqiu ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 8.1G 6.0G 1.7G 79% / tmpfs 1.9G 0 1.9G 0% /dev/shm /dev/vda1 190M 37M 143M 21% /boot /dev/mapper/vg0-lv0 2.9G 4.5M 2.8G 1% /var/lv0

[root@test-huanqiu ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert LogVol00 VolGroup00 -wi-ao---- 8.28g LogVol01 VolGroup00 -wi-ao---- 1.50g lv0 vg0 -wi-a----- 3.00g

---------------------------------------------------------------------------------------------------- 如果要想删除这个lvs,操作如下: [root@test-huanqiu ~]# umount /data/mysql/data/            //先卸载掉这个lvs的挂载关系 [root@test-huanqiu ~]# lvremove /dev/vg0/lv0 [root@test-huanqiu ~]# vgremove vg0 [root@test-huanqiu ~]# pvremove /dev/vdc1 [root@test-huanqiu ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert LogVol00 VolGroup00 -wi-ao---- 8.28g LogVol01 VolGroup00 -wi-ao---- 1.50g ----------------------------------------------------------------------------------------------------

mysql的数据目录是/data/mysql/data,密码是123456 [root@test-huanqiu ~]# ps -ef|grep mysql mysql 2066 1286 0 07:33 ? 00:00:06 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql/data/mysql-error.log --pid-file=/data/mysql/data/mysql.pid --socket=/usr/local/mysql/var/mysql.sock --port=3306 root 2523 2471 0 07:55 pts/1 00:00:00 grep mysql [root@test-huanqiu ~]# /etc/init.d/mysql stop Shutting down MySQL.... SUCCESS!

[root@test-huanqiu ~]# cd /data/mysql/data/ [root@test-huanqiu data]# tar -cf - . | tar xf - -C /var/lv0/

[root@test-huanqiu data]# umount /var/lv0/

[root@test-huanqiu data]# mount /dev/vg0/lv0 /data/mysql/data [root@test-huanqiu data]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 8.1G 6.0G 1.7G 79% / tmpfs 1.9G 0 1.9G 0% /dev/shm /dev/vda1 190M 37M 143M 21% /boot /dev/mapper/vg0-lv0 2.9G 164M 2.6G 6% /data/mysql/data

删除挂载后产生的lost+found目录 [root@test-huanqiu data]# rm -rf lost+found

[root@test-huanqiu data]# ll -d /data/mysql/data [root@test-huanqiu data]# ll -Z /data/mysql/data [root@test-huanqiu data]# ll -Zd /data/mysql/data

需要注意的是: 当SElinux功能开启情况下,mysql数据库重启会失败,所以必须执行下面命令,恢复SElinux安全上下文. [root@test-huanqiu data]# restorecon -R /data/mysql/data/ [root@test-huanqiu data]# /etc/init.d/mysql start Starting MySQL... SUCCESS!

二、备份: (生产环境下一般都是整个数据库备份) 1)锁表 2)查看position号并记录,便于后期恢复 3)创建snapshot快照 4)解表 5)挂载snapshot 6)拷贝snapshot数据,进行备份。备份整个数据库之前,要关闭mysql服务(保护ibdata1文件) 7)移除快照

设置此变量为1,让每个事件尽可能同步到二进制日志文件里,以消耗IO来尽可能确保数据一致性。 mysql> SET GLOBAL sync_binlog=1;

查看二进制日志和position,以备后续进行binlog日志恢复增量数据(记住这个position节点记录,对后面的增量数据备份很重要)  mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 1434 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

创建存放binlog日志的position节点记录的目录 所有的position节点记录都放在这同一个binlog.pos文件下(后面就使用>>符号追加到这个文件下) [root@test-huanqiu ~]# mkdir /backup/mysql/binlog [root@test-huanqiu ~]# mysql -p123456 -e "SHOW MASTER STATUS;" > /backup/mysql/binlog/binlog.pos [root@test-huanqiu snap1]# cat /backup/mysql/binlog/binlog.pos File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set mysql-bin.000004 1434

刷新日志,产生新的binlog日志,保证日志信息不会再写入到上面的mysql-bin.000004日志内。 mysql> FLUSH LOGS;

全局读锁,读锁请求到后不要关闭此mysql交互界面 mysql> FLUSH TABLES WITH READ LOCK;

在innodb表中,即使是请求到了读锁,但InnoDB在后台依然可能会有事务在进行读写操作, 可用"mysql> SHOW ENGINE INNODB STATUS;"查看后台进程的状态,等没有写请求后再做备份。

创建快照,以只读的方式(--permission r)创建一个3GB大小的快照卷snap1 -s:相当于--snapshot [root@test-huanqiu ~]# mkdir /var/snap1 [root@test-huanqiu ~]# lvcreate -s -L 2G -n snap1 /dev/vg0/lv0 --permission r Logical volume "snap1" created.

查看快照卷的详情(快照卷也是LV): [root@test-huanqiu ~]# lvdisplay

解除锁定 回到锁定表的mysql交互式界面,解锁: mysql> UNLOCK TABLES;

此参数可以根据服务器磁盘IO的负载来调整 mysql> SET GLOBAL sync_binlog=0;

[root@test-huanqiu ~]# mount /dev/vg0/snap1 /var/snap1                //挂载快照卷 [root@test-huanqiu snap1]# df -h Filesystem             Size   Used  Avail  Use%  Mounted on /dev/mapper/VolGroup00-LogVol00                             8.1G  5.8G  1.9G  76%    / tmpfs                    1.9G  0       1.9G  0%      /dev/shm /dev/vda1              190M 37M   143M 21%    /boot /dev/mapper/vg0-lv0 2.9G 115M 2.7G 5% /data/mysql/data /dev/mapper/vg0-snap1                                2.9G 115M 2.7G 5% /var/snap1

[root@test-huanqiu ~]# cd /var/snap1/ && ll /var/snap1 [root@test-huanqiu snap1]# mkdir -p /backup/mysql/data/               //创建备份目录 total 0

对本机的数据库进行备份,备份整个数据库。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database beijing;
Query OK, 1 row affected (0.00 sec)

mysql> use beijing;
Database changed

mysql> create table people(id int(5),name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into people values("1","wangshibo");
Query OK, 1 row affected (0.00 sec)

mysql> insert into people values("2","guohuihui");
Query OK, 1 row affected (0.01 sec)

mysql> insert into people values("3","wuxiang");
Query OK, 1 row affected (0.01 sec)

mysql> select * from people;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | wangshibo |
|    2 | guohuihui |
|    3 | wuxiang   |
+------+-----------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| beijing            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

-------------------------------------------------------------------------------------------------------------------------- 需要注意的是: innodb表,一般会打开独立表空间模式(innodb_file_per_table)。 由于InnoDB默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1文件。 增删数据库的时候,ibdata1文件不会自动收缩,这对单个或部分数据库的备份也将成为问题(如果不是整个数据库备份的情况下,ibdata1文件就不能备份,否则会影响全部数据库的数据)。 所以若是对单个数据库或部分数据库进行快照备份: 1)若是直接误删除mysql数据目录下备份库目录,可以直接将快照备份数据解压就能恢复 2)若是使用drop或delete误删除的数据,那么在使用快照备份数据恢复时,就会出问题!因为单库备份时ibdata1文件不能单独备份,恢复时会导致这个文件损坏!

所以正确的做法是: 要对整个数据库进行备份,并且一定要在mysql服务关闭的情况下(这样是为了保护ibdata1文件)。 因为mysql是采用缓冲方式来将数据写入到ibdata1文件中的,这正是fflush()函数存在的理由。当mysql在运行时,对ibdata1进行拷贝肯定会导致ibdata1文件中的数据出错,这样在数据恢复时,也就肯定会出现“ERROR 1146 (42S02): Table '****' doesn't exist“的报错!

在对启用innodb引擎的mysql数据库进行迁移的时候也是同理: 在对innodb数据库进行数据迁移的时候,即将msyql(innodb引擎)服务从一台服务器迁移到另一台服务器时,在对数据库目录进行整体拷贝的时候(当然就包括了对ibdata1文件拷贝),一定要在关闭对方mysql服务的情况下进行拷贝!

ibdata1用来储存文件的数据,而库名的文件夹里面的那些表文件只是结构而已,由于新版的mysql默认试innodb,所以ibdata1文件默认就存在了,少了这个文件有的数据表就会出错。要知道:数据库目录下的.frm文件是数据库中很多的表的结构描述文件;而ibdata1文件才是数据库的真实数据存放文件。

-------------------------------------------innodb_file_per_table参数说明------------------------------------------ 线上环境的话,一般都建议打开这个独立表空间模式。 因为ibdata1文件会不断的增大,不会减少,无法向OS回收空间,容易导致线上出现过大的共享表空间文件,致使当前空间爆满。 并且ibdata1文件大到一定程序会影响insert、update的速度;并且 另外如果删表频繁的话,共享表空间产生的碎片会比较多。打开独立表空间,方便进行innodb表的碎片整理

使用MyISAM表引擎的数据库会分别创建三个文件:表结构、表索引、表数据空间。 可以将某个数据库目录直接迁移到其他数据库也可以正常工作。

然而当使用InnoDB的时候,一切都变了。 InnoDB默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1文件。 增删数据库的时候,ibdata1文件不会自动收缩,单个数据库的备份也将成为问题。 通常只能将数据使用mysqldump 导出,然后再导入解决这个问题。

在MySQL的配置文件[mysqld]部分,增加innodb_file_per_table参数。 可以修改InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。

它的优点: 1)每个表都有自已独立的表空间。 2)每个表的数据和索引都会存在自已的表空间中。 3)可以实现单表在不同的数据库中移动。 4)空间可以回收(除drop table操作处,表空不能自已回收)

Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

它的缺点: 单表增加过大,如超过100个G。

结论: 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整一下:innodb_open_files。 InnoDB Hot Backup(冷备)的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实。

1)innodb_file_per_table设置.设置为1,表示打开了独立的表空间模式。 如果设置为0,表示关闭独立表空间模式,开启方法如下: 在my.cnf中[mysqld]下设置 innodb_file_per_table=1

2)查看是否开启: mysql> show variables like "%per_table%"; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec)

3)关闭独享表空间 innodb_file_per_table=0关闭独立的表空间 mysql> show variables like ‘%per_table%’; -------------------------------------------innodb_file_per_table参数说明------------------------------------------ --------------------------------------------------------------------------------------------------------------------------

备份前,一定要关闭mysql数据库!因为里面会涉及到ibdata1文件备份,不关闭mysql的话,ibdata1文件备份后会损坏,从而导致恢复数据失败! [root@test-huanqiu snap1]# /etc/init.d/mysql stop Shutting down MySQL.... SUCCESS! [root@test-huanqiu data]# lsof -i:3306 [root@test-huanqiu data]#

现在备份整个数据库 [root@test-huanqiu snap1]# tar -zvcf /backup/mysql/data/`date +%Y-%m-%d`dbbackup.tar.gz ./ [root@test-huanqiu snap1]# ll /backup/mysql/data/ total 384 -rw-r--r--. 1 root root 392328 Dec 5 22:15 2016-12-05dbbackup.tar.gz

释放快照卷,每次备份之后,应该删除快照,减少IO操作 先卸载,再删除 [root@test-huanqiu ~]# umount /var/snap1/ [root@test-huanqiu ~]# df -h                //确认上面的挂载关系已经没了 Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 8.1G 5.8G 1.9G 76% / tmpfs 1.9G 0 1.9G 0% /dev/shm /dev/vda1 190M 37M 143M 21% /boot /dev/mapper/vg0-lv0 2.9G 115M 2.7G 5% /data/mysql/data [root@test-huanqiu ~]# lvremove /dev/vg0/snap1 Do you really want to remove active logical volume snap1? [y/n]: y Logical volume "snap1" successfully removed

数据被快照备份后,可以启动数据库 [root@test-huanqiu ~]# /etc/init.d/mysql start Starting MySQL.. SUCCESS! [root@test-huanqiu ~]# lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 15943 mysql 16u IPv4 93348 0t0 TCP *:mysql (LISTEN) [root@test-huanqiu ~]#

现在再进行新的数据写入:

mysql> use beijing;
Database changed
mysql> insert into people values("4","liumengnan");
Query OK, 1 row affected (0.02 sec)

mysql> insert into people values("5","zhangjuanjuan");
Query OK, 1 row affected (0.00 sec)

mysql> select * from people;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | wangshibo     |
|    2 | guohuihui     |
|    3 | wuxiang       |
|    4 | liumengnan    |
|    5 | zhangjuanjuan |
+------+---------------+
5 rows in set (0.00 sec)

mysql> create table heihei(name varchar(20),age varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into heihei values("jiujiujiu","nan");
Query OK, 1 row affected (0.00 sec)

mysql> select * from heihei;
+-----------+------+
| name      | age  |
+-----------+------+
| jiujiujiu | nan  |
+-----------+------+
1 row in set (0.00 sec)

mysql> create database shanghai;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| beijing            |
| mysql              |
| performance_schema |
| shanghai           |
| test               |
+--------------------+
6 rows in set (0.00 sec)

假设一不小心误操作删除beijing和shanghai库

mysql> drop database beijing;
Query OK, 2 rows affected (0.03 sec)

mysql> drop database shanghai;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

莫慌!接下来就说下数据恢复操作~~ 三、恢复流程如下: 0)由于涉及到增量数据备份,所以提前将最近一次的binlog日志从mysql数据目录复制到别的路径下 1)在mysql数据库中执行flush logs命令,产生新的binlog日志,让日志信息写入到新的这个binlog日志中 1)关闭数据库,一定要关闭 2)删除数据目录下的文件 3)快照数据拷贝回来,position节点记录回放 4)增量数据就利用mysqlbinlog命令将上面提前拷贝的binlog日志文件导出为sql文件,并剔除其中的drop语句,然后进行恢复。 5)重启数据

先将最新一次的binlog日志备份到别处,用作增量数据备份。 比如mysql-bin.000006是最新一次的binlog日志 [root@test-huanqiu data]# cp mysql-bin.000006 /backup/mysql/data/

产生新的binlog日志,确保日志写入到这个新的binlog日志内,而不再写入到上面备份的binlog日志里。 mysql> flush logs;

[root@test-huanqiu data]# ll mysql-bin.000007 -rw-rw----. 1 mysql mysql 120 Dec 5 23:19 mysql-bin.000007

[root@test-huanqiu data]# /etc/init.d/mysql stop Shutting down MySQL.... SUCCESS! [root@test-huanqiu data]# lsof -i:3306 [root@test-huanqiu data]# pwd /data/mysql/data [root@test-huanqiu data]# rm -rf ./* [root@test-huanqiu data]# tar -zvxf /backup/mysql/data/2016-12-05dbbackup.tar.gz ./

[root@test-huanqiu data]# /etc/init.d/mysql start Starting MySQL SUCCESS! [root@test-huanqiu data]# cat /backup/mysql/binlog/binlog.pos File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set mysql-bin.000004 1434 [root@test-huanqiu data]# mysqlbinlog --start-position=1434 /data/mysql/data/mysql-bin.000004 | mysql -p123456

登陆数据库查看,发现这只是恢复到快照备份阶段的数据:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| beijing            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from beijing.people;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | wangshibo |
|    2 | guohuihui |
|    3 | wuxiang   |
+------+-----------+
3 rows in set (0.00 sec)

mysql> 

快照备份之后写入的数据要利用mysqlbinlog命令将上面拷贝的mysql-bin000006文件导出为sql文件,并剔除其中的drop语句,然后进行恢复。 [root@test-huanqiu ~]# cd /backup/mysql/data/ [root@test-huanqiu data]# ll total 388 -rw-r--r--. 1 root root 392328 Dec 5 22:15 2016-12-05dbbackup.tar.gz -rw-r-----. 1 root root 1274 Dec 5 23:19 mysql-bin.000006 [root@test-huanqiu data]# mysqlbinlog mysql-bin.000006 >000006bin.sql

剔除其中的drop语句 [root@test-huanqiu data]# vim 000006bin.sql          //手动删除sql语句中的drop语句

然后在mysql中使用source命令恢复数据 mysql> source /backup/mysql/data/000006bin.sql;

再次查看下,发现增量部分的数据也已经恢复回来了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| beijing            |
| mysql              |
| performance_schema |
| shanghai           |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use beijing;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_beijing |
+-------------------+
| heihei            |
| people            |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from people;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | wangshibo     |
|    2 | guohuihui     |
|    3 | wuxiang       |
|    4 | liumengnan    |
|    5 | zhangjuanjuan |
+------+---------------+
5 rows in set (0.00 sec)

mysql> select * from heihei;
+-----------+------+
| name      | age  |
+-----------+------+
| jiujiujiu | nan  |
+-----------+------+
1 row in set (0.00 sec)

----------------------------------------------------------------------------------------------------------------- 思路: 1)全库的快照备份只需要在开始时备份一份即可,这相当于全量备份。 2)后续只需要每天备份一次最新的binlog日志(备份后立即flush logs产生新的binlog日志),这相当于增量备份了。 3)利用快照备份恢复全量数据,利用备份的binlog日志进行增量数据恢复 4)crontab计划任务,每天定时备份最近一次的binlog日志即可。 ----------------------------------------------------------------------------------------------------------------

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(61)-如何使用框架来开发

前言 这篇文本讲述了这个框架的使用方式,及一些疑问的答疑,更加精准的使用这个框架来建立功能 经过几个版本的迭代,系统使用更加方便,代码更加简洁也更加的智能,...

33060
来自专栏数据和云

Oracle 12.2新特性掌上手册 - 第一卷 Availability

注:文章内容来自官方文档翻译。若需要了解更多,请查阅官方文档。 1、Multi-Instance Redo Apply (多实例redo应用) 在Oracle ...

39860
来自专栏运维技术迷

从Mysql备份中恢复单个表

因为云平台的备份是把库中所有的表都打包成一个 .sql文件,然而这一个.sql文件大约有20G,现阶段的方法是把.sql文件source到数据库数据处理机器上,...

425110
来自专栏乐沙弥的世界

数据泵IMPDP 导入工具的使用

数据的导入导出时数据库经常处理的作业之一,Oracle 提供了IMP和IMPDP以及SQL*Loader等工具来完成数据的导入工作,其中IMP服务于早期的9i...

11210
来自专栏云计算

如何在Debian 9上设置MariaDB

MariaDB是MySQL的直接替代品。它致力于成为寻求强大,可扩展且可靠的SQL Server的数据库专业人员的合理选择。本指南将帮助初学者在Debian 9...

1.3K30
来自专栏程序猿

mssql注入

一些sql扩展 xp_regaddmultistring xp_regdeletekey 删除键名 xp_regdeletevalue 删除键值 xp_...

44680
来自专栏数据和云

高频错误:ORA-01555深入剖析

黄玮(Fuyuncat) 资深Oracle DBA,个人网站www.HelloDBA.com,致力于数据库底层技术的研究,其作品获得广大同行的高度评价. ORA...

33280
来自专栏Java学习123

忘记oracle的sys用户密码怎么修改

32150
来自专栏云计算教程系列

如何在Ubuntu系统上搭建MySQL数据库

MySQL原本是一个开放源代码的关系数据库管理系统,原开发者为瑞典的MySQL AB公司,2009年,甲骨文公司(Oracle)收购MySQL,成为Oracle...

24700
来自专栏小樱的经验随笔

sqlmap简单中文说明

首先下载需要的文件,如果是windows环境直接到http://sqlmap.org/下载安装所需要的文件即可。 更新 svn checkout https:...

33070

扫码关注云+社区

领取腾讯云代金券