鄙人的环境virtualbox+centos7

1.mysql8安装

（ip addr 查看本机ip以及网络模式是否为桥接模式 如果ip：127.0.0.1 请vi /etc/sysconfig/network-scripts/ifcfg-enp0s3，修改属性onboot=yes）
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo yum --enablerepo=mysql80-community install mysql-community-server

修改配置my.cnf

[mysqld]
lower_case_table_names = 1
character-set-server = utf8mb4
default_authentication_plugin=mysql_native_password
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysql]
default-character-set = utf8mb4
[client]
default-character-set = utf8mb4
sudo service mysqld start
service mysqld status
grep 'password' /var/log/mysql.log
validate_password.length 是密码的最小长度，默认是8，我们把它改成6
输入：set global validate_password.length=6;
validate_password.policy 验证密码的复杂程度，我们把它改成0
输入：set global validate_password.policy=0;
validate_password.check_user_name 用户名检查，用户名和密码不能相同，我们也把它关掉
输入：set global validate_password.check_user_name=off;
alter user 'root'@'localhost' identified by 'siti@bike001';
CREATE USER 'sky'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON . TO 'sky'@'localhost' WITH GRANT OPTION;

2.mysql8主从配置：

主服务器配置

在mysqld下增加如下参数
[mysqld]
server_id=1
log-bin=mysql-bin
binlog-format=ROW
binlog-do-db=DB1
binlog-ignore-db=mysql

添加复制用户

CREATE USER 'rootslave'@'从服务器ip地址' IDENTIFIED WITH mysql_native_password BY 'root123';#创建用户
grant replication slave on *.* to 'rootslave'@'从服务器IP地址'；#授权

从数据库配置

[mysqld]中配置如下
server-id=2
log-bin=mysql-bin
binlog-format=ROW
replicate-do-db=test_master
replicate-ignore-db=mysql

slave跳过错误有两种方式：

1.跳过指定数量的事务：
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务
mysql>slave start
2.修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
slave-skip-errors=all #跳过所有错误

在从数据库中执行下面指令

mysql> CHANGE MASTER TO
-> MASTER_HOST='服务器主机地址',
-> MASTER_USER='用户名',
-> MASTER_PASSWORD='密码!',
-> MASTER_LOG_FILE='binlog.000001',
-> MASTER_LOG_POS=1807;
Query OK, 0 rows affected, 1 warning (0.07 sec)
开始同步()
mysql> START SLAVE;
查看Slave的运行状态
mysql> SHOW SLAVE STATUS\G

3.jdk8安装(mycat需要)

1. yum search java|grep jdk
   2.yum install java-1.8.0-openjdk*
   3.which java
   ls -lrt /usr/bin/java
   ls -lrt /etc/alternatives/java
   4.vi /etc/profile
   5.#set java environment
   JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.232.b09-0.el7_7.x86_64/
   CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
   PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
   export JAVA_HOME JRE_HOME CLASS_PATH PATH
   6.java-version

4.mycat安装

wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

查看已开放的端口
firewall-cmd --list-ports
开放端口（开放后需要要重启防火墙才生效）
firewall-cmd --zone=public --add-port=3338/tcp --permanent
重启防火墙
firewall-cmd --reload
关闭端口（关闭后需要要重启防火墙才生效）
firewall-cmd --zone=public --remove-port=3338/tcp --permanent
开机启动防火墙
systemctl enable firewalld
开启防火墙
systemctl start firewalld
删除
firewall-cmd --zone= public --remove-port=80/tcp --permanent
禁止防火墙开机启动
systemctl disable firewalld

停止防火墙
systemctl stop firewalld
tar -xzvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz -C /usr/local

修改环境变量

mycat env

export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
source /etc/profile

修改/usr/local/src/mycat/wrapper.conf,jdk启动路径

wrapper.java.command=%JAVA_HOME%/bin/java

mycat启动命令 mysql -umycat -pmycat123 -P9066 -h199.3.10.184 --default-auth=mysql_native_password(切记)

mycat配置文件

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="defaultSqlParser">druidparser</property>
<property name="useHandshakeV10">1</property>
<property name="mutiNodeLimitType">1</property>
<property name="charset">utf8</property>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
</system>
<user name="root" defaultAccount="true">
<property name="password">root</property>
<property name="schemas">testdb</property>
</user>
</mycat:server>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" >
<table name="masterslave" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="host108" database="test" />
<dataHost name="host108" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>

<writeHost host="hostM1" url="10.1.4.82:3306" user="dc"
password="siti@bike001">
</writeHost>
</dataHost>
</mycat:schema>

wrapper.conf一定要修改

wrapper.java.command=%JAVA_HOME%/bin/java

常见问题

不能创建connection

登陆mysql flush hosts