MySQL主从备份

通过日志备份

主库配置

my.ini配置文件

[mysqld]
#server_id不能和从库一样
server_id= 2
#log-bin包含路径和文件名
log-bin=/path-to-log/mysql-bin

重启数据库

net stop mysql
net start mysql

登录主数据库，查询bin-log是否打开

mysql> show variables like '%log_bin%';

建立用于主从复制的账号

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' WITH GRANT OPTION;
mysql> flush privileges;

查看账号

mysql> select user,host,plugin from user;

从库配置

设置server-id并关闭binlog功能

server_id=21
skip-log-bin

重启数据库

主库数据备份

可通过 mysqldump 备份日志坐标节点前的所有数据

mysqldump --all-databases --master-data > dbdump.db

从库配置需要复制的主机

主库中查询

mysql> show master status;

记录主库的File和Position

从库中配置主库连接信息、日志路径和位置

mysql> CHANGE MASTER TO MASTER_HOST='localhost',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000033',MASTER_LOG_POS=7231;

启动

start slave;

关闭

stop slave;
reset slave;

查看slave进程状态

show slave status\G

看到如下显示即为成功

Slave_IO_Running:Yes
Slave_SQL_Running:Yes

验证

主库

create database test;

从库

show database;

通过GTID备份（补充）

binlog格式设置

STATEMENT模式

binlog_format=STATEMENT

SBR优点：

• 历史悠久，技术成熟
• 不需要记录每一行变化，binlog文件较小
• 主从版本可以不一样

SBR缺点：

• 不是所有UPDATE语句都能被复制
• 复制需要全表扫描，需要请求更多的行锁
• 复杂的语句耗资源更严重

ROW模式

binlog_format=ROW

PBR优点：

• 安全可靠，任何时候都可以复制
• 有主键，复制会比较快
• 执行INSERT, UPDATE, DELETE语句时锁更少

PBR缺点：

• binlog大了很多
• 复杂的回滚binlog会包含大量数据
• 主服务器执行update语句的时候，所有发生变化的记录都会写在binlog中，而SBR只会写一次

MIXED模式

实际上是两者的结合

MySQL备份方式

navicat定时自动备份

步骤：

1. 打开navicat自动运行
2. 新建批处理作业
3. 选择备份——数据库名称——可用工作
4. 单击保存，输入文件名
5. 配置触发器
6. 输入任务描述
7. 单击确定

用户登录那块不用管，一般服务器也只有一个用户会登录，也一直是登录状态

本质上还是创建了一个windows任务计划程序，创建了之后无论navicat是否打开，都会定时触发任务

可通过修改系统时间进行测试

优点：相比于mysqldump，nb3体积相比于sql更小，因为在数据查询的同时进行了二进制压缩

缺点：还原速度稍低

bat脚本+window定时任务

单个数据库备份

@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
set "Hms=%time:~0,2%%time:~3,2%%time:~6,2%"
set "DateTime=%Ymd%%Hms%"
set host=127.0.0.1
set port=3306
set username=your_username
set password=your_password
set dbname=test
set backupfile=D:\db_backup\%dbname%-%Ymd%.sql
set orderPath="E:\Program Files x86\MySQL8.0\MySQL Server 8.0\bin\mysqldump.exe"
%orderPath% -h%host% -P%port% -u%username% -p%password% --single-transaction --quick %dbname%>%backupfile%
@echo on

保存到txt文件，后缀名改为bat，要注意备份文件路径必须是存在的，mysqldump脚本路径正确

备份命令中使用单一事务和快速插入

创建定时任务：

1. 打开任务计划程序
2. 选中任务计划程序库
3. 创建基本任务
4. 输入名称、描述
5. 配置可执行文件路径、配置执行时间

数据库批量备份

@echo off
:: 允许使用循环变量
setlocal enabledelayedexpansion

:: 构造日期字符串
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
set "Hms=%time:~0,2%%time:~3,2%%time:~6,2%"
set "DateTime=%Ymd%%Hms%"
:: 数据库连接参数
set host=127.0.0.1
set port=3306
set username=your_username
set password=your_password
:: 备份文件地址
set "backupPath=D:\db_backup\%Ymd%"
if not exist "%backupPath%" ( mkdir "%backupPath%")
set orderPath="E:\Program Files x86\MySQL8.0\MySQL Server 8.0\bin\mysqldump.exe"
:: 数据库列表
set databases=test;fengnan2021;huanghua2020

:: 循环备份每个数据库
for %%i in (%databases%) do (
    set backupfile=%backupPath%\%%i-%DateTime%.sql
    %orderPath% -h%host% -P%port% -u%username% -p%password% --single-transaction --quick %%i > "!backupfile!"
)
endlocal
@echo on

备份时操作数据库

1. navicat备份时可以正常对数据进行增删改查，执行备份时是以表的顺序依次备份
2. 执行mysqldump时，执行脚本添加–single-transaction --quick 属性，运行脚本时可以正常操作数据库，没加就不行