MySQL数据备份与恢复

一、 mysql冷备份

1. 添加数据库进行测试

mysql> create database testdata;
mysql> show databases;

2. 关闭MySQL服务

[root@centos03 ~]# systemctl stop mysqld

3. 创建备份目录

[root@centos03 ~]# mkdir /bak

4. 使用归档备份数据库

[root@centos03 ~]# tar zcf /bak/backup_mysql-$(date +F.%F).tar.gz /usr/local/mysql/data/

5. 切换到备份目录下

[root@centos03 ~]# cd /bak/
[root@centos03 bak]# ls

6. 模拟数据丢失删除/usr/local/mysql/data/，后ls查看usr/local/mysql/data/中的数据

[root@centos03 ~]# cd /usr/local/mysql/data/
[root@centos03 data]# rm -rf ./*
[root@centos03 data]# ls
[root@centos03 data]# cd

7. 进入备份目录，释放备份的归档文件

[root@centos03 ~]# cd /bak
[root@centos03 bak]# tar zxvf backup_mysql-F.2021-04-02.tar.gz

8. 进入备份的data中，查看数据，将数据移动到/usr/local/mysql/data中

[root@centos03 data]# cd /bak/usr/local/mysql/data/
[root@centos03 data]# ls
[root@centos03 data]# mv ./* /usr/local/mysql/data/

9. 查看数据，备份完成

[root@centos03 data]# cd /usr/local/mysql/data/
[root@centos03 data]# ls

10. 启动服务

[root@centos03 ~]# systemctl start mysqld

二、 使用mysqldump备份

1. 在testdata中创建表

mysql> create table testdata.ceshi(姓名 char(4),性别 char(3),手机号 char(11));

2. 使用mysql备份表

mysqldump -uroot -ppwd@123 testdata ceshi > ./testdate_ceshi.sql

3. 删除数据库中的表

mysql> drop table testdata.ceshi;

4. 恢复数据库中的表

[root@centos03 ~]# mysql -uroot -ppwd@123 testdata < ./testdate_ceshi.sql

5. 查看恢复数据

6. 备份testdata数据库

[root@centos03 ~]# mysqldump -uroot -ppwd@123 --database testdata > ./testdata.sql

7. 删除testdata数据库

mysql> drop database testdata;

8. 恢复testdata数据库

[root@centos03 ~]# mysql -uroot -ppwd@123 < ./testdata.sql

9. 查看数据库数据

[root@centos03 ~]# mysql -uroot -ppwd@123 -e 'show databases;'

10. 备份所有数据库数据

mysqldump -uroot -ppwd@123 --opt --all-databases > ./all-database.sql

11. 删除testdata数据库进行恢复测试

mysql> drop database testdata;

12. 恢复数据并查看

[root@centos03 ~]# mysql -uroot -ppwd@123 < ./all-database.sql

三、 使用二进制日志备份

1. 开启二进制日志功能

[root@centos03 ~]# vim /etc/my.cnf
49 log-bin=mysql-bin

2. 重新启动服务

[root@centos03 ~]# systemctl restart mysqld

3. 切割日志

[root@centos03 ~]# mysqladmin -uroot -ppwd@123 flush-log

4. 添加数据

mysql> insert into ceshi values('张三','男','15314561235');
mysql> insert into ceshi values('李四','女','15234561235');

5. 使用mysqldump备份模拟数据丢失

mysqldump -uroot -ppwd@123 testdata ceshi > ./testdata_ceshi.sql

6. 再次切割日志，查看切割日志（之后的操作会保存在000008的二进制日志中）

[root@centos03 ~]# mysqladmin -uroot -ppwd@123 flush-log
[root@centos03 ~]# ls /usr/local/mysql/data/

7. 添加数据，查看表

mysql> insert into testdata.ceshi values('王五','女','15211111111');

8. 切割日志（完成添加操作的分割）

[root@centos03 ~]# mysqladmin -uroot -ppwd@123 flush-log

9. 使用mysqldump备份的数据恢复，模拟数据误操作丢失

[root@centos03 ~]# mysql -uroot -ppwd@123 testdata < ./testdata_ceshi.sql

10. 查看ceshi表

[root@centos03 ~]# mysql -uroot -ppwd@123 -e 'select * from testdata.ceshi;'

11. 使用二级制日志恢复数据

mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000009 | mysql -uroot -ppwd@123

12. 查看ceshi表

[root@centos03 data]# mysql -uroot -ppwd@123 -e 'select * from testdata.ceshi;

13. 切割日志，间隔添加两条数据，进行根据位置恢复数据，和根据时间恢复数据

14. 切割日志，

[root@centos03 data]# mysqladmin -uroot -ppwd@123 flush-log

15. 模拟数据丢失

[root@centos03 data]# mysql -uroot -ppwd@123 testdata < /root/testdata_ceshi.sql

16. 查看表

17. 查看日志的位置

[root@centos03 data]# mysqlbinlog ./mysql-bin.000011

18. 进行根据位置的数据恢复

[root@centos03 ~]# mysqlbinlog --start-position '171' --stop-position '320' /usr/local/mysql/data/mysql-bin.000011 | mysql -uroot -ppwd@123

19. 查看表

20. 根据二进制生成时间恢复数据，并查看表

[root@centos03 ~]# mysqlbinlog --start-date '2021-04-02 15:39:55' --stop-date '2021-04-02 15:43:05' /usr/local/mysql/data/mysql-bin.000011 | mysql -uroot -ppwd@123