devbox
秋刀鱼在做梦
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mysql数据库主从、全量、增量备份还原_mysql主从全量备份

作者：秋刀鱼在做梦 | 2024-08-14 04:34:36

赞

踩

mysql主从全量备份

数据库主从、全量、增量

情景一

  1. 主数据库的服务器发生停电，宕机，
  2. 机房发生了地震，海啸，战争等天灾人祸

方案：主从备份

优点：数据库自带备份功能，配置简单

缺点：影响所有租户的使用

情景二

  1. 相关运维人员误删表数据

方案：全量备份

优点：可以备份全部数据

缺点：由于每次需要备份还原的数据量相当大，因此备份还原所需时间较长。

情景三

  1. 某个租户误删除数据

方案：增量备份。在测试库还原全量备份数据，然后再还原最近的增量数据。最后使用dts迁移工具，实现当前租户数据的还原。

优点：没有重复数据，备份量不大，时间短。

缺点：对单个租户的数据备份和恢复困难，需要运维人员专业素质较高，以及细心严谨的操作

1、主从备份

使用主从同步实现

环境准备：

主库ip：192.168.1.130

从库ip：192.168.1.131

数据库：mysql8.0.22

主库配置

  1. 修改主库配置文件

    $ vim /etc/my.cnf

    • 1
    [mysqld]
server-id=1
log-bin=mysql-bin
#不需要同步的数据库  
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#需要同步的数据库
binlog-do-db=ly_ycasp_qxpt0
binlog-do-db=ly_ycasp_qxpt1
binlog-do-db=ly_ycasp_qxpt2
binlog-do-db=ly_ycasp_qxpt3
binlog-do-db=ly_ycasp_qxpt4
 
#mysql复制模式，三种：SBR（基于sql语句复制），RBR（基于行的复制），MBR（混合模式复制）
binlog_format=MIXED 
#混合模式复制，可靠性更高

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

  2. 重启数据库

    $ systemctl restart mysqld

    • 1

  3. 重新登陆数据库并锁主库

    $ mysql -uroot -padmin

    • 1
    mysql> flush tables with read lock;

    • 1

  4. 从主服务器中创建从库用户,专门使用这个用户做主从同步，这里设置了用户名为repl，密码为repl

    # 改密码规则
mysql> set global validate_password.policy=0;
mysql> set global validate_password.length=1;
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
mysql> GRANT ALL ON *.* TO 'repl'@'%';
# 修改密码为用不过期
mysql> ALTER USER 'repl'@'%' IDENTIFIED BY 'repl' PASSWORD EXPIRE NEVER;
# 修改密码并指定加密规则为mysql_native_password
mysql> ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
# 刷新权限
mysql> flush privileges;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

  5. 查看主库状态，这里的File和Position会在从库中用到

    mysql> show master status \G

    • 1

    在这里插入图片描述

  6. 解锁主库表

    mysql> unlock tables;

    • 1

从库配置

  1. 关闭数据库

    $ systemctl stop mysqld

    • 1

  2. 配置从库my.cnf配置文件

    $ vim /etc/my.cnf

    • 1
    [mysqld]
server-id=2
log-bin=mysql-bin
binlog_format=MIXED

    • 1
    • 2
    • 3
    • 4

  3. 重启数据库

    $ systemctl restart mysqld

    • 1

  4. 登陆数据库，从库设置slave复制主库数据

    $ mysql -uroot -padmin

    • 1

    这里为主库配置第五步show master status的两个值
    master_log_post为主库中Postion的值
    master_log_file为主库中File的值

    mysql> change master to master_host='主mysql服务器ip',master_user='repl',master_password='repl',master_log_file='主库中File的值',master_log_pos=主库中Postion的值;

    • 1

  5. 启动从库复制

    mysql> start slave;

    • 1

  6. 查看从库是否同步上主库，红色框框两个yes说明成功了

    mysql> show slave status \G

    • 1

    在这里插入图片描述

踩坑提示：

如果在“Slave_SQL_Running_State”中报如下错误

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must bedifferent for replication to work.

  • 1

删掉从库服务器中的auto.cnf文件或者换名字，重启从数据库即可

$ mv /var/lib/mysql/data/auto.cnf /var/lib/mysql/data/auto.cnf.bak 

$ systemctl restart mysqld

  • 1
  • 2
  • 3

2、全量备份与还原

创建专用备份文件夹

mkdir -p /data/backup

  • 1

全量备份命令

/usr/bin/mysqldump -uroot -padmin  --lock-all-tables --flush-logs test > /home/backup.sql

  • 1

如上一段代码所示，其功能是将 test 数据库全量备份。

其中 MySQL 用户名为：root

密码为：admin

备份的文件路径为：/home（当然这个路径也是可以按照个人意愿修改的。）

备份的文件名为：backup.sql

参数 flush-logs：使用一个新的日志文件来记录接下来的日志参数

lock-all-tables：锁定所有数据库

恢复全量备份

执行shell命令

mysql -h localhost -uroot -padmin < bakdup.sql

  • 1

或者登录数据库后执行

mysql> source /home/backup/bakdup.sql

  • 1

数据库备份脚本文件

/data/backup.sh

#!/bin/bash  
#备份的数据库名
DATABASES=(
            "ly_ycasp_qxpt0"
            "ly_ycasp_qxpt1" 
            "ly_ycasp_qxpt2"
            "ly_ycasp_qxpt3"
            "ly_ycasp_qxpt4" 
)
USER="root"
PASSWORD="admin"
 
BACKUP_DIR=/data/backup
LOGFILE=/data/backup/data_backup.log 
DATE=`date +%Y%m%d_%H%M`

cd $BACKUP_DIR
#开始备份之前，将备份信息头写入日记文件   
echo "--------------------" >> $LOGFILE   
echo "BACKUP DATE:" $(date +"%y-%m-%d %H:%M:%S") >> $LOGFILE   
echo "-------------------" >> $LOGFILE

for DATABASE in ${DATABASES[*]};
	do
  		/usr/bin/mysqldump -u$USER -p$PASSWORD --events  -R --opt  $DATABASE | gzip >${BACKUP_DIR}/${DATABASE}_${DATE}.sql.gz
	if [ $? == 0 ];
  	then
    	echo "$DATE--$DATABASE is backup succeed" >> $LOGFILE
  	else
    	echo "Database Backup Fail!" >> $LOGFILE
	fi
done

#删除30天以上的备份文件  
find $BACKUP_DIR  -type f -mtime +30 -name "*.gz" -exec rm -f {} \;


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

定时备份

输入如下命令，进入 crontab 定时任务编辑界面

crontab -e

  • 1

每天执行

0 0 * * * sh /data/backup.sh

  • 1

每周执行

0 0 * * 0 sh /data/backup.sh

  • 1

3、增量备份与还原

  1. 查看log_bin是否开启

    show variables like '%log_bin%';

    • 1

    如下命令所示，则为未开启

    mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

  2. 修改/etc/my.cnf，在[mysqld]下添加log-bin=mysql-bin

    再次查看

    show variables like '%log_bin%';

    • 1
    mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

  3. 增量备份
    增量备份脚本

    #!/bin/bash
#在使用之前，请提前创建以下各个目录
backupDir=/data/backup/daily
#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/var/lib/mysql
#mysql的数据目录
logFile=/data/backup/daily_bak.log
BinFile=/var/lib/mysql/mysql-bin.index
#mysql的index文件路径，放在数据目录下的

mysqladmin -uroot -p123456 flush-logs
#这个是用于产生新的mysql-bin.00000*文件
# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
   base=`basename $file`
   echo $base
   #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
   NextNum=`expr $NextNum + 1`
   if [ $NextNum -eq $Counter ]
   then
       echo $base skip! >> $logFile
   else
       dest=$backupDir/$base
       if(test -e $dest)
       #test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
       then
           echo $base exist! >> $logFile
       else
           cp $mysqlDir/$base $backupDir
           echo $base copying >> $logFile
        fi
    fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    新建测试表

    CREATE TABLE `ly_ycasp_qxpt0`.`test`  (
     `id` int NOT NULL AUTO_INCREMENT,
     `name` varchar(255) NULL,
     PRIMARY KEY (`id`)
);

    • 1
    • 2
    • 3
    • 4
    • 5

    插入三条数据

    insert into test (id, name) values(1, 'test1');
insert into test (id, name) values(2, 'test2');
insert into test (id, name) values(3, 'test3');
insert into test (id, name) values(4, 'test4');

    • 1
    • 2
    • 3
    • 4

    误删id=3和id=4这两条数据

    delete from test where id = 3;
delete from test where id = 4;

    • 1
    • 2

    继续插入id=5和id=6条数据

    insert into test (id, name) values(5, 'test5');
insert into test (id, name) values(6, 'test6');

    • 1
    • 2

    增量备份

    #进行增量备份,生成新的备份日志文件，这样旧的日志文件就不会输入新的日志，方便排查问题。
[root@localhost data]# mysqladmin -uroot -padmin flush-logs

    • 1
    • 2

    查看增量备份文件

    备份文件在/var/lib/mysql目录下，生成最新新的日志文件为mysql-bin.000002，我们通过旧的日志文件mysql-bin.000001来还原数据。

    -rw-r-----. 1 mysql mysql      4361 Jan  5 09:50 mysql-bin.000001
-rw-r-----. 1 mysql mysql       156 Jan  5 09:50 mysql-bin.000002

    • 1
    • 2

  4. 解码就可以看生成的日志文件内容

    mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000001  > /opt/bk01.txt

    • 1

在这里插入图片描述

​
在这里插入图片描述

  1. 根据日志文件还原数据

    在测试库先还原全量数据，然后再根据这里的增量备份，使数据还原

    基于位置恢复
    恢复数据到误操作前一次的指定位置

    mysqlbinlog --stop-position='操作id'  二进制日志 | mysql -u 用户名 -p 密码

    • 1
    [root@localhost mysql]# mysqlbinlog --stop-position='3323' mysql-bin.000001 | mysql -uroot -padmin

    • 1

    恢复数据到误操作后一次的指定位置

    mysqlbinlog --start-position='操作id'  二进制日志 | mysql -u 用户名 -p 密码

    • 1
    [root@localhost mysql]# mysqlbinlog --start-position='4078' mysql-bin.000002 | mysql -uroot -padmin

    • 1

    基于时间恢复
    从日志开头截止到某个时间点的恢复

    mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p密码

    • 1

    从某个时间点到日志结尾的恢复

    mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p密码

    • 1

    从某个时间点到某个时间点的恢复

    mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-
datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p密码

    • 1
    • 2

4、总结

在主从备份的基础上

数据量小，每天做一次全量备份，每半天做一次增量备份

数据量大，每周做一次全量备份，每天做一次增量备份

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/977980
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号