MySQL主从备份(1)v1.0

56341

Mysql>SHOW GLOBAL VARIABLES LIKE ‘%log%’;                     查看mysql与log相关的变量

日志:

查询日志:general_log     

慢查询日志:;log_show_queries

错误日志:log_error,log_warnings

二进制日志:binlog_*

中续日志:relay_log

事物日志:innodb_log_*

**********************************

1.查询日志: 用来记录查询日志 一般不开启 日志存储位置

a)日志存储位置:

i.文件中:file

ii.MySQL表中 Tables_in_.general_log

General_log=’ON|OFF’        永久有效修改配置文件

Feneral_log_file=              文件记录位置

Log_output={FILE|TABLE|NONE}        log存储的位置 决定上面两个参数

2.慢查询:

a)存储位置:

i.文件:FILE

ii.表: 默认位置 mysql.slog_log

b)慢查询: 运行时间超出指定时长的查询

i.SHOW GLOBAL VARIABLES LIKE ‘%long%’

Long_query_time 超过此时间为慢查询

Ii.       Log_slow_querier={ON|OFF}       打开关闭慢查询

Iii.       Slow_query_log={ON|OFF}      

Iv.       Slow_query_log_file       指定存储位置

Log_slow_filiter        过滤器

Log_slow_queries

Log_slow_verbosity

3.错误日志

记录信息:

(1)MySQLd启动和关闭过程中的 输出的信息:              并不是错误信息

(2)MySQLd运行中产生的错误信息:

(3)Event scheduler(事件调度器,计划任务)运行产生的信息:

(4)主从复制架构中,从服务器的复制线程启动时产生的日志:

SHOW VARIABLES LIKE ‘%error%’

Log_error=                     日志位置

Log_warnings={ON|OFF}                     是否记录警告信息

4.二进制日志: 一般只在主服务器开启

开启功能修改配置文件:Log_bin=/var/FILE

用于记录引起数据改变或潜在有可能改变数据可能性的语句(SRAREMENT)或改变后觉的结果(ROW)或混合(MIXED)

功用:用于”重放”

Binlog_format={SRAREMENT|ROW|MIXED}

SRAREMENT:语句:记录语句数据量较小  但是有可能结果不一致(变量)

ROW:重放时 可以不用变量 直接记录数据改变的值

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

Sql_log_bin={ON|OFF} 是否把信息记录在文件内容中

Max_binlog_size=                            字节单位 默认1G

Sync_binlog={1|0}              同步写入磁盘 性能下降 也可能导致主从服务器数据不一致

mysqlbinlog：客户端查看二进制文件命令

                                   YYYY-MM-DD hh:mm:ss                       

                            --start-datetime=                         从指定时间开始查看

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

                            /*!*/;时间发生的日期时间:

       事件发生的日期时间：#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/*!*/;

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

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

             redo log            重读日志
             undo log            撤销日志

**************************************

备份和恢复(数据):
备份:存储的数据副本
原始数据:持续改变:
恢复:把副本应用到线上系统;
仅能恢复到备份操作时刻的数据状态;

为什么备份?
灾难恢复:硬件故障(冗余). 软件故障(bug).自然灾害.黑客攻击....
测试:
备份时应该注意事项:
能容忍最多丢失多少数据:
恢复数据需要在多长时间内完成;
需要恢复哪些数据:

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

备份类型;
备份的数据集范围:
完全备份:整个数据集
部分备份:数据集的一部分,比如部分表;

完全备份:整个数据集
增量备份:仅备份自上一次完全备份或增量备份以来变量的那部分数据:
差异备份:仅备份自上一次完全备份依赖变量的那部分数据;

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

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

备份需要考虑的因素:
锁定资源多长时间?
备份过程需要多长时间?
备份时的IO网络等服务器负载状态?
恢复过程时长?

备份策略:
完全+差异:
完全+增量:

备份手段:物理.逻辑:

备份什么?
数据:
二进制日志:
InnoDB事物日志:
代码(存储过程.存储函数.触发器.事件调度器)
服务器的配置文件:
备份工具:
Mysqldump mysql服务自带的服务工具;逻辑备份工具; 基于c/s架构实现,服务器进程必须在线

完全.部分备份:
         InnoDB:热备(启动事物,阻塞R/W请求)
                                         MyISAM:温备

Cp/tar (基于ssh远程备份):
                 Lvm: 快照(之前请求一个全局锁),快照后立即释放锁.达到几乎热备的效果;物理备份
                 注意:不能金备份数据文件; 还要备份事物日志;
     前提:要求数据文件和事物日志位于同一个逻辑卷(时间一致);

Xtrabackup: 最好用的工具

有percona提供,开源工具,支持对innodb做热备,物理备份工具:

完全备份,部分备份;
  完全备份,增量备份;

Mysqlhotcopy;

Select 只备份内容 没有结构

备份: SELECT cluase INTO OUTFILE ‘FILENAME’;  
         备份:CREATE TABLE  
  导入:LOAD DATA (导入数据前要手动创建表和表结构索引等信息 select备份只能备份数据)

Mysqldump:

逻辑备份.完全备份.部分备份; 不适用于大表 (巨大的INSERT INTO 语句备份成的sql脚本)

二次封装工具:

Mydumper
          Phpmyadmin

                     Usage:

                    mysqldump [OPTIONS] database [tables]
                             OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
                             OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

                           第二种备份方式包含CREATE DATABASE语句 第一种没有 需要手动创建
            例:mysqldump mydb testable > /tmp/testable.sql      (`]# vim .my.cnf [mysql]-->[client] )
        options:

                     MyISAM存储引擎：支持温备，备份时要锁定表；
                             -x, --lock-all-tables：锁定所有库的所有表，读锁；
                             -l, --lock-tables：锁定指定库所有表； 如果锁定单表,一个库内的表时间可能不一致

                           

                     InnoDB存储引擎：支持温备和热备；
                             --single-transaction：创建一个事务，基于此快照执行备份；

                           

                     其它选项：
                             -R, --routines：存储过程和存储函数；
                              --triggers            触发器
                              -E, --events          事件         

                             --master-data[=#]

                                   1：记录为CHANGE MASTER TO语句，此语句不被注释；
                                            2：记录为CHANGE MASTER TO语句，此语句被注释；
                                                        --flush-logs：锁定表完成后，即进行日志刷新操作(刷新二进制文件,滚动为一个新文件)；