Kiến Thức Linux

How to Connect to MySQL Database

How to connect to MySQL database.

Introduction

Projects such as website building, data-driven application management, or data analytics frequently require access to databases. MySQL lets users connect to their databases via native and third-party command-line and GUI clients.

This article shows you how to connect to a MySQL database on Windows, macOS, and Linux.

How to connect to MySQL database.

Prerequisites

Connect to MySQL Database on Windows

During the installation of MySQL on Windows, you choose to perform additional installations of MySQL Workbench and the command-line client. These tools enable GUI and CLI access to MySQL and provide extensive database management features.

The following sections show how to connect to a MySQL database on Windows using MySQL Workbench and the command-line client.

Via MySQL Workbench

MySQL Workbench is the official MySQL GUI tool, available on Windows, macOS, and Linux. Follow the steps below to use MySQL workbench to connect to a database.

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

1. Open MySQL Workbench.

2. In the top menu, select Database, then click Connect to Database. Alternatively, select the plus sign next to the MySQL Connections section heading.

Opening the Setup New Connection dialog from the main page of MySQL Workbench.

The Setup New Connection dialog opens.

3. Fill out the necessary connection details, such as the name and method of the connection, hostname, port, and username.

4. Select the Store in Vault button to provide the password.

The location of the Store in Vault button for entering the user password.

5. Type the password in the dialog and press OK.

Entering password in MySQL Workbench.

6. Test the configuration parameters by selecting the Test Connection button located at the bottom-right corner of the dialog.

The location of the Test Button in the Setup New Connection dialog.

The pop-up window shows if the connection is successful.

7. Select OK to dismiss the window.

The Test Connection pop-up window.

8. Select OK in the bottom-right corner of the Setup New Connection dialog. The application returns to the main screen.

9. Select the newly created connection in the MySQL Connections section.

Selecting the connection from the main screen of the MySQL Workbench.

A database management window opens.

10. Select the Schemas tab in the Navigator section on the left side of the screen.

The location of the Schemas tab in the Navigator menu in MySQL Workbench.

11. Choose a database schema.

Choosing a database schema in the Schemas tab in MySQL Workbench.

MySQL Workbench is now connected and ready to manage the database.

Via Command Line

Connecting via the MySQL Command-Line Client allows you to use the MySQL CLI tools to connect to and manage your database. Follow the steps below to establish a CLI connection with a MySQL database:

1. Open MySQL Command-Line Client.

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.

2. Enter the root password when prompted.

The welcome message appears, followed by the mysql prompt.

The welcome prompt after entering the root password in MySQL Command Line Client.

3. Switch to your user by typing:

SYSTEM mysql -u [username] -p

Another welcome message appears alongside the prompt for the new user.

The welcome prompt after changing a user in MySQL Command Line Client.

4. Change to a specific database by typing:

USE [database];

The output confirms the change.

Changing to a specific database in Windows.

You can now issue CLI commands to manipulate the database.

Connect to MySQL Database on Mac

The following sections provide steps for connecting to a MySQL database using Sequel Ace, a third-party MySQL management tool for Mac, and the macOS Terminal.

Via Sequel Ace

Sequel Ace is free for download on the macOS App Store. Follow the steps below to use Sequel Ace to connect to your MySQL database.

1. Open Sequel Ace.

The main window allows users to establish a previously configured database connection or to create a new one.

2. Type the parameters of your connection, such as connection name, hostname, and user credentials. Test the connection by selecting the Test Connection button in the bottom-right corner of the window.

3. Select Connect when you finish inputting the parameters.

The main screen of Sequel Ace, with example parameters for connecting to a MySQL database.

A Database window opens.

4. Select a specific database in the Choose Database dropdown menu at the top of the window.

The location of the Choose Database dropdown menu in the database window of Sequel Ace.

Via Terminal

Mac users can access MySQL CLI from the Terminal app. The steps below show you how to connect to a MySQL database via the macOS command line.

1. Open a terminal app.

2. Enter the following command:

sudo /usr/local/mysql/bin/mysql -u [username] -p

3. When prompted, enter your user password.

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

The mysql prompt appears.

The welcome prompt after entering a MySQL user password in macOS.

4. Switch to a specific database:

USE [database];
Changing to a specific database in macOS.

Note: If you plan to use the MySQL CLI often, add MySQL’s bin directory to the PATH variable. For detailed instructions, read How to Set Environment Variables in macOS.

Connect to MySQL Database on Linux

The MySQL installation available in many Linux repositories comes with the mysql command installed in the path, which means no further setup is needed. Use the following procedure to connect to a MySQL database in Linux.

1. Open a terminal app.

2. Enter the following command:

sudo mysql -u [username] -p

3. Enter the sudo password and the MySQL user password.

The mysql prompt appears.

The welcome prompt after entering a MySQL user password in Ubuntu.

4. Change to a specific database:

USE [database];
Changing to a specific database in Ubuntu.

The MySQL client is now configured to manage your database.

Conclusion

After reading this tutorial, you should know how to connect to a MySQL deployment on Windows, macOS, and Linux. The article included both GUI and CLI methods for accessing MySQL databases.

Next, if you are new to MySQL, learn more about MySQL data types.

Đăng ký liền tay Nhận Ngay Bài Mới

Subscribe ngay

Cám ơn bạn đã đăng ký !

Lỗi đăng ký !

Add Comment

Click here to post a comment

Đăng ký liền tay
Nhận Ngay Bài Mới

Subscribe ngay

Cám ơn bạn đã đăng ký !

Lỗi đăng ký !