mycat读写分离配置

2020年4月10日

准备 MYSQL 集群
当前注备三个MYSQL实例
示例：
192.168.11.1  Mycat
192.168.11.1  mysql master
192.168.11.2  mysql slave
192.168.11.3  mysql slave

本次测试示例
docker 起的mysql 实例
192.168.11.120  Mycat
192.168.11.120:33001   Master
192.168.11.120:33002   salve
192.168.11.120:33004   slave

33001 master server  配置 （MariaDB10.4）

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking

init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
character_set_server = utf8
collation_server = utf8_general_ci
#character-set-client-handshake = FALSE
#character-set-server = utf8mb4
#collation-server = utf8mb4_unicode_ci

max_connections         = 1000
connect_timeout         = 180
wait_timeout            = 600
max_allowed_packet      = 16M
thread_cache_size       = 128
bulk_insert_buffer_size = 16M
tmp_table_size          = 32M
max_heap_table_size     = 32M

myisam_recover_options = BACKUP
key_buffer_size         = 128M
#open-files-limit       = 2000
table_open_cache        = 512
myisam_sort_buffer_size = 512M
concurrent_insert       = 2
read_buffer_size        = 4M
read_rnd_buffer_size    = 4M
sort_buffer_size        = 1M
join_buffer_size        = 1M

query_cache_limit               = 128K
query_cache_size                = 64M

log_warnings            = 2

slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit    = 1000
log_slow_verbosity      = query_plan

server-id              = 1000
report_host            = master
######  忽略
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index

#sync_binlog            = 1
expire_logs_days        = 60
max_binlog_size         = 1G

#### innodb 配置
default_storage_engine  = InnoDB
innodb_buffer_pool_size = 4G
innodb_log_buffer_size  = 8M
innodb_log_file_size   = 128
innodb_file_per_table   = 1
innodb_open_files       = 1000
innodb_io_capacity      = 500
innodb_io_capacity      = 1000
innodb_flush_method     = O_DIRECT

[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
default-character-set = utf8

[isamchk]
key_buffer              = 16M

!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/


33002  Mysql  Slave （mysql5.7）

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0


[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
character-set-server=utf8
collation-server=utf8_general_ci


max_connections         = 1000
max_connect_errors     = 100
connect_timeout         = 180
wait_timeout            = 600
max_allowed_packet      = 16M
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 512M
table_open_cache        = 1024
max_heap_table_size     = 32M


myisam_recover_options = BACKUP
key_buffer_size         = 128M
myisam_sort_buffer_size = 512M
concurrent_insert       = 2
read_buffer_size        = 4M
read_rnd_buffer_size    = 4M
sort_buffer_size        = 2M
join_buffer_size        = 2M

#* Query Cache Configuration

query_cache_limit               = 1M
query_cache_size                = 64M

slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time = 10

server-id              = 1023
report_host            = mysql-slave1
#auto_increment_increment = 2
#auto_increment_offset  = 1
log_bin                 = /var/log/mysql/mysql-bin
log_bin_index           = /var/log/mysql/mysql-bin.index
# not fab for performance, but safer
#sync_binlog            = 1
expire_logs_days        = 30
max_binlog_size         = 2048M

default_storage_engine  = InnoDB
innodb_buffer_pool_size = 4G
innodb_log_buffer_size  = 8M
innodb_log_file_size   = 128
innodb_file_per_table   = 1
innodb_open_files       = 1000
innodb_io_capacity      = 500
innodb_io_capacity      = 1000
innodb_flush_method     = O_DIRECT

[galera]
# Mandatory settings
# #wsrep_on=ON
# #wsrep_provider=
# #wsrep_cluster_address=
# #binlog_format=row
# #default_storage_engine=InnoDB
# #innodb_autoinc_lock_mode=2
# #
# # Allow server to accept connections on all interfaces.
# #
# #bind-address=0.0.0.0
# #
# # Optional setting
# #wsrep_slave_threads=1
# #innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
default-character-set = utf8

[isamchk]
key_buffer              = 16M


!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/


33004   Mysql  Slave （MariaDB10.4）


[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking

init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
character_set_server = utf8
collation_server = utf8_general_ci
#character-set-client-handshake = FALSE
#character-set-server = utf8mb4
#collation-server = utf8mb4_unicode_ci


max_connections         = 1000
connect_timeout         = 180
wait_timeout            = 600
max_allowed_packet      = 16M
thread_cache_size       = 128
bulk_insert_buffer_size = 16M
tmp_table_size          = 256M
max_heap_table_size     = 32M

# * MyISAM

myisam_recover_options = BACKUP
key_buffer_size         = 128M
#open-files-limit       = 2000
table_open_cache        = 512
myisam_sort_buffer_size = 512M
concurrent_insert       = 2
read_buffer_size        = 4M
read_rnd_buffer_size    = 4M
sort_buffer_size        = 1M
join_buffer_size        = 1M


# * Query Cache Configuration

query_cache_limit               = 128K
query_cache_size                = 64M

log_warnings            = 2

slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit    = 1000
log_slow_verbosity      = query_plan


server-id              = 1024
report_host            = mariadb-slave1
#auto_increment_increment = 2
#auto_increment_offset  = 1
log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog            = 1
expire_logs_days        = 60
max_binlog_size         = 1G
# slaves
#relay_log              = /var/log/mysql/relay-bin
#relay_log_index        = /var/log/mysql/relay-bin.index
#relay_log_info_file    = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#

# * InnoDB

default_storage_engine  = InnoDB
innodb_buffer_pool_size = 4G
innodb_log_buffer_size  = 8M
innodb_log_file_size   = 128
innodb_file_per_table   = 1
innodb_open_files       = 1000
innodb_io_capacity      = 500
innodb_io_capacity      = 1000
innodb_flush_method     = O_DIRECT

[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
default-character-set = utf8
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer              = 16M


!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/

主从配置 (忽略配置过程)

配置两个用户 一直只读一个读写
admin
read

安装 MyCat

官方网站下载：https://github.com/MyCATApache/Mycat-download

  1. wget https://github.com/MyCATApache/Mycat-download/blob/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
  2. tar -zxvf Mycat-server-1.6-RELEASE-20161012170031-linux.tar.gz
  3. mv mycat  /usr/local/

配置 MyCat
cat    schema.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  4.         <schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
  5.         </schema>
  6.         <dataNode name="dn1" dataHost="localhost1" database="mydb" />
  7.         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
  8.                 <heartbeat>select user()</heartbeat>
  9.                 <!-- can have multi write hosts -->
  10.                 <writeHost host="master" url="192.168.11.120:33001" user="admin" password="wx123123">
  11.                         <!-- can have multi read hosts -->
  12.                         <readHost host="mariadb-slave1" url="192.168.11.120:33004" user="admin" password="wx123123" />
  13.                         <readHost host="mysql-slave1" url="192.168.11.120:33002" user="admin" password="wx123123" />
  14.                 </writeHost>
  15.         </dataHost>
  16.         <!--<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  17.                 <heartbeat>select user()</heartbeat>
  18.                 <writeHost host="192.168.11.120" url="192.168.11.120:3306" user="admin" password="wx123123">
  19.                         <readHost host="192.168.11.120" url="192.168.11.120:3306" user="admin" password="wx123123" />
  20.                 </writeHost>
  21.         </dataHost>-->


</mycat:schema>

其中：

schema 标签用于定义MyCat 实例中的逻辑库，MyCat 可以有多个逻辑库，每个逻辑库都有自己的相关配置。可以使用schema 标签来划分这些不同的逻辑库。

dataNode 标签定义了MyCat 中的数据节点，也就是我们通常说所的数据分片。一个dataNode 标签就是一个独立的数据分片。

dataHost标签直接定义了具体的数据库实例、读写分离配置和心跳语句。其中有几个重要的属性：

balance属性
负载均衡类型，目前的取值有3 种：
balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost 上。
balance="1"，全部的readHost 与stand by writeHost 参与select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1 与M2 互为主备)，正常情况下，M2,S1,S2 都参与select 语句的负载均衡。
balance="2"，所有读操作都随机的在writeHost、readhost 上分发。
balance="3"，所有读请求随机的分发到wiriterHost 对应的readhost 执行，writerHost 不负担读压
力，注意balance=3 只在1.4 及其以后版本有，1.3 没有。

writeType 属性
负载均衡类型，目前的取值有3 种：
writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个
writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5 以后废弃不推荐。

配置server.xml，即主要配置连接Mycat的用户账号信息，即：

cat   server.xml

  1. .....
  2.          <user name="admin" defaultAccount="true">
  3.                 <property name="password">wx123123</property>
  4.                 <property name="schemas">mydb</property>
  5.                 <property name="defaultSchema">mydb</property>
  6.                 <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
  7.  
  8.                 <!-- 表级 DML 权限设置 -->
  9.                 <!--
  10.                 <privileges check="false">
  11.                         <schema name="mydb" dml="0110" >
  12.                                 <table name="tb01" dml="0000"></table>
  13.                                 <table name="tb02" dml="1111"></table>
  14.                         </schema>
  15.                 </privileges>
  16.                  -->
  17.         </user>
  18.         <user name="read">
  19.                 <property name="password">read123123</property>
  20.                 <property name="schemas">mydb</property>
  21.                 <property name="readOnly">true</property>
  22.                 <property name="defaultSchema">mydb</property>
  23.         </user>
  24. .....


即配置一个用户名为admin，密码为 wx123123 的用户，同时具有 mydb 模式下的读写权限，注意该模式即是在schema.xml配置文件定义的模式名一样。

启动Mycat，即：

./mycat start

 

参考链接：https://blog.peos.cn/2018/01/16/mycat-mysql.html

参考链接：https://yangxx.net/?p=3979#mysql

标签：

没有评论

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注