最棒实践,MySQl备份与回复

 

  • 日志
  • 备份和还原
  • MySQL Replication 复制

备份和出山小草(数据卡塔尔国:

1、mariadb的日志

询问日志:general_log
慢查询日志:log_slow_queries
张冠李戴日志:log_error, log_warnings
二进制日志:binlog
连通日志:relay_log
政工日志:innodb_log

1、查询日志
记录查询语句,日志存储位置:
文件:file
表:table (mysql.general_log)         
general_log={ON|OFF}查询日志开启方式
general_log_file=HOSTNAME.log 查询日志文件的名字
log_output={FILE|TABLE|NONE}查询日志的输出位置,如果这里为none,即使general_log为on也不会开启
查询日志一般是不会开启的
2、慢查询日志
慢查询:运行时间超出指定时长的查询;
long_query_time
存储位置:
文件:FILE
表:TABLE,mysql.slog_log  
log_slow_queries={ON|OFF}是否启动慢查询日志
slow_query_log={ON|OFF}慢查询日志是否启动以上两项都要开启才能启动慢查询日志
slow_query_log_file=hostname-slow.log慢查询日志存放的位置,一般存放在数据目录下的此文件
log_output={FILE|TABLE|NONE}
log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk出现哪种情况的才启动慢慢查询日志
log_slow_rate_limit慢查询日志的速率,一秒钟记录多少条慢查询日志
log_slow_verbosity慢查询日志记录的详细程度
3、错误日志
记录如下几类信息:
(1) mysqld启动和关闭过程中输出的信息; 
(2) mysqld运行中产生的错误信息; 
(3) event scheduler事件调度器运行时产生的信息;事件调度器相当于mysql的计划任务
(4) 主从复制架构中,从服务器复制线程启动时产生的日志;               
log_error=/var/log/mariadb/mariadb.log|OFF表示关闭错误日志
log_error=/var/log/mariadb/mariadb.log表示开启错误日志,不用加on
log_warnings={ON|OFF}是否记录警告信息到日志文件中,1表示记录,0表示不记录
错误日志一般是要开启的
4、二进制日志
用于记录引起数据改变或存在引起数据改变的潜在可能性的语句(STATEMENT)或改变后的结果(ROW),也可能是二者混合;
功用:用于"重放"日志文件中的事件来生成数据副本
binlog_format={STATEMENT|ROW|MIXED}二进制日志记录的格式
STATEMENT:语句;记录SQL语句
ROW:行;记录语句执行的结果,比如修改年龄大于50的所有人的工资,这样会记录所有符合修改条件的行,而不是记录执行的SQL语句
MIXED:混编;
show global variables like 'binlog_format'
查看二进制日志文件列表:
SHOW MASTER|BINARY LOGS;
查看当前正在使用的二进制日志文件:
SHOW MASTER STATUS;
查看二进制日志文件中的事件:
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
服务器变量:
log_bin=/PATH/TO/BIN_LOG_FILE启动二进制日志,只能写到配置文件中才能启动二进制日志,不能通过命令进行启动,是一个只读变量;
session.sql_log_bin={ON|OFF},运行时设置是否开启二进制日志
max_binlog_size=1073741824大概1g,超过1g就会滚动
sync_binlog={1|0}是否启用二进制日志同步功能,只要事物commit就触发二进制同步操作,将内存缓冲区的信息写到磁盘上的二进制文件中。
mysqlbinlog:客户端命令工具
--start-datetime=根据开始时间查看二进制日志,时间格式YYYY-MM-DD hh:mm:ss
--stop-datetime=根据结束时间
 -j, --start-position=#  根据开始位置
 --stop-position=#  根据结束位置
二进制日志事件格式:
# at 553
#160831  9:56:08 server id 1  end_log_pos 624   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1472608568/*!*/;
BEGIN
/*!*/;  
事件的起始位置:# at 553
事件发生的日期时间:#160831  9:56:08
事件发生的服务器id:server id 1
事件的结束位置:end_log_pos 624
事件的类型:Query
事件发生时所在服务器执行此事件的线程的ID: thread_id=2 
语句的时间戳与将其写入二进制日志文件中的时间差:exec_time=0
错误代码:error_code=0
设定事件发生时的时间戳:SET TIMESTAMP=1472608568/*!*/;
事件内容:BEGIN
中继日志:
主从复制时,从服务器将主服务器的二进制日志先记录到中继日志中,再从中继日志中调到内存进行重放,完成主从复制。
事务日志:
事务型存储引擎innodb用于保证事务特性的日志文件,如果服务器突然断电,开启msyql后会根据事物日志中记录的内容进行提交和回滚操作。
redo log 
undo log 

亲自过问:如何运营和查阅二进制日志

[root@centos7 app]#mkdir -pv /app/logs/   ---创建一个目录用于存放二进制日志文件,也可以不创建,直接存放在数据目录下
mkdir: created directory ‘/app/logs/’
[root@centos7 app]#ls
logs
[root@centos7 app]#chown -R mysql:mysql /app/logs
[root@centos7 app]#systemctl stop mariadb
[root@centos7 app]#vim /etc/my.cnf.d/server.cnf 
[server]
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
log_bin = /app/logs/bin-log   ---开启二进制日志,并指明日志文件的位置,如果直接写相对路径/bin-log,表示二进制日志保存在数据目录下
[root@centos7 app]#systemctl start mariadb
[root@centos7 logs]#ls  /app/logs  ----发现生成了两个文件,一个是二进制日志文件,一个是二进制文件的索引文件,正是因为有这个文件我们在执行show binary logs时候才能看到二进制文件的列表
bin-log.000001  bin-log.index
[root@centos7 logs]#!mysql
mysql -uroot -pcentos
MariaDB [(none)]> show binary logs;   ---查看二进制日志文件列表
MariaDB [(none)]> show master status;  ---查看正在使用的二进制日志文件
MariaDB [(none)]> flush logs;   ---让二进制日志文件滚动
服务器端查看二进制日志文件的事件
MariaDB [(none)]> show binlog events in 'bin-log.000001';  ---查看二进制日志文件中的事件
MariaDB [hidb]> insert into students values(5,'ouyangfeng',70,'F','hamogong');
MariaDB [hidb]> select * from students;
MariaDB [hidb]> show binlog events in 'bin-log.000002';
MariaDB [hidb]> show binlog events in 'bin-log.000002' from 313;   ---从哪个位置开始查看二进制日志文件的事件
MariaDB [hidb]> show binlog events in 'bin-log.000002' from 313 limit 1;  ---从哪个位置开始并只显示1行
客户端查看二进制日志文件的事件
[root@centos7 logs]#mysqlbinlog bin-log.000002
[root@centos7 logs]#mysqlbinlog -j 313 --stop-position 437 bin-log.000002   ---查看二进制文件从哪个位置开始从哪个位置结束
[root@centos7 logs]#mysqlbinlog --start-datetime="2017-11-10 21:04:14" bin-log.000002    ---查看二进制文件从哪个时间开始
运行时开启和关闭二进制日志
MariaDB [hidb]> select @@session.sql_log_bin;  ---查看二进制日志已经开启
 ----------------------- 
| @@session.sql_log_bin |
 ----------------------- 
|                     1 |
 ----------------------- 
1 row in set (0.00 sec)

MariaDB [hidb]> set @@session.sql_log_bin=off;  ---关闭二进制日志
Query OK, 0 rows affected (0.00 sec)
MariaDB [hidb]> show session variables like "sql_log_bin";  ---也可用这种方式查看二进制文件是否关闭
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| sql_log_bin   | OFF   |
 --------------- ------- 

文本组的中央知识点介绍完结后,依据气象引进中的内容,大家将选用SQL Server文件组本领来得以完结冷热数据隔绝备份的方案设计介绍如下。

一、日志:

  • 日记的归类:
    询问日志:general_log
    慢查询日志:log_slow_queries
    怪诞日志:log_error, log_warnings
    二进制日志:binlog
    连接日志:relay_log
    事情日志:innodb_log
备份:
    存储的数据副本;
    原始数据:持续改变;
恢复:
    把副本应用到线上系统;
    仅能恢复至备份操作时刻的数据状态;

时间点恢复:
    binary logs; 

2、mysql的备份和苏醒

1、 备份类型:

  • 备份的数据集的范围:
    完全备份和部分备份
    全盘备份:整个数据集;
    风度翩翩对备份:数据集的生龙活虎部分,例如一些数据库也许部分表;
  • 全量备份、增量备份、差别备份:
    全量备份:完全备份
    增量备份:仅备份自上三回完全备份或增量备份以来变量的那部数据;
    差异备份:仅备份自上三遍完全备份以来变量的那部数据;
![](https://upload-images.jianshu.io/upload_images/6854348-921c5720fdfe6362.png)

无标题.png



可以根据生产需要每月进行一次全量备份,每周或者每天进行一次增量或者差异备份
  • 概略备份、逻辑备份:
    大要备份:用cp大概tar命令复制数据文件进行的备份,物理备份的快慢快
    逻辑备份:利用客户端工具通过mysql左券连接到数据库,并从数据库导出数据另存在三个或多个文件中;
  • 基于数据服务是还是不是在线:
    热备:读写操作均可开展的景况下所做的备份;
    温备:可读但不足写情况下进展的备份;
    冷备:读写操作均不足实行的意况下所做的备份;
  • 备份计谋:
    全量 差异 binlog
    全量 增量 binlog
    备份花招:物理、逻辑
    貌似情状下行使物理热备
  • 备份工具:
    ①mysqldump:mysql服务自带的备份工具;逻辑备份工具;
    完全、部分备份;
    InnoDB:热备;
    MyISAM:温备;
    cp/tar
    ②lvm2:逻辑卷,快速照相(乞请叁个大局锁卡塔 尔(阿拉伯语:قطر‎,之后马上释放锁,达到差不多热备的成效;物理备份;
    注意:不可能仅备份数据文件;要同一时候备份职业日志;
    前提:需要数据文件和作业日志位于同一个逻辑卷;
    ③xtrabackup:
    由Percona提供,开源工具,扶植对InnoDB做热备,物理备份工具;
    完全备份、部分备份;
    全盘备份、增量备份;
    一心备份、差距备份;
  • 备份机制:
    xtrabackup:
    全量 差异 binlog
    全量 增量 binlog
    mysqldump:数据非常的小的时候利用
    全量 binlog
  • 备份什么?
    数据
    二进制日志、InnoDB的业务日志;
    代码(存款和储蓄进程、存款和储蓄函数、触发器、事件调治器卡塔尔国
    服务器的布局文件
  • mysqldump的用法
    逻辑备份工具:基于mysql客商端公约
    一心备份、部分备份;
    InnoDB:热备或温备;
    MyISAM:温备;
    三次封装工具:
    mydumper
    phpMyAdmin
    Usage:
    mysqldump [OPTIONS] database [tables] # 备份单库,能够只备份在那之中的黄金年代局地表(部分备份卡塔尔国;
    OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] # 备份多库;
    OR mysqldump [OPTIONS] --all-databases [OPTIONS] # 备份全体库;
    MyISAM存款和储蓄引擎:扶植温备,备份时要锁定表;
    -x, --lock-all-tables:锁定全部库的装有表,读锁;
    -l, --lock-tables:锁定钦赐库全数表;
    InnoDB存款和储蓄引擎:帮助温备和热备;
    --single-transaction:制造二个事务,基于此快速照相推行备份,约等于张开三个大的事物,在REPEATABLE-READ的东西接离等第下,外人更动了提交了你也看不到,此刻备份时得以确定保障各事物的小时点同样,不然备份的历程中旁人只怕在改动数据,就可以产生各事物的更改时间不是在相同的时候,使备份不可用;
    任何选项:
    -科雷傲, --routines:备份钦定库的存款和储蓄进程和存储函数;
    --triggers:备份钦点库的触发器;
    -E, --events:备份事件调解器
    --master-data[=#]:指明备份时二进制日志文件的名字和所处的职位,备份之后的数目就足以从二进制日志文件的这里地方实行重播
    1:记录为CHANGE MASTE凯雷德 TO语句,此语句不被批注;
    2:记录为CHANGE MASTE奥迪R8 TO语句,此语句被疏解;
    --flush-logs:备份的还要扩充滚动二进制日志;
  • Xtrabackup的用法:
    MyISAM:温备,不支持增量备份;
    InnoDB:热备,增量;
    大意备份,速率快、可信赖;备份达成后自行校验备份结果集是或不是可用;还原速度快;
    Usage: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
    备份 --> 应用日志 --> 还原
    运用日志:--apply-log
    还原:--copy-back
    一同备份:
    完全 binlog(总结):
    备份:innobackupex --user --password= --host= /PATH/TO/BACKUP_DIR
    准备:innobackupex --apply-log /PATH/TO/BACKUP_DIR
    恢复:innobackupex --copy-back
    注意:--copy-back供给在mysqld主机本地开展,mysqld服务不能够开发银行;
    innodb_log_file_size也许要再度设定;
    示范1:使用mysqldump对innodb引擎的数据库举行全量 binlog热备
实验环境:172.18.21.107做为正常运行的mysql服务器,172.18.21.7做为备份的主机,把172.18.21.107的mysql数据备份到本机
1、在172.18.21.107上开启二进制日志并启动mariadb服务
vim /etc/my.cnf.d/server.cnf 
log_bin = /app/logs/bin-log ---开启二进制日志
systemcatl start mariadb
2、在172.18.21.7上进行一次全量备份
[root@redhat7 ~]#mysqldump -uroot -pcentos -h172.18.21.107 --single-transaction -R --triggers -E --master-data=2 --flush-logs --databases hidb > /app/hidb-`date " %F-%T"`.sql
[root@redhat7 ~]#cd /app
[root@redhat7 app]#ls
hidb-2017-11-11-08:42:11.sql  
[root@redhat7 app]#less hidb-2017-11-11-08:42:11.sql ---可以看到备份时二进制日志的名字和所处位置
3、在172.18.21.107上插入一个字段、删除一个字段,这些变化都是在全量备份之后进行的数据修改
MariaDB [hidb]> select * from  students;
 ---- ------------ ------ -------- ------------------- 
| id | name       | age  | gender | major             |
 ---- ------------ ------ -------- ------------------- 
|  1 | yangguo    |   25 | F      | anranxiaohuizhang |
|  2 | xiaolongnv |   30 | M      | yunvjian          |
|  3 | guojing    |   50 | F      | xianglong         |
|  4 | huangrong  |   45 | M      | dagoubang         |
|  5 | ouyangfeng |   70 | F      | hamogong          |
 ---- ------------ ------ -------- ------------------- 
MariaDB [hidb]> insert into students 
MariaDB [hidb]> delete from students where id=5;
Query OK, 1 row affected (0.00 sec)

MariaDB [hidb]> select * from  students;
 ---- ------------ ------ -------- ------------------- 
| id | name       | age  | gender | major             |
 ---- ------------ ------ -------- ------------------- 
|  1 | yangguo    |   25 | F      | anranxiaohuizhang |
|  2 | xiaolongnv |   30 | M      | yunvjian          |
|  3 | guojing    |   50 | F      | xianglong         |
|  4 | huangrong  |   45 | M      | dagoubang         |
|  6 | aobama     |   58 | F      | presedent         |
 ---- ------------ ------ -------- ------------------- /
4、将全量备份之后发生的数据改变的二进制日志重定向到一个文件中
[root@centos7 logs]#mysqlbinlog -j 245 bin-log.000005 > /app/binlog.sql
[root@centos7 logs]#scp /app/binlog.sql 172.18.21.7:/app
5、在172.18.21.7上进行恢复操作
systemctl start mariadb   ---注意恢复时不要开启二进制日志,不然会将恢复过程中执行的大量SQL语句记录到二进制日志中
[root@redhat7 app]#mysql < hidb-2017-11-11-08:42:11.sql   ---读取全量备份的内容
[root@redhat7 app]#mysql < binlog.sql    ---读取全量备份之后二进制日志中的内容
[root@redhat7 app]#mysql
MariaDB [(none)]> show databases;  ---可以看到备份的数据库
MariaDBMariaDB [hidb]> select * from students;  ---发现和172.18.21.107上的一样,备份恢复成功
 ---- ------------ ------ -------- ------------------- 
| id | name       | age  | gender | major             |
 ---- ------------ ------ -------- ------------------- 
|  1 | yangguo    |   25 | F      | anranxiaohuizhang |
|  2 | xiaolongnv |   30 | M      | yunvjian          |
|  3 | guojing    |   50 | F      | xianglong         |
|  4 | huangrong  |   45 | M      | dagoubang         |
|  6 | aobama     |   58 | F      | presedent         |
 ---- ------------ ------ -------- ------------------- 
 [(none)]> use hidb;

在percona官网上下载xtrabackup及备份举个例子

https://www.percona.com/
[root@centos7 app]#yum install  percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm
[root@centos7 app]#rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex  ---innobackupex工具是为了兼容oracle官方innobackup,将xtrabackup进行二次封装,此工具支持客户端远程连接进行备份,所以一般使用这个工具进行备份
/usr/bin/xtrabackup
[root@centos7 app]#mkdir /app/backups  ---在本机创建一个备份的目录
[root@centos7 app]#innobackupex --user=root --password=centos --host=172.18.21.107 /app/backups  ---将整个数据库备份到/app/backups目录下,也可以用--databases 指明只备份某个数据库
[root@centos7 app]#cd backups/
[root@centos7 backups]#ls  ---发现有一个和时间相关的备份目录
2017-11-11_09-50-40
[root@centos7 backups]#cd 2017-11-11_09-50-40
[root@centos7 2017-11-11_09-50-40]#ll
total 18460
-rw-r----- 1 root root      417 Nov 11 09:50 backup-my.cnf   ---配置文件的备份
drwxr-x--- 2 root root       60 Nov 11 09:50 hidb
-rw-r----- 1 root root 18874368 Nov 11 09:50 ibdata1  ---表空间文件备份
drwxr-x--- 2 root root     4096 Nov 11 09:50 mysql
drwxr-x--- 2 root root     4096 Nov 11 09:50 performance_schema
drwxr-x--- 2 root root       20 Nov 11 09:50 test
-rw-r----- 1 root root       19 Nov 11 09:50 xtrabackup_binlog_info ---备份了二进制文件的名字和备份时所处的位置
-rw-r----- 1 root root      113 Nov 11 09:50 xtrabackup_checkpoints  ---记录了备份自己的属性
-rw-r----- 1 root root      491 Nov 11 09:50 xtrabackup_info  ---记录了使用工具的版本及连接到服务器时使用的选项等
-rw-r----- 1 root root     2560 Nov 11 09:50 xtrabackup_logfile  ---xtrbackup执行的日志信息,看不了,是个二进制文件
[root@centos7 2017-11-11_09-50-40]#cat  xtrabackup_checkpoints
backup_type = full-backuped    ---备份类型是全量备份
from_lsn = 0
to_lsn = 2241248
last_lsn = 2241248   ---从哪个日志序列号开始备份到哪个日志序列号结束备份,数据和索引在表空间里保存的时候的编号称为日志序列号
compact = 0  ----是否打包
recover_binlog_info = 0   ---二进制日志保存的时候是否有未完成的事物需要回滚的,0表示没有

运用Xtrabackup对innodb引擎的数据库实行全量 增量 binlog热备

实验环境:172.18.21.107为工作数据库服务器,在172.18.21.7进行恢复操作,在172.18.21.107上开启二进制日志
在两个主机上都要安装xtrabackup工具
1、在172.18.21.7上准备一个干净的mariadb数据库服务器,设置如下
[root@redhat7 app]#systemctl stop mariadb
[root@redhat7 app]#rm -rf /var/lib/mysql/*
2、在172.18.21.107上进行一次全量备份
[root@centos7 app]#mkdir /app/backups  ---创建一个目录做为备份的目录
[root@centos7 app]#innobackupex --user=root --password=centos --host=172.18.21.107 /app/backups/
[root@centos7 app]#cd backups/
[root@centos7 backups]#ls
2017-11-11_16-29-15
3、在172.18.21.107上进行一次数据修改
MariaDB [hidb]> select * from students;
 ---- ----------- ------ -------- ------- 
| id | name      | age  | gender | major |
 ---- ----------- ------ -------- ------- 
|  1 | stu1      |   66 | M      | NULL  |
|  2 | stu2      |   30 | F      | NULL  |
|  3 | stu3      |   18 | M      | NULL  |
|  4 | stu4      |   45 | F      | NULL  |
|  5 | stu5      |   54 | F      | NULL  |
|  6 | stu6      |   97 | F      | NULL  |
|  7 | stu7      |   53 | M      | NULL  |
|  8 | stu8      |   98 | M      | NULL  |
|  9 | stu9      |   30 | M      | NULL  |
| 10 | stu10     |   19 | F      | NULL  |
| 11 | yangguo   |   30 | M      | NULL  |
| 12 | xiaolognv |   37 | F      | NULL  |
 ---- ----------- ------ -------- ------- 
12 rows in set (0.00 sec)

MariaDB [hidb]> delete from students where id=11; 
Query OK, 1 row affected (0.01 sec)

MariaDB [hidb]> delete from students where id=12; 
Query OK, 1 row affected (0.01 sec)

MariaDB [hidb]> select * from students;
 ---- ------- ------ -------- ------- 
| id | name  | age  | gender | major |
 ---- ------- ------ -------- ------- 
|  1 | stu1  |   66 | M      | NULL  |
|  2 | stu2  |   30 | F      | NULL  |
|  3 | stu3  |   18 | M      | NULL  |
|  4 | stu4  |   45 | F      | NULL  |
|  5 | stu5  |   54 | F      | NULL  |
|  6 | stu6  |   97 | F      | NULL  |
|  7 | stu7  |   53 | M      | NULL  |
|  8 | stu8  |   98 | M      | NULL  |
|  9 | stu9  |   30 | M      | NULL  |
| 10 | stu10 |   19 | F      | NULL  |
 ---- ------- ------ -------- ------- 
10 rows in set (0.00 sec)

4、在172.18.21.107中基于上一次全量的基础上进行一次增量备份
[root@centos7 backups]#innobackupex --user=root --password=centos --host=172.18.21.107  --incremental /app/backups/ --incremental-basedir=/app/backups/2017-11-11_16-29-15
[root@centos7 backups]#ls
2017-11-11_16-29-15  2017-11-11_16-32-49
5、在172.18.21.107上再进行一次数据修改
MariaDB [hidb]> insert into students (name,age,gender)values('xiaolognv',37,'F');
Query OK, 1 row affected (0.01 sec)
MariaDB [hidb]> select * from students;
 ---- ----------- ------ -------- ------- 
| id | name      | age  | gender | major |
 ---- ----------- ------ -------- ------- 
|  1 | stu1      |   66 | M      | NULL  |
|  2 | stu2      |   30 | F      | NULL  |
|  3 | stu3      |   18 | M      | NULL  |
|  4 | stu4      |   45 | F      | NULL  |
|  5 | stu5      |   54 | F      | NULL  |
|  6 | stu6      |   97 | F      | NULL  |
|  7 | stu7      |   53 | M      | NULL  |
|  8 | stu8      |   98 | M      | NULL  |
|  9 | stu9      |   30 | M      | NULL  |
| 10 | stu10     |   19 | F      | NULL  |
| 13 | xiaolognv |   37 | F      | NULL  |
 ---- ----------- ------ -------- ------- 

6、在172.18.21.107中在上一次增量备份的基础上进行增量备份
[root@centos7 backups]#innobackupex --user=root --password=centos --host=172.18.21.107  --incremental /app/backups/ --incremental-basedir=/app/backups/2017-11-11_16-32-49
[root@centos7 backups]#ls
2017-11-11_16-29-15  2017-11-11_16-32-49  2017-11-11_16-34-56
[root@centos7 backups]#cd 2017-11-11_16-34-56
[root@centos7 2017-11-11_16-34-56]#ls
backup-my.cnf  ibdata1.delta  mysql               test                    xtrabackup_checkpoints  xtrabackup_logfile
hidb           ibdata1.meta   performance_schema  xtrabackup_binlog_info  xtrabackup_info
[root@centos7 2017-11-11_16-34-56]#cat xtrabackup_checkpoints 
backup_type = incremental   ---后面两次备份的类型都是增量备份
from_lsn = 1617586
to_lsn = 1617886
last_lsn = 1617886
compact = 0
[root@centos7 2017-11-11_16-34-56]#cat xtrabackup_binlog_info  ---最后一次备份时二进制日志所处的位置
bin-log.000001  875
7、在172.18.21.107上进行数据修改
MariaDB [hidb]> insert into students (name,age,gender,major)values('ouyangfeng',55,'F','hamogong');
Query OK, 1 row affected (0.00 sec)

MariaDB [hidb]> select * from students;
 ---- ------------ ------ -------- ---------- 
| id | name       | age  | gender | major    |
 ---- ------------ ------ -------- ---------- 
|  1 | stu1       |   66 | M      | NULL     |
|  2 | stu2       |   30 | F      | NULL     |
|  3 | stu3       |   18 | M      | NULL     |
|  4 | stu4       |   45 | F      | NULL     |
|  5 | stu5       |   54 | F      | NULL     |
|  6 | stu6       |   97 | F      | NULL     |
|  7 | stu7       |   53 | M      | NULL     |
|  8 | stu8       |   98 | M      | NULL     |
|  9 | stu9       |   30 | M      | NULL     |
| 10 | stu10      |   19 | F      | NULL     |
| 13 | xiaolognv  |   37 | F      | NULL     |
| 14 | ouyangfeng |   55 | F      | hamogong |
 ---- ------------ ------ -------- ---------- 

此时此服务器荡机了,但二进制日志保存完好
[root@centos7 app]#mysqlbinlog -j 875 /app/logs/bin-log.000001> /app/binlog.sql    ---将最后一次增量备份到荡机时刻的二进制日志保存到一个文件中
8、在172.18.21.107上进行恢复前的prepare操作
[root@centos7 app]# cd 2017-11-11_16-29-15
[root@centos7 2017-11-11_16-29-15]#innobackupex --apply-log --redo-only ./  ---将全量备份中未完成的事物进行redo
[root@centos7 2017-11-11_16-29-15]#innobackupex --apply-log --redo-only ./ --incremental-dir=/app/backups/2017-11-11_16-32-49    ---将第一个增量备份合并到全量上,并将增量备份中未完成的事物进行redo
[root@centos7 2017-11-11_16-29-15]#innobackupex --apply-log --redo-only ./ --incremental-dir=/app/backups/2017-11-11_16-34-56   ---将第而个增量备份合并到全量上,并将增量备份中未完成的事物进行redo
[root@centos7 2017-11-11_16-29-15]#innobackupex --apply-log  ./    ---最后对全量备份和增量备份中未提交的事物进行回滚,如果没有增量备份,则不用合并直接对全量备份中未提交的事物进行回滚
[root@centos7 2017-11-11_16-29-15]#cat xtrabackup_checkpoints 
backup_type = full-prepared   ---发现已经准备完毕
from_lsn = 0
to_lsn = 1617886
last_lsn = 1617886
compact = 0
[root@centos7 backups]#scp -r 2017-11-11_16-29-15 172.18.21.7:/app  ---把合并好的全量备份文件拷贝到要恢复的主机
[root@centos7 app]#scp binlog.sql 172.18.21.7:/app   ---将准备好的二进制文件也拷贝到要恢复的主机
9、在172.18.21.7上进行恢复操作
[root@redhat7 app]#cd 2017-11-11_16-29-15
[root@redhat7 2017-11-11_16-29-15]#innobackupex --copy-back ./    ---将全量备份的目录中的内容拷贝到mariadb对应的目录下
[root@redhat7 2017-11-11_16-29-15]#cd /var/lib/mysql/
[root@redhat7 mysql]#ls ---发现对应的目录已经生成
hidb  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test  xtrabackup_binlog_pos_innodb  xtrabackup_info
[root@redhat7 mysql]#chown -R mysql:mysql ./*   ---修改这个目录的属主和属组
[root@redhat7 mysql]#systemctl start mariadb   
---开启服务,这里需要注意,有的时候开启不了,需要修改innodb_log_file_size的值为48M,可以根据日志中提示的进行修改,只要在配置文件加上这条就可以了,但此处却不可以增加,因为和ib_logfile0  ib_logfile1 这两个事物日志文件冲突
[root@redhat7 app]#mysql < binlog.sql  ---将最后一次增量备份到服务器荡机时间段的二进制日志进行重放,此处本机不要启动二进制日志
二进制日志重放时也可以采用如下方法
[root@redhat7 app]#cp binlog.sql /tmp/   ---复制到tmp目录下保证所有用户都可读
[root@redhat7 app]#mysql -uroot -pcentos -h172.18.21.107
MariaDB [(none)]> select @@session.sql_log_bin;
 ----------------------- 
| @@session.sql_log_bin |
 ----------------------- 
|                     1 |
 ----------------------- 
MariaDB [(none)]> set  @@session.sql_log_bin=off;   ---关闭二进制日志
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@session.sql_log_bin;
 ----------------------- 
| @@session.sql_log_bin |
 ----------------------- 
|                     0 |
 ----------------------- 
MariaDB [(none)]> . /tmp/bin-log.sql   ---source这个脚本文件到数据库中
MariaDB [hidb]> select * from students;    ---发现恢复完成
 ---- ------------ ------ -------- ---------- 
| id | name       | age  | gender | major    |
 ---- ------------ ------ -------- ---------- 
|  1 | stu1       |   66 | M      | NULL     |
|  2 | stu2       |   30 | F      | NULL     |
|  3 | stu3       |   18 | M      | NULL     |
|  4 | stu4       |   45 | F      | NULL     |
|  5 | stu5       |   54 | F      | NULL     |
|  6 | stu6       |   97 | F      | NULL     |
|  7 | stu7       |   53 | M      | NULL     |
|  8 | stu8       |   98 | M      | NULL     |
|  9 | stu9       |   30 | M      | NULL     |
| 10 | stu10      |   19 | F      | NULL     |
| 13 | xiaolognv  |   37 | F      | NULL     |
| 14 | ouyangfeng |   55 | F      | hamogong |
 ---- ------------ ------ -------- ---------- 

注意:--copy-back须求在要苏醒的mariadb主机本地开展,并且不能够运行mariadb服务;innodb_log_file_size大概要重新设定何况恐怕和东西日志冲突,那时候就无须在配备文件中设定了。

规划分析

是因为payment数据库过大,超越10TB,单次全量备份超过20钟头,如若根据正规的通通备份,会促成备份文件过大、耗费时间过长、甚至会因为备份操作对I/O工夫的成本影响到健康专门的学业。大家紧凑用脑筋想会发现,即使数据库本身相当大,不过,由于唯有当二零一七年表数据会不断变动(热数据卡塔尔国,历史年表数据不会修正(冷数据卡塔尔,因而正真有数据变化操作的数据量相对整个库来看并一点都不大。那么,我们将数据库设计为历史年表数据放到Read only的文本组上,把当二〇黄金时代四年表数据放到Read write的公文组上,备份系统仅仅须要备份Primary和当二〇风度翩翩八年表所在的文件组就能够(当然第二次如故要求对数据库做三遍性完整备份的卡塔尔国。那样不仅可以够大大节约备份对I/O技术的成本,又完结了冷热数据的隔断备份操作,还落成了散落了文本的I/O压力,最后落得数据库设计和备份系统优化的指标,可谓一箭多雕。

以上文字深入分析,画二个优越的安排图出来,直参观展览示如下: 图片 1

(风度翩翩卡塔 尔(英语:State of Qatar)查询日志:记录查询语句,日常不开启

  • 日志存款和储蓄地点:
    文件:file
    表:table (mysql.general_log)

  • 安插的变量
    general_log={ON|OFF}
    general_log_file=HOSTNAME.log
    log_output={FILE|TABLE|NONE}

干什么备份?

3、mysql的主从复制

主从复制时,从服务器开启三个io_thread线程连接到主服务器,将主服务器二进制日志中著录的剧情保留到本机的连结日志中,复制时从服务器开启叁个sql_thread线程,将连通日志中的内容读到内部存款和储蓄器中进行重放,实现复制操作。
实施进程如下:

172.18.21.107为主,172.18.21.7为从
1、在主和从上都进行的设置
[root@centos7 app]#systemctl stop mariadb
[root@centos7 app]#rm -rf /var/lib/mysql/*
2、在主上的设置
[root@centos7 app]#vim /etc/my.cnf.d/server.cnf 
[server]
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
log_bin = bin-log   ---启动二进制日志,这里写的是相对路径,相对于数据目录/var/lib/mysql
server_id = 1
[root@centos7 app]#systemctl start mariadb
3、在从上的设置
[root@redhat7 mysql]#vim /etc/my.cnf.d/server.cnf 
[server]
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
server_id = 2   ---设置一个服务器的id
relay_log = relay-log   ---启动中继日志
[root@redhat7 mysql]#systemctl start mariadb
4、同步两个服务器的时间,如果没有设置skip_name_resolve = on,还要保证两个主机能互相解析主机名
5、在主服务器上授权一个用户用于复制操作
[root@centos7 app]#mysql
MariaDB [(none)]> grant replication client,replication slave on *.* to repluser@'172.18.%.%' identified by 'centos';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;  ---查看正在使用的二进制日志
 ---------------- ---------- -------------- ------------------ 
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 ---------------- ---------- -------------- ------------------ 
| bin-log.000001 |      492 |              |                  |
 ---------------- ---------- -------------- ------------------ 
6、在从服务器上的设置
[root@redhat7 mysql]#mysql
MariaDB [(none)]> help change master to   ---可以查看一下帮助,看一下change master to 的选项
MariaDB [(none)]> change master to master_host='172.18.21.107',master_user='repluser',master_password='centos',master_log_file='bin-log.000001',master_log_pos=492;
MariaDB [(none)]> show slave status G   ---可以查看从服务器的一些状态信息
MariaDB [(none)]> start slave io_thread,sql_thread;  ---启动从服务器,可以直接写start slave,表示两个线程都启动
MariaDB [(none)]> show slave status G
7、在主服务器上创建一个数据库
MariaDB [(none)]> create database mydb;
8、在从上可以发现复制成功
MariaDB [(none)]> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| mydb               |    ---可以看到主服务器上创建的数据库,说明主从复制成功
| mysql              |
| performance_schema |
| test               |
 -------------------- 
MariaDB [(none)]> show slave status G

小结:如若主服务器已经运维后生可畏段时间了,要想达成主从复制,先对主服务器进行一个一心备份,然后在从服务器上实行恢复生机,使双方的多寡到达相似后在开启主从复制功效。从服务器常常要启用read_only=ON,表示只允许读,但只对这几个从没supper权限的客户有效,举例对于具备root权限的用户还是能写的。

设计图表达

以下对两全图做详细表明,以便对应用方案有进一层直观和深深驾驭。 整个数据库包罗11个文本,包涵:

1个主文件组(Primary File Group卡塔 尔(阿拉伯语:قطر‎:顾客贮存数据库系统表、视图等对象新闻,文件组可读可写。

11个客户自定义只读文件组(User-defined Read Only File Group卡塔尔国:用于寄存历史年表的数额及相应索引数据,一年一度的多少寄放到叁个文件组中。

1个客商自定义可读写文件组(User-defined Read Write File Group卡塔 尔(英语:State of Qatar):用于存放当二〇生机勃勃三年表数据和对应索引数据,该表数据必得可读可写,所以文件组必需可读可写。

1个数据库事务日志文件:用于数据库事务日志,大家需求依期备份数据库事务日志。

(二卡塔 尔(阿拉伯语:قطر‎慢查询日志:运维时刻抢先指准期间长度的查询

  • 依照变量long_query_time鲜明内定时期长度

  • 日志存款和储蓄地方:
    文件:FILE
    表:TABLE(mysql.slog_log)

  • 计划的变量
    log_slow_queries={ON|OFF}
    slow_query_log={ON|OFF}
    slow_query_log_file=
    log_output={FILE|TABLE|NONE} log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk:允许存款和储蓄为慢日志的来由
    log_slow_rate_limit
    log_slow_verbosity

灾难恢复:硬件故障(冗余)、软件故障(bug)、自然灾害、黑客攻击、误操作、测试...;

4、完毕mysql主主复制

在四个服务器上都要拉开二进制日志和接通日志
生龙活虎旦七个服务器的数据库id运转自动拉长功能,要在布署文件中设置多少个的id为奇数,其它二个的id为偶数,也许一个的id为偶数,别的八个的id为奇数,因为只要不那样设置,有超大可能率多少个服务器上还要都有顾客在写的时候会时有产生同样的id号形成冲突,所以日常景况下不提议数据库的id号启用自动增进功能,或许用id生成器进行转换。
实践进程如下:

实验环境有两台主机172.18.21.107为a和172.18.21.7为b
1、在a和b服务器上都关闭数据库服务
[root@redhat7 mysql]#systemctl stop mariadb
2、在a服务器上的设置
[root@centos7 app]#vim /etc/my.cnf.d/server.cnf 
[server]
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
log_bin = bin-log   ---开启二级制日志
relay_log = relay-log   ---开启中继日志
server_id = 1
auto_increment_offset = 1   ---表示id号从1开始
auto_increment_increment = 2  ---表示以2为步进,即为奇数
[root@centos7 app]#systemctl start mariadb
3、在b服务器上的设置
[root@redhat7 mysql]#vim /etc/my.cnf.d/server.cnf 
[server]
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
server_id = 2
relay_log = relay-log
log_bin = bin-log
auto_increment_offset = 2
auto_increment_increment = 2
[root@redhat7 mysql]#systemctl start mariadb
4、在a服务器上的设置
[root@centos7 app]#mysql
MariaDB [(none)]> show master status; ---查看二进制日志的名字和位置
 ---------------- ---------- -------------- ------------------ 
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 ---------------- ---------- -------------- ------------------ 
| bin-log.000002 |      245 |              |                  |
 ---------------- ---------- -------------- ------------------ 
MariaDB [(none)]> select user,password,host from mysql.user;  ---查看一下是否有授权的用户用于主从复制
5、在b服务器上的设置
[root@redhat7 mysql]#mysql
MariaDB [(none)]> show master status;
 ---------------- ---------- -------------- ------------------ 
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 ---------------- ---------- -------------- ------------------ 
| bin-log.000001 |      245 |              |                  |
 ---------------- ---------- -------------- ------------------ 
MariaDB [(none)]> select user,password,host from mysql.user;
MariaDB [mydb]> grant replication client,replication slave on *.* to 'repluser'@'172.18.%.%' identified by 'centos';   ---发现b服务器上没有授权的用于主从复制的用户,所以授权一个用户
6、在a上的设置
MariaDB [mydb]> change master to master_host='172.18.21.7',master_user='repluser',master_password='centos',master_log_file='bin-log.000001',master_log_pos=245;
MariaDB [mydb]> start slave;
MariaDB [mydb]> show slave status G
7、在b上的设置
MariaDB [mydb]> change master to master_host='172.18.21.107',master_user='repluser',master_password='centos',master_log_file='bin-log.000002',master_log_pos=245;
MariaDB [mydb]> start slave;
MariaDB [mydb]> show slave status G
8、测试
在a和b上查看一下
MariaDB [mydb]> select * from students;  ---刚开始是相同的
 ---- ------- ------ -------- ------- 
| id | name  | age  | gender | major |
 ---- ------- ------ -------- ------- 
|  1 | stu1  |   76 | F      | NULL  |
|  2 | stu2  |   28 | M      | NULL  |
|  3 | stu3  |   42 | F      | NULL  |
|  4 | stu4  |   71 | M      | NULL  |
|  5 | stu5  |   70 | M      | NULL  |
|  6 | stu6  |   77 | F      | NULL  |
|  7 | stu7  |   38 | F      | NULL  |
|  8 | stu8  |   82 | F      | NULL  |
|  9 | stu9  |   83 | M      | NULL  |
| 10 | stu10 |   31 | F      | NULL  |
 ---- ------- ------ -------- ------- 
在a上删除一行
MariaDB [mydb]> delete from students where id=9;
在b上发现也删除了
MariaDB [mydb]> select * from students;
 ---- ------- ------ -------- ------- 
| id | name  | age  | gender | major |
 ---- ------- ------ -------- ------- 
|  1 | stu1  |   76 | F      | NULL  |
|  2 | stu2  |   28 | M      | NULL  |
|  3 | stu3  |   42 | F      | NULL  |
|  4 | stu4  |   71 | M      | NULL  |
|  5 | stu5  |   70 | M      | NULL  |
|  6 | stu6  |   77 | F      | NULL  |
|  7 | stu7  |   38 | F      | NULL  |
|  8 | stu8  |   82 | F      | NULL  |
| 10 | stu10 |   31 | F      | NULL  |
 ---- ------- ------ -------- ------- 

复制时应该注意的标题:
1、从服务设定为“只读”:在从服务器运营read_only,但仅对非SUPE中华V权限的客户有效;
要想阻止全体客商能够报名二个大局读锁:mysql> FLUSH TABLES WITH READ LOCK;
2、尽量确定保证复制时的事体安全
在master节点启用参数:sync_binlog = ON,表示风流浪漫旦当前节点有东西提交时就应声从内部存款和储蓄器缓冲区保存到二进制日志中,制止从服务器复制时二进制日志中未有这几个提交操作主服务器就坏了,那样从服务器就不清楚这么些事物该不应当提交了
倘使用到的是InnoDB存款和储蓄引擎:
innodb_flush_logs_at_trx_commit=ON ---每当事物提交时就伙同到东西日志中
innodb_support_xa=ON 让innodb扶助遍及式事物
3、从服务器意外中止时尽量防止自动运营复制线程 ,若是从服务器在复制一个事件的时候复制到百分之五十的时候从服务器意外终止了,如若重新启航,复制作用实是会自行开启的,因为在/var/lib/mysql/master.info文件中著录了连接到主服务器的新闻,所以运转mariadb服务时会自动运维复制线程,这样就能够引致难点,因为终止前的事物复制到八分之四,不精通该不应该提交,为了幸免这种业务时有发生,大家要把网断掉,查看一下是否有复制到四分之二的事物,倘使有手动删除,然后手动加change master to 指向意外终止时主服务器二进制日志的岗位,恐怕重新备份恢复生机后运营复制功用
4、从节点:设置参数
sync_master_info=ON
sync_relay_log_info=ON

方案落成

解决方案实现之后,接下去便是方案的国有达成了,具体落到实处包含:

创制数据库

创造年表

文件组织设立置

冷热备份完毕

(三卡塔尔错误日志

  • 笔录如下几类音信:

    • mysqld运维和停业进程中输出的消息
    • mysqld运转中生出的错误新闻
    • event scheduler运营时发出的新闻
    • 主从复制架构中,从服务器复制线程运转时发生的日志
  • 配备的变量
    log_error={/var/log/mariadb/mariadb.log|OFF}
    log_warnings={ON|OFF}

备份时应该注意事项:

4、主从复制centos6.9的安插及注意事项

vim /etc/my.cnf 

[root@centos6 mysql]#vim /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000  ---以上三行不能写到server里,mysql服务启动不了
[server]    ---注意在这里要加上server这一项不然不会生效
server_id = 1
log_bin = bin-log
relay_log = relay-log
auto_increment_offset = 1   
auto_increment_increment = 2
启动slave时要用mysql> start slave io_thread,sql_thread;
不能简写成mysql> start slave,不然开启不了复制操作

创立数据库

创建数据库的还要,大家创造了Primary文件组和二〇〇九 ~ 2017的文件组,这里必要专门提醒,请必需确认保证具备文件组中文件的起头大小和增加量相近,代码如下:

USE master
GO

EXEC sys.xp_create_subdir 'C:DATAPaymentData'
EXEC sys.xp_create_subdir 'C:DATAPaymentLog'

CREATE DATABASE [Payment]
 ON  PRIMARY 
( NAME = N'Payment', FILENAME = N'C:DATAPaymentDataPayment.mdf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2008] 
( NAME = N'FGPayment2008', FILENAME = N'C:DATAPaymentDataPayment_2008.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2009] 
( NAME = N'FGPayment2009', FILENAME = N'C:DATAPaymentDataPayment_2009.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2010] 
( NAME = N'FGPayment2010', FILENAME = N'C:DATAPaymentDataPayment_2010.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2011] 
( NAME = N'FGPayment2011', FILENAME = N'C:DATAPaymentDataPayment_2011.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2012] 
( NAME = N'FGPayment2012', FILENAME = N'C:DATAPaymentDataPayment_2012.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2013] 
( NAME = N'FGPayment2013', FILENAME = N'C:DATAPaymentDataPayment_2013.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2014]
( NAME = N'FGPayment2014', FILENAME = N'C:DATAPaymentDataPayment_2014.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2015] 
( NAME = N'FGPayment2015', FILENAME = N'C:DATAPaymentDataPayment_2015.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2016] 
( NAME = N'FGPayment2016', FILENAME = N'C:DATAPaymentDataPayment_2016.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2017] 
( NAME = N'FGPayment2017', FILENAME = N'C:DATAPaymentDataPayment_2017.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB )
 LOG ON 
( NAME = N'Payment_log', FILENAME = N'C:DATAPaymentLogPayment_log.ldf' , SIZE = 5MB , FILEGROWTH = 50MB)
GO

虚构到每一年咱们都要增加新的文书组到数据库中,由此2018年的文本组单独成立如下:

--Add filegroup FGPayment2018
USE master
GO
ALTER DATABASE [Payment] ADD FILEGROUP [FGPayment2018];

-- Add data file to FGPayment2018
ALTER DATABASE [Payment]
ADD FILE (NAME = FGPayment2018, SIZE = 5MB , FILEGROWTH = 50MB ,FILENAME = N'C:DATAPaymentDataPayment_2018.ndf')
TO FILEGROUP [FGPayment2018]
GO

聊起底再度确认数据库文件组信息,代码如下:

USE [Payment]
GO
SELECT file_name = mf.name, filegroup_name = fg.name, mf.physical_name,mf.size,mf.growth 
FROM sys.master_files AS mf
 INNER JOIN sys.filegroups as fg
 ON mf.data_space_id = fg.data_space_id
WHERE mf.database_id = db_id('Payment')
ORDER BY mf.type;

结果突显如下图所示: 图片 2

(四卡塔尔二进制日志

能容忍最多丢失多少数据;
恢复数据需要在多长时间内完成;
需要恢复哪些数据;

做恢复演练:
    测试备份的可用性;
    增强恢复操作效率;

5、完结基于ssl的主从复制

1、查看所安装的数据库软件是否支持ssl,默认yum安装都是不支持的,需要编译安装的时候加上ssl功能
MariaDB [(none)]> show global variables like '%ssl%';  
 --------------- ---------- 
| Variable_name | Value    |
 --------------- ---------- 
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
 --------------- ---------- 
7 rows in set (0.00 sec)
2、在主服务器上创建一个用于复制的账号,要求从服务器使用这个账号连接到主服务器时必须使用ssl连接
grant replication client,replication slave on *.* to hello@'172.18.%.%' identified by 'centos' require ssl;
3、在从服务器上创建证书和私钥文件
从服务器上使用change master to命令指明自己的ssl相关选项
help change master to查看使用的选项
比如:change master to master_host='172.18.21.106',master_user='hello',master_password='centos',master_log_file='bin-log.000005',master_log_pos=106,MASTER_SSL_CERT='/etc/pki/tls/certs/mysql.crt',MASTER_SSL_KEY='/etc/pki/tls/certs/mysql.key';   
这里的,MASTER_SSL_CERT='/etc/pki/tls/certs/mysql.crt',MASTER_SSL_KEY='/etc/pki/tls/certs/mysql.key';  是从服务器上的证书和私钥文件路径
4、如果从服务器也需要主服务器提供ssl验证,就需要在主服务器上也创建证书和私钥文件
将证书和私钥文件在主服务器的配置文件中指定
一般情况下主服务器端不需要配置证书和私钥,因为是从服务器去连接主服务器进行复制,所以要求从服务器提供证书和私钥文件进行认证。

开创年表

数据库以至对应文件组创制完结后,接下去我们成立对应的年表并插入一些测验数据,如下:

USE [Payment]
GO
CREATE TABLE [dbo].[Payment_2008](
 [Payment_ID] [bigint] IDENTITY(12008,100) NOT NULL,
 [OrderID] [bigint] NOT NULL,
 CONSTRAINT [PK_Payment_2008] PRIMARY KEY CLUSTERED 
 (
  [Payment_ID] ASC
 ) ON [FGPayment2008]
) ON [FGPayment2008]
GO

CREATE NONCLUSTERED INDEX IX_OrderID
ON [dbo].[Payment_2008] ([OrderID])
ON [FGPayment2008];

CREATE TABLE [dbo].[Payment_2009](
 [Payment_ID] [bigint] IDENTITY(12009,100) NOT NULL,
 [OrderID] [bigint] NOT NULL,
 CONSTRAINT [PK_Payment_2009] PRIMARY KEY CLUSTERED 
 (
  [Payment_ID] ASC
 ) ON [FGPayment2009]
) ON [FGPayment2009]
GO

CREATE NONCLUSTERED INDEX IX_OrderID
ON [dbo].[Payment_2009] ([OrderID])
ON [FGPayment2009];

--这里省略了2010-2017的表创建,请参照以上建表和索引代码,自行补充
CREATE TABLE [dbo].[Payment_2018](
 [Payment_ID] [bigint] IDENTITY(12018,100) NOT NULL,
 [OrderID] [bigint] NOT NULL,
 CONSTRAINT [PK_Payment_2018] PRIMARY KEY CLUSTERED 
 (
  [Payment_ID] ASC
 ) ON [FGPayment2018]
) ON [FGPayment2018]
GO

CREATE NONCLUSTERED INDEX IX_OrderID
ON [dbo].[Payment_2018] ([OrderID])
ON [FGPayment2018];

那边须要特地提示两点:

限于篇幅,建表代码中回顾了二〇〇八 - 2017表创造,请自行补充

各类年表的Payment_ID字段初叶值是不均等的,避防查询全数payment消息该字段值存在重新的情况

说不上,大家检查有着年表的文件组布满情况如下:

USE [Payment]
GO
SELECT table_name = tb.[name], index_name = ix.[name], located_filegroup_name = fg.[name] 
FROM sys.indexes ix
 INNER JOIN sys.filegroups fg
 ON ix.data_space_id = fg.data_space_id
 INNER JOIN sys.tables tb
 ON ix.[object_id] = tb.[object_id] 
WHERE ix.data_space_id = fg.data_space_id
GO

查询结果截取当中部分如下,大家看见有着年表及索引都遵照大家的料想布满到对应的文件组上去了。 图片 3

终极,为了测验,大家在对应年表中放入一些数额:

USE [Payment]
GO
SET NOCOUNT ON
INSERT INTO [Payment_2008] SELECT 2008;
INSERT INTO [Payment_2009] SELECT 2009;
--省略掉2010 - 2017,自行补充
INSERT INTO [Payment_2018] SELECT 2018;
(1卡塔尔基本概念
  • 概念:用于记录引起数据变动或存在引起数据变动的暧昧恐怕性的口舌(STATEMENT)或转移后的结果(ROW),也恐怕是双方混合

  • 职能:“重放”,数据库备份和主从复制时选用

  • 翻开二进制日志文件列表:
    SHOW MASTER|BINARY LOGS;

  • 查阅当前正值使用的二进制日志文件:
    SHOW MASTER STATUS;

  • 翻看二进制日志文件中的事件:
    SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

备份类型:

文件组织设立置

年表创设完停止、测量试验数据开端化实现后,接下去,大家做文件组读写属性的设置,代码如下:

USE master
GO
ALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2008] READ_ONLY;
ALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2009] READ_ONLY;
--这里省略了2010 - 2017文件组read only属性的设置,请自行补充
ALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2018] READ_WRITE;

最终大家的公文组读写属性如下:

USE [Payment]
GO
SELECT name, is_default, is_read_only FROM sys.filegroups
GO

截图如下:

图片 4

(2卡塔尔相关变量
  • 概念存放消息格式的变量
    binlog_format={STATEMENT|ROW|MIXED}
    STATEMENT:语句,默认
    ROW:行
    MIXED:混编

  • 服务器变量:

    • log_bin=/PATH/TO/BIN_LOG_FILE
      只读变量
    • session.sql_log_bin={ON|OFF}
      调节某会话中的“写”操作语句是不是会被记录于日志文件中
    • max_binlog_size=1073741824
      日志最大存款和储蓄体积,单位B
    • sync_binlog={1|0}
      事务COMMIT后是不是及时联合至二进制日志,打开后品质有损失,但消释了多少错过的高危害
完全备份和部分备份
    完全备份:整个数据集;
    部分备份:数据集的一部分,比如部分表;

全量备份、增量备份、差异备份:
    完全备份
    增量备份:仅备份自上一次完全备份或 增量备份以来变量的那部数据;
    差异备份:仅备份自上一次完全备份以来变量的那部数据;

物理备份、逻辑备份:
    物理备份:复制数据文件进行的备份;
    逻辑备份:从数据库导出数据另存在一个或多个文件中;

根据数据服务是否在线:
    热备:读写操作均可进行的状态下所做的备份;
    温备:可读但不可写状态下进行的备份;
    冷备:读写操作均不可进行的状态下所做的备份;

冷热备份达成

具备文件组创制作而成功,并且读写属性配置完成后,大家要求对数据库可读写文件组实行全量备份、差别备份和数据库品级的日记备份,为了方便测量试验,我们会在一遍备份之间插入一条数据。备份操作的大约思路是:

首先,对一切数据库实行二回性全量备份

其次,对可读写文件组开展周期性全量备份

接下来,对可读写文件组实行周期性差别备份

最终,对整个数据库进行周期性事务日志备份

--Take a one time full backup of payment database
USE [master];
GO
BACKUP DATABASE [Payment]
 TO DISK = N'C:DATAPaymentBACKUPPayment_20180316_full.bak' 
 WITH COMPRESSION, Stats=5
;
GO

-- for testing, init one record
USE [Payment];
GO
INSERT INTO [dbo].[Payment_2018] SELECT 201801;
GO

--Take a full backup for each writable filegoup (just backup FGPayment2018 as an example)
BACKUP DATABASE [Payment]
 FILEGROUP = 'FGPayment2018'
 TO DISK = 'C:DATAPaymentBACKUPPayment_FGPayment2018_20180316_full.bak' 
 WITH COMPRESSION, Stats=5
;
GO

-- for testing, insert one record
INSERT INTO [dbo].[Payment_2018] SELECT 201802;
GO

--Take a differential backup for each writable filegoup (just backup FGPayment2018 as an example)
BACKUP DATABASE [Payment]
   FILEGROUP = N'FGPayment2018'
   TO DISK = N'C:DATAPaymentBACKUPPayment_FGPayment2018_20180316_diff.bak'
   WITH DIFFERENTIAL, COMPRESSION, Stats=5
 ;
GO

-- for testing, insert one record
INSERT INTO [dbo].[Payment_2018] SELECT 201803;
GO

-- Take a transaction log backup of database payment
BACKUP LOG [Payment]
TO DISK = 'C:DATAPaymentBACKUPPayment_20180316_log.trn';
GO

如此那般备份的实惠是,大家只要求对可读写的文件组(FGPayment2018卡塔 尔(英语:State of Qatar)举办完全和异样备份(Primary中隐含系统对象,变化异常的小,实际情状中,Primary文件组也供给备份卡塔尔,而任何的9个只读文件组无需备份,因为数量不会再转变。如此,我们就兑现了冷热数据隔断备份的方案。 接下来的贰个标题是,万意气风发Payment数据爆发灾殃,招致数据损失,大家什么样从备份集上校数据库苏醒出来吗?大家能够根据如下思路来苏醒备份集:

首先,还原整个数据库的一回性全量备份

其次,还原全体可读写文件组最终三个全量备份

接下来,还原可读写文件组倒数间隔备份

最后,还原整个数据库的享有专门的学业日志备份

-- We restore full backup
USE master
GO
RESTORE DATABASE [Payment_Dev]
FROM DISK=N'C:DATAPaymentBACKUPPayment_20180316_full.bak' WITH
MOVE 'Payment' TO 'C:DATAPayment_DevDataPayment_dev.mdf',
MOVE 'FGPayment2008' TO 'C:DATAPayment_DevDataFGPayment2008_dev.ndf',
MOVE 'FGPayment2009' TO 'C:DATAPayment_DevDataFGPayment2009_dev.ndf',
MOVE 'FGPayment2010' TO 'C:DATAPayment_DevDataFGPayment2010_dev.ndf',
MOVE 'FGPayment2011' TO 'C:DATAPayment_DevDataFGPayment2011_dev.ndf',
MOVE 'FGPayment2012' TO 'C:DATAPayment_DevDataFGPayment2012_dev.ndf',
MOVE 'FGPayment2013' TO 'C:DATAPayment_DevDataFGPayment2013_dev.ndf',
MOVE 'FGPayment2014' TO 'C:DATAPayment_DevDataFGPayment2014_dev.ndf',
MOVE 'FGPayment2015' TO 'C:DATAPayment_DevDataFGPayment2015_dev.ndf',
MOVE 'FGPayment2016' TO 'C:DATAPayment_DevDataFGPayment2016_dev.ndf',
MOVE 'FGPayment2017' TO 'C:DATAPayment_DevDataFGPayment2017_dev.ndf',
MOVE 'FGPayment2018' TO 'C:DATAPayment_DevDataFGPayment2018_dev.ndf',
MOVE 'Payment_log' TO 'C:DATAPayment_DevLogPayment_dev_log.ldf',
NORECOVERY,STATS=5;
GO

-- restore writable filegroup full backup
RESTORE DATABASE [Payment_Dev]
   FILEGROUP = N'FGPayment2018'
   FROM DISK = N'C:DATAPaymentBACKUPPayment_FGPayment2018_20180316_full.bak'
   WITH NORECOVERY,STATS=5;
GO

-- restore writable filegroup differential backup
RESTORE DATABASE [Payment_Dev]
   FILEGROUP = N'FGPayment2018'
   FROM DISK = N'C:DATAPaymentBACKUPPayment_FGPayment2018_20180316_diff.bak'
   WITH NORECOVERY,STATS=5;
GO

-- restore payment database transaction log backup
RESTORE LOG [Payment_Dev]
FROM DISK = N'C:DATAPaymentBACKUP\Payment_20180316_log.trn'
WITH NORECOVERY;
GO

-- Take database oneline to check
RESTORE DATABASE [Payment_Dev] WITH RECOVERY;
GO

提起底检查数据还原的结果,依照我们插入的测验数据,应该会有四条记下。

USE [Payment_Dev]
GO
SELECT * FROM [dbo].[Payment_2018] WITH(NOLOCK)

体现试行结果,有四条结果集,契合大家的意料,截图如下:

图片 5

(3)mysqlbinlog
  • 鉴于二进制日志以二进制格式存款和储蓄,使用相像文本查看工具可能对日记发生破坏,何况也不可能查询到有效新闻,故设计了mysqlbinlog作为查询二进制日志新闻的专项使用工具

  • 语法:mysqlbinlog OPTIONS

    • 点名时限
      --start-datetime=YYYY-MM-DD hh:mm:ss
      --stop-datetime=
    • 点名起止点
      -j, --start-position=#
      --stop-position=#
    • 钦点客商、主机、密码
      --user, --host, --password

备份必要思忖因素:

最终总计

本篇月报分享了什么样运用SQL Server文件组工夫来贯彻和优化冷热数据隔开备份的方案,在大大进步数据库备份还原功能的同时,还提供了I/O能源的载重均衡,进步和优化了全部数据库的习性。

翻阅原来的小说

(4卡塔 尔(英语:State of Qatar)二进制日志事件格式:

二进制日志截取片段如下:

# at 553
#160831 9:56:08 server id 1 end_log_pos 624 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1472608568/*!*/;
BEGIN
/*!*/;

事件的前奏地方:# at 553
事件发生的日期时间:#160831 9:56:08
事件发生的服务器id:server id 1
事件的完工地点:end_log_pos 624
事件的项目:Query
事件时有爆发时所在服务器试行那件事件的线程的ID: thread_id=2
言辞的光阴戳与将其写入二进制日志文件中的时间差:exec_time=0
错误代码:error_code=0
设定事件时有产生时的小时戳:SET TIMESTAMP=1472608568/*!*/;
事件从头到尾的经过:BEGIN

锁定资源多长时间?
备份过程的时长?
备份时的服务器负载?
恢复过程的时长?
(5卡塔尔实验1:二进制日志的连带操作
  • 张开二进制效率,设置二进制日志的存放路线

    mkdir -p /mydata/{data,logs}
    chown -R mysql.mysql /mydata/*
    vim /etc/my.cnf.d/server.cnf
    log_bin = /mydata/logs/master-log    // 在[server]下添加一行
    systemctl restart mariadb.service
    

    那儿在日记存放目录下,现身了日记文件

  • MySQL下询问二进制日志文件状态音讯:SHOW BINARY LOGS;

  • 二进制文件滚动记录,通过命令FLUSH LOGS;强行滚动日志,再查询二进制日志文件,开掘了新的轮转文件

    图片 6

  • 询问当前应用的二进制日志文件:SHOW MASTER STATUS;

    图片 7

  • 查询钦点二进制日志记录的平地风波:SHOW BINLOG EVENTS IN 'master-log.000001';

    图片 8

  • 暂且关门二进制日志:SET @@session.sql_log_bin=OFF;

    图片 9

    能够看来当关闭二进制日志后,试行增多一条新记录的操作后,二进制日志未有新事件的记录

  • 二进制日志在数据库的备份恢复生机和主从复制操作中表述了比较大的遵从,平时情况下不会停业,唯有在实践“重放”操作时才有不时关门的要求

备份计策:

(五卡塔 尔(英语:State of Qatar)中继日志:

从服务器上记录下来从主服务器的二进制日志文件同步过来的平地风波

全量 差异   binlogs
全量 增量   binlogs

(六卡塔 尔(阿拉伯语:قطر‎事务日志:

事务型存款和储蓄引擎innodb用于保险职业性情的日志文件

备份哪些内容?

二、备份和复苏:

备份:存款和储蓄的数量别本,须要注意主机备份进度中本来数据大概不仅仅发出更动
平复:把别本应用到线上系统,须要介意仅能回涨至备份操作时刻的数量状态
时刻点过来:通过二进制日志binary logs苏醒备份进程中发出变动的多少

数据
二进制日志、InnoDB的事务日志;
代码(存储过程、存储函数、触发器、事件调度器)
服务器的配置文件

(意气风发卡塔尔为何备份?

  • 苦难复苏:人为错误、硬件故障(冗余卡塔尔国、软件故障(bug卡塔尔、自然灾荒、红客攻击等原因促成

  • 测试

本文将为大家介绍二种备份工具,msyqldump和xtrabackup

(二卡塔尔备份时应当注意事项:

  • 能隐忍最多有失多少多少
  • 光复数据须要在多久内成功
  • 亟待还原哪些数据
  • 做复苏演习:
    测量检验备份的可用性
    增加复苏操作效用

Mysqldump

mysqldump是mysql服务自带的备份工具,是一个逻辑备份工具。基于mysql顾客端合同,能够扩充完全、部分备份,协助InnoDB热备和温备,MyISAM扶植温备。

mysqldump有几个叁次封装工具

mydumper
phpMyAdmin

有两种备份方式

mysqldump [OPTIONS] database [tables]                       #备份单库,可以只备份其中的一部分表;
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]  #备份多个库;
mysqldump [OPTIONS] --all-databases [OPTIONS]               #备份所有库;

常用选项:

-x, --lock-all-tables:         #锁定所有库的所有表,读锁;
-l, --lock-tables:             #锁定指定库所有表;
--single-transaction:          #创建一个事务,基于此快照执行备份;
-R, --routines:                #备份指定库的存储过程和存储函数;
--triggers:                    #备份指定库的触发器;
-E, --events:                  #备份指定库的事件调度器;
--master-data[=#]               #记录当前备份是处于那个二进制日志文件的什么位置,用于时间点恢复;
        1:#记录为CHANGE MASTER TO语句,此语句不被注释;
        2:#记录为CHANGE MASTER TO语句,此语句被注释,建议进行注释,否则在备份恢复时会执行这条命令;
--flush-logs:                  #锁定表完成后,即进行日志刷新操作;

示例:

  1. 创设一个测量试验数据库
MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [mydb]> CREATE TABLE test1(id int unsigned auto_increment primary key,name varchar(200) not null,age int);
MariaDB [mydb]> insert into test1 (name,age) values ('Zhang Xiaofan',18),('Lu Xueqi',17),('Bi Yao',16);
MariaDB [mydb]> select * from test1;
 ---- --------------- ------ 
| id | name          | age  |
 ---- --------------- ------ 
|  1 | Zhang Xiaofan |   18 |
|  2 | Lu Xueqi      |   17 |
|  3 | Bi Yao        |   16 |
 ---- --------------- ------ 
  1. 单库备份
[root@CentOS7.3 app]#mysqldump mydb > /app/mydb-backup
-- MySQL dump 10.14  Distrib 5.5.52-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: mydb
-- ------------------------------------------------------
-- Server version       5.5.52-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test1`
--

DROP TABLE IF EXISTS `test1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test1`
--

LOCK TABLES `test1` WRITE;
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` VALUES (1,'Zhang Xiaofan',18),(2,'Lu Xueqi',17),(3,'Bi Yao',16);
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-09-15 20:33:11

除去数据库并复苏

MariaDB [(none)]> drop database mydb;               #删除数据库
MariaDB [(none)]> create database mydb;             #创建数据库
MariaDB [(none)]> quit                              
Bye
[root@CentOS7.3 app]#mysql mydb < mydb-backup       #备份恢复,需要指定数据库名。
[root@CentOS7.3 app]#mysql
MariaDB [(none)]> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> show tables;
 ---------------- 
| Tables_in_mydb |
 ---------------- 
| test1          |
 ---------------- 
1 row in set (0.00 sec)

MariaDB [mydb]> select * from test1;        
 ---- --------------- ------ 
| id | name          | age  |
 ---- --------------- ------ 
|  1 | Zhang Xiaofan |   18 |
|  2 | Lu Xueqi      |   17 |
|  3 | Bi Yao        |   16 |
 ---- --------------- ------ 
3 rows in set (0.00 sec)

注:因为运用备份单个库的吩咐时,在苏醒是不会创设数据库,所以提议即就是备份单个库也使用备份多少个库的下令实行备份。

[root@CentOS7.3 app]#mysqldump --databases mydb > /app/mydb-fullbackup      #使用--databases 指定要备份的库名
[root@CentOS7.3 app]#less /app/mydb-fullbackup 
-- MySQL dump 10.14  Distrib 5.5.52-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: mydb
-- ------------------------------------------------------
-- Server version       5.5.52-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `mydb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */;
#在备份多个库和所有库时会在配置文件内添加创建库的命令。

USE `mydb`;
  1. 全部备份
[root@CentOS7.3 ~]#mysqldump  --all-databases -x -R --triggers -E --master-data=2 --flush-logs > /app/mysql-alldbbackup-`date  %F-%H-%M-%S`      

mysqldump默许把音讯输出到正规输出中,所以需求把mysqldump的出口重定向到八个钦点文件中,建议文件名的格式:库名 备份时间。

数据库备份过后有极大可能率会生出多少变动,那么在还原时那有个别数额是回天无力透过备份举行理并答复原的,就须要动用二进制日志对那生机勃勃部分数据开展重播操作,那就供给记录在开展备份时所在哪个二进制日志文件的哪位岗位,使用--master-data[=#]能够记下,为了幸免数据备份时二进制日志文件在有些文件叫靠后的任务,提出搭配--flush-logs参数刷新到叁个新的二进制日志文件再记录地点。

[root@CentOS7.3 ~]#less /app/mysql-alldbbackup-2017-09-15-21-20-34
     #查看备份文件里的信息

-- MySQL dump 10.14  Distrib 5.5.52-MariaDB, for Linux (x86_64)         #由于内容过多,所以大部分内容无法显示。
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.5.52-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin-log.000004', MASTER_LOG_POS=245;
        #记录的二进制文件及位置,245是二进制日志文件的当前大小,单位字节
--
-- Current Database: `mydb`
--

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

USE `mydb`;

--
-- Table structure for table `test1`
--

DROP TABLE IF EXISTS `test1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test1`
--

LOCK TABLES `test1` WRITE;
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` VALUES (1,'Zhang Xiaofan',18),(2,'Lu Xueqi',17),(3,'Bi Yao',16);
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'mydb'
--

--
-- Dumping routines for database 'mydb'
--

--
-- Current Database: `mysql`
--

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

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `columns_priv`
--

LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `db`
--

在数据库用丰富多少,用来验证时间点复苏

MariaDB [mydb]> insert into test1 (name,age) values ('Xiao Ming',10),('Xiao Hong',9); 
MariaDB [mydb]> select * from test1;
 ---- --------------- ------ 
| id | name          | age  |
 ---- --------------- ------ 
|  1 | Zhang Xiaofan |   18 |
|  2 | Lu Xueqi      |   17 |
|  3 | Bi Yao        |   16 |
|  4 | Xiao Ming     |   10 |
|  5 | Xiao Hong     |    9 |
 ---- --------------- ------ 
5 rows in set (0.00 sec)
MariaDB [mysql]> drop database mydb;            #删除数据库

还原,在还原时提议有的时候关闭二进制日志不然苏醒的语句会被记录在二进制日志内的。

[root@CentOS7.3 ~]#vim /etc/my.cnf.d/server.cnf 
#修改配置文件,这个参数一般设置在/etc/my.cnf或/etc/my.cnf.d/server.cnf文件中
log-bin=master-bin-log          #找到这一项将其注释。
[root@CentOS7.3 mysql]#mysql < /app/mysql-alldbbackup-2017-09-15-21-20-34 
[root@CentOS7.3 ~]#mysql
MariaDB [(none)]> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> show tables;
 ---------------- 
| Tables_in_mydb |
 ---------------- 
| test1          |
 ---------------- 
1 row in set (0.00 sec)

MariaDB [mydb]> select * from test1;
 ---- --------------- ------ 
| id | name          | age  |
 ---- --------------- ------ 
|  1 | Zhang Xiaofan |   18 |
|  2 | Lu Xueqi      |   17 |
|  3 | Bi Yao        |   16 |
 ---- --------------- ------ 
3 rows in set (0.00 sec)            #数据已经恢复

时光点苏醒,在还原前需求查阅备份文件内记录的二进制日志文件的职位。

[root@CentOS7.3 ~]#ls /var/lib/mysql/           #使用yum安装的Mariadb的数据文件默认路径在/var/lib/mysql
aria_log.00000001  ib_logfile1            master-bin-log.000004  mysql.sock
aria_log_control   master-bin-log.000001  master-bin-log.index   performance_schema
ibdata1            master-bin-log.000002  mydb                   test
ib_logfile0        master-bin-log.000003  mysql
[root@CentOS7.3 mysql]#mysqlbinlog master-bin-log.000004 > master-binlog        
#使用mysqlbinlog命令转换二进制文件,如果有多个文件还可以进行合并,格式如下。
[root@CentOS7.3 mysql]#mysqlbinlog master-bin-log.000003 master-bin-log.000004 > master-binlog 
[root@CentOS7.3 mysql]#mysql < master-binlog            #重放
[root@CentOS7.3 mysql]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 9
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> select * from test1;
 ---- --------------- ------ 
| id | name          | age  |
 ---- --------------- ------ 
|  1 | Zhang Xiaofan |   18 |
|  2 | Lu Xueqi      |   17 |
|  3 | Bi Yao        |   16 |
|  4 | Xiao Ming     |   10 |       #备份后添加的数据也恢复了
|  5 | Xiao Hong     |    9 |
 ---- --------------- ------ 
5 rows in set (0.00 sec)

(三卡塔尔备份类型:

  • 备份的数据集的限定:完全备份和有个别备份
    全然备份:整个数据集
    有的备份:数据集的生机勃勃有个别,比方有的表

  • 全量备份、增量备份、差别备份:
    全量备份:即完全备份
    增量备份:仅备份自上二次完全备份或增量备份以来变化的那有个别数目
    差距备份:仅备份自上一遍完全备份以来变化的那有个别数据

  • 大意备份、逻辑备份:
    大意备份:复制数据文件实行的备份,功能较高
    逻辑备份:从数据库导出数据另存在三个或八个公文中,功用好低

  • 基于数据服务是还是不是在线:
    热备:读写操作均可开展的动静下所做的备份,实际临蓐条件独有热备是可肩负的
    温备:可读但不可写意况下开展的备份
    冷备:读写操作均不可举办的情事下所做的备份

Xtrabackup

Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟生机勃勃朝气蓬勃款开源的能够对innodb和xtradb数据库实行热备的工具。

特点:

(1)备份过程快速、可靠;
(2)备份过程不会打断正在执行的事务;
(3)能够基于压缩等功能节约磁盘空间和流量;
(4)自动实现备份检验;
(5)还原速度快;
(6)物理备份;

MyISAM:温备,不帮助增量备份;

InnoDB:热备,增量;

(四卡塔尔国备份战术:

  • 全量 差异 binlogs
  • 全量 增量 binlogs

安装

官方下载地址:https://www.percona.com/downloads/XtraBackup/LATEST/

[root@CentOS7.3 app]#yum -y install /app/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm 
                        #建议使用yum安装这个rpm包,因为xtrabackup有一些依赖包。

重大文件

/usr/bin/innobackupex       innobackupex是一个封装了xtrabackup的Perl脚本,增加里xtrabckup的易用性。
/usr/bin/xbcrypt            备份恢复程序
/usr/bin/xbstream           stream功能程序
/usr/bin/xtrabackup         主程序文件

(五卡塔 尔(阿拉伯语:قطر‎备份内容:

  • 数据
  • 二进制日志、InnoDB的业务日志;
  • 代码(存款和储蓄进程、存款和储蓄函数、触发器、事件调节器卡塔 尔(阿拉伯语:قطر‎
  • 服务器的计划文件

备份的兑现

备份战略:

xtrabackup:
    全量 差异 binlog
    全量 增量 binlog

语法

[innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]

常用用的参数

--defaults-file         指定要备份的mysql实例的my.cnf文件,使用rpm包安装可忽略。
--host                  指定主机名/IP。
--port                  指定所需连接的端口,默认3306可忽略不写。
--socket                连接套字节的位置,默认为/var/lib/mysql/mysql.sock。
--use-memory=4G         此参数用来控制备份所使用到的内存大小,默认为100M。
--apply-log             数据准备。
--copy-back             把备份数据拷贝回server的datadir,它决定于my.cnf中的datadir参数,恢复时datadir目录必须是空的,并且mysql数据库必须是关闭的。
--no-timestamp          禁止innobackup生成以时间命名的目录,自己只当备份目录名,使用--no-timestamp时备份目录不能自己提前创建,innobackup会自动生成。
--redo-only             在做增量恢复时,全备和增量备份的数据文件在恢复前必须先将在重做日志文件中的已提交的事务重做!此参数将会合并全备和增量备份的数据文件。
--compress              压缩选项,此选项不兼容--stream=tar,只兼容--stream=xbstream,此外加密项encrypted也不能兼容--stream=tar。
--decompress            在备份时如果使用了加密或者压缩选项,在用xbstream -x 解压包出来后,还须使用innodbackupex       --decompress/data/backup   压缩后的文件为ibdata1.qp,qp结尾的文件!另外使用decompress还需yum install qpress.x86_64 装上qpress这个包。

1、完全备份

提议创设贰个十分小权限的客商展开备份,则可借助如下命令创立此类客商:

MariaDB [(none)]> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'backup123';
MariaDB [(none)]> REVOKE ALL  ON *.* FROM 'bkpuser'@'localhost';
MariaDB [(none)]> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

采纳innobakupex备份时,其会调用xtrabackup备份全数的InnoDB表,复制全体关于表结构定义的连锁文书(.frm)、以致MyISAM、ME大切诺基GE、CSV和ARC生殖器疱疹E表的相干文件,同不经常间还或然会备份触发器和数据库配置新闻有关的公文。这么些文件会被保留至七个以时间命名的目录中。

在备份的还要,innobackupex还只怕会在备份目录中创立如下文件:

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

        LSN:每个InnoDB页(通常为16k大小)都会包含一个日志序列号,即LSN。InnoDB是使用空间存储数据,会在表空间生
    成一个个盘区,有一个LSN会标记每次数据改变,这个序列号会一直累加,这个序列号记录了这次更改是那个盘区发生的。
    在做增量备份是就可以利用这种机制,只备份上次备份后的日志序列号记录发生改变的盘区。

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

3. xtrabackup_logfile           #备份中生成日志信息;

4. backup-my.cnf                #备份命令用到的配置选项信息

5. xtrabackup_info              #备份过程中的一些信息

在行使innobackupex进行备份时,还是能够动用--no-timestamp选项来阻拦命令自动创立三个以时日命名的目录;如此一来,innobackupex命令将会成立四个BACKUP-DI奥迪Q5目录来存款和储蓄备份数据。

语法:innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

[root@CentOS7.3 ~]#innobackupex --user=bkpuser --password=backup123 /app/backup #全量备份
...
170915 23:29:27 completed OK!   #执行完成出现这一行表示备份成功
[root@CentOS7.3 ~]#cd /app/backup/  
[root@CentOS7.3 backup]#ls
2017-09-15_23-29-25
[root@CentOS7.3 backup]#cd 2017-09-15_23-29-25/
[root@CentOS7.3 2017-09-15_23-29-25]#ls
backup-my.cnf  mydb   performance_schema  xtrabackup_binlog_info  xtrabackup_info
ibdata1        mysql  test                xtrabackup_checkpoints  xtrabackup_logfile
[root@CentOS7.3 2017-09-15_23-29-25]#cat xtrabackup_binlog_info 
master-bin-log.000005   245
[root@CentOS7.3 2017-09-15_23-29-25]#cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1611224
last_lsn = 1611224
compact = 0
recover_binlog_info = 0
[root@CentOS7.3 2017-09-15_23-29-25]#cat backup-my.cnf 
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0
server_id=0

redo_log_version=0

2、希图(prepare)三个一心备份

平常意况下,在备份达成后,数据尚且不可能用来复苏操作,因为备份的多少中大概会蕴藏尚未提交的事体或已经付诸但绝非同步至数据文件中的事务。由此,当时数据文件仍管理不均等状态。“计划”的基本点效率就是通过回滚未提交的作业及联合已经交付的业务至数据文件也使得数据文件处于黄金年代致性状态。使用innobakupex命令的--apply-log选项可用于贯彻上述意义。

[root@CentOS7.3 ~]#innobackupex --apply-log /app/backup/2017-09-15_23-29-25/    #指定完全备份文件的路径
...
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1611304
170915 23:30:19 completed OK!           #执行完毕后出现这一行表示准备成功,未出现这一行表示失败。

在落到实处“筹划”的进度中,innobackupex平常仍然是能够利用--use-memory选项来钦点其得以选拔的内部存款和储蓄器的轻重,暗中认可通常为100M。假如有丰硕的内部存款和储蓄器可用,能够多分割一些内存给prepare的长河,以巩固其完结速度。

3、从二个一心备份中回复数据

[root@CentOS7.3 ~]#systemctl stop mariadb       #恢复不用启动MySQL
[root@CentOS7.3 ~]#rm -rf /var/lib/mysql/*      #删除数据库的数据目录下所有文件

(六卡塔 尔(英语:State of Qatar)备份工具:

注意:xtrabackup在复制时只会复制你的多寡和布局文件,并不会复制日志文件,提议不要和小编同样一贯删除全数文件,提出保留全体的日志文文件。

innobackupex命令的--copy-back选项用于施行复苏操作,其通过复制全数数据相关的文本至mysql服务器DATADITiggo目录中来实施苏醒进度。innobackupex通过backup-my.cnf来得到DATADIWrangler目录的有关新闻不须要人工内定。

[root@CentOS7.3 ~]#innobackupex --copy-back /app/backup/2017-09-15_23-29-25/    #--copy-back 后面指定备份文件路径
170916 09:18:50 [01]        ...done
170916 09:18:50 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
170916 09:18:50 [01]        ...done
170916 09:18:50 completed OK!           #出现这一行表示成功恢复

在意:当数据苏醒至DATADIR目录未来,还要求保险全体数据文件的属主和属组均为科学的客户,如mysql,在运营mariadb早先还亟需事先改善数据文件的属主和属组。

[root@CentOS7.3 mysql]#chown -R mysql.mysql /var/lib/mysql/   
                    #修改/varlib/mysq目录的属主和属组为mysql用户并递归修改其子目录和文件。
[root@CentOS7.3 mysql]#systemctl start mariadb  #重启mariadb
[root@CentOS7.3 mysql]#mysql
MariaDB [(none)]> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| mydb               |          #数据库已恢复
| mysql              |
| performance_schema |
| test               |
 -------------------- 
5 rows in set (0.00 sec)

MariaDB [(none)]> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> show tables ;
 ---------------- 
| Tables_in_mydb |
 ---------------- 
| test1          |
 ---------------- 
1 row in set (0.00 sec)

MariaDB [mydb]> select * from test1;        #test1表内的数据也已恢复
 ---- --------------- ------ 
| id | name          | age  |
 ---- --------------- ------ 
|  1 | Zhang Xiaofan |   18 |
|  2 | Lu Xueqi      |   17 |
|  3 | Bi Yao        |   16 |
|  4 | Xiao Ming     |   10 |
|  5 | Xiao Hong     |    9 |
 ---- --------------- ------ 
5 rows in set (0.00 sec)

4、使用innobackupex举行增量备份

各样InnoDB的页面都会蕴藏三个LSN音信,每当相关的多寡发生变动,相关的页面的LSN就能够自动拉长。那正是InnoDB表能够扩充增量备份的底工,即innobackupex通过备份上次统统备份之后产生退换的页面来落实。

运用如下命令

innobackupex --incremental /backup --incremental-basedir=BASEDIR

里头,BASEDI讴歌RDX指的是截然备份所在的目录,此命令试行落成后,innobackupex命令会在/backup目录中开创多少个新的以时间命名的目录以寄存全部的增量备份数据。其余,在实践过增量备份之后再一遍实行增量备份时,其--incremental-basedir应该本着上二次的增量备份所在的目录。倘诺要做差距备份还钦点完全备份所在的目录就能够了

[root@CentOS7.3 backup]#innobackupex --incremental /app/backup/ --incremental-basedir=/app/backup/2017-09-15_23-29-25
...
170916 09:52:55 completed OK!           #出现这一行表示成功
(1)mysqldump
  • mysql服务自带的备份工具;逻辑备份工具
  • 全盘、部分备份
    • InnoDB:热备
    • MyISAM:温备
  • 备份计策:全量 binlog
亟需在意的是,增量备份仅能采取于InnoDB或XtraDB表,对于MyISAM引擎的表来讲,试行增量备份时其实举办的是一点一滴备份。

5、“准备”(prepare)增量备份

筹算增量备份与筹算完全备份有着一些不等,极其要注意的是:

(1)须要在种种备份(饱含完全和顺序增量备份)上,将曾经付出的事务实行“回看”。“重播”之后把具有的备份数据将合併到完全备份上。

(2)基于全体的备份将未提交的业务进行“回滚”。

需求增加--redo-only(卡塔 尔(英语:State of Qatar)参数,到最终叁次增量备份要去掉--redo-only

[root@CentOS7.3 backup]#innobackupex --apply-log --redo-only /app/backup/2017-09-15_23-29-25/

[root@CentOS7.3 backup]#innobackupex --apply-log --redo-only /app/backup/2017-09-15_23-29-25/ --incremental-dir=/app/backup/2017-09-16_09-52-52/

[root@CentOS7.3 backup]#innobackupex --apply-log --redo-only /app/backup/2017-09-15_23-29-25/ --incremental-dir=/app/backup/2017-09-16_10-01-21

其中/app/backup/2017-09-15_23-29-25/指的是一心备份所在的目录,而/app/backup/2017-09-16_09-52-52/指的是首先次增量备份的目录,/app/backup/2017-09-16_10-01-21指的是第一遍增量备份的目录,其余依次类推,即只要有数次增量备份,每叁次都要施行如上操作;

最后叁个增量命令达成后,要在全量备份上做“回滚”操作。

[root@CentOS7.3 backup]#innobackupex --apply-log /app/backup/2017-09-15_23-29-25/
...
170916 10:11:55 completed OK!           #出现这一行标识完成

6、Xtrabackup的“流”及“备份压缩”功用

Xtrabackup对备份的数据文件扶植“流”成效,即能够将备份的多少经过STDOUT传输给tar程序举行归档,实际不是默许的第一手保存至某备份目录中。要使用此意义,仅要求利用--stream选项就可以。如:

innobackupex --stream=tar  /backup | gzip > /backup/`date  %F_%H-%M-%S`.tar.gz

如故也足以采用相像如下命令将数据备份至别的服务器:

innobackupex --stream=tar  /backup | ssh user@www.magedu.com  "cat -  > /backups/`date  %F_%H-%M-%S`.tar" 

其余,在实行本地备份时,还足以行使--parallel选项对八个公文举办并行复制。此选项用于钦定在复制时起步的线程数目。当然,在实际开展备份时要接受此效率的便利性,也急需启用innodb_file_per_table选项或分享的表空间通过innodb_data_file_path选项存款和储蓄在八个ibdata文件中。对某意气风发数据库的四个文本的复制不能够利用到此功效。其差十分的少利用格局如下:

innobackupex --parallel  /path/to/backup

再正是,innobackupex备份的数据文件也得以积存至远程主机,那能够选用--remote-host选项来落到实处:

innobackupex --remote-host=root@www.magedu.com  /path/IN/REMOTE/HOST/to/backup

7、导入或导出单张表

暗中认可情形下,InnoDB表不可能经过平素复制表文件的方法在mysql服务器之间实行移植,就算使用了innodb_file_per_table选项。而接受Xtrabackup工具得以兑现此种效率,不过,那个时候要求“导出”表的mysql服务器启用了innodb_file_per_table选项(严峻来说,是要“导出”的表在其创建在此之前,mysql服务器就启用了innodb_file_per_table选项卡塔 尔(英语:State of Qatar),况且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。

(1)“导出”表
导出表是在备份的prepare阶段进行的,由此,意气风发旦完全备份完结,就足以在prepare进程中经过--export选项将某表导出了:

innobackupex --apply-log --export /path/to/backup

此命令会为各样innodb表的表空间创设三个以.exp结尾的文本,这么些以.exp结尾的公文则足以用来导入至别的服务器。

(2)“导入”表
要在mysql服务器上导入来自于别的服务器的某innodb表,要求先在时下服务器上开创叁个跟原表表结构相仿的表,而后技术贯彻将表导入:

mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;

然后将此表的表空间删除:

mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;

接下去,未来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的多寡目录,然后接纳如下命令将其“导入”:

mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;

8、使用Xtrabackup对数据库举行一些备份

Xtrabackup也可以完结部分备份,即只备份有个别或一些钦点的数据库或某数据库中的有个别或某个表。但要使用此作用,必须启用innodb_file_per_table选项,即每张表保存为三个独自的文书。同期,其也不援救--stream选项,即不扶植将数据经过管道传输给任何程序开展拍卖。

其它,还原部分备份跟过来全体数据的备份也大有差异,即你不能够通过轻松地将prepared的部分备份使用--copy-back选项直接复制回数据目录,而是要透过导入表的样子来贯彻复苏。当然,有些情状下,部分备份也能够平素通过--copy-back实行恢复生机,但这种方法还原而来的数目超级多会发生多少分裂等的主题素材,因而,无论怎么样不推荐使用这种艺术。

(1)创造部分备份

创办部分备份的点子有三种:正则表明式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)。

(a)使用--include
接受--include时,要求为其钦命要备份的表的完好名称,即形如databasename.tablename,如:

innobackupex --include='^mageedu[.]tb1'  /path/to/backup

(b)使用--tables-file
此选项的参数需若是贰个文书名,此文件中每行李包裹涵二个要备份的表的完全名称;如:

 echo -e 'mageedu.tb1nmageedu.tb2' > /tmp/tables.txt
 innobackupex --tables-file=/tmp/tables.txt  /path/to/backup

(c)使用--databases
此选项选拔的参数为数据名,假如要钦点多少个数据库,互相间供给以空格隔离;同不平日间,在内定某数据库时,也得以只内定在这之中的某张表。其它,此选项也足以承当三个文本为参数,文件中每后生可畏行为贰个要备份的指标。如:

innobackupex --databases="mageedu testdb"  /path/to/backup

(2)收拾(preparing)部分备份
prepare部分备份的长河看似于导出表的进程,要选择--export选项实行:

innobackupex --apply-log --export  /pat/to/partial/backup

此命令实施进度中,innobackupex会调用xtrabackup命令从数据字典中移除缺点和失误的表,因而,会展示杰出多关于“表空中楼阁”类的警示音信。同一时候,也会显得出为备份文件中设有的表创制.exp文件的相关音讯。

(3)还原部分备份
光复部分备份的经过跟导入表的经过同样。当然,也足以透过直接复制prepared状态的备份直接至数据目录中贯彻苏醒,不要那时候供给数据目录处于同黄金年代状态。

(2)cp/tar
  • lvm2:快速照相(央求多少个大局锁卡塔 尔(英语:State of Qatar),之后立刻释放锁,达到差不离热备的成效;物理备份
  • 在乎:无法仅备份数据文件;要同一时候备份工作日志
  • 前提:须要数据文件和事务日志位于同多个逻辑卷
(3)Innobackup/xtrabackup
  • 由Percona提供,开源工具,接济对InnoDB做热备,物理备份工具
  • 支撑以下备份方式
    全然备份、部分备份
    统统备份、增量备份
    一心备份、差距备份
  • 备份计策
    全量 差异 binlog
    全量 增量 binlog

(七卡塔 尔(英语:State of Qatar)mysqldump备份工具的运用:

(1卡塔尔备份机制:逻辑备份,即备份内容为SQL脚本
  • 库:CREATE DATABASE
  • 表:CREATE TABLE
  • 数据:INSERT INTO
(2卡塔 尔(阿拉伯语:قطر‎具体使用语法:
  • 备份:

    • mysqldump [OPTIONS] database [tables]:
      备份单库,能够只备份个中的风流浪漫局地表(部分备份卡塔 尔(阿拉伯语:قطر‎,备份内容中不设有创立库的SQL语句
    • mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]:
      备份多库,备份单库也引入应用
    • mysqldump [OPTIONS] --all-databases [OPTIONS]:
      备份全体库
  • MyISAM存款和储蓄引擎:援救温备,备份时要锁定表
    -x, --lock-all-tables:锁定全体库的具备表,读锁
    -l, --lock-tables:锁定钦定库全体表

  • InnoDB存款和储蓄引擎:扶助温备和热备
    --single-transaction:成立三个作业,基于此快速照相实行备份

  • 其余选项:
    -Murano, --routines:备份钦定库的囤积进程和仓库储存函数;
    --triggers:备份钦点库的触发器;
    -E, --events:备份内定度的时间调治器
    --master-data[=#]
    1:记录为CHANGE MASTEXC90 TO语句,此语句不被批注
    2:记录为CHANGE MASTE君越 TO语句,此语句被解说
    --flush-logs:锁定表完毕后,即举办日志刷新操作

(3卡塔 尔(英语:State of Qatar)实验:使用mysqldump完成“完全 binlog”情势的备份与回复操作
  • 手续1:远程备份数据库
    mysqldump -uroot -pcentos -h 192.168.136.230 --single-transaction -R --triggers -E --master-data=2 --flush-logs --databases hellodb > /root/hellodb-$(date %F-%H:%M:%S).sql

  • 步骤2:查看备份文件,个中找到"CHANGE MASTER TO"语句,能够观察在全量备份时正在专门的职业的二进制日志和所处地点

图片 10

  • 步骤3:那是备份服务器上原有数据库,能够见见没有备份的hellodb数据库

图片 11

  • 步骤4:那个时候在原数据库中打开增、删、改等操作,本例中增加了两条记下

图片 12

  • 手续5:在备份服务器上导入全量备份的SQL脚本:
    mysql -uroot -pcentos < /root/hellodb-2017-11-14-10:45:45.sql
    能够看来成功复苏了hellodb数据库,可是全量备份后增添的两条记下错过

图片 13

  • 手续6:在原数据库少校二进制文件导出,并传导至备份服务器
mysqlbinlog master-log.000007 > /tmp/binlog.sql
scp /tmp/binlog.sql 192.168.136.130:/root
  • 手续7:将二进制日志备份文件导入,能够看看错失的记录也上涨了
    mysql -uroot -pcentos < /root/binlog.sql

图片 14

  • 注意:
    鉴于手续第11中学的远程备份操作使用了选项"flush-logs" 强制刷新日志,故导入的二进制日志备份文件全体为全量备份后的操作;
    若步骤第11中学从不加多此选项,则需求在导出二进制日志备份时使用''mysqlbinlog -j ##"钦赐从第##行开始,保险二进制日志备份记录与全量备份未有重新,##能够从全量备份文件的"CHANGE MASTE奥迪TTS TO"语句中观察,如步骤2所示

(八卡塔 尔(阿拉伯语:قطر‎Xtrabackup备份工具的应用

(1卡塔 尔(阿拉伯语:قطر‎Xtrabackup备份选取物理备份:
  • 概况备份,速率快、可相信
  • 备份达成后活动校验备份结果集是不是可用
  • 回复速度快
(2卡塔 尔(英语:State of Qatar)具体行使语法:
  • innobackupex:对用C语言编写的xtrabackup程序的Perl脚本封装工具

  • 完全 binlog
    备份:innobackupex --user --password= --host= /PATH/TO/BACKUP_DIR
    准备:innobackupex --apply-log /PATH/TO/BACKUP_DIR
    复原:--copy-back,注意还原须要在mysqld主机本地开展,mysqld服务不能够开发银行

(3卡塔 尔(阿拉伯语:قطر‎实验:使用xtrabackup达成“完全 增量 binlog ”方式的备份与回复操作
  • 着力命令:

    • 备份:
      全量备份:
      innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
      增量备份:
      innobackupex --user=DBUSER --password=DBUSERPASS --incremental /backup --incremental-basedir=BASEDIR

    • 准备:
      独有全量备份时:
      innobackupex --apply-log --redo-only BASEDIR
      有增量备份时,先举行增量备份合并入全量备份的操作:
      innobackupex --apply-log --redo-only BASEDIR --incremental-dir=INCREMENTAL-DIR

    • 恢复:
      innobackupex --copy-back BASEDIR

  • 手续1.1: 实施对数据库的全量备份

mkdir -pv /mydata/backups
innobackupex --user=root --password=centos --host=localhost /mydata/backups/
ls -l /mydata/backups      // 查看目录下以时间命名的备份目录
  • 手续1.2:对数据库的表增添一条记下模拟对数据库的更改,推行对数据库的增量备份
MariaDB [(none)]> INSERT INTO students (NAME,AGE,GENDER) VALUES ('Philip',17,'M')
innobackupex --user=root --password=centos --host=localhost --incremental /mydata/backups/ --incremental-basedir=/mydata/backups/2017-11-14_14-22-51/

图片 15

  • 手续1.3:再对数据库的表增添一条记下,实施对数据库的第2次增量备份。
MariaDB [(none)]> INSERT INTO students (NAME,AGE,GENDER) VALUES ,('Nancy',19,'F');
innobackupex --user=root --password=centos --host=localhost --incremental /mydata/backups/ --incremental-basedir=/mydata/backups/2017-11-14_14-30-19/

图片 16

  • 步骤1.4:对数据库的表记录进行改造后,数据库服务器服务不可用。输出第2次增量备份后的二进制日志,至此备份操作全体产生
MariaDB [(none)]> UPDATE students SET DESP='physical' WHERE AGE>22;
// 查询最后一次增量备份后二进制日志事件位置
cat /mydata/backups/2017-11-14_14-37-45/xtrabackup_binlog_info
// 将之后的日志导出
mysqlbinlog -j 1026 /mydata/logs/master-log.000007 > /root/mybinlog.sql

图片 17

  • 步骤2.1:备份后的预备干活
// 依次将增量备份合并至全量备份上
innobackupex --apply-log --redo-only /mydata/backups/2017-11-14_14-22-51/
innobackupex --apply-log --redo-only /mydata/backups/2017-11-14_14-22-51/ --incremental-dir=/mydata/backups/2017-11-14_14-30-19/
innobackupex --apply-log --redo-only /mydata/backups/2017-11-14_14-22-51/ --incremental-dir=/mydata/backups/2017-11-14_14-37-45
// 最后对合并后的全量备份做整理准备
innobackupex --apply-log /mydata/backups/2017-11-14_14-22-51/
  • 手续2.2:将备份文件复制到崭新的数据库服务器上,至此还原前的备选干活到位
scp -r /mydata/backups/2017-11-14_14-22-51/ 192.168.136.130:/root
scp /root/mybinlog.sql 192.168.136.130:/root
  • 步骤3.1:在新服务器上恢复生机数据库,注意无法有别的数据库内容,服务更不可能开发银行
innobackupex --copy-back /root/2017-11-14_14-22-51/
ls -l /var/lib/mysql/     // 查看备份还原情况
chown -R mysql.mysql /var/lib/mysql/*
systemctl start mariadb

那儿重振旗鼓至最终三次增量备份时的图景

图片 18

  • 手续3.2:在新服务器上过来二进制日志

艺术1:三回导入SQL脚本

SET @@session.sql_log_bin=OFF;
mysql -uroot -pcentos < /root/mybinlog.sql
SET @@session.sql_log_bin=ON;

办法2:在mysql景况下履行SQL脚本

SET @@session.sql_log_bin=OFF;
mv /root/mybinlog.sql /tmp/
mysql -uroot -pcentos
MariaDB [(none)]> . /tmp/mybinlog.sql
SET @@session.sql_log_bin=ON;

此刻整整回复实现

图片 19

  • 步骤3.3:还原成功后,提议即刻全量备份三遍
mkdir -pv /mydata/backups
innobackupex --user=root --password=centos --host=localhost /mydata/backups/

三、MySQL Replication 复制:

(一)主从(MASTER/SLAVE)架构

  • 出于架议和公理的限制,数据库服务器不能透过分享存款和储蓄设备达成高可用
  • 数据库系统利用主从架构、异步复制的秘诀贯彻高可用
  • 主干架构:
    • Master:主节点主要承当写操作,日常独有三个
    • Slave:从节点主要肩负读,个数不限
  • 异步复制:
    • 从节点I/O线程:监视主节点的二进制日志,将立异复制到自己的交接日志中
    • 从节点SQL线程:将连通日志中的更新重播
    • 异步复制的性状,决定了中央结构中从节点的数量连接晚于主节点更新,数据的准头不高
    • 当从节点落后主节点更新过多时,只可以通过不常挂起从节点的劳动,恢复生机主节点的备份完结

(二卡塔尔国主从架构的分类

  • 黄金年代主多从:主肩负写操作,多从担任读操作
  • 生龙活虎从风流倜傥主:后生可畏主多从的简易版
  • 级联复制:
    黄金时代台服务器作为上层主节点的从节点,同不常间作为下层多个从节点的主节点;
    自己不实行多少的写操作,只需生成二进制日志即可;
    运用BLACKHOLE引擎达成
  • 循环复制
  • 双主复制
    两台服务器都以主节点,在实践写操作外,还监督对方的二进制日志更新中继日志用于重放同步;
    是因为两条写操作指令的实行前后相继顺序分化会吸引分裂的结果,故双主复制大概产生数据的混乱
  • 半二只复制

(三卡塔 尔(英语:State of Qatar)MySQL主从复制的得以完结

  • 步骤风流倜傥:主服务器的安装
ntpdate 172.18.0.1                // 时间同步
vim /etc/my.cnf.d/server.cnf      //[server]段落下添加如下两行
log_bin = master-log
server_id = 1
systemctl start mariadb
// mysql登录
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.136.%'' IDENTIFIED BY 'centos'    // 给从服务器的账号授权允许复制
MariaDB [(none)]> FLUSH PRIVILEGES;
SHOW MASTER STATUS;              // 查看当前二进制日志所在位置

图片 20

  • 步骤二:从服务器的安装
ntpdate 172.18.0.1                // 时间同步
vim /etc/my.cnf.d/server.cnf 
relay_log = relay-log
server_id = 2                     // 独一无二,不能与主服务器和其他从服务器相同
systemctl start mariadb
// mysql登录
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.136.230',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=497;
MariaDB [(none)]> START SLAVE IO_THREAD,SQL_THREAD;     // 开启复制
MariaDB [(none)]> SHOW SLAVE STATUSG                   // 查看当前从服务器状态

红框1:主服务器的地点,客户;
红框2:从服务器获取的主服务器二进制日志和所在地方;
红框3:从服务器IO和SQL线程的开启状态

图片 21

  • 手续3:测量检验MySQL的主从复制效率
    主服务器上加多数据库、表,增加表记录
MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [(none)]> USE mydb;
CREATE TABLE students (ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,NAME CHAR(20) NOT NULL,AGE TINYINT NOT NULL, GENDER ENUM('M','F'),DESP VARCHAR(50));
INSERT INTO students (NAME,AGE,GENDER) VALUE ('Alice',20,'F'),('Bob',22,'M');
SELECT * FROM students;

能够看来眼下主服务器的二进制日志地点已经发展至1054

图片 22

登陆从服务器,检查数据库同步成功

MariaDB [(none)]> SHOW DATABASES;
USE mydb;
MariaDB [mydb]> SELECT * FROM students;

图片 23

查看从服务器的事态消息

MariaDB [mydb]> SHOW SLAVE STATUSG

能够见见从服务器上显得的主服务器二进制文件和地方音信跟随主服务器进行了一只

图片 24

(四卡塔尔国MySQL主主复制的完毕

  • 配备必要:

    1. 几个节点各自都要开启binlog和relay log

    2. 为担保id自动增进且不冲突,必要自定义增进措施:

      • 概念多少个节点使用奇数id
        auto_increment_offset=1
        auto_increment_increment=2
      • 另一个节点使用偶数id
        auto_increment_offset=2
        auto_increment_increment=2
    3. 劳务运维后,都授权有复制权限的客商账号并各把对方内定为主节点

  • 手续1:节点1,配置并授权

vim /etc/my.cnf.d/server.cnf
server_id = 1
log_bin = master-log
relay_log = relay-log
auto_increment_offset = 1
auto_increment_increment = 2
systemctl start mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.136.%' IDENTIFIED BY 'centos';
MariaDB [(none)]> SHOW MASTER STATUS;

记录这时节点1二进制日志文件和任务

图片 25

  • 手续2:节点2,配置并授权
vim /etc/my.cnf.d/server.cnf 
server_id = 2
log_bin = master-log
relay_log = relay-log
auto_increment_offset = 2
auto_increment_increment = 2
systemctl start mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.136.%' IDENTIFIED BY 'centos';
MariaDB [(none)]> SHOW MASTER STATUS;

笔录那时候节点1二进制日志文件和职位

图片 26

  • 手续3:节点1,开启从节点,那个时候的主节点新闻从步骤2获得
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.136.130',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=428;
MariaDB [(none)]> START SLAVE IO_THREAD,SQL_THREAD;
MariaDB [(none)]> SHOW SLAVE STATUSG

图片 27

  • 手续4:节点2,开启从节点,那时候的主节点音信从步骤1获取
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.136.230',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='master-log.000004',MASTER_LOG_POS=428;
MariaDB [(none)]> START SLAVE IO_THREAD,SQL_THREAD;
MariaDB [(none)]> SHOW SLAVE STATUSG

图片 28

  • 步骤5:节点1,数据库开始数据如下
MariaDB [(none)]> USE mydb;
MariaDB [mydb]> SELECT * FROM students;

图片 29

  • 步骤6:节点1,对数码扩张记录,在节点第22中学询问已经同步成功
MariaDB [mydb]> INSERT INTO students (NAME,AGE,GENDER) VALUES ('John',19,'M'),('Penny',23,'F');
MariaDB [mydb]> SELECT * FROM students;

图片 30

  • 步骤7:节点2,对数据增添记录,在节点第11中学查询已经联合成功
MariaDB [(none)]> USE mydb;
MariaDB [mydb]> INSERT INTO students (NAME,AGE,GENDER) VALUES ('James',25,'M'),('Marry',24,'F');
MariaDB [mydb]> SELECT * FROM students;

图片 31

  • 能够窥见:差异节点的ID并不是自身依次增加,如节点2日增的记录并非从"2"后的"4"继续编码,而是以目前表中最大的号码"5"后多年来的偶数"6"作为编码,故主主复制的数据编码或然出现跳过ID的景色

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:最棒实践,MySQl备份与回复

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。