How to Run MySQL Command in Shell?

MySQL is a popular database server utilized by applications such as WordPress for data storage. Occasionally, you may need to access MySQL directly while connected to your server via SSH. This can be achieved by utilizing MySQL's command line tools.

Accessing the MySQL Shell

By accessing your server via SSH as root, you can initiate a MySQL shell by executing the following command:

If you are logged into your app's system user via SSH, you can launch a MySQL shell by using the following command:

Provide the database name and the username which were created using the ServerPilot control panel. You will then receive a prompt to enter the password for the database user.

Using the MySQL Shell

After launching the MySQL shell, you will observe a prompt similar to the following:

Once you reach that stage, execute the command "use DATABASE_NAME;" to switch to a specific database. Suppose you have a database named "cars" then enter the command and view the resulting output as follows:

Once you have successfully switched to the desired database, you are free to enter any mysql shell commands that you wish to execute on that database.

What is a MySQL Client?

MySQL client refers to a group of tools that are specifically created to establish a connection with the MySQL Server. These client programs enable users to send queries or mysql shell commands to the server and manage data in the databases stored on it. Therefore, it is the utility that facilitates sending mysql shell commands to the MySQL Server via the command line.

a. What is the Difference between MySQL Server and MySQL Client?

While the MySQL Client is used to access and modify that data, the MySQL Server is in charge of storing data. However, people typically refer to the MySQL Command Line Client, the simplified CLI client tool that is a part of the MySQL package, when they talk about MySQL Client. Users can issue several instructions to the MySQL server through the command prompt, including those for creating and deleting tables and databases, inserting new rows, requesting data, and much more.

When talking about using the command line to access the MySQL Server, MySQL Shell is another important tool. A powerful MySQL client called MySQL Shell enables users to create, manage, and control MySQL databases via the command line. Additionally, it is a part of the MySQL Server package.

b. What is the Difference between MySQL Shell and MySQL Command Line Client?

Compared to MySQL Command Line Client, MySQL Shell is a far more advanced MySQL Server client tool. Although MySQL Shell offers input line editing capabilities, it is significantly more functional than MySQL Command Line Client. The MySQL Shell comes with JavaScript and Python scripting support, working APIs for command completion, MySQL, and more.

How to Install MySQL Command Line Client?

There is typically no requirement for downloading and installing the MySQL client separately because it is typically installed concurrently with the MySQL Server. Search for "MySQL Command Line Client" in the Apps tab of the search window to see if the client program is installed on your device.

If the MySQL Command Line Client is not present on your computer, and you do not wish to install the complete MySQL Server package, you can visit the MySQL download page. From there, choose the Microsoft Windows platform, ZIP Archive and download the Windows (x86, 64-bit). The MySQL Command Line Client binaries can be found in the bin folder.

How to Use MySQL Command Line Client?

To initiate the MySQL Command Line Client, open the program and input the password. Once authenticated, you can utilize the client to access the MySQL Server from the command line.

How to Start Managing MySQL Database from the Command Line?

You must become familiar with the command-line syntax before you can start managing and administering MySQL databases via the command line. Let's take a closer look at a few simple commands in this regard.

a. Create a User from the Command Line

Making a user is the first step. To achieve this, execute the following command:

Remember to substitute the placeholders for username and password with your preferred username and password.

It's crucial to note that solely creating a user isn't sufficient; you must also confer specific privileges to the user. To achieve this, execute the subsequent mysql shell commands:

The SELECT permission is only granted to the selected user by the aforementioned command. Execute the following mysql shell commands to grant all permissions to a user across all databases.:

b. Create a Database from the Command Line

To establish a database, utilize the given command, and replace the placeholder with the desired database name.

To begin working with the recently created database, run the following query:

c. Delete a MySQL Database from the Command Line

To delete a database, execute the following command. Keep in mind that this operation cannot be undone, so proceed with caution.

d. Delete a MySQL User Account

You can delete a user in MySQL by executing the following query:

MySQL Client Options and Query Syntax

To view a full list of mysql shell commands, you can enter either "help" or "\h" at the command prompt.

When utilizing the commands, it is important to note that each command should end with a semicolon symbol.

Additionally, the MySQL Command Line Client supports a large number of command-line options. A list of all available options can be found here. Options are given in the MySQL command-line syntax following the command name and are followed by either 1 or 2 dashes, based on whether the option name is in the long or short form (for example, "--help" and "-?"). The case sensitivity of option names must be kept in mind.

Alternative to MySQL Command Line Tool

There are several recommended replacements for MySQL Command Line Client, including dbForge Studio for MySQL, MySQL Workbench, Toad Edge for MySQL, PHPMyAdmin, DataGrip, HeidiSQL, and Navicat. These tools have been available for some time and are useful, dependable, and trustworthy. Additionally, there are many MySQL clients for the macOS in addition to Windows, which is the primary platform for database software.

Every solution has a large user base and provides significant benefits. As a result, the question is raised that which MySQL client is the best on the market currently.