Tips to Use MySQL Client

Photo by Rubaitul Azad on Unsplash

The MySQL database system uses a client-server architecture. The client, mysql, can be installed on your computer to connect to the server. The server, mysqld, is the program that actually manipulates databases that can run on your local computer or anywhere on the planet.

This article introduces you to some tips on how to use the mysql client. Before you go, you should have access to a mysql server and get the mysql client installed on your computer.

0x00 Connect to server

To connect your mysql server, you need to know where it is running (the host and port) and an account to access the server:

$ mysql -h<HOST> -P<PORT> -u<USERNAME> -p<PASSWORD>

Replace with your own parameters and you will see:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 8.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Each option is the single-dash “short” form. There are also corresponding double-dash “long” forms: --host for -h, --port for -P, --user for -u, and --password for -p.

If your mysql server runs on your local computer, you can even omit -h and -P parameters:

$ mysql -u<USER> -p<PASSWORD>

Entering your password in a plain text format is not recommended, you can use -p to be prompted for the password:

$ mysql -u<USER> -p
Enter password: ******

Use mysql --help for more options.

0x01 Specify Parameters in Option File

Specify the parameters in an option file to avoid entering the frequently used parameters in the command line. Option files are plain text files, you can use mysql --help to see where mysql will find option files:

$ mysql --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/Cellar/mysql-client/8.1.0/etc/my.cnf ~/.my.cnf

Take ~/.my.cnf for example:

[client]
host=localhost
user=username
password=your_password

Now you don’t need to enter the parameters every time you invoke mysql:

$ mysql

0x02 Encrypt Your Password

Putting your password in the plain text option file is not secure. Fortunately, there is another way to handle your password: mysql_config_editor .

mysql_config_editor only stores connection parameters host, user, password, and socket in a file, named .mylogin.cnf, located in your home directory.

$ mysql_config_editor set --login-path=client --host=localhost \
> --user=username --password
Enter password: PASSWORD

You can also see what you have set:

$ mysql_config_editor print --all

You will see the password is replaced with asterisks:

[client]
user = "root"
password = *****
host = localhost

0x03 Use client Interactively

Once connected, you can execute SQL statements interactively:

mysql> SHOW DATABASES;

The statement is ended with a semicolon (;) and is necessary. But you can also use \g :

mysql> SELECT NOW()\g
+---------------------+
| now()               |
+---------------------+
| 2023-09-14 11:47:32 |
+---------------------+
1 row in set (0.00 sec)

Sometimes the outputs of your statements are so long that they take more than one line on your terminal, and are difficult to read. Thus, use \G instead of ; or \g to generate vertical outputs:

mysql> SHOW CREATE TABLE sundays;
*************************** 1. row ***************************
       Table: sundays
Create Table: CREATE TABLE `sundays` (
  `year` year DEFAULT NULL,
  `month` int(2) unsigned zerofill DEFAULT NULL,
  `day` int(2) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

To exit the interactive mode, just enter exit :

mysql> exit
Bye

or press Command + D (Ctrl + D):

mysql>^DBye

0x04 Specify the Default Database

For now, you don’t specify the database after connecting:

mysql> SELECT DATABASE();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

You have to specify a database to execute statements interactively:

mysql> USE exercise;
Database changed

Or, you can specify the default database in the connection parameter when connecting:

$ mysql -D exercise

The double-dash form for -D is --database:

$ mysql --database exercise

You can also specify the database directly:

$ mysql exercise

0x05 Customize a mysql Prompt

The default prompt for mysql client is mysql>. You can customize the prompt with another value:

mysql> prompt exercise>
PROMPT set to 'exercise>'
exercise>

Prompt with exercise> may be useless, here are some tips.

Prompt with server host:

mysql> prompt \h
PROMPT set to '\h>'
localhost>

Prompt with user account:

mysql> prompt \u>
PROMPT set to '\u>'
root> prompt \U>
PROMPT set to '\U>'
root@localhost>

Prompt with current database:

prompt \d>
PROMPT set to '\d>'
(none)>use exercise;
Database changed
exercise>

Prompt with current time:

mysql> prompt \R:\m:\s>
PROMPT set to '\R:\m:\s>'
12:40:54> prompt \D>
PROMPT set to '\D>'
Thu Sep 14 12:41:20 2023>

This is helpful because you know exactly when you executed a certain statement.

Prompt with default value:

Thu Sep 14 12:41:20 2023>prompt
Returning to default PROMPT of mysql>
mysql>

0x06 Specify a pager

Sometimes the output is too long to fit on the screen when executing the SQL statements interactively, then you can specify a pager to solve this:

$ mysql pager -F -X
PAGER set to 'less -F -X'
mysql> select * from city;
+----------------+----------------+----------------+
| state          | capital        | largest        |
+----------------+----------------+----------------+
| Alabama        | Montgomery     | Birmingham     |
| Alaska         | Juneau         | Anchorage      |
| Arizona        | Phoenix        | Phoenix        |
| Arkansas       | Little Rock    | Little Rock    |
| California     | Sacramento     | Los Angeles    |
| Colorado       | Denver         | Denver         |
| Connecticut    | Hartford       | Bridgeport     |
| Delaware       | Dover          | Wilmington     |
| Florida        | Tallahassee    | Jacksonville   |
:

Type Enter to see more, and q to quit.

Disable the pager:

mysql> nopager
PAGER set to stdout

0x07 Execute Statements from Command Line

We have executed SQL statements interactively before, another way to execute statements if from the command line withe the -e option:

$ mysql -e "SELECT NOW();"
+---------------------+
| now()               |
+---------------------+
| 2023-09-14 14:01:22 |
+---------------------+

Executing SQL statements in the command line is useful when you need to export some data from MySQL to other people.

You can also execute multiple statements separated by ; :

$ mysql exercise -e "SELECT NOW();SELECT * FROM artist;"
+---------------------+
| now()               |
+---------------------+
| 2023-09-14 14:14:13 |
+---------------------+
+------+----------+
| a_id | name     |
+------+----------+
|    1 | Da Vinci |
|    2 | Monet    |
|    4 | Renoir   |
|    3 | Van Gogh |
+------+----------+

Use -v option to see the statement:

$ mysql exercise -e "SELECT * FROM artist;" -v
--------------
select * from artist
--------------

+------+----------+
| a_id | name     |
+------+----------+
|    1 | Da Vinci |
|    2 | Monet    |
|    4 | Renoir   |
|    3 | Van Gogh |
+------+----------+

Use -vv to see more info:

$ mysql exercise -e "SELECT * FROM artist;" -vv
--------------
select * from artist
--------------

+------+----------+
| a_id | name     |
+------+----------+
|    1 | Da Vinci |
|    2 | Monet    |
|    4 | Renoir   |
|    3 | Van Gogh |
+------+----------+
4 rows in set (0.01 sec)

Bye

Use -s to remove tabular box:

$ mysql exercise -e "SELECT * FROM artist;" -s
a_id name
1 Da Vinci
2 Monet
4 Renoir
3 Van Gogh

This is useful when redirecting the output to a file and then converting it to CSV format:

$ mysql exercise -e "SELECT * FROM artist;" -s | sed -e "s/\t/,/g"
1,Da Vinci
2,Monet
4,Renoir
3,Van Gogh

Then you can save the output to a CSV file:

mysql exercise -e "SELECT * FROM artist;" -s | sed -e "s/\t/,/g" > artist.csv
cat artist.csv
1,Da Vinci
2,Monet
4,Renoir
3,Van Gogh

To remove the column names, use-ss :

$ mysql exercise -e "SELECT * FROM artist;" -ss
1 Da Vinci
2 Monet
4 Renoir
3 Van Gogh

0x08 Execute Statements from File

You can feed mysql with statements from a file:

$ mysql < exercise.sql

SQL scripts are also useful when you want to distribute the data to others.