MySQL作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台MySQL作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。
因此，一般来说都是通过主从同步(Master-Slave)的方式来同步数据，再通过读写分离(MyCAT)来提升数据库的并发负载能力这样的方案来进行部署与实施的。
基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE)，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载
阿里巴巴B2B开发的关系型分布式系统，管理将近3000个MySQL实例。 在阿里经受住了考验，后面由于作者的走开的原因Cobar没有人维护 了，阿里也开发了Tddl替代Cobar。
社区爱好者在阿里Cobar基础上进行二次开发，解决了Cobar当时存在的一些问题，并且加入了许多新的功能在其中。目前MyCAT社区活跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比较高，也会一直维护下去。
数据库界大牛，前支付宝数据库团队领导楼总开发，基于MySQL官方的Proxy思想利用C进行开发的，OneProxy是一款商业收费的中间件， 楼总舍去了一些功能点，专注在性能和稳定性上。有朋友测试过说在高并发下很稳定。
这个中间件是Youtube生产在使用的，但是架构很复杂。 与以往中间件不同，使用Vitess应用改动比较大要使用他提供语言的API接口，我们可以借鉴他其中的一些设计思想。
Kingshard是前360Atlas中间件开发团队的陈菲利用业务时间用Go语言开发的，目前参与开发的人员有3个左右， 目前来看还不是成熟可以使用的产品，需要在不断完善。
360团队基于MySQL Proxy把Lua用C改写。原有版本是支持分表， 目前已经放出了分库分表版本。在网上看到一些朋友经常说在高并发下会经常挂掉，如果大家要使用需要提前做好测试。
这两个中间件都算是官方的，MaxScale是MariaDB (MySQL原作者维护的一个版本)研发的，目前版本不支持分库分表。MySQL Route是现在MySQL官方Oracle公司发布出来的一个中间件。
B：可以在程序上实现一些高级控制，如：透明化水平拆分，failover,监控
· 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
· 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
· 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
| 主机名 | IP | 系统 | 角色 |
| MyCAT | 192.168.10.69 | RHEL7.3 | MyCAT服务器 |
MyCAT有提供编译好的安装包，支持windows、Linux、Mac、Solaris等系统上安装与运行。
官方下载主页http://www.mycat.io
Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz
Linux下可以下载Mycat-server-xxxxx.linux.tar.gz 解压在某个目录下，注意目录不能有空格，在Linux(Unix)下，建议放在/usr/local/目录下
anaconda-ks.cfg jdk-8u201-linux-x64.tar.gz Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz
[root@Mycat ~]# tar xvf Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz -C /usr/local
[root@Mycat ~]# cd /usr/local/
[root@Mycat local]# ls
bin etc games include lib lib64 libexec mycat sbin share src
[root@Mycat local]# cd mycat/
[root@Mycat mycat]# ls
bin catlet conf lib logs version.txt
[root@Mycat mycat]# useradd mycat
[root@Mycat mycat]# chown -R mycat.mycat /usr/local/mycat
[root@Mycat mycat]# ls -dl /usr/local/mycat
drwxr-xr-x. 7 mycat mycat 85 Oct 10 10:05 /usr/local/mycat
[root@Mycat mycat]# passwd mycat
Changing password for user mycat.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@Mycat mycat]# pwd
/usr/local/mycat
[root@Mycat mycat]# ll
total 12
drwxr-xr-x. 2 mycat mycat 190 Oct 10 10:05 bin
drwxrwxrwx. 2 mycat mycat 6 Jul 18 05:17 catlet
drwxrwxrwx. 4 mycat mycat 4096 Oct 11 09:42 conf
drwxr-xr-x. 2 mycat mycat 4096 Oct 10 10:05 lib
drwxrwxrwx. 3 mycat mycat 92 Oct 11 09:42 logs
drwxr-xr-x. 2 root root 6 Oct 10 11:40 tmlogs
-rwxrwxrwx. 1 mycat mycat 227 Sep 27 04:11 version.txt
http://www.oracle.com/technetwork/java/javase/downloads/index.html
[root@Mycat ~]# mkdir /usr/java
[root@Mycat ~]# tar xvf jdk-8u201-linux-x64.tar.gz -C /usr/java/
[root@Mycat ~]# ls /usr/java/
jdk1.8.0_201
[root@Mycat ~]# vim /etc/profile
#配置MyCAT所需环境变量
JAVA_HOME=/usr/java/jdk1.8.0_201
PATH=$JAVA_HOME/bin:$PATH
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVA_HOME CLASSPATH
MYCAT_HOME=/usr/local/mycat
PATH=$MYCAT_HOME/bin:$PATH
[root@Mycat ~]# source /etc/profile
[root@Mycat ~]# java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[root@Mycat ~]# mycat
Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
在MyCAT Server所在的服务器上配置IP和主机名的映射，配置方式如下：
[root@Mycat ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.10.67 MySQL1
192.168.10.68 MySQL2
192.168.10.69 Mycat
[root@Mycat ~]# cd /usr/local/mycat/conf
[root@Mycat conf]# vim server.xml
#只需要改动以下配置，其他的不要改
#ha是MySQL主从同步的数据库
<user name="root" defaultAccount="true">
<property name="password">123123</property>
<property name="schemas">ha</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>
<user name="user">
<property name="password">user</property>
<property name="schemas">ha</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
[root@Mycat conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="ha" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
</schema>
<dataNode name="dn1" dataHost="dthost" database="ha"/>
<dataHost name="dthost" maxCon="500" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="MySQL1" url="192.168.10.67:3306" user="root" password="123123">
<readHost host="MySQL2" url="192.168.10.68:3306" user="root" password="123123" />
</writeHost>
</dataHost>
</mycat:schema>
1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
2. balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。
4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力
3. switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status
4. switchType='3'基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'。
验证方式， 修改Mycat的日志等级为 Debug，可以观察SQL的路由情况
[root@Mycat ~]# cd /usr/local/mycat/conf
[root@Mycat conf]# vim log4j2.xml
<asyncRoot level="debug" includeLocation="true">
<!--<AppenderRef ref="Console" />-->
<AppenderRef ref="RollingFile"/>
</asyncRoot>
</Loggers>
</Configuration>
[root@Mycat conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@Mycat ~]# tail -f /usr/local/mycat/logs/wrapper.log
INFO | jvm 1 | 2019/10/10 11:40:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2019/10/10 11:46:52 | TERM trapped. Shutting down.
STATUS | wrapper | 2019/10/10 11:46:53 | <-- Wrapper Stopped
STATUS | wrapper | 2019/10/10 11:47:00 | --> Wrapper Started as Daemon
STATUS | wrapper | 2019/10/10 11:47:00 | Launching a JVM...
INFO | jvm 1 | 2019/10/10 11:47:00 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO | jvm 1 | 2019/10/10 11:47:00 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2019/10/10 11:47:00 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2019/10/10 11:47:00 |
INFO | jvm 1 | 2019/10/10 11:47:01 | MyCAT Server startup successfully. see logs in logs/mycat.log
看到 MyCAT Server startup successfully 说明MyCAT成功启动！
从MySQL5.7版本开始，安装MySQL需要依赖 Boost 的C++扩展，而且只能是 1.59.0 版本。
Boost 下载地址：Boost Version History ；选择1.59.0版本下载，在编译是填写相应参数，指定Boost源码位置即可。
Boost库是一个可移植、提供源代码的C++库，作为标准库的后备，是C++标准化进程的开发引擎之一。
Boost库由C++标准委员会库工作组成员发起，其中有些内容有望成为下一代C++标准库内容。
在C++社区中影响甚大，是不折不扣的“准”标准库。Boost由于其对跨平台的强调，对标准C++的强调，与编写平台无关。
按照以下脚本内容操作或者复制以下脚本内容，用脚本快速安装MySQL5.7
[root@MySQL ~]# vim mysql5.7.sh
#!/bin/bash
#Explain: This is a script to quickly deploy the MySQL5.7 service
#Author: Jaking
#Email: Jaking@vip.163.com
#Date: 2019-10-10
#注意: 运行这个脚本前，请配置好yum源，把脚本和源码包放在同一目录下
#推荐源码包：mysql-5.7.19.tar.gz boost_1_59_0.tar.gz
#1.准备c++ booost库，不需要编译
tar xvf boost_1_59_0.tar.gz
mv boost_1_59_0 /usr/local/boost
#2.源码安装MySQL5.7
tar xvf mysql-5.7.19.tar.gz -C /usr/local/src
cd /usr/local/src/mysql-5.7.19
#3.安装相关编译工具和依赖包
yum install -y gcc-c++ cmake ncurses-devel
#4.创建mysql用户来运行MySQL数据库,不创建mysql的家目录并禁止其登录系统
groupadd -g 27 mysql
useradd mysql -u 27 -g mysql -M -s /sbin/nologin
#5.创建存放数据的目录并改变相关权限
mkdir -p /data/mysql
chown mysql:mysql /data/mysql
#6.预编译
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 -DMYSQL_DATADIR=/data/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost -DWITH_INNODB_MEMCACHED=ON
#7.编译
make
#8.编译安装
make install
#9.创建my.cnf配置文件
mv /etc/my.cnf /etc/my.cnf.bak
cat >>/etc/my.cnf<<EOF
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql
tmpdir=/tmp
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
EOF
#10.将编译好的/usr/local/mysql57/bin里面的命令，加入到 $PATH 变量中
echo "export PATH=\$PATH:/usr/local/mysql57/bin" >> /etc/profile
source /etc/profile
#注意：如果用脚本来安装MySQL5.7数据库,在脚本执行完成后，需要重新执行source /etc/profile
#11.初始化MySQL数据库
/usr/local/mysql57/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
#12.启动MySQL数据库
/usr/local/mysql57/support-files/mysql.server start
#13.查看MySQL数据库运行状态
/usr/local/mysql57/support-files/mysql.server status
[root@MySQL ~]# chmod 755 mysql5.7.sh
[root@MySQL ~]# ./mysql5.7.sh
source /etc/profile
"A temporary password is generated for root@localhost: "后面的字段就是 root 用户的初始密码
[root@MySQL2 ~]# cd /data/mysql/
[root@MySQL2 mysql]# ls
auto.cnf ibdata1 ib_logfile1 mysql mysqld.pid mysql.sock.lock sys
ib_buffer_pool ib_logfile0 ibtmp1 mysqld.log mysql.sock performance_schema
[root@MySQL2 mysql]# cat mysqld.log | grep root
2019-10-10T07:18:53.141237Z 1 [Note] A temporary password is generated for root@localhost: yg*baqh6di4G
2019-10-10T07:18:53.320270Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2019-10-10T07:18:53.320305Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2019-10-10T07:18:55.012861Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2019-10-10T07:18:55.012905Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
[root@MySQL2 mysql]# /usr/local/mysql57/support-files/mysql.server status
SUCCESS! MySQL running (21771)
[root@MySQL2 mysql]# /usr/local/mysql57/bin/mysql -uroot -p"yg*baqh6di4G"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123123';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@MySQL2 mysql]# /usr/local/mysql57/bin/mysql -uroot -p"yg*baqh6di4G"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@MySQL2 mysql]# /usr/local/mysql57/bin/mysql -uroot -p"123123"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[root@MySQL ~]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
Unit mysqld.service could not be found.
[root@MySQL ~]# cp -f /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld
[root@MySQL ~]# service mysqld status
MySQL running (23872) [ OK ]
[root@MySQL ~]# mysql -uroot -p123123
-bash: mysql: command not found
[root@MySQL ~]# vim /etc/profile
#要确保 /etc/profile 里面有"export PATH=$PATH:/usr/local/mysql57/bin"才能使用 mysql 命令来登录MySQL数据库
[root@MySQL ~]# source /etc/profile
[root@MySQL ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
| 主机名 | IP | 系统/MySQL版本 | 角色 |
| MySQL1 | 192.168.10.67 | RHEL7.3/MySQL5.7 | Master |
| MySQL2 | 192.168.10.68 | RHEL7.3/MySQL5.7 | Slave |
注意：Linux下部署安装MySQL，默认不忽略表名大小写，需要手动到/etc/my.cnf 下配置 lower_case_table_names=1 使Linux环境下MySQL忽略表名大小写，否则使用MyCAT的时候会提示找不到表的错误！
[root@MySQL1 mysql-5.7.19]# vim /etc/my.cnf
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql
tmpdir=/tmp
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
log-bin=mysql-bin-master #启用二进制日志
server-id=1 #本机数据库ID 标示
binlog-do-db=ha #可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-ignore-db=mysql #不可以被从服务器复制的库
lower_case_table_names=1
#注意：Linux下部署安装MySQL，默认不忽略表名大小写，需要手动到/etc/my.cnf 下配置 lower_case_table_names=1 使Linux环境下MySQL忽略表名大小写，否则使用MyCAT的时候会提示找不到表的错误！
~
~
"/etc/my.cnf" 24L, 556C written
[root@MySQL1 ~]# service mysqld restart
Shutting down MySQL........... SUCCESS!
Starting MySQL. SUCCESS!
[root@MySQL1 mysql-5.7.19]# mysql -uroot -p123123
mysql> create database ha;
Query OK, 1 row affected (0.00 sec)
mysql> use ha;
Database changed
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
[root@MySQL1 mysql-5.7.19]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to slave@"192.168.10.%" identified by "123123";
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
sc
mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000002 | 449 | ha | mysql | |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events\G;
*************************** 1. row ***************************
Log_name: mysql-bin-master.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.19-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin-master.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: mysql-bin-master.000001
Pos: 154
Event_type: Stop
Server_id: 1
End_log_pos: 177
Info:
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql> exit
Bye
[root@MySQL1 mysql-5.7.19]# ls /data/mysql/
auto.cnf ib_logfile0 mysql-bin-master.000001 mysqld.pid QDX
ha ib_logfile1 mysql-bin-master.000002 mysql.sock sys
ib_buffer_pool ibtmp1 mysql-bin-master.index mysql.sock.lock
ibdata1 mysql mysqld.log performance_schema
[root@MySQL1 mysql-5.7.19]# mysqldump -uroot -p123123 ha > ha.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@MySQL1 mysql-5.7.19]# ls
BUILD CPackConfig.cmake install_manifest.txt mysys storage
client CPackSourceConfig.cmake libbinlogevents mysys_ssl strings
cmake CTestTestfile.cmake libbinlogstandalone packaging support-files
CMakeCache.txt dbug libevent plugin testclients
CMakeFiles Docs libmysql rapid unittest
cmake_install.cmake Doxyfile-perfschema libmysqld README VERSION
CMakeLists.txt extra libservices regex VERSION.dep
cmd-line-utils HA.sql make_dist.cmake scripts vio
config.h.cmake include Makefile source_downloads win
configure.cmake info_macros.cmake man sql zlib
COPYING INSTALL mysql-test sql-common
[root@MySQL1 mysql-5.7.19]# scp ha.sql 192.168.10.68:/root
The authenticity of host '192.168.10.68 (192.168.10.68)' can't be established.
ECDSA key fingerprint is 57:3d:7f:91:5a:bc:6f:a1:88:4f:d2:fc:17:0e:51:8b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.10.68' (ECDSA) to the list of known hosts.
root@192.168.10.68's password:
ha.sql 100% 1765 1.7KB/s 00:00
注意：Linux下部署安装MySQL，默认不忽略表名大小写，需要手动到/etc/my.cnf 下配置 lower_case_table_names=1 使Linux环境下MySQL忽略表名大小写，否则使用MyCAT的时候会提示找不到表的错误！
[root@MySQL2 ~]# vim /etc/my.cnf
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql
tmpdir=/tmp
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
server-id=2
#从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。
lower_case_table_names=1
#注意：Linux下部署安装MySQL，默认不忽略表名大小写，需要手动到/etc/my.cnf 下配置 lower_case_table_names=1 使Linux环境下MySQL忽略表名大小写，否则使用MyCAT的时候会提示找不到表的错误！
~
~
"/etc/my.cnf" 20L, 649C written
[root@MySQL2 ~]# service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]
[root@MySQL1 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
%
mysql> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| innodb_version | 5.7.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.19-log |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------+
8 rows in set (0.01 sec)
mysql>
[root@MySQL2 mysql]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| innodb_version | 5.7.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.19 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------+
8 rows in set (0.02 sec)
mysql> exit
Bye
[root@MySQL2 mysql]# mysql -uslave -p123123 -h 192.168.10.67
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
#此时看不到ha数据库属正常现象
[root@MySQL2 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.19 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database ha;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
[root@MySQL2 ~]# mysql -uroot -p123123 ha <ha.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@MySQL2 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.19 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ha |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use ha;
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_ha |
+--------------+
| t1 |
+--------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@MySQL2 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.10.67',master_user='slave',master_password='123123';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.67
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000002
Read_Master_Log_Pos: 601
Relay_Log_File: MySQL2-relay-bin.000003
Relay_Log_Pos: 828
Relay_Master_Log_File: mysql-bin-master.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running ：一个负责与主机的IO通信
Slave_SQL_Running：负责自己的Slave MySQL进程
[root@MySQL1 mysql-5.7.19]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 6
User: slave
Host: 192.168.10.68:54656
db: NULL
Command: Binlog Dump
Time: 165
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 7
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
+--------------------+
| Database |
+--------------------+
| information_schema |
| ha |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use ha;
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> insert into t1 values (1,'man');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | man |
+------+------+
1 row in set (0.00 sec)
mysql>
+--------------------+
| Database |
+--------------------+
| information_schema |
| ha |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use ha;
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 t1;
+------+------+
| id | name |
+------+------+
| 1 | man |
+------+------+
1 row in set (0.00 sec)
mysql>
在从数据库上能看到主数据库写入的数据，说明主从同步配置成功！
[root@MySQL1 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 80
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all privileges on *.* to root@"%" identified by "123123";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
[root@MySQL2 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all privileges on *.* to root@"%" identified by "123123";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
目前MyCAT有两个端口，8066数据端口，9066管理端口，测试读写操作用8066数据端口
[root@Mycat ~]# mysql -uroot -p123123 -h 192.168.10.69 -P8066
#注意：是从MyCAT服务器登录到MySQL数据库！
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.3-release-20190927161129 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+----------+
| DATABASE |
+----------+
| ha |
+----------+
1 row in set (0.00 sec)
mysql> use ha;
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> insert into t1 values (2,'superman');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (3,'woman');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | man |
| 2 | superman |
| 3 | woman |
+------+----------+
3 rows in set (0.02 sec)
mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | man |
| 2 | superman |
+------+----------+
2 rows in set (0.00 sec)
[root@Mycat ~]# tail -f /usr/local/mycat/logs/mycat.log
#在测试读写操作的同时观察mycat.log日志的变化
2019-10-11 09:42:46.934 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection [id=5, lastTime=1570801366932, user=root, schema=ha, old shema=ha, borrowed=true, fromSlaveDB=false, threadId=25, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.67, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2019-10-11 09:42:46.934 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection [id=13, lastTime=1570801366932, user=root, schema=ha, old shema=ha, borrowed=true, fromSlaveDB=true, threadId=15, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.68, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2022-08-03 16:02:52.943 DEBUG [Timer1] (io.mycat.sqlengine.SQLJob.connectionAcquired(SQLJob.java:89)) - con query sql:select user() to con:MySQLConnection [id=6, lastTime=1659513772943, user=root, schema=vos_data_platform2, old shema=vos_data_platform2, borrowed=true, fromSlaveDB=false, threadId=20629, charset=utf8, txIsolation=3, autocom
由下图可知，读操作是在Slave 192.168.10.68上进行的
由下图可知，写操作是在Master 192.168.10.67上进行的
6591
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
