mysql主从复制与mycat读写分离
文章目录
mysql主从复制与mycat读写分离试验环境：一.搭建一主一从1.主机配置(master)2.从机配置(slave)3.主机、从机重启mysql服务，查看状态4.主机从机都查看关闭防火墙5.在主机上建立账户并授权slave6.在从机上配置需要复制的主机7.主机新建库、新建表、insert记录，从机复制8.停止从服务复制功能及重新配置主从
二、mycat读写分离1.修改mycat的配置文件schema.xml2.修改server.xml 文件3.启动mycat4.验证数据分离
试验环境：
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)
