mysql主从复制同步+maxscale读写分离+mycat分片存储

mysql主从同步

mysql主从同步概述与配置

实现数据自动同步的服务结构

主服务器master:接受客户端访问连接

从服务器slave:自动同步主服务器数据

主从同步原理

master启用binlog日志

slave_io:复制master主机binlog日志文件里的sql命令到本机的relay-log中继日记

slave_sql:执行本机relay-log文件里的sql语句，实现master数据一致。

基本应用：

一主一从     单点故障

一主多从      主挂，多台从不同步

主->从->从   链式复制

互为主从       主<->主

一主一从： 两台数据库一样才能配  

主服务器启用binlog日志，授权用户，查看binlog日志信息

vim /etc/my.cnf   找到[mysql]在下面插入

log_bin=master   //启用binlog日志在/var/lib/mysql下

server_id=51       //启用服务器id主机位

:wq

systemctl  restart  mysqld
主数据库：

grant  replication slave on *.*  to repluser@"%"   identified by  "密码“；

show  master  status;   查看启用日志信息

从服务器配置:

指定server_id（编辑my.cnf文件）

vim  /etc/my.cnf  在[mysql]下插入

server_id=主机位

：wq

systemctl restart   mysqld

指定主服务器信息

show  slave  status;  查看是不是从服务器

change master  to  master_host="192.168.4.51",

master_user="repluser",master_password="123",

master_log_file="master51.000001"(主服务器show master status),

master_log_pos=441(主服务器show master status);

show  slave  status \G;

start  slave;          开启slave_io_running,slave_sql_running

存放在数据库目录下：

删除文件，重启数据库服务，可把主机恢复为独立数据服务器

在从服务器上/var/lib/mysql/文件名

master.info  主库信息

relay-log-info     中继日志信息

主机名-relay-bin-xxxxxxx     中继日志var/lib/mysql/

主机名-relay-bin.index    索引文件

配置mysql一主多从：

在已有主从中加从：主安装innobackupex拷贝数据库给新从服务器

主服务器：innobackupex     --user root  --password  123  --slave-info  /alldb  --no-timestampnong

scp  -r  /alldb     从服务器ip:/opt/

从服务器：

systemctl   stop  mysqld

rm -rf    /var/lib/mysql/*

innobackupex  --apply-log  /opt/alldb

innobackupex  --copy-back  /opt/alldb

chown -R  mysql:mysql       /var/lib/mysql

systemctl  start  mysqld

cat  /opt/alldb/xtrabackup_binlog_info      看日志文件和便宜量给配置从服务器指定

配置从服务器

vim /etc/my.cnf   找到[mysql]在下面插入

server_id=52       //启用服务器id主机位

:wq

systemctl  restart  mysqld

change master  to  master_host="192.168.4.51",

master_user="repluser",master_password="123",

master_log_file="master51.000001",

master_log_pos=441;

start slave;

show master  status;

主从从结构:给从配从链式

53主机服务器：启动binlog日志，用户授权，查看日志信息

vim    /etc/my.cnf     在[mysql]下插入

log-bin=日志名

server_id=id号

：wq

systemctl  restart  mysqld

show  user,host from  mysql.user;

grant  replication  slave on  *.*  to  用户名@c”从库ip地址“ identified by  ”密码“；

systemctl  restart    mysqld

show grants  for  repluser@"%";

配置   即是从服务器又是主服务器

5.4主机从服务主服务器  修改配置文件  用户授权 指定主库信息 启动slave进程

vim    /etc/mysql.cnf    在[mysql]  下插入

server_id=id号

log_bin=日志名

log_slave_updates    //允许级联复制

：wq

systemctl   restart mysqld

grant  replication    slave on *.*   to 用户名@”%“ identified by "密码”； //用户授权

change  master  to master_host="192.168.4..53",master_user="repluser",master_password="密码“，master_log_file="master53.00001"(查看show master status)，master_log_pos=154;

start slave;

show  slave  status   \G;  查看是否是从服务器

做主服务器配置：

grant  replication   slave on *.*   jim@"%" identified  by  "密码”；

show  master  status; 查看日志文件

5.5从服务器：

修改vim /etc/my.cnf     在[mysql]插入

server_id=数字

:wq

change  master  to  master_host=54ip ,master_user="jim",

master_password="123",master_log_file="master54.00001",

master_log_pos=334;

start  slave;

show  slave  status;

在主库授权访问数据的连接用户

客户端连接主库，执行与权限匹配的sql操作

授权用户连接第1台从库,可以看到主库的数据

授权用户连接第2台从库，可看到主库的数据f

grant  all on “库” to  用户@“客户端” identified  by “密码”；

mysql   -u主服务ip地址 -p 密码   //客户端连接主服务器

复制模式：

介绍：

异步复制:主服务器执行完一次事务后，立即将结果返回给客户端，不关心从服务器是否已经同步数据。客户端体验好 默认异步复制

半同步复制：主服务器在执行依次事务后，等待至少一台从服务器同步数据完成，才将结果返回给客户端。客户端体验下降

临时命令复制主服务器：install  plugin    rpl_semi_sync_master  SONAME "semisync master.so";

永久设置：vim /etc/my.cnf   ,在[mysqld]下方添加

plugin-load="rpl_semi_sync_master=semisync_master.so"

临时命令从服务器：install  plugin  rpl_semi_sync_slave SONAME “semisync_slave.so"  

永久设置：vim /etc/my.cnf   ,在[mysqld]下方添加

plugin-load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=1

即是主又是从服务器两个都配；

永久：vim /etc/my.cnf  在[mysql]下

plugin-load="rpl_semi_sync_master=semisync_master.so；rpl_semi_sync_slave=semisync_slave.so"

rpl_semi_sync_slave_enabled=1

rpl_semi_sync_master_enabled=1

systemctl  restart mysqld

show variables like "%semi%";

查看模块是否加载：

select  plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";

启用模块：

set  global  rpl_semi_sync_master_enabled=1;   //启用master模块

set  global  rpl_semi_sync_slave_enabled=1;  //启用slave模块

show  variables  like  "rpl_semi_sync_%_enabled";  //查看

数据读写分离：

把客户端查询数据的请求和存储数据请求分给不同数据服务器

添加一个mysql代理，接收客户端访问，为客户提供访问数据接口

由mysql代理面向客户端提供服务

收到sql写请求时，交给master服务器处理

收到sql读请求，交给slave服务器处理 mycat  mysql-proxy maxscale

mysql中间键

一主一从读写分离：

56主机主服务器：

58主机从服务器

57主机读写分离：

MaxScale  代理软件

由mysql的兄弟公司maradb开发

下载地址https://downloads.mariadb.com/files/MaxScale

安装软件：

rpm  -ivh  maxscale-2.1.2-1.rhel.7.x86_64.rpm

ls  /etc/maxscale.cnf

max

ls  /var/log/maxscale/

vim /etc/maxscale.cnf      在[maxscale]

threads=auto    //定义线程个数

[server1]            //定义数据库服务器

type=server        

address=192.168.4.56

port=3306

protocol=mysqlbackend

[server2]

type=server

address=192.168.4.58

port=3306

protocol=mysqlbackend

[MySQL Monitor]   //定义监视的数据库服务器

servers=server1,server2

user=maxscalemon      //监视用户

password=123           //

#[Read-Only Service]     //注释只读

#type=service

#route=server1

#server=server1

#user=myuser

#passwd=mypwd

#router_options=slave

[Read-Write Service]    //定义读写分离服务

servers=server1,server2

user=maxscalerouter     //监视路由用户

password=123

[MaxAdmin Listener]    //定义管理服务端口

port=4016

：wq

配置数据库服务器

创建授权用户

再主服务器添加，从服务器会自动同步授权用户

//创建监控用户

grant  replication  slave，replication client  on *.*   to maxscalemon@"%"  identified  by  "密码”；

//创建路由用户

grant  select on mysql.*  to maxscalerouter@"%" identified by “密码”；//创建路由用户

启动服务

maxscale  -f  /etc/maxscale.cnf  //启动服务

netstat     -utnlp  | grep  maxscale  //查看端口

kill  -9  pid //停止服务

netstat   -utnlp  | grep  4406

netstat   -utntp  | grep  4016

netstat   -untlp  | grep maxscale

ps -C  maxscale   进程pid

ls /var/log/maxscale/   日志信息

测试读写分离：

57主机：maxadmin  -uadmin  -pmariadb   -P4016  //管理

list    servers

访问代理服务：

主服务器授权连接用户

grant  all on  testdb.*  to plj@"%“  identified  by "123";

客户端连接代理查看代理服务

mysql   -h192.168.4.57    -uplj      -p123      -P4006

insert  into  

select     查询数据
验证读写分离：

在从服务器上添加新纪录

客户端连接代理服务器

查看记录

写入记录

数据分片概述与mycat软件介绍

将存放在一台数据库服务器中的数据，按照特定方式进行拆分，

分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果

水分分割：

横向切分：

纵向切分：将单个数据库的多个表，按业务类型，分撒存储到不同的数据库

MyCAT 是基于java的分布式数据库系统中间件，为高并发环境分布式存储提供解决方案

适合数据大量写入的存储需求

支持mysql oracle sqlserver  mongodb等

提供数据读写分离服务

提供数据分片服务

基于阿里巴巴cobar进行研发的开源软件

mycat  提供十种分片规则

枚举法 sharding-by-intfile

固定分片  rule1

范围约定auto-sharding-long

求模法 mod-long

日期列分区法 sharding-by-date

通配取模 sharding-by-pattern

ascll  码求模通配 sharding-by-prefixpattern

编程指定  sharding-by-substring

字符串拆分hash解析   sharding-by-stringhash

一致性hash  sharding-by-murmur

工作过程

当mycat 收到一个sql命令时

解析sql命令涉及到的表

然后看对表的配置，如果有分片规则，则获取sql命令分片字段的值

并匹配分片函数，获取分片列表

然后将sql命令发往对应的数据库服务器去执行

最后收集和处理所有分片结果数据，并返回到客户端

规划角色与ip

主机名              角色               数据库                          ip地址

client                客户端                无                     192.168.4.50/24

mycat                分片                   无                     192.168.4.56/24

mysql53            数据库服务器     db1                   192.168.4.53/24

mysql54           数据库服务器       db2                 192.168.4.54/24

mysql55           数据库服务器       db3                 192.168.4.55/24

mycat主机服务配置：

安装jdk

系统自带的即可

yum  -y install  java-1.8.0-openjdk.x86_64

下载源码包

http://www.mycat.org.cn

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

mv  mycat   /usr/local/

ls  /usr/local/mycat/

bin     //mycat命令   catlet  //扩展功能  conf  //配置文件  lib //mycat使用的jar包

logs  //mycat启动日志和运行日志

wrapper.log   //mycat  服务启动日志

mycat.log   //记录sql脚本执行后的报错内容

vim     /usr/local/mycat/conf/server.xml    定义客户端的用户和密码

<user name="root">

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

<property name="schemas">TESTDB(显示在客户端的虚拟库）</property>

</user>

<user name="root">

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

<property name="schemas">TESTDB(显示在客户端的虚拟库）</property>

<property  name="readOnly">true</property>  //只读权限

</user>

:wq

schema.xml

<schema></schema>定义分片信息

<table></table> 定义表

name  //逻辑库名或逻辑表名

dataNode   指定数据库服务器库名信息

datahost    配置数据库ip地址

rule     指定使用的分片规则

type=global   数据部分片存储

sed  -i "56,77d"  schema.xml  删掉读写分离部分

server.xml ：定义客户端连接用户密码逻辑库名访问权限

schema.xml:T<table>定义逻辑表名，服务器台数，分片规则

配置数据库服务器并启动服务

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

<schema name="TESTDB"  ..>

<table/>

</schema>

<dataNode name="dn1"   dataHost="localhost1" database="db1">

<dataHost name="localhost1">

          <writeHost host="host1" url="数据库ip"   user="数据库授权用户" password="23>

配置数据库服务器

根据分片配置做相应的设置

创建存储数据库db1 db2 db3

create    database  db1;  //53主机

create   database  db2;  //54主机

create   database   db3 ； //55主机

添加授权用户

grant  all on  *.*   to  admin@"%" identified  by "密码”；  所有主机

56主机启用mycat

/usr/local/mycat/bin/mycat  start|stop

netstat  -untlp  | grep 8066

分片规则之枚举法sharding-by-intfile

分片字段值，必须在规则文件定义的值里选择

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

<tableRule name="sharding-by-intfile">     在schema.xml下rule

  <rule>
   <columns>sharding-id</columns>    分片字段i是id改名

    <algorithm>hash-int</algorithm>     算法

....

  <function name="hash-int"> 

     <property name=mapfile>partition-hash-int.txt</property>  需要vim   文件更改

:wq

vim ./partition-hash-int.txt

10000=0

10010=1

10020=2

:wq

/usr/local/mycat/bin/mycat stop

/usr/local/mycat/bin/mycat start 

查看使用枚举规则表 schema.xml   table.rule=""

分片规则对应的算法及分片字段名 cat  rule.xml

查看分片规则对应的配置文件  shardind_id

parttition-hash-int.txt

建表

字段id 

必要字段sharding_id

插入记录

求模法mon-long

根据字段值与设定的数字求模结果存储数据

schema.xml   rule.xml

建表

create   table  hostname (ID int  primary key auto_increment,

num  int  , title  char(15)   ,comment  varchar(150));

求模法ID必须赋值可以NULL占位

insert  into  hotnews(ID,num,title，comment)  values (NULL（自增）,22,"kk","ll");

添加新库新表

修改server.xml 文件

<property name="schemas">TESTDB,BBSDB</property>

修改schema.xml  在<mycat:schema...> 下 添加几行

<schema name="BBSDB"...> 可参考下面

<table  name="user"  dataNode"db1,db2,db3",rule="mod-long“></table>

</schema>

:wq

重启