MySQLadmin Mastery: Command-Line Database Management

Summary: in this tutorial, you will learn how to efficiently perform various database administrative tasks using the mysqladmin command-line utility.

Introduction to mysqladmin utility

The mysqladmin is a powerful command-line utility designed for performing database administrative tasks. By default, the MySQL installation includes the mysqladmin utility.

To use the mysqladmin command, follow these steps:

First, open the Command Prompt on Windows or the Terminal on macOS or Linux.

Second, execute the mysqladmin command with various options.

The basic syntax of mysqladmin command is as follows:

mysqladmin [options] command [command-options] [command-arguments]Code language: CSS (css)

In this syntax:

• options: specify various options to specify the MySQL server connection.
• command: specify the action to perform (e.g., create a database, check server status, etc.).
• command-options: provide additional options specific to the chosen command.
• command-arguments: provide arguments required for the selected command.

If you have set up a default login path, you don’t need to specify the user account and password; Otherwise, you need to add parameters (user, password, host, port, etc) to the [options] part of the command.

After you press the Enter key, mysqladmin will prompt you to enter the password for the user account and execute the task.

The mysqladmin common command examples

Let’s explore some common commands that you can execute using the mysqladmin utility.

Note that we assume that you set up a default login path for the user who has sufficient privileges to perform the task.

If you have not done so, you need to use the following option in all the commands:

-u your_username -p

Change your_username with the user account that you want to use to connect to the MySQL server.

1) Checking MySQL Server Status

The following command checks the MySQL Server status:

mysqladmin status

Output:

Uptime: 83279  Threads: 2  Questions: 3162  Slow queries: 0  Opens: 396  Flush tables: 3  Open tables: 265  Queries per second avg: 0.037Code language: CSS (css)

The command returns the essential information about the MySQL server, including uptime, thread activity, and more.

2) Creating a New Database

The following command creates a new database called sample:

mysqladmin create sample

It’ll functionally be equivalent to the CREATE DATABASE statement.

3) Deleting a Database

The following command drops the sample database:

mysqladmin drop sample

It’ll display a message to request you to confirm the deletion:

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'sample' database [y/N]Code language: PHP (php)

If you genuinely want to delete the database, enter the letter Y and press the Enter key; or enter N otherwise.

Behind the scenes, the mysqladmin drop command uses the DROP DATABASE statement.

4) Displaying MySQL Server Version

The following command shows the version of the MySQL server:

mysqladmin version

5) Changing the password for a user

The following command changes the password of the user used to connect to the MySQL server:

mysqladmin -u root -p password

In this command, you need to provide the current password for the root user and provide the new password twice.

6) Flushing MySQL Server Privileges

The following command flushes the MySQL server privileges:

mysqladmin flush-privileges

It will reload the grant tables and apply changes to MySQL privileges.

7) Reloading MySQL Configuration

The following command reloads the MySQL server configuration without restarting:

mysqladmin reload

8) Monitoring MySQL Process List

The following command shows a list of current processes:

mysqladmin processlist

Sample output:

+----+-----------------+-----------------+----+---------+-------+------------------------+------------------+
| Id | User            | Host            | db | Command | Time  | State                  | Info             |
+----+-----------------+-----------------+----+---------+-------+------------------------+------------------+
| 5  | event_scheduler | localhost       |    | Daemon  | 84165 | Waiting on empty queue |                  |
| 41 | root            | localhost:61467 |    | Query   | 0     | init                   | show processlist |
+----+-----------------+-----------------+----+---------+-------+------------------------+------------------+Code language: PHP (php)

9) Shutting down the MySQL Server

The following command gracefully shut down the MySQL server:

mysqladmin shutdown

Summary

• Use mysqladmin command-line utility to perform database administrative tasks more efficiently.