11月10日 mysql进阶2 备份和恢复、主从复制

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   |
+---------------+-------+

2、mysql的备份和恢复

1、备份类型：

备份的数据集的范围：
完全备份和部分备份
完全备份：整个数据集；
部分备份：数据集的一部分，比如部分数据库或者部分表；
全量备份、增量备份、差异备份：
全量备份：完全备份
增量备份：仅备份自上一次完全备份或增量备份以来变量的那部数据；
差异备份：仅备份自上一次完全备份以来变量的那部数据；

无标题.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的事物隔离级别下，别人修改了提交了你也看不到，此刻备份时可以保证各事物的时间点一致，不然备份的过程中别人可能在修改数据，就会造成各事物的修改时间不是在同一时间，使备份不可用；
其它选项：
-R, --routines：备份指定库的存储过程和存储函数；
--triggers：备份指定库的触发器；
-E, --events：备份事件调度器
--master-data[=#]：指明备份时二进制日志文件的名字和所处的位置，备份之后的数据就可以从二进制日志文件的此处位置进行重放
1：记录为CHANGE MASTER TO语句，此语句不被注释；
2：记录为CHANGE MASTER 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可能要重新设定并且可能和事物日志冲突，此时就不要在配置文件中设定了。

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权限的用户还是可以写的。

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，但仅对非SUPER权限的用户有效；
要想阻止所有用户可以申请一个全局读锁：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

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，不然开启不了复制操作

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验证，就需要在主服务器上也创建证书和私钥文件
将证书和私钥文件在主服务器的配置文件中指定
一般情况下主服务器端不需要配置证书和私钥，因为是从服务器去连接主服务器进行复制，所以要求从服务器提供证书和私钥文件进行认证。

最后编辑于：2018.07.19 13:27:58

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,636评论 5赞 468
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 83,890评论 2赞 376
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,680评论 0赞 330
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,766评论 1赞 271
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,665评论 5赞 359
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,045评论 1赞 276
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,515评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,182评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,334评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,274评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,319评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,002评论 3赞 315
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,599评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,675评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,917评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,309评论 2赞 345
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,885评论 2赞 341