mysql备份设置主从同步

1.创建备份脚本

此mysql是由docker启动,挂在宿主机的/data/mysql目录下

创建文件backup.sh

#!/bin/bash

# 获取当前日期和时间，并格式化为 YYYYMMDDHHMMSS
CURRENT_DATETIME=$(date +%Y%m%d%H%M%S)
# 定义日志文件路径
LOG_FILE="/home/crawler/backupdatabase/backupmysql_${CURRENT_DATETIME}.log"

sudo tar -czvf /data/backup/mysql/mysql_backup_$(date +%Y%m%d%H%M%S).tar.gz /data/mysql/data  >> "${LOG_FILE}" 2>&1

echo "Backup finished at $(date)" >> "${LOG_FILE}"

2.定时脚本

# 输入命令
crontab -e
# 增加定时任务,凌晨四点启动
0 4 * * * /bin/bash /home/crawler/backupdatabase/backup.sh

3.恢复单张表数据

修改MySQL配置文件：my.cnf

innodb_force_recovery = 1

我这里尝试到4才可以启动,但是发现复制过来的ware_price.idb文件没有展示

3.使用mysqldump备份mysql数据

docker exec mysql_backup sh -c 'exec mysqldump  -uroot -p"your password"' > /data/backup.sql

查看所有库名

docker exec -it mysql_backup mysql -u root -p'your password' -e "SHOW DATABASES;"

#!/bin/bash

# 设置变量
MYSQL_POD_NAME=$(kubectl get pods --namespace your-namespace -l app=mysql -o jsonpath="{.items[0].metadata.name}")
BACKUP_DIR="/path/to/backup"
TIMESTAMP=$(date +"%F")
BACKUP_FILE="$BACKUP_DIR/mysql-backup-$TIMESTAMP.sql"

# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR

# 执行 MySQL 备份
kubectl exec -n your-namespace $MYSQL_POD_NAME -- mysqldump -u your_user -p'your_password' --all-databases > $BACKUP_FILE

# 输出备份结果
if [ $? -eq 0 ]; then
    echo "MySQL backup successful: $BACKUP_FILE"
else
    echo "MySQL backup failed"
fi

定时任务

0 4 * * * /bin/bash /home/crawler/backupdatabase/backup.sh

4.主从备份

1.主服务器操作

-- 创建用户，使用 % 允许从任意IP连接
CREATE USER 'repuser'@'%' IDENTIFIED BY 'your password';
-- 赋予复制所需权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repuser'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

主服务器先不写入数据,要不然position会变化

## 主节点查询
mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                       | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------------+-------------------+
| mysql-bin.000001 |  1579916 |              | mysql,mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------------+-------------------+
1 row in set (0.00 sec)

将主服务器的数据导出,从服务器数据需要与住服务器数据保持一致

docker exec -it fa98fc63945e mysqldump -P 33306 -u root -pyour_password \
    --all-databases \
    --single-transaction \
    --master-data=2 \
    --triggers \
    --routines \
    --events | gzip > new_backup.sql.gz

2.从服务器操作

## 从节点执行
change master to master_host='47.109.68.100',master_user='repuser',master_password='*****',MASTER_PORT=33306,MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=1579916;

将数据导入到从服务器

docker exec -i d7b10e2812b3 mysql -u root -H 192.168.100.103 -p ZZwl@2024! < full_backup.sql

另开一个终端看下执行状态

docker exec -it d7b10e2812b3 mysql -u root -pyour_pasword -e "SHOW PROCESSLIST\G"

查看主从状态

docker exec -it d7b10e2812b3 mysql -u root -pyour_pasword -e "show slave status\G;"

3.从库新增查询用户

# 新增用户
CREATE USER 'rpselect'@'%' IDENTIFIED BY 'your_pasword';
# 授予查询权限：
GRANT SELECT ON your_database.* TO 'rpselect'@'%';
# 授予所有库查询权限
GRANT SELECT ON *.* TO 'rpselect'@'%';
# 如果授予增删改查权限
-- 授予远程访问权限
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%' WITH GRANT OPTION;
# 刷新权限
FLUSH PRIVILEGES;