MySQL Command-Line Client Setup & Usage Guide

What is a MySQL client?

MySQL client is a common name for tools designed to connect to MySQL Server. Client programs allow sending commands or queries to the MySQL Server and managing data in the databases stored on the server. Simply put, MySQL Server holds data, and MySQL Client accesses that data and enables data manipulation. Therefore, the answer to the question "What is a MySQL Command-Line Client?" is quite apparent.

When referring to MySQL Client, users often mean the MySQL Command-Line Client, a lightweight CLI utility included in the MySQL package. This MySQL command prompt lets you issue various commands to the MySQL server, such as creating or dropping tables or entire databases, adding columns and rows, fetching data, and much more. Another option worth mentioning when talking about accessing MySQL from the command line is MySQL Shell.

MySQL Shell is an advanced MySQL client that enables users to develop, manage, and administer MySQL databases from the command line. It also comes as a component of the MySQL Server package. MySQL Shell is a significantly more advanced MySQL Server client tool. Similar to MySQL Command-Line Client, it delivers input line editing features, but its functionality goes far beyond that and includes scripting capabilities for JavaScript and Python, APIs for working with MySQL, and command completion, among other features.

How to download the MySQL Command-Line Client

The MySQL Command-Line Client usually comes bundled with the MySQL Installer. On the official download page, select your platform, and download the package. When you install MySQL Server, the client installs automatically, and you just need to launch it afterward.

However, it's not uncommon to have MySQL Server installed without the client. This can happen if you used a minimal setup or a container image that excludes client tools. In this case, you need to download MySQL Command-Line Client separately and install it to interact with MySQL.

On Windows, a standalone MySQL Command-Line Client download is not available, but you can still install the client only.

Use the MySQL installer (recommended)

Download the MySQL installer for Windows and run it. Choose Add to modify the setup, proceed to the product list, and select MySQL Command-Line Client under Applications and Tools.

Proceed and complete the setup. This way, you download and install only the missing components.

Use the ZIP archive

You can also download the MySQL Community Server ZIP archive from the official MySQL website. After saving the archive, extract its contents and locate the mysql.exe file in the /bin directory. Add this directory to your PATH environment variable, and you'll be able to launch the client using the standard command:

mysql -u root -p

How to install MySQL Command-Line Client

As we mentioned, on Windows MySQL Command-Line Client is installed together with the MySQL Server, or you may use some alternative methods to install the MySQL client separately. To check whether you have the client program on your machine, go to search, switch to the Apps tab, and type "MySQL".

You may also need the information on how to download and install MySQL Command-Line Client on other platforms. The possibility is available on both macOS and Linux.

On macOS

The DMG installer used to install MySQL on macOS, always installs both the server and client together. If you need the MySQL Command-Line Client only, you can add it using Homebrew:

brew install mysql-client

Next, link the binaries to your PATH:

echo export PATH="/usr/local/opt/mysql-client/bin:$PATH" >> ~/.zshrc
source ~/.zshrc

After that, you can open MySQL Command-Line Client successfully.

On Linux

On Linux, the MySQL client is an independent package, so you can install it separately with ease. On Ubuntu/Debian, use the below command:

sudo apt update
sudo apt install mysql-client

When the installation is complete, launch the client and connect to MySQL.

How to use MySQL Command-Line Client

So, how to start MySQL Command Line Client? To access MySQL Server from the command-line client, open the program and enter the password. After that, you will be able to use the client.

You can also access MySQL Command-Line Client from the Command Prompt:

1. Open Command Prompt and navigate to the bin folder (e.g., cd C:\Program Files\MySQL\MySQL Server 9.4\bin)
2. Run the mysql -u root -p command.
3. Enter the password.

This way, you can connect to MySQL Command-Line Client and use it to perform your tasks in MySQL.

For more details on how to open MySQL in cmd, refer to How to connect to MySQL server using the command-line client.

How to start managing MySQL database from the Command-Line

To manage and administer MySQL databases from the command line, you need to get acquainted with command-line syntax. Let's look at the basic commands.

How to create a user from the Command-Line

First of all, we need to create a user. For this, run the following command:

CREATE USER 'username' IDENTIFIED BY 'password';

Don't forget to replace the username and password placeholders with a username and password of your choice.

Keep in mind, that just creating a user is not enough, you need to grant certain privileges to this user. Use the following query:

CREATE USER 'username' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'username';

This explicitly grants only the SELECT permission for the specified user. In case you want to grant a user all permissions on all databases, run the following command:

GRANT ALL PRIVILEGES ON *.* TO 'username';

For more information, refer to How to create a new user in MySQL.

How to create a database from the Command-Line

To create a database, use the following command. Replace the placeholder with the required name of the database.

CREATE DATABASE dbname;

To start working with the newly created database, execute the query:

USE dbname;

For more information, please refer to How to create a database from the command-line client.

How to delete a MySQL database from the Command-Line

To delete a database, run the following simple command. Remember that you won't be able to revert the deletion, so perform the operation with caution.

DROP DATABASE dbname;

How to delete a MySQL user account

In our blog, we talked about how to create a new user in MySQL. To delete a user in MySQL, execute a query:

DROP USER 'username';

For more information on command-line operations, refer to Check MySQL version from the command line and Backup MySQL database from the command line.

MySQL Client options and query syntax

To get a full list of MySQL Client commands, enter help or \h into the prompt.

MySQL Command-Line Client supports a broad range of options. You can find the full list here.

In MySQL command-line syntax, options are given after the command name and begin with one or two dashes, depending on whether it is a short or long form of the option name (for example, -? and --help). Please remember that option names are case-sensitive.

Common MySQL CLI errors and fixes

Let us review some of the most common errors that may occur when you work with the MySQL Command-Line Client.

'mysql' is not recognized as an internal or external command

This error means that your system can't find mysql.exe or mysql because the MySQL bin directory is not present in the PATH environment variable in your system.

To fix it, navigate to the MySQL bin folder (the default path is like C:\Program Files\MySQL\MySQL Server 9.4\bin) and copy that path. Then open System Properties > Environment Variables > System variables. Find the PATH variable and edit it: you need to add the copied MySQL bin path.

After that, restart the MySQL CLI and verify the setup by running the following command: mysql -V.

Access denied for user 'root'@'localhost'

This error takes place if your credentials are incorrect, or the specified user doesn't have permission to access the database from your host. To fix the issue, check and verify the credentials and permissions for the user. If you have forgotten the root password, you can reset it.

Can't connect to MySQL server

This error occurs if your CLI client can't reach the MySQL server. Usually, the cause of the problem is that the MySQL service isn't running, the hostname/port is incorrect, or a firewall blocks the connection.

First of all, check if the server is running using the following command: net start | find "MySQL". Make sure you're connecting to the right host and port. If you're connecting to a remote server, check the server's firewall and make sure that it allows inbound connections on port 3306 (the default port for MySQL).

Version mismatch between client and server

In some cases, a newer client version may not connect to an older server (or vice versa) because of protocol or authentication issues. Check both your MySQL client and the MySQL server versions and make sure they match.

Alternatives to MySQL Command Line Tool

Though the knowledge of command-line tools is essential for MySQL database specialists (as well as for all database developers and administrators working with all database management systems), the usage of GUI-based solutions for database management is equally popular. These tools simplify the work for all users, from newbies to seasoned pros, by visualizing the task configuration and performance. GUI tools save time and effort, significantly improving the quality of the output by helping eliminate errors caused by manual coding.

The default GUI IDE for MySQL databases is MySQL Workbench. However, there are numerous alternatives, often more powerful and user-friendly. Among the most popular alternatives to MySQL Command Line Client, experts outline dbForge Studio for MySQL, PHPMyAdmin, Toad Edge for MySQL, HeidiSQL, DataGrip and DBeaver, SQLyog, as well as Navicat for MySQL. All these tools have been on the market for quite a while and have proved to be helpful, reliable, and credible. Though the primary platform for database management software is Windows, there are many MySQL clients for macOS and Linux. And the most powerful and comprehensive GUI database client for MySQL and MariaDB is dbForge Studio for MySQL.

Best MySQL client instead of MySQL CLI

dbForge Studio for MySQL is a robust MySQL GUI for database management, development, and administration. The Studio greatly surpasses the functionality of MySQL command-line tools, offering a rich feature set for all tasks in MySQL and MariaDB.

For beginners, dbForge Studio for MySQL platform offers features like code autocompletion and syntax check as well as the ability to build complex queries visually without any coding.

You'll find the MySQL Tutorial invaluable for getting started. Additionally, detailed documentation and comprehensive tutorial for beginners ensure you have all the resources you need at your fingertips, backed by top-notch customer service. For those looking to deepen their expertise, check out the free online course Mastering MySQL and MariaDB With dbForge Studio available on Devart Academy.

Experienced users will appreciate the database design tools that simplify complex tasks and automation capabilities for routine processes. Dive deeper into these features with Expert MySQL Resources and Guides.

Conclusion

Using CLI tools is a standard practice in database management, particularly for DevOps specialists who rely on automation. If your role involves such tasks, it's essential to understand how CLI tools work and how to use them efficiently. This tutorial is designed to help you master these tools for working with MySQL and MariaDB databases, providing both essential information and practical tips.

That said, GUI-based database clients can also play a vital role in DevOps workflows. They are more convenient for all tasks related to database design, management, and administration. dbForge Studio for MySQL is a comprehensive solution that covers all aspects of database development and administration. It includes advanced automation capabilities and an intuitive interface designed to optimize your work. You can explore its full potential in practice — download a fully functional 30-day free trial and evaluate how the Studio enhances your daily workflows!

FAQ

sudo apt update
sudo apt install mysql-client

sudo dnf install mysql