Mysql 配置mycat主从读写分离

测试环境：

mycat服务器：192.168.1.2

mysql主服务器：192.168.1.3

mysql主服务器：192.168.1.4

 

配置主从

mysql主服务器（192.168.1.3）配置

配置mysql配置

vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加以下配置

#mysql服务器id，唯一的

server-id = 1

#binlog位置

log-bin=/var/lib/mysql/mysql-binlog

#binlog名字格式

log-bin-index=master-bin.index

binlog_format=mixed

#开启binlog同步

sync_binlog =1

#主从同步数据库

binlog-do-db=test

#主从不同步数据库

binlog-ignore-db=mysql

binlog_cache_size = 4M

max_binlog_cache_size = 1G

max_binlog_size =1G

expire_logs_days = 7

重启mysql服务

Service mysql restart

 

创建mysql从机账号

mysql -uroot -p

mysql>create user salve;

mysql> GRANT REPLICATION SLAVE ON *.* TO 'salve'@'192.168.1.4' IDENTIFIED BY 'test';

 

查看 MASTER

mysql> SHOW MASTER STATUS;

+---------------------+------------+--------------+------------------+-----------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+------------+--------------+------------------+-----------------------------------------------+

| mysql-binlog.000013 | 1020192213 | test | mysql | f914a04f-5d85-11e8-bed9-005056a3631f:1-795125 |

+---------------------+------------+--------------+------------------+-----------------------------------------------+

1 row in set (0.46 sec)

mysql从机服务器（192.168.1.4）

配置mysql

vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加或更改以下配置

server-id = 2
#id和主服务器

#log-bin=/var/lib/mysql/mysql-binlo

确保binlog关闭

重启mysql服务

servier mysql restart

 

配置从机mysql账号

mysql -uroot -p

mysql>change master to master_host='10.200.62.212', master_user='salve', master_password='test',master_log_file='ysql-binlog.000004',master_log_pos=154;

 

启动savle

mysql>stopslave;

mysql>reset slave;

mysql>start slave;

 

查看salve状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.2

Master_User: savle

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-binlog.000013

Read_Master_Log_Pos: 1025991450

Relay_Log_File: test-relay-bin.000036

Relay_Log_Pos: 1025991669

Relay_Master_Log_File: mysql-binlog.000013

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1025991450

Relay_Log_Space: 1025991975

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: f914a04f-5d85-11e8-bed9-005056a3631f

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: f914a04f-5d85-11e8-bed9-005056a3631f:1-795490

Executed_Gtid_Set: f914a04f-5d85-11e8-bed9-005056a3631f:1-795490

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

确认 Slave_IO_Running: Yes和Slave_SQL_Running: Yes即完成 主从服务器配置完成，如果有no，要导出主库数据后，导入到从库在重新开启主从。

 

配置mycat服务器(192.168.1.2)

安装java

#add-apt-repository ppa:webupd8team/java

#apt-get update

#apt-get install oracle-java8-installer

测试java

root@ubuntu:/usr/local/mycat# java -version

java version "1.8.0_181"

Java(TM) SE Runtime Environment (build 1.8.0_181-b13)

Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

 

配置limits.conf

vim /etc/security/limits.conf

添加以下内容

* soft nofile 65000

* hard nofile 65000

 

配置profile

vim /etc/profile

添加以下内容

export PATH=${JAVA_HOME}/bin:${MYCAT_HOME}/lib:$PATH

export MYCAT_HOME=/usr/local/mycat/

 

mycat安装

下载mycat

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

mv mycat/ /usr/local/

 

配置mycat

配置server.conf

vim /usr/local/mycat/conf/server.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">

<mycat:server xmlns:mycat="http://io.mycat/">

<system>

<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->

<property name="useGlobleTableCheck">0</property>

<property name="sequnceHandlerType">2</property>

<property name="processors">4</property>

<property name="processorExecutor">16</property>

<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->

<property name="processorBufferPoolType">0</property>

<property name="serverPort">3306</property>

<!--默认是65535 64K 用于sql解析时最大文本长度 -->

<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->

<property name="handleDistributedTransactions">0</property>

<property name="useOffHeapForMerge">1</property>

<property name="memoryPageSize">1m</property>

<property name="spillsFileBufferSize">1k</property>

<property name="useStreamOutput">0</property>

<property name="systemReserveMemorySize">384m</property>

<!--是否采用zookeeper协调切换 -->

<property name="useZKSwitch">true</property>

</system>

<user name="root"><!-- mycat连接账号、密码、数据库 -->

<property name="password">test</property>

<property name="schemas">test</property>

</user>

<user name="test">

<property name="password">test</property>

<property name="schemas">test</property>

</user>

</mycat:server>

 

配置schema.xml

vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

</schema>

<!-- 配置数据库，namenode的名字， dataHost对应dataHost 配置的name，database对应schema name-->

<dataNode name="dn1" dataHost="test" database="test1" />

<dataHost name="test1" maxCon="5000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<!-- 写入服务器 -->

<writeHost host="hostM1" url="192.168.1.4:3306" user="root"

password="MIS@2018">

<!-- 读取服务器 -->

<readHost host="hostS2" url="192.168.1.3:3306" user="root" password="test" />

</writeHost>

</dataHost>

</mycat:schema>

 

启动mycat

cd /usr/local/mycat/bin/

./mycat start