MyCat水平分库和垂直分库

Centos7静态IP配置

# 'MYSQL01'
BOOTPROTO=static

IPADDR=192.168.18.129
NETMASK=255.255.255.0
GATEWAY=192.168.18.2

# 'MYSQL02'

BOOTPROTO=static

IPADDR=192.168.18.130
NETMASK=255.255.255.0
GATEWAY=192.168.18.2

MyCat的安装

# 1.解压并安装
[root@localhost src]# tar -xzvf Mycat-server-1.6.7.6-test-20201104174609-linux.tar.gz -C /usr/local
[root@localhost src]# chmod -R 777 /usr/local/mycat/
    
# 2.配置环境变量(需要Java环境)
[root@localhost src]# vim /etc/profile
    export MYCAT_HOME=/usr/local/mycat
[root@localhost src]# source /etc/profile
    
# 启动MyCat
[root@localhost src]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
   
# 查看是否启动成功
[root@localhost ~]# netstat -tunpl | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      2061/java           

# 注意:
#    默认用户: root 默认密码: 123456 默认端口: 8066

配置MySQL,MyCat集群环境

[root@mysql01 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=0cad5662-201a-11eb-96eb-000c2cacac01
[root@mysql01 ~]# service mysqld restart 
[root@mysql01 ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
Mycat-server is already running.
# 检查MySQL,MyCat是否安装正常
[root@mysql01 ~]# netstat -tunpl | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      6246/mysqld         
[root@mysql01 ~]# netstat -tunpl | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      6328/java

[root@mysql02 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=0cad5662-201a-11eb-96eb-000c2cacac02
[root@mysql02 ~]# service mysqld restart
[root@mysql02 ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
Mycat-server is already running.
# 检查MySQL,MyCat是否安装正常
[root@mysql02 ~]# netstat -tunpl | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      7001/mysqld         
[root@mysql02 ~]# netstat -tunpl | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      7114/java   

配置MyCat主从复制**

# MySQL01(主服务器)配置
[root@mysql01 ~]# vim /etc/my.cnf
# 在 [mysqld] 下方添加如下内容
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
    
[root@mysql01 ~]# service mysqld restart
[root@mysql01 ~]# mysql -uroot -p
Enter password: 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# MySQL02(从服务器)的配置
[root@mysql02 ~]# vim /etc/my.cnf
# 在 [mysqld] 下方添加如下内容
server-id=2
[root@mysql02 ~]# mysql -uroot -p
Enter password: 
use mysql;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /usr/share/mysql/mysql_system_tables.sql;
[root@localhost ~]# service mysqld restart
[root@mysql02 ~]# mysql -uroot -p
Enter password: 

change master to master_host='192.168.18.129',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=154;

start slave;

show slave status;

Slave_IO_Running 	Slave_SQL_Running
Yes					Yes

如果遇到事务回滚导致的ERROR 3021 (HY000): Unknown error 3021,使用如下方法重新绑定

# MySQL01:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

=========================================================================================

# MySQL02:
[root@mysql02 ~]# mysql -uroot -p
Enter password: 
use mysql;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /usr/share/mysql/mysql_system_tables.sql;
[root@localhost ~]# service mysqld restart
[root@mysql02 ~]# mysql -uroot -p
Enter password: 

stop slave;
reset master;
change master to master_host='192.168.18.129',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000003',master_log_pos=154;

start slave;

show slave status;

Slave_IO_Running 	Slave_SQL_Running
Yes					Yes

MyCat读写分离

<!--
修改主服务器MyCat的 server.xml
[root@mysql01 ~]# vim /usr/local/mycat/conf/server.xml
-->

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">

......
    
 <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">MYTESTDB</property>
                <property name="defaultSchema">TESTDB</property>
                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>
    
......
    
</mycat:server>

<!-- 
修改主服务器MyCat的 schema.xml
[root@mysql01 ~]# vim /usr/local/mycat/conf/schema.xml
-->
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	
	<schema name="MYTESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="mydn1"></schema>
	<dataNode name="mydn1" dataHost="mylocalhost1" database="DBNAME01" />
	<dataHost name="mylocalhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<!-- 主服务器负责写的操作 -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
				   password="root">
				   <!-- 从服务器负责读操作 -->
				   <readHost host="hostS1" url="192.168.18.130:3306" user="root" password="root"/>
		</writeHost>
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
</mycat:schema>