一、全量备份,备份时数据库需处于关闭状态——物理冷备
[root@localhost ~]# service mysql stop
[root@localhost ~]# mkdir -p /opt/backup
[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# tar zcvf /opt/backup/stu_sql-$(date +%F).tar.gz data
恢复#(停止MySQL服务后进行恢复)
[root@localhost ~]#tar zxvf /opt/backup/stu_sql-2020-08-19.tar.gz -C /usr/local/mysql/
二、MySQL完整备份与恢复
### 备份整个数据库 》》chmod +x /opt/dump/ 如果新建文件夹,一定要授权
## 备份单个数据库
[root@localhost ~]# mysqldump -uroot -p school > /opt/school.sqlbak
//school是数据库
Enter password: (数据库密码)
# 恢复方法一
mysql> create database school;
mysql> use school;
mysql> source /opt/school.sqlbak;
# 恢复方法二
[root@localhost opt]#mysql -uroot -p school</opt/school.sqlbak;
Enter password:
## 备份多个数据库
[root@localhost opt]# mysqldump -uroot -p --databases school comany >/opt/school_comany.sqlbak
//school和comany都是数据库
Enter password:(数据库密码)
# 恢复多个数据库方法一:
mysql> create database school;
mysql> use school;
mysql> source /opt/school_comany.sqlbak;
# 恢复方法二:
[root@localhost opt]#mysql -uroot -p </opt/school_comany.sqlbak;
### 备份表
[root@localhost opt]# mysqldump -uroot -p school teacher > /opt/rowteach.sqlbak;
//school是数据库teacher是表
Enter password:
# 恢复表方法一:
mysql> use school;
mysql> source /opt/rowteach.sqlbak;
# 恢复方法二:
[root@localhost opt]#mysql -uroot -p 库名</opt/rowteach.sqlbak
Enter password:
### 备份所有库
[root@localhost opt]# mysqldump -uroot -p --all-databases >/opt/all.sqlbak
Enter password:
# 恢复方法一:
mysql> source /opt/all.sqlbak;
# 恢复方法二:
[root@localhost opt]#mysql -uroot -p </opt/all.sqlbak
Enter password:
三、MySQL增量备份与恢复 (binlog)
1)完整备份
[root@localhost ~]# mysqldump -uroot -p school > /opt/school.sqlbak
//school是数据库
4)刷新MySQL数据库 binlog
[root@localhost data]# mysqladmin -uroot -p flush-logs
或
mysql> flush logs;
5)保存日志文件并转化成64位简码
[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 > /opt/baksql.01
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/baksql.02
1】基于位置恢复
7-1)查看简码日志利用at位置点恢复
# 历史全量备份(选用)
mysql> source /opt/school.sqlbak
# 利用at位置点恢复,找到误操作的地方,跳过
[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='571' /usr/local/mysql/data/binlog.000001 |mysql -uroot -p
Enter password:
[root@localhost opt]# mysqlbinlog --no-defaults --start-position='681' /usr/local/mysql/data/binlog.000001 |mysql -uroot -p
Enter password:
2】基于时间点恢复
7-2)查看简码日志利用时间点恢复(注意时间表示格式)
mysql> source /opt/school.sqlbak
[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2020-08-23 8:52:03' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p
Enter password:
[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2020-08-23 8:52:27' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p
Enter password: