mysql引擎介绍，日志汇总，主从原理，xtrabackup进行全量加增量加binlog恢复数据库

最新推荐文章于 2021-08-29 20:00:15 发布

淡泪无痕

最新推荐文章于 2021-08-29 20:00:15 发布

阅读量278

点赞数

文章标签： linux centos mysql 运维经验分享

本文链接：https://blog.csdn.net/DLWH_HWLD/article/details/119084364

版权

mysql服务

1、总结mysql常见的存储引擎以及特点。
2、MySQL日志各类总结。
3、主从复制及主主复制的实现
4、xtrabackup实现全量+增量+binlog恢复库

1、总结mysql常见的存储引擎以及特点。

show engines; 查看数据库支持的数据库引擎

在这里插入图片描述
MyISAM引擎特点：

不支持事务
读写互斥，不能同时进行读或写
不支持外键
读写数据较快，占用资源少
崩溃恢复性差
mysql 5.5之前默认的数据库引擎

MyISAM生成的文件

table_name.frm
table_name.MYD
table_name.MYI

InnoDB引擎特点：

支持行级锁
支持事务操作
读写阻塞和事务隔离级别相关
可以缓存数据和索引
崩溃恢复性较好
支持MVCC高并发
mysql5.5以后默认的数据库引擎

InnoDB生成的文件

table_name.ibd
table_name.frm

其他引擎：

MEMORY：内存数据保表存引擎
ARCHIVE：表查询和插入数据相关
CSV：表导出和导入相关

2、MySQL日志各类总结。

show variables like ‘%log%’; 查看全部跟日志相关的参数
在这里插入图片描述
日志类型有：事务日志（innodb引擎专属），错误日志，通用日志，慢查询日志，二进制日志等

事务日志：transaction log（只有使用innodb引擎才会生成的日志）

 - show variables like '%innodb_log%';    查看日志相关参数

innodb_log_buffer_size                        日志缓存区大小
innodb_log_checksums                          日志文件是否开启校验
innodb_log_compressed_pages                   是否压缩日志
innodb_log_file_size                          单个日志文件大小
innodb_log_files_in_group                     最多存储几个日志
innodb_log_group_home_dir                     事务日志存放目录。./表示安装目录下

在这里插入图片描述

show variables like '%innodb_flush_log%';    查看日志的刷新设置

innodb_flush_log_at_timeout 日志刷新频率
innodb_flush_log_at_trx_commit 日志写入到磁盘的级别

在这里插入图片描述

错误日志：mysql运行中输出错误事件的日志

SHOW GLOBAL VARIABLES LIKE 'log_error' ;  查看错误日志保存路径

log_error 错误日志保存目录和文件名
在这里插入图片描述

通用日志：记录数据库的sql语句和其他通用操作

默认不会开启这个功能

show global variables like 'general_log';    查看通用日志是否开启

在这里插入图片描述

开启方式
vi /etc/my.cnf
#在[mysqld]下添加
[mysqld]
general_log=ON
general_log_file=/var/log/mysql/general.log
log_output=file

#可以设置为将日志保存到mysql数据库中
#log_output=table 设置保存到mysql数据库中
#如果设置记录为table格式，那么就会记录在mysql数据库中的general_log这个表格中

mkdir /var/log/mysql -p                            创建文件保存路径
chown mysql.mysql /var/log/mysql                   将目录属主设置为mysql
systemctl restart mysqld                           重启服务

show global variables like 'general_log%';        查看通用日志参数

general_log 是否开启通用日志功能
general_log_file 通用日志保存路径
在这里插入图片描述

tail -F /var/log/mysql/general.log           实时查看是否生成通用日志

在这里插入图片描述

慢查询日志：记录查询时间超出指定时长的sql语句

show variables like '%slow_query%';    查看慢查询功能

slow_query_log                         慢查询功能是否开启
slow_query_log_file                    慢查询记录文件路径

show variables like 'long_query_time';     查看慢查询超时时间

long_query_time                            sql语句超时时间

在这里插入图片描述
开启方式：
vi /etc/my.cnf
#在[mysqld]下添加
[mysqld]
slow_query_log=ON
long_query_time=1 #单位为秒
slow_query_log_file=/var/log/mysql/slow_query.log

在这里插入图片描述

select * from cc;       输入一条慢查询语句

在这里插入图片描述

tail -3 /var/log/mysql/slow_query.log        确认慢查询被记录到

在这里插入图片描述

二进制日志：用于断电后恢复数据，备份等

show variables like 'log_bin%';        查看二进制的参数

log_bin                                是否开启二进制功能
log_bin_basename                       二进制的文件名
log_bin_index                          记录二进制文件日志的索引

show variables like 'sql_log_bin';    二进制动态变量，可以直接在终端上修改。要恢复数据时，可以关闭这个功能，暂停二进制日志写入。

在这里插入图片描述

show variables like 'binlog_format';  查看二进制日志保存格式

binlog_format            查看二进制日志保存格式

二进制日志记录三种模式：
ROW：行模式逐行记录，有重复也会记录
statement：语句模式只记录sql语句
mixed：混合模式行模式和语句模式同时进行，由系统自行判断记录

注意：语句模式只会记录sql语句是什么样子就是什么样子，如果有数据当时有时间戳等，恢复数据时的时间戳会跟原本的数据不一致。行模式则会自动返回当时的时间戳。

在这里插入图片描述

show master logs;       查看二进制文件列表

在这里插入图片描述

flush logs;           重新生成新的二进制日志

在这里插入图片描述

配置文件参数：

vi /etc/my.cnf
#在[mysqld]下添加
[mysqld]
log_bin=/var/lib/mysql/              设置二进制日志保存路径
binlog_format=ROW                    设置二进制记录模式
max_binlog_size=500M                 设置单个二进制文件大小
binlog_cache_size=40M                单个二进制日志缓存大小
max_binlog_cache_size=500M           全部二进制日志缓存大小
expire_logs_days=0                   设置二进制日志自动删除的天数，默认为0，不删除

3、主从复制及主主复制的实现

主从复制原理：

主节点：
为每个从节点开启一个Binlog dump线程，这个线程会把主节点写入二进制的文件的内容实时传送给从节点。

从节点：
开启一个I/O线程，请求并接收主节点的线程传过来的二进制数据，然后保存到中继日志中，SQL thread线程会把中继日志写到数据库中。
在这里插入图片描述

名称	信息
系统版本	Centos 7.8
数据库版本	mysql 8.0.26
主节点（master）	192.168.116.130
从节点（slave）	192.168.116.145

cat /etc/redhat-release
mysql -V
在这里插入图片描述

主从节点都安装数据库，安装mysql-8.0

vi /etc/yum.repos.d/mysql.repo
#设置yum源
[mysql]
name=mysql8.0
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-x86_64/
gpgcheck=0

yum -y install mysql-community-server      安装mysql8.0
systemctl enable mysqld                    开机启动
systemctl start mysqld                     启动服务

新的mysql版本必须有密码才能登录，默认查看日志会有一个初始密码
grep ‘temporary password’ /var/log/mysqld.log
在这里插入图片描述

主节点配置：

启动服务后，要重新初始化密码和将服务初始化

mysql -uroot -pfqM7lN:Hf%h-
在这里插入图片描述
alter user user() identified by ‘Master12#$’; 修改初始root密码

初始化数据库
mysql_secure_installation

Enter password for user root: 输出刚才设置的新的root密Master12#$
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
是否修改root密码，选择n

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
是否删除匿名用户，选择y
在这里插入图片描述
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
是否允许root用户能远程登录，测试时可以选择n。最好选y

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
默认会自动生成test这个数据库，是否删除test数据库，选择y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
是否刷新权限表，选择y
在这里插入图片描述
初始化数据库成功后：

vi /etc/my.cnf

#在[mysqld]下添加
[mysqld]
server-id=1                     #设置ID号
log-bin=/var/lib/mysql/binlog/binlog  #开启二进制并设置保存目录

命令行配置

mkdir /var/lib/mysql/binlog/ -p             创建二进制日志保存目录
chown mysql.mysql /var/lib/mysql/binlog     设置mysql为目录属主
systemctl restart mysqld                    重启服务使配置生效
mysql -uroot -pMaster12#$                   登录数据库

mysql端配置：

show master logs;                               
查看二进制日志记录位置为156，下面的新增账户操作会更新二进制日志的位置

新建账户
注意：mysql 8.0版本的密码插件与老的版本密码插件不一样。
CREATE USER 'copy'@'192.168.116.%' IDENTIFIED WITH mysql_native_password BY 'Copy12#$';

授权账户对所有数据库有复制权限
GRANT replication slave ON *.* TO 'copy'@'192.168.116.%';

在这里插入图片描述

show processlist;                默认只有root线程和event_cheduler线程

在这里插入图片描述

从节点配置：

systemctl enable mysqld                    开机启动
systemctl start mysqld                     启动服务

启动服务后，找到密码

grep ‘temporary password’ /var/log/mysqld.log
在这里插入图片描述
mysql -uroot -pvoIiq/kSR0%t 登录数据库修改初始密码
alter user user() identified by ‘Slave12#$’;

配置文件修改：

vi /etc/my.cnf

#在[mysqld]下添加
[mysqld]
server-id=2           
log-bin=/var/lib/mysql/binlog/binlog
read_only

命令行配置

mkdir /var/lib/mysql/binlog/ -p             创建二进制日志保存目录
chown mysql.mysql /var/lib/mysql/binlog     设置mysql为目录属主
systemctl restart mysqld                    重启服务，使配置生效

mysql进行初始化，初始化步骤看上面

mysql端配置

CHANGE MASTER TO
  MASTER_HOST='192.168.116.130',              #主服务器IP
  MASTER_USER='copy',                         #主服务器复制账户名
  MASTER_PASSWORD='Copy12#$',                 #主服务器复制账户密码
  MASTER_PORT=3306,                           #主服务器端口
  MASTER_LOG_FILE='binlog.000001',            #主服务器二进制日志名      
  MASTER_LOG_POS=156;                         #主服务器二进制日志记录位置
  
  start slave;                                #启动主从服务

在这里插入图片描述
show slave status\G 查看从节点配置状态

Master_Host: 192.168.116.130               主节点的IP
Master_User: copy                          主节点复制账户
Master_Port: 3306                          主节点mysql端口
Connect_Retry: 60                          主从连接超时时间
Master_Log_File: binlog.000001             主节点的二进制文件名
Read_Master_Log_Pos: 683                   已经读取到主节点的二进制位置
Relay_Log_File: slave-relay-bin.000002     从节点中继位置名
Relay_Log_Pos: 848                         从节点中继日志读取位置
Relay_Master_Log_File: binlog.000001       从节点SQL线程的二进制名字
Slave_IO_Running: Yes                      从节点的I/O连接线程
Slave_SQL_Running: Yes                     从节点的SQL线程

当看到Slave_IO_Running: Yes ，Slave_SQL_Running: Yes 都是Yes，证明主从复制成功。
在这里插入图片描述
验证主从配置：
主节点：

show processlist;  多了一个Binlog Dump线程

在这里插入图片描述
create database aa; 新建一个数据库

从节点查看：

show processlist;              查看多了2个线程

Connect                        等待主节点更新二进制信息过来
Query                          读取中继日志写入数据库

在这里插入图片描述
show databases; 确认主节点创建的数据库更新到从节点

主主复制原理：

节点之间互为主从，每台设备既是主节点又是从节点。

在这里插入图片描述

名称	信息
系统版本	Centos 7.8
数据库版本	mysql 8.0.26
主节点1（master1）	192.168.116.130
主节点2（master2）	192.168.116.145

2个服务器都按照mysql 8.0

vi /etc/yum.repos.d/mysql.repo
#设置yum源
[mysql]
name=mysql8.0
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-x86_64/
gpgcheck=0

yum -y install mysql-community-server 安装mysql8.0
systemctl enable mysqld 开机启动
systemctl start mysqld 启动服务

新的mysql版本必须有密码才能登录，默认查看日志会有一个初始密码
grep ‘temporary password’ /var/log/mysqld.log
在这里插入图片描述

master1配置：
mysql -uroot -p"fWsDDf51uQ&7"       进入数据库
alter user user() identified by 'Master12#$';   修改初始root密码

设置完密码后可以自己初始化，详细看上面。

vi /etc/my.cnf
#在[mysqld]下添加
server-id=1
log-bin=/var/lib/mysql/binlog/master1   #开启二进制日志并指定目录
auto_increment_offset=1                 #设置记录开始点
auto_increment_increment=2              #设置记录增幅

mkdir /var/lib/mysql/binlog/ -p             创建二进制日志保存目录
chown mysql.mysql /var/lib/mysql/binlog     设置mysql为目录属主
systemctl restart mysqld                    重启服务，使配置生效
mysql -uroot -p"Master12#$"                 进入数据库

在这里插入图片描述

show master logs;    确认二进制日志记录位置

新建账户
注意：mysql 8.0版本的密码插件与老的版本密码插件不一样。
CREATE USER 'copy'@'192.168.116.%' IDENTIFIED WITH mysql_native_password BY 'Copy12#$';

授权账户对所有数据库有复制权限
GRANT replication slave ON *.* TO 'copy'@'192.168.116.%';

在这里插入图片描述
从节点配置：

grep ‘temporary password’ /var/log/mysqld.log 查看初始root密码
在这里插入图片描述

master2配置：
mysql -uroot -p'.0!zuxoL5aLP'       进入数据库
alter user user() identified by 'Master12#$';   修改初始root密码

设置完密码后可以自己初始化，详细看上面。

vi /etc/my.cnf
#在[mysqld]下添加
server-id=2
auto_increment_offset=2
auto_increment_increment=2
log-bin=/var/lib/mysql/binlog/master2

mkdir /var/lib/mysql/binlog/ -p             创建二进制日志保存目录
chown mysql.mysql /var/lib/mysql/binlog     设置mysql为目录属主
systemctl restart mysqld                    重启服务，使配置生效

CHANGE MASTER TO
  MASTER_HOST='192.168.116.130',              #主服务器IP
  MASTER_USER='copy',                         #主服务器复制账户名
  MASTER_PASSWORD='Copy12#$',                 #主服务器复制账户密码
  MASTER_PORT=3306,                           #主服务器端口
  MASTER_LOG_FILE='master1.000001',            #主服务器二进制日志名      
  MASTER_LOG_POS=156;                         #主服务器二进制日志记录位置
  
  start slave;                                #启动主从服务

start slave;

在这里插入图片描述
确认master1为主，master2为从的主从复制成功。
show processlist; master2查看线程

master1创建数据库
create database aa;

master查看到数据库生成成功
show databases;

将master1变成master2的从节点
master2查看二进制位置
show master logs;
在这里插入图片描述

master1配置

 CHANGE MASTER TO
    ->   MASTER_HOST='192.168.116.145',      #设置master2的IP
    ->   MASTER_USER='copy',                 #账户用原本创建的账户就行
    ->   MASTER_PASSWORD='Copy12#$',         #密码不变
    ->   MASTER_PORT=3306,                   #master2的数据库端口
    ->   MASTER_LOG_FILE='master2.000001',   #master2的二进制日志文件名
    ->   MASTER_LOG_POS=898;                 #master2的二进制日志位置
start slave;                                 启动主从进程
show slave status\G                          查看主从状态

在这里插入图片描述

测试master1作为从，master2作为主
master2 创建数据库
create database bb;
在这里插入图片描述
show processlist; 查看master2的线程，多了一个复制线程

master1查看是否生效
show databases;
在这里插入图片描述

show processlist;              master1 线程

在这里插入图片描述

4、xtrabackup实现全量+增量+binlog恢复库

xtrabackup特点：

备份还原过程快，数据还原可靠性高
备份过程中不会打断正在执行的事务
能够压缩文件，节省磁盘空间
开源的工具，免费软件
命令行操作，方便脚本实现自动备份

官网地址：https://www.percona.com
mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本
mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本

以数据库mysql 5.7.29为例子
下载xtrabackup的rpm包。数据库要是太新，这个备份工具可能不支持备份。
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

安装rpm包
yum install percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm -y

备份步骤：

数据库版本	mysql5.7.29
系统版本	Centos 7.8

进入数据库生成一些数据，使得可以全量备份

create database aa;
use aa;
create table aa(id int,name char(5));
insert aa(id,name)values('1','AA');
insert aa(id,name)values('2','BB');
insert aa(id,name)values('3','CC');
insert aa(id,name)values('4','DD');

select * from aa;       查看表格全部内容

在这里插入图片描述

mkdir /mysql/backup -p 新建备份目录

开始全量备份

xtrabackup -uroot -pTest1234 --backup --target-dir=/mysql/backup/full

创建新增的数据，然后进行第一次增量备份

use aa;
insert aa(id,name)values('5','EE');
insert aa(id,name)values('6','FF');
insert aa(id,name)values('7','GG');
insert aa(id,name)values('8','HH');

在这里插入图片描述

进行第一次增量备份，需要指定全量备份的路径

xtrabackup -uroot  -pTest1234 --backup --target-dir=/mysql/backup/ZL1 --incremental-basedir=/mysql/backup/full

查看备份结束后的二进制位置
cat /mysql/backup/ZL1/xtrabackup_binlog_info
在这里插入图片描述

创建新增的数据，然后进行第二次增量备份

use aa;
insert aa(id,name)values('9','II');
insert aa(id,name)values('10','JJ');
insert aa(id,name)values('11','KK');
insert aa(id,name)values('12','LL');
select * from aa;

在这里插入图片描述

进行第二次增量备份，需要指定第一次增量备份的路径

xtrabackup -uroot  -pTest1234 --backup --target-dir=/mysql/backup/ZL2 --incremental-basedir=/mysql/backup/ZL1

cat /mysql/backup/ZL2/xtrabackup_binlog_info
在这里插入图片描述

创建新增的数据，然后进行第三次增量备份

use aa;
insert aa(id,name)values('13','MM');
insert aa(id,name)values('14','NN');
insert aa(id,name)values('15','OO');
insert aa(id,name)values('16','PP');
select * from aa;

在这里插入图片描述

进行第三次增量备份，需要指定第二次增量备份的路径

xtrabackup -uroot  -pTest1234 --backup --target-dir=/mysql/backup/ZL3 --incremental-basedir=/mysql/backup/ZL2

cat /mysql/backup/ZL3/xtrabackup_binlog_info
在这里插入图片描述

tar -zcf mysql_backup.tar.gz /mysql 备份的目录压缩

如果数据库还在对外访问，恢复时可能会出现一些数据丢失，最好关闭数据库服务。

binlog数据备份

创建新的binlog数据

use aa;
insert aa(id,name)values('17','QQ');
insert aa(id,name)values('18','RR');
insert aa(id,name)values('19','SS');
insert aa(id,name)values('20','TT');
commit;                  将所有事务提交

systemctl stop mysqld 关闭数据库服务

原本第3次增量备份二进制的位置为4818
cat /mysql/backup/ZL3/xtrabackup_binlog_info
在这里插入图片描述
mysqlbinlog 是mysql自带的binlog二进制日志查看工具
如果binlog文件太大或者，千万不要直接打开

mysqlbinlog /data/mysql/localhost-bin.000002|tail 查看到最新的二进制的位置
在这里插入图片描述

也就是说实际需要localhost-bin.000002的4818到5818这中间的数据。
注意：–stop-position=5839，导出二进制文件指定结束点需要加1，要不然不会导出5818这段记录。

导出binlog日志文件，然后传送到新的节点

mysqlbinlog --start-position=4818 --stop-position=5839 /data/mysql/localhost-bin.000002>binlog.sql

/etc/my.cnf和/etc/my.cnf.d备份
这2个文件是mysql的配置文件和目录，如果有做过配置，也要备份。

cp /etc/my.cnf /mysql/backup/
cp -r /etc/my.cnf.d /mysql/backup/

全量备份和增量备份还原

还原最好mysql的版本和系统版本一致，要不然可能出现问题。

将全量备份和增量备份的压缩文件传到新安装的数据库节点上面

scp mysql_backup.tar.gz 192.168.116.145:/root/
systemctl stop mysqld                          停止新安装的数据库
rm -rf /data/mysql/*                           删除已经安装好的数据库目录下的全部内容
mkdir /old_mysql                               新建目录存放老的数据库文件
tar xf mysql_backup.tar.gz -C /old_mysql/      解压备份的压缩文件到目录
ll /old_mysql/mysql/backup/                    确认备份目录还在

在这里插入图片描述

展开全量备份内容

xtrabackup --prepare --apply-log-only --target-dir=/old_mysql/mysql/backup/full/

需要将第一次增量备份复制到全量备份的文件夹下

xtrabackup --prepare --apply-log-only --target-dir=/old_mysql/mysql/backup/full/ --incremental-dir=/old_mysql/mysql/backup/ZL1

需要将第二次增量备份复制到全量备份的文件夹下

xtrabackup --prepare --apply-log-only --target-dir=/old_mysql/mysql/backup/full/ --incremental-dir=/old_mysql/mysql/backup/ZL2

注意：
最后一次增量备份不用添加 --apply-log-only参数

xtrabackup --prepare --target-dir=/old_mysql/mysql/backup/full/ --incremental-dir=/old_mysql/mysql/backup/ZL3

将还原的数据库文件复制到数据库，指定/old_mysql/mysql/backup/full/ 这个展开的目录即可，会自动去寻找安装mysql时的目录

xtrabackup --copy-back --target-dir=/old_mysql/mysql/backup/full/ 
ll /data/mysql/                                             确认恢复成功
chown -R mysql.mysql /data/mysql                            还原后会把数据库目录的权限修改了，改回mysql权限

在这里插入图片描述
systemctl start mysqld
select * from aa; 进入数据库，查看数据是否还在

binglog日志恢复

将binlog的日志文件传送到新安装的数据库节点上面

scp binlog.sql 192.168.116.145:/root/                 
source /root/binlog.sql         导入数据库
select * from aa;               确认二进制日志导入成功

在这里插入图片描述

淡泪无痕

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql引擎介绍，日志汇总，主从原理，xtrabackup进行全量加增量加binlog恢复数据库

mysql服务1、总结mysql常见的存储引擎以及特点。2、MySQL日志各类总结。3、主从复制及主主复制的实现4、xtrabackup实现全量+增量+binlog恢复库
复制链接

扫一扫