preface

Some time ago to install MySQL8.0 database, database to prepare for Oracle. Now the company is planning to build a MySQL cluster. So I picked two applications and tested them to gain experience.

The migration tool

During the test, Powerdesinger was used for table structure conversion and Navicat was used for data import. In the production environment, there is a large amount of data. Therefore, the data group will use other tools to migrate data and supplement data at that time.

Application of modified

Add the mysql8.0 driver package

Mysql-connector-java-8.0.15. jar = mysql-connector-java-8.0.15.jar = mysql-connector-java-8.0.15.jar = mysql-connector-java-8.0.15.jar

<! --> <dependency> <groupId> MySql </groupId> <artifactId>mysql-connector-java</artifactId> < version > 8.0.15 < / version > < scope > runtime < / scope > < / dependency >Copy the code

Modifying data Source Configuration

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://host:ip/database? useUnicode=true&characterEncoding=utf8 spring.datasource.username=root spring.datasource.password=passwordCopy the code

Object reconstruction

  • If you are using Hibernate, remove the primary key increment sequence from the configuration file and set primary key increment to mysql. Or create corresponding functions for sequences. The primary key generation will also change with mysql clusters.
  • Check SQL statement by statement, especially in mybatis, including:
    1. Primary key modification: delete sequence sequence, change the primary key in the database to increment; Or create the corresponding increment function
    2. Fields involving mysql keywords are processed using ‘ ‘identifiers
    3. Date format processing
    4. Change the rownum conditional query to limit conditional query

The problem summary

Error: Local remote connection to mysql database, report 10060 login exception

  • Possible causes of this problem:

1. The network is unavailable. 2. The service is not started. 3. The firewall is not closed; 4. The firewall port on the server is not open. 5. The port is not monitored. 6. Insufficient permissions. I am here to find the reason why port 3306 on the test database server is not open.

  • Solutions:
sudo vim /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
sudo service iptables restart
sudo iptables -L -n
Copy the code

Error 2059 occurred when Navicat connected to MySQL8

  • Cause: Before mysql8, the encryption rule was mysql_native_password. After mysql8, the encryption rule is caching_sha2_password
  • Solution: Change the encryption rules
Mysql -uroot -ppassword ALTER USER 'root'@'%' IDENTIFIED BY 'password' password EXPIRE NEVER; ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; # Update user password FLUSH PRIVILEGES; # refresh permissionCopy the code

> [Err] [Dtf] 1426-too-big precision 7 specified for ‘TIME_CREATE’. Maximum is 6.

  • Cause: The DATE type of Oracle is 7 bits, while the time type of mysql is 6 bits at most, so it cannot be imported.
  • Solution: Change the oracle database DATE to TIMESTAMP, at the same time change the length of 6 (must change the type and length of both before saving), can import.

Error: Data too long for column ‘DESIGNER’ at row 1

  • Cause: Oracle and mysql use different encoding sets, so even if the same character, the storage length requirements are different (need further understanding).
  • Solution: Need to extend field length in MSYQL

Unknown system variable ‘query_cache_size’

  • Cause: The version of the jar package used by the mysql driver is too early and incompatible with mysql8.0
  • Jar: use mysql-connector-java-8.0.15.jar and change the Driver name to com.mysql.cj.jdbc.driver

Problem: After changing to mysql, garbled characters are displayed on the application front page

  • Cause: Check the encoding format from the database, server, and page
  1. Check the MySQL database encoding format
  2. Check the server encoding format
  3. Check front-end page coding format

Finally, it was found that the encoding format was set to UTF-8 in Navicat connection, so that the imported data can be seen in Navicat normally, but the database is garbled, and the query result is also garbled. This really checked for a long time, did not pay attention to the reason of the tool.

  • Solution: Reset the encoding format, import data, display normal

reference

Mysql keyword table