Mycat读写分离 + 主从复制(Gtid)

大概架构如下：

        IP 　　　　主机名　　  　　 OS
192.168.1.177　　mycat　　  CentOS7.4.1708
192.168.1.184 　  master　　 CentOS7.4.1708
192.168.1.185      salve　　   CentOS7.4.1708

#master、slave端安装mysql(这里使用同一个脚本)

master、slave端安装mysql(这里使用脚本)
cat>>~/mysql.sh<<EOF
#!/bin/bash
#Centox7已经不支持mysql,因为一部分原因(比如收费)所以内部集成了mariadb，而安装mysql的话会和mariadb的文件冲突，所以必须先卸载掉mariadb
for i in `rpm -qa|grep mariadb`;do rpm -e --nodeps $i;done
for i in `rpm -qa|grep mysql`;do rpm -e --nodeps $i;done
#安装mysql依赖
yum -y install wget make cmake zlib-devel gcc gcc-c++ libtool openssl openssl-devel autoconf automake pcre* gd gd-devel bison ncurses ncurses-devel
#下载mysql包
[ ! -d /opop ] && mkdir /opop
cd /opop
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.25.tar.gz
tar -zxvf mysql-boost-5.7.25.tar.gz
cd mysql-5.7.25/
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/usr/local/mysql/data
-DDOWNLOAD_BOOST=1
-DWITH_BOOST=/opop/mysql-5.7.25/boost/boost_1_59_0
-DSYSCONFDIR=/etc
-DWITH_MYISAM_STORAGE_ENGINE=1
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_PARTITION_STORAGE_ENGINE=1
-DWITH_READLINE=1
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock
-DMYSQL_TCP_PORT=3306
-DENABLED_LOCAL_INFILE=1
-DENABLE_DTRACE=0
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DMYSQL_USER=mysql
make -j2 && make install
EOF

#执行脚本
sh opop.sh

#创建数据存储目录
mkdir -p /usr/local/mysql/data　
chown -R mysql.mysql /usr/local/mysql/

#新创建并配置my.cnf文件
vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
####开启gtid
server-id=184
gtid-mode=on
enforce-gtid-consistency=true
log-bin-index=master-bin.index
log_bin=master-binlog
log-slave-updates = ON
binlog-checksum = CRC32
master-verify-checksum = 1
####
port=3306
socket=/usr/local/mysql/mysql.sock
character-set-server=utf8
pid-file=/usr/local/mysql/mysqld.pid
log-error=/var/log/mysqld.log
explicit_defaults_for_timestamp=true

#注意1:slave在创建并编辑my.cnf,需将server-id换成其他值(这里是以IP最后一位定义)

#注意2:slave端关于Gtid在my.cnf中需开启项：
log-slave-updates = ON
log-bin=slave-binlog
master-info-repository=TABLE 　　    #Slave配置需要
relay-log-info_repository=TABLE 　　 #Slave配置需要
binlog-format=ROW 　　　　    　　 #Slave配置需要
server_id=185
gtid-mode=on 　　　　　　　　　　#开启GTID需要
enforce-gtid-consistency=true 　　 #开启GTID需要
binlog-checksum=CRC32
master-verify-checksum=1
skip_slave_start=1

#配置MySQL启动脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on

#修改路径:
vim /etc/init.d/mysqld
....
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

#配置环境变量(追加)
echo 'export PATH=/usr/local/mysql/bin:$PATH'>>/etc/profile
#使变量生效
source /etc/profile

#创建其他相关文件
touch /var/log/mysqld.log
chown -R mysql.mysql /var/log/mysqld.log
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.log
chown -R mysql.mysql /var/log/mariadb/

#初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#注意:此时会有产生mysql的root用户登录密码,执行grep "localhost" /var/log/mysqld.log最后一串字符串就是,大概是这样的：CaMpm!9evsG_ 或者 faQ!BoB1Jytk

#启动数据库
/etc/init.d/mysqld start

#修改mysql的root用户登录密码为123456
mysql -uroot -p 　　 #输入上面筛选出来的密码
alter user user() identified by '123456';
grant all privileges on *.* to 'root'@'localhost' identified by '123456';
grant all privileges on *.* to 'root'@'%' identified by '123456';
exit
mysql -uroot -p 　　 #重新登录进行测试

#Master端配置Gtid
shell> mysql -uroot -p
mysql> create user zhangsan;
mysql> grant replication slave on *.* to 'zhangsan'@'%'identified by '123456';
mysql> flush privileges;

#Slave端配置Gtid
mysql> change master to
master_host='192.168.1.184',
master_port=3306,
master_user='zhangsan',
master_password='123456',
master_auto_position=1;

mysql> start slave;

mysql> show slave statusG;

#Master端测试

#Slave端测试

#Mycat配置

#安装JDK环境
#https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
#需要oracle账号才能下载
tar -zxvf jdk-8u211-linux-x64.tar.gz -C /usr/local/
mv /usr/local/jdk1.8.0_211/ /usr/local/jdk1.8
echo 'export JAVA_HOME=/usr/local/jdk1.8'>>/etc/profile
echo 'export CLASSPATH=$:CLASSPATH:$JAVA_HOME/lib/' >>/etc/profile
echo 'export PATH=$PATH:$JAVA_HOME/bin'>>/etc/profile
source /etc/profile
java -version