day5-备份

数据备份

数据备份方式

物理备份——直接拷贝库或对应的文件

可以直接CP文件

只支持myisam储存引擎的表

yum –y install perl-DBD-MySQL perl-DBI

mysqlhotcopy –u 用户名 –p 密码数据库名 [目录/备份文件名]

数据库名_copy

使用物理备份文件恢复数据

直接把备份文件拷贝回数据库目录下就可以了

文件的所有者/组，必须是mysql

逻辑备份——备份产生当前数据的sql语句

数据库服务软件自带的逻辑备份工具或安装第三方软件，提供逻辑备份工具

热备份（在线备份）

冷备份（不在线备份）

数据备份策略

完全备份：备份所有数据

一台数据库服务器上的所有数据

备份数据库服务器上某个库的所有数据

备份数据库服务器上某个库里某个表的所有数据

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

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

完全备份+差异备份

完全备份+增量备份

备份策略存储设备备份数据的时间备份文件名备份周期

数据完整备份mysqldump——默认输出到屏幕上

mysqldump –u用户名 -p密码数据库名>xxx.sql

数据库名的表示方式

--all-databases 备份数据库服务器上的所有数据

数据库名备份数据库服务器上某个库的所有数据

数据库名表名备份数据库服务器上某个库里某个表的所有数据

-B 数据库名1 数据库名2 备份数据库服务器上某几个库的所有数据

数据恢复

使用备份文件恢复数据

mysql –u用户名 –p密码数据库名〈xxx.sql

当备份文件里有create database SQL语句时,恢复时不用指定数据库名

实验

完整备份所有的数据库所有记录

[root@localhost ~]# mysqldump -uroot -p --all-databases > /bk/bk.sql

Enter password:

[root@localhost bk]# grep -vE '^/|^-|^$' /bk/bk.sql | head -15

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `db1`;

DROP TABLE IF EXISTS `tb1`;

CREATE TABLE `tb1` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `tb1` WRITE;

INSERT INTO `tb1` VALUES (100),(100),(100);

UNLOCK TABLES;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;

CREATE TABLE IF NOT EXISTS `general_log` (

`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`user_host` mediumtext NOT NULL,

`thread_id` int(11) NOT NULL,

注意：若数据库都使用MyISAM存储引擎，可以采用冷备份的方式，直接复制对应的数据库目录即可；恢复时重新复制回来就行。

备份指定的某一个库

[root@localhost bk]# mysqldump -uroot -p db1 >/bk/db1.sql

[root@localhost bk]# grep -vE '^/|^-|^$' /bk/db1.sql | head -15

DROP TABLE IF EXISTS `tb1`;

CREATE TABLE `tb1` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `tb1` WRITE;

INSERT INTO `tb1` VALUES (100),(100),(100);

UNLOCK TABLES;

同时备份指定的多个库

[root@localhost bk]# mysqldump -uroot -p -B db1 db2 >/bk/db1+db2.bk

[root@localhost bk]# grep -vE '^/|^-|^$' /bk/db1+db2.sql

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `db1`;

DROP TABLE IF EXISTS `tb1`;

CREATE TABLE `tb1` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `tb1` WRITE;

INSERT INTO `tb1` VALUES (100),(100),(100);

UNLOCK TABLES;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `db2`;

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `t1` WRITE;

INSERT INTO `t1` VALUES (10),(10),(10);

UNLOCK TABLES;

备份数据库里某个表

[root@localhost bk]# mysqldump -uroot -p db1 tb1 tb2 > /bk/db1_tb1+2.sql

[root@localhost bk]# grep -vE '^/|^-|^$' /bk/db1_tb1+2.sql

DROP TABLE IF EXISTS `tb1`;

CREATE TABLE `tb1` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `tb1` WRITE;

INSERT INTO `tb1` VALUES (100),(100),(100);

UNLOCK TABLES;

DROP TABLE IF EXISTS `tb2`;

CREATE TABLE `tb2` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `tb2` WRITE;

INSERT INTO `tb2` VALUES (2),(2),(2);

UNLOCK TABLES;

恢复数据库表

通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库

mysql> create database tb3;

[root@localhost bk]# mysql -uroot -p db3 < /bk/db1_tb1+2.sql

恢复所有数据库

[root@localhost bk]# mysql -uroot -p </bk/bk.sql

库和表都恢复完成

安装图形管理工具phpMyAdmin——使用PHP语言编写的，使用浏览器打开，必须运行HTTP服务

可以安装在服务器端、也可以安装在客户端

安装包

yum –y install php httpd php-*

下载phpMyAdmin包 https://www.phpmyadmin.net/files/

解压安装放到HTTPD服务目录下，将数据库设置一个密码

复制文件

[root@localhost mysql]# cp config.sample.inc.php config.inc.php

17行就字符串、31行是主机名或IP

完全备份的缺点

1、使用完全备份文件恢复数据时，只能把数据恢复到备份时状态，完全备份后新产生的数据无法恢复

2、不能够对数据时时备份

3、使用完全备份文件恢复数据时锁表

增量备份

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

binlog日志——是mysql数据库服务日志中的一种，又称二进制日志，记录的是用户登录mysql数据库服务器后，执行的所有的使数据发生改变的sql语句

修改主配置文件启用binlog日志

vim /etc/my.cnf

[mysqld]

log-bin

以下自定义

log-bin=文件名默认名为:主机名-bin.000001

log-bin=/路径/文件名默认存储位置/var/lib/mysql/

max-binlog-size=指定的大小默认为500M

查看binlog日志文件内容

mysqlbinlog日志文件名

当数据发生改变，sql语句会添加到日志文件当中

binlog日志记录sql语句方式

字符偏移量（pos） #at 值

起始字符偏移量 --start-position=数字

起始字符偏移量 –stop-postion=数字

时间点（time） #年月日时间

起始时间 --start-datetime="yyyy-mm-dd hh:mm:ss"

线束时间 --stop-datetime="yyyy-mm-dd hh:mm:ss"

执行binlog日志里的sql语句恢复数据

mysqlbinlog 选项文件名 |mysql -u用户名 –p密码 [数据库]

使用binlog日志恢复库和表记录

手动生成新的binlog日志文件方式

重启mysql数据库服务
mysql>flush logs;
mysql -u用户名 –p密码 –e "flush logs"
mysqldump -u用户名 –p密码 –-flush-logs 数据库名 >xxx.sql

删除所有binlog日志,重建新日志RESET MASTER;

当启用binlog日志时,会生成两个一文件

当数据库发生数据改变时,会记录命令

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

[root@localhost mysql]# cat mysqld-bin.000001

@db1create database db1

使用binlog恢复数据

[root@localhost mysql]# mysqlbinlog mysqld-bin.000003
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160907  9:34:26 server id 1  end_log_pos 106     Start: binlog v 4, server v 5.1.66-log created 160907  9:34:26 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
om7PVw8BAAAAZgAAAGoAAAABAAQANS4xLjY2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACibs9XEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#160907  9:35:05 server id 1  end_log_pos 187     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1473212105/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database db5
/*!*/;
# at 187
#160907  9:35:18 server id 1  end_log_pos 272     Query    thread_id=2    exec_time=0    error_code=0
use db5/*!*/;
SET TIMESTAMP=1473212118/*!*/;
create table t1(id int)
/*!*/;
# at 272　　　　　　　　//字符偏移量　　272-359
#160907  9:35:32 server id 1  end_log_pos 359     Query    thread_id=2    exec_time=0    error_code=0　　　　//时间#yymmdd hh:mm:ss
SET TIMESTAMP=1473212132/*!*/;　　　　//回车
insert into t1 values (1)
/*!*/;
# at 359
#160907  9:35:35 server id 1  end_log_pos 446     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1473212135/*!*/;
insert into t1 values (2)
/*!*/;
# at 446
#160907  9:35:37 server id 1  end_log_pos 533     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1473212137/*!*/;
insert into t1 values (3)
/*!*/;
# at 533
#160907  9:36:15 server id 1  end_log_pos 614     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1473212175/*!*/;
delete  from db5.t1
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

恢复T1表的数据

使用xtrabackup对数据做增量备份第三方软件percona-xtrabackup

1在线备份备份时不锁表

xtrabackup:C程序,支持InnoDB/XtraDB存储引擎的表

innoackupex:以Perl脚本封装xtrabackup,还支持MyISAM存储引擎的表

xtrabackup 增量备份增量恢复

必须先有一次完整备份,因为只有完整备份后,在备份时,才知道哪些数据是新产生的.备份数据时只备份表中的数据,不备份表结构

--backup 备份数据

--datadir=数据库目录指定数据库目录

--target-dir=目录名指定备份文件存储的位置

--incremental-basedir=目录名增量备份时,指定上一次备份文件存储(参照的完整备份)目录

--incrementtal-dir=目录名准备恢复数据时,指定增量备份文件存储的目录

--prepare 准备恢复数据

2使用xtrabackup恢复数据

xtrabackup --perpare –datadir=/var/lib/mysql –target-dir=/xxx

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

下载xtrabackup包　　https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/

可以使用yum来安装,解决依赖关系[root@localhost ~]# yum localinstall percona-xtrabackup-2.2.10-1.el6.x86_64.rpm

选项 --backup表示备份，--datadir指定MySQL的数据库存放目录，--target-dir指定目标文件夹

mysql 5.6以后的版本初始密码在/root/.mysql_secret

完整备份

xtrabackup_56 --backup --datadir=/var/lib/mysql/ --target-dir=/bk

增量备份

xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/bk1/ --incremental-basedir=/bk

[root@localhost ~]# du -sh /bk /bk1

13M /bk
1.1M /bk1

这种情况下，官方建议连做两次--prepare，以确保数据一致性

完整恢复

xtrabackup --prepare --target-dir=/bk

完整+增量恢复

xtrabackup --prepare --target-dir=/bk --apply-log-only --incremental-dir=/bk1