mysql dump不同服务器_第三十二天 MySQL服务器日志、备份恢复、MySQLdump及xtrabackup的使用...

1、MySQL服务器日志

2、MySQL备份和恢复的基本概念

3、使用MySQLdump及lvm2进行备份

4、xtrabackup 的使用

一、MySQL服务器日志MySQL日志：查询日志：查询语句的记录；由于I/O占用比较大所以不建议开启。慢查询日志：查询日志的一种子类型，记录查询时间超出设定时长的查询的日志记录。错误日志：记录错误信息，要启用错误日志二进制日志：重做日志中继日志：备服务器从主服务器复制过来的数据临时暂存在备服务器上日志文件叫中继日志事务日志：保证事务性存储引擎能满足ACID,同时不降低服务器性能； 将随机I/O转换为顺序I/Oshow global variables like '%log%';：显示日志相关参数查询日志：(生产环境不开启查询日志){  log_output = {TABLE|FILE|NONE}：日志输出位置，指定存储格式,none不记录查询日志信息。general_log = {ON|OFF}: 是否启用查询日志，如果设置为ON,log_output变量才会有意义。general_log_file = /opt/mysqld.log：当log_output有FILE类型时，日志信息的记录位置；}慢查询日志：select @@global.long_query_time;：如果查询超过设定值就判定为慢查询。slow_query_log = {ON|OFF}：是否启动慢查询日志slow_query_log_file = /opt/mysqld.log：慢查询日志文件错误日志：mysqld会记录启动和关闭过程中输出的信息和运行中产生的错误信息，在主从架构中的从服务器上启动成功，可以在该日志中体现。log_error：指定错误日志路径和文件名log_warnings：是否将警告信息记录在错误日志中二进制日志：show {binary | master} logs;：在主从架构中主服务器端处于由mysqld维护状态中的二进制日志文件show binlog events in 'mysql-bin.000005'\G：显示二进制文件中的数据日志记录格式分为2种“：一种为语句记录：记录sql执行语句一种分为行记录：记录表中修改过的行，例如：时间改变后不能再基于语句记录mysql可以采用混合记录模式：mixedbinlog_format：二进制日志记录模式 {MIXED(混合模式)| statement(语句模式)| row(行模式)}log_bin：指定日志文件记录位置sql_log_bin ：是否启用二进制记录日志max_binlog_size：二进制文件单个大小max_binlog_cache_size： 二进制缓存大小max_binlog_stmt_cache_size：二进制语句缓存大小sync_binlog：是否立即同步缓存到日志文件中，0表示不同步，其他数值表示记录多少语句后同步二进制日志文件构成日志文件：文件名前缀.文件名后缀：例如在data目录下：mysql-bin.000001索引文件：文件名前缀.index：例如在data目录下：mysql-bin.indexmysqlbinlog：查看二进制二文件每一个at为一个事件开始：例如： mysqlbinlog mysql-bin.000005事务日志(innodb日志)innodb_log_grep_home_dir：日志文件目录innodb_log_file_size：日志文件大小innodb_mirrored_log_groups：事务日志镜像。

二、MySQL备份恢复的基本概念备份类型：(完全备份、增量备份、差异备份)部分备份：只备份数据库中的一张或多张表。增量备份：备份上一次完全备份或增量备份后有变化的数据(如周日完备+周3的增量)

差异备份：备份上一次完全备份后有变化的数据(如周日完备+周3、2、1的备份)热备份、温备份、冷备份(判断标准：服务器是否在线)：热备份：在线备份，服务器读写操作不受影响(开始备份到备份结束时数据可能不一致)温备份：在线备份，数据库只能读不能写冷备份：离线备份，备份期间不能为业务提供服务myisam：最多支持到温备，innodb支持热备。物理备份和逻辑备份物理备份：直接复制物理文件备份基于文件备份，直接复制数据文件即可，但是不能跨平台。备份工具简单，恢复快。逻辑备份：从数据库中导出数据，保存在文本文件中。

逻辑备份：优点，备份的文本文件可二次加工，恢复过程比较简单，可以通过网络来实现恢复和备份，备份灵活定义，与存储引擎无关myisam和innodb可以相互使用备份数据，特定数据除外例如innodb中支持事务。缺点：无法保证还原后和原数据一样，从逻辑备份中还原要索引重建，耗时太长。

规则备份时需要考虑的因素：  持锁的时长  备份过程时长  备份负载   恢复过程时长

备份什么呢？

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

设计备份方案：完全备份+增量备份备份工具：mysqldump：逻辑备份工具，支持所有存储引擎，只支持温备不支持热备，支持完全备份和部分备                                                      份；如果做完全备份和增量备份的话，完备数据后再备份二进制日志就是增量备份。cp、tar：物理备份工具，冷备适用于所有存储引擎，一般只备份myisam的存储引擎。lvm2的快照：几乎热备，借助于cp、tar实现物理备份

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

备份方案：mysqldump+binlog：mysqldump：完全备份，通过二进制数据实现增量备份，逻辑备份方案。

mysqldump支持innodb的热备                           lvm2快照+binlog：几乎热备，物理备份，lvm2完备+binlog增量备份

xtrabackup：对innodb支持热备，支持完全备份和增量备份，myisam引擎温备，不支持增备。

三、使用mysqldump及lvm2进行备份mysqldump: 客户端，通过mysql协议连接至mysqld；-A、--all-databases：表示全部备份，例如：./mysqldump -A -uroot -p123456 >/tmp/all1.sql，可以压缩存放在 all1.sql-x、--lock-all-tables：启动备份前锁定所有表，执行(myisam\innodb)温备。-l、--lock-tables：备份单张表只锁定备份表。--single-transaction：innodb热备，启动一个大的单一事务实现备份 (必须innodb引擎才可以用)-B, --databases db_name1、db_name2：备份单个或多个数据库。不加-B备份数据库不会创建数据库，还原时需要手动创建数据库后导入数据，加-B会重新创建新库在导入数据(删除原有库)。例如：./mysqldump -uroot -p123456 -B mysql >/tmp/all1.sql-C, --compress：压缩传输；--master-data[=#]：# 取值 0 1 2

如何备份二进制日志

手动查看完备后二进制日志的位置：

>flush tables with read lock;

>show master logs;

>show binlog events in 'master-bin.000001';    查看最后的节点，下次从该节点开始备份。                                      >flush logs;滚动下，换个新文件直接备份000002, 这样会很麻烦，我来看master-data选项

例如：mysqldump --master-data=1 --lock-all-tables --all-databases > /tmp/all.2.sql

1：记录change master to 语句，此语句未被注释

2：记录change master to 语句，此语句被注释，一般用2

-F，--flush-logs :锁定表之后执行flush logs命令；

例如：mysqldump -uroot -p123456 --master-data=1 -B test > 123.sql记录当前二进制文件最后事件位置，1表示不注释该语句，2表示注释该语句。一般用2.mysqlbinlog --start-datetime '2015-06-13 18:00:00' --stop-datetime '2015-06-13 18:30:00' /mydata/data/mysql-bin.*;：增量备份格式。mysqlbinlog --stop-position=4 /mydata/data/mysql-bin.000007 >/tmp/a.sql：保存事件到4之前的操作语句

命令语法格式：

mysqldump [options] database [tables]: 备份单个库，或库指定的一个或多个表

mysqldump [options] --database [options] DB1 [DB2 DB3...]，备份一个或多个库

mysqldump [options] --all-database [options]: 备份所有库

mysqldump备份格式mysqldump -uroot -p123456 mysql user>mysql_user.sql ：备份mysql数据库中user表mysqldump -uroot -p123456 -B mysql >/tmp/mysql.sql：备份单个数据库mysqldump -A -uroot -p123456 >/tmp/all1.sql：备份所有数据库其他选项：-E, --events：备份指定库的事件调度器；-R, --routines：备份存储过程和存储函数；--triggers：备份触发器flush tables with read lock;：关闭已打开的表，对所有表添加共享锁unlock tables;：解除锁文件show master logs;：显示二进制数据文件show binlog events in 'mysql-bin.000005';：显示当前二进制事件到那个位置flush logs； ：滚动二进制日志，为二进制数据添加一个新文件show master status;：显示当前使用的是那个二进制文件和具体位置

案例：hellodb单数据库实现备份策略为每周完全，每日增量(二进制文件和数据文件强烈建议放同一磁盘上)

#mysqldump -B hellodb --lock-all-tables --master-data=2 > /backup/hellodb-date +%F`.sql

#mysql  连入MySQL做些操作来模仿生成环境数据变化来进行增量备份

>use hellodb;

>create table tb8(id int (3))  ;

>unlock tables;(刚才备份时锁定了所有表，现在解锁)

>insert into tb8 values (1),(2);

#vi hellodb-xxxx.xx.xx.sql  查看日志备份位置

>flush logs;

>show binary logs;   查看日志，现在又滚动了一个。

#mysqlbinlog --start-datetime '2014-09-01 15:00:00' --stop-datetime '2014-09-01 15:30:00' /mydata/data/master-bin.* > /backup/incre-`date +%F`.sql (导出日志到备份文件，相当于第一次增量备份)

>use hellodb;

>insert into tb9 values (9),(20);   又执行了新的操作(插入数据)

>drop database hellodb;   误删除了hellodb数据库

>mysqlbinlog /mydata/data/master-bin.000003 > /tmp/a.sql  导出误删除日志到sql文件(日志中有删除数据库语句，要编辑此日志注释掉删除数据库语句，或者查看drop数据库的位置，执行mysqlbinlog --stop-position=567 /mydate/data/master-bin.000003 > /tmp/a.sql)

#mysql < /backup/hellodb-2014-09-01.sql   #还原完备

#mysql < /backup/incre-2014-09-01.sql       #还原第一次的增量备份

#mysql

至此一次完备+增量备份及还原就完成了，自己写成脚本文件。

如果数据库很大(100G)mysqldump备份会很慢几个小时(逻辑备份太慢)，所有我们在数据很大的数据库用物理备份rsync直接复制物理文件。     物理备份：数据文件的时间一致性

方法1、主从复制，其中从服务器节点用于离线备份(冷备份)是一个经济廉价、易实现的备份方案

方法2、lvm2快照，几乎热备，需要数据库在逻辑卷上。

步骤： 1、请求锁定所有表：msyql >flush tables with read lock;

2、记录二进制日志文件及事件位置：mysql>show master status;

3、创建快照：lvcreate -L SIZE -s -p -r -n NAME /dev/VG_NAME/LV_NAME

4、释放锁：mysql >unlock tables

5、挂载快照卷，复制数据进行备份 cp,rsync,tar 等命令复制数据

6、备份完成后，删除快照卷

注意：                      在重新安装过mysql后再执行mysqldump时明确启用的是当前数据库的mysqldump                      将数据和二进制文件放置于不同的设备；二进制日志也应该周期性地备份；

记录日志文件写脚本中实现：

#mysql -e 'show master status'  > /backup/postion/pos 查看导入二进制日志位置节点

#rsync -a hellodb /backup/hellodb-`date +%F`  同步到备份服务器(hellodb表示同步此目录，hellodb/表示同步此目录下的文件)

问题：如果只备份了单个库，而基于二进制日志做增量备份会有什么问题？

它把所有库的事件全记录下来了，还的使用文本分析工具筛选只属于相关数据库的语句，再进行导入才可以。四、xtrabackup的使用为避免在还原时产生二进制日志，将临时关闭二进制文件，之后还原数据库，在开启二进制文件mysql>set session sql_log_bin=0;  关闭二进制日志mysql>source /opt/all.sql; 读取备份脚本mysql>set session sql_log_bin=1;  开启二进制日志xtrabackup：工具 官方站点：www.percona.com此工具依赖于perl-DBD-MySQL，rpm包完全备份：innobackupex --user=root --password=123456  /opt/123.sql