Клиент для подключения к mysql windows

To interact with MySQL databases, it’s common to employ MySQL GUI clients. They enable users to visually view, create and modify database objects such as tables, rows, and columns. Some familiar features of MySQL GUI clients include SQL generator and export data, which makes designing, creating, and administering MySQL databases easier and more convenient. Here, we gathered 5 best MySQL GUI Clients on the market right now.

The Official: MySQL Workbench#

MySQL Workbench is a free database design and model access tool for MySQL, meant for database architects, developers, and of course, DBAs. It is available on Windows, Linux, as well as MacOS. Since the official MySQL vendor offers it, it looks like it’s going to be free and maintained for the foreseeable future.

MySQL Workbench’s main features can be grouped threefold:

1. Database design and modeling: you can create and edit your databases, tables, and their relationships visually. The ER diagram comes in handy for complex database architecture.

   

   Картинка с сайта: www.bytebase.com

2. SQL development: with the built-in SQL editor, you can build, edit, and run SQL queries against MySQL databases. It includes some useful mechanisms to aid in writing and debugging SQL statements, including color syntax highlighting, context-sensitive help, and auto-complete.

   

   Картинка с сайта: www.bytebase.com

3. Server Administration: MySQL Workbench offers a centralized platform to manage user accounts, export/import data, backup/restore databases, monitor server performance, and more to ease admin work.

   

   Картинка с сайта: www.bytebase.com

The Old School: phpMyAdmin#

phpMyAdmin is a web-based interface to MySQL and MariaDB written in PHP that was first released back in 1998. It’s open-source and free to use. For over 20 years, phpMyAdmin remains one of the most popular administration tools for MySQL databases, with a large community of users and contributors.

A range of features are available (managing databases, tables, users, permissions, etc) and can be performed via the user-friendly interface, you can also execute SQL queries directly. However, being web-based has pros and cons: phpMyAdmin is available on all the platforms with a web browser, yet it can be prone to security attacks such as SQL injection, so make sure to take proper precautionary measures.

Картинка с сайта: www.bytebase.com

BTW, Adminer, formerly called phpMinAdmin, as the name suggests, is a mini phpMyAdmin version.

The Power Couple: Navicat & DBeaver#

Navicat#

Navicat’s first release came in 2002 and back then, it was a simple application only available for MySQL on Windows. Now it’s available on macOS and Linux, with a long list of compatible databases, including Redis, PostgreSQL, SQL Server, Oracle, MariaDB, SQLite, MongoDB, and a handful of cloud databases.

It is not open-source, nor does it have a free offering, you can only choose between the premium and lite (with a compact list of features and database support as compared to the premium versions.

With Navicat, you can connect to multiple databases on a single GUI, which is convenient to manage and compare data across different platforms.

Картинка с сайта: www.bytebase.com

Other features Navicat offers include data modeling and design, data synchronization between databases, backup and restore, data import/export, and SQL Development (it has a built-in SQL editor for users to write and optimize SQL queries).

Overall, Navicat is a comprehensive database management tool for novice and experienced users.

DBeaver#

Unlike Navicat, which only offers commercial versions, DBeaver offers both open-source and commercial products. It started as a hobby project back in 2010 and was open-sourced for use in 2013. The OS version caters to most database management and administration needs, while the commercial version extends the capabilities with additional advanced features like reverse engineering, data modeling, collaboration tools, and tech support.

Картинка с сайта: www.bytebase.com

Currently, it supports 80+ databases (SQL, NoSQL, document-oriented, key-value, big data, cloud, you name it). DBeaver is a desktop client, if you prefer web-based tools, they also have CloudBeaver.

The Starlet: TablePlus#

Starting in 2017, TablePlus is the newbie on the list, and its modern and simple UI reflects it. It supports most relational databases and some NoSQL ones. When they just started, they only supported macOS, but it is now available on Windows, Linux, and iOS (!). It is not open-source, but the roadmap is open and anyone can open an issue on their GitHub Issue Tracker. TablePlus has two plans: a free tier (has no limit on trial time) and a paid subscription model (license) with extended features.

Картинка с сайта: www.bytebase.com

It’s worth mentioning that DBngin, a tool to spin up a local database server (currently supports PostgreSQL, MySQL, and Redis) on your Mac, belongs to TablePlus and is open-source. You can connect it to TablePlus, meaning you can manage your local databases visually all in one go.

Final Thoughts#

MySQL GUI Clients can be used to help you manage databases with more confidence. However, choosing the right option for your organization is important.

Any of the aforementioned provides a UI for users to operate on databases, a SQL Editor, and the ability to export data. On the other hand, if your organization needs are beyond those and demands an extra layer of control over database queries, changes, and admin actions, you can try our Bytebase, an open-source Database DevOps and CI/CD tool for teams, designed to centralize the control and secure your organization’s most valuable asset, the database data.

Картинка с сайта: www.bytebase.com

Further Readings#

• Top MySQL Schema Compare Tool to Diff and Sync Database
• Top Open Source SQL Clients
• Top Database Schema Migration Tools
• Top DBeaver Alternatives

Note: The process for connecting to MySQL deployments on macOS and Linux using MySQL Workbench is the same as in Windows.

Note: If you expect to use Unicode characters in working with the database, find and open MySQL Command-Line Client — Unicode, which offers the same CLI experience but provides Unicode support.

Note: The sudo command precedes the mysql command, so the system may first ask you for the sudo password.

Was this article helpful?

YesNo

• Introduction
• Connect to MySQL
• Setup libmysql
• Making the MySQL Database Option Visible in Windows
• Troubleshooting
• More Information

Introduction

This article explains how to connect to MySQL databases for Windows, Linux and Mac.

It covers the following topics:

1. How to connect to MySQL (with code samples).
2. How to setup the libmysql shared library:
   • Install libmysql
   • Configure Iguana to use libmysql

If you are having trouble getting Windows to recognize MySQL you should look at the last section Making the MySQL Database Option Visible in Windows.

Note: If you are using a 64 bit Windows you should usually use a 64 bit version of MySQL. If you are using 32 bit Windows you will need to use an older 32 bit version of MySQL (5.7.24 or earlier).

Connect to MySQL [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The code samples will work with all operating systems.

Follow these steps to connect:

1. Create a new channel to try out the code:
   • Create a channel like this:
     • Name: DB MySQL Connection (or similar)
     • Source: From Translator
     • Destination: To Channel
   • Alternatively use an existing channel:

     Your channel will need a From/To Translator or a Filter component.

2. Connect to a database using the libmysql shared library:
   • Paste this code into the Translator:

     If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote MySQL database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

     function main()
           local conn = db.connect{   
           api=db.MY_SQL, 
           name='mydatabase@server_name',
           user='your_login', 
           password='secret',
           use_unicode = true,
           live = true
        }
     
        conn:execute{sql='SELECT * FROM <your table>', live=true}
     end

   • The location (name) of the host can be specified as any of the following:
     • The machine name: mydatabase@server_name
     • The server name: mydatabase@server_name
     • The IP address: mydatabase@10.10.10.10
   • If the MySQL server is using a port other the standard port of 3306, you can specify it as part of the name parameter:
     • mydatabase@server_name:1111
3. Adapt the code to your requirements.

Setup libmysql [top]

Windows:

The easiest way to install the drivers on Windows is to use an MSI installer rather than a ZIP archive, as the install process is simpler.

Note: The procedure and screenshots in this section are from Windows 10 — and may differ somewhat if you are using a different version of Windows.

1. Download MySQL:
   • Go to the MySQL download site
   • Download the MSI install file for the latest version of MySQL for Windows
2. Install the MySQL drivers:
   • Double click the MSI file to open it.
   • Click Yes to allow the installer to make changes on your machine.
   • Choose Client Only to install the client tools which includes the drivers:
     

     Картинка с сайта: help.interfaceware.com

   • Click Next.
   • Ignore the two failed Check Requirements:
     

     Картинка с сайта: help.interfaceware.com

     • Click Next to continue.
     • Click Yes to continue in the warning dialog box.
   • Follow the remaining steps to finish installing the MySQL client tools:
     • We strongly recommend accepting the default install values.
     • Note: Do not configure MySQL Router — just click Finish.
3. In Iguana, click the Settings tab.
4. Click IGUANA Setup > Database to view the available database APIs.
5. Click the Edit button for MySQL Compliant:
   

   Картинка с сайта: help.interfaceware.com

6. Load the MySQL shared library for the version of MySQL you installed:

   We suggest various paths for the MySQL Library as this can change depending on the version of MySQL that you have installed. You should use the the latest driver which will normally be in the install directory that you use for MySQL.

   • Try these library paths:
     • C:\Program Files\MySQL\MySQL Server 8.0\lib\libmysql.dll
     • C:\Program Files\MySQL\MySQL Workbench 8.0 CE\libmysql.dll
     • C:\Program Files\MySQL\MySQL Router 8.0 CE\libmysql.dll
     • Otherwise search for libmysql.dll in the MySQL install directory
   • Paste the path into the Preferred Version text box:
     

     Картинка с сайта: help.interfaceware.com

   • If you installed MySQL in a different location (than the default) then you can browse to find the libmysqlclient.dll driver file.

Linux:

1. Download MySQL:
   • Go to the MySQL download site
   • Choose Linux – Generic from the Select Operating System dropdown.
   • Download the TAR archive file for the latest version of MySQL.
2. Install the libmysql driver:
   1. Terminal Commands: Unpack the TAR archive:

      This assumes file is <home>/Downloads, otherwise change the source path to match.

      cd ~/Downloads
      tar xvf ~/Downloads/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

   2. Copy the driver to the drivers directory:

      This directory can differ on different versions of Linux. For Ubuntu (and other Debian based Linux) use /usr/local/lib, for CentOS (and other Red Hat based Linux) use /usr/lib.

      1. Terminal Command: To copy the drivers for Ubuntu:

         Replace <version> to match the path where you unpacked the TAR file.

         sudo cp ~/Downloads/mysql-<version>-linux-glibc2.12-x86_64/lib/libmysqlclient.so* /usr/local/lib

      2. Terminal Command: To copy the drivers for CentOS:

         Replace <version> to match the path where you unpacked the TAR file.

         sudo cp ~/Downloads/mysql-<version>-linux-glibc2.12-x86_64/lib/libmysqlclient.so* /usr/lib

3. In Iguana, click the Settings tab.
4. Click IGUANA Setup > Database to view the available database APIs.
5. Click the Edit button for MySQL Compliant:
   

   Картинка с сайта: help.interfaceware.com

6. Load the MySQL shared library:
   •  Choose Automatically choose which OCI Oracle shared library to load.
   • Alternatively (if Iguana does not find the driver) you can Browse to find the Library you just installed and click Save Changes:
     

     Картинка с сайта: help.interfaceware.com

7. Your installed library should look something like this:
   

   Картинка с сайта: help.interfaceware.com

Mac:

The easiest way to install the libmysql shared library on a Mac is to install the full MySQL database server. We recommend using a DMG archive rather than a TAR archive, as the install process is simpler.

But you may find it easier to use ODBC connections on the Mac instead.

Note: The procedure and screenshots in this section are from macOS Mojave — and may differ somewhat if you are using a different version of macOS.

Tip: When you install the MySQL on Windows the installer includes a “drivers only” option. There is no “drivers only” option for the MacOS installer — so you need to install the full database engine.

1. Download MySQL:
   • Go to the MySQL download site
   • Download the DMG install file for the latest version of MySQL for the MacOS
2. Install MySQL:
   • Double click the DMG file to open it.
   • Double click the package to run it:
   • Follow the steps to install MySQL:

     We strongly recommend accepting the default install values.

3. In Iguana, click the Settings tab.
4. Click IGUANA Setup > Database to view the available database APIs.
5. Click the Edit button for MySQL Compliant:
   

   Картинка с сайта: help.interfaceware.com

6. Load the MySQL shared library:

   If you are using an older version of MySQL it may use libmysqlclient_r.dylib instead of libmysqlclient.dylib.

   • Use the default library path: /usr/local/mysql/lib/libmysqlclient.dylib
   • Paste the default path into the Preferred Version text box:
     

     Картинка с сайта: help.interfaceware.com

   • If you installed MySQL in a different location (than the default) you will need to browse to find the libmysqlclient.dylib driver file.

Making the MySQL Database Option Visible in Windows [top]

You must have MySQL installed and the system PATH must be set correctly to ensure that the MySQL database option is visible in the database API drop-down menu.

Картинка с сайта: help.interfaceware.com

Note: The screenshots in this section are from Windows 10 — the screens will be different for other versions of Windows.

To make the MySQL database option visible:

1. Search for “Environment” and choose Edit the system environment variables:
2. The System Properties window will open on the Advanced page:
   

   Картинка с сайта: help.interfaceware.com

3. Click the Environment Variables button near the bottom of the page:
   

   Картинка с сайта: help.interfaceware.com

4. Highlight the System Path variable and click Edit:
   

   Картинка с сайта: help.interfaceware.com

5. The Edit environment variable window appears.
6. Add the location of your libmysql.dll file on the next line:
   

   Картинка с сайта: help.interfaceware.com

7. Click OK to save your changes.
8. Restart your computer for the changes to take effect.
9. The MySQL database option should now be visible in the API drop-down menu:
   

   Картинка с сайта: help.interfaceware.com

Troubleshooting [top]

• Problems with connection settings:

  Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

More Information

• Using Database Connection Objects
• Forum thread on Windows ODBC
• Connect to MySQL from Windows with ODBC
• Connect to MySQL from Linux or Mac with ODBC