centos7下nginx+mycat实现负载均衡、数据库的主从复制、读写分离
序号 |
内网IP |
主机名Hostname |
服务service |
1 |
192.168.78.132 |
mysqlmaster |
mysql5.7、nginx |
2 |
192.168.78.133 |
mysqlslave |
mysql5.7、tomcat7、JDK1.8、mycat |
3 |
192.168.78.134 |
mysqlslave |
mysql5.7、tomcat7、JDK1.8 |
#关闭SELINUX sed -i 's/SELINUX=enforcing/SELINUX=disabled/'g /etc/selinux/config setenforce 0 #禁用系统防火墙 systemctl disable firewalld systemctl stop firewalld firewall-cmd –state #安装系统依赖 yum -y install iptables-services lrzsz libcgroup libtool-ltdl libseccomp policycoreutils-python crontabs sysstat tree telnet dstat iotop htop dos2unix nc nmap vim wget net-tools screen lsof tcpdump mtr libaio-devel bzip2 zip unzip #设置时区 timedatectl set-timezone Asia/Shanghai #设置对时 ntpdate cn.pool.ntp.org systemctl start ntpd #磁盘检查 df-h #主机名检查 hostname #如果不一致,修改hostnamectl set-hostname xxx |
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /software/
mv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql
useradd -r -s /sbin/nologin -g mysql mysql -d /software/mysql
chown -R mysql:mysql /software/mysql/
bin/mysqld --initialize --user=mysql --basedir=/software/mysql --datadir=/software/mysql/data/mysql
bin/mysql_ssl_rsa_setup --datadir=/software/mysql/data/mysql
cp mysql.server /etc/init.d/mysql
init_connect='SET collation_connection = utf8_unicode_ci'
datadir=/software/mysql/data/mysql
ln -s /software/mysql/bin/mysql /usr/bin/
增加用户权限:grant all privileges on *.* to 'root'@'%' identified by 'root123456';
chkconfig --level 345 mysql on
binlog-do-db=baizhi(要同步的数据库) binlog-ignore-db=mysql(忽略同步的数据库)
change master to master_host='192.168.78.132',master_user='root',master_password='root123456',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=154;
export PATH=/software/mysql/bin:$PATH
export JAVA_HOME=/software/jdk1.8
export MYCAT_HOME=/software/mycat
export PATH=$PATH:$JAVA_HOME/bin:$MYCAT_HOME/bin
6.4 修改/confg目录下的server.xml和schema.xml配置文件
[[email protected] conf]# cat schema.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--dataNode配置数据库地址,对应下发dataNode标签name属性值-->
<schema name="MYCATDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
database:物理数据库名,这里有数据库名为mycatdb
<dataNode name="dn1" dataHost="host1" database="db01" />
ataHost标签中的balance属性配置读写分离类型,默认为0,实际生产过程中设置为1(主双从)或者3(单主单从)
0:不开启读写分离,所有读操作都发送到当前可用的writeHost上
1:全部的readHost与stand by writeHost参与SELECT 语句的负载均衡
2:所有的读操作随机在writeHost和readHost上分发
3:所有读请求随机分发到readHost上执行,writeHost不负担读压力
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.78.132:3306" user="root"
<readHost host="hostS2" url="192.168.78.134:3306" user="root" password="root123456" />
<readHost host="hostS2" url="192.168.78.133:3306" user="root" password="root123456" />
<!--<writeHost host="hostS1" url="localhost:3306" user="root"
注意项目中数据库连接的url需要修改为安装mycat的机器的ip地址,数据库名为schema.xml配置文件中的数据库名
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.78.133:8066/TESTDB?useUnicode=true&characterEncoding=utf8