mysql主从搭建与mycat读写分离

mysql主从复制与mycat读写分离

试验环境：

centos6.5 mysql5.7.24 mycat 1.6

一.搭建一主一从

1.主机配置(master)

修改配置文件：vi /etc/my.cnf 
#主服务器唯一ID server-id=1  
#启用二进制日志 log-bin=mysql-bin 

#设置不要复制的数据库(可设置多个)

binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字 
#设置logbin格式 
binlog_format=STATEMENT 

vi /etc/my.cnf

在my.cnf[mysqld]下添加
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema
binlog-do-db=需要复制的主数据库名字 
binlog_format=STATEMENT 

2.从机配置(slave)

修改配置文件：vi /etc/my.cnf
 #从服务器唯一ID server-id=2  
#启用中继日志 relay-log=mysql-relay 

vi /etc/my.cnf
在my.cnf [mysqld]下添加
server-id=2
relay-log=mysql-relay 

3.主机、从机重启mysql服务，查看状态

service mysql restart
service mysql status

4.主机从机都查看关闭防火墙

service iptables stop   命令关闭防火墙，但是系统重启后会开始
chkconfig iptables off--关闭防火墙开机自启动

5.在主机上建立账户并授权slave

#在master主机MySQL里执行授权命令 
mysql -uroot -p123456
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456'; 

#查询master的状态
#记录下File和Position的值 #执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化 
mysql -uroot -p123456 -h 
show master status; 

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      604 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6.在从机上配置需要复制的主机

#复制主机的命令
CHANGE MASTER TO MASTER_HOST=‘主机的IP地址’,
MASTER_USER=‘slave’,
MASTER_PASSWORD=‘123456’,
MASTER_LOG_FILE=‘mysql-bin.具体数字’,MASTER_LOG_POS=具体值;
具体数字和具体值在上面 show master status查看得到;

#启动从服务器复制功能
start slave;
#查看从服务器状态，是否配置成功
#下面两个参数都是Yes，则说明主从配置成功！
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

show slave status\G;  # \G按列显示

mysql> CHANGE MASTER TO MASTER_HOST='192.168.137.141',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=604;
Query OK, 0 rows affected, 2 warnings (0.34 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.137.141
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 604
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

7.主机新建库、新建表、insert记录，从机复制

master操作
mysql -uroot -p123456
create database firstdb;
use firstdb;
create table mydb(id int,name varchar(20));
insert into mydb values(1,"zhangsan");
select * from mydb;

展示结果：

mysql> create database firstdb;
Query OK, 1 row affected (0.00 sec)

mysql> use firstdb;
Database changed
mysql> create table mydb(id int, name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into mydb values(1,"zhangsan");
Query OK, 1 row affected (0.31 sec)

mysql> select * from mydb;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

从机登录mysql查看

 mysql -uroot -p123456
 show databases;
 use firstdb;
 show tables;
 select * from mydb;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| firstdb            |
| mysql              |
| orders             |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
7 rows in set (0.00 sec)

mysql> use firstdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_firstdb |
+-------------------+
| mydb              |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from mydb;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

8.停止从服务复制功能及重新配置主从

stop slave;
reset master;

二、mycat读写分离

1.修改mycat的配置文件schema.xml

ps:mycat为逻辑库，仍需在mysql内对应配置文件里的数据库orders，创建相应的表mydb，才能进行select * from mydb 查看

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

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <table name="customer" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
                <table name="mydb" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
        </schema>
        <dataNode name="dn1" dataHost="host1" database="orders" />
        <dataNode name="dn2" dataHost="host2" database="orders" />
        <dataNode name="dn3" dataHost="host1" database="orders" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.137.141:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                </writeHost>
        </dataHost>
         <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.137.142:3306" user="root"
                                   password="123456">

                </writeHost>
        </dataHost>

2.修改server.xml 文件

<user name="root">
         <property name="password">123456</property>
          <property name="schemas">TESTDB</property>
          <property name="readOnly">false</property>
</user>

3.启动mycat

cd /usr/local/mycat/bin/  # mycat配置位置
./mycat start			  # 启动命令
./mycat status			  # mycat运行状态
./mycat console			  # 控制台，查看运行启动日志

4.验证数据分离

# 登录mycat查看
mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from mydb;
+------+----------+
| id   | name     |
+------+----------+
|    3 | service2 |
|    1 | service2 |
|    2 | service2 |
|    3 | service1 |
|    1 | service1 |
|    2 | service1 |
|    3 | service1 |
|    1 | service1 |
|    2 | service1 |
+------+----------+
9 rows in set (0.04 sec)

# 从机查看
mysql> use orders;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
| dict_order_type  |
| mydb             |
| orders           |
| orders_detail    |
+------------------+
5 rows in set (0.00 sec)

mysql> select * from mydb;
+------+----------+
| id   | name     |
+------+----------+
|    3 | service2 |
|    1 | service2 |
|    2 | service2 |
+------+----------+
3 rows in set (0.00 sec)

_order_type  |
| mydb             |
| orders           |
| orders_detail    |
+------------------+
5 rows in set (0.00 sec)

mysql> select * from mydb;
+------+----------+
| id   | name     |
+------+----------+
|    3 | service2 |
|    1 | service2 |
|    2 | service2 |
+------+----------+
3 rows in set (0.00 sec)