Top 8 MySQL Command-line Tips and Tricks

MySQL is an open-source relational database management system that is developed, distributed, and supported by Oracle Corporation. There are several web-based tools available for managing the MySQL server, but best practice is to use the native MySQL command-line client for performing day-to-day MySQL tasks. It does take some time to get familiar with a command-line interface, particularly if you don’t regularly work in a CLI environment.

In this tutorial, we will show you some MySQL command-line tips and tricks that help you to perform day-to-day tasks.

Prerequisites

• A fresh Ubuntu 18.04 VPS with MySQL installed on the Atlantic.Net Cloud Platform.
• A root password configured on your server.

Create Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server.  Create a new server, choosing Ubuntu 18.04 as the operating system with at least 1GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged into your Ubuntu 18.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

1 – Automate the MySQL Login

In order to connect the MySQL server, you will be required to authenticate by specifying a username and password.

If this is your routine process, then you can save your lots of time by specifying your MySQL username and password in ~/.my.cnf file to automate the login process.

To do so, create a ~/.my.cnf file in your home directory:

nano ~/.my.cnf

Add the following information containing your MySQL host, username, and password:

[client]
host     = localhost
user     = root
password = your-password

Save and close the file then change the ownership of the file to mysql:

chown mysql:mysql ~/.my.cnf

Now, you can connect your MySQL server without specifying a username and password as shown below:

mysql

You should get the following output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql>

2 – Check Running Processes

To check all the running processes of the MySQL service, run the following command:

mysqladmin -u root -pnew-password processlist

You should see the following output:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----------+----+---------+------+----------+------------------+
| Id | User | Host      | db | Command | Time | State    | Info             |
+----+------+-----------+----+---------+------+----------+------------------+
| 6  | root | localhost |    | Query   | 0    | starting | show processlist |
+----+------+-----------+----+---------+------+----------+------------------+

3 – Shutdown MySQL Service

To shut down the MySQL server, run the following command:

mysqladmin -u root -pnew-password shutdown

4 – Create and Delete Database

To create a new database, run the following command:

mysqladmin -u root -pnew-password create dbname

To remove a database, run the following command:

mysqladmin -u root -pnew-password drop dbname

5 – Check the Status of MySQL

To check whether the MySQL server is running or not, use the following command:

mysqladmin -u root -pnew-password ping

You should get the following output:

mysqld is alive

To verify the current status of the MySQL server, run the following command:

mysqladmin -u root -pnew-password status

You should see the status of uptime with running threads and queries in the following output:

Uptime: 2607  Threads: 1  Questions: 30  Slow queries: 0  Opens: 112  Flush tables: 2  Open tables: 0  Queries per second avg: 0.011

If you want to check the status of remote MySQL server, run the following command:

mysqladmin -h remote-server-ip -u root -pnew-password status

6 – Check MySQL Version

To check the installed version of the MySQL server, run the following command:

mysqladmin -u root -pnew-password version

You should get the following output:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin  Ver 8.42 Distrib 5.7.29, for Linux on x86_64
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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

Server version             5.7.29-0ubuntu0.18.04.1
Protocol version          10
Connection                 Localhost via UNIX socket
UNIX socket               /var/run/mysqld/mysqld.sock
Uptime:                                   44 min 44 sec

Threads: 1  Questions: 35  Slow queries: 0  Opens: 113  Flush tables: 2  Open tables: 1  Queries per second avg: 0.013

7 – Store MySQL Debug Information

If you want to tell MySQL server to write debug information in MySQL log file, run the following command:

mysqldump -u root -pnew-password debug

8 – Backup and Restore MySQL Database

To back up a single database, run the following command:

mysqldump -u root -pnew-password databasename > database-backup.sql

To back up multiple databases in a single file, run the following command:

mysqldump -u root -pnew-password database1 database2 database3 > database-backup.sql

To back up all databases in a single file, run the following command:

mysqldump -u root -pnew-password --all-databases --single-transaction --quick --lock-tables=false > full-database.sql

To restore a database from the backup file, run the following command:

mysqldump -u root -pnew-password databasename < database-backup.sql

Conclusion

In the above guide, you learned some useful MySQL commands with examples. We hope this will help you to save lots of time when using MySQL on your VPS from Atlantic.Net.