• MySQLBackup备份数据库

    环境：MySQL 8.0.36

    1、安装部署

    [root@node5 ~]# wget -c https://edelivery.oracle.com/osdc/softwareDownload?fileName=V1040085-01.zip
    [root@node5 ~]# ll
    total 22776
    -rw-------. 1 root root 1066 Jan 21 14:59 anaconda-ks.cfg
    -r–r–r–. 1 root root 23316009 Feb 24 18:50 V1040085-01.zip
    [root@node5 ~]# unzip V1040085-01.zip
    [root@node5 ~]# yum install mysql-commercial-backup-8.0.36-1.1.el8.x86_64.rpm -y

    2 、配置备份管理员

    mysql> create user 'mysqlbackup'@'localhost' identified by 'MySQL@123';
    Query OK, 0 rows affected (0.09 sec)
    mysql> grant all on *.* to 'mysqlbackup'@'localhost';
    Query OK, 0 rows affected (0.03 sec)

    3、 backup-to-image方式备份数据库

    3.1 全量备份

    [root@node5 ~]# mkdir /data #创建备份目录
    [root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock --backup-image=my_full_bak.mbi --backup-dir=/data/backup --show-progress --compress backup-to-image

    [root@node5 ~]# ll /data/backup/ #查看备份后的文件
    total 4408
    -rw-r–r–. 1 root root 255 Feb 24 19:59 backup-my.cnf
    drwxr-x—. 2 root root 4096 Feb 24 19:59 datadir
    drwxr-x—. 2 root root 4096 Feb 24 19:59 meta
    -rw-r-----. 1 root root 4475730 Feb 24 19:59 my_full_bak.mbi
    -rw-r-----. 1 root root 19936 Feb 24 19:59 server-all.cnf
    -rw-r-----. 1 root root 632 Feb 24 19:59 server-my.cnf

    3.2 全量备份还原

    1、检验

    [root@node5 ~]# mysqlbackup --backup-image=/data/backup/my_full_bak.mbi list-image
    #通过list-image查看备份image中的文件内容
    [root@node5 ~]# mysqlbackup --backup-image=/data/backup/my_full_bak.mbi validate
    #通过validate验证备份image的有效性

    [root@node5 ~]# mysql -e 'drop database school' #删库
    [root@node5 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    2、还原image

    #这两步必做
    [root@node5 ~]# systemctl stop mysqld.service #关闭mysqld服务
    [root@node5 ~]# rm -rf /var/lib/mysql/* #清空mysql的datadir目录
    [root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql --backup-image=/data/backup/my_full_bak.mbi --backup-dir=/data/backup/tmp --uncompress copy-back-and-apply-log
    [root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/* #更改权限
    [root@node5 ~]# systemctl start mysqld.service
    [root@node5 ~]# mysql -e 'select * from school.stu' #确认恢复

    +-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    3.2 增量备份

    1、第一次增量备份
    mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (5, 'Emma', 'Female', 20, 'Mathematics'),(6, 'Tom', 'Male', 22, 'Computer Science');

    [root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock --compress --incremental --incremental-base=dir:/data/backup --backup-dir=/data/backup/incr1 --backup-image=my_inc1_bak.mbi backup-to-image
    [root@node5 ~]# ll /data/backup/incr1/
    total 1792
    -rw-r–r–. 1 root root 255 Feb 24 20:22 backup-my.cnf
    drwxr-x—. 2 root root 4096 Feb 24 20:22 datadir
    drwxr-x—. 2 root root 4096 Feb 24 20:22 meta
    -rw-r-----. 1 root root 1796619 Feb 24 20:22 my_inc1_bak.mbi
    -rw-r-----. 1 root root 19936 Feb 24 20:22 server-all.cnf
    -rw-r-----. 1 root root 505 Feb 24 20:22 server-my.cnf

    2、第二次增量备份
    mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (7, 'Lily', 'Female', 21, 'Engineering'),(8, 'Jack', 'Male', 19, 'Literature');

    [root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock --compress --incremental --incremental-base=history:last_backup --backup-dir=/data/backup/incr2 --backup-image=my_inc_bak.mbi backup-to-image
    #–incremental-base 可以写成history:last_backup不用写上次备份的dir，两个方法都可以

    [root@node5 ~]# ll /data/backup/incr2/
    total 1700
    -rw-r–r–. 1 root root 255 Feb 24 20:28 backup-my.cnf
    drwxr-x—. 2 root root 4096 Feb 24 20:28 datadir
    drwxr-x—. 2 root root 4096 Feb 24 20:28 meta
    -rw-r-----. 1 root root 1696167 Feb 24 20:28 my_inc_bak.mbi
    -rw-r-----. 1 root root 19936 Feb 24 20:28 server-all.cnf
    -rw-r-----. 1 root root 632 Feb 24 20:28 server-my.cnf

    3.3 增量备份还原

    [root@node5 ~]# mysql -e 'drop database school' #删库
    [root@node5 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    [root@node5 ~]# systemctl stop mysqld.service #关闭mysqld服务
    [root@node5 ~]# rm -rf /var/lib/mysql/* #清空mysql的datadir目录

    1、先还原完全备份
    [root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql --backup-image=/data/backup/my_full_bak.mbi --backup-dir=/data/backup/tmp1 --uncompress copy-back-and-apply-log
    #注意backup-dir刚才是/data/backup/tmp，这里得换一个

    2、第一次增量备份还原
    [root@node5 ~]# mysqlbackup --backup-image=/data/backup/incr1/my_inc1_bak.mbi --backup-dir=/data/backup/incr1/tmp2 --datadir=/var/lib/mysql/ --incremental copy-back-and-apply-log

    3、第二次增量备份还原
    [root@node5 ~]# mysqlbackup --backup-image=/data/backup/incr2/my_inc_bak.mbi --backup-dir=/data/backup/incr2/tmp3 --datadir=/var/lib/mysql/ --incremental copy-back-and-apply-log

    [root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/*
    [root@node5 ~]# systemctl start mysqld.service
    [root@node5 ~]# mysql -e 'select * from school.stu'

    +-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+

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

    4、datafile方式备份数据库

    环境：最好恢复初始环境，或只把上次备份的目录删了

    4.1 完全备份

    [root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock --backup-dir=/data/backup --show-progress --compress backup
    [root@node5 ~]# ll /data/backup/
    total 36
    -rw-r–r–. 1 root root 255 Feb 24 20:53 backup-my.cnf
    drwxr-x—. 6 root root 4096 Feb 24 20:53 datadir
    drwxr-x—. 2 root root 4096 Feb 24 20:53 meta
    -rw-r-----. 1 root root 19936 Feb 24 20:53 server-all.cnf
    -rw-r-----. 1 root root 632 Feb 24 20:53 server-my.cnf

    4.2 完全备份恢复

    [root@node5 ~]# mysql -e 'drop database school'
    [root@node5 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    [root@node5 ~]# systemctl restart mysqld.service
    [root@node5 ~]# systemctl stop mysqld.service
    [root@node5 ~]# rm -rf /var/lib/mysql/*

    [root@node5 ~]# mysqlbackup --backup-dir=/data/backup apply-log #备份过程中产生的日志文件
    [root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql/ --backup-dir=/data/backup copy-back-and-apply-log #将备份的文件拷贝到datadir下

    [root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/*
    [root@node5 ~]# systemctl start mysqld.service
    [root@node5 ~]# mysql -e 'select * from school.stu'

    +-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    4.2 增量备份

    1、第一次增量备份
    mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (5, 'Emma', 'Female', 20, 'Mathematics'),(6, 'Tom', 'Male', 22, 'Computer Science');

    [root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock --compress --incremental --incremental-base=dir:/data/backup --incremental-backup-dir=/data/backup/incr1 backup

    [root@node5 ~]# ll /data/backup/incr1/
    total 36
    -rw-r–r–. 1 root root 255 Feb 24 21:55 backup-my.cnf
    drwxr-x—. 6 root root 4096 Feb 24 21:55 datadir
    drwxr-x—. 2 root root 4096 Feb 24 21:55 meta
    -rw-r-----. 1 root root 19936 Feb 24 21:55 server-all.cnf
    -rw-r-----. 1 root root 632 Feb 24 21:55 server-my.cnf

    2、第二次增量备份
    mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (7, 'Lily', 'Female', 21, 'Engineering'),(8, 'Jack', 'Male', 19, 'Literature');

    [root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock --compress --incremental --incremental-base=history:last_backup --incremental-backup-dir=/data/backup/incr2 backup

    [root@node5 ~]# ll /data/backup/incr2/
    total 36
    -rw-r–r–. 1 root root 255 Feb 24 21:56 backup-my.cnf
    drwxr-x—. 6 root root 4096 Feb 24 21:56 datadir
    drwxr-x—. 2 root root 4096 Feb 24 21:56 meta
    -rw-r-----. 1 root root 19936 Feb 24 21:56 server-all.cnf
    -rw-r-----. 1 root root 632 Feb 24 21:56 server-my.cnf

    4.3 增量备份还原

    [root@node5 ~]# mysql -e 'drop database school' #删库
    [root@node5 ~]# mysql -e 'show databases'

    +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    [root@node5 ~]# systemctl stop mysqld.service #关闭mysqld服务
    [root@node5 ~]# rm -rf /var/lib/mysql/* #清空mysql的datadir目录

    [root@node5 ~]# mysqlbackup --backup-dir=/data/backup apply-log #备份过程中产生的日志文件
    [root@node5 ~]# mysqlbackup --incremental-backup-dir=/data/backup/incr1 --backup-dir=/data/backup/ apply-incremental-backup #增备apply-incremental-backup

    [root@node5 ~]# mysqlbackup --incremental-backup-dir=/data/backup/incr2 --backup-dir=/data/backup/ apply-incremental-backup

    [root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql --backup-dir=/data/backup/ copy-back-and-apply-log #将几次备份搞得文件拷到datadir

    [root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/*
    [root@node5 ~]# systemctl start mysqld.service
    [root@node5 ~]# mysql -e 'select * from school.stu'

    +-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
  • 相关阅读:
    第四章：前缀和、差分（数列）
    python爬取每日天气情况
    主流的深度学习推理架构有哪些（NCNNN）
    有效备考浙大MEM的五个好习惯建议
    (JavaSE)继承和多态
    visual-studio-code通过跳板机连接远程服务器的配置操作
    python opencv 读取mp4,上一帧，下一帧
    9.30小任务
    设计模式-Adapter
    openGauss通过VIP实现的故障转移
  • 原文地址：https://blog.csdn.net/weixin_72583321/article/details/136380664