MySQL数据库的备份与恢复（物理备份/逻辑备份）

1、物理备份的方式：

完全备份，增量备份，差异备份

物理备份需要安装xtrabackup-percona

wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm

rpm -ivh percona-release-latest.noarch.rpm

yum -y install percona-xtrabackup-24.x86_64

注意：需要先安装xtrabackup-percona，再安装MySQL，不然容易报错

（1）完全备份

创建备份文件夹：   mkdir /xtrabackup

备份：innobackupex --user=root --password='123' /xtrabackup

恢复流程：（1）停止数据库（2）清理环境（3）重演回滚（4）恢复数据（5）修改权限（6）启动数据库

1. 停止数据库，清理环境

systemctl stop firewalld

rm -rf /var/lib/mysql/*

rm -rf /var/log/mysqld.log

rm -rf /var/log/mysql-slow/slow.log

1. 重演回复

innobackupex --apply-log /xtrabackupex/2024-03-09_11-28-00

1. 确认恢复之前要确认配置文件里数据库目录指定

[root@localhost ~] # cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

1. 恢复数据

innobackupex --copy-back /xtrabackup/2024-03-09_11-28-00

1. 修改权限

chown mysql.mysql /var/lib/mysql -R

1. 启动数据库

systemctl start mysqld

（2）增量备份

第一次：innobackupex --user=root --password='123' /xtrabackup

第二次：innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-03-09_11-28-00

第三次：innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-nasedir=/xtrabackip/2024-03-10_11-28-00

基于上一次备份进行增量备份

systemctl stop mysqld

依次回滚：

innobackupex --apply-log --redo-only /xtrabackup/2024-03-09_11-28-00

innobackupex --apply-log --redo-only /xtrabackup/2024-03-09_11-28-00 --incremental-dir=/xtrabackup/2024-03-10_11-28-00

恢复：

innobackupex --copt-back /xtrabackup/2024-03-09_11-28-00

修改权限：

chown -R mysql.mysql /var/lib/mysql

systemctl start mysqld

（3）差异备份

第一次：innobackupex --user=root --password='123' /xtrabackup

第二次：innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/第一次完备文件

第三次：innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/第一次完备文件

回滚：

systemctl stop mysqld

innobackupex --apply-log --redo-only /xtrabackup/完备文件 --incremental-dir=/xtrabackup/某个差异备份

恢复：

innobackupex --copy-back /xtrabackup/完备文件

修改权限：

chown -R mysql.mysql /var/lib/mysql

systemctl start mysqld

2、逻辑备份

远程备份语法：mysqldump -h 服务器IP -u用户名 -p密码  数据库名  >  备份文件.sql

本地备份语法：mysqldump -u用户名 -p密码   数据库名  >  备份文件.sql

常用备份选项：-A备份所有库     -B备份多个库   --no-data(-d)只导出数据库表结构

注意：

在对一个正在运行的数据库备份的时候要慎重！如果一定要在服务运行期间进行备份，可以添加--single-transaction选项，例如：mysqldump --single-trancaction -uroot -p'123' dbname > mysql.sql

（1）备份表

mysqldump -uroot -p'123' database_name table_name > name.sql

mysqldump -uroot -p'123' database_name table_name1 table_name2 > name.sql

（2）备份库

mysqldump -uroot -p'123' -B database_name1 database_name2 > name.sql

（3）备份所有库

mysqldump -uroot -p'123' -A > name.sql

（4）恢复库

mysql -uroot -p'123' database_name < name.sql

（5）恢复表

mysql -uroot -p'123' database_name < name.sql

（6）只备份表结构与恢复

mysqldump -uroot -p'123' -d database_name table_name > name.sql

mysql -uroot -p'123' -D database_name < name.sql

（7）只导出导入数据

查询导入导出目录：show variables like "secure_file_priv";

如果想要修改导出目录，需要修改/etc/my.cnf配置文件

在[mysqld]里添加：secure_file_priv=/sql

重启MySQL

导出指令：select * from t3 into outfile '/sql/test.t3.bak';

导入指令：load data infile '/sql/test.t3.bak' into table t3;

3、通过binlog恢复数据

首先需要开启binlog日志，修改配置文件/etc/my.cnf

追加log_bin=/var/log/sql_bin/mylog

server_id=1

（1）创建目录并修改权限

mkdir /var/log/sql_bin

chown mysql.mysql /var/log/sql_bin

systemctl restart mysqld

mysql>flush logs;   (刷新binlog日志会截断产生新的日志文件)

（2）找到恢复的sql语句的起始位置和结束位置

mysqlbinlog mysql.000002

（3）恢复数据

mysqlbinlog --start-positon 219 --stop-position 321 mysql.000002 | mysql -uroot -p'123'