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 ...
    • 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 ...
    • 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 ...
    • Popular Articles

    • Examples for diagrams

      This tutorial shows how you can import and use the new examples for diagrams released with Scriptpcase V9.2.16.  The applications were created using the components: Jsmind, JKanban e Orgchart.  - Sample 1: Mind Map diagram Click here to  execute the ...
    • Oracle Connection - Scriptcase 9 - Linux 64 Bits

      This procedure will only work if you have a Scriptcase manual installation in your server. Installing PHP 7.0 Installing the Scriptcase Manually To connect with Oracle in a Linux System, we must follow these steps below: Step 1 -  Installing these ...
    • Correcting access error for the Scriptcase database

      Sometimes we encounter an error accessing the Scriptcase database itself, as shown in the image below:   This problem happens when we change the folder name of the scriptcase to another name, such as "sc". Then, the error happens because the complete ...
    • Necessary libraries to install the Scriptcase in Windows 7

      To install Windows 7 SP1, you need to verify that the system has the following items installed: * Visual C ++ Redistributable Packages for Visual Studio (2008) x86 and x64. * Visual C ++ Redistributable Packages for Visual Studio (2010) x86 and x64. ...
    • Manual Update - Windows

      Before performing any procedure we recommend performing a backup of your projects. To back up Scriptcase: Go to Settings > Administration, in the side menu go to Services > Backup. A file (.ZIP) will be generated with all your projects, save this ...