MySQL的运维

MySQL运维

运维课程

日志
主从复制
分库分表
读写分离

日志

错误日志
二进制日志
查询日志
慢查询日志

错误日志

就是在my.cnf中配置错误日志

可以通过show variables like "%error%" ;查询如下

log_error | /my_mysql/3306/mysql_3306_error.log

error log主要记录MySQL在启动、关闭或者运行过程中的错误信息，在MySQL的配置文件my.cnf中，可以通过log-error=/var/log/mysqld.log 执行mysql错误日志的位置。

错误日志：主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。排查问题的首选文件。

error log记录信息

mysql执行过程中的错误信息
mysql执行过程中的警告信息
event scheduler运行时所产生的信息
mysql启动和停止过程中的输出信息，未必是错误信息
主从复制结构中，从服务器IO复制线程的启动信息

控制error log记录信息参数log_warnings

log_warnings的值为0，表示不记录警告信息。
log_warnings的值为1，表示警告信息一并记录到错误日志中。
log_warnings的值大于1，表示"失败的连接"的信息和创建新连接时"拒绝访问"类的错误信息也会被记录到错误日志中。

注意：错误日志中记录的并非全是错误信息，例如 MySQL 如何启动 InnoDB 的表空间文件、如何初始化自己的存储引擎等，这些也记录在错误日志文件中。

mysql> show variables like "%error%" ;
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| binlog_error_action | IGNORE_ERROR                        |
| error_count         | 0                                   |
| log_error           | /my_mysql/3306/mysql_3306_error.log |
| max_connect_errors  | 100                                 |
| max_error_count     | 64                                  |
| slave_skip_errors   | OFF                                 |
+---------------------+-------------------------------------+
6 rows in set (0.00 sec)

通过MySQL的命令

show variables like "%log_error%";

也可以获取到错误日志的位置。

二进制日志

MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。简单来讲，就是当你的一条sql操作对数据库中的内容进行了更新，就会增加一条bin log日志。查询操作不会记录到bin log中。bin log最大的用处就是进行主从复制，以及数据库的恢复。
通过下面的命令可以查看是否开启binlog日志

二进制日志：binlog属于逻辑日志，记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。有Mixed、Statement、ROW 3种格式、不同格式各有优缺点，主要用于复制和数据任意时间点还原。

Binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志。启用了binlog的前提下任何引擎都会记录binlog，可通过mysqlbinlog工具解析binlog日志。

binlog 有三种格式：

Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

#通过如下方式可以看到binlog的格式
mysql> show variables like "%binlog_format%";
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
#这种格式就是直接是sql语句


show VARIABLES like '%log_bin%'

开启binlog的方式如下：

log-bin=mysql-bin

server-id=1

binlog_format=ROW

其中log-bin指定日志文件的名称，默认会放到数据库目录下，可通过以下命令查看

show VARIABLES like '%datadir%'
mysql> show variables like "%log_bin%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /my_mysql/3306/mysql-bin       |
| log_bin_index                   | /my_mysql/3306/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

#刷新binlog

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     69459 |
| mysql-bin.000002 |   1640526 |
| mysql-bin.000003 |     69480 |
| mysql-bin.000004 |   1640526 |
| mysql-bin.000005 |     69480 |
| mysql-bin.000006 |   1640526 |
| mysql-bin.000007 |     69480 |
| mysql-bin.000008 |   1640526 |
| mysql-bin.000009 |      5516 |
| mysql-bin.000010 |    731622 |
| mysql-bin.000011 |       120 |
+------------------+-----------+
11 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#重置binlog
mysql> reset master;
Query OK, 0 rows affected (0.45 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

查询日志

general log 记录了客户端连接信息以及执行的SQL语句信息，通过MySQL的命令

可以查看general log是否开启以及日志的位置。

mysql> show variables like "%general%";
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | OFF                               |
| general_log_file | /my_mysql/3306/data/localhost.log |
+------------------+-----------------------------------+
2 rows in set (0.00 sec)
general log 可通过配置文件启动，配置参数如下：

general_log = on

general_log_file = /usr/local/mysql/mysql-8.0.20/data/hecs-78422.log

普通查询日志会记录增删改查的信息，因此一般是关闭的。

慢查询日志

慢查询日志用来记录执行时间超过指定阈值的SQL语句，慢查询日志往往用于优化生产环境的SQL语句。可以通过以下语句查看慢查询日志是否开启以及日志的位置：

show variables like "%slow_query%";

慢查询日志的常用配置参数如下：
mysql> show variables like "%slow_query%";
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | OFF                                    |
| slow_query_log_file | /my_mysql/3306/data/localhost-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)



slow_query_log=1 #是否开启慢查询日志，0关闭，1开启

slow_query_log_file=/usr/local/mysql/mysql-8.0.20/data/slow-log.log #慢查询日志地址（5.6及以上版本）

long_query_time=1 #慢查询日志阈值，指超过阈值时间的SQL会被记录

log_queries_not_using_indexes #表示未走索引的SQL也会被记录

分析慢查询日志一般会用专门的日志分析工具。找出慢SQL后可以通过explain关键字进行SQL分析，找出慢的原因。

主从复制

概念

将主库的数据变更同步到从库，从而保证主库和从库数据一致。

数据备份，失败迁移，读写分离，降低单库读写压力

原理

主库会把数据变更记录再二进制日志文件binlog中。
从库连接主库，读取binlog日志，并写入自身中继日志relaylog
slave重做中继日志，将改变反映它自己的数据

搭建

准备服务器
配置主库
配置从库
测试主从复制

准备两台服务器

一台master

一台slave

#开放指定的3306端口号
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
#关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

主库配置

修改配置文件/etc/my.cn文件

#mysql服务ID，保证整个集群环境的唯一性，取值方位：1-2的32次方-1，默认为1
sercer-id=1
#是否只读，1代表只读，0代表读写
read-only=0
#忽略的数据，指的是不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

登入mysql配置

#登入mysql，创建远程连接的账号，并且授予主从复制的权限
create user 'itcast'@'%' identified with mysql_native_password by 'Root@123456';
#为itcast@‘%’分配用户主从权限
grant replication slave on *.* to itcast'%';
#通过指令查看二进制日志分析坐标
show master status；
字段的含义
file：从那个日志文件开始推送日志文件
position：从那个位置开始推送日志
binlog_ignore_db：指定不要同步的数据库

从库的配置

修改配置文件/etc/my.cnf

#mysql服务ID，保证整个集群环境的唯一性，取值方位：1-2的32次方-1，默认为1
sercer-id=2
#是否只读，1代表只读，0代表读写
read-only=1

重启mysql服务

systemctl restart mysqld

登入mysql，设置主从库

change prelication source to source_host='','source_user='',source_password='',source_log_file='',source_log_pos=xxx;
#如果是8.0.23之前的版本，语法如下
change master  to master_host='','master_user='',master_password='',master_log_file='',master_log_pos=xxx;

参数名	含义	8.0.23之前参数名
source_host	主库的IP地址	master_host
source_user	连接主库的用户名	master_user
source_password	连接主库的密码	master_password
source_log_fil	binlog日志文件名	master_log_file
source_log_pos	binlog日志文件位置	master_log_pos

开启同步操作

start replica; #8.0.22之后
start slave;   #8.0.22之前

查看主从同步状态

show replica status; #8.0.22之后
show salve status;    #8.0.22之前

#show replica status\G
#Replica_IO_Running: yes
#Replica_SQL_Running: yes

测试

再主库上创建数据库，表，并且插入数据
在从库中查询，验证主从是否同步

分库分表

介绍

问题分析

随着互联网及移动网的发张，应用系统的数据也是成指数式增长，若采用单数据库进行数据存储，存在以下瓶颈：

IO瓶颈：热点数据太多，数据库缓存不足，产生大量的磁盘IO，效率低，请求太多，带宽不够，网络IO瓶颈
CPU瓶颈：排序，分组，连接查询，聚合统计等SQL会耗费大量的CPU资源，请求数量太多，CPU出现瓶颈
分库分表的中心思想都市将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而大道提升数据库性能的目的

拆分策略

垂直拆分
- 垂直分库
- 垂直分表
水平拆分
- 水平分库
- 水平分表

Mycat概述

介绍

参考文章：https://www.jianshu.com/p/460ab9c072be

Mycat是开源的、活跃的、基于java语言编写的Mysql数据中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在
优势
- 性能可靠稳定
- 强大的技术团队
- 体系完善
- 社区活跃

安装

下载
现在默认还是使用1.6版本的Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz

Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍Mycat的Linux中环境的搭建，我们需要准备好如下软件。

Mysql
JDK
Mycat

服务器	安装软件	说明
192.168.20.97	JDK，Mycat	Mycat中间件服务器
192.168.20.97	Mysql	分片服务器
192.168.20.96	Mysql	分片服务器
192.168.20.95	Mysql	分片服务器

#解压
tar -zxvf Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz  -C /usr/local/
[root@localhost mycat]# ls -l
总用量 12
drwxr-xr-x. 2 root root  190 8月  27 16:12 bin
drwxrwxrwx. 2 root root    6 7月  18 2019 catlet
drwxrwxrwx. 4 root root 4096 8月  27 16:12 conf
drwxr-xr-x. 2 root root 4096 8月  27 16:12 lib
drwxrwxrwx. 2 root root    6 8月  28 2019 logs
-rwxrwxrwx. 1 root root  227 8月  28 2019 version.txt
bin:存放的是可执行文件，用于启停mycat
conf：存放mycat的配置文件
lib：存放mycat的项目依赖包（jar）
logs：存放mycat的日志文件
 catlet：多表联查

注意：需要删除mysql-connector-java-5.1.35.jar

在[root@localhost lib]# pwd
/usr/local/mycat/lib

然后替换成8.0版本的链接工具

可以参考这个下载https://www.jb51.net/article/190860.htm

#删除旧的java链接工具
[root@localhost lib]# rm -fr mysql-connector-java-5.1.35.jar 
[root@localhost lib]# rz -E
[root@localhost lib]# ls -l | grep mysql-connecto*
-rw-r--r--. 1 root root 2513563 7月   1 03:18 mysql-connector-java-8.0.30.jar
[root@localhost lib]# 
#授权
[root@localhost lib]# ls -l | grep mysql-connecto*
-rw-r--r--. 1 root root 2513563 7月   1 03:18 mysql-connector-java-8.0.30.jar
[root@localhost lib]# chmod 777 mysql-connector-java-8.0.30.jar

逻辑库
逻辑表
分片规则
分片节点
节点主机

Mycat入门

需求

水平分表：表结构一样，表中数据内容不一样

由于tb_other表中的数据量大，磁盘的IO达到了瓶颈，现在需要对tb_order表进行数据分片，分为3个节点，每一个节点主机位于不用的服务器上，具体的结构：如下链接图

环境准备

关闭防护墙
在3台数据创建db01
mycat分片配置：secema.xml
- 配置逻辑库
- 配置逻辑表
- 配置数据节点
- 配置节点
- 节点主机
- 分片规则

Mycat配置

Mycat分片

范围分片
取模分片
一致性哈希算法
运用指定算法
固定哈希算法
字符串哈希算法
按天分片
按自然月分片

Mycat管理及监控

Mycat的原理
mycat的web管理

总结

分库分表

核心：将单台数据库服务器的数据分散（垂直拆分，水平拆分）存储在多台数据库中

Mycat的概述

Mycat是一个开源的分库分表的中间件

Mycat入门

入门程序

Mycat的配置

Schema.xml 、rule.xml、server.xml

Mycat分片

分片规则：范围，取模，枚举，一致性哈希，固定分片哈希算法，字符串哈希解析算法，按天分片，按自然月分片

Mycat的监控和管理

原理，管理，监控工具

读写分离

读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操

作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。

通过MyCat即可轻易实现上述功能，不仅可以支持MySQL，也可以支持Oracle和SQL Server。

一主一从

MySQL的主从复制，是基于二进制日志（binlog）实现的。

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控

制。

schema.xml****配置

<!-- 配置逻辑库 --> 
<schema name="ITCAST_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"> </schema> <dataNode name="dn7" dataHost="dhost7" database="itcast" /> 
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"> 
<heartbeat>select user()</heartbeat> 
<writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306? useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" > 
<readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306? useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
</writeHost>
</dataHost>

writeHost代表的是写操作对应的数据库，readHost代表的是读操作对应的数据库。所以我们要想

实现读写分离，就得配置writeHost关联的是主库，readHost关联的是从库。

而仅仅配置好了writeHost以及readHost还不能完成读写分离，还需要配置一个非常重要的负责均衡

的参数 balance，取值有4种，具体含义如下：

参数值	含义
0	不开启读写分离，所有操作都发送到当前可用的writeHost上
1	全部的readHost与备用的writeHost都参与select语句的负载均衡（主要针对双主双从模式）
2	所有的读写操作都随机在writeHost，readHost上分发
3	所有的读写请求随机分发到writeHost对应的readhost上执行，writehost不负责读压力

所以，在一主一从模式的读写分离中，balance配置1或3都是可以完成读写分离的。

server.xml****配置

<user name="root" defaultAccount="true"> 
<property name="password">123456</property> 
<property name="schemas">SHOPPING,ITCAST,ITCAST_RW</property> 
<!-- 表级 DML 权限设置 -->
<!-- 
<privileges check="true"> 
<schema name="DB01" dml="0110" > 
<table name="TB_ORDER" dml="1110"></table>
</schema>
</privileges> 
--> 
</user>

双主双从

一个主机 Master1 用于处理所有写请求，它的从机 Slave1 和另一台主机 Master2 还有它的从

机 Slave2 负责所有读请求。当 Master1 主机宕机后，Master2 主机负责写请求，Master1 、

Master2 互为备机

双主双从读写分离

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控

制，通过writeType及switchType来完成失败自动切换的。

schema.xml

配置逻辑库：

 <schema name="ITCAST_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"> </schema>

配置数据节点：

<dataNode name="dn7" dataHost="dhost7" database="db01" />

配置节点主机：

<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306? useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" > <readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306? useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" /> </writeHost> <writeHost host="master2" url="jdbc:mysql://192.168.200.213:3306? useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" > <readHost host="slave2" url="jdbc:mysql://192.168.200.214:3306? useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" /> </writeHost> </dataHost>

属性说明:

balance="1"

代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简

单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，

M2,S1,S2 都参与 select 语句的负载均衡 ;

writeType

0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;

1 : 所有的写操作都随机地发送到配置的writeHost上 ;

switchType

-1 : 不自动切换

1 : 自动切换

user.xml

配置root用户也可以访问到逻辑库 ITCAST_RW2。

<user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">SHOPPING,ITCAST,ITCAST_RW2</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="true"> <schema name="DB01" dml="0110" > <table name="TB_ORDER" dml="1110"></table> </schema> </privileges> --> </user>

本文作者：Mr.zheng

本文链接：https://www.cnblogs.com/zheng520/p/16670892.html

posted @ 2022-09-08 21:15 迷茫的28岁阅读(10) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

公告

昵称：迷茫的28岁园龄：2年6个月粉丝：0 关注：5

昵称：迷茫的28岁
园龄： 2年6个月
粉丝： 0
关注： 5

+加关注

这是我的HOME|

2025年3月

日

一

二

三

四

五

六

Mr.zheng

一眼万年