一、mysql数据库备份的前因后果
1、备份的概念:
将数据收集并保存至另外的多个副本,其目的是将数据还原恢复至备份数据时那个状态。
2、备份数据的原因
1)做灾难恢复使用,要将数据副本做到异地多份备份;
2)数据库数据改动时使用备份;
3)需要对当前服务器做相关测试时使用备份,备份与测试都是最好在服务器访问量最少时进行。
3、备份的事先注意事项
1)可以容忍丢失多长时间的数据;
2)恢复数据能在多长时间内完成;
3)是否需要持续提供服务;
4)需要恢复哪些内容,整个服务器的数据库,单个数据库,一个或多个表。
二、数据的备份类型
1、根据是否需要数据库离线可分为
1)冷备:cold backup
备份需要关闭mysql服务或读写请求均不允许;
2)温备:warm backup
备份的同时,mysql服务在线,只允许读不允许写,在线交易要终止:
3)热备:hot backup
备份的同时,mysql服务在线,支持读写请求,业务不受影响,但服务器的性能会有所下降。
2、根据要备份的数据范围可分为
1)完全备份:full backup
备份当前状态的整个数据库的数据;
2)增量备份:increment backup
备份基于上次的完全备份或增量备份以来所改变的数据;
3)差异备份:different backup
备份基于上次的完全备份改变了的数据。
3、根据备份数据是否为文件可分为
1)物理备份:直接备份数据库文件
2)逻辑备份:备份表中的数据和库代码
三、备份的对象及备份工具
1、备份对象
1)数据
2)配置文件
3)代码、存储过程、存储函数、触发器等
4)OS相关的配置文件
5)复制相关的配置
6)二进制日志
2、引擎所支持的备份
MyISAM引擎只支持温备,而InnoDB还支持热备。
3、备份工具
1)mysqldump,mysql客户端的经典备份工具
为逻辑备份工具,备份和恢复比较慢;
2)mylvmdumper,mysqldump升级版
多线程的逻辑备份工具,备份和恢复速度稍快于mysqldump;
3)lvm-snapshot,基于快照卷的备份工具
接近于热备的物理备份工具,备份和恢复的速度较快;
4)select ,mysql内置的备份工具
逻辑备份工具,速度快于mysqldump;
select into outfile;
load data infile;
5)xtrabackup,由percana提供的免费开源备份工具
为物理备份工具,速度快。
6)mysql hotcopy:几乎冷备的工具,一般不采用,速度慢。
四、数据从备份到恢复的完整流程
1)停止mysql服务;
2)记录服务和配置文件权限;
3)复制备份文件与数据目录;
4)按需调整配置;
5)按需改变文件权限;
6)尝试启动服务;
7)装载逻辑备份;
8)检查和重放二进制日志;
9)确定数据还原正常完成;
10)以完全权限重启服务器。
五、使用mysqldump进行数据备份及恢复
1:为测试的数据库及二进制创建备份目录;
[root@node1 ~]# mkdir -pv {/mydata/data,/backup,/ var /binlog}
mkdir: created directory `/mydata/data'
mkdir: created directory `/backup'
mkdir: created directory `/ var /binlog'
2:启动mysqld服务,创建测试数据库,并创建测试数据
[root@node1 ~] # service mysqld restart
Shutting down MySQL.... [ OK ]
Starting MySQL.. [ OK ]
[root@node1 ~] #mysql
mysql> create database students;
Query OK, 1 row affected (0.00 sec)
mysql> use students
Database changed
mysql> create table TLtb (Id tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M' ,Courses char(30) not null);
Query OK, 0 rows affected (0.28 sec)
mysql> insert into TLtb (Name,Age,Gender,Courses) values ( 'Xu zu' ,20, 'M' , 'Xiao Wuxianggong' ),( 'Qiao Feng' ,28, 'M' , 'Xianglong Shibazhang' ),( 'Duan Fu' ,23, 'M' , 'Liumai Shenjian' );
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from TLtb;
+----+-----------+------+--------+----------------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+----------------------+
| 1 | Xu zu | 20 | M | Xiao Wuxianggong |
| 2 | Qiao Feng | 28 | M | Xianglong Shibazhang |
| 3 | Duan Fu | 23 | M | Liumai Shenjian |
+----+-----------+------+--------+----------------------+
3 rows in set (0.01 sec)
3:使用mysqldump对测试数据库进行备份,并且滚动二进制日志,记录日志位置;
[root@node1 ~]# mysqldump --lock-all-tables --flush-logs --master-data= 2 --databases students > /backup/students_`date +%F`.sq
[root@node1 ~]# cp / var /binlog/mysql-bin.
mysql-bin. 000001 mysql-bin. 000002 mysql-bin. 000003 mysql-bin. 000004 mysql-bin. 000005 mysql-bin. 000006 mysql-bin.index
4:备份二进制日志文件;
[root@node1 ~]# cp / var /binlog/mysql-bin. 00000 * /backup/
[root@node1 ~]# ls /backup/
mysql-bin. 000001 mysql-bin. 000003 mysql-bin. 000005 students_2013- 09 - 30 .sql
mysql-bin. 000002 mysql-bin. 000004 mysql-bin. 000006 students_.sql
5:新增数据库数据进行增量备份,查看当前日志位置;
mysql> create table CDtb (Id tinyint unsigned not null primary key auto_increment,Name char( 20 ) not null unique key,Age tinyint unsigned,Gender char( 1 ) default 'M' ,Courses char( 30 ) not null );
Query OK, 0 rows affected ( 0.14 sec)
mysql> insert into CDtb (Name,Age,Gender,Courses) values ( 'Yideng Dashi' , 80 , 'M' , 'Yiyangzhi' ),( 'Hong Qigong' , 66 , 'M' , 'Dagou Bangfa' ),( 'Huang Yaoshi' , 60 , 'M' , 'Tanzhi Shengong' );
Query OK, 3 rows affected ( 0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from CDtb;
+----+--------------+------+--------+-----------------+
| Id | Name | Age | Gender | Courses |
+----+--------------+------+--------+-----------------+
| 1 | Yideng Dashi | 80 | M | Yiyangzhi |
| 2 | Hong Qigong | 66 | M | Dagou Bangfa |
| 3 | Huang Yaoshi | 60 | M | Tanzhi Shengong |
+----+--------------+------+--------+-----------------+
3 rows in set ( 0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. 000006 | 716 | | |
+------------------+----------+--------------+------------------+
1 row in set ( 0.00 sec)
[root@node1 ~]# less /backup/students_2013- 09 - 30 .sql
-- CHANGE MASTER TO MASTER_LOG_FILE= 'mysql-bin.000006' , MASTER_LOG_POS= 107 ;
[root@node1 ~]# mysqlbinlog --start-position= 107 / var /binlog/mysql-bin. 000006 > /backup/students_incremental.sql
6:模拟数据库数据损坏;
mysql> insert into CDtb (Name,Age,Gender,Courses) values ( 'Ou Yangfeng' , 75 , 'M' , 'Hamagong' );
Query OK, 1 row affected ( 0.05 sec)
mysql> drop database students;
Query OK, 2 rows affected, 2 warnings ( 0.13 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. 000006 | 1082 | | |
+------------------+----------+--------------+------------------+
1 row in set ( 0.00 sec)
7:恢复备份数据:完全备份+增量备份+二进制日志文件;
bac
[root@node1 ~]# mysqlbinlog / var /binlog/mysql-bin. 000006
......
# at 993
# 130930 22 : 51 : 40 server id 1 end_log_pos 1082 Query thread_id= 2 exec_time= 0 error_code= 1146
SET TIMESTAMP= 1380552700 /*!*/ ;
drop database students
[root@node1 ~]# mysqlbinlog --start-position= 716 --stop-position= 993 / var /binlog/mysql-bin. 000006 > /backup/students_993.sql
[root@node1 ~]# mysqlbinlog / var /binlog/mysql-bin. 000006
......
# at 993
# 130930 22 : 51 : 40 server id 1 end_log_pos 1082 Query thread_id= 2 exec_time= 0 error_code= 1146
SET TIMESTAMP= 1380552700 /*!*/ ;
drop database students
[root@node1 ~]# mysqlbinlog --start-position= 716 --stop-position= 993 / var /binlog/mysql-bin. 000006 > /backup/students_993.sql
mysql> set global sql_log_bin= 0 ;
Query OK, 0 rows affected ( 0.00 sec)
mysql> source /backup/students_2013- 09 - 30 .sql
mysql> source /backup/students_incremental.sql
mysql> source /backup/students_993.sql
8:检测备份的数据是否已经正常恢复。
mysql> set global sql_log_bin= 1 ;
Query OK, 0 rows affected ( 0.00 sec)
mysql> use students;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| CDtb |
| TLtb |
+--------------------+
2 rows in set ( 0.00 sec)
mysql> select * from TLtb;
+----+-----------+------+--------+----------------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+----------------------+
| 1 | Xu zu | 20 | M | Xiao Wuxianggong |
| 2 | Qiao Feng | 28 | M | Xianglong Shibazhang |
| 3 | Duan Fu | 23 | M | Liumai Shenjian |
+----+-----------+------+--------+----------------------+
3 rows in set ( 0.00 sec)
mysql> select * from CDtb;
+----+--------------+------+--------+-----------------+
| Id | Name | Age | Gender | Courses |
+----+--------------+------+--------+-----------------+
| 1 | Yideng Dashi | 80 | M | Yiyangzhi |
| 2 | Hong Qigong | 66 | M | Dagou Bangfa |
| 3 | Huang Yaoshi | 60 | M | Tanzhi Shengong |
| 4 | Ou Yangfeng | 75 | M | Hamagong |
+----+--------------+------+--------+-----------------+
4 rows in set ( 0.00 sec)
六、使用lvm-snapshot进行数据备份及恢复
1、创建LVM逻辑卷并开机自动挂载,并创建mysql数据与二进制日志存放目录;
[root@localhost ~]# pvcreate /dev/sdb{ 1 , 2 }
Physical volume "/dev/sdb1" successfully created
Physical volume "/dev/sdb2" successfully created
[root@localhost ~]# vgcreate vg1 /dev/sdb{ 1 , 2 }
Volume group "vg1" successfully created
[root@localhost ~]# lvcreate -L +10G -n lv1 vg1
Logical volume "lv1" created
[root@localhost ~]# mke2fs -t ext4 /dev/vg1/lv1
[root@localhost ~]# vim /etc/fstab
/dev/vg1/lv1 /Mydata ext4 defaults 0 0
[root@localhost ~]# mount -a
[root@localhost ~]# mkdir /Mydata/{data,,binlog} /backup1
[root@localhost ~]# chown -R mysql:mysql /Mydata/*
2、新建mysql数据库并新增数据;
mysql> create database schooldb;
Query OK, 1 row affected ( 0.01 sec)
mysql> use schooldb
Database changed
mysql> create table studentstb (Id tinyint unsigned not null primary key auto_increment,Name char( 20 ) not null unique key,Age tinyint unsigned,Gender char( 1 ) default 'M' ,Courses char( 30 ) not null );
Query OK, 0 rows affected ( 0.38 sec)
mysql> insert into studentstb (Name,Age,Gender,Courses) values ( 'Zhang San' , 19 , 'M' , 'Shujujiegou' ),( 'Li Ling' , 18 , 'F' , 'Daxueyingyu' ),( 'Wang Wu' , 20 , 'M' , 'Dianluyuanli' );
Query OK, 3 rows affected ( 0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from studentstb;
+----+-----------+------+--------+--------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+--------------+
| 1 | Zhang San | 19 | M | Shujujiegou |
| 2 | Li Ling | 18 | F | Daxueyingyu |
| 3 | Wang Wu | 20 | M | Dianluyuanli |
+----+-----------+------+--------+--------------+
3 rows in set ( 0.03 sec)
3、登录mysql,对所有表加锁,并滚动日志,查看当前日志所在位置;
mysql> flush tables with read lock;
Query OK, 0 rows affected ( 0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected ( 0.04 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. 000015 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set ( 0.01 sec)
4、另外启动一个终端,为逻辑卷创建快照卷,做完快照释放施加的锁请求;
[root@localhost ~]# lvcreate -L 1G -s -p r -n lv1-snap /dev/vg1/lv1
Logical volume "lv1-snap" created
mysql> unlock tables;
Query OK, 0 rows affected ( 0.00 sec)
5、挂载快照卷,并备份数据,备份完成卸载快照卷并删除;
[root@localhost ~]# cp -rp /mnt/* /backup1
[root@localhost ~]# umount /mnt/
[root@localhost ~]# lvremove /dev/vg1/lv1-snap
Do you really want to remove active logical volume lv1-snap? [y/n]: y
Logical volume "lv1-snap" successfully removed
6、停止mysql服务,模拟数据库数据损坏,进行数据恢复;
[root@localhost ~]# service mysqld stop
Shutting down MySQL... [ OK ]
[root@localhost ~]# rm -rf /Mydata/*
[root@localhost ~]# cp -rp /backup1/* /Mydata/
[root@localhost ~]# service mysqld start
Starting MySQL.. [ OK ]
7、检测数据恢复是否正常恢复完成。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| schooldb |
| test |
| xiaozheng |
+--------------------+
7 rows in set ( 0.01 sec)
mysql> use schooldb
Database changed
mysql> show tables;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| studentstb |
+--------------------+
1 row in set ( 0.00 sec)
mysql> select * from studentstb;
+----+-----------+------+--------+--------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+--------------+
| 1 | Zhang San | 19 | M | Shujujiegou |
| 2 | Li Ling | 18 | F | Daxueyingyu |
| 3 | Wang Wu | 20 | M | Dianluyuanli |
+----+-----------+------+--------+--------------+
3 rows in set ( 0.00 sec)
七、使用xtrabackup进行数据备份恢复
1、下载并安装xtrabackup;
[root@localhost ~]# ls
anaconda-ks.cfg install.log percona-xtrabackup- 2.1 . 4 - 656 .rhel6.x86_64.rpm
hellodb.sql install.log.syslog
[root@localhost ~]# yum -y install percona-xtrabackup- 2.1 . 4 - 656 .rhel6.x86_64.rpm
2、创建有备份权限的数据库用户;
mysql> create user 'xtrabackup' @ 'localhost' identified by 'mypass' ;
Query OK, 0 rows affected ( 0.09 sec)
mysql> revoke all privileges,grant option from 'xtrabackup' @ 'localhost' ;
Query OK, 0 rows affected ( 0.00 sec)
mysql> grant reload,lock tables,replication client,event on *.* to 'xtrabackup' @ 'localhost' ;
Query OK, 0 rows affected ( 0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected ( 0.00 sec)
3、对数据库进行完全备份;
[root@localhost ~]# innobackupex --user=xtrabackup --password=mypass /backup2
InnoDB Backup Utility v1. 5.1 -xtrabackup; Copyright 2003 , 2009 Innobase Oy
and Percona Ireland Ltd 2009 - 2012 . All Rights Reserved.
........
innobackupex: Backup created in directory '/backup2/2013-09-08_21-06-19'
innobackupex: MySQL binlog position: filename 'mysql-bin.000017' , position 598
130908 21 : 06 : 24 innobackupex: Connection to database server closed
130908 21 : 06 : 24 innobackupex: completed OK!
4、关闭mysql服务并模拟数据损坏,并准备一个完全备份(prepare);
[root@localhost ~]# service mysqld stop
Shutting down MySQL... [ OK ]
[root@localhost ~]# rm -rf /mydata/data/*
[root@localhost backup2]# innobackupex --apply-log /backup2/ 2013 - 09 -08_21- 06 - 19 /
InnoDB Backup Utility v1. 5.1 -xtrabackup; Copyright 2003 , 2009 Innobase Oy
and Percona Ireland Ltd 2009 - 2012 . All Rights Reserved.
........
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130908 21 : 13 : 44 InnoDB: Starting shutdown...
130908 21 : 13 : 48 InnoDB: Shutdown completed; log sequence number 1626636
130908 21 : 13 : 48 innobackupex: completed OK!
5、从一个完全备份中恢复数据,并检测数据库数据是否正常恢复完成。
[root@localhost ~]# innobackupex --copy-back /backup2/ 2013 - 09 -08_21- 06 - 19 /
InnoDB Backup Utility v1. 5.1 -xtrabackup; Copyright 2003 , 2009 Innobase Oy
and Percona Ireland Ltd 2009 - 2012 . All Rights Reserved.
............
innobackupex: Copying '/backup2/2013-09-08_21-06-19/ib_logfile1' to '/mydata/data'
innobackupex: Finished copying back files.
130908 21 : 21 : 23 innobackupex: completed OK!
[root@localhost ~]#service mysqld start
[root@localhost ~]# innobackupex --copy-back /backup2/ 2013 - 09 -08_21- 06 - 19 /
InnoDB Backup Utility v1. 5.1 -xtrabackup; Copyright 2003 , 2009 Innobase Oy
and Percona Ireland Ltd 2009 - 2012 . All Rights Reserved.
............
innobackupex: Copying '/backup2/2013-09-08_21-06-19/ib_logfile1' to '/mydata/data'
innobackupex: Finished copying back files.
130908 21 : 21 : 23 innobackupex: completed OK!
[root@localhost ~]# service mysqld start
Starting MySQL.. [ OK ]
mysql> use schooldb
Database changed
mysql> show tables;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| studentstb |
+--------------------+
1 row in set ( 0.00 sec)
mysql> select * from studentstb;
+----+-----------+------+--------+--------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+--------------+
| 1 | Zhang San | 19 | M | Shujujiegou |
| 2 | Li Ling | 18 | F | Daxueyingyu |
| 3 | Wang Wu | 20 | M | Dianluyuanli |
+----+-----------+------+--------+--------------+
3 rows in set ( 0.00 sec)
--------------------------------------------------------------------
6、在测试数据库中新增数据,实现数据的增量备份;
7、进行第一次增量备份;
8、继续于测试数据库中新增数据,进行第二次增量备份;
9、关闭mysql服务并模拟数据库数据损坏,进行两次增量备份的恢复;
10、检测增量备份的数据恢复是否正常完成。