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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

备份基础知识

冷备(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,如需转载请自行联系原作者
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之从MySQL同步数据到Doris时,历史数据时间字段显示为null,而增量数据部分的时间类型字段正常显示的原因是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
12天前
|
关系型数据库 MySQL OLAP
实时计算 Flink版产品使用合集之可以支持 MySQL 数据源的增量同步到 Hudi 吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
12天前
|
SQL 存储 关系型数据库
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
|
12天前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版产品使用合集之2.2.1版本同步mysql数据写入doris2.0 ,同步完了之后增量的数据延迟能达到20分钟甚至一直不写入如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
12天前
|
Prometheus Cloud Native 关系型数据库
实时计算 Flink版产品使用合集之binlog被清理掉的问题,并且binlog有备份,有什么方法来恢复到RDS
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
14天前
|
存储 关系型数据库 MySQL
【MySQL系列笔记】分库分表
分库分表是一种数据库架构设计的方法,用于解决大规模数据存储和处理的问题。 分库分表可以简单理解为原来一个表存储数据现在改为通过多个数据库及多个表去存储,这就相当于原来一台服务器提供服务现在改成多台服务器组成集群共同提供服务。
34 8
|
14天前
|
关系型数据库 MySQL Linux
服务器Linux系统配置mysql数据库主从自动备份
这是一个基本的配置主从复制和设置自动备份的指南。具体的配置细节和命令可能因您的环境和需求而有所不同,因此建议在操作前详细阅读MySQL文档和相关资源,并谨慎操作以避免数据丢失或不一致。
34 3
|
14天前
|
关系型数据库 MySQL 数据库连接
用Navicat备份Mysql演示系统数据库的时候出:Too Many Connections
用Navicat备份Mysql演示系统数据库的时候出:Too Many Connections
48 0
|
14天前
|
关系型数据库 MySQL 数据管理
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
198 0
|
14天前
|
存储 安全 关系型数据库
MySQL中使用percona-xtrabackup工具 三种备份及恢复 (超详细教程)
MySQL中使用percona-xtrabackup工具 三种备份及恢复 (超详细教程)