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.
- What is a MySQL 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
- 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?
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:
- Open Command Prompt.
- Navigate to the bin folder. For example:
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
- Run the
mysql -u root -pcommand.
- 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:
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
\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,
--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 development, management, 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.