MySQL备份恢复

日志

分类

错误日志
通用查询日志
二进制日志
慢查询日志

开启日志

vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
#指定错误日志的保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin
#也可以 log_bin=mysql-bin

##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5  #设置超过5秒执行的语句被记录，缺省时为10秒

systemctl restart mysqld.service 

验证查询日志是否开启

查看二进制日志是否开启

查看慢查询日志相关功能

补充

备份

备份可以分为物理备份和逻辑备份

备份又可以从备份策略分为：

常见方法

MySQL完全备份

恢复

mysqldump备份与恢复

MySQL 完全恢复

MySQL 增量备份

1、开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id = 1
binlog_format = MIXED				#指定二进制日志(binlog)的记录格式为 MIXED

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl restart mysqld.service
ls -l /usr/local/mysql/data/mysql-bin.*

每周对数据库或表进行完全备份

每天进行增量备份操作，生成新的二进制日志文件

查看二进制日志文件的内容

MySQL 增量恢复

断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000006

#部分二进制文件的内容
......
BEGIN
/*!*/;
##-------------解释：at xxx 表示位置点------------------------------------------------
# at 587
##--------------解释：开头210206 15:45:53表示时间，其他的现在用不到-----------------------------------
#210412  7:54:21 server id 1  end_log_pos 734 CRC32 0x46f74eb6 	Query	thread_id=25	exec_time=0	error_code=0
##--------------解释：这里是执行的操作语句---------------------
SET TIMESTAMP=1618228461/*!*/;<------------建立时间戳
insert into class values ('6','srs6','男','00006','666666','城南')<-------向表中插入数据
/*!*/;
# at 734
#210412  7:54:21 server id 1  end_log_pos 765 CRC32 0x8c58b8d9 	Xid = 411
COMMIT/*!*/;
# at 765
#210412  7:56:51 server id 1  end_log_pos 830 CRC32 0x546d845a 	Anonymous_GTID	last_committed=2	sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 830
#210412  7:56:51 server id 1  end_log_pos 913 CRC32 0xdf882827 	Query	thread_id=25	exec_time=0	error_code=0
SET TIMESTAMP=1618228611/*!*/;

##-------------------------------插入第二个数据--------------------------
BEGIN
/*!*/;
# at 913
#210412  7:56:51 server id 1  end_log_pos 1060 CRC32 0xd56daa4d 	Query	thread_id=25	exec_time=0	error_code=0
SET TIMESTAMP=1618228611/*!*/;
insert into class values ('7','srs7','男','00007','777777','城南')
/*!*/;
# at 1060
#210412  7:56:51 server id 1  end_log_pos 1091 CRC32 0x42ca40ae 	Xid = 412
COMMIT/*!*/;
# at 1091
#210412  7:57:39 server id 1  end_log_pos 1138 CRC32 0x777f989b 	Rotate to mysql-bin.000007  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
.......

（1）基于位置恢复
仅恢复到位置点为“913”之前的数据，即不恢复“id=7”的数据
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#到位置点913停止恢复数据
mysqlbinlog --no-defaults --stop-position='913' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"

仅恢复“id=7”的数据，跳过“id=6”的数据
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#从位置点913开始恢复数据
mysqlbinlog --no-defaults --start-position='913' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"

（2）基于时间点恢复
仅恢复到210412  7:56:51之前的数据，即不恢复“id=7”的数据
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#到21-04-12  7:56:51截止恢复数据
mysqlbinlog --no-defaults --stop-datetime='21-04-12  7:56:51' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"

仅恢复“id=7”的数据，跳过“id=6”的数据恢复
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#从21-04-12  7:56:51开始恢复数据
mysqlbinlog --no-defaults--start-datetime='21-04-12  7:56:51' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"