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!
Table of Contents
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.