Logo

Menu

张帆
张帆
Published on 2025-08-12 / 56 Visits
0
0

Mysql主从部署及备份恢复

主服务器配置

1. 安装 MySQL

# 添加 MySQL Yum 仓库
wget https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
rpm -ivh mysql80-community-release-el7-7.noarch.rpm
 
# 导入 MySQL 官方最新的 GPG 密钥
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
 
# 安装 MySQL 服务器
yum install mysql-community-server -y
 
# 启动 MySQL 服务并设置开机自启
systemctl start mysqld
systemctl enable mysqld

2. 获取初始密码并修改

# 获取临时密码
grep 'temporary password' /var/log/mysqld.log
 
# 使用临时密码登录
mysql -uroot -p
 
# 修改密码（在 MySQL 命令行中执行）
ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';
 
#示例
ALTER USER 'root'@'localhost' IDENTIFIED BY 'IMOMTest@123';

3. 配置主服务器

编辑 MySQL 配置文件 /etc/my.cnf

vi /etc/my.cnf

添加以下内容到 [mysqld] 部分

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW

重启 MySQL 服务

systemctl restart mysqld

4. 创建复制用户

mysql -uroot -p
 
-- 创建用于复制的用户
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl_Password@123';
 
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
 
-- 刷新权限
FLUSH PRIVILEGES;
 
-- 查看主服务器状态，记录 File 和 Position 值
SHOW MASTER STATUS;

5. 防火墙配置

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
# 查看防火墙配置
firewall-cmd --list-all

从服务器配置

1. 安装 MySQL（参考主服务器）

2. 获取初始密码并修改（参考主服务器）

3. 配置从服务器

编辑 MySQL 配置文件 /etc/my.cnf

vi /etc/my.cnf

添加以下内容到 [mysqld] 部分

[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1

重启 MySQL 服务：

systemctl restart mysqld

4. 配置从服务器连接主服务器

mysql -uroot -p
 
-- 停止从服务器复制
STOP SLAVE;
 
-- 配置主服务器连接信息（使用主服务器SHOW MASTER STATUS获取的值）
CHANGE MASTER TO MASTER_HOST='主服务器IP',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000001',  -- 主服务器SHOW MASTER STATUS中的File值
MASTER_LOG_POS=154;                  -- 主服务器SHOW MASTER STATUS中的Position值
 
# 示例
CHANGE MASTER TO MASTER_HOST='192.168.1.103', MASTER_USER='repl', MASTER_PASSWORD='repl_Password@123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1177;
 
-- 启动从服务器复制
START SLAVE;
 
-- 查看从服务器状态
SHOW SLAVE STATUS\G

5、 其他命令

# 从库同步设置有问题时，彻底清除复制配置
STOP SLAVE;
RESET SLAVE ALL;

验证主从复制

1. 主服务器建表插入数据

#建测试数据库
CREATE DATABASE IF NOT EXISTS my_test_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
#建测试表
create table if not exists `my_test_database`.`sys_dept` (
  dept_id           bigint(20)      not null auto_increment    comment '部门id',
  parent_id         bigint(20)      default 0                  comment '父部门id',
  ancestors         varchar(50)     default ''                 comment '祖级列表',
  dept_name         varchar(30)     default ''                 comment '部门名称',
  order_num         int(4)          default 0                  comment '显示顺序',
  leader            varchar(20)     default null               comment '负责人',
  phone             varchar(11)     default null               comment '联系电话',
  email             varchar(50)     default null               comment '邮箱',
  status            char(1)         default '0'                comment '部门状态（0正常 1停用）',
  del_flag          char(1)         default '0'                comment '删除标志（0代表存在 2代表删除）',
  create_by         varchar(64)     default ''                 comment '创建者',
  create_time 	    datetime                                   comment '创建时间',
  update_by         varchar(64)     default ''                 comment '更新者',
  update_time       datetime                                   comment '更新时间',
  primary key (dept_id)
) engine=innodb auto_increment=200 comment = '部门表';
 
#插入数据
insert into `my_test_database`.`sys_dept` values(100,  0,   '0',          '赛意信息',   0, '赛意', '15888888888', 'ry@qq.com', '0', '0', 'admin', sysdate(), '', null);
 

验证备份恢复

1. 主服务备份恢复

1.1 使用mysqldump备份

# 备份单个数据库,要修改username，--set-gtid-purged=OFF：如果你未使用 GTID，必须关闭，否则从库可能报错
mysqldump -u username -p --single-transaction --source-data=2 --set-gtid-purged=OFF database_name > backup.sql
 
# 备份所有数据库,要修改username,--set-gtid-purged=OFF：如果你未使用 GTID，必须关闭，否则从库可能报错
mysqldump -u username -p --single-transaction --source-data=2 --set-gtid-purged=OFF --all-databases > all_databases_backup.sql
 
#获取binlog 文件名和位置
grep "CHANGE MASTER TO" full_backup.sql
#获取binlog 文件名和位置会输出类似以下日志，后续从库同步会用到
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156;

1.2 使用备份文件恢复

# 恢复单个数据库
mysql -u username -p database_name < /backups/mysql/binlog_backup.sql
#示例
mysql -uroot -p my_test_database < /backups/mysql/all_databases_backup.sql

1.3 自动化备份脚本示例

#!/bin/bash
DATE=$(date +%Y%m%d%H%M)
BACKUP_DIR="/backups/mysql"
USER="username"
PASS="password"
 
# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE
 
# 备份所有数据库
mysqldump -u$USER -p$PASS --all-databases | gzip > $BACKUP_DIR/$DATE/all_databases.sql.gz
 
# 删除7天前的备份
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

 

2. 重建从库

# 1. 暂停同步
STOP SLAVE;
# 2.清空所有数据库（此动作会删除从库所有数据）
RESET SLAVE ALL;
# 3. 删除所有数据库，除系统库外
-- 查看所有数据库
SHOW DATABASES;
 
-- 手动删除每个业务数据库（示例）
DROP DATABASE IF EXISTS your_db1;
DROP DATABASE IF EXISTS your_db2;
-- ...
 
# 4.导入主库备份
mysql -u root -p < full_backup.sql
 

 

framework-db 配置
redis 部署升级

Comment