mycat學(xué)習(xí)01-- mycat我?guī)闳腴T

請(qǐng)耐心讀完整篇文章，過程中出現(xiàn)的錯(cuò)誤點(diǎn)在文章結(jié)尾都有總結(jié)和解決辦法。

服務(wù)器架構(gòu)

安裝MySQL

創(chuàng)建mysql用戶

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

安裝MySQL

yum install -y libaio

cd /usr/local/src/

wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

tar -zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3306

cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3307

chown -R mysql:mysql /data/app/mysql-3306

chown -R mysql:mysql /data/app/mysql-3307

/data/app/mysql-3306/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data

/data/app/mysql-3307/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data

修改my.cnf

需要修改的參數(shù)： 

·        server-id：保證每個(gè)配置文件唯一

·        兩臺(tái)master的自增長(zhǎng)ID必須不同

linux-node2

master

cat > /data/app/mysql-3306/my.cnf<<EOF

[client]

port = 3306

socket = /data/app/mysql-3306/mysql.sock

[mysqld]

port = 3306

user = mysql

server-id = 1

bind-address = 0.0.0.0

basedir = /data/app/mysql-3306

datadir = /data/app/mysql-3306/data

socket = /data/app/mysql-3306/mysql.sock

pid-file = /data/app/mysql-3306/mysql.pid

log-error = /data/app/mysql-3306/mysqld.log

skip-name-resolve

log_bin = mysql-bin

log-slave-updates

auto-increment-increment = 2

auto-increment-offset = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3306/my.cnf

slave

cat > /data/app/mysql-3307/my.cnf<<EOF

[client]

port = 3307

socket = /data/app/mysql-3307/mysql.sock

[mysqld]

port = 3307

user = mysql

server-id = 11

bind-address = 0.0.0.0

basedir = /data/app/mysql-3307

datadir = /data/app/mysql-3307/data

socket = /data/app/mysql-3307/mysql.sock

pid-file = /data/app/mysql-3307/mysql.pid

log-error = /data/app/mysql-3307/mysqld.log

skip-name-resolve

log_bin = mysql-bin

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3307/my.cnf

linux-node3

master

cat > /data/app/mysql-3306/my.cnf<<EOF

[client]

port = 3306

socket = /data/app/mysql-3306/mysql.sock

[mysqld]

port = 3306

user = mysql

server-id = 2

bind-address = 0.0.0.0

basedir = /data/app/mysql-3306

datadir = /data/app/mysql-3306/data

socket = /data/app/mysql-3306/mysql.sock

pid-file = /data/app/mysql-3306/mysql.pid

log-error = /data/app/mysql-3306/mysqld.log

skip-name-resolve

log_bin = mysql-bin

log-slave-updates

auto-increment-increment = 2

auto-increment-offset = 2

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3306/my.cnf

slave

cat > /data/app/mysql-3307/my.cnf<<EOF

[client]

port = 3307

socket = /data/app/mysql-3307/mysql.sock

[mysqld]

port = 3307

user = mysql

server-id = 22

bind-address = 0.0.0.0

basedir = /data/app/mysql-3307

datadir = /data/app/mysql-3307/data

socket = /data/app/mysql-3307/mysql.sock

pid-file = /data/app/mysql-3307/mysql.pid

log-error = /data/app/mysql-3307/mysqld.log

skip-name-resolve

log_bin = mysql-bin

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3307/my.cnf

啟動(dòng)MySQL

啟動(dòng)服務(wù)

linux-node2和linux-node3都執(zhí)行如下命令

touch /data/app/mysql-3306/mysqld.log && chown mysql.mysql /data/app/mysql-3306/mysqld.log

sed -i 's#/usr/local/mysql#/data/app/mysql-3306#g' /data/app/mysql-3306/bin/mysqld_safe

/data/app/mysql-3306/bin/mysqld_safe --defaults-file=/data/app/mysql-3306/my.cnf --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data --user=mysql &

touch /data/app/mysql-3307/mysqld.log && chown mysql.mysql /data/app/mysql-3307/mysqld.log

sed -i 's#/usr/local/mysql#/data/app/mysql-3307#g' /data/app/mysql-3307/bin/mysqld_safe

/data/app/mysql-3307/bin/mysqld_safe --defaults-file=/data/app/mysql-3307/my.cnf --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data --user=mysql &

檢查端口

ss -lntup |egrep '3306|3307'

tcp    LISTEN     0      80                     *:3306                  *:*      users:(("mysqld",19973,22))

tcp    LISTEN     0      80                     *:3307                  *:*      users:(("mysqld",20537,22))

配置雙主

配置主從

linux-node2

master

cd /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';

Query OK, 0 rows affected (0.05 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.%';

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      613 |              |                  |                   |

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

1 row in set (0.00 sec)

slave

cd /data/app/mysql-3307/

./bin/mysql -uroot -p -S mysql.sock -P 3307

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',

    ->                  MASTER_PORT=3306,

    ->                  MASTER_USER='repl',

    ->                  MASTER_PASSWORD='mysql',

    ->                  MASTER_LOG_FILE='mysql-bin.000001',

    ->                  MASTER_LOG_POS=613;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.12

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 613

               Relay_Log_File: linux-node2-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

linux-node3

master

cd /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';

Query OK, 0 rows affected (0.05 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.%';

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      613 |              |                  |                   |

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

1 row in set (0.00 sec)

slave

cd /data/app/mysql-3307/

./bin/mysql -uroot -p -S mysql.sock -P 3307

mysql>

CHANGE MASTER TO MASTER_HOST='192.168.56.13',

                 MASTER_PORT=3306,

                 MASTER_USER='repl',

                 MASTER_PASSWORD='mysql',

                 MASTER_LOG_FILE='mysql-bin.000001',

                 MASTER_LOG_POS=613;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.13

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 613

               Relay_Log_File: linux-node2-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

配置雙主

master的binlog位置

linux-node2 master端

d /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      613 |              |                  |                   |

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

1 row in set (0.00 sec)

linux-node3 master端

cd /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      613 |              |                  |                   |

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

1 row in set (0.00 sec)

linux-node2 master配置跟linux-node3 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.13',

                 MASTER_PORT=3306,

                 MASTER_USER='repl',

                 MASTER_PASSWORD='mysql',

                 MASTER_LOG_FILE='mysql-bin.000001',

                 MASTER_LOG_POS=613;

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.13

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 613

               Relay_Log_File: linux-node2-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes        

linux-node3 master配置跟linux-node2 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',

                 MASTER_PORT=3306,

                 MASTER_USER='repl',

                 MASTER_PASSWORD='mysql',

                 MASTER_LOG_FILE='mysql-bin.000001',

                 MASTER_LOG_POS=613;

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.12

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 613

               Relay_Log_File: linux-node3-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

驗(yàn)證

從linux-node2上驗(yàn)證

在linux-node2 master上創(chuàng)建數(shù)據(jù)

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

mysql> use test;

Database changed

mysql> create table temp(id int,name varchar(64));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into temp values(1,'aaa');

Query OK, 1 row affected (0.28 sec)

mysql> CREATE TABLE temp2(id INT PRIMARY KEY  NOT NULL AUTO_INCREMENT ,nname VARCHAR(64));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp2(nname) values('bbb');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test.temp;

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

| id   | name |

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

|    1 | aaa  |

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

1 row in set (0.01 sec)

在linux-node2 slave上查看數(shù)據(jù)

mysql> select * from test.temp;

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

| id   | name |

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

|    1 | aaa  |

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

1 row in set (0.00 sec)

在linux-node3 master上查看數(shù)據(jù)

mysql> select * from test.temp;

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

| id   | name |

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

|    1 | aaa  |

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

1 row in set (0.00 sec)

在linux-node3 slave上查看數(shù)據(jù)

mysql> select * from test.temp;

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

| id   | name |

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

|    1 | aaa  |

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

1 row in set (0.00 sec)

從linux-node3上驗(yàn)證

在linux-node3 master上創(chuàng)建數(shù)據(jù)

mysql> use test;

mysql> insert into temp2(nname) values('ddd');

Query OK, 1 row affected (0.02 sec)

mysql>  insert into temp2(nname) values('fff');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test.temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

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

在linux-node3 slave上查看數(shù)據(jù)

mysql> select * from test.temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

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

3 rows in set (0.00 sec)

在linux-node2 master上查看數(shù)據(jù)

mysql> select * from test.temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

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

3 rows in set (0.00 sec)

在linux-node2 slave上查看數(shù)據(jù)

mysql> select * from test.temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

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

3 rows in set (0.00 sec)

結(jié)論

·        在任意一個(gè)master端更新數(shù)據(jù)，其他任意端都可以更新數(shù)據(jù)

·        兩臺(tái)服務(wù)器配置了間隔自增長(zhǎng)，數(shù)據(jù)不同沖突

linux-node1上安裝mycat

安裝mycat

cd /usr/local/src

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 /data/app/mycat-1.6

ln -s /data/app/mycat-1.6 /data/app/mycat

修改schema.xml

·        balance="1"
全部的readHost與stand by writeHost參與select語句的負(fù)載均衡

·        writeType="0"
所有寫操作發(fā)送到配置的第一個(gè)writeHost，第一個(gè)掛了切到還生存的第二個(gè) writeHost，重新啟動(dòng)后以切換后的為準(zhǔn)，切換記錄在配置文件中:dnindex.properties

·        switchType="1"
默認(rèn)值為1，自動(dòng)切換

cd /data/app/mycat

cp conf/schema.xml conf/schema.xml.bak

cat > conf/schema.xml <<EOF

<?xml version="1.0"?>

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

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

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>

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

    <dataHost name="node1" maxCon="10" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">

        <heartbeat>select user()</heartbeat>

        <writeHost host="master1" url="192.168.56.12:3306" user="root" password="mysql">

            <readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />

        </writeHost>

        <writeHost host="master2" url="192.168.56.13:3306" user="root" password="mysql">

            <readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />

        </writeHost>

    </dataHost>

</mycat:schema>

EOF

啟動(dòng)mycat

./bin/mycat start

ss -lntup |egrep  '(8066|9066)'  

tcp    LISTEN     0      100                   :::8066                 :::*      users:(("java",16546,79))

tcp    LISTEN     0      100                   :::9066                 :::*      users:(("java",16546,75))

驗(yàn)證mycat服務(wù)是否正常

在linux-node2-master端配置mycat連接賬號(hào)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'192.%' IDENTIFIED BY 'mysql';

Query OK, 0 rows affected, 1 warning (0.07 sec

在mycat服務(wù)器上安裝mysql服務(wù)，但是不啟動(dòng)

步驟省略，詳細(xì)內(nèi)容可以參考上面的MySQL安裝

使用mysql的客戶端連接mycat

cd /data/app/mysql/

./bin/mysql -uroot -p -P 8066 -h 192.168.56.11 ##連接mycat，初始密碼123456

mysql> show databases;

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

| DATABASE |

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

| TESTDB   |

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

1 row in set (0.00 sec)

mysql> use TESTDB;

mysql> insert into temp2(nname) values('eee');

Query OK, 1 row affected (0.09 sec)

mysql> insert into temp2(nname) values('ggg');

Query OK, 1 row affected (0.01 sec)

linux-node3 slave端查看數(shù)據(jù)是否同步

mysql> select * from test.temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

|  5 | eee   |

|  7 | ggg   |

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

5 rows in set (0.00 sec)

結(jié)果發(fā)現(xiàn)數(shù)據(jù)寫入到了linux-node2 slave端

測(cè)試

服務(wù)自動(dòng)遷移

關(guān)閉linux-node2 master的MySQL服務(wù)

mysql> shutdown;

Query OK, 0 rows affected (0.01 sec)

shell > ss -lntup |grep 3306

mycat端插入新的數(shù)據(jù)查看數(shù)據(jù)是否同步

mysql> insert into temp2(nname) values('mmmm');

Query OK, 1 row affected (0.07 sec)

mysql> insert into temp2(nname) values('nnnn');

Query OK, 1 row affected (0.01 sec)

linux-node3 slave端查看數(shù)據(jù)是否同步

mysql> select * from test.temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

|  5 | eee   |

|  7 | ggg   |

|  8 | mmmm  |

| 10 | nnnn  |

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

7 rows in set (0.00 sec)

linux-node2 slave端查看數(shù)據(jù)是否同步

mysql> select * from test.temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

|  5 | eee   |

|  7 | ggg   |

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

5 rows in set (0.00 sec)

 發(fā)現(xiàn)因?yàn)?/span>linux-node2的master端已經(jīng)掛了，數(shù)據(jù)不能同步了

數(shù)據(jù)訪問是否正常

登錄到mycat服務(wù)器上執(zhí)行如下命令：

mysql> select * from temp2;

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

| id | nname |

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

|  1 | bbb   |

|  2 | ddd   |

|  4 | fff   |

|  5 | eee   |

|  7 | ggg   |

|  8 | mmmm  |

| 10 | nnnn  |

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

7 rows in set (0.00 sec)

執(zhí)行多次發(fā)現(xiàn)結(jié)果一樣，說明在一臺(tái)master端掛掉的情況下，其連接的slave端也被剔除，因此數(shù)據(jù)完整性可以保證

故障匯總

第一次配置的時(shí)候maser端沒有配置log-slave-updates，導(dǎo)致node3-slave上沒有node2-master端的數(shù)據(jù)。
解釋：
從庫開啟log-bin參數(shù)，如果直接往從庫寫數(shù)據(jù)，是可以記錄log-bin日志的，但是從庫通過I0線程讀取主庫二進(jìn)制日志文件，然后通過SQL線程寫入的數(shù)據(jù)，是不會(huì)記錄binlog日志的。也就是說從庫從主庫上復(fù)制的數(shù)據(jù)，是不寫入從庫的binlog日志的。所以從庫做為其他從庫的主庫時(shí)需要在配置文件中添加log-slave-updates參數(shù)。

解決辦法：

[mysqld]

log-slave-updates