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; 确认二进制日志导入成功