执行备份策略前,先做好scp命令的准备
解决思路:
生成SSH公钥/私钥后,您需要将公钥添加到服务器上,从而使服务器可以使用该公钥来验证您的身份。
生成SSH公钥/私钥的命令为
ssh-keygen -t rsa -b 4096
什么都不用输入,直接下一步即可
生成SSH公钥/私钥后,您需要将公钥添加到服务器上,从而使服务器可以使用该公钥来验证您的身份。-p指定端口
ssh-copy-id -p85 root@112.94.31.288
添加成功后,您可以使用以下命令测试身份验证是否成功:
ssh -p85 root@112.94.31.288
出现一下截图表示成功
mysql数据备份
先查看my.cnf是在哪个目录
locate my.cnf
二.mysql数据备份
先查看my.cnf是在哪个目录
locate my.cnf
查看mysql默认读取的my.cnf
mysql --help|grep 'my.cnf'
1.增量备份(本地服务器+正式环境)
1.1 开启二进制并配置my.cnf
查看是否开启二进制日志,输入一下命令
show variables like 'log_bin';
如果出现一下截图的话,表示二进制日志未开启。
vi /etc/my.cnf目录下开启二进制日志
ps:自己定义二进制日志存存放的路径,默认是放在/var/lib/mysql 路径
先新建目录
cd /homemkdir mysqlcd mysqlmkdir binlog#为目录设置mysql权限chown -R mysql.mysql /home/mysql/binlogchown -R mysql.mysql /home/mysql/
在/etc/my.cnf增加配置
#二进制存放的文件格式log-bin=/home/mysql/binlog/mysql-bin#当前机器的服务IDserver-id=254#二进制日志的格式binlog_format = MIXED#设置binlog清理时间expire_logs_days = 30#看到我们写入的sql语句格式binlog_rows_query_log_events = 1#binlog缓存大小binlog_cache_size = 4m
#binlog每个日志文件大小 默认大小,如果超过该值,则产生新的二进制文件,后缀名+1
max_binlog_size = 1024m
二进制日志的格式
A) binlog_format 在mysql5.7默认是row, 不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨
例如:update a < 10 的数据,statement模式会记录这一条sql,而row模式的话,会记录a=1,a=2到a=9所有的记录的更改
B) STATEMENT模式 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题
C) MIXED模式 以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
重启mysql
systemctl restart mysqld
可能会出现报错
解决方案:
执行一下命令
setenforce 0
然后再进行重启即可
查看mysql状态
systemctl status mysqld
查看日志
tail -2000f /var/log/mysqld.log
去查配置是否生效,可以看出,已经生效了
1.2 bin日志的备份和还原演示
(1) 首先我们先建立一个test数据库,并且里面有temp_test 表
这时候我们执行下命令 可以输出二进制日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v /home/mysql/binlog/mysql-bin.000002
可以看到,对应的sql语句都记录下来了
- 根据时间点增量进行备份还原
然后再把temp_test这张表给删除
然后我们看一下bin日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v /home/mysql/binlog/mysql-bin.000002
根据时间点进行恢复
mysqlbinlog --no-defaults --start-datetime='2023-09-06 09:21:13' --stop-datetime='2023-09-06 09:26:37' mysql-bin.000002 | mysql -u root -p
输入mysql密码
可以看到这段时间范围的数据已恢复,注意,开始时间和结束时间要大于日志记录的时间,比如结束时间记录的是 2023-09-06 09:26:36 你要写成 2023-09-06 09:26:37
1.3 定时执行备份脚本(本地服务器+正式环境)
本地服务器要新建目录脚本,以时间进行命名
在目录/home/backups/mysql/binlogdata 下新建 date.sh(本地服务器)
#!/bin/bash echo '开始新建binlogdata 目录'DATE=`date +%Y%m%d`#定义时间的格式,注意需要反引号。DIR=/home/backups/mysql/binlogdata#这里定义创建文件夹的地址。mkdir-p$DIR/$DATE#创建文件夹的语句,把上面两个变量引用进来。echo '新建binlogdata 目录完成'
赋值权限
chmod 777 date.sh
并且也设置脚本执行的时间
crontab -e
输入备份的时间(每天23:59都进行备份作业)
58 23 * * */home/backups/mysql/binlogdata/date.sh> /tmp/load.log 2>&1
在/home/mysql/binlogdata目录下新建脚本(正式服务器)
新建binlog.sh脚本
touch binlog.sh#并赋予权限chmod 777 binlog.sh
添加下面脚本内容
#!/bin/bash DATE=`date +%Y%m%d`#定义时间的格式,注意需要反引号。REMOTEDIR=/home/backups/mysql/binlogdata#远程本地服务器地址路径localData=/home/mysql/binlog/#本机数据地址路径echo "开始进行数据迁移到本地服务器"expect << EOFset timeout -1spawn scp -P 85 -r $localDataroot@112.94.31.288:$REMOTEDIR/$DATEexpect eofexitEOFecho "数据迁移到本地服务器结束"
执行备份策略
crontab -e
输入备份的时间(每天23:59都进行备份作业)
59 23 * * * /home/mysql/binlog.sh> /tmp/load.log 2>&1
查看任务列表
crontab -l
2.全量备份(只需正式环境部署脚本)
增量备份策略:每周六凌晨1点执行备份策略
在 /home/mysql/allsql 目录下新建脚本
touch backupsql.sh
输入一下内容
#!/bin/bash echo '开始新建目录'DATE=`date +%Y%m%d`#定义时间的格式,注意需要反引号。NYSQLDIR=/home/mysql/allsql #这里定义创建文件夹的地址。mkdir-p$NYSQLDIR/$DATE#创建文件夹的语句,把上面两个变量引用进来。REMOTEDIR=/home/backups/mysql/allsql#远程本地服务器配置路径echo '新建目录完成'echo '开始执行 smkj-pro-backend备份操作'mysqldump -u dev -pPassword@hwgy01dev --databases smkj-pro-backend > $NYSQLDIR/$DATE/smkj-pro-backend_$(date +%Y%m%d_%H%M).sqlecho '开始执行 smkj-pro-backend备份操作'echo '=================================='echo '开始执行 smkj-pro-base备份操作'mysqldump -u dev -pPassword@hwgy01dev --databases smkj-pro-base > $NYSQLDIR/$DATE/smkj-pro-base_$(date +%Y%m%d_%H%M).sqlecho '开始执行 smkj-pro-base备份操作'echo '=================================='echo '开始执行 smkj-pro-shop备份操作'mysqldump -u dev -pPassword@hwgy01dev --databases smkj-pro-shop > $NYSQLDIR/$DATE/smkj-pro-shop_$(date +%Y%m%d_%H%M).sqlecho '开始执行 smkj-pro-shop备份操作'echo '=================================='echo '开始执行 smkj-pro-user备份操作'mysqldump -u dev -pPassword@hwgy01dev --databases smkj-pro-user > $NYSQLDIR/$DATE/smkj-pro-user_$(date +%Y%m%d_%H%M).sqlecho '开始执行 smkj-pro-user备份操作'echo '=================================='echo '开始执行 smkj-pro-job备份操作'mysqldump -u dev -pPassword@hwgy01dev --databases smkj-pro-job > $NYSQLDIR/$DATE/smkj-pro-job_$(date +%Y%m%d_%H%M).sqlecho '开始执行 smkj-pro-job备份操作'echo '======全部备份结束========='echo "开始进行数据迁移到本地服务器"expect << EOFset timeout -1spawn scp -P 85 -r $NYSQLDIR/$DATEroot@112.94.31.288:$REMOTEDIR/expect eofexitEOFecho "数据迁移到本地服务器结束"
执行定时任务启动脚本
crontab -e
输入 (意思是每天凌晨20份执行脚本,考虑到目前数据库数据整体大小不大,后续数据库量大再进行策略调整)
20 0 * * */home/mysql/allsql/backupsql.sh > /tmp/load.log 2>&1
查看任务列表
crontab -l