How to use MySQL 8.0.4 with a GUI

If you want to have a look on what is about to come in the new version of the popular database and is used to Syntax Highlighting you don’t need to be chained to the Terminal.

Some of you may use tools like MySQL Workbench or Sequel Pro (as of the release of this post both tools had the following error occurring), and even if you are using the Terminal (if you are using an old version of mysql​, like 5.7) you may encounter this error:

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Authentication plugin ‘caching_sha2_password’ cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

The reason for that is because since the 8.0.4 RC release, MySQL now uses SHA-2 Pluggable Authentication. In another words, how the database does authentication now changed.

Graphical User Interface

As of now, the only tool I could verify that it is working is Datagrip. But there is some steps to make sure you can successfully connect to the server. Follow the steps

1. The JDBC Connector

  1. Open the JDBC Connector page. Click on “Development Releases” tab and select your operating system, as of this post 8.0.9 was the latest version.
  2. Select the zip version of the file, if you are using macOS, select “Platform Independent”.
  3. The website it will ask for you to login, you don’t need to login, there is a link on the bottom of the page that says: “No thanks, just start my download.”.
  4. Unzip the zip​ file mysql-connector-java-8.0.9-rc.zip (the name may be different for you if the version is different)
  5. A folder will be created with the name of the compressed file, inside copy the jar file to a location where you can access it later easily, for example, I put mine in ~/workspace/drivers folder.

2. The GUI configuration

  1. Open Datagrip. Go to “File > Data Sources“. A window will open, right click on top of the “MySQL” name and select Duplicate.
  2. A new Driver is added with the name “MySQL [2]”, rename it to “MySQL 8.0”
    Data_Sources_and_Drivers.png
  3. Then, unselect “Use” on “Driver Files” and click on the + sign. Select the jar file you downloaded on the previous section.
    Data_Sources_and_Drivers.png
  4. Click in Apply.

3. Adding the source

  1. On the same window, click on the + sign on the top left. Select “MySQL 8.0”
    Screen Shot 2018-02-22 at 14.50.28.png
  2. Fill out the details as you would for a connection: Data_Sources_and_Drivers
  3. Click on “Test Connection“.
  4. If everything worked, just click in “OK” to exit the screen.

6 thoughts on “How to use MySQL 8.0.4 with a GUI”

  1. J’ai arreté d’utiliser MysqlWorkbench parce qu’il crash avec les tables mariadb contenant des champs json.
    Je suis passé à DBeaver et franchement j’y retrouve sous linux, le confor que j’avais avec SqlYog.
    I stopped using MysqlWorkbench because it crashed with mariadb tables containing json fields.
    I switched to DBeaver and frankly I found, in linux environment, the confor I had with SqlYog (but windows only).

    Reply

  2. Thanks for the sharing! Gabriela!
    In addition to what you have said, if anyone test connection and didn’t work, try changing these two variables:
    When creating a Project using MySQL 8.0 (ready to test connection), go to advanced tab,
    change
    defaultAuthenticationPlugin to: com.mysql.cj.mysqla.authentication.CachingSha2PasswordPlugin
    zeroDateTimeBehavior to: CONVERT_TO_NULL

    fixed my problem.

    Reply

  3. Hello Gaby, I am trying to do the same trick in PyCharm 2017.3, but I am getting the “Could not create connection to database server. Attempted reconnect 3 times. Giving up” error message, do you have any ideas?

    Reply
  4. Pingback: How to use MySQL 8.0.4 with a GUI - SSWUG.ORG

  5. Thanks for sharing, Gabriela!
    I used Sequel Pro to connect to MySQL 8 and after running a few queries, it crashed. That happened quite frequent so I looked for another tool. I never tried DataGrip but I found TablePlus. It’s a great GUI tool that works well with MySQL 8 and also supports a handful list of other popular relational databases. The design is pretty intuitive. I think you should check it out too 🙂
    https://tableplus.io/

    Reply

Leave a Reply