MySQLBackup备份数据库的操作过程,
目录
- 1、安装部署
- 2 、配置备份管理员
- 3、 backup-to-image方式备份数据库
- 3.1 全量备份
- 3.2 全量备份还原
- 3.2 增量备份
- 3.3 增量备份还原
- 4、datafile方式备份数据库
- 4.1 完全备份
- 4.2 完全备份恢复
- 4.3增量备份
- 4.4增量备份还原
环境: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 | +--------------------+
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 | +-----+---------+--------+------+------------------+
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 | +--------------------+
[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 | +-----+---------+--------+------+------------------+
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 | +--------------------+
[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 | +-----+---------+--------+------+------------------+
4.3增量备份
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.4增量备份还原
[root@node5 ~]# mysql -e 'drop database school'
#删库
[root@node5 ~]# mysql -e 'show databases'
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
[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 | +-----+---------+--------+------+------------------+
到此这篇关于MySQLBackup备份数据库的文章就介绍到这了,更多相关MySQLBackup备份数据库内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友!
您可能感兴趣的文章:- 详解Mysql之mysqlbackup备份与恢复实践
- MySQL使用innobackupex备份连接服务器失败的解决方法
- MySQL使用xtrabackup进行备份还原操作
- 安装使用Percona XtraBackup来备份恢复MySQL的教程
- 编写脚本令Xtrabackup对MySQL数据进行备份的教程
- MySQL定时备份数据库操作示例
- Python实现定时备份mysql数据库并把备份数据库邮件发送
- Navicat for MySQL定时备份数据库及数据恢复详解
- mysqldump备份数据库时排除某些库的实例
- mysql自动定时备份数据库的最佳方法(windows服务器)