mysql数据库四

备份和恢复

1 .  备份注意要点 

能容忍最多丢失多少数据 

恢复数据需要在多长时间内完成 

需要恢复哪些数据 

2 .  还原要点 

做还原测试，用于测试备份的可用性 

还原演练

3 .备份类型

完全备份作为主要备份定时如每周备份一次，将数据全部备份，增量备份和差异备份以及部分备份作为辅助备份，每天备份更改后的数据，来辅助完全备份在需要的时候还原数据。

完全备份+部分备份

完全备份+增量备份

完全备份+差异备份

 

完全备份：整个数据库的数据以及数据库的自定义的参数 全部都备份

部分备份：只备份数据子集，如部分库或表

增量备份：仅备份最近一次完全备份后的一天或者自定义的时常，到下一个时间段再次备份此时间点到上一次增量备份之间的变化的内容，以后一次类推直到再次到达完全备份的时间段。备份较快，还原复杂

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单（如星期3.则备份星期3到最近一次完全备份的变化的数据）

4 . 备份的方式：

冷备：读写操作均不可进行  （只有在设备停机时才使用）

温备：读操作可执行；但写操作不可执行 

热备：读写操作均可执行

MyISAM：温备，不支持热备

InnoDB: 都支持

物理和逻辑备份 ；

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间， 速度快 

逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关， 占用空间少，速度慢，可能丢失精度

5 . 备份时需要考虑的因素

温备的持锁多久 ；（温备时写操作被限制，如果时间过长影响用户的使用）

备份产生的负载  （备份时会造成设备负载过大，备份前查看机器的负载状况以免发生宕机）

备份过程的时长 ：（如果长时间做一次备份造成数据过大，会使备份时间过长）

恢复过程的时长   （同上，所以要考虑备份的时常，既多久备份一次）

6 .设计备份方案 

数据集：完全+增量 

备份手段：物理，逻辑

7 . 备份工具

mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备 份；对InnoDB存储引擎支持热备    （较为常用的备份方式）

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备（在设备停止服务时）； 完全和部分备份

mysqlhotcopy：几乎冷备；仅适用于MyISAM存储引擎

逻辑备份工具：mysqldump, mydumper, phpMyAdmin

mysqldump工具：客户端命令，通过mysql协议连接至mysqld服务器进行 备份

8 . mysqldump常见选项

-A，  –all-databases 备份所有数据库，含create database （数据库的创建）

-B , –databases db_name… 指定备份的数据库，包括create database语句（表的格式信息的创建）

-E, –events：备份相关的所有event scheduler 

-R, –routines：备份所有存储过程和存储函数 

–triggers：备份表相关的触发器，默认启用,用–skip-triggers，不备份触发器 

–master-data[=#]： 此选项须启用二进制日志

1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#， 默认为1 （此为主从备份时会用到）

2：记录为注释的CHANGE MASTER TO语句 此选项会自动关闭–lock-tables功能，自动打开–lock-all-tables功能（除非开启-single-transaction）

–compact 去掉注释，适合调试，生产不使用

-d, –no-data 只备份表结构

-t, –no-create-info 只备份数据,不备份create table

-n,–no-create-db 不备份create database，可被-A或-B覆盖

–flush-privileges 备份mysql或相关时需要使用

-f, –force 忽略SQL错误，继续执行

–hex-blob 使用十六进制符号转储二进制列（例如，“abc”变为0x616263），受影响的数据类 型包括BINARY， VARBINARY，BLOB，BIT

-q, –quick 不缓存查询，直接输出，加快备份速度

-F, –flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文 件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用-flush-logs和-x，–master-data或-single-transaction,此时只刷新一次 建议：和-x，–master-data或 –single-transaction一起使用 

（使用-F 备份时会将原有的日志锁定不在记录，生成新的二进制日志文件来记录新的二进制日志）

InnoDB建议备份策略 mysqldump –uroot –A –F –E –R –single-transaction –master-data=1 -flush-privileges –triggers –hex-blob >xxxx.sql 

MyISAM建议备份策略 mysqldump –uroot –A –F –E –R –x –master-data=1 –flush-privileges -triggers –hex-blob >xxxxxx.sql
具体备份还原实例：

1 . 冷备份：

首先将服务停了：systemctl stop mariadb

tar Jcvf /data/all.tar.xz /var/lib/mysql/ (用打包压缩的方式将数据库目录下的文件全部拷走)

rm -rf /var/lib/mysql/* （将数据库的文件全部都删掉）

tar xvf /data/all.tar.xz -C /var/lib/mysql/（将备份的文件解压缩到原来的目录下就实现了数据的还原）

cd /var/lib/mysql/（进入到原来的数据库文件夹下发现以一个文件夹的方式解压到此了）

mv /var/lib/mysql/var/lib/mysql/* /var/lib/mysql/（将文件移动到/var/lib/mysql/文件夹下就实现了还原）

重新启动服务查看数据：systemctl restart mariadb;

 

2 . 使用mysqldump来进行备份和还原；（以下的mysqldump命令都省略了mysqldump -uroot  -p密码）

使用-B对单个表进行备份和还原操作：

mysqldump -B mysql > /data/mysql.sql  （使用—B 备份表的结构，即使删除此表也能还原，只对单个表进行备份）

rm -rf mysql    （将mysql表删除然后实现还原）

mysql < /data/mysql.sql  (不需要进入到数据库命令行直接执行此命令就还原数据了)

 

使用-A对整个数据库进行备份和还原：

mysqldump -A > /data/all.sql   （全部备份数据库到/data/目录下）

rm -rf /var/lib/mysql/* （模拟删除数据库的所有数据）

systemctl restart mariadb  (如果mysql命令无法使用就需要重启服务才可以)

mysql < /data/all.sql     （还原全部数据；不要再数据库里进行操作，再Linux界面就可以）

 

连备份带压缩：

mysqldump -A | gzip > /data/all.sql.gz   （将数据库的全部文件备份带压缩）

rm -rf /var/lib/mysql/*   （再次模拟删除数据库的所有数据）

gzip -d /data/all.sql.gz  .（解压数据库文件压缩包到此地）

systemctl restart mariadb  （重启服务）

mysql < /data/all.sql（还原数据库的所有数据,不进入mysql数据库）

 

分库备份：（将数据库里的每个库分开备份，用一条指令实现）

1 .用for循环来实现

for i in `mysql -e ‘show databases’ | grep -iEv ‘database|schema‘`;do mysqldump -B $i | gzip > /data/$i.sql.gz;done

（database和schema代表从show databases;里排除的选项；schema为infor和perform…..）

2 . 用一条sed命令来实现：
直接生成脚本：

mysql -e ‘show databases’ | grep -ivE ‘database|schema’ | sed -r ‘s/(.*)/mysqldump -B \1 |gzip >/data/\1.sql.gz/‘> /data/back.sh

（mysqldump -B \1 |gzip >/data/\1.sql.gz 此为sed里整个要替换的内容）

backup.sh的脚本生成后加上#！机制走一遍脚本即可。

 

 

实验：数据库数据文件损坏，如何还原最新状态？

思路：使用全备份文件和增量文件来恢复数据库

1 . 首先开启两个主机二进制日志；然后将数据全备份：

mysqldump -A -F –single-transaction –master-data=2 > /backup/fullbak_`date +%F`.sql

现在模拟对数据库进行修改以便后面测试

2  . 将备份的全数据和二进制文件复制到另一台机器上

scp /backup/fullbak_2018-06-14.sql 192.168.60.20:/（复制全备份文件）

查看二进制文件到底要复制那些：（vim /backup/fullbak_2018-06-14.sql ）

(‘mariadb-bin.000002’, MASTER_LOG_POS=245; 此处的意思是全备份前的二进制日志，需要复制其以后的二进制文件，上图中的文件已经备份到全备份里了，他后面的文件为更改的增量文件)

scp /var/lib/mysql/mariadb-bin.000002 192.168.60.20:/   (将二进制文件复制到另一台主机上)

复制完后将自己本机的数据库文件模拟破坏：（rm -rf /var/lib/mysql/*）

3 . 在另一台主机上进行还原

rm -rf /var/lib/mysql/* （删除本机的数据库文件，使其成为干净的系统）

systemctl restart mariadb   （重启服务）

4 . 在本机的配置文件上添加：/etc/my.cnf

skip-networking (暂时将网络关上防止其他用户登陆或者修改数据库的文件)

5 . systemctl restart mariadb  （再次重启服务）

6 . mysql < fullbak_2018-06-14.sql  （恢复刚才传过来的全备份文件）

7 .mysqlbinlog –start-position=245 mariadb-bin.000002 >bin.sql (将二进制文件打开并导入到一个新文件里；其中–start-position=245是完全备份后修改的增量二进制文件)

8 . mysql < bin.sql  （将刚才的增量文件再次导入到数据库中就完成了）

9 . 将刚才在配置文件里加的防止其他用户访问的条件取消掉。

（以上操作都是在linux界面上的命令）

 

 

实验：误删除数据库的一张表，如何还原最新状态

思路：将本机的数据库全部删除（前提是二进制文件与库文件不在同一个磁盘上而且有全备份文件和二进制文件），然后将本机还原到全备份时的状态，在打开二进制文件将删除表的命令找到并删掉，之后将此增量文件再次还原到本机上。

具体步骤：

1 . mysqldump -A -F –single-transaction –master-data=2 > /backup/full.sql  （备份本机的全备份文件）

2 . 10：00前修改数据
insert students
3 10:00 删除表
drop table students;

4    10:00-10:10 修改数据库
insert teachers;

 还原操作

5 .  flush tables with read lock;  （进入数据库，对所有表加上只读锁，其他用户只能读不能修改）

6 .查看全备份文件查看全备份时二进制文件处于什么状态：vim /backup/full.sql

上图中的方框里的就是全备份时的二进制日志的位置

再查看此时二进制的状态：在数据库中输入：show master logs;

上图中最后一行的766就是全备份时255到766加上只读锁时所做的增量。

mysqlbinlog –start-position=245 /var/lib/mysql/mariadb-bin.000003 > /backup/bin.sql  （将全备份到加只读锁时间段的增量二进制文件导出来）如果有多个二进制日志文件，需要依次追加到文件里。

7 . vim bin.sql （修改导出的二进制文件将删表的命令找到并删掉）

8 . rm -rf /var/lib/mysql/*(将数据库的所有文件都删除)

9 .  systemctl restart mariadb  （重启数据库服务）

10 .  mysql < /backup/full.sql  （导入全备份文件）

11 . mysql < /backup/bin.sql   （将修改后的增量二进制文件导入数据库中）

12 . 将刚才添加的只读锁解掉：unlock tables (在数据库里执行)

 

 

 

3 .使用xtrabackup 来进行备份还原  （官网：www.percona.com ）

1 简介和参数设置；

percona提供的mysql数据库备份工具，惟一开源的能够对innodb和 xtradb数据库进行热备的工具

特点： 

(1)备份还原过程快速、可靠 

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

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

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

(5)开源，免费

xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表

xtrabackup安装：

yum install percona-xtrabackup      （下载安装包后还需要开启EPEL源，有个依赖包依赖与epel源）

最新版本下载安装： https://www.percona.com/downloads/XtraBackup/LATEST/

选项说明

–user：该选项表示备份账号  –password：该选项表示备份的密码  –host：该选项表示备份数据库的地址  –databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如： “xtra_test dba_test”，同时，在指定某数据库时，也可以只指定其中的某张表。如： “mydatabase.mytable”。该选项对innodb引擎表无效，还是会备份所有innodb表  –defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置  –incremental：该选项表示创建一个增量备份，需要指定–incremental-basedir  –incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base 目录，与–incremental同时使用  –incremental-dir：该选项表示增量备份的目录  –include=name：指定表名，格式：databasename.tablename

–apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数 据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此 时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的 事务至数据文件使数据文件处于一致性状态  –use-memory：该选项表示和–apply-log选项一起使用，prepare 备份的时候， xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)， 推荐1G  –defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选 项的位置  –export：表示开启可导出单独的表之后再导入其他Mysql中  –redo-only：这个选项在prepare base full backup，往其中merge增量备份时候使 用

还原选项及说明

还原注意事项：  1.datadir目录必须为空。除非指定innobackupex –force-non-empty-directorires选项指 定，否则–copy-backup选项不会覆盖  2.在restore之前,必须shutdown MySQL实例，你不能将一个运行中的实例restore到 datadir目录中  3.由于文件属性会被保留，大部分情况下你需要在启动实例之前将文件的属主改为 mysql，这些文件将属于创建备份的用户 chown -R mysql:mysql /data/mysql 以上需要在用户调用innobackupex之前完成 –force-non-empty-directories：指定该参数时候，使得innobackupex –copy-back或-move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果–copy-back和-move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败

 

具体实验：使用xtrabackup 来进行全备份及还原：（/var/lib/mysql/数据库的默认路径）

在一台主机上作完全备份：

1 . 安装包  ：yum install percona-xtrabackup  （需要配置epel源进行安装；两台主机都需要安装）

2 . mkdir /backup/    (新建一个空文件夹)

3 . innobackupex –-user=root /backups （开始备份）

4 . scp -r /backup/2018-02-23_11-55-57/ 目标主机:/data/ （将备份的数据复制到另一台主机上）

在另一台主机上还原：

5  .首先将数据库服务停用

6 . rm -rf /var/lib/mysql/*  （将自己的数据库目录清空）

7 .  innobackupex –apply-log /data/2018-06-13_22-37-24/ （整理复制过来的全备份文件）

8 . innobackupex –copy-back /data/2018-06-13_22-37-24/  （将整理好的备份文件复制到自己的数据库下）

9 . ll /var/lib/mysql/（查看自己数据库下的文件是否有，查看权限）

10 .chown -R  mysql: /var/lib/mysql/（将数据库文件的所有者和所属组更改为mysql所有）

11 . systemctl start mariadb   （最后重新启动服务查看数据库的文件是否还原了）

小结：注意各主机的selinux，getenforce以及防火墙；iptables -F ;以上操作都在linux界面，不是咋数据库中。在两个主机的配置文件里都要添加：log_bin(启用二进制日志功能)；临时开启：set log_bing=on

show variables like ‘log_bin’;（查看二进制日志此时的状态是否开启）

 

实验 ：使用Xtrabackup完全，增量备份及还原

前提实验环境：selinux  iptables  开启二进制日志记录功能

1 .innobackupex /backup  （完全备份数据）

2 . mkdir /backup/inc{1,2}  （新建两个增量备份的文件夹）

对数据库进行增删改的操作，以便后面增量备份看出效果

3 . innobackupex –incremental /backup/inc1 –incremental-basedir=/backups/2018-02-23_14-21-42

（在全备份的基础上进行增量备份）

–incrementalbasedir=/backups/2018-02-23_14-21-42 （全备份文件的路径）

/backups/inc1 （第一次增量备份的路径）

再次修改数据以便在第二次增量备份时看出效果

4 . innobackupex –incremental /backup/inc2 –incremental-basedir=/backup/inc1/2018-06-13_23-34-00  （在第一次增量备份的基础上进行再次增量备份）

–incremental-basedir=/backup/inc1/2018-06-13_23-34-00（第一次增量备份的路径）

5 . scp -r /backup/* 192.168.27.17:/data/ （将所有的备份数据复制到另一台机器上）

在目标恢复的主机上进行还原操作：

6 . 不启动mariadb

7 . rm -rf /var/lib/mysql/* （删除此数据库里的所有内容）

8 . innobackupex –apply-log –redo-only /data/2018-06-13_23-15-05/（整理完全备份的数据）

9 .innobackupex –apply-log –redo-only /data/2018-06-13_23-15-05/ –-incremental-dir=/data/inc1/2018-06-13_23-34-00/  （基于完全备份对第一次的增量备份进行整理）

10 . innobackupex –apply-log –redo-only /data/2018-06-13_23-15-05/ –incremental-dir=/data/inc2/2018-06-13_23-43-09/ （基于完全备份对第二次的增量备份进行整理）

11 .innobackupex –copy-back /data/2018-06-13_23-15-05/（将整理好的数据库的文件复制到本机的数据库下）

12 . chown -R mysql: /var/lib/mysql/（修改复制过来的数据库文件的所有者）

13 . systemctl start mariadb（启动数据库服务）

 

 

实验：  使用Xtrabackup进行单表导出和导入 (mariadb10.2以上的版本支持)

二进制日志要开启；

1  . innobackupex –include=’hellodb.students’ /backup/（将数据库中的一张表内容备份出来）

2 .  mysql -e ‘show create table hellodb.students’ > /backup/student.sql  （将上述表的结构导出来）

3 . mysql -e ‘drop table hellodb.students’      (模拟删除此表)

4 . innobackupex –apply-log –export /backup/2018-06-14_02-04-17/ （将备份表的内容进行整理）

5 . mysql hellodb </backup/ student.sql （将刚才导出的表结构重新导进去）

CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum(‘F’,’M’) NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

6 . mysql -e ‘alter table hellodb.students discard tablespace’  （将原有表的数据删除）

7 . cp  /backup/2018-06-14_02-04-17/hellodb/students.cfg students.exp students.ibd /var/lib/mysql/hellodb/   (将备份的表的内容三个文件复制到数据库的路径下)

8 .  chown -R mysql.mysql /var/lib/mysql/hellodb/ （将刚才复制过去的文件更改所有者，和所属组）

9 . mysql -e ‘alter table hellodb.students import tablespace’  (将复制过去的数据导入到数据库中)

小结：log_bin；innodb_file_per_table  （让每个表的文件都独立分开）在配置文件里添加/etc/my.cnf

 

 

 

主从配置过程： 参看：https://mariadb.com/kb/en/library/setting-up-replication/ https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

实验：建立新的主从复制

在主服务器上：

1  .安装mariadb软件：yum install mariadb-server  ；启动服务：systemctl start mariadb

2 .修改主服务器的配置文件：

vim /etc/my.cnf

[mysqld]

log_bin                    （开启二进制日志的记录）

server_id=1            （为当前主服务器设置一个全局惟的ID号 ）

systemctl restart mariadb （重启服务）

3  .  创建有复制权限的从用户的账号 ：

grant replication slave on *.* to lv@192.168.60.20 identified by ‘centos’;  (授权从主机复制的权限)

从服务器上的实现

4 .  编辑配置文件

vim /etc/my.cnf

[mysqld]

server_id=2

systemctl restart mariadb    （重启服务）

5   进入数据库：>create user ‘lv’@’192.168.60.%’ identified by ‘centos’;（本地要新建一个同步主数据的账号）

CHANGE MASTER TO MASTER_HOST=’192.168.60.4′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=245;

MASTER_HOST=192.168.60.4 （主服务器的IP地址）
MASTER_USER=lv （授权从服务器的用户名）
MASTER_PASSWORD=’centos’（授权从服务器的密码）
MASTER_LOG_FILE=’mariadb-bin.000001’（主服务器的二进制文件名）
MASTER_LOG_POS=245;（从主服务器二进制文件的那个节点开始同步）
最后两项可以在主服务器上查询：show master logs;

6  show slave status\G;（查看从服务器的状态）

7 . start slave;（最后一步开启复制）

到此完成了，将主服务器的文件同步到从服务器上了，主服务器更改，从服务器也可以即使同步了，如果从服务器重启了，主服务器此时更新内容，待到重服务器起来之后，数据会自动同步的。

 

实验：已有旧的主，建立新从

在主节点实现
1 修改配置文件
vim /etc/my.cnf
server_id=1
log_bin
innodb_file_per_table

systemctl restart mariadb  （重启服务）

2  mysql>grant replication slave on *.* to lv@’192.168.30.20′ identified by ‘centos’;

3  mysqldump -A -F –single-transaction –master-data=1 > /backup/all.sql   (将主服务器备份)
scp /backup/all.sql  192.168.60.20：/backup  （将备份的数据传送到从服务器上）

在从节点实现

4  vim /etc/my.cnf
server_id=2
systemctl restart mariadb

5 vim /backup/all.sql
CHANGE MASTER TO MASTER_HOST=’192.168.30.17′,
MASTER_USER=’repluser’,MASTER_PASSWORD=’centos’,MASTER_PORT=3306,
MASTER_LOG_FILE=’master-bin.000002′, MASTER_LOG_POS=245;

…..

(MASTER_LOG_FILE=’master-bin.000002′, MASTER_LOG_POS=245此位置为 全备份时的主服务器所在的节点位置  ；在主服务器上用show master logs 可以查看现在的状态值，为增量二进制日志文件；我们要取的是全备份的位置点到此时的位置点之间的数据，所以不要改变全备份时留下的最后位置，让他同步)

6 mysql < /backup/all.sql   （还原数据库文件）

7 mysql > start slave          （开启自动同步服务）  stop slave :暂停同步状态。
mysql> show slave status\G     （查看同步状态）