Linux日志备份以及多源主从复制

Celia 字数: 6746 阅读耗时: 16 分钟 2023/11/10 2023/12/07 博客独享热度: 34 评论: 0

Linux的mysql日志以及备份

日志分类

Error Log
错误日志包含在/var/log/mysqld.log
默认位置：编译安装：在安装目录下 rpm安装：/var/log/mysqld.log
指定日志位置

log-error=/var/log/mysqld.log

注:日志存储的目录必须修改所有者和所属组为mysql

Binary Log 产生binlog日志：修改配置文件,添加如下配置

log-bin=/var/log/mysql-bin/slave2
server-id=2    //mysql5.7要写

mkdir  /var/lib/mysql-bin
chown mysql.mysql /var/lib/mysql-bin/
systemctl restart mysqld

使用binlog

vim  /etc/my.cnf   
[mysqld]
log-bin=mylog   # 追加如下配置
server-id=1

查看方式

mysqlbinlog mysql.000002

安装datatime看

mysqlbinlog /log/bin.000001 --start-datetime="2023.10.31 00:00:00" --stop-datetime="2023-11-07 00:00:00"

按position读取

mysqlbinlog mysql.000002 --start-position=260

根据binlog恢复数据

mysqlbinlog --start-datetime='2014-11-25 11:56:54'  --stop-datetime='2014-11-25 11:57:41'  binlog | mysql -u root -p1

刷新bin-log日志

mysql -u root -p flush logs

mysqldump

备份

mysqldump -u root -p db >db.sql

导入恢复

mysql -u root -p <db.sql

percona-xtrabackup物理备份

安装软件centos7

yum install https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

创建备份目录

mkdir -p backup/full

开始全量备份

innobackupex --user=root --password=Wang@qwer123  backup/full

完全备份恢复流程

停止数据库
清理环境
重演回滚－－> 恢复数据
修改权限
启动数据库

开始还原流程
关闭数据库并清理环境

systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex --apply-log backup/full/2018-01-21_18-19-25/ /验证备份

恢复数据

innobackupex --copy-back backup/full/2018-01-21_18-19-25/

给予权限,重启启动

chown mysql.mysql  /var/lib/mysql  -R
systemctl  start mysqld

增量备份

先开始一个完整的备份

innobackupex --user=USER --password=PASSWORD /backup/full

开始增量备份

innobackupex --incremental /path/to/inc/dir \
  --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD

校验包

innobackupex --apply-log --redo-only /xtrabackup/full/2016-12-08_10-13-42/

合并包

innobackupex --apply-log --redo-only /xtrabackup/full/2023-11-10_05-26-09/ --incremental-dir=/xtrabackup/full/2023-11-10_05-44-15/ //把谁合并到谁

差异备份

mysql主从复制

配置环境
统一MySQL5.7
关闭selinux
防火墙
centos安装5.7

yum install https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm

配置hosts和my.conf

10.0.0.1 master
10.0.0.2 node1
10.0.0.3 node2

vim /etc/my.cnf文件

log-bin=/var/lib/mysql/master
server-id=1 		//ID号需要跟从的配置不一样

gtid_mode=ON
enforce_gtid_consistency=1

授权用户

grant replication slave,super,reload on *.* to slave@'%' identified by 'Wang@qwer123';

添加master

change master to master_host='10.0.0.2',master_user='admin',master_password='Wang@qwer123',master_auto_position=1;

slave命令

stop salve;
start slave;
show slave status;
show master status;
show slave status \G;

多源复制

配置文件一样

授权一样

grant replication slave,super,reload on *.* to admin@'%' identified by 'Wang@qwer123';

让master互联

change master to master_host='master2',master_user='admin',master_password='Wang@qwer123',master_auto_position=1;

到此，互为主从配置成功

接下来配置两台slave: slave1: 修改配置文件vim /etc/my.cnf


log-bin 
server-id=3 
gtid_mode=ON 
enforce_gtid_consistency=1 
master-info-repository=TABLE
relay-log-info-repository=TABLE

两个机器都加入master1和2

change master to master_host='master2',master_user='admin',master_password='Wang@qwer123',master_auto_position=1 for channel 'master2';
change master to master_host='master',master_user='admin',master_password='Wang@qwer123', master_auto_position=1 for channel 'master1';

加入报错查id

解决链接
帮助