一、查看binlog是否开启
二、开启binlog
- 查看mysql的数据存储位置
Remove leading # to turn on a very important data integrity option: logging
changes to the binary log between backups.
binlog未开启
log-bin
Remove leading # to set options mainly useful for reporting servers.
The server defaults are faster for transactions and fast SELECTs.
Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
Disabling symbolic-links is recommended to prevent assorted security risks
三、备份binlog
四、备份sql文件

一、查看binlog是否开启

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name                   | Value                                       |
+---------------------------------+---------------------------------------------+
| log_bin                         | OFF                                          |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                         |
| log_bin_use_v1_row_events       | OFF                                         |
| sql_log_bin                     | OFF                                          |
+---------------------------------+---------------------------------------------+
6 rows in set (0.01 sec)

参数说明

log_bin binlog是否开启（ON 是已开启， OFF 是未开启）
log_bin_basename binlog的前缀位置及名称
log_bin_index binlog的索引位置及名称
二、开启binlog
查看mysql的数据存储位置
```shell [root@localhost ~]# vim /etc/my.cnf [mysqld]
Remove leading # to turn on a very important data integrity option: logging
changes to the binary log between backups.
binlog未开启
log-bin

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

datadir 为mysql的数据存储位置， 如果开启binlog开启之后， 默认会存储在这个目录， 为方便管理， 重新创建个目录统一存放binlog
<a name="RyyZm"></a>
#### 创建binlog目录
```shell
[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# mkdir mysql-binlog
[root@localhost mysql]# chown -R mysql:mysql mysql-binlog   
[root@localhost mysql]# ll
总用量 122960
drwxr-x---. 2 mysql mysql       52 5月   5 20:26 aa
-rw-r-----. 1 mysql mysql       56 5月   5 15:01 auto.cnf
drwxr-x---. 2 mysql mysql       20 5月   5 20:29 bbb
-rw-------. 1 mysql mysql     1676 5月   5 15:01 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 5月   5 15:01 ca.pem
-rw-r--r--. 1 mysql mysql     1112 5月   5 15:01 client-cert.pem
-rw-------. 1 mysql mysql     1680 5月   5 15:01 client-key.pem
-rw-r-----. 1 mysql mysql      390 5月   6 11:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   7 10:12 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月   7 10:12 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月   5 15:01 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月   7 10:12 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月   5 15:01 mysql
-rw-r-----. 1 mysql mysql      177 5月   5 16:08 mysql-bin.000001
-rw-r-----. 1 mysql mysql       32 5月   5 15:59 mysql-bin.index
drwxr-xr-x. 2 mysql mysql      173 5月   7 10:12 mysql-binlog
srwxrwxrwx. 1 mysql mysql        0 5月   7 10:12 mysql.sock
-rw-------. 1 mysql mysql        5 5月   7 10:12 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 5月   5 15:01 performance_schema
-rw-------. 1 mysql mysql     1676 5月   5 15:01 private_key.pem
drwxr-x---. 2 mysql mysql       20 5月   5 15:03 pro_wms_test
-rw-r--r--. 1 mysql mysql      452 5月   5 15:01 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 5月   5 15:01 server-cert.pem
-rw-------. 1 mysql mysql     1680 5月   5 15:01 server-key.pem
drwxr-x---. 2 mysql mysql     8192 5月   5 15:01 sys

开启binlog

打开log-bin的注释，设置为刚才创建的目录，文件前缀为mysql-bin，添加server-id（单机都可以，集群不能重复）

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
server-id=1
log_bin=/var/lib/mysql/mysql-binlog/mysql-bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

重启mysql

[root@localhost mysql]# systemctl restart mysqld
[root@localhost mysql]# ps aux | grep mysql
mysql     2124  2.5  4.4 1119476 173900 ?      Sl   10:49   0:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root      2153  0.0  0.0 112824   984 pts/2    S+   10:49   0:00 grep --color=auto mysql

查看binlog是否开启

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name                   | Value                                       |
+---------------------------------+---------------------------------------------+
| log_bin                         | ON                                          |
| log_bin_basename                | /var/lib/mysql/mysql-binlog/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                         |
| log_bin_use_v1_row_events       | OFF                                         |
| sql_log_bin                     | ON                                          |
+---------------------------------+---------------------------------------------+
6 rows in set (0.00 sec)

binlog开启成功

查看生成的binlog文件

刚开始可能会没有，操作后会生成

[root@localhost mysql-binlog]# cd /var/lib/mysql/mysql-binlog
[root@localhost mysql-binlog]# ls
mysql-bin.000001  mysql-bin.index

binlog相关命令

# 查看生成的binlog内容, 默认是我们看不懂的， 可通过下面的命令转为能看懂的
/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-binlog/mysql-bin.000001  | more
# 把binlog转为人能看懂的
/usr/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-binlog/mysql-bin.000001 
[root@localhost mysql-binlog]# /usr/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000001
# 数据恢复
# 恢复全部数据
/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p
# 恢复恢复某数据库的数据
/usr/bin/mysqlbinlog --no-defaults --database=xxx /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p
# 恢复指定位置数据
/usr/bin/mysqlbinlog --no-defaults --start-position="204" --stop-position="556" /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p
#  恢复指定时间段数据
/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002 --stop-date= "2022-04-19 12:00:00" --start-date= "2022-04-19 11:55:00" | mysql -uroot -p
# 将数据导入sql文件中
/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002  > /tmp/aa.sql

三、备份binlog

创建binlog备份目录

[root@localhost ]# cd /home/cmstxa/open/mysql5.7
[root@localhost mysql5.7]# mkdir back-binlog
[root@localhost mysql5.7]# cd back-binlog
[root@localhost mysql5.7]# mkdir back-binlog  # binlog归档目录
[root@localhost mysql5.7]# mkdir temp-binlog  # binlog备份操作的临时目录

编写备份脚本

[root@localhost mysql5.7]# touch back-binlog.sh # binlog 备份的脚本
[root@localhost mysql5.7]# chmod +x back-binlog.sh # 给脚本添加执行权限
[root@localhost back-binlog]# vim back-binlog.sh

#!/bin/bash
# /mysql/backup/sh/backup_mysql_binlog.sh
MYSQL_BAK_BIN_FILE=mysql-bin.`date +%Y%m%d%H`.tar.gz
MYSQL_BAK_DEST_GZ_LOCALHOST='/home/cmstxa/open/mysql5.7/back-binlog/back-binlog'
# 备份binlog的位置          目标位置
MYSQL_BAK_BIN_LOCATION='/var/lib/mysql/mysql-binlog'
MYSQL_BAK_DEST_LOCATION='/home/cmstxa/open/mysql5.7/back-binlog/temp-binlog'
# 删除90天之前的数据
find ${MYSQL_BAK_DEST_GZ_LOCALHOST} -mtime +90 -name 'mysql-bin.*' -exec rm -rf {} \;
# 删除临时文件夹的数据
rm -fr ${MYSQL_BAK_DEST_LOCATION}/*
# 复制一天内的binlog，并保留文件属性
find ${MYSQL_BAK_BIN_LOCATION} -mtime -1 -name 'mysql-bin.*' -exec cp -f -p {} ${MYSQL_BAK_DEST_LOCATION} \;
echo ${MYSQL_BAK_BIN_LOCATION} files  done cp ;
cd ${MYSQL_BAK_DEST_LOCATION} \;
tar -czvf ${MYSQL_BAK_BIN_FILE} mysql-bin.*
mv ${MYSQL_BAK_BIN_FILE} ${MYSQL_BAK_DEST_GZ_LOCALHOST}

测试备份脚本

产生binlog后再测试
```
[root@localhost back-binlog]# ./back-binlog.sh 
/var/lib/mysql/mysql-binlog files done cp
mysql-bin.000001
```
查看 /home/cmstxa/open/mysql5.7/back-binlog/back-binlog 目录下是否产生备份文件，并按照上述方法测试文件是否可用
编写定时备份脚本
此处定时备份用的是linux的crontab服务，没有的话需要安装
编写脚本 (脚本需要写全路径), 有多个任务写多行
当crontab存在一个任务后，同一个用户添加后续任务使用 crontab -e 命令
```
[root@localhost back-binlog]# touch back-binlog.cron
[root@localhost back-binlog]# vim back-binlog.cron
5 0 * * * /home/cmstxa/open/mysql5.7/back-binlog/back-binlog.sh
```
前面的是cron表达式，后面是脚本路径
加入定时任务
```
[root@localhost back-binlog]# crontab back-binlog.cron
```
查看定时任务
```
[root@localhost back-binlog]# crontab -l
5 0 * * * /home/cmstxa/open/mysql5.7/back-binlog/back-binlog.sh
```
测试定时任务
可以把时间设的短一点，查看备份文件是否生成，并按照周期时间观测几次

四、备份sql文件

备份sql文件用的是MySQL的mysqldump工具

创建sql备份目录

[root@localhost ]# cd /home/cmstxa/open/mysql5.7
[root@localhost mysql5.7]# mkdir back-sqllog
[root@localhost mysql5.7]# cd back-sqllog
[root@localhost mysql5.7]# mkdir back-sqllog  # sqllog归档目录
[root@localhost mysql5.7]# mkdir temp-sqllog  # sqllog备份操作的临时目录

编写备份脚本

[root@localhost mysql5.7]# touch back-sqllog.sh # sqllog 备份的脚本
[root@localhost mysql5.7]# chmod +x back-sqllog.sh # 给脚本添加执行权限
[root@localhost back-sqllog]# vim back-sqllog.sh

需要用户名和密码

#!/bin/bash
# /mysql/backup/sh/backup_mysql_splite_db.sh
MYSQL_USER="xxxx"
MYSQL_USER_PASSWORD='xxxx'
MYSQL_BAK_DATABASES="xxxx"
# 数据临时目录
MYSQL_BAK_DIRECTORY="/home/cmstxa/open/mysql5.7/back-sqllog/temp-sqllog"
MYSQL_BAK_FILE_PREFIX=mysql.`date +%Y%m%d%H`.
MYSQL_BAK_FILE_SUFFIX=.sql.gz
#数据归档目录
MYSQL_BAK_DEST_GZ_DIRECTORY='/home/cmstxa/open/mysql5.7/back-sqllog/back-sqllog'
# 删除90天之前的数据
find ${MYSQL_BAK_DEST_GZ_DIRECTORY} -mtime +90 -name 'mysql.*' -exec rm -rf {} \;
rm -fr ${MYSQL_BAK_DIRECTORY}/*
for dbName in $MYSQL_BAK_DATABASES;
        do
        ## 备份指定databases
        mysqldump -u${MYSQL_USER%} -p${MYSQL_USER_PASSWORD} --databases ${dbName} --single-transaction --hex-blob -R |gzip >  ${MYSQL_BAK_DIRECTORY}/$MYSQL_BAK_FILE_PREFIX$dbName$MYSQL_BAK_FILE_SUFFIX
        mv ${MYSQL_BAK_DIRECTORY}/$MYSQL_BAK_FILE_PREFIX$dbName$MYSQL_BAK_FILE_SUFFIX  ${MYSQL_BAK_DEST_GZ_DIRECTORY}
        echo $MYSQL_BAK_FILE_PREFIX$dbName$MYSQL_BAK_FILE_SUFFIX done backup ;
done

测试备份脚本

[root@localhost back-sqllog]# ./back-sqllog.sh

查看 /home/cmstxa/open/mysql5.7/back-sqllog/back-sqllog目录下是否产生备份文件，并按照周期时间观测几次

编写定时任务脚本

此处定时备份用的是linux的crontab服务，没有的话需要安装
编写脚本 (脚本需要写全路径), 有多个任务写多行
当crontab存在一个任务后，同一个用户添加后续任务使用 crontab -e 命令
因为上面已经加了一个任务，第二个任务直接使用crontab -e编辑，在下面加一行

[root@localhost back-sqllog]# crontab -e
5 0 * * * /home/cmstxa/open/mysql5.7/back-binlog/back-binlog.sh
5 0 * * * /home/cmstxa/open/mysql5.7/back-sqllog/back-sqllog.sh

前面的是cron表达式，后面是脚本路径

查看定时任务

[root@localhost back-sqllog]# crontab -l
5 0 * * * /home/cmstxa/open/mysql5.7/back-binlog/back-binlog.sh
5 0 * * * /home/cmstxa/open/mysql5.7/back-sqllog/back-sqllog.sh

linux服务器问题排查

<八>MySQL定期备份binlog及sql日志

一、查看binlog是否开启

二、开启binlog

查看mysql的数据存储位置

Remove leading # to turn on a very important data integrity option: logging

changes to the binary log between backups.

binlog未开启

log-bin

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

Disabling symbolic-links is recommended to prevent assorted security risks

开启binlog

重启mysql

查看binlog是否开启

查看生成的binlog文件

binlog相关命令

三、备份binlog

创建binlog备份目录

编写备份脚本

测试备份脚本

编写定时备份脚本

加入定时任务

查看定时任务

测试定时任务

四、备份sql文件

创建sql备份目录

编写备份脚本

测试备份脚本

编写定时任务脚本

查看定时任务

测试定时任务