数据库负载均衡和双机备份 - 管理猿 2019 年 08 月 05 日 |访问: 2,079 次

数据库类型:mysql
数据库版本:mysql Ver 14.14 Distrib 5.1.73
负载均衡器:Mycat
负载均衡器版本:Mycat-server-1.6.6.1
数据库主服务器:192.168.100.66
数据库备服务器:192.168.100.69
Mycat服务器:192.168.100.62
实现方案:两台mysql数据库通过slave实现数据同步功能。通过mycat实现负载
Mycat简介:

    MYCAT基于阿里开源的Cobar产品而研发,Cobar的稳定性、可靠性、优秀的架构和性能以及众多成熟的使用案例使得MYCAT一开始就拥有一个很好的起点,站在巨人的肩膀上,我们能看到更远。业界优秀的开源项目和创新思路被广泛融入到MYCAT的基因中,使得MYCAT在很多方面都领先于目前其他一些同类的开源项目,甚至超越某些商业产品。 

    MYCAT对cobar的代码进行了彻底的重构,使用NIO重构了网络模块,并且优化了Buffer内核,增强了聚合,Join等基本特性,同时兼容绝大多数数据库成为通用的数据库中间件。 

   MYCAT背后有一支强大的技术团队,其参与者都是5年以上资深软件工程师、架构师、DBA等,优秀的技术团队保证了MYCAT的产品质量。 

   MYCAT并不依托于任何一个商业公司,因此不像某些开源项目,将一些重要的特性封闭在其商业产品中,使得开源项目成了一个摆设。

Mysql原理:

  用户发送SQL语句到mycat监听的进程中,通过拦截用户的SQL语句,分析语句再次通过路由分库,分表读写等。本次只用到代理功能。

一.数据库主机192.168.100.66的配置
首先mysql的my.cnf,与另外一台的server-id和自增偏移不能相同

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=101
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1000
auto_increment_increment=2  # 自增因子(每次加2)
auto_increment_offset=1     # 自增偏移(从1开始),单数
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

然后主数据的slave配置,创建一个能供备份数据库读写的远程权限,然后锁表准备同步,根据show master status的状态修改同步语句的bin和log_pos

CREATE USER 'master1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE  ON *.* TO 'master1'@'%' IDENTIFIED BY '123456';
flush tables with read lock;
show master status;
change master to master_host='192.168.100.69',master_user='master2',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=854;

二:备份数据库192.168.100.69的配置
首先还是my.cnf配置[vi /etc/my.cnf],server-id和自增依然不能相同

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=103
log-bin = mysql-bin
auto_increment_increment=2
auto_increment_offset=2
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

写入备份数据库的SLave相关配置,创建一个提供给足数据库的slave账户,然后锁表『停止写入』。根据show master statusG;的状态修改最后一条的bin和log_pas。IP地址也需要修改为对应的地址。

CREATE USER 'master2'@'%' IDENTIFIED BY '123456'; 
GRANT REPLICATION SLAVE  ON *.* TO 'master2'@'%' IDENTIFIED BY '123456';
flush tables with read lock;
show master status\G;
change master to master_host='192.168.100.66',master_user='master1',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=369;

三.开启同步,成功后解除锁表
分别在两台机器上的mysql>下执行同步语句,并查看同步状态,同步成功后两条“Slave_IO_Running: Yes”和“Slave_SQL_Running: Yes”,如果不成功检查防火墙是否停用{systemctl status firewalld.service或者service iptables status},远程访问能否成功mysql -umaster2 -p123456 -h192.168.100.66

start slave;
show slave status\G;
unlock tables;

四.mycat的配置
首先下载安装mycat软件(文章末尾有提供),通过FTP上传到目标主机192.168.100.62

 cd /root
 tar -zxvf  Mycat-server-1.6.6.1-test-20180709095126-linux.tar.gz
 cp -r mycat/ /usr/local/

添加mycat的环境变量vi /etc/profile

export MYCAT_HOME=/usr/local/mycat

使变量生效,设置相关权限

source /etc/profile
groupadd mycat
useradd -g mycat mycat
passwd mycat
chown -R mycat.mycat /usr/local/mycat

重点来了,编辑mycat的配置文件,根据我的安装目录,配置文件在/usr/local/conf/
首先编辑schema.xml配置『记得创建下prox的账号,上文没建』

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="databaseserver" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" >
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="portalserver" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100" maxRetryCount="4">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.100.66:3306" user="prox" password="Axattribute.1002"/>
                <writeHost host="hostM2" url="192.168.100.69:3306" user="prox" password="Axattribute.1002"/>
        </dataHost>
</mycat:schema>

修改另外一条配置server.xml(都在conf下面)。最下面是配置账号密码和数据库名称的地方

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
        - you may not use this file except in compliance with the License. - You 
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
        - - Unless required by applicable law or agreed to in writing, software - 
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
        License for the specific language governing permissions and - limitations 
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0,设置为1则需要指定默认账户-->
        <property name="useHandshakeV10">1</property>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

                <property name="sequnceHandlerType">2</property>
        <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!-- 
        <property name="processors">1</property> 
        <property name="processorExecutor">32</property> 
         -->
        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
                <property name="processorBufferPoolType">0</property>
                <!--默认是65535 64K 用于sql解析时最大文本长度 -->
                <!--<property name="maxStringLiteralLength">65535</property>-->
                <!--<property name="sequnceHandlerType">0</property>-->
                <!--<property name="backSocketNoDelay">1</property>-->
                <!--<property name="frontSocketNoDelay">1</property>-->
                <!--<property name="processorExecutor">16</property>-->
                <!--
                        <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
                        <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
                <!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
                <property name="handleDistributedTransactions">0</property>
                
                        <!--
                        off heap for merge/order/group/limit      1开启   0关闭
                -->
                <property name="useOffHeapForMerge">1</property>

                <!--
                        单位为m
                -->
        <property name="memoryPageSize">64k</property>

                <!--
                        单位为k
                -->
                <property name="spillsFileBufferSize">1k</property>

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

                <!--
                        单位为m
                -->
                <property name="systemReserveMemorySize">384m</property>


                <!--是否采用zookeeper协调切换  -->
                <property name="useZKSwitch">false</property>

                <!-- XA Recovery Log日志路径 -->
                <!--<property name="XARecoveryLogBaseDir">./</property>-->

                <!-- XA Recovery Log日志名称 -->
                <!--<property name="XARecoveryLogBaseName">tmlog</property>-->
                <!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
                <property name="strictTxIsolation">false</property>
                
                <property name="useZKSwitch">true</property>
                
        </system>
        
        <!-- 全局SQL防火墙设置 -->
        <!--白名单可以使用通配符%或着*-->
        <!--例如<host host="127.0.0.*" user="root"/>-->
        <!--例如<host host="127.0.*" user="root"/>-->
        <!--例如<host host="127.*" user="root"/>-->
        <!--例如<host host="1*7.*" user="root"/>-->
        <!--这些配置情况下对于127.0.0.1都能以root账户登录-->
        <!--
        <firewall>
           <whitehost>
              <host host="1*7.0.0.*" user="root"/>
           </whitehost>
       <blacklist check="false">
       </blacklist>
        </firewall>
        -->

        <user name="prox" defaultAccount="true">
                <property name="password">123</property>
                <property name="schemas">databaseserver</property>
                
                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="portalserver" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

</mycat:server>

五.开启mycat并设置开机启动

/usr/local/mycat/bin/mycat start

启动后默认监听的端口是8066,然后尝试登录.登录成功配置即完成
QQ截图20190805101155.png
设置开机启动
vi /etc/rc.local
/usr/local/mycat/bin/mycat start

最后mycat安装文件下载:
Mycat-server-1.6.6.1-test-20180709095126-linux.tar.gz

标签:none

添加新评论


评论:只有地板了

  1. 主机版本:centos6.10
    设置开机自启和centos7有所区别