How to connect to MySQL 8.0

How to connect to MySQL 8.0

The MySQL 8 uses a different authentication from your predecessors, that until the moment it's not accepted by PHP 7, that generates the error "The server requested authentication method unknown to the client" at connecting to Scriptcase.

First, you need to create a new user, because as the current MySQL user by default uses the caching2_sha2_password authentication type the connection will not be made using it.

We will create the new user using the old authentication to make the connection possible.

After this configuration, you will need to change the default MySQL authentication type.

To make this change you need to follow the error tutorial connecting to the MySQL8 database.


Creating an user using some database management tools.

If you are using some of this tools below, we will show how create a new user to your MySQL through them.

  1. NAVICAT
  2. SQLYOG
  3. WORKBENCH
  4. TERMINAL/CMD

NAVICAT

1. Open the Navicat, and next, connect to a desired database server.

2.  Open the connection with any database in this server.

3. Open the SQL commands execution window, in "Query>New Query".

4. Execute this 3 SQL commands below, in order.

Command to create the new user with the old authentication:

CREATE USER newuser@"%" IDENTIFIED WITH mysql_native_password BY'password';

Command to grant permissions to the new user:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';
FLUSH PRIVILEGES;



SQLYOG

1- To connect to a database server by SQLyog, type the commands in the "Query" tab and execute them.

Command to create the new user with the old authentication:

CREATE USER newuser@"%" IDENTIFIED WITH mysql_native_password BY'password';

Command to grant permissions to the new user:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';
FLUSH PRIVILEGES;



WORKBENCH

1- Open one connection with a database server or create a new connection.


2- Type the commands in the "Query" tab and execute them.

Command to create the new user with the old authentication:

CREATE USER newuser@"%" IDENTIFIED WITH mysql_native_password BY'password';

Command to grant permissions to the new user:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';
FLUSH PRIVILEGES;


CMD/TERMINAL


1. Access MySQL through Terminal.

Linux: Just open the terminal and use the MySQL command passing the parameters to make the connection.

-h = Server Ip (If on the same machine not required)

-P = Server Port (If on the same machine not required if default)

-u = User (Required)

-p = Password (Required)

Windows: If you are not using the application to directly open the MySQL terminal, you will need to navigate to the MySQL folder.

cd "c:\Program Files\MySQL\MySQL Server{Your version}\bin"

So, access MySQL


2. Next, create a new MySQL user with the older MySQL authentication.

The command mysql_native_password is the one that should be used.


Command to create the new user with the old authentication:

CREATE USER newuser@"%" IDENTIFIED WITH mysql_native_password BY 'password';


Command to grant permissions to the new user:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';
FLUSH PRIVILEGES;



3. In the Scriptcase, in creating the connection to MySQL, use the new user to create the connection.




For New MySQL Installations


For new installations, we are able to select the type of authentication.

In this case, we must select  Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)


Installing the MySQL with this option selected the connection to Scriptcase can be created without any problem.


    • Related Articles

    • Error connecting mysql database 8

      In this tutorial, we will see how to connect to a MySQL database that prohibits access when you have PHP 7 with MySQL 8. First, you must follow the manual Creating a new MySQL User. 1- Once the creation of the new user has been done, you must go to ...
    • Melhorando performance do banco de dados com MySQL Tuner

      Você pode baixar todo o repositório usando ‘git clone’ seguido pela URL clonagem acima. O método mais simples e mais curto é: wget http://mysqltuner.pl/ -O mysqltuner.pl wget ...
    • Connection error: error_profile_test_module

      When we deploy a project and we find this type of error: "Connection error: error_profile_test_module" This error normally occurs when you use a different production environment DBMS Type than the development environment. To solve this, access your ...
    • MSSQL Server Connection - Scriptcase - Mac OS X

      Currently we must set up the connection to SQL Server on computers running Mac OS X according to the steps below: Step 1 - We should access the connection setup, in this case MSSQL Server Click on "Set FreeTDS". Step 2 - We must set the data to ...
    • Updating Scriptcase's PHP

      Currently, the installation of Scriptcase 9.x comes with version 7.0.14 of PHP, unfortunately, this version has a compatibility problem with the new version of authentication of MySQL 8.0, so it is necessary to perform the PHP update so the ...