mysql全备份+增量备份笔记总结

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql全备份+增量备份笔记总结

余二五 2017-11-15 18:41:00 浏览955
展开阅读全文

备份基础知识

冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;

温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;

热备(hot backup):备份的同时,业务不受影响。


这种类型的备份,取决于业务的需求,而不是备份工具

MyISAM不支持热备,InnoDB支持热备,但是需要专门的工具


完全备份:full backup,备份全部字符集。

增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。

差异备份:differential backup 上次完全备份以来改变了的数据。

建议的恢复策略:

完全+增量+二进制日志

完全+差异+二进制日志



全备份

MyISAM:

mysql -uroot  -p'123456' -A  -B -F   –flush-privileges  –master-data=2 –x -–events | gzip > /opt/x_$(date +%F).sql.gz

InnoDB:

mysqldump    -uroot -p123456  --single-transaction   -A -B -F  --events | gzip > /server/backup/x_$(date +%F).sql.gz



--single-transaction    MySIM直接选-x(--lock-all-tables)参数锁表,InnoDB选择这个参数来保证备份的一致性。相当于设置一个隔离级别,REPEATABLE READ ,以确保本次会话dump时,不会看到其它会话已经提交的数据。

--master-data[=#]     自动找到binlog的位置  This causes the binary log position andfilename to be

   如: --master-data=1     可以不用刷新binlog了,做增量备份的时候很有用

       --master-data=2    后会加个注释,好找些

-E, --events        Dump events.

-A, --all-databases Dump  导出所有数据,一般加-B 共用+--events all

-B,  --databases    指定多个库名备份    直观看,加上-B参数作用是增加创建数据库和连接数据库的命令,生产环境备份必用。

-F, --flush-logs    刷新,切割binlog

补充:上诉两种备份如果数据库有存储过程和触发器还得加两个参数:

--triggers  –routines  --hex-blob,   一般公司无这三个参数。

触发器       存储过程   如果你库中有blob字段,而你又没加这个参数 ,那你的blog大字段数据就会丢失



分库备份

mysql  -uroot -p'123456' -e "show databases;" | grep -Evi "Database|infor|perf" | sed -r 's#^([a-z].*$)#mysqldump -uroot-p'123456'  --events -B  \1 | gzip > /tmp/logs/\1.sql.gz#g'      | bash

-t,--no-create-info     如果希望只导出表数据

-d, --no-data           只备份表结构No row information.


报错

mysqlbinlog:unknownvariable'default-character-setutf8'解决方法

mysqlbinlog  --no-defaults   ./mysql-bin.000007   


mysql全量导出时碰到如下告警:                     默认是不备份事件表的,只有加了--events 才会不警告

Warning: Skipping the data of table mysql.event. Specify the --events option explicitly

解决办法:

--events --ignore-table=mysql.event


ERROR 1046 (3D000) at line 22: No database selected

修改.sql   在22行前面加上   use   库名字;



恢复

首先恢复全备

mysql    -uroot  -p123456    <  /server/backup/x.sql


多分库文件恢复

for name in `ls *.sql| sed 's#.sql##g' `; do mysql -uroot -p123456    < ${name}.sql ; done


然后

mysqladmin -uroot -p123456 flush-log       //切割日志


汇总所有的binlog, 把错误的删除

剩下的转成sql语句

cp mysql-bin.000016  /server/backup/

mysqlbinlog  --no-defaults  -d user mysql-bin.000016   > bin.sql

mysql  -uroot -p123456  < bin.sql

执行 | mysql

根据binlog位置和时间回复

mysqlbinlog --start-postion=107  --stop-position=1000    -d   库名       二进制文件          

mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d   库名 二进制文件

egrep -v "#|\*|--|^$"   可以过滤查看备份内容

rsync配合定时任务

rsync -avz   /data/3306/mysql-bin.000*   rsync_backup@10.0.0.18::backup  --password-file=/etc/rsync.passsword




一主多从,一个从 做备份

vim  .my.cnf        //设置登陆

[client]

user=root

host=localhost

password=123456

mysql> show  variables  like "character_set%";   //查看字符集

[client]                   //设置字符集

default-character-set=utf8

[mysqld]

character-set-server=utf8     //5.5

default-character-set=utf8   //5.1

[mysql]

default-character-set=utf8


开启bin-log,存在一份全备份,及所有增量binlog文件备份

mysql> show  full   processlist; //连接情况

mysql> show variables like "%log_bin%";

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_trust_function_creators | OFF   |

| sql_log_bin                     | ON    |

+---------------------------------+-------+


mysql> flush  table with read lock;   //读锁

mysql> unlock  tables;  //解锁

replicate_wild_ignore_table=mysql.%     //可以加通配符

--skip-name-resolve    选项启动mysqld来禁用DNS主机名查找

slave-skip-errors = 1032,1062   //排除1032,1062的保持。

lower_case_table_names=1  //让MySQL不区分大小写!  //慎用,会影响原来的表名字

[mysqld]

read-only  //只读,root不受限

主从不同步


slave 开启从库记录binlog               级联同步        当做数据库备份

log-bin = /data/3307/mysql-bin

log-slave-updates  //这个参数用来配置从服务器的更新是否写入二进制日志,这个选项默认是不打开的,但是,如果这个从服务器B是服务器A的从服务器,同时还作为服务器C的主服务器,那么就需要开发这个选项,这样它的从服务器C才能获得它的二进制日志进行同步操作

expire_logs_days = 7     //保留7天


# mysql -e "show slave status\G;" | egrep 'Slave_IO_Running|Slave_SQL_Running'  | awk '{print $2}' | egrep 'Yes' | wc -l           //监控状态


mysql数据库优化

  1. 硬件优化   物理机

  2. 软件优化   系统  mysql编译

  3. my.cnf参数优化       //SHOW  GLOBAL  STATUS\G;                 工具mysqlreport

  4. SQL语句的优化       索引优化

  5. 架构的优化

  6. 流程制度安全优化     人的流程  测试流程      客户端phpmyadmin











本文转自 295631788 51CTO博客,原文链接:http://blog.51cto.com/hequan/1775333,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论