MyCat2数据中间件应用

mysql主从配置与自动切换

环境准备

  1. 三台虚拟机192.168.2.5(mycat)、192.168.2.6(mysql1)、192.168.2.7(mysql2)
  2. jdk8
  3. mysql版本：mysql-8.0.20-linux-glibc2.12-x86_64.tar
  4. mycat版本：mycat2-1.11-05.tar.gz 下载地址：http://dl.mycat.org.cn/2.0/

数据库安装配置

  1. 关闭防火墙
systemctl stop firewalld
systemctl status firewalld
  1. 禁用防火墙（系统启动时不启动防火墙服务）
systemctl disable firewalld
systemctl is-enabled firewalld
  1. 解压
tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64
  1. 重命名
mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql8
  1. 移动
mv mysql8 /usr/local
  1. 访问
cd /usr/local/mysql8
  1. 创建mysql数据储存文件
mkdir data
  1. 创建mysql用户组和mysql用户
groupadd mysql  
useradd -g mysql mysql
  1. 改变msql8目录权限
chown -R mysql.mysql /usr/local/mysql8/
  1. 初始化数据库

注意 lower-case-table-names =1 不区分大小写 在mysql8.x中只能在初始化时修改，安装完成后不能修改

bin/mysqld --initialize --user=mysql --lower-case-table-names=1 --basedir=/usr/local/mysql8 --datadir=/usr/local/mysql8/data
  1. 记录初始密码
A temporary password is generated for root@localhost: dvBbQz=yu8mP
  1. 修改配置 vi /etc/my.cnf
    最简配置
[client]
port=3306
socket=/tmp/mysql8/mysql.sock

[mysqld]
port=3306
user=mysql
socket=/tmp/mysql8/mysql.sock
basedir=/usr/local/mysql8
datadir=/usr/local/mysql8/data

优化配置

在var/log中创建并赋权mysql日志文件夹、创建tmpdir文件夹并赋权

mkdir /var/log/mysql8
chown -R mysql.mysql /var/log/mysql8
touch /var/log/mysql8/mariadb.log
chown -R mysql.mysql /var/log/mysql8/mariadb.log
chmod 777 /var/log/mysql8/mariadb.log

mkdir /var/log/mysql8/run
chown -R mysql.mysql /var/log/mysql8/run

mkdir /tmp/mysql8
chown -R mysql.mysql /tmp/mysql8

注意 innodb_log_file_size-日志大小可根据实际磁盘大小配置、

innodb_buffer_pool_size=251M-设置buffer pool size,一般为服务器内存60%

[mysqld_safe] 
 log-error=/var/log/mysql8/mariadb.log 
 pid-file=/var/log/mysql8/run/mysqld.pid 
   
 [client] 
 socket=/tmp/mysql8/mysql.sock
 default-character-set=utf8 
   
 [mysqld] 
 port=3306 
 user=mysql 
 basedir=/usr/local/mysql8
 tmpdir=/tmp/mysql8/tmp 
 datadir=/usr/local/mysql8/data
 socket=/tmp/mysql8/mysql.sock 
 # 默认使用“mysql_native_password”插件认证
 default_authentication_plugin=mysql_native_password 
 # 创建新表时将使用的默认存储引擎
 default-storage-engine=INNODB
 #设置页大小 
 innodb_page_size=16384 
 #关闭ssl 
 ssl=0 
 lower_case_table_names = 1
  #设置最大连接数 
 max_connections=2000 
 #设置会话请求缓存个数
 back_log=2048   
 #关闭性能模式
 performance_schema=OFF  
 max_prepared_stmt_count=128000 
 #服务器或客户端之间进行传输大小
 max_allowed_packet=128M
 
 #file 
 #设置每个表一个文件
 innodb_file_per_table  
 #设置logfile大小 1500M
 innodb_log_file_size=150M 
 #设置logfile组个数 
 innodb_log_files_in_group=32 
  #设置最大打开表个数 
 innodb_open_files=4000
   
 #buffers 
 #设置buffer pool size,一般为服务器内存60%
 innodb_buffer_pool_size=251M  
 #设置buffer pool instance个数，提高并发能力 
 innodb_buffer_pool_instances=16 
  #设置log buffer size大小 
 innodb_log_buffer_size=64M
   
 #tune 
 #设置每次sync_binlog事务提交刷盘 
 sync_binlog=1 
 #每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去
 innodb_flush_log_at_trx_commit=1  
 #开启异步IO
 innodb_use_native_aio=1  
 #设置spin_wait_delay 参数，防止进入系统自旋 
 innodb_spin_wait_delay=180 
 #设置spin_loops 循环次数，防止进入系统自旋 
 innodb_sync_spin_loops=25  
 #设置innodb数据文件及redo log的打开、刷写模式 
 innodb_flush_method=O_DIRECT 
 # 设置innodb 后台线程每秒最大iops上限 
 innodb_io_capacity=20000 
 #设置压力下innodb 后台线程每秒最大iops上限 
 innodb_io_capacity_max=40000 
 #设置page cleaner线程每次刷脏页的数量 
 innodb_lru_scan_depth=9000 
 #设置将脏数据写入到磁盘的线程数 
 innodb_page_cleaners=16  
   
 #perf special 
 #检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新，SSD关闭该功能
 innodb_flush_neighbors=0  
 #设置写线程数
 innodb_write_io_threads=16  
 #设置读线程数 
 innodb_read_io_threads=16 
 #设置回收已经使用并分配的undo页线程数 
 innodb_purge_threads=32  
   
 sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES
  1. 在tmp中创建mysql8文件夹 并赋权
mkdir /tmp/mysql8/tmp
chown -R mysql:mysql /tmp/mysql8/tmp
  1. 拷贝mysql启动文件到系统初始化目录
cp /usr/local/mysql8/support-files/mysql.server /etc/init.d/mysqld
  1. 启动mysql 服务器
service mysqld start
  1. 连接mysql
/usr/local/mysql8/bin/mysql -u root -p
  1. 登录不了 进行修改root密码
    修改配置文件免密码登录
mysql vim /etc/my.cnf
# 在 [mysqld]最后加上如下语句
 skip-grant-tables
  1. 重启mysql服务
service mysqld restart
  1. 连接
mysql /usr/local/mysql8/bin/mysql -u root -p
  1. 首先查看当前root用户相关信息，在mysql数据库的user表中
use mysql;
select host, user, authentication_string, plugin from user;
  1. 如果当前root用户authentication_string字段下有内容，先将其设置为空
update user set authentication_string='' where user='root';
  1. 退出mysql, 删除/etc/my.cnf文件最后的 skip-grant-tables 重启mysql服务
  2. 使用root用户进行登录，因为上面设置了authentication_string为空，所以可以免密码登录
  3. 使用ALTER修改root用户密码
ALTER user 'root'@'localhost' IDENTIFIED BY 'root';
  1. 修改root用户可以远程连接
update mysql.user set host='%' where user='root';

数据库主从配置

  1. 安装好两个服务器的数据库后 修改主服务器配置
  2. 修改配置
vim /etc/my.cnf
  1. 修改内容增加
# 节点唯一id值
server-id=1
# 开启二进制日志
log-bin=mysql-bin
# 指定日志格式 有mixed|row|statement 推荐mixed
binlog-format=mixed   
# 忽略mysql，information_schema库(可选配置)
binlog-ignore-db = mysql,information_schema
  1. 重启服务 查看 master 的状态
![在这里插入图片描述]()
  1. 创建用于复制操作的用户
CREATE USER 'repl'@'192.168.2.7' IDENTIFIED WITH mysql_native_password BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.7';
flush privileges;
  1. 修改从服务器配置
  2. 修改配置
vim /etc/my.cnf
  1. 修改内容增加
# 节点唯一id值
server-id=2
# 开启二进制日志
log-bin=mysql-bin
# 指定日志格式 有mixed|row|statement 推荐mixed
binlog-format=mixed
  1. 在从（Slave）节点上设置主节点参数
CHANGE MASTER TO MASTER_HOST='192.168.2.6', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=856;
  1. 查看主从同步状态
show slave status\G;
  1. 开启主从同步
start slave;
  1. 再查看主从同步状态
show slave status\G;
  1. 若I/O任务启动失败
    从机的server_id改成2
    set global server_id=2; #此处的数值和my.cnf里设置的一样就行
  2. 若出现错误 mysql-bin.000001’：
  1. the first event ‘mysql-bin.000001’ at 98, the last eventread from ‘.\mysql-bin.000001’ at 98, the last byte read from ‘.\mysql-bin.000001’ at 117.
  2. Slave_IO_Running: NO 或 Slave_SQL_Running: NO

解决

先进入slave中执行:"stop slave ;"来停止从库同步；

再去master中执行:"flush logs;"来清空日志；

然后在master中执行:"show master status;"查看下主库的状态，主要是日志的文件和position；

然后回到slave中，执行:“CHANGE MASTER TO MASTER_LOG_FILE=‘查看到的日志文件’,MASTER_LOG_POS=查看到的position值;”，文件和位置对应master中的;

  1. 重新开启同步并查看状态
start slave;
show slave status\G;
  1. 测试主从
    在主创建数据库：
CREATE DATABASE test;

在从查看数据库:

show databases;

Mycat2安装配置

  1. 下载并安装jdk8
    下载jdk-8u221-linux-x64.tar.gz，官网地址：http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
    解压：tar -zxvf jdk-8u221-linux-x64.tar.gz
    将解压后的文件夹移动到/usr/local目录下：mv jdk1.8.0_221 /usr/local/
    打开文件 vim /etc/profile，添加以下内容：
export JAVA_HOME=/usr/local/jdk1.8.0_221
export JRE_HOME=${JAVA_HOME}/jre
export CLASSPATH=.:${JAVA_HOME}/lib/dt.JAVA_HOME/lib/tools.jar:${JRE_HOME}/lib
export PATH=${JAVA_HOME}/bin:${PATH}

重新加载配置文件

source /etc/profile

验证

java -version
  1. 解压mycat2并运行
tar -xvf mycat2-1.11-05.tar.gz
cd mycat/bin

./mycat start 启动
./mycat stop 停止
./mycat console 前台运行
./mycat install 添加到系统自动启动（暂未实现）
./mycat remove 取消随系统自动启动（暂未实现）
./mycat restart 重启服务
./mycat pause 暂停
./mycat status 查看启动状态
  1. 修改配置文件
    官方配置参考
    修改mycat.yml文件中
    读写分离，分配配置：
    schemas: [] 是读写分离，分配配置，默认无需修改，默认读写分离
    schemas.schemaName:‘配置逻辑库’
    数据库连接配置：
    datasources:[]，配置需要连接的数据库
    集群配置：cluster []
    name: repli 和schemas的读写配置对应
    masters：主节点列表,普通主从,当主失去连接后,依次选择列表中存活的作为主节点
    replicas：从节点列表
  2. 连接测试
    适用navicat工具连接

mysql主从自动切换的脚本_mycat


mysql主从自动切换的脚本_mycat_02

测试

注意：mycay不存在创建库，创建表操作，需要主节点创建库(物理库)后在mycat配置文件中配置逻辑库，表同理，分库分表需额外配置。

  1. 创建库
    在主-数据库中创建 test1 配置mycat并重启
  2. 创建表
    在主-数据库中创建 test_table1 刷新mycat连接表

Mycat2管理与监控

Mycat2管理与监控相关配置

参考配置 配置9066管理端和7066监控信息列表

cd /usr/local/mycat2/mycat/conf
vim mycat.yml
# 增加以下配置
plug:
#此配置默认开启7066端口.并提供以下url供查询监控信息 http://127.0.0.1:7066/metrics
  extra: [
           "io.mycat.exporter.PrometheusExporter"
     ]
manager:
  ip: 0.0.0.0
  port: 9066
  users: [{ip: '.', password: 'root', username: root}]
properties:
#此配置可以更改io.mycat.exporter.PrometheusExporter开启的端口
  prometheusPort: 7066

监控管理的命令

关闭连接
kill @@connection id1,id2...

id是mycat前端连接或者后端native连接的id(它们公用一个id生成器)

不能关闭jdbc连接,当关闭mycat前端连接的时候会自动关闭连接占用的jdbc连接

显示Mycat前端连接
show @@connection

ID 连接的标识符

USER_NAME 登录的用户名

HOST 客户端连接地址

SCHEMA 当前schema,与sql解析有关

AFFECTED_ROWS AFFECTED_ROWS

AUTOCOMMIT 是否自动提交

IN_TRANSACTION 是否处于事务状态

CHARSET 字符编码,一般是utf8

CHARSET_INDEX 对应mysql的字符编码序号

OPEN 连接是否打开

SERVER_CAPABILITIES 服务器能力数字

ISOLATION 事务隔离级别

LAST_ERROR_CODE 最后一次错误码

LAST_INSERT_ID 插入自增主键ID

LAST_MESSAGE 最后一次错误信息

PROCESS_STATE 请求处理状态,正在接收,正在处理,完成

WARNING_COUNT 警告数量

MYSQL_SESSION_ID 如果代理

TRANSACTION_TYPE 事务类型,XA,Proxy,Local

TRANSCATION_SNAPSHOT 事务管理器状态快照

CANCEL_FLAG 当前执行的任务是否已经被取消

显示native连接
show @@backend.native

显示mycat proxy native 连接的信息

SESSION_ID 连接ID,可被kill命令杀死

THREAD_NAME 所在线程名

DS_NAME数据源名字

LAST_MESSAGE 接收到的报文中的信息(错误信息)

MYCAT_SESSION_ID 如果有绑定前端连接,则显示它的ID

IS_IDLE 是否在连接池,即是否闲置

SELECT_LIMIT限制返回行数

IS_RESPONSE_FINISHED响应是否结束

RESPONSE_TYPE响应类型

IS_IN_TRANSACTION是否处于事务状态

IS_REQUEST_SUCCESS是否向后端数据库发起请求成功

IS_READ_ONLY是否处于readonly状态

显示数据源状态
show @@backend.datasource

显示配置中的数据源信息

显示心跳状态
show @@backend.heartbeat

显示配置中的心跳信息

显示可以使用的管理命令
show @@help
显示心跳中数据源实例中的状态

navite连接与jdbc连接使用相同的数据源配置,指向相同的服务器,那么它们的数据源实例只有一个

show @@backend.instance

NAME 数据源名字

ALIVE 是否存活

READABLE 是否可以选择为读节点

TYPE 数据源类型

SESSION_COUNT 当前连接数量

WEIGHT 负载均衡权重

MASTER是否主节点

HOST连接信息

PORT连接端口

LIMIT_SESSION_COUNT连接限制数量

REPLICA所在集群名字

显示逻辑库配置
show @@metadata.schema

显示配置中的逻辑库信息

显示逻辑表配置
show @@metadata.schema.table

显示配置中的逻辑表信息

显示reactor线程状态

reactor是mycat2的io线程,主要处理透传响应与接收报文,解析sql等任务

show @@reactor

THREAD_NAME线程名字

THREAD_ID 线程ID

CUR_SESSION_ID当前正在处理的前端,后端会话ID

BUFFER_POOL_SNAPSHOT 网络缓冲区池快照

LAST_ACTIVE_TIME 最近活跃时间

显示集群状态
show @@backend.replica

NAME 集群名字

SWITCH_TYPE 切换类型

MAX_REQUEST_COUNT 获取连接的时候尝试请求的次数

TYPE 集群类型

WRITE_DS 写节点列表

READ_DS 读节点列表

WRITE_L写节点负载均衡算法

READ_L读节点负载均衡算法

显示定时器状态
show @@schedule
显示sql统计信息
show @@stat

COMPILE_TIME 编译SQL的耗时

RBO_TIME 规则优化耗时

CBO_TIME 代价优化与生成执行器耗时

CONNECTION_POOL_TIME 连接池获取连接耗时

CONNECTION_QUERY_TIME 发起查询到获得响应耗时

EXECUTION_TIME 执行引擎耗时

TOTAL_TIME 查询总耗时

重置sql统计信息
reset @@stat
显示线程池状态
show @@threadPool

NAME 线程池名字

POOL_SIZE 线程最大数量

ACTIVE_COUNT 活跃线程数

TASK_QUEUE_SIZE 等待队列大小

COMPLETED_TASK 完成的任务数量

TOTAL_TASK 总任务数量

设置数据源实例状态
switch @@backend.instance = {name:'xxx' ,alive:'true' ,readable:'true'}

name是数据源名字

alive是数据源可用状态,值 true|false

readable是数据源可读状态,值 true|false

此命令供外部服务修改mycat里的数据源实例状态,可以以此支持多种集群服务

集群切换
switch @@backend.replica = {name:'xxx'}

name是数据源名字

手动触发集群切换

此命令供外部服务修改mycat里的数据源实例状态,可以以此支持多种集群服务

心跳开关
switch @@backend.heartbeat = {true|false}

当有心跳配置的时候,可以进心跳进行开启关闭

心跳会自动修改数据源实例的状态,关闭心跳可以自行通过上面的命令修改状态

此命令供外部服务修改mycat里的数据源实例状态,可以以此支持多种集群服务

显示心跳定时器是否正在运行
show @@backend.heartbeat.running
配置更新
reload @@config by file

修改本地的mycat.yml就可更新,支持更新metadata与jdbc数据源.请在低峰时段执行,配置更新停止IO请求,尽量选择没有事务的一刻进行更新,不保证配置前后一致性等问题

显示服务器信息
show @@server