How do i create a database connection in mysql workbench?

• Last updated on July 26, 2021
• 7.1, 7.2, administrator, installation, on-prem, sql

This article provides the instructions to install and configure the MySQL database service with MySQL Workbench for Appspace on-prem.

• Prerequisite
• Install and Configure MySQL Server
• Configure MySQL Workbench

Prerequisite

• Download MySQL 8.x: https://dev.mysql.com/downloads/mysql/

Important

• Links to the installation instructions on the provider’s website are provided.
• Please ensure you take note of and complete the specific configuration required for each application, described in each step below.
• Please right-click each installer file and select Run as Administrator to install.

Install and Configure MySQL Server

Follow the instructions below to install and configure the MySQL Server with MySQL Workbench:

Warning

• SQL passwords should NOT contain shell metacharacters such as ! # ’ ” ` & ; or spaces.
• The database credentials CANNOT be changed once Appspace has been installed.
• The database user account must NOT be deleted once Appspace has been installed.

1. Install MySQL 8.x on your server, as outlined in the Installing MySQL on Microsoft Windows guide here: https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html 
2. Ensure the following configuration settings are made:
   • Select the Custom option on the Choosing a Setup Type window, and click Next to continue.
   • On the Select Products and Features window, ensure the following options are selected, and moved under the Products/Features To Be Installed section.
     
     
     
     • MySQL Server 8.x (x64 version)
     • MySQL Workbench CE 8.0.x (x64 version)

       Warning

       Only select these 2 options. Leave all the other options unchecked. It is important to make sure that the other options (such as MySQL Connectors) are NOT selected during the MySQL installation.

   • In the Group Replication window, select the Standalone MySQL Server/Classic MySQL Replication option.
   • In the Type and Networking window, do the following:
     
     • Select Server Machine from the Config Type drop-down menu.
     • Under Connectivity, check the TCP/IP checkbox, and set the Port Number to 3306.
     • Check Open Firewall port for network access.
     • Check Show Advanced and Logging Options.
   • In the Authentication Method window, select the Use Legacy Authentication Method (Retain MySQL 5.x Compatibility) option.
   • In the Accounts and Roles window, enter in the MySQL Root Password.

     Warning

     SQL passwords should NOT contain shell metacharacters such as ! # ’ ” ` & ; or spaces.

3. Proceed to configure the MySQL Workbench by following the instruction in the next section below.

Configure MySQL Workbench

Warning

Ensure the SQL root password created during installation has been granted System Administrator privileges in Windows before proceeding.

Follow the instructions below to configure MySQL Workbench:

1. Launch the MySQL Workbench from the desktop.
2. Click the Local instance MySQL80 button, and click Connect to begin the configuration process.
3. When prompted, enter in the MySQL server root password which was created during the MySQL installation process. Optionally, you may check the Save password in vault check box. Click OK to continue.
4. In the Navigator pane, click Options File under the INSTANCE section to access the Administration – Options File tab.
   
5. Click the General tab, and scroll down to the SQL section.
6. Edit the sql-mode text field by adding the following to the existing string (before the closing quotation marks): ,ANSI_QUOTES
   

   Warning

   Ensure the comma is added, and do NOTleave any blank spaces.

7. Click the Networking tab, and scroll down to the Timeout Settings and Advanced sections. Check the following check boxes, and set its values as stated:
   
   • interactive_timeout, set the value at 30.
   • wait_timeout, set the value at 30.
   • max_connect_errors, set the value at 300.
   • max_connections, set the value at 500.
8. Click the Security tab, and uncheck the secure-file-priv option in the Security section.
9. Click Apply once all configurations have been done.
10. The Apply Changes to MySQL Configuration File window is displayed, and lists all the configurations that were just made. Click Apply to confirm the changes.
11. In the Navigator pane, click Startup / Shutdown under the INSTANCE section to access the Administration – Startup / Shutdown tab. The database server instance is status should display running. Click Stop Server to stop the MySQL server.
    
12. Once The database server instance is status displays stopped, it can be confirmed that the MySQL server has shutdown.
13. Click Start Server to restart the MySQL server.

Next Steps: Proceed back to the Configure Folder Permissions section in the Appspace On-prem installation article.

Was this article helpful?

Related Articles

Latest Articles

Browse By Category

• Announcement (1)
• Appspace App for Devices (55)
• Appspace Employee App (34)
• Cards (120)
• Channels (62)
• Cloud (37)
• Communication Cards (11)
• Content & Library (33)
• Data Cards (13)
• Developer (9)
• Devices (69)
• Digital Signage (61)
• Employee Communication (25)
• Feed Cards (36)
• Guided Paths (35)
• Guides (30)
• Help (7)
• How-to (304)
• Kiosks (16)
• Limited Release (2)
• Marlin-Upgrade (11)
• Platform (86)
• Premium Cards (13)
• Release Notes (41)
• Security (17)
• Social Cards (6)
• Space Reservations (39)
• Visitor Management (17)
• Workplace Cards (22)

How do I create a connection in MySQL Workbench?

How do I create a database connection in MySQL?

How do I connect to a local database in MySQL Workbench?

How do I create a database connection?