10 Useful mysqladmin Commands for Database Administration

10 useful mysqladmin commands for database administration

In this blog post, we will show you ten useful mysqladmin commands for database administration.

Mysqladmin is a client for performing administrative operations. We can use this for checking the server’s configuration, creating and removing databases, users, tables, rows, and many more in MySQL. MySQL is a relational database management system used worldwide where the information is stored in tables. There are many database types offered by MySQL such as int, bigint, float,tinyint, smallint, mediumint and many more.

In this tutorial, first, we will install MySQL on the latest Ubuntu 22.04, then show you the mysqladmin commands. Let’s get started!

Prerequisites

  • Fresh install of Ubuntu 22.04
  • User privileges: root or non-root user with sudo privileges

Update the System

A fresh installation of Ubuntu 22.04 needs a system update. To update the system packages execute the following commands.

sudo apt update -y && sudo apt upgrade -y

MySQL Installation

To install MySQL execute the following command:

sudo apt install mysql-server -y

Once installed start and enable the MySQL service:

systemctl start mysql.service && systemctl enable mysql.service

To check the status of the service:

systemctl status mysql.service

If everything is ok, you should get the following output:

root@host:~# systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2022-10-13 02:50:00 CDT; 8min ago
   Main PID: 239318 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4575)
     Memory: 361.4M
        CPU: 4.761s
     CGroup: /system.slice/mysql.service
             └─239318 /usr/sbin/mysqld

Oct 13 02:49:58 host.test.vps systemd[1]: Starting MySQL Community Server...
Oct 13 02:50:00 host.test.vps systemd[1]: Started MySQL Community Server.

Now, when MySQL database service is installed, we can start with the mysqladmin commands. The syntax of mysqladmin command is the following:

mysqladmin [options] command [command-arg] [command [command-arg]] ...

To Log in to the MySQL command line with the following command:

mysql

On a fresh installation of MySQL, there is no password required, so you will receive the following output:

root@host:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2022, 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.

mysql>

In the next sections, we will show you the most 10 used mysqladmin commands for database administration.

1. Set MySQL Root Password

As we said previously, a fresh installation of MySQL does not require a password. To set up MySQL root password with mysqladmin command, execute the following:

mysqladmin -u root password StrongPasswordHere

After executing this command, you should receive this warning:

root@host:~# mysqladmin -u root password StrongPasswordHere
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

This means that setting a new MySQL password using mysqladmin should be considered vulnerable, and it can be viewed in the history of the server commands. So you need to be careful and be sure not to share your server’s root password.

2. Create a Database

To create an empty database, execute the following command:

mysqladmin -u root -p create testdb

You will be asked for the MySQL root password, and once inserted the database will be created successfully.

root@host:~# mysqladmin -u root -p create testdb
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

3. Drop Database

To drop the database with mysqladmin command, execute the following:

mysqladmin -u root -p drop testdb

You will be asked for the root password

root@host:~# mysqladmin -u root -p drop testdb
Enter password:
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 'testdb' database [y/N] y
Database "testdb" dropped

4. Check Active Processes

To check the active threads, execute the following command:

mysqladmin -u root -p processlist

You will get a table with output similar to this:

root@host:~# mysqladmin -u root -p processlist
Enter password:
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
| Id | User            | Host      | db | Command | Time  | State                  | Info             |
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
| 5  | event_scheduler | localhost |    | Daemon  | 19834 | Waiting on empty queue |                  |
| 14 | root            | localhost |    | Query   | 0     | init                   | show processlist |
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+

5. Check the Status of the MySQL Server

To check the status of the MySQL server, execute the following command:

mysqladmin -u root -p status

You should receive output similar to this:

root@host:~# mysqladmin -u root -p status
Enter password:
Uptime: 19978  Threads: 2  Questions: 18  Slow queries: 0  Opens: 167  Flush tables: 3  Open tables: 86  Queries per second avg: 0.000

6. Check MySQL Status Variables

To check the status of the MySQL status variables and their values, execute the following command:

mysqladmin -u root -p extended-status

You will get a huge table with a lot of information.

7. Reload MySQL Privileges

To reload the MySQL privileges, execute the following command:

mysqladmin -u root -p reload

8. Connect Remote MySQL Server

To connect to a remote MySQL Host, execute the following command

mysqladmin -h "IP-Address-Remote-Host" -u root -p

9. Run Multiple mysqladmin commands

To run multiple mysqladmin commands, just enter them one by one in the same line:

mysqladmin  -u root -p processlist status reload

If there is any output available for each command, you will get it respectively like the outputs below:

root@host:~# mysqladmin  -u root -p processlist status reload
Enter password:
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
| Id | User            | Host      | db | Command | Time  | State                  | Info             |
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
| 5  | event_scheduler | localhost |    | Daemon  | 20915 | Waiting on empty queue |                  |
| 18 | root            | localhost |    | Query   | 0     | init                   | show processlist |
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
Uptime: 20916  Threads: 2  Questions: 25  Slow queries: 0  Opens: 170  Flush tables: 3  Open tables: 89  Queries per second avg: 0.001

10. Shutdown MySQL Server

The last mysqladmin command will be shutting down safely the MySQL server:

mysqladmin -u root -p shutdown

If you check the status with the systemcl status mysql you should get this:

root@host:~# systemctl status mysql
○ mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Thu 2022-10-13 08:42:56 CDT; 12s ago
    Process: 239318 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 239318 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"
        CPU: 2min 15.480s

The MySQL service is stopped successfully with the mysqladmin command.

That’s all. We just showed you the ten most used mysqladmin commands on Ubuntu 22.04 using the latest MySQL 8.0 version. If you find any difficulties while managing your MySQL server, you just need to sign up for one of our NVMe VPS hosting plans and submit a support ticket. Our admins will help you with any aspect of the MySQL server. We are available 24/7.

If you liked this post about ten useful mysqladmin commands for database administration, please share it with your friends on social networks or simply leave a reply below.

Leave a Comment

To prove you are human please solve the following *