mysql 中的备份恢复、分区分表、主从复制、读写分离

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: 数据库 mysql 的灾难恢复,高并发必备知识

🎈  mysql 的备份和恢复

  • 创建备份管理员
  • 创建备份管理员,并授予管理员相应的权限
  • 备份所需权限:select,reload,lock tables,replication client,show view,event,process
# 创建管理员
create user 'backup'@'localhost' identified by'123456';# 给管理员授权
grant select,reload,lock tables,replication client,show view,event,process on*.* to 'backup'@'localhost';
  • sql 文件恢复之全量恢复
  • 只要将备份的 sql 文件直接导入数据库即可
mysql -uroot -p 数据库 < sql文件
  • sql 文件恢复之基于时间点的恢复
# 首先进行一次基于最近一次的全量备份的文件进行一次全量恢复
mysql -uroot -p 数据库 < sql文件
# 然后查看备份的sql文件的 CHANGE MASTER 值,基于该值进行二进制日志的还原
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=154;# 查看二进制日志,根据时间点找到误操作前一段时间的二进制日志
cd /var/lib/mysql
mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=数据库名 binlog.000008 | grep -B3 DELETE | more# 记录最早删除记录的节点值,执行日志导出
mysqlbinlog --start-position=开始节点 --stop-position=结束节点 --database=数据库 二进制日志名 > 导出的sql文件名mysqlbinlog --start-position=154 --stop-position=26158 --database=laravel binlog.000007 > laravel.sql# 对导出的sql文件进行全量的还原
mysql -uroot -p 数据库 < sql文件
  • mysqldump逻辑备份
  • 指定数据库多个表进行备份:mysqldump [OPTIONS] database [table]
  • 指定多个数据库备份:mysqldump [OPTIONS] database [OPTIONS] DB1 DB2
  • 整个数据库实例进行备份:mysqldump [OPTIONS] --all-database [OPTIONS]
  • 参数 --single-transaction:开启事务保证备份数据的完整性,innodb 特有
  • 参数 -l或--lock-tables:依次锁定备份数据库所有表保证备份数据的完整性
  • 参数 -x或--lock-all-table:一次性锁定整个数据库实例所有数据表保证数据完整性
  • 参数 --master-data=[1/2]CHANGE MASTER TO 语句会被写成一个 sql 注释;1不会被写成注释,2写成注释,默认1
  • 参数 -R或--routines:备份数据库存储过程
  • 参数 --triggers:备份数据库触发器
  • 参数 -E或--events:备份数据库调度事件
  • 参数 --hex-blob16进制导出bit列和blob列数据 避免数据文本不可见
  • 参数 --tab=path:指定路径下为每个数据库生成两个文件:数据结构数据
  • 参数 -w或--where=过滤条件:过滤指定数据,仅支持单表导出
  • 注意:--single-transaction--lock-tables 参数是互斥的,所以,如果同一个数据库下同时存在 innodb 表和myisam表只能使用 --lock-tables 来保证备份数据的一致性,但是 --lock-tables 只能保证某一备份数据库的完整性,不能保证整个实例备份的完整性
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events 数据库 > 备份文件.sql
# 下载 libev软件包
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm# 安装 libev软件包
yum install percona-xtrabackup-24
# 全量备份
innobackupex --user=管理员账号 --password=密码 --parallel=2 备份路径innobackupex --user=backup --password=Gzjunyu19970925. --parallel=2 /home/db_backup/# 全量恢复,建议恢复前停止mysql服务,且清空mysql数据文件
innobackupex --datadir=mysql数据路径 --copy-back 备份路径innobackupex --datadir=/var/lib/mysql --copy-back /home/db_backup/2018-04-21_10-44-22/# 修改mysql数据路径的权限为777
chmod -R 777/var/lib/mysql


🎈  mysql 的分区分表

  • 查看是否支持分区分表:show plugins;
  • 删除分区命令:ALTER TABLE 表名 DROP PARTITION 分表名称;
  • 分区分类:范围分区哈希分区时间分区
# 范围分区
CREATETABLE `表名`(-- 数据字段)engine=INNODB
PARTITION BY RANGE(`字段名称`)(    PARTITION 分表名称 VALUES LESS THAN(范围),    PARTITION 分表名称 VALUES LESS THAN(范围));# 哈希分区
createtable `表名`(-- 数据字段)engine=INNODB
PARTITION BY HASH(UNIX TIMESTAMP(`字段名称`)) PATITIONS 4;# 时间分区
createtable `表名`(-- 数据字段)engine=INNODB
PARTITION BY RANGE(YEAR(`字段名称`))(    PARTITION p0 VALUES LESS THAN(2017),    PARTITION p1 VALUES LESS THAN(2018),    PARTITION p2 VALUES LESS THAN(2019));# 查看分区情况
SELECT    table_name,partition_name,partition_description,table_rows
FROM    information_schema.`PARTITIONS`
WHERE table_name ='表名';# 归档分区数据,mysql数据库版本需要大于等于5.7-- 分区归档操作步骤-- 1.新建和分区表字段一致的数据表 归档表前缀为 arch_CREATETABLE `归档表表名`()engine=INNODB
-- 2.进行数据交换 p0为分区名ALTERTABLE `原数据表表名` exchange PARTITION p0 WITH TABLE `归档表表名`;-- 3.删除分区,避免对数据的再次写入ALTERTABLE `原数据表表名` DROP PARTITION p0;-- 4.将归档表引擎设置为 archive  在检表语句中mysql引擎必须和原数据表引擎一致,否则无法进行数据交换ALTERTABLE `归档表表名` ENGINE=ARCHIVE;


🎈  mysql 的主从复制读写分离

  • 授权远程访问 mysql 数据库
  • 新建相关数据库管理员,授权并开启远程访问权限
-- 建议新建一个备份和主从复制的数据库管理员CREATE USER 'backup'@'localhost' IDENTIFIED BY'密码';-- 分配相关权限grant select,reload,lock tables,replication client,show view,event,process on*.* to 'backup'@'localhost';-- 开启远程访问权限GRANT ALL PRIVILEGES ON*.* TO 授权用户名@被授权服务器的IP IDENTIFIED BY'授权密码';FLUSH PRIVILEGES;
  • 配置mysql主服务器
  • 需要在 master 服务器和 slave 服务器都建立一个同名的数据(备份数据库)
  • 在主服务器中开启 binlog 日志和设置要发生主从同步数据库,使用 vim 打开 /etc/my.cnf 文件,修改配置如下
  • 配置完毕后,需要重启服务:service mysqld restart;
#mysql的bin-log日志配置选项,假设做读写(主从),这个选项在从服务器必须关闭
log_bin = binlog
#端口信息,其实可以不写
port =3306#主服务器的id,这id不一定设为1,只要主从不一样就行
server-id =1#要做同步的数据库名字,可以是多个数据库,之间用分号分割。
binlog_do_db = test
  • 检查配置
  • 登录 mysql 查看 binlog 日志相关参数是否正确
show master status;show variables like'log_bin';
  • 配置mysql从服务器
  • 主服务器已经配置成功,这时要切换到从服务器开始配置
  • 在从服务器中开启 binlog 日志和设置要发生主从同步数据库,使用 vim 打开 /etc/my.cnf 文件,修改配置如下
#从服务器的id,必须与主服务器的id是不同
server-id =2#主服务器的ip地址
master-host =192.168.56.2#grant授权的可复制用户账号
master-user = backup
#grant授权的可复制密码
master-password =123456#主服务器的mysql端口
master-port =3306#这个参数是用来设置在和主服务器连接丢失的时候,重试的时间间隔,默认是60秒
master-connect-retry =20#需要同步的主服务器数据库
replicate-do-db = test
  • 检测主从复制配置是否成功
show slave status\G
-- 如果结果包含如下参数,则证明主从已经配置成功Slave_IO_Running: Yes
Slave_SQL_Running: Yes
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
27天前
|
关系型数据库 MySQL 数据库
数据迁移脚本优化过程:从 MySQL 到 Django 模型表
在大规模的数据迁移过程中,性能问题往往是开发者面临的主要挑战之一。本文将分析一个数据迁移脚本的优化过程,展示如何从 MySQL 数据库迁移数据到 Django 模型表,并探讨优化前后的性能差异。
|
1月前
|
SQL 运维 关系型数据库
MySQL数据库运维第一篇(日志与主从复制)
MySQL数据库运维第一篇(日志与主从复制)
|
15天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
990 7
Mysql 数据库主从复制
|
1月前
|
存储 关系型数据库 MySQL
Java大佬必知必会——MySQL主从复制
如果你现在有两台MySQL,一台版本是03年的MySQL5.0,另一台是18年的MySQL8.0.11。新版本可以作为老版本的从服务器,但反过来是不可行的。如果二进制文件包含了已存在的数据,就会造成数据重复了。如果从服务器复制该二进制文件后的数据库状态是混乱无序的,那整个复制的过程就没有意义了。如果主、从服务器存储数据的顺序不一样,就会导致每次执行删除的数据都是不同的。,老版本可能无法解析新版本的新特性,甚至复制的文件格式都差异太大。MySQL从库只会复制它本身缺失的最新数据,利用二进制文件里的。
Java大佬必知必会——MySQL主从复制
|
1月前
|
SQL 关系型数据库 MySQL
MySQL高可用架构设计:从主从复制到分布式集群
MySQL高可用性涉及主从复制、半同步复制和Group/InnoDB Cluster。主从复制通过二进制日志同步数据,保证故障时可切换。半同步复制确保事务在至少一个从服务器确认后才提交。Group Replication是多主复制,支持自动故障切换。InnoDB Cluster是8.0的集成解决方案,简化集群管理。使用这些技术能提升数据库的稳定性和可靠性。
222 2
|
7天前
|
运维 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在处理MySQL表新增数据记录时,没有正确触发变更事件,该如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
8天前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版产品使用问题之从MySQL数据库中捕获变更数据并进行实时处理如何按天分表同步CDC数据
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
关系型数据库 MySQL
mysql-proxy实现mysql的读写分离
mysql-proxy实现mysql的读写分离
|
1月前
|
负载均衡 关系型数据库 MySQL
MySQL 主主复制与主从复制对比
MySQL的主主复制和主从复制是两种常见的数据库复制配置方式,各有优缺点和适用场景。以下是对这两种复制方式的详细对比: ### 主从复制 (Master-Slave Replication) **特点:** 1. **单向复制**:数据从主服务器复制到一个或多个从服务器。从服务器只能从主服务器接收数据更新,不能向主服务器发送数据。 2. **读写分离**:主服务器处理写操作(INSERT、UPDATE、DELETE),从服务器处理读操作(SELECT),可以分担读负载,提高系统的整体性能。 3. **数据一致性**:数据在主服务器上是最新的,从服务器上可能会有一定的延迟。 **优点:**
|
1月前
|
关系型数据库 MySQL
蓝易云 - 如何修复MySQL中损坏的表
最后,为了防止数据丢失,定期备份数据是非常重要的。
105 3