Linux下数据库学习过程之备份恢复（持续更新中）

蝉鸣@l

已于 2024-07-09 16:54:49 修改

阅读量195

点赞数 2

分类专栏：数据库学习文章标签： linux 数据库学习

于 2024-07-09 16:53:06 首次发布

本文链接：https://blog.csdn.net/L18291789297/article/details/140290452

版权

数据库学习专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1MySQL日志管理

日志类型

配置文件
vim  /etc/my.cnf
错误日志存放路径
log-error=/var/log/mysqld.log

1.查询日志（不太重要）

查找和查询有关的文件
mysql> show variables like '%query%';
+------------------------------+----------------------------+
| Variable_name                | Value                      |
+------------------------------+----------------------------+
| binlog_rows_query_log_events | OFF                        |     关闭状态
| ft_query_expansion_limit     | 20                         |
| have_query_cache             | NO                         |
| long_query_time              | 10.000000                  |      慢日志
| query_alloc_block_size       | 8192                       |
| query_prealloc_size          | 8192                       |
| slow_query_log               | OFF                        |     关闭状态
| slow_query_log_file          | /var/lib/mysql/db-slow.log |
+------------------------------+----------------------------+
8 rows in set (0.02 sec)

2.二进制日志（重要）
应用场景：1数据恢复，2主从同步
记录增删改的操作
记录的模式：
					1.statement：记录sql语句
					2.row：记录数据本身
					3.mixed：混合

[root@db ~]# ls -l /var/lib/mysql
total 106636
-rw-r-----. 1 mysql mysql       56 Jun 28 20:45  auto.cnf
-rw-r-----. 1 mysql mysql      181 Jun 28 20:45  binlog.000001
-rw-r-----. 1 mysql mysql      181 Jun 28 20:47  binlog.000002
-rw-r-----. 1 mysql mysql      181 Jun 28 21:46  binlog.000003
-rw-r-----. 1 mysql mysql     2536 Jun 29 21:50  binlog.000004

[root@db mysql]# mysqlbinlog binlog.000013    查看binlog.000013日志文件
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240709  8:51:50 server id 1  end_log_pos 127 CRC32 0xd776bb77 	Start: binlog v 4, server v 8.4.0 created 240709  8:51:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
pomMZg8BAAAAewAAAH8AAAABAAQAOC40LjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACmiYxmEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAF3u3bX
'/*!*/;
# at 127
#240709  8:51:50 server id 1  end_log_pos 158 CRC32 0x050dd2e3 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;



[root@db mysql]# mysqlbinlog binlog.000013 --base64-output=decode-rows  查看具体信息
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240709  8:51:50 server id 1  end_log_pos 127 CRC32 0xd776bb77 	Start: binlog v 4, server v 8.4.0 created 240709  8:51:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 127
#240709  8:51:50 server id 1  end_log_pos 158 CRC32 0x050dd2e3 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


3.慢日志（比较重要）

4.事物日志
 binlog_transaction_compression                 | OFF

数据恢复实操

备份：全量备份，增量备份
在指定时间做全量备份，在下一次做全量备份前做增量备份

all-databses:所有数据库
default-character-set:默认字符集
single-transaction:备份期间，更新操作不会影响备份
-B,--databases:指定数据库

全量备份（mysqldump）

[root@db mysql]# mysqldump -uroot -pLj.123456 --all-databases > /root/my.sql;  全量备份
测试
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mydb2              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database mydb2;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@db ~]# mysql -uroot -pLj.123456 < my.sql    全量恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mydb2              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

开启增量备份

| log_bin                                        | ON                          |	binlog开启
| log_bin_basename                               | /var/lib/mysql/binlog       |	binlog文件的基名：binlog （binlog.000001）
| log_bin_index                                  | /var/lib/mysql/binlog.index |	binlog索引文件

测试
mysql> create database backdb;
Query OK, 1 row affected (0.00 sec)

mysql> use backdb;
Database changed
mysql> create table stu_info(
    -> stu_no int primary key auto_increment,
    -> stu_name varchar(32) not null
    -> )auto_increment=1001;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stu_info values(null,'张三'),(null,'李四'),(null,'王五');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from stu_info;
+--------+----------+ 
| stu_no | stu_name |
+--------+----------+
|   1001 | 张三     |
|   1002 | 李四     |
|   1003 | 王五     |
+--------+----------+
3 rows in set (0.00 sec)

做全量备份
[root@db ~]# mysqldump -uroot -pLj.123456 --all-databases --default-character-set=utf8mb4 > /root/backup.sql;
刷新
[root@db ~]# mysqladmin -uroot -pLj.123456 flush-logs

mysql> insert into stu_info values(null,'丽丽');
Query OK, 1 row affected (0.00 sec)

mysql> delete from stu_info where stu_no=1002;
Query OK, 1 row affected (0.01 sec)

mysql> select *from stu_info;
+--------+----------+
| stu_no | stu_name |
+--------+----------+
|   1001 | 张三     |
|   1003 | 王五     |
|   1004 | 丽丽     |
+--------+----------+
3 rows in set (0.00 sec)
误删操作
mysql> drop database backdb;
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;

mysql> show binary logs;	显示全部的binlog文件
mysql> show binary log status;	显示最近的一次binlog文件
mysql>  show binlog events in 'binlog.000014';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000014 |   4 | Format_desc    |         1 |         127 | Server ver: 8.4.0, Binlog ver: 4     |
| binlog.000014 | 127 | Previous_gtids |         1 |         158 |                                      |
| binlog.000014 | 158 | Anonymous_Gtid |         1 |         237 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 237 | Query          |         1 |         314 | BEGIN                                |
| binlog.000014 | 314 | Table_map      |         1 |         378 | table_id: 200 (backdb.stu_info)      |
| binlog.000014 | 378 | Write_rows     |         1 |         425 | table_id: 200 flags: STMT_END_F      |
| binlog.000014 | 425 | Xid            |         1 |         456 | COMMIT /* xid=3355 */                |
| binlog.000014 | 456 | Anonymous_Gtid |         1 |         535 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 535 | Query          |         1 |         612 | BEGIN                                |
| binlog.000014 | 612 | Table_map      |         1 |         676 | table_id: 200 (backdb.stu_info)      |
| binlog.000014 | 676 | Delete_rows    |         1 |         723 | table_id: 200 flags: STMT_END_F      |
| binlog.000014 | 723 | Xid            |         1 |         754 | COMMIT /* xid=3356 */                |
| binlog.000014 | 754 | Anonymous_Gtid |         1 |         831 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 831 | Query          |         1 |         941 | drop database backdb /* xid=3358 */  |
| binlog.000014 | 941 | Rotate         |         1 |         985 | binlog.000015;pos=4                  |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
15 rows in set (0.00 sec)

还原
全量恢复
[root@db ~]# mysql -uroot -pLj.123456 < backup.sql
此时没有全量备份后的数据操作

进行增量恢复：两种方式：1根据时间点，2根据位置
[root@db mysql]# mysqlbinlog binlog.000014 >/root/increment.sql 重定向到一个路径
这里使用第二种
[root@db mysql]# mysqlbinlog -uroot -pLj.123456  binlog.000014 --start-position=4 --stop-position=831 -r result.sql
[root@db mysql]# mysql -uroot -pLj.123456 <result.sql
查看数据库内的数据，成功恢复
mysql> select *from stu_info;
+--------+----------+
| stu_no | stu_name |
+--------+----------+
|   1001 | 张三     |
|   1003 | 王五     |
|   1004 | 丽丽     |
+--------+----------+
3 rows in set (0.00 sec)

备份类型
待补充