MySQL数据库的实时备份知识点详解

2024-09-29 00:31:59

前言

数据库实时备份的需求很常见，MySQL本身提供了 Replication 机制，摘译官方介绍如下：

MySQL Replication 可以将一个主数据库中的数据同步到一个或多个从数据库中。并且这个同步过程默认以异步方式工作，不需要保持主从数据库的实时连接（即允许连接中断）。同时允许自定义配置需同步的数据库及数据表。

MySQL Replication 的优点及应用场景如下：

1、通过 MySQL Replication 实现负载均衡与读写分离（主数据库仅更新，从数据库仅读取），提升数据库性能。

2、通过 MySQL Replication 实现数据的实时备份，保证数据安全。

3、通过 MySQL Replication 实现数据的离线分析（主数据库生成数据，从数据库分析计算不影响主数据库性能）。

4、数据分发。

MySQL Replication完整的官方文档请参阅：https://dev.mysql.com/doc/refman/5.7/en/replication.html

工作原理

1111

1、Master中的所有数据库变更事件写入Binary Log文件

2、当在Slave中执行“SLAVE START”命令时，开启Slave I/O Thread，并连接Master

3、Master侦测到Slave I/O Thread的连接，开启Log Jump Thread进行响应

4、Master Binary Log经Master Log Jump Thread和Slave I/O Thread传输至Slave Relay Log

5、Slave SQL Thread将Relay Log还原至数据，同步完成

注：可使用“SHOW PROCESSLIST”命令在Master和Slave中查看对应线程的运行情况

配置Master

开启Binary Log并设置ServerID，ServerID必须唯一，取值范围1至232-1

[mysqld]
# 开启Binary Log
log-bin=mysql-bin
# 设置全局ID
server-id=1

# 指定需同步的数据库（因为数据库名称可能包含逗号，因此多个数据库必须重复配置多次而不能以逗号分隔）
binlog-do-db=database_name
# 指定禁止同步的数据库
binlog-ignore-db=database_name
# 指定Binary Log格式
binlog_format=MIXED

创建同步帐号

因为每个Slave均需要使用帐号密码连接至主数据库，所以在主数据库上必须提供帐号。建议使用一个独立帐号，仅授权数据同步权限。

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

获取Binary Log信息

Slave启动I/O Thread时需要传入Binary Log部分信息，因此需要获取一下Binary Log信息：

SHOW MASTER STATUS;

使用“SHOW MASTER STATUS”命令获取Binary Log信息，并记录File和Position字段值。

同步前保证Master与Slave的数据一致

在Slave启动I/O Thread前，需确保Master与Slave的数据一致，因此先对Master进行锁定（防止数据变更），手动同步并确保数据一致后再解锁。

FLUSH TABLES WITH READ LOCK;

手动数据同步相关操作略...

UNLOCK TABLES;

配置Slave

设置ServerID，可不必开启BinLog：

[mysqld]
# 设置全局ID
server-id=2

# 指定同步的数据库
replicate-do-db=database_name
# 指定禁止同步的数据库
replicate_ignore_db=database_name

设置Master信息，执行如下命令：

mysql> CHANGE MASTER TO
  ->   MASTER_HOST='master_host_name',
  ->   MASTER_PORT='master_host_port',
  ->   MASTER_USER='replication_user_name',
  ->   MASTER_PASSWORD='replication_password',
  ->   MASTER_LOG_FILE='recorded_log_file_name',
  ->   MASTER_LOG_POS=recorded_log_position;

启动I/O Thread

START SLAVE;

查看同步状态：

SHOW SLAVE STATUS;

Master的binlog_format 参数

binlog_format用于配置Binary Log的格式，支持如下三种类型：

Row

按数据行的变化进行记录，该模式与SQL语句、存储过程、函数、触发器等无关，它只关心每一行的数据是否发生变化，如变化则记录，因此Row模式是准确度最高的。但它的缺点是某些情况下会产生大量内容而导致效率下降，比如表结构发生变更时。

Statement

按SQL语句进行记录，很明显这个解决了Row模式的短板，但问题是准确度不够高，因为SQL语句可以非常复杂并且容易出现意外情况。

Mixed

Row与Statement混合模式，由MySQL自动决定什么时候使用Row，什么时候使用Statement，这也是默认模式。

replicate-do-db注意事项

当在Slave中使用replicate-do-db和replicate-ignore-db配置项时，需特别注意，跨数据库的SQL语句将不会被同步，如：

replicate-do-db=a
use b;
update a.some_table set some_field = 'some value';

解决方案是使用replicate_wild_do_table和replicate_wild_ignore_table，如：

replicate_wild_do_table=database_name.%
replicate_wild_ignore_table=database_name.%

Mysql触发器在PHP项目中用来做信息备份、恢复和清空

案例: 通过PHP后台代码可以将员工的信息删除,将删除的员工信息进行恢复(类似于从回收站中恢复员工信息),并且还可以将已经删除的员工进行清空(类似于清空回复站的功能). 思路: 要有一张员工表,还要有一张员工备份表:备份,使用触发器,在点击删除按钮执行删除功能之前将员工表中的信息导入到备份表中,这样就达到了备份的效果:恢复,对备份表使用触发器,将备份表中的数据删除,删除的同时将此数据导入到员工表中:清空,使用truncate方法,将备份表中的数据彻底清空,并且释放内存,而且这个方法进行数据删除不
C#实现MySQL命令行备份和恢复

MySQL数据库的备份有很多工具可以使用,这两天写了一个使用C#调用MYSQL的mysqldump命令完成MySQL数据库的备份与恢复的小工具先来说一下mysqldump命令备份MySQL数据库的使用方法 mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 直接将MySQL数据库压缩备份 mysqldump -hhostname -uusername -ppassword databasename |
Mysql实现企业级日志管理、备份与恢复的实战教程

背景随着业务的发展,公司业务和规模不断扩大,网站积累了大量的用户信息和数据,对于一家互联网公司来说,用户和业务数据是根基.一旦公司的数据错乱或者丢失,对于互联网公司而言就等于说是灭顶之灾,为防止系统出现操作失误或系统故障导致数据丢失,公司要求加强用户数据的可靠性,要求全面加强数据层面备份,并能在故障发生时第一时间恢复. 数据备份形式文件备份: 通过Linux的备份命令把文件统一打个包存起来,可存在本地和远程服务器,等到要恢复时,再用这些文件恢复到指定位置. 数据库数据备份: 在一些对数据可靠
Mysql数据库增量备份的思路和方法

MySQL数据库增量备份,在这之前修改我们的数据库配置文件/etc/my.cnf开启bin-log日志功能即可.接下来是我参考了下网上的一些方法,自己写的,主要还是要能学到他的一些思路和方法. #function:MYSQL增量备份 #version:1.0.0 #author:wangyanlin #date:2017/08/02 #---------------------------------------------------------------------------------
Mysql实时备份实现方法

目前成熟的实时备份为双机(master/slave),是基于同步日志事件来实现,那单机如何实现具有增量的备份呢?可以借用双机的原理,非常简单,实施步骤如下: Mysql版本:mysql4.0+ 1.vi my.cfg 复制代码代码如下: [mysqld] log-update=/home/backup/update #添加该行 2.service mysql restart 会在/home/backup/update00001文件,内容为数据库变化的所有SQL(没有select) 3.每天的全
mysql备份脚本 mysqldump使用方法详解

本文实例为大家分享了mysql备份脚本,供大家参考,具体内容如下 #!/bin/bash #全备方式,一般在从机上执行,适用于小中型mysql数据库 #删除15天以前备份 #作者:fafu_li #时间:2015.08.10 source /etc/profile #加载系统环境变量 source ~/.bash_profile #加载用户环境变量 set -o nounset #引用未初始化变量时退出 #set -o errexit #执行shell命令遇到错误时退出 user="root&q
MySQL数据库的实时备份知识点详解

前言数据库实时备份的需求很常见,MySQL本身提供了 Replication 机制,摘译官方介绍如下: MySQL Replication 可以将一个主数据库中的数据同步到一个或多个从数据库中.并且这个同步过程默认以异步方式工作,不需要保持主从数据库的实时连接(即允许连接中断).同时允许自定义配置需同步的数据库及数据表. MySQL Replication 的优点及应用场景如下: 1.通过 MySQL Replication 实现负载均衡与读写分离(主数据库仅更新,从数据库仅读取),提升数据库
MySQL数据库使用mysqldump导出数据详解

mysqldump是mysql用于转存储数据库的客户端程序.它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的 SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等.可以用来实现轻量级的快速迁移或恢复数据库.是mysql数据库实现逻辑备份的一种方式. 在日常维护工作当中经常会需要对数据进行导出操作,而mysqldump是导出数据过程中使用非常频繁的一个工具:它自带的功能参数非常多,文章中会列举出一些常用的操作,在文章末尾会将所有
JDBC连接MySQL数据库批量插入数据过程详解

这篇文章主要介绍了JDBC连接MySQL数据库批量插入数据过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 1.读取本地json数据 2.jdbc理解数据库 3.批量插入 maven 引入jar包: <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2
mybatis 连接mysql数据库 tinyint 为boolean类型详解

字段类型为tinyint(1)的返回类型设置为integer 现象描述: 数据库表字段类型为:tinyint 长度为1,即类型为:tinyint(1) 查询时,该字段对应的的java类型为boolean 问题描述: 如何将该字段的java类型设置为Integer? 解决方案: 1. 在jdbcUrl添加参数:tinyInt1isBit=false(默认为true): 2.避免使用长度为1的tinyint类型字段存储数字格式的数据: 补充知识:[mybatis]mysql数据库tinyint类型
Python MySQL数据库基本操作及项目示例详解

目录一.数据库基础用法二.项目:银行管理系统 1.进行初始化操作 2.登录检查,并选择操作 3.加入查询功能 4.加入取钱功能 5.加入存钱功能一.数据库基础用法要先配置环境变量,然后cmd安装:pip install pymysql 1.连接MySQL,并创建wzg库 #引入decimal模块 import pymysql #连接数据库 db=pymysql.connect(host='localhost',user='root',password='1234',charset='ut
Python写入MySQL数据库的三种方式详解

目录场景一:数据不需要频繁的写入mysql 场景二:数据是增量的,需要自动化并频繁写入mysql 方式一方式二总结大家好,Python 读取数据自动写入 MySQL 数据库,这个需求在工作中是非常普遍的,主要涉及到 python 操作数据库,读写更新等,数据库可能是 mongodb. es,他们的处理思路都是相似的,只需要将操作数据库的语法更换即可. 本篇文章会给大家分享数据如何写入到 mysql,分为两个场景,三种方式. 场景一:数据不需要频繁的写入mysql 使用 navicat 工
MySQL数据库学习之查询操作详解

目录 1.示例表内容 2.简单查询 3.给列起别名 4.列运算 5.条件查询 1.示例表内容 dept表: +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS
MySQL数据库学习之分组函数详解

目录 1.分组函数极值求和平均值列数和 2.分组查询 3.小练习 4.大BOSS 1.分组函数极值示例表内容见此篇文章找出最高工资: mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+ 1 row in set (0.00 sec) 找出最低工资: mysql> select min(sal) from emp; +----------+
MySQL数据库子查询语法规则详解

目录子查询简介 WHERE 子查询 FROM 子查询 SELECT 子查询子查询是在查询语句里面再嵌套一个查询,这是因为我们在提取数据的时候有很多不知道的数据产生了依赖关系.此时我们就需要先查询一组数据的结果集,然后将这个结果集作用为下一个查询的对象.在 “表连接的章节”,我们曾说过子查询的效率低下的问题,其实并不是所有的子查询效率都是低下的,“WHERE” 子查询在匹配记录的时候要反复执行,这是不推荐使用的:但是如果将查询结果集当做一张表来使用,与其他的表做一个连接,这就是 “FROM”
MySQL数据库表中的约束详解

目录 MySQL表中的约束(constraint) 约束分类非空约束唯一性约束复合约束主键约束自增列-AUTO_INCREMENT 外键约束FOREIGN KEY约束 CHECK约束 DEFAULT约束 MySQL表中的约束(constraint) 为了保证数据的完整性,(数据的精确性和可靠性) SQL规范以约束的方式对表数据进行额外的条件限制,可从以下四个方面进行考虑实体完整性域完整性引用完整性用户自定义完整性约束?对表中字段的限制. 约束分类约束作用字段的个数单列约束

MySQL数据库的实时备份知识点详解

相关推荐

随机推荐