MySQL备份和恢复

最新推荐文章于 2024-08-08 00:15:00 发布

星辰为谁变

最新推荐文章于 2024-08-08 00:15:00 发布

阅读量1.1k

点赞数 2

文章标签： mysql linux 运维开发

本文链接：https://blog.csdn.net/XinCenWeiSei/article/details/124591591

版权

本文详细介绍了MySQL的几种备份与恢复方法，包括物理冷备份与恢复、mysqldump工具的使用、增量备份及基于日志的位置恢复。通过实例展示了如何在数据库出现故障后，利用备份文件有效恢复数据，确保数据安全性。

摘要由CSDN通过智能技术生成

1、物理冷备份与恢复

（1）备份数据库

冷备份一定先要在停止MySQL数据库，再进行备份！

创建一个backup目录作为储备路径，使用tar命令备份文件，整个数据库文件夹为完整备份。

systemctl stop mysqld    （停止MySQL数据库）

mkdir /backup    （创建文件夹）

tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/   （按年月日备份）

（2）模拟故障

mkdir /bak    （创建文件夹）
mv /usr/local/mysql/data/ /bak/    （将数据库剪切的bak目录中）

（3）将备份文件恢复数据

mkdir restore
tar zxf /backup/mysql_all-2022-05-05.tar.gz -C restore/    （将数据库文件解压到restore目录中）

mv restore/usr/local/mysql/data/ /usr/local/mysql/      （将解压好的数据库文件恢复到原来路径）

systemctl start mysqld    （启动MySQL数据库）

2、 MySQLdump恢复与备份

（1）备份数据库

备份指定库中的部分表：
mysqldump 选项库名表名1 表名2... > /备份路径/备份文件名

mysqldump -u root -p mysql user > mysql-user.sql    （将mysql库中的user表导出为user.sql）

备份一个或多个完整的库：
mysqldump 选项 --databases 库名1 库名2... > /备份路径/备份文件名

mysqldump -u root -p --databases benet > benet.sql    (将整个benet库导出为benet.sql)

备份MySQL服务器中所有的库：
mysqldump 选项 --all-databases /备份路径/备份文件名

mysqldump -u root -p --opt --all-databases  > all-data.sql    （将整个数据库导出为all-data.sql）

常用的选项包括 ‘-u’ ‘-p’，分别用于指定数据库用户名、密码，导出数据量较大时可以使用‘--opt’进行优化执行速度，

（3）恢复数据库

mysql 选项库名表名 < /备份路径/备份文件名

mysql -u root -p test < mysql-user.sql    （从备份文件mysql.sql中将表导入test库中）



mysql -u root -p -e ' show tables from test;'

    （验证导入结果）
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+

。。。。。。。。。。。

（4）模拟故障

mysql -u root -p -e ' drop database benet;'

（5）查看benet库是否存在

mysql -u root -p  -e ' show databases;'

（以下为显示内容）

--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

（6）执行导入操作

mysql -u root -p < benet.sql

（7）确认恢复后结果

mysql -u root -p -e ' show databases;'

(以下为显示内容，benet库已恢复)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| benet              |
| mysql              |
| performance_schema |
| test               |
+--------------------+

3、MySQL增量备份与恢复

（1）开启二进制日志功能
二进制日志文件拓展名一个六位数字如mysql-bin 000001

vim /etc/my.cnf    （设置主配置文件）

[mysqld] （在以下输入）

log-bin=/usr/local/mysql/mysql-bin


systemctl restart mysqld    （重启mysql数据库）


ls -l /usr/local/mysql/mysql-bin.*    （查看二进制拓展名）

（以下为显示内容）
-rw-rw---- 1 mysql mysql 120 5月   5 21:35 /usr/local/mysql/mysql-bin.000001

（2）一般恢复
添加数据库、表，录入信息

mysql -uroot -p

create database client;    （创建client数据库）

use client;    （进入lient数据库）

create table user_info (shenfenzheng char(20),xingming char(20),xingbie char(4));    （创建user_info表）


insert into user_info values('000006','zhangsan','nan');
insert into user_info values('000007','lisi','nv');
insert into user_info values('000008','wangwu','vn');       （创建三行数据）


select * from user_info;    （查看user_info表）

    （以下为显示内容）
+--------------+----------+---------+
| shenfenzheng | xingming | xingbie |
+--------------+----------+---------+
| 000006       | zhangsan | nan     |
| 000007       | lisi     | vn      |
| 000008       | wangwu   | vn      |
+--------------+----------+---------+

（3）先进行一次完整备份

mkdir /mysql_bak    （创建备份目录）

mysqldump -uroot client user_info >/mysql_bak/client_userinfo-$(date +%F).sql    （按年月份备份）

ls /mysql_bak/    （查看备份包）

mysqladmin -uroot -p flush-logs    （生成新的二进制日志）

ls -l /usr/local/mysql/mysql-bin.*    （查看二进制拓展名）

（一下为显示内容）

-rw-rw---- 1 mysql mysql 1157 5月   5 22:11 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  120 5月   5 22:11 /usr/local/mysql/mysql-bin.000002    （多了一行）
-rw-rw---- 1 mysql mysql   68 5月   5 22:11 /usr/local/mysql/mysql-bin.index

（4）继续录制新的数据进行增量备份

mysql -uroot -p

use client;

insert into user_info values('000009','zhaoliu','nan');
insert into user_info values('0000010','sunqi','nan');

select * from user_info; 
    （以下为显示内容）

+--------------+----------+---------+
| shenfenzheng | xingming | xingbie |
+--------------+----------+---------+
| 000006       | zhangsan | nan     |
| 000007       | lisi     | vn       |
| 000008       | wangwu   | vn      |
| 000009       | zhaoliu  | nan     |
| 0000010      | sunqi    | nan     |
+--------------+----------+---------+


退出MySQL服务器：exit

mysqladmin -uroot flush-logs    （生成新的二进制日志）

ls -l /usr/local/mysql/mysql-bin.*    （查看二进制拓展名）

    （以下为显示内容）
-rw-rw---- 1 mysql mysql 1157 5月   5 22:11 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  658 5月   5 22:26 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql  120 5月   5 22:26 /usr/local/mysql/mysql-bin.000003    （多了一行）
-rw-rw---- 1 mysql mysql  102 5月   5 22:26 /usr/local/mysql/mysql-bin.index

cp /usr/local/mysql/mysql-bin.000002 /mysql_bak/

（5）模拟误操作删除

mysql -uroot -p -e ' drop table client.user_info;'

mysql -uroot -p  -e ' select * from  client.user_info;'

（6）恢复操作

mysql -uroot client < /mysql_bak/client_userinfo-2022-05-05.sql    （恢复完全操作）



mysql -uroot -p  -e ' select * from client.user_info;'    (查看client中的user_info表)

    （以下为查看内容）

+--------------+----------+---------+
| shenfenzheng | xingming | xingbie |
+--------------+----------+---------+
| 000006       | zhangsan | nan     |
| 000007       | lisi     | vn      |
| 000008       | wangwu   | vn      |
+--------------+----------+---------+

    已经恢复到000002的时间段

mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root    （恢复增量备份）
   
    （以下为显示内容）
+--------------+----------+---------+
| shenfenzheng | xingming | xingbie |
+--------------+----------+---------+
| 000006       | zhangsan | nan     |
| 000007       | lisi     | vn      |
| 000008       | wangwu   | vn      |
| 000009       | zhaoliu  | nan     |
| 0000010      | sunqi    | nan     |
+--------------+----------+---------+

4、基于位置备份（指定停止位置--stop-position）

（1）模拟误操作

mysql -uroot -p  -e ' drop table client.user_info;'

mysql -uroot -p  -e ' select * from client.user_info;'    （查看表是否存在）

（2）恢复到完全备份

mysql -uroot client </mysql_bak/client_userinfo-2022-05-05.sql

（3）查看二进制日志文件的具体内容

mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002


# at 449
#220505 22:22:51 server id 1  end_log_pos 580 CRC32 0x077b9502 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1651760571/*!*/;
insert into user_info values('0000010','sunqi','nan')

（4）恢复ID449前的数据

mysqlbinlog --no-defaults --stop-position='499' /mysql_bak/mysql-bin.000002 |mysql -uroot


mysql -uroot -p  -e ' select * from client.user_info;'    （再次查看）

    （以下为显示内容）
+--------------+----------+---------+
| shenfenzheng | xingming | xingbie |
+--------------+----------+---------+
| 000006       | zhangsan | nan     |
| 000007       | lisi     | vn      |
| 000008       | wangwu   | vn      |
| 000009       | zhaoliu  | nan     |
+--------------+----------+---------+

5、基于位置备份（从指定位置开始恢复--start-position）

（1）模拟误操作

mysql -uroot -p  -e ' drop table client.user_info;'

（2）恢复到完全备份

mysql -uroot client < /mysql_bak/client_userinfo-2022-05-05.sql

（3）恢复ID449后的数据

mysqlbinlog --no-defaults --start-position='449' /mysql_bak/mysql-bin.000002 | mysql  -uroot


mysql -uroot -p  -e ' select * from client.user_info;'    （再次查看）

6、基于时间恢复（指定时间之前--stop-datetime）

（1）模拟误操作

mysql -uroot -p  -e ' drop table client.user_info;'

（2）恢复到完全备份

mysql -uroot client < /mysql_bak/client_userinfo-2022-05-05.sql

(3)恢复到2022-05-05 22:22:51之前

mysqlbinlog --no-defaults --stop-datetime='2022-05-05 22:22:51' /mysql_bak/mysql-bin.000002 | mysql  -uroot


mysql -uroot -e ' select * from client.user_info;'    （再次查看）

7、基于时间恢复（指定时间之后--start-datetime）

（1）模拟误操作

mysql -uroot -e ' drop table client.user_info;'

（2）恢复到完全备份

mysql -uroot client < /mysql_bak/client_userinfo-2022-05-05.sql

(3)恢复到2022-05-05 22:22:51之后

mysqlbinlog --no-defaults --start-datetime='2022-05-05 22:22:51' /mysql_bak/mysql-bin.000002 | mysql  -uroot


mysql -uroot -e ' select * from client.user_info;'    （再次查看）