Mycat 读写分离的构建

  • A+
所属分类:mysql

 

1. Mysql安装和配置

1.1    下载mysql源

在MySQL官网中下载YUM源rpm安装包:http://dev.mysql.com/downloads/repo/yum/

点击Download出现下面的页面

mysql57-community-release-el6-11.noarch.rpm

我们 直接下载就ok了

rpm -ivh wget http://dev.mysql.com/get/ mysql57-community-release-el6-11.noarch.rpm

 

安装成功后

可以修改vim /etc/yum.repos.d/mysql-community.repo源,改变默认安装的mysql版本。比如要安装5.6版本,将5.7源的enabled=1改成enabled=0。然后再将5.6源的enabled=0改成enabled=1即可

1.2    安装mysql

yum install -y mysql-community-server

 

安装需要epel源

mysql安装完成之后,在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码,然后登录mysql进行修改

 

grep 'temporary password' /var/log/mysqld.log

 

修改密码

mysql –p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY ' MyNewPass4!';

或者

mysql> set password for 'root'@'localhost'=password('MyNewPass4!');

 

注意:mysql5.7默认安装了密码安全检查插件(validate_password),默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号,并且长度不能少于8位。否则会提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements错误,如下图所示:

配置默认编码为utf8

在/etc/my.cnf中添加

[mysqld]

character_set_server=utf8

init_connect='SET NAMES utf8'

 

1.3搭建mysql主从
1.3.1在mysql主库的更改 /etc/my.cnf

 

[mysqld]

 

datadir=/db/data

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

character_set_server=utf8

init_connect='SET NAMES utf8'

default-storage-engine = MyISAM

default-tmp-storage-engine = MYISAM

loose-skip-innodb

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 70M

table_open_cache = 512

sort_buffer_size = 2M

net_buffer_length = 16K

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 12

query_cache_size = 32M

innodb_open_files = 500

innodb_buffer_pool_size = 512M

innodb_log_buffer_size = 16M

max_connections = 10000

sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

concurrent_insert = 2

max_write_lock_count = 1

 

log-bin=mysql-bin

expire_logs_days = 30

server-id=1

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=mysql

binlog-ignore-db=sys

 

 

[mysqldump]

 

quick

 

 

 

[mysql]

 

no-auto-rehash

 

 

[myisamchk]

 

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

 

 

[mysqlhotcopy]

 

interactive-timeout

 

 

mkdir –p /db/data && chown mysql:mysql  /db -R

touch  /var/log/slow.log  chown mysql:mysql  /var/log/slow.log

重启 mysql

/etc/init.d/mysqld restart

 

 

1.3.2在mysql备库的更改 /etc/my.cnf

 

 

[mysqld]

 

datadir=/db/data

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

 

slow_query_log = ON

slow_query_log_file=/var/log/slow.log

long_query_time = 2

pid-file=/var/run/mysqld/mysqld.pid

 

 

character_set_server=utf8

init_connect='SET NAMES utf8'

default-storage-engine = MyISAM

default-tmp-storage-engine = MYISAM

loose-skip-innodb

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 70M

table_open_cache = 512

sort_buffer_size = 2M

net_buffer_length = 16K

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 12

query_cache_size = 32M

innodb_open_files = 500

innodb_buffer_pool_size = 512M

innodb_log_buffer_size = 16M

max_connections = 10000

sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

concurrent_insert = 2

low_priority_updates = 1

 

server-id=2

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-ignore-db=mysql

replicate-ignore-db=sys

 

 

[mysqldump]

 

quick

 

 

[mysql]

 

no-auto-rehash

 

 

[myisamchk]

 

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

 

 

[mysqlhotcopy]

 

interactive-timeout

mkdir –p /db/data && chown mysql:mysql  /db -R

touch  /var/log/slow.log  chown mysql:mysql  /var/log/slow.log

重启mysql

/etc/init.d/mysql restart

 

1.3.3 授权

在主库授权用于备库连接主

mysql –p

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.50.12'IDENTIFIED BY 'MyNewPass4!' WITH GRANT OPTION;

mysql> flush privileges;

 

1.3.4 主从的建立

查看主的状态

mysql –p

mysql> show master status\G

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

File: mysql-bin.000001

Position: 772

Binlog_Do_DB:

Binlog_Ignore_DB: information_schema,performance_schema,mysql,sys

Executed_Gtid_Set:

1 row in set (0.00 sec)

 

在备库上面执行

mysql –p

mysql> stop slave;

mysql>change master to master_host='172.31.50.11',master_user='root',master_password='MyNewPass4!' ,master_log_file='mysql-bin.000001',master_log_pos=772;

mysql> start slave;

mysql>show slave status\G

当这两个为yes时搭建成功

master_log_file 指定主的bin_log日志开始

master_log_pos 指定开始的位置 开始为0

 

mysql 的 一些 命令

mysql>show full PROCESSLIST; 查看当前运行的进程

mysql>kill id; 杀死当前id的进程

mysql>show variables like 'slow_query%'; 查看慢查询的状态

mysql>select sleep(5); 测试慢查询

mysql> show engines;  查看mysql现在已提供什么存储引擎

mysql> show variables like '%storage_engine%'; 查看mysql当前默认的存储引擎

mysql> CREATE DATABASE IF NOT EXISTS dukepay DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 用UTF-8的格式创建数据库

 

2.Mycat搭建和配置

2.1 下载mycat和jdk

下载Mycat

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

 

下载jdk

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

 

需要先 同意才能下载

 

2.2 安装 jdk和mycat

安装jdk

rpm –ivh jdk-8u162-linux-x64.rpm

 

java –version 查看版本 看是否安装完成

 

安装mycat

tar –xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz –C /usr/local/

 

2.3 配置mycat

cd /usr/local/mycat

cd conf

2.3.1更改 schema.xml 配置文件如下

<?xml version="1.0"?>

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

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

 

<schema name="dukepay" checkSQLschema="false" dataNode="dn1"></schema>

<schema name="dukepay2" checkSQLschema="false" dataNode="dn2"></schema>

 

<dataNode name="dn1" dataHost="localhost1" database="dukepay" />

<dataNode name="dn2" dataHost="localhost2" database="dukepay" />

 

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="D-DB-M" url="172.31.50.11:3306" user="root" password="IGJrM4%Yp">

<readHost host="D-DB-S" url="172.31.50.12:3306" user="root" password="IGJrM4%Yp" />

</writeHost>

</dataHost>

<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="D-DB-M" url="172.31.50.11:3306" user="root" password="IGJrM4%Yp">

<readHost host="D-DB-S1" url="172.31.26.29:3306" user="root" password="IGJrM4%Yp" />

</writeHost>

</dataHost>

</mycat:schema>

 

如图:

 

2.3.2修改 server.xml

打开压缩

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

修改sql析时最大文本长度

<property name="maxStringLiteralLength">73400320</property>

更改监听端口

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

 

如图:

 

更改下面的值

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

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

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

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

 

如图:

 

 

建立用户:

 

2.3.3添加hosts不然会报错

 

 

2.3.4在mysql 主和备上授权

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.26.35'IDENTIFIED BY 'MyNewPass4!' WITH GRANT OPTION;

mysql> flush privileges;

 

2.3.5启动mycat

/usr/local/mycat/bin/mycat start

查看状态

/usr/local/mycat/bin/mycat status

如果启动不了查看/usr/local/mycat/logs/wrapper.log 日志

 

2.3.6 测试mycat

mysql -h 172.31.26.35(mycat的IP) –u dukepay –p MyNewPass4!

 

验证读写分离可以在主库和备库分别建立test1和test2 进行测试 在连接mycat 看创建表是在那个库中 , 是否能看到 当然 需要先关掉主备同步

2.4 mycat 详细配置查看官网

http://www.mycat.io/document/Mycat_V1.6.0.pdf

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
ssh

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: