MySQL数据库的备份与恢复
文章目录
1. 常用的备份方案
- 全量备份，数据库内容全部备份
- 增量备份，在全量备份的基础上把新增的数据备份
- 差异备份，
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 对某一段时间的所有数据进行完全拷贝，数据恢复快，备份时间长。 |
| 增量备份 | 在第一次全量备份或者增量备份后，每次备份只需备份前一次相比增加。没有重复的备份数据，备份时间短，恢复数据时必须按照一定的顺序进行 |
| 差异备份 | 备份上次完全备份后发生改变的所有文件数据，在进行第一次全备份后进行差异备份的这段时间内增加，修改文件的备份。进行恢复时，只需要对第一次全备和最后一次差异备份进行恢复 |
2. MySQL备份工具，mysqldump
mysqldump 选项 --all-databases 全备
mysqldump 选项 database [tables]
mysqldump 选项 --databases db1 [2][3]
选项：
-uusername 指定数据库用户名
-hhost 指定主机，IP地址
-ppassword 数据库用户密码
-P 数据库端口号
3. 进行全备，与恢复
1.全备
[root@129a ~]# mysqldump -uroot -prun123456 --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql anaconda-ks.cfg passwd test web
[root@129a ~]# file all-20220630170137.sql
all-20220630170137.sql: UTF-8 Unicode text, with very long lines
[root@129a ~]#
2.误删
mysql> drop database school;
3.恢复
[root@129a ~]# mysql -uroot -prun123456 < all-20220630170137.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]#
mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| tb_coursse |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
4. 备份一张表，数据库
备份表
[root@129a ~]# mysqldump -uroot -prun123456 school tb_coursse > course.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql anaconda-ks.cfg course.sql test web
[root@129a ~]#
备份数据库
[root@129a ~]# mysqldump -uroot -prun123456 school > school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql course.sql test
anaconda-ks.cfg school.sql web
[root@129a ~]#
5. 删除某张表，并且恢复
mysql> drop table tb_coursse;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)
mysql>
在数据库里面恢复
[root@129a ~]# ls
all-20220630170137.sql course.sql test
anaconda-ks.cfg school.sql web
[root@129a ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source course.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.10 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_coursse |
| tb_students_info |
+------------------+
2 rows in set (0.01 sec)
mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c++ |
| 6 | html |
+----+-------------+
6 rows in set (0.00 sec)
mysql> drop table tb_coursse;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)
mysql>
在数据库外边恢复
[root@129a ~]# mysql -uroot -prun123456 school < course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]#
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_coursse |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c++ |
| 6 | html |
+----+-------------+
6 rows in set (0.00 sec)
mysql>
6. MySQL的二进制备份与恢复
6.1 开启MySQL的二进制日志功能
1.修改文件内容 /etc/my.cnf
[root@129a ~]# vim /etc/my.cnf
[root@129a ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id = 10
log-bin = mysql_bin
[root@129a ~]#
2.查看内容
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err ib_logfile0 private_key.pem
auto.cnf ib_logfile1 public_key.pem
ca-key.pem ibdata1 school
ca.pem ibtmp1 server-cert.pem
client-cert.pem mysql server-key.pem
client-key.pem mysql.pid sys
ib_buffer_pool performance_schema
[root@129a data]#
3.重启mysql
[root@129a ~]# systemctl restart mysqld
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err ib_logfile1 private_key.pem
auto.cnf ibdata1 public_key.pem
ca-key.pem ibtmp1 school
ca.pem mysql server-cert.pem
client-cert.pem mysql.pid server-key.pem
client-key.pem mysql_bin.000001 sys
ib_buffer_pool mysql_bin.index
ib_logfile0 performance_schema
[root@129a data]#
[root@129a data]# cat mysql_bin.index
./mysql_bin.000001
[root@129a data]#
mysql_bin.index //当前正在记录日志文件是哪个？
mysql_bin.000001
6.2 对数据库进行完全备份
- –single-transaction 单独的事务
- –flush-logs 刷新日志
- –master-data=2 版本号
- –all-databases 所有的数据库
- –delete-master-logs 删除master的日志
--single-transaction 单独的事务
--flush-logs 刷新日志
--master-data=2 版本号
--all-databases 所有的数据库
--delete-master-logs 删除master的日志
[root@129a ~]# mysqldump -uroot -prun123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql anaconda-ks.cfg school.sql web
all-20220630175624.sql course.sql test
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err ib_logfile1 private_key.pem
auto.cnf ibdata1 public_key.pem
ca-key.pem ibtmp1 school
ca.pem mysql server-cert.pem
client-cert.pem mysql.pid server-key.pem
client-key.pem mysql_bin.000002 sys
ib_buffer_pool mysql_bin.index
ib_logfile0 performance_schema
[root@129a data]#
- 用vimdiff查看不同内容，使用qa!退出
6.3 新增数据，创建新表，修改数据，删除数据
1.新增数据
mysql> insert tb_coursse(course_name) values('linux'),('lishi');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c++ |
| 6 | html |
| 7 | linux |
| 8 | lishi |
+----+-------------+
8 rows in set (0.00 sec)
mysql>
2.创建新表
mysql> create table info(id int not null primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.15 sec)
mysql> insert info(name) values('zhang'),('wang'),('li'),('ying');
Query OK, 4 rows affected (0.23 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from info;
+----+-------+
| id | name |
+----+-------+
| 1 | zhang |
| 2 | wang |
| 3 | li |
| 4 | ying |
+----+-------+
4 rows in set (0.00 sec)
mysql>
3.修改数据
mysql> update tb_students_info set height = 177 where name = 'green';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
4.删除数据
mysql> delete from tb_students_info where name = 'lily';
Query OK, 1 row affected (0.13 sec)
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | danny | 25 | M | 160 | 1 |
| 2 | green | 23 | M | 177 | 2 |
| 3 | henry | 23 | F | 185 | 1 |
| 4 | jane | 23 | M | 162 | 3 |
| 5 | jim | 22 | F | 175 | 2 |
| 6 | john | 21 | F | 172 | 4 |
| 8 | susan | 23 | M | 170 | 5 |
| 9 | thomas | 22 | F | 178 | 5 |
| 10 | tom | 23 | F | 165 | 5 |
| 11 | liming | 22 | M | 180 | 7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
mysql>
6.4 误删数据库
mysql> drop database school;
Query OK, 3 rows affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
6.5 刷新创建新的二进制日志， mysqladmin -uroot -prun123456 flush-logs
- 刷新二进制日志是为了产生新的日志文件，他人的数据内容会在新的日志文件中记录
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err ib_logfile0 mysql_bin.index
auto.cnf ib_logfile1 performance_schema
ca-key.pem ibdata1 private_key.pem
ca.pem ibtmp1 public_key.pem
client-cert.pem mysql server-cert.pem
client-key.pem mysql.pid server-key.pem
ib_buffer_pool mysql_bin.000002 sys
[root@129a data]#
刷新
[root@129a ~]# mysqladmin -uroot -prun123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err ib_logfile1 performance_schema
auto.cnf ibdata1 private_key.pem
ca-key.pem ibtmp1 public_key.pem
ca.pem mysql server-cert.pem
client-cert.pem mysql.pid server-key.pem
client-key.pem mysql_bin.000002 sys
ib_buffer_pool mysql_bin.000003
ib_logfile0 mysql_bin.index
[root@129a data]#
[root@129a data]# cat mysql_bin.index
./mysql_bin.000002
./mysql_bin.000003
[root@129a data]#
6.6 恢复完全备份
[root@129a ~]# mysql -uroot -prun123456 < all-20220630175624.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_coursse |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c++ |
| 6 | html |
+----+-------------+
6 rows in set (0.00 sec)
mysql>
6.7 检查误删数据在什么位置 show binlog events in 'mysql_bin.000002';
events 事件 pos开始位置 Event_type事件类型 End_log_pos 结束位置
进入数据库，show binlog events in 'mysql_bin.000002';找到第二相同数值的第二个数值
mysql> show binlog events in 'mysql_bin.000002';
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 10 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 10 | 375 | use `school`; create table info(id int not null primary key auto_increment,name varchar(10)) |
| mysql_bin.000002 | 375 | Anonymous_Gtid | 10 | 440 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 440 | Query | 10 | 514 | BEGIN |
| mysql_bin.000002 | 514 | Table_map | 10 | 566 | table_id: 142 (school.info) |
| mysql_bin.000002 | 566 | Write_rows | 10 | 640 | table_id: 142 flags: STMT_END_F |
| mysql_bin.000002 | 640 | Xid | 10 | 671 | COMMIT /* xid=484 */ |
| mysql_bin.000002 | 671 | Anonymous_Gtid | 10 | 736 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 736 | Query | 10 | 810 | BEGIN |
| mysql_bin.000002 | 810 | Table_map | 10 | 868 | table_id: 140 (school.tb_coursse) |
| mysql_bin.000002 | 868 | Write_rows | 10 | 925 | table_id: 140 flags: STMT_END_F |
| mysql_bin.000002 | 925 | Xid | 10 | 956 | COMMIT /* xid=488 */ |
| mysql_bin.000002 | 956 | Anonymous_Gtid | 10 | 1021 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 1021 | Query | 10 | 1095 | BEGIN |
| mysql_bin.000002 | 1095 | Table_map | 10 | 1165 | table_id: 141 (school.tb_students_info) |
| mysql_bin.000002 | 1165 | Update_rows | 10 | 1245 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000002 | 1245 | Xid | 10 | 1276 | COMMIT /* xid=493 */ |
| mysql_bin.000002 | 1276 | Anonymous_Gtid | 10 | 1341 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 1341 | Query | 10 | 1415 | BEGIN |
| mysql_bin.000002 | 1415 | Table_map | 10 | 1485 | table_id: 141 (school.tb_students_info) |
| mysql_bin.000002 | 1485 | Delete_rows | 10 | 1541 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000002 | 1541 | Xid | 10 | 1572 | COMMIT /* xid=494 */ |
| mysql_bin.000002 | 1572 | Anonymous_Gtid | 10 | 1637 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 1637 | Query | 10 | 1735 | drop database school |
| mysql_bin.000002 | 1735 | Rotate | 10 | 1782 | mysql_bin.000003;pos=4 |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)
mysql>
6.8 恢复数据
1.不加|是查看
[root@129a ~]# mysqlbinlog --stop-position=1637 /opt/data/mysql_bin.000002
2.恢复
[root@129a ~]# mysqlbinlog --stop-position=1637 /opt/data/mysql_bin.000002 | mysql -uroot -prun123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]#
3.查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info |
| tb_coursse |
| tb_students_info |
+------------------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from info;
+----+-------+
| id | name |
+----+-------+
| 1 | zhang |
| 2 | wang |
| 3 | li |
| 4 | ying |
+----+-------+
4 rows in set (0.00 sec)
mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c++ |
| 6 | html |
| 7 | linux |
| 8 | lishi |
+----+-------------+
8 rows in set (0.00 sec)
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | danny | 25 | M | 160 | 1 |
| 2 | green | 23 | M | 177 | 2 |
| 3 | henry | 23 | F | 185 | 1 |
| 4 | jane | 23 | M | 162 | 3 |
| 5 | jim | 22 | F | 175 | 2 |
| 6 | john | 21 | F | 172 | 4 |
| 8 | susan | 23 | M | 170 | 5 |
| 9 | thomas | 22 | F | 178 | 5 |
| 10 | tom | 23 | F | 165 | 5 |
| 11 | liming | 22 | M | 180 | 7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
mysql>
7. MySQL数据库的三大类
7.1 按备份系统的准备程度，可将其分为 冷备份、温备份和热备份三大类 :
-
冷备份 : 离线备份 数据库服务停止
-
优点 : 设备投资较少，节省通信费用，通信环境要求不高
-
缺点 : 恢复时间较长，一般要数天至1周，数据完整性与一致性较差
-
温备份 : 将备份系统已安装配置成与当前使用的系统相同或相似的系统和网络运行环境，安装应用系统业务定期备份数据。一旦发生灾难，直接使用定期备份数据，手工逐笔或自动批量追补孤立数据或将终端用户通过通讯线路切换到备份系统，恢复业务运行
-
优点 : 设备投资较少，通信环境要求不高
-
缺点 : 恢复时间长，一般要十几个小时至数天，数据完整性与一致性较差
-
热备份 : 备份处于联机状态，在线备份，数据库没有停止服务
-
优点 : 恢复时间短，一般几十分钟到数小时，数据完整性与一致性最好，数据丢失可能性最小
-
缺点 : 设备投资大，通信费用高，通信环境要求高，平时运行管理较复杂
1044
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
