MySQL CLI (Command-Line Interface): How to Get Started with MySQL Database Management

The article provides a detailed guide on MySQL Command Line Client and shares useful tips on how to download, install, and get started with it. Read the tutorial to find out the best MySQL client on the market today.

Contents

• What is a MySQL client?
  • What is the difference between MySQL Server and MySQL client?
  • What is the difference between MySQL Shell and MySQL Command Line Client?
• How to install MySQL Command Line Client
• How to use MySQL Command Line Client
• How to start managing MySQL database from the command line
  • Create a user from the command line
  • Create a database from the command line
  • Delete a MySQL database from the command line
  • Delete a MySQL user account
• MySQL Client options and query syntax
• Alternative to MySQL Command Line Tool
• Best MySQL client instead MySQL CLI

What is a MySQL client?

MySQL client is a common name for tools that are designed to connect to MySQL Server. Client programs are used to send commands or queries to the server and allow managing data in the databases stored on the server. Thus the answer to the question: “What is a MySQL command-line client?” is quite obvious. It is the tool that allows sending commands to MySQL Server from the command line.

What is the difference between MySQL Server and MySQL Client?

Simply put, MySQL Server holds data, and MySQL Client is used for accessing and manipulating it.

However, under MySQL Client, users quite usually mean the MySQL Command Line Client—a lightweight CLI client utility that comes with the MySQL package. The MySQL command prompt lets you issue lots of commands to the MySQL server such as for creating and dropping databases and tables, adding new rows, fetching data, and much more.

MySQL Shell is also worth mentioning when talking about accessing MySQL Server from the command line. MySQL Shell is an advanced MySQL client which lets the user develop, manage, and administer MySQL databases from the command line. It comes as a component of the MySQL Server package too.

What is the difference between MySQL Shell and MySQL Command Line Client?

In simple words, MySQL Shell is a significantly more advanced MySQL Server client tool. Similarly to MySQL Command Line Client, MySQL Shell delivers input line editing features. However, MySQL Shell’s functionality goes far beyond that and includes scripting capabilities for JavaScript and Python, APIs for working with MySQL, command completion, etc.

How to install MySQL Command Line Client

Generally, MySQL Command Line Client is installed together with the MySQL Server, and you don’t need to download and 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”.

In case you don’t have the client on your PC and you don’t want to install the entire MySQL Server package, go to the MySQL download page, select the Microsoft Windows platform, and download Windows (x86, 64-bit), ZIP Archive. You will find MySQL Command Line Client binaries in the bin folder.

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 Command Prompt. For this:

1. Open Command Prompt.
2. Navigate to the bin folder. For example: cd C:\Program Files\MySQL\MySQL Server 8.0\bin
3. Run the mysql -u root -p command.
4. Enter the password.

For more information, please refer to How to connect to MySQL server using the command-line client.

How to start managing MySQL database from the command line

To start managing and administering MySQL databases from the command line, you will need to get acquainted with command-line syntax. Let’s look at the bunch of simple commands in detail.

How to create a user from the command line

First of all, we need to create a user. For this, we 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. For this, run the MySQL query:

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, please 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 

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 at the prompt.

When using the commands, don’t forget to end each command with the semicolon symbol.

MySQL Command Line Client supports a great deal of command-line options, the full list of which can be found 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.

Alternative to MySQL Command Line Tool

Among the most popular alternatives to MySQL Command Line Client, experts outline MySQL Workbench, dbForge Studio for MySQL, PHPMyAdmin, Toad Edge for MySQL, HeidiSQL, DataGrip, and Navicat. Note that besides Windows, which is the primary platform for database software, you have a rich selection of MySQL clients for macOS.

All these tools have been on the market for quite a while and proved to be helpful, reliable, and credible. Each solution has an army of admirers and delivers great value. So the question arises: “What is the best MySQL client on the market today?”

Best MySQL client to replace MySQL CLI

One of the best MySQL clients regardless of your particular goals and needs is dbForge Studio for MySQL. It is a fully-functional MySQL GUI for database management, development, and administration. The Studio greatly surpasses the functionality of MySQL command-line tools—MySQL Command Line Client and Shell and is a worthy rival to popular desktop MySQL GUI clients. It boasts a rich feature set that can be used both from the neat and user-friendly interface and command line.

For beginners, code autocompletion, syntax check, the ability to build complex queries visually without any coding, extensive documentation, and top-notch customer service will be really beneficial. Experienced users will appreciate robust database design tools, the capability to schedule and automate routine tasks, and vast possibilities for data reporting and analysis.

Devart grants a free 30-day trial to potential purchasers of dbForge Studio for MySQL. Download the dbForge MySQL client for Windows and try the overwhelmingly rich functionality it delivers.