Use MySQL-CLI

Foreword

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

Prerequisites

Installed MySQL or MariaDB server
SSH access

\

Use of the local MySQL CLI

First connect to the server via SSH with the corresponding access data.

You can start the MySQL CLI with the following command:

mysql

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

mysql -u <username> -p

If you want to establish the connection from an external device, the remote connection to the database server must be explicitly permitted and the port (default = 3306) used by the MySQL/MariaDB server must be released in the server's firewall.

For security reasons, we recommend that you only release the port for certain IP addresses.

Use the following command to use the MySQL CLI of an external server:

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

The specification of a port number is only necessary if the port used by the server does not correspond to the standard port 3306.

Examples for MySQL CLI commands

When using MySQL commands, it is important to end them with a semicolon ";", as otherwise the input of further commands or similar is expected.

display of all databases to which the currently used MySQL user has access:

MariaDB [(none)]> SHOW DATABASES;

selection of a database, e.g. to perform queries:

MariaDB [(none)]> USE <database name>;

The output should then look like this:

MariaDB [<database name>]>

display all tables of the selected database:

MariaDB [<database name>]> SHOW TABLES;

example query of all entries from a table in the selected database;

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

query specific columns of a database table:

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

query of a specific value from a specific table of a database:

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

create a database:

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

delete a database:

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

deletion of certain entries from a database table:

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

modify an entry in a database table:

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

add values for 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 for all columns in a database table:

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

create user:

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

delete user permanently:

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