MySql运维篇

ops/2024/11/15 0:40:22/

目录

一.日志

1.1日志分类

1.2Error Log

1.3BinaryLog 

1.4SlowQuery Log

二.备份

2.1备份原因

2.2备份目标

2.3备份技术

2.3.1物理备份

2.3.2逻辑备份

2.4备份方式

2.4.1完全备份

2.4.2增量备份

2.4.3差异备份

2.5备份环境准备

2.6完全备份实验

2.6.1完全备份示例

2.6.2恢复数据库

2.7增量备份实验

2.7.1增量备份示例

2.7.2增量备份恢复

2.8差异备份实验

2.8.1差异备份示例

2.8.2差异备份恢复

2.8压缩备份实验

三.数据库集群

3.1集群目的

3.2集群原理图

mysql%E9%9B%86%E7%BE%A4%E7%8E%AF%E5%A2%83%E6%90%AD%E5%BB%BA-toc" style="margin-left:40px;">3.3mysql集群环境搭建

3.4一主一从(M-S)实验1

3.5一主一从(M-S)实验2

一.日志

1.1日志分类

1.2Error Log

vim /etc/my.cnf  进入主配置文件， 观察日志是否启动。
log-error/var/log/myqld.log 该字段， 标记是否启动日志，以及日志位置。
如果哪天mysq|服务起不来了，请来这个日志文件看看。

1.3BinaryLog 

默认没有开启，二进制日志

启动二进制日志:

                          vim /etc/my.cnf

                           log_bin 启动二进制日志

                           server-id=2 指定主机序号

                           systemctl restart mysqld  重启数据库                  

1.4SlowQuery Log

默认慢查询日志未开启

开启慢查询日志:

                          vim /etc/my.cnf   添加如下内容:

                           slow_query_log=1

                           long_query_time=3

                          systemctl restart mysqld  重启服务器          

二.备份

2.1备份原因

数据库中的信息容易丢失，同时也容易被人不小心删除

2.2备份目标

  • 保持数据的一致性
  • 保持服务的可用性

2.3备份技术

物理备份(冷备份)

逻辑备份(热备份)

2.3.1物理备份

直接复制数据库文件，适用于大型数据库环境

拷贝数据，优点快，缺点服务停止

2.3.2逻辑备份

备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低

2.4备份方式

2.4.1完全备份

将原来的数据原封不动地复制一份

2.4.2增量备份

备份上一次备份以来有变化的文件，因此备份体积小，备份速度快，但恢复时间较长

2.4.3差异备份

备份的数据是相对于第一次备份后，后面每次备份的数据，都是相对于第一次备份后的变化的数据，因此占用空间比增量备份大，比完整备份小，恢复时仅需回复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间

2.5备份环境准备

#yum安装percona仓库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
#安装XtraBackup
yum install percona-xtrabackup-80 -y

2.6完全备份实验

2.6.1完全备份示例

#创建备份目录

mkdir -p /data/backup

#完全备份
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -p'Openlab123!' -H localhost -P 3306
对备份参数的解释:
--defaults-file=/etc/my.cnf: 这个选项指定了 MySQL 的配置文件路径。
--backup: 这个选项告诉 xtrabackup 执行一个备份操作。
--target-dir=/data/backup/base/: 此选项指定了备份数据存储的目标目录。
-uroot: 使用 -u 选项后跟用户名（在这里是 root）来指定连接数据库时使用的 MySQL 用户。
-p'Openlab123!': -p 选项后跟的是用于认证的密码。

注意:每次备份时需要将已有的数据清除掉用rm -rf /data/backup/

2.6.2恢复数据库

# 停止MySQL服务，并删除MySQL数据目录下的所有文件  
systemctl stop mysqld
rm -rf /var/lib/mysql/*
# 准备需要备份的文件
xtrabackup --prepare --target-dir=/data/backup/base/
# 开始恢复数据库
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base
# 授权MySQL数据目录给MySQL用户
chown -R mysql.mysql /var/lib/mysql
# 重启MySQL服务并登录MySQL
systemctl restart mysqld
mysql -uroot -p

2.7增量备份实验

2.7.1增量备份示例

# 在进行增量备份前，需要进行一次完全备份
mkdir -p /data/backup/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

# 在全备的基础上，添加了新的数据后进行了增量备份1
insert into students values (4,'王二');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/ --incremental-basedir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

# 在增量备份1的基础上，再次添加新的数据后进行了增量备份2
insert into students values (5,'黎明');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

注意:每次增量备份基于的目标是上一次的备份，产生变化的数据

2.7.2增量备份恢复

# 停止MySQL服务并删除MySQL数据目录下的所有文件
systemctl stop mysqld
rm -rf /var/lib/mysql/*

# 准备需要备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc1
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc2

# 开始恢复数据库
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/

重新授权后重启MySQL服务
chown -R mysql.mysql /var/lib/mysql
systemctl restart mysqld

这里注意:最后一次增量备份恢复时，不需要添加--apply-log-only参数

2.8差异备份实验

2.8.1差异备份示例

# 在进行差异备份前，需要进行一次完全备份
mkdir -p /data/backup/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check
# 在全备的基础上，添加了新的数据后进行了差异备份1
insert into students values (5,'黎明');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/ --incremental-basedir=/data/backup/base/ -uroot -p#A123321000a -Hlocalhost -P 3306 --no-server-version-check
# 在全备的基础上，添加了新的数据后进行了差异备份2
insert into students values (6,'章节');
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2/ --incremental-basedir=/data/backup/base/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

注意:每次差异备份基于的目标是第一次的完全备份，产生变化的数据

2.8.2差异备份恢复

# 停止MySQL服务，并删除MySQL数据目录下的所有文件  
systemctl stop mysqld
rm -rf /var/lib/mysql/*
# 准备需要备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc1
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc2
# 开始恢复数据库
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/
# 授权MySQL数据目录给MySQL用户
chown -R mysql.mysql /var/lib/mysql
# 重启MySQL服务并登录MySQL
systemctl restart mysqld
mysql -uroot -p
注意:最后一次增量备份恢复时，不需要添加--apply-log-only参数

2.8压缩备份实验

创建备份目录，开始压缩备份:

mkdir -p /data/backup/compressed/
xtrabackup --defaults-file=/etc/my.cnf --backup --compress --target-dir=/data/backup/compressed/ -uroot -p#A123321000a -H localhost -P 3306 --no-server-version-check

 解压缩:

#解压缩前，删除原有的数据
systemctl stop mysqld
rm -rf /var/lib/mysql/*
解压缩(压缩的文件，需要解压后才能使用):
xtrabackup --defaults-file=/etc/my.cnf --decompress --target-dir=/data/backup/compressed/

恢复数据到mysql中:

#准备备份文件
xtrabackup --prepare --target-dir=/data/backup/compressed
#开始拷贝文件
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/compressed/
#重启服务并登录MYSQL
systemctl restart mysqld
 mysql -uroot -p
注意:解压时报错,安装yum install qpress -y

三.数据库集群

3.1集群目的

  • 实现负载均衡，解决高并发
  • 高可用HA，提高服务的可用性
  • 远程灾备，保证数据的有效性

3.2集群原理图

原理:当主库中的数据发生变化时，会记录到他的二进制日志中，从库的I/O线程会读取主库中的二进制日志，然后写入中继日志中，最后SQL线程会读取中继日志中的信息，将数据存储到本地数据库中。

mysql%E9%9B%86%E7%BE%A4%E7%8E%AF%E5%A2%83%E6%90%AD%E5%BB%BA">3.3mysql集群环境搭建

IP规划:

master1:192.168.145.133
master2:192.168.145.135
slave1:192.168.145.134
slave2:192.168.145.136
mycat:192.168.145.137(不安装mysql)

域名配置(5台机器都配置):

vim /etc/hosts
192.168.145.133 master1
192.168.145.135 master2
192.168.145.134 slave1
192.168.145.136 slave2
192.168.145.137 mycat
在第一台配置域名解析的服务器上对剩余4台机器快捷配置域名解析:
scp /etc/hosts 192.168.145.134:/etc/     
scp /etc/hosts 192.168.145.135:/etc/     
scp /etc/hosts 192.168.145.136:/etc/     
scp /etc/hosts 192.168.145.137:/etc/    

注意:在进行环境搭建时，必须是全新的未安装mysql的服务器

3.4一主一从(M-S)实验1

环境:

主(master1)
从(master2)
前提:安装了mysql，以及做好了域名解析:基于mysql集群的环境下进行

主(master1)端配置:


准备数据1(验证主从同步使用):
        create database master1db;
        create table master1db.master1tab(name char(50));
        insert into master1db.master1tab values(111);
        insert into master1db.master1tab values(222);

开启二进制日志:
 vim /etc/my.cnf #添加以下两行
log_bin
server-id=1
systemctl restart mysqld

授予用户权限
#登录数据库
mysql -uroot -p
#创建用户yulang，允许从IP地址以192.168.145开头的主机连接，并设置密码为#A123321000a
CREATE USER 'yulang'@'192.168.145.%' IDENTIFIED BY '#A123321000a'; 
#授予用户yulang从IP地址以192.168.145开头的主机上的所有数据库的复制从服务器和复制客户端权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'yulang'@'192.168.145.%';

退出mysql，备份master数据库的数据
mysqldump -p'#A123321000a' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql   # 导出数据库的备份，使用密码#A123321000a，采用单个事务导出，生成带有日期的文件名                                                                                                                           
准备数据2(验证主从同步使用):
mysql -uroot -p
insert into master1db.master1tab values(333);
insert into master1db.master1tab values(444);

从(master2)端配置:

测试yulang用户是否可用:
mysql -uyulang -p#A123321000a -h master1 
当这里登不进时，需要在每台服务器上关闭selinux和防火墙
setenforce 0
systemctl stop firewalld


启动服务器序号:
vim /etc/my.cnf
server-id=2
systemctl restart mysqld
mysql -uroot -p


手动同步数据，在主服务器端将数据同步到从服务器:
scp 2024-03-07-mysql-all.sql master2:/tmp/    #这一步在主服务器端操作
mysql -uroot -p
set sql_log_bin=0;
source /tmp/2024-03-07-mysql-all.sql; #将从服务器中临时目录中的数据同步到mysql中
select * from master1db.master1tab;     #查询是否同步成功

指定从服务器上的主服务器:
 vim 2024-03-07-mysql-all.sql  #此步在主服务器操作，将显示的数据添加到从服务器中
 CHANGE MASTER TO MASTER_LOG_FILE='bogon-bin.000002', MASTER_LOG_POS=157;

 #指定主从关系

CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='yulang', MASTER_PASSWORD='#A123321000a',  MASTER_LOG_FILE='bogon-bin.000002', MASTER_LOG_POS=157;

启动从设备，让主从关系生效:
start slave;
 

查看同步状态:

show slave status\G;  #显示如下两行时，说明同步成功

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

验证是否同步成功:

我们向主服务器插入数据:

mysql> insert into master1db.master1tab values(666);
Query OK, 1 row affected (0.03 sec)

mysql> select * from master1db.master1tab;
+------+
| name |
+------+
| 111  |
| 222  |
| 333  |
| 444  |
| 555  |
| 666  |
+------+
6 rows in set (0.00 sec)
 

转向从服务器查看同步的数据:

mysql> select * from master1db.master1tab;
+------+
| name |
+------+
| 111  |
| 222  |
| 333  |
| 444  |
| 555  |
| 666  |
+------+
6 rows in set (0.01 sec)

发现主服务器的数据确实同步到了从服务器中

3.5一主一从(M-S)实验2

与实验1的区别:自动记录二进制位置，不需要手动指定

master1端配置:

启动二进制日志，服务器ID，GTID
vim /etc/my.cnf
log_bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1
systemctl restart mysqld


备份数据
mysqldump -p --all-databases --single-transaction --set-gtid-purged=OFF --flush-logs > "$(date +\%F-\%H)-mysql-all.sql"
scp 2024-03-08-17-mysql-all.sql master2:/tmp
root@master2's password:    #注意这里不要输成了mysql的登录密码

模拟数据变化
insert into master1db.master1tab values(777);

master2配置:

重置从服务器的数据库
systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld
grep password /var/log/mysqld.log
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '#A123321000a';

测试主服务器上的yulang用户是否可用
mysql -h master1 -uyulang -p  #登录到master1数据库

启动二进制日志，服务器ID，GTID
vim /etc/my.cnf
log_bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
systemctl restart mysqld

将tmp目录下的数据同步到mysql中
mysql -uroot -p
set sql_log_bin=0; #临时关闭二进制日志
source /tmp/2024-03-08-17-mysql-all.sql
select * from master1db.master1tab;

设置主从关系
change master to master_host='master1',master_user='yulang',master_password='#A123321000a',master_auto_position=1;
这里注意我遇到了 Slave_IO_Running: Connecting的问题，其原因是我在进行设置主从关系的时候，将密码打错了
解决:
stop slave;
change master to master_host='master1',master_user='yulang',master_password='#A123321000a',master_auto_position=1;
显示主从状态
show slave status\G;


http://www.ppmy.cn/ops/6856.html

相关文章

Ubuntu 中如何高效的使用grep命令

Ubuntu 中如何高效的使用grep命令

grep 是一个命令行实用程序，在 Unix 和类 Unix 操作系统中用于在文本文件和命令输出中搜索模式。它的名称代表“全局正则表达式打印”。 基本语法： grep [选项] 模式 [文件 …] **模式：**你想搜索的文本模式。它可以是一个简单的字符串或一…
阅读更多...
考试酷基本功修炼课学习历程＿FPGA成长篇

考试酷基本功修炼课学习历程＿FPGA成长篇

本文为明德扬原创文章，转载请注明出处！作者：明德扬学员：考试酷账号：11167760 我是硬件工程师，日常工作中主要跟数字电路、模拟电路、嵌入式系统打交道，当然也会涉及到FPGA，但是苦于…
阅读更多...
手把手学浪视频怎么保存到本地

手把手学浪视频怎么保存到本地

很多人在学浪app上面购买了课程,但是并不是所有课程都是永久观看,所以就想要下载下来,进行永久观看 由于很多人都是小白用户,考虑到这一点,我封装成软件,大家不需要考虑视频m3u8地址是怎么获取的、KEY是怎么解密的,只需要掌握工具怎么用 工具我也给大家准备好了,有需要的自己…
阅读更多...
6.SpringBoot 日志文件

6.SpringBoot 日志文件

文章目录 1.日志概述2.日志作用3.使用和观察日志3.1如何观察日志3.2使用日志3.3日志级别3.4日志持久化3.5日志分割 4.日志框架4.1门面模式(外观模式)4.2 SLF4J框架介绍4.3 日志格式的说明4.3.1日志名称 5.日志颜色设置6.总结 大家好，我是晓星航。今天为大家带来的是…
阅读更多...
头歌实训作业答案c++

头歌实训作业答案c++

由于“头歌实训作业答案C”这个表述可能指的是某个特定课程或机构的C编程作业答案，通常这类作业答案不会公开分享，因为这涉及到版权和学术诚信的问题。但我可以提供一些C编程的通用指导和资源，帮助你完成实训作业。 ### C编程基础 1. **变量…
阅读更多...
网页vue3导出pdf

网页vue3导出pdf

需求：统计页面导出pdf； 利用插件：html2canvas和jspdf库； 原理就是：我们把需要转换的元素【例如一个div元素】使用html2canvas库将它转换为一个canvas对象，使用jspdf库创建PDF文档，用addImage方法…
阅读更多...
BGP边界网关路由实验（华为）

BGP边界网关路由实验（华为）

思科设备参考：BGP边界网关路由实验（思科） 一，技术简介 BGP（边界网关路由协议）是一种自治系统（AS）间的协议，主要用于在不同的AS之间交换路由信息。AS是一个由一组网络设…
阅读更多...
MySQL Prepared语句（Prepared Statements）

MySQL Prepared语句（Prepared Statements）

在数据库应用中，很多SQL语句都会重复执行很多次，每次执行可能只是where条件中的变量值不同，但MySQL依然会解析SQL语法并生成执行计划。对于这类情况，可以利用prepared语句来避免重复解析SQL的开销。 文章目录 一、prepared语句优…
阅读更多...
最新文章

Copyright @ 2022~2023