目录
一、逻辑备份
备份的是建库、建表、插入等操作所执行SQL语句,适用于中小型数据库,效率相对较低。
本质:导出的是SQL语句文件
优点:不论是什么春初引擎,都可以使用mysqldump备成SQL语句
缺点:速度较慢,导入时可能出现格式不兼容的突发情况,无法做增量备份和累计增量备份。
语法:
远程备份:mysqldump -h 服务器 -u 用户名 -p 密码 数据库名 > 备份文件名.sql
本地备份:mysqldump -u 用户名 -p 密码 数据库名 > 备份文件名.sql
常用选项
备份所有库
[-A|--all-databases]
[root@haitao ~]# mysqldump -uroot -p'Haitao@123' --all-databases >all_db.sql
mysqldump -uroot -p'Haitao@123' --all-databases >all_db.sql
备份多个数据库
[-B|--database]
查看我拥有的数据库
[root@haitao ~]# mysqldump -uroot -p'Haitao@123' --databases xiaohan >one_db.sql
mysqldump -uroot -p'Haitao@123' --databases xiaohan >one_db.sql
只备份数据库表结构
[-d|--no-data]
查看有哪些表
[root@haitao ~]# mysqldump -uroot -p'Haitao@123' -d xiaohan haitao >one_tb.sql
mysqldump -uroot -p'Haitao@123' -d xiaohan haitao >one_tb.sql
备份表
语法:mysqldump -u 用户名 -p 密码 数据库名 表名 表名 表名 > 备份表名.sql
[root@haitao ~]# mysqldump -uroot -p'Haitao@123' xiaohan haitao > haitao_tb.sql
mysqldump -uroot -p'Haitao@123' xiaohan haitao > haitao_tb.sql
恢复数据库和表
恢复全部库
为保证数据一致性,应在恢复数据之前停止数据库对外的服务,停止binlog日志,因为binlog使用binlog日志恢复数据时也会产生binlog日志。
首先删除数据库和表
mysql> drop database xiaohan;
mysql> drop database xiaoxi;
[root@haitao ~]# mysql -uroot -p'Haitao@123' <all_db.sql
mysql -uroot -p'Haitao@123' <all_db.sql
登录数据库查看刚刚删除的库是否有恢复成功。
恢复单个或多个库
mysql> drop database xiaohan;
创建一个库
mysql> create database xiaohan;
恢复单个库
[root@localhost ~]# mysql -uroot -p'Haitao@123' xiaohan <one_db.sql
mysql -uroot -p'Haitao@123' xiaohan <one_db.sql
登录数据库查看是否恢复成功
恢复表
删除表
mysql> use xiaohan;
mysql> drop table haitao;
恢复表
[root@localhost ~]#mysql -uroot -p'Haitao@123' xiaohan <haitao_tb.sql
mysql -uroot -p'Haitao@123' xiaohan <haitao_tb.sql
验证是否恢复成功
[root@localhost ~]# mysql -p'Haitao@123'
mysql> use xiaohan;
mysql> show tables;
mysql> desc haitao;
二、物理备份
物理备份: 直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。
安装innobackup
[root@localhost ~]# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@localhost ~]# yum -y install percona-xtrabackup-24
yum -y install percona-xtrabackup-24
安装压缩工具
[root@localhost ~]# yum -y install qpress
yum -y install qpress
创建备份目录
[root@localhost ~]# mkdir -p /xtrabackup/{full,increment,difference}
mkdir -p /xtrabackup/{full,increment,difference}
full存放完整备份
increment存放增量备份
difference存放差异备份
完全备份
每次都将所有数据(不管自第一次备份以来有没有修改过),进行一次完整的复制,备份后会清除文件的存档属性,方便日后增量备份或者差异备份进行版本比较。
特点:占用空间大,备份速度慢,但恢复时一次恢复到位,恢复速度快。
登录数据库查看我们有哪些库
[root@localhost ~]# mysql -uroot -p"Haitao@123"
mysql -uroot -p"Haitao@123"
mysql> show databases;
show databases;
退出数据库执行全量备份
mysql> \q;
\q;
[root@localhost ~]# innobackupex --user=root --password='Haitao@123' /xtrabackup/full/
innobackupex --user=root --password='Haitao@123' /xtrabackup/full/
提示OK!即为成功。
[root@localhost ~]# ll /xtrabackup/full/
ll /xtrabackup/full/
完全备份恢复
1、停止数据库
[root@localhost ~]# systemctl stop mysqld
systemctl stop mysqld
2、清理环境
[root@localhost ~]# rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/*
[root@localhost ~]# rm -rf /var/log/mysqld.log
rm -rf /var/log/mysqld.log
[root@localhost ~]# rm -rf /var/log/mysql-slow/slow.log
rm -rf /var/log/mysql-slow/slow.log
3、恢复数据
验证恢复
[root@localhost ~]# innobackupex --apply-log /xtrabackup/full/2023-11-20_14-08-14/
innobackupex --apply-log /xtrabackup/full/2023-11-20_14-08-14/
恢复数据
[root@localhost ~]# innobackupex --copy-back /xtrabackup/full/2023-11-20_14-08-14/
innobackupex --copy-back /xtrabackup/full/2023-11-20_14-08-14/
[root@localhost ~]# ll /var/lib/mysql
ll /var/lib/mysql
4、修改权限
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
5、启动数据库
[root@localhost ~]# systemctl start mysqld
systemctl start mysqld
[root@localhost ~]# mysql -uroot -p'Haitao@123'
mysql -uroot -p'Haitao@123'
mysql> show databases;
show databases;
增量备份
增量备份: 每次备份上一次备份到现在产生的新数据
全量备份(周一)
[root@localhost ~]# innobackupex --user=root --password='Haitao@123' /xtrabackup/increment/
innobackupex --user=root --password='Haitao@123' /xtrabackup/increment/
增量备份(周二)
创建一个新的库
[root@localhost ~]# mysql -uroot -p'Haitao@123'
mysql -uroot -p'Haitao@123'
mysql> create database two charset utf8;
create database two charset utf8;
创建一个表
mysql> use two;
use two;
mysql> create table day(id int,name varchar(20));
create table day(id int,name varchar(20));
插入数据
mysql> insert into day values(1,'周二');
insert into day values(1,'周二');
mysql> \q
\q
[root@localhost ~]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/
innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/
在周二增量备份基础上再增量备份
[root@localhost ~]# mysql -uroot -p'Haitao@123'
mysql -uroot -p'Haitao@123'
mysql> use two;
use two;
mysql> insert into day values (2,'周三');
insert into day values (2,'周三');
mysql> select * from day;
select * from day;
mysql> \q
\q
[root@localhost increment]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-21-49/
[root@localhost increment]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-21-49/
增量备份恢复
1、停止数据库
[root@localhost ~]# systemctl stop mysqld
systemctl stop mysqld
2、清理环境
[root@localhost ~]# rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/*
3、重演回滚redo log
先回滚到全量备份
[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/
innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/
回滚周二备份
[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-21-49/
innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-21-49/
回滚周三备份
[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-46-39/
innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-46-39/
恢复数据
[root@localhost ~]# innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/
innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/
4、修改权限
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
5、启动数据库
[root@localhost ~]# systemctl start mysqld
systemctl start mysqld
登录数据库查看数据是否存在
[root@localhost ~]# mysql -p'Haitao@123'
mysql -p'Haitao@123'
mysql> use two;
use two;
mysql> select * from day;
select * from day;
差异备份
差异备份:只备份跟完整备份不一样的
备份差异备份(周四)
给day表插入数据
[root@localhost ~]# mysql -p'Haitao@123'
mysql -p'Haitao@123'
mysql> use day;
use day;
mysql> insert into day values (3,'周四');
insert into day values (3,'周四');
mysql> \q
\q
创建差异备份
语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录(周一)
[root@localhost ~]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/difference/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/
[root@localhost ~]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/difference/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/
差异备份恢复
1、停止数据库
[root@localhost ~]# systemctl stop mysqld
systemctl stop mysqld
2、清理环境
[root@localhost ~]# rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/*
3、重演回滚redo log
恢复全量的redo log
[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/
innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/
恢复差异备份redo log
语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录(周一)--incremental-dir=/xtrabacku/某个差异备份
[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/difference/2023-11-20_19-35-51/
innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/difference/2023-11-20_19-35-51/
恢复数据
语法:# innobackupex --copy-back /xtrabackup/完全备份目录(周一)
[root@localhost ~]# innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/
innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/
4、修改权限
[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
5、启动数据库
[root@localhost ~]# systemctl start mysqld
systemctl start mysqld
登入数据库查看
[root@localhost ~]# mysql -p'Haitao@123'
mysql -p'Haitao@123'
mysql> use two;
use two;
mysql> select * from day;
select * from day;
三、Mysql bin-log日志恢复数据
需要开启binlog日志功能
在配置文件/etc/my.cnf中添加
[root@localhost ~]# vim /etc/my.cnf
vim /etc/my.cnf
修改完配置文件需要重启服务
[root@localhost ~]# systemctl restart mysqld
开启后会生成binlog日志
刷新binlog日志,会出现新的binlog日志,记录之后的操作语句
mysql> flush logs;
查看binlog日志
mysqlbinlog 日志文件名
[root@localhost mysql]# mysqlbinlog mysql-bin.000001
mysqlbinlog mysql-bin.000001
进入数据库创建一个库
mysql> create database xiaoliu;
查看binlog日志
[root@localhost mysql]# mysqlbinlog mysql-bin.000002
mysqlbinlog mysql-bin.000002
删除新创建的xiaoliu库
mysql> drop database xiaoliu;
利用binlog日志恢复xiaoliu库
[root@localhost mysql]# mysqlbinlog --start-position 219 --stop-position 322 mysql-bin.000002 |mysql -uroot -p'Haitao@123'
mysqlbinlog --start-position 219 --stop-position 322 mysql-bin.000002 |mysql -uroot -p'Haitao@123'
登录数据库查看xiaoliu库是否恢复成功