一.mysql的日志应用

(1)查询日志：general_log

记录查询语句，日志存储位置：
文件：file
表：table (mysql.general_log)
general_log={ON|OFF}
general_log_file=HOSTNAME.log
log_output={FILE|TABLE|NONE}

• desc general_log;——查看日志

  
  搜狗截图20171110213009.png

• 默认情况下日志为关闭状态，需要手动开启

  
  搜狗截图20171110213223.png
  

  *手动开启日志功能

  
  搜狗截图20171110213339.png
  注意：开启日志功能要在在global中设置

• 在默认情况下，修改表后内容会存在文件类型的日志中
  手动进行修改莫数据库中的表内容来进行验证

  
  搜狗截图20171110213918.png
  搜狗截图20171110213533.png
  

  cd /var/lib/mysql/
  cat node1.log

  
  搜狗截图 20171110214753.png

• 通过手动设置将日志存储为改为table

  
  搜狗截图20171110213757.png
  

  修改完成后，再次在数据库中执行命令时，内容就会存储在table中

• 修改成功后再执行一些命令来查看

  
  搜狗截图20171110215624.png

(2).慢查询日志：log_slow_queries

慢查询：运行时间超出指定时长的查询；
long_query_time

搜狗截图20171110220111.png

存储位置：
文件：FILE
表：TABLE，mysql.slog_log

搜狗截图20171110220301.png
log_slow_queries={ON|OFF}——set @@global.slow_query_log=ON
搜狗截图20171110220350.png
slow_query_log={ON|OFF}—— set @@global.log_slow_queries=ON;

注意：为了稳妥起见，要将slow_query_log和log_slow_queries都开启，他们功能类似，根据不同的数据库类型而设置成不同格式，所以在执行操作之前要将这两种方式都开启

搜狗截图20171110221420.png

(3).错误日志：log_error， log_warnings

记录如下几类信息：
1.mysqld启动和关闭过程中输出的信息；
2.mysqld运行中产生的错误信息；
3.event scheduler运行时产生的信息；
4.主从复制架构中，从服务器复制线程启动时产生的日志；
log_error=/var/log/mariadb/mariadb.log|OFF——开启错误日志不用写on而是直接写错误日志文件路径就是开启，关闭的话执行set @@global.log_error=OFF
默认情况下错误日志是开启的

搜狗截图20171110222302.png

查看错误日志内容

搜狗截图20171110222354.png
log_warnings={ON|OFF}

(4).二进制日志：binlog

1.概念：用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT）或改变后的结果（ROW），也可能是二者混合；
功用：“重放”
2.查看二进制日志格式
binlog_format={STATEMENT|ROW|MIXED}
STATEMENT：语句；
ROW：行；
MIXED：混编；

搜狗截图20171110223122.png

查看二进制日志文件列表：
SHOW MASTER|BINARY LOGS;

搜狗截图20171110223838.png
查看当前正在使用的二进制日志文件：
SHOW MASTER STATUS；
搜狗截图20171110223953.png
查看二进制 日志文件中的事件：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
搜狗截图20171110224519.png
在数据库中做一些操作，然后查看二进制日志内容
例：

use hellodb;
delete from students where id=23;
执行完后查看二进制日志

搜狗截图20171110225014.png
也可以挑选内容进行查看
搜狗截图20171110225210.png
当关闭二进制日志后，再执行的后续操作将不会出现在二进制文件中
退出mysql=数据库后，创建mkdir -pv /mydata/{logs,data}
然后 vim /etc/my.cnf.cd/server.cnf 搜狗截图20171110225820.png
cd /mydata/logs
搜狗截图20171110225914.png
注意不能直接用cat命令进行查看，因为生成的日志文件都是二进制格式的

• 查看二进制格式日志的方法
  mysqlbinlog master-log.000002

  
  搜狗截图20171111090825.png
  

  更多的使用方法可以采用man mysqlbinlog来进行查看
  例：可以直接跳过多少行来查看
  mysqlbinlog -j 316 master-log.000002

  
  搜狗截图20171111091348.png
  可以从第几行开始，到多少行结束的方式来查看
  mysqlbinlog -j 316 --stop-position 414 master-log.000002
  也可根据日志的时间来查看
  

  mysqlbinlog --start-datetime="2017-11-10 20:39:16" master-log.000002

  
  搜狗截图20171111091812.png
  服务器变量：
  log_bin=/PATH/TO/BIN_LOG_FILE
  只读变量；
  session.sql_log_bin={ON|OFF}
  控制某会话中的“写”操作语句是否会被记录于日志文件中；
  max_binlog_size=1073741824——设置二进制日志存储内容的大小
  sync_binlog={1|0}——是否同步二进制日志
  以上内容如果想永久保存就要写入配置文件中，否则重启就会消失

(5).中继日志：relay_log

从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；

(6).事务日志：innodb_log

事务型存储引擎innodb用于保证事务特性的日志文件：
redo log
undo log

二.备份和恢复（数据）

(1)备份：存储的数据副本；
原始数据：持续改变；
(2)恢复：把副本应用到线上系统；
仅能恢复至备份操作时刻的数据状态；
时间点恢复：
binary logs;
(3)为什么备份？
灾难恢复：硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、...
测试；
(4)备份时应该注意事项
能容忍最多丢失多少数据；
恢复数据需要在多长时间内完成；
需要恢复哪些数据；
(5)备份类型
备份的数据集的范围：
完全备份和部分备份
完全备份：整个数据集
部分备份：数据集的一部分，比如部分表；

• 全量备份:完全备份,如果备份数据较大不建议经常使用完全备份，备份的策略可设定为一星期一次或是一个月一次，因为每备份一次会消耗很长时间和资源
• 增量备份：仅备份自上一次完全备份或增量备份以来变量的那部数据；
• 差异备份：仅备份自上一次完全备份以来变量的那部数据；
• 物理备份：复制数据文件进行的备份；

• 逻辑备份：从数据库导出数据另存在一个或多个文件中；
  根据数据服务是否在线：
  热备：读写操作均可进行的状态下所做的备份；
  温备：可读但不可写状态下进行的备份；
  冷备：读写操作均不可进行的状态下所做的备份；
  (6)备份需要考虑因素
  锁定资源多长时间？
  备份过程的时长？
  备份时的服务器负载？
  恢复过程的时长？
  (7)备份策略
  全量+差异 + binlogs
  全量+增量 + binlogs
  备份手段：物理、逻辑
  备份的是数据
  二进制日志、InnoDB的事务日志；
  代码（存储过程、存储函数、触发器、事件调度器）
  服务器的配置文件
  (8)备份工具
  mysqldump：mysql服务自带的备份工具；逻辑备份工具；
  完全、部分备份；
  InnoDB：热备；
  MyISAM：温备；
  cp/tar
  lvm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份；
  注意：不能仅备份数据文件；要同时备份事务日志；
  前提：要求数据文件和事务日志位于同一个逻辑卷；
  xtrabackup：
  由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具；
  完全备份、部分备份；
  完全备份、增量备份；
  完全备份、差异备份；
  mysqlhotcopy
  select：
  备份：SELECT cluase INTO OUTFILE 'FILENAME';
  恢复：CREATE TABLE
  导入：LOAD DATA
  InnoBase：Innodb --> XtraDB, Innobackup --> Xtrabackup
  (9)备份策略：
  xtrabackup：
  全量+差异+binlog
  全量+增量+binlog
  mysqldump：
  全量+binlog
  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] # 备份所有库；

  
  搜狗截图20171111113716.png
  搜狗截图20171111114026.png
  

  MyISAM存储引擎：支持温备，备份时要锁定表；
  -x, --lock-all-tables：锁定所有库的所有表，读锁；
  -l, --lock-tables：锁定指定库所有表；
  InnoDB存储引擎：支持温备和热备；
  --single-transaction：创建一个事务，基于此快照执行备份；
  其它选项：
  -R, --routines：备份指定库的存储过程和存储函数；
  --triggers：备份指定库的触发器；
  -E, --events：
  --master-data[=#]
  1：记录为CHANGE MASTER TO语句，此语句不被注释；=1
  2：记录为CHANGE MASTER TO语句，此语句被注释；=2
  --flush-logs：锁定表完成后，即进行日志刷新操作；

实验：通过一些演示来加深印象

1.首先在另一个节点主机上执行
mysqldump -uroot -h172.18.254.242 -pmagedu --single-transaction -R --triggers -E --databases hellodb > /root/hellodb.sql
将主服务器数据库此时的操作内容存储到本地中并命名为hellodb.sql
2.将主数据库中的内容导入另一个数据库中
mysql -uroot -hlocalhost < hellodb.sql
开启mysql后发现主数据库上的hellodb数据库的内容导入了本数据库中

搜狗截图20171111115104.png

3.加入刷新日志功能，并重新储存到一个新的以时间格式定义的文件中
mysqldump -uroot -h172.18.254.242 -pmagedu --single-transaction -R --triggers -E --databases hellodb --master-data=2 --flush-logs > /root/hellodb-$(date +%F-%H-%M-%S).sql

搜狗截图20171111115510.png
4.关闭远程服务器数据库
systemctl stop mariadb
rm -rf /var/lib/mysql/*
systecmtl start mariadb
开启mysql
搜狗截图20171111143444.png
5.恢复数据

mysql </root/ hellodb-2017-11-12-10-58-36.sql

搜狗截图20171111143919.png
注意：如果此时在主数据库中执行新的操作，那么远程服务器的数据库上将不会显示新的操作内容，如果想显示最新的操作内容应该将主服务器的二进制日志传送到远程服务器上，然后将二进制日志文件导入mysql数据库中再查看，主服务的数据库的二进制日志一定要先开启 搜狗截图20171111145918.png 搜狗截图20171111150035.png
搜狗截图20171111150419.png
在远程数据库上首先systemctl stop mariadb
rm -rf /var/lib/mysql/*
systemctl start mariadb
开启mysql后之前导入的数据都被清空
此时执行mysql </root/hellodb-2017-11-12-14-49-51.sql
再次开启后不能看见新执行的内容
解决办法是在主服务器上将生成的二进制日志文件scp到远程
mysqlbinlog master-log.000007 > /tmp/binlog.sql
scp /tmp/binlog.sql 172.18.25.62:/root/
数据库上，然后在远程数据库上执行mysql </binlog.sql
再次开启mysql后可以看见新更新的内容
搜狗截图20171111152222.png

(10)xtrabackup

该软件属于percona开发，官网：www.percona.com
实现全量+差异+binlog ，全量+增量+binlog
1.安装percona——官网下载，或是在ftp上下载
percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm
针对innodb支持热备份增量，而myisam支持温备全量
2.备份的实现
在主数据库上创建一个目录 mkdir /mydata/backups
innobackupex --user=root --password=magedu --host=localhost /mydata/backups/——进行备份到指定目录里
cd /mydata/backups
cd 2017-11-11_16-32-51/

搜狗截图20171111171432.png
3.在主数据上进行一些修改，修改完后做增量备份
innobackupex --user=root --password=magedu --host=localhost --incremental /mydata/backups/ --incremental-basedir=/mydata/backups/2017-11-11_16-32-51——建立第一个增量备份
搜狗截图20171111172234.png
然后建立第二个增量，还是先在主数据库上做一些修改
再执行innobackupex --user=root --password=magedu --host=localhost --incremental /mydata/backups/ --incremental-basedir=/mydata/backups/2017-11-11_16-38-00
搜狗截图20171111172549.png
但是在此时我们要考虑这么一种情况那就是如果还未来的及做增量备份时服务就崩溃了，那么我们就要在最新的二进制日志中保存下来
步骤：cd /mydata/backups/2017-11-11_16-41-24
搜狗截图20171111174020.png
mysqlbinlog -j 667 /mydata/logs/master-log.000007 > /root/mybinlog.sql
此时设置全量加增量方式备份完毕，如果要将备份文件传到远程主机上还要在本机上执行prepare操作，即先在本机上将增量合成一个完整的全量再传到远程主机上去
具体过程：
(1)先将远程服务器数据库关闭并rm -rf /var/lib/mysql
(2)在主机服务器上的/mydata/backups/2017-11-11_16-32-51/下执行innobackupex --apply-log --redo-only ./
innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/backups/2017-11-11_16-38-00
innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/backups/2017-11-11_16-41-24
innobackupex --apply-log ./ ——此步骤一定要做
此时将增量和全量合并成一个新的全量并复制到远程数据库上
搜狗截图20171111194544.png
(3)在远程服务器上库中
chown -R mysql.mysql /var/lib/mysql/*
systemctl start mariadb
此时不适用 mysql < mybinlog.sql的方式来导入，而是将mybinlog,sql > /tmp目录下，这样其他用户也可以访问
(4)开启mysql
use hellodb
. /tmp/mybinlog.sql
此时查看新修改的内容
select * from students where age=28;
搜狗截图20171111195709.png

• 注意：在主数据库的/mydata/backups/中的 2017-11-11_16-32-51已经做了回滚，因此要建立新的全量备份
  innobackupex --user=root --password=magedu --host=localhost /mydata/backups/——新的全量备份
  (5)如果只做全量的话
  首先innobackupex --user=root --password=magedu --host=localhost /mydata/backups
  scp -r /mydata/backups/2017-11-11_16-10-53/ 172.18.25.62:/root
  在远程数据库上，关闭mariadb rm -rf /var/lib/mysql/*
  在远程数据库上执行 cd 2017-11-11_16-32-51/
  innobackupex --copy-back ./
  cd /var/lib/mysql/
  chown -R mysql.mysql ./
  systemctl start mariadb
  全量备份成功

三.mysql Replication

• MySQL复制方式：主从复制，主主复制
  (1)主从复制master/slave
  Master: write/read——主数据库具有读写功能
  Slaves: read——从服务器只有读权限
  (2)设置主从复制的原因：
  冗余：promte（提升为主），异地灾备
  人工
  工具程序：MHA扩展：转移一部分“读”请求；
  支援安全的备份操作；
  测试；
  (3)主/从架构：
• 异步复制：
  1.一主多从；
  2.一从一主；
  3.级联复制；主服务器——从服务器，该从服务又是另一个服务器的主服务器
  4.循环复制；
  5.双主复制；
• 半同步复制：
  一从多主：每个主服务器提供不同的数据库；
  (4)配置：
  时间同步；
  复制的开始位置：
  从0开始；
  从备份中恢复到从节点后启动的复制，复制的起始点备份操作时主节点所处的日志文件及其事件位置；
  注意：从服务器的版本号高于主服务器的版本号；
  1.主服务器：
  配置文件my.cnf
  server_id=#
  log_bin=log-bin
  启动服务：
  mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
  mysql> FLUSH PRIVILEGES;
  2.从服务器：
  配置文件my.cnf
  server_id=#
  relay_log=relay-log
  read_only=ON
  启动服务：
  mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
  mysql> START SLAVE [IO_THREAD|SQL_THREAD];
  mysql> SHOW SLAVE STATUS;

3.通过实验来进行验证
步骤：

• 首先对配置文件进行设置 vim /etc/my.cnf.d/server.cnf

  
  搜狗截图20171112150153.png
• 在主服务器上做恢复数据
  cd /mydata/backups/2017-11-11_16-32-5
  执行 innobackupex --copy-back ./——恢复成功
  cd /var/lib/mysql
  chown -R mysql.mysql ./
• 启动Mysql数据库
  连接数据库mysql -uroot -pmagedu
  执行授权grant replication client,replication slave on . to 'repluser'@'172.18.%.%' identified by 'replpass';
  flush privileges
  搜狗截图20171112154008.png
• 进行备份并将备份数据复制到从服务器上
  采用逻辑备份方式
  mysqldump -uroot -magedu --all-databases -x(指定温备方式) -E -R --trigger --flush-log --masater-data=2 > /root/all.sql
  scp /root/all.sql 172.18.25.62:/root

• 在从服务器上执行vim /etc/my.cnf.d/server.cnf

  
  搜狗截图20171112155537.png
  

  注意如果采用逻辑备份方式备份数据库内容那么一定要先开启mySQL后再恢复数据
  systemctl start mariadb
  mysql < all.sql
  开启mysql

  
  搜狗截图20171112155832.png

• 设置从服务器数据库的配置
  head -30 all.sql

  
  搜狗截图20171112160123.png
  

  进入数据库
  设置change master to master_host='172.18.254.242',master_user='repluser',master_password='replpass',master_log_file='master-log.000010',master_log_pos=245;
  查看从服务器状态 show slave status\G

  
  搜狗截图20171112160955.png
  搜狗截图20171112161100.png
  现在在主服务器上进行一些修改
  use hellodb;
  

  select * from students;
  delete from students where name='agan';

  
  搜狗截图20171112161907.png
  在从服务器上进行查看
  搜狗截图20171112162012.png
  实现主从复制的目的
  注意：一般情况下要在从服务器上的配置文件中设置read_only=ON——但只对普通用户有效，对root和超级用户无效
  4.实现主主复制方式
  首先要在配置文件中进行设置 搜狗截图20171112163023.png
  在另一个服务器上
  搜狗截图20171112163129.png
  开启数据库
  在主服务器上执行change master to master_host='172.18.25.62',master_user='repluser',master_password='replpass',master_log_file='mater-log.000001'(对方的文件),master_log_pos=245(开始的位置);
  

  start slave;
  show slave status\G
  在另一个服务器上执行
  change master to master_host='172.18.254.242',master_user='repluser',master_password='replpass',master_log_file='mater-log.000003'对方的文件),master_log_pos=245(开始的位置);
  start slave;
  show slave status\G
  实现双主模式
  当在其中一台服务器上执行例如：
  use hellodb;
  select * from students;
  delete from studens where id=10;
  再次执行select * from students
  然后在另一台服务器上查看，发现现实内容和另一台服务器相同
  当在server_id=1的主服务器上执行 insert into students (name.age,gender,major) values('am',18,'M','chushi'),('dasha',24,'F','fuwuyuan');
  查询发现id号会从207开增加，并且步进为2
  而在server_id=2的数据库上查看也是相同的，但是此时在该服务器上执行insert into students (name.age,gender,major) values('aam',22,'F','jishi'),
  再次查看发现id号会从210开始增加，而208号变为间隙ID

复制时应该注意的问题：

    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
                
    3、从服务器意外中止时尽量避免自动启动复制线程
            
    
    4、从节点：设置参数
        sync_master_info=ON
        
        sync_relay_log_info=ON