2.0 Mycat实现MySQL的分库分表、读写分离、主从复制

短命女 2022-03-11 02:44 648阅读 0赞

Mycat及MySQL实例部署情况：

Mycat（版本：5.6.29）：

IP：192.168.227.6，Port：3310/3311

MySQL（版本：5.6.29） ：注意MySQL版本最好5,6以上,刚开始yum安装默认版本5.1.73版本总报错

userHost1，IP：192.168.227.3，Port：3306

userHost2，IP：192.168.227.4，Port：3306

userHost3，IP：192.168.227.5，Port：3306

架构图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0MTg1MzA_size_16_color_FFFFFF_t_70][]

1，MySQL主从复制配置

主IP：192.168.227.3

从IP：192.168.227.5

首先：修改mysql的配置文件，使其支持二进制日志功能。

打开主服务器的mysql配置文件：my.conf

vim /etc/my.cnf 加上：

server-id = 3  //为服务器设置一个独一无二的id便于区分，这里使用ip地址的最后一位充当server-id

log-bin=mysql-bin //将mysql二进制日志取名为mysql-bin

binlog\_format=mixed //二进制日志的格式，有三种：statement/row/mixed,具体分别不多做解释，这里使用mixed

replicate-do-db = db\_store //需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

replicate-ignore-db = mysql //不需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

replicate-ignore-db = information\_schema

保存重启数据库

同样进入从数据库修改

server-id = 5

log-bin=mysql-bin

binlog\_format=mixed

replicate-do-db = db\_store

replicate-ignore-db = mysql

replicate-ignore-db = information\_schema

截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0MTg1MzA_size_16_color_FFFFFF_t_70 1][]

其次：在主服务器上为从服务器分配一个账号，就像一把钥匙，从服务器拿着这个钥匙，才能到主服务器上来共享主服务器的日志文件。

进入主服务器的mysql界面，

命令： \# mysql -u root -p password     //我这里mysql账号是root，密码是password

在mysql操作界面下，输入下面一行命令：

GRANT replication slave ON \*.\* TO 'slave'@'%' IDENTIFIED BY 'password';

![20190307090454998.png][]

查看主服务器BIN日志的信息（执行完之后记录下这两值，然后在配置完从服务器之前不要对主服务器进行任何操作，因为每次操作数据库时这两值会发生改变）.

![20190307090503656.png][]

再次：设置从服务器

进入从服务器

关闭slave（如果你以前配置过主从的话，一定要先关闭）

命令：stop slave;

开始配置：

输入下面代码即可：

![20190307090529991.png][]

CHANGE MASTER TO

MASTER\_HOST="192.168.227.3",

MASTER\_USER="root",

MASTER\_PASSWORD="password",

MASTER\_LOG\_FILE="mysql-bin.000001",

MASTER\_LOG\_POS=311;

注意：MASTER\_LOG\_FILE和MASTER\_LOG\_POS值看主服务器日志信息

从服务器配置完成，启动从服务器：

命令： start slave;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0MTg1MzA_size_16_color_FFFFFF_t_70 2][]

2，配置Mycat

server.xml：

<!DOCTYPE mycat:server SYSTEM "server.dtd">  
<mycat:server xmlns:mycat="http://io.mycat/">  
    <system>  
        <property name="useSqlStat">1</property>  
        <property name="useGlobleTableCheck">0</property>  
        <property name="defaultSqlParser">druidparser</property>  
        <property name="sequnceHandlerType">2</property>  
        <property name="processorBufferPoolType">0</property>  
        <property name="serverPort">3310</property>        
        <property name="managerPort">3311</property>        
        <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">389m</property>  
    </system>  
    <user name="root">  
        <property name="password">123456</property>  
         
<property name="schemas">db\_store,db\_user</property>  
        <property name="readOnly">false</property>  
    </user>  
</mycat:server>

schema.xml：

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

<schema name="db\_user" checkSQLschema="false" sqlMaxLimit="100">   
        <table name="data\_dictionary" type="global" dataNode="db\_user\_dataNode1,db\_user\_dataNode2" primaryKey="dataDictionaryID"/>  
        <table name="users" dataNode="db\_user\_dataNode$1-2"  rule="mod-userID-long" primaryKey="userID">        
            <childTable name="user\_address"  joinKey="userID" parentKey="userID" primaryKey="addressID"/>  
        </table>  
    </schema>  
    <schema name="db\_store" checkSQLschema="false" sqlMaxLimit="100">  
        <table name="store" dataNode="db\_store\_dataNode" primaryKey="storeID"/>  
        <table name="employee" dataNode="db\_store\_dataNode" primaryKey="employeeID"/>  
    </schema>

<dataNode name="db\_user\_dataNode1" dataHost="db\_userHOST1" database="db\_user" />  
    <dataNode name="db\_user\_dataNode2" dataHost="db\_userHOST2" database="db\_user" />  
      
       
     <dataNode name="db\_store\_dataNode" dataHost="db\_storeHOST" database="db\_store" />

<dataHost name="db\_userHOST1" maxCon="1000" minCon="10" balance="0"  
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  
        <heartbeat>select user()</heartbeat>  
        <writeHost host="userHost1" url="192.168.227.3:3306" user="root"  password="password">  
        </writeHost>  
    </dataHost>  
      
    <dataHost name="db\_userHOST2" maxCon="1000" minCon="10" balance="0"  
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  
        <heartbeat>select user()</heartbeat>  
          
        <writeHost host="userHost2" url="192.168.227.4:3306" user="root"  password="password">  
        </writeHost>  
    </dataHost>

<dataHost name="db\_storeHOST" maxCon="1000" minCon="10" balance="1"  
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  
        <heartbeat>select user()</heartbeat>  
          
        <writeHost host="hostM1" url="192.168.227.3:3306" user="root"  password="password">  
              
            <readHost host="hostS1" url="192.168.227.5:3306" user="root" password="password" />      
        </writeHost>          
    </dataHost>

</mycat:schema>

修改rule.xml中下列配置项：

--------------------

db\_store建表语句：

DROP TABLE IF EXISTS \`employee\`;

CREATE TABLE \`employee\` (

\`employeeID\` int(11) NOT NULL,

\`userName\` varchar(16) COLLATE utf8\_bin DEFAULT NULL,

\`phoneNum\` varchar(32) COLLATE utf8\_bin DEFAULT NULL,

\`age\` int(11) DEFAULT NULL,

\`createTime\` datetime DEFAULT NULL,

\`lastUpdate\` datetime DEFAULT NULL,

PRIMARY KEY (\`employeeID\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin;

\-- ----------------------------

\-- Table structure for store

\-- ----------------------------

DROP TABLE IF EXISTS \`store\`;

CREATE TABLE \`store\` (

\`storeID\` int(11) NOT NULL,

\`storeName\` varchar(32) COLLATE utf8\_bin DEFAULT NULL,

\`storeAddress\` varchar(256) COLLATE utf8\_bin DEFAULT NULL,

\`createTime\` datetime DEFAULT NULL,

\`lastUpdate\` datetime DEFAULT NULL,

PRIMARY KEY (\`storeID\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0MTg1MzA_size_16_color_FFFFFF_t_70]: /images/20220311/af97bf3fadcd46cf8874b622ab753079.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0MTg1MzA_size_16_color_FFFFFF_t_70 1]: /images/20220311/ebeeda576ae147f9873983e95fc8e37b.png
[20190307090454998.png]: /images/20220311/c00b58e047ff498f84b0f23c36d0b860.png
[20190307090503656.png]: /images/20220311/798bf7a479b74569aa1fb9050ac84df1.png
[20190307090529991.png]: /images/20220311/4740fb64237a4e96baffb906978984e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0MTg1MzA_size_16_color_FFFFFF_t_70 2]: /images/20220311/5d159ecca41b4d9ab4b864d6d8c3cce3.png