mysql数据库备份恢复(逻辑备份/物理备份)

数据库备份方式

逻辑备份：备份内容是sql语句形式，速度慢，建议数据量小时使用
物理备份：备份数据块文件，速度块，需要使用其他软件rpm包

---

逻辑备份:

提示：使用mysql自带的mysqldump进行备份

全库备份
-u用户名 -p密码
-S .sock文件路径
–single-transaction保持数据一致性的参数
-A 全库
>导出文件路径

mysqldump -uroot -proot -S /tmp/my3306.sock --single-transaction -A > all.sql

指定数据库备份

mysqldump -uroot -p  -S /tmp/mysql3306.sock --single-transaction -B test1 test2 > datebases.sql

指定数据库的表格备份

mysqldump -uroot -p  -S /tmp/mysql3306.sock --single-transaction -B test1 test2 > datebases.sql

逻辑备份恢复数据:

两种方式，一种是在mysql中用source 运行备份出来的 .sql文件
一种写非交互命令

/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock < all.sql

---

物理备份:

使用xtrabackup进行备份

备份分为全备与增备
增备的形式节省空间，但是需要建立在有一次全备的基础。如果中间出现某次增备失败，可能会导致所有的备份失效。
备份时，定期全呗，中间每天可以进行增量备份，避免出现增量备份文件损坏，导致备份数据失效

备份的内容是数据库文件，恢复时，全部复制到数据库文件目录即可

复制之前需要对所有xtrabackup-log进行应用，这样能保证数据全部更新到数据库备份结束的时间
全库备份
-u用户名
-S sock文件路径 也可以使用 -H ip地址 -P 端口登陆指定数据库

innobackupex --defaults-file=/etc/my3306.cnf --no-timestamp --user root --password root -S /tmp/mysql.sock /tmp/xtrabackup/all-20220325.bak

备份的地址是存储备份文件的文件夹

数据恢复
应用xtrabackup日志
与备份语句差–apply-log 这个参数

innobackupex --defaults-file=/etc/my3306.cnf --no-timestamp --user root --password root -S /tmp/mysql.sock --apply-log /tmp/xtrabackup/all-20220325.bak

关闭数据库

mysqladmin shutdown -uroot -proot

复制文件

cp -r /tmp/xtabackup/all-20220325.bak/* /home/mysql3306/mysql3306/

需要使用chown -r mysql:mysql 文件路径 更改文件的权限

增量备份

首先进行一次全备库

innobackupex --defaults-file=/etc/my3306.cnf --no-timestamp --user root --password root -S /tmp/mysql.sock /tmp/xtrabackup/all-20220325.bak

根据全备记录进行增备
增量备份中–incremental --incremental-basedir=/tmp/xtrabackup/inc-20220326.bak用来指定基于哪个版本的备份内容进行增量备份，每次进行增量备份时，这个参数需要调整成上次备份的文件路径

innobackupex --defaults-file=/etc/my3306.cnf --no-timestamp --user root --password root -S /tmp/mysql.sock --incremental --incremental-basedir=/tmp/xtrabackup/all-20220325.bak /tmp/xtrabackup/inc-20220326.bak
innobackupex --defaults-file=/etc/my3306.cnf --no-timestamp --user root --password root -S /tmp/mysql.sock --incremental --incremental-basedir=/tmp/xtrabackup/inc-20220326.bak /tmp/xtrabackup/inc-20220327.bak

数据恢复
依次应用日志，并不执行回滚
比正常执行应用日志的语句多了 --redo-only参数，这样可以记录在当前redu日志里没有进行commit的数据，如果后面的备份集进行了commit，不会出现数据错误。

innobackupex --defaults-file=/etc/my3306.cnf --user root --password root -S /tmp/mysql.sock --apply-log --redo-only /tmp/xtrabackup/all-20220325.bak
innobackupex --defaults-file=/etc/my3306.cnf --user root --password root -S /tmp/mysql.sock --apply-log --redo-only --incremental-dir=/tmp/xtrabackup/inc-20220326.bak /tmp/xtrabackup/all-20220325.bak 

最后一次日志应用，执行回滚
这样就会将最后一次备份时进行rollback的语句也进行回滚

innobackupex --defaults-file=/etc/my3306.cnf --user root --password root -S /tmp/mysql.sock --apply-log --incremental-dir=/tmp/xtrabackup/inc-20220327.bak /tmp/xtrabackup/all-20220325.bak 

关闭数据库

mysqladmin shutdown -uroot -proot

复制文件

cp -r /tmp/xtabackup/all-20220325.bak/* /home/mysql3306/mysql3306/