creoline.com Create account Log in

Using the MySQL CLI

Introduction

The following article explains how to use the MySQL CLI on your server and from any remote server.

Prerequisites

A MySQL or MariaDB server must be installed
SSH access

Using the Local MySQL CLI

First, connect to the server via SSH using the appropriate login credentials.

You can start the MySQL CLI with the following command:

mysql

This method only works if the UNIX Socket authentication plugin is being used. If mysql_native_password has been set as the authentication method for the user instead, you can use the following command:

mysql -u <username> -p

If you want to connect from an external device, remote connections to the database server must be explicitly allowed, and the port (default = 3306) used by the MySQL/MariaDB server must be opened in the server’s firewall.

For security reasons, we recommend that you open the port only for specific IP addresses.

Use the following command to access the MySQL CLI on an external server:

mysql -u <username> -p -h <IP address> --port <port number>

Specifying a port number is only necessary if the port used by the server is not the default port 3306.

Examples of MySQL CLI Commands

When using MySQL commands, it is important to end them with a semicolon “;”, otherwise the system will expect further commands or similar input.

Display all databases to which the currently logged-in MySQL user has access:

MariaDB [(none)]> SHOW DATABASES;

Select a database, for example, to run queries:

MariaDB [(none)]> USE <database_name>;

After that, the output should look like this:

MariaDB [<database_name>]>

Display all tables in the selected database:

MariaDB [<database name>]> SHOW TABLES;

Example query to retrieve all records from a table in the selected database:

MariaDB [<database name>]> SELECT * FROM <table name>;

Querying specific columns of a database table:

MariaDB [<database name>]> SELECT <Column 1, Column 2, Column 3...> FROM <table name>;

Query for a specific value from a specific table in a database:

MariaDB [<database name>]> SELECT <Column 1,Column 2,Column 3...> FROM user WHERE <column name>='<value>';

Creating a database:

MariaDB [(none)]> CREATE DATABASE <database name>;

Deleting a database:

MariaDB [(none)]> DROP DATABASE <database name>;

Deleting specific records from a database table:

MariaDB [<database name>]> DELETE FROM <table name> WHERE <column name>='<value>';

Updating a record in a database table:

MariaDB [<database name>]> UPDATE <table name> SET <column 1>='<value 1>', <column 2>='<value 2>' WHERE <column name>='<value>';

Adding values to specific columns in a database table:

MariaDB [<database name>]> INSERT INTO <table name> (<column 1>, <column 2>, <column 3>, ...) VALUES (<value 1>, <value 2>, <value 3>, ...);

Add values to all columns in a database table:

MariaDB [<database name>]> INSERT INTO <table name> VALUES (<value 1>, <value 2>, <value 3>, ...);

Create a user:

MariaDB [(none)]> CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';

Permanently delete a user:

MariaDB [(none)]> DROP USER <username>;