Mycat currently does not fully support versions of MySQL 8 or above, which may cause problems such as incorrect passwords when Mycat connects to MySQL 8 because the new version does not encrypt the password the same way as the old version.There is also the time zone issue, and the new version of the connection requires additional time zone parameters.In addition, there may be other problems.Therefore, we need to change some of Mycat's configuration so that it can properly connect to MySQL 8.x.

Download 8.x driver

You first need to download the 8.x driver, which Mycat defaults to 5.x for MySQL.reach maven central warehouse Copy the download address of the 8.x driver jar package on and download it on Linux using the wget command:

[root@txy-server ~]# cd /usr/local/src
[root@txy-server /usr/local/src]# wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.18/mysql-connector-java-8.0.18.jar

Move the driver package to mycat's lib directory and delete the original 5.x driver package:

[root@txy-server /usr/local/src]# mv mysql-connector-java-8.0.18.jar /usr/local/mycat/lib/
[root@txy-server /usr/local/src]# rm -rf /usr/local/mycat/lib/mysql-connector-java-5.1.35.jar

Next, use the chmod command to set the permissions for the driver package:

[root@txy-server /usr/local/mycat]# chmod 777 lib/mysql-connector-java-8.0.18.jar 

Modify schema.xml

Modify the schema.xml configuration file located in the conf directory to change how Mycat connects MySQL:

[root@txy-server /usr/local/mycat]# vim conf/schema.xml

Modify the dbDriver property in the dataHost tag to jdbc and the url property in the writeHost tag:

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" 
               url="jdbc:mysql://192.168.130.28:3306?useSSL=false&serverTimezone=GMT%2B8" 
               user="root"
               password="123456">
    </writeHost>
</dataHost>

Modify server.xml

Modify the server.xml configuration file located in the conf directory:

[root@txy-server /usr/local/mycat]# vim conf/server.xml

Set useHandshakeV10 to 1, or add it if you don't have it:

<system>
    <property name="useHandshakeV10">1</property>
    ...
</system>

Restart Mycat and test

Restart Mycat:

[root@txy-server /usr/local/mycat]# mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
[root@txy-server /usr/local/mycat]# mycat start
Starting Mycat-server...
[root@txy-server /usr/local/mycat]# 

Another problem encountered while testing whether Mycat could be properly connected was that a password error was reported when Mycat was connected using the MySQL 8.x client tool:

[root@txy-server ~]# mysql -uroot -p -P9066 -h127.0.0.1
Enter password: 
ERROR 1045 (HY000): Access denied for user 'root', because password is error 
[root@txy-server ~]#

Similarly, this is because versions above MySQL 8 do not encrypt passwords the same way as previous versions.Mycat currently only supports 5.x encryption, so password errors will be reported when Mycat is connected using more than 8 versions of the MySQL client tools.

There are two main solutions: one is to specify the encryption method for mysql client connections.Examples include the following:

[root@txy-server ~]# mysql -uroot -p -P9066 -h127.0.0.1 --default-auth=mysql_native_password

The second is to use the 5.x version of the MySQL client tool to connect Mycat.Examples include the following:

[root@txy-server /usr/local/mysql-5.7.28]# bin/mysql -uroot -p -P9066 -h127.0.0.1