目录

一、mysql日志

1、日志类型

2、事务日志性能优化

3、开启慢查询日志 

4、开启通用日志

5、开启刷新二进制日志

6、mysql的双“1”调优

二、 数据库备份与恢复

1、备份类型

2、mysqldump工具的备份与恢复

3、二进制日志的备份与恢复

一、mysql日志

1、日志类型

①事务日志：用于存储记录数据库事务的相关内容，默认开启。

②错误日志：用于存储记录数据库出现错误，yum安装默认位置：/var/log/mysql.log，默认开启。

③慢查询日志：用于存储记录数据库具体些语句执行时较慢，默认关闭。

④通用日志：记录对数据库的通用操作，包括错误的sql语句，默认是关闭。

⑤二进制日志：二进制方式记录数据库所有操作可用于备份恢复数据，默认关闭。

⑥中继日志：主从复制架构中，从服务器用于保存主传输过来的二进制日志文件。

2、事务日志性能优化

事务日志调优在/etc/my.cnf的mysql配置文件中

①innodb_log_file_size 50331648 #调整每个日志文件大小字节，默认约50M

②innodb_log_files_in_group 2 #日志分隔个数，默认2个

③innodb_log_group_home_dir ./ #事务文件路径

④innodb_flush_log_at_trx_commit=0|1|2#调整事务日志的存储方式有0、1、2三种

0：事务提交时并没有保存到磁盘，而是在一个周期中例如3s，每三秒自动提交一次保存到磁盘中，一般不用。

1：事务每提交一次，都要写到磁盘中，速度相对较慢，但是安全。默认是1。

2：事务每次提交都会到缓存区中，到达一定数量一起提交保存到磁盘中，速度快但是安全性低。

⑤事务日志位置：/var/lib/mysql/ib_logfile0和/var/lib/mysql/ib_logfile1

3、开启慢查询日志 

慢查询日志开启在/etc/my.cnf的mysql配置文件中

①slow_query_log=on|off；#开启或关闭慢查询日志

②long_query_time=N  #配置多少秒判断为慢记录

③慢查询日志位置：/var/lib/mysql/主机名-slow.log

4、开启通用日志

通用日志开启在/etc/my.cnf的mysql配置文件中

①SET GLOBAL general_log=1；#开启通用日志

②通用日志位置：/var/lib/mysql此目录下会生成一个主机名.log；

5、开启刷新二进制日志

二进制日志开启在/etc/my.cnf的mysql配置文件中

①server_id=1  #二进制日志serverid建议配置为本机ip最后三位，serverid不可重复

②log_bin=mysql-bin  #保存二进制日志文件路径，默认/var/lib/mysql/下，可以自己配置路径但是记得创建对应目录并将权限给mysql账户。

③binlog_format = MIXED #可选，指定二进制日志的记录格式为MIXED，一般不写除非特殊要求

④二进制日志文件位置：/var/lib/mysql/mysql-bin.00001

⑤刷新二进制日志文件，即再生成一个新的二进制日志文件。

命令：mysqladmin -u root -p flush-logs  #建议每天刷新一次

6、mysql的双“1”调优

①事务日志存储模式调整为1，innodb_flush_log_at_trx_commit=1。表示每提交一次事务则立即保存到磁盘中。

②每次事务提交后将binlog_cache读缓存中的内容写入到磁盘的二进制日志中 sync_binlog=1。

二、 数据库备份与恢复

1、备份类型

①增量备份：只备份每天相对之前新增的内容，恢复时要从头开始一天天恢复

②完全备份：每次对数据库进行完整的备份

③差异备份：只备份相对之前有差异的内容，恢复时要从头开始一次次恢复

④物理冷备：数据库关闭时进行，完全备份cp 或scp将/var/libg/mysql这个文件打包后的文件拷贝到其他服务器。yum安装路径，解压后注意权限问题

⑤热备份：数据库处于运行状态

⑥温备份：数据库锁定表格可读不可写的状态下进行备份操作，myisam只支持温备，因为不支持事务

2、mysqldump工具的备份与恢复

①备份工具：

mysqldump属于热备，数据库服务需要启动

-A 备份所有数据库

-B 备份指定的数据库

-d 只备份表结构即字段

--all--databases  备份所有库

②备份test库的所有操作包括创建库：mysqldump -u'账户' -p'密码'  --databases  test(库名) >/opt/test.mysql(保存路径)

注意：若备份时不添加 --databases参数则恢复备份时不会执行创建库操作，需要恢复的库名存在。

③备份test库中test1表：mysqldump  -用户   -p'密码'     test库名    test1(表名) >/opt/test.mysql(保存路径)

④恢复：

恢复备份库数据： mysql -u用户  -p'密码' 

恢复备份表数据： mysql -u用户  -p'密码'   test(库名) 

3、二进制日志的备份与恢复

①开启分隔二进制日志

vim /etc/my.cnf
[mysqld]下插入
server_id=11
#id建议为本机ip的最后三位
bin-log=mysql-bin
#二进制日志保存位置，不写目录yum安装默认是/var/lib/mysql/mysql-bin.000001
#分隔二进制日志
mysqladmin -u用户  -p'密码'   flush-logs
#会生成一个新的二进制日志文件，后续数据库操作内容保存到新的二进制日志文件中

②查看二进制日志

二进制日志格式是二进制，需要使用 mysqlbinlog  --no-defaults --base64-output=decode-rows -v  日志名查看。 

 ③二进制日志开启即表示备份，可以根据二进制日志中的时间或者位置即at 节点决定恢复什么时间段的数据或从什么位置开始恢复。

④二进制日志恢复备份

#一般恢复
mysqlbinlog  --no-defaults   二进制日志位置| mysql  -u用户 -p‘密码’
#表示恢复该日志中的所有操作
#根据位置断点恢复备份
mysqlbinlog  --no-defaults  --start-position=节点   二进制日志位置  | mysql  -u用户 -p‘密码’
#表示从节点位置开始恢复备份到最后
mysqlbinlog  --no-defaults  --start-position=节点   二进制日志位置  | mysql  -u用户 -p‘密码’
#表示从头开始恢复备份到节点前停止
#避坑:若数据被删除建议使用--stop-position 恢复，因为若从某个节点开始最后一条也会记录删除库的命令，所有恢复了会将删除的命令也恢复一遍相当于没恢复
#根据时间断点恢复备份
mysqlbinlog  --no-defaults  --start-datatime=2023-03-16 18:17:24(年-月-日 时:分:秒)   二进制日志位置  | mysql  -u用户 -p‘密码’
#表示从此时间开始恢复备份到最后
mysqlbinlog  --no-defaults  --stop-datatime=2023-03-16 18:17:24(年-月-日 时:分:秒)   二进制日志位置  | mysql  -u用户 -p‘密码’
#表示从此时间恢复备份到此时间则停止