Manage MySQL / MariaDB Server with mysqladmin command

In this tutorial we are going to learn how to use mysqladmin command to Administrate MYSQL / MariaDB Server.

The mysqladmin is a command line tool that can perform administrative Tasks without login to the MySQL Console. It can perform many administrative operations Like create databases and drop databases.

Command Options

Two Most important command options are -u and -p which uses to specify MySQL username and password, respectively. The username you give to the mysqladmin command must have privileges to perform whichever task you are asking to perform.

mysqladmin -u username -p'password' status

Another important command option is -h which uses to perform administrative operations on Remote MySQL Server.

mysqladmin -u username -p'password' -h 192.168.1.10 status

MySQL Server Administration with mysqladmin command

First we will see how to Check if MySQL Server is Running using mysqladmin ping command.

mysqladmin -u root -p'123456' ping
mysqld is alive

If Server is running you should get the message 'mysqld is alive'.

View Server Status - The status command shows the MySQL Server Status.

mysqladmin -u root -p'123456' status

View Server Version - The version command will display MySQL / MariaDB Server version and other information including Server Uptime and UNIX socket.

mysqladmin -u root -p'123456' version

Create New Database - The create command use to create new a new Database. Following Command will create a new MySQL Database called 'example_db'.

mysqladmin -u root -p'123456' create 'example_db'

Remove MySQL Database - The drop command will remove a database from the Server. The Following command will Delete the database named example_db and all its tables.

mysqladmin -u root -p'123456' drop example_db

By default drop command will ask for the confirmation before deleting the database but if you use -f option it will not ask for confirmation.

mysqladmin -f -u root -p'123456' drop example_db

Show Processlist - The processlist command will Show a list of active threads currently executing on the MySQL / MariaDB server. This equivalent to SHOW PROCESSLIST statement on MySQL Console.

mysqladmin -u root -p'123456' processlist

To get Full Processlist (equivalent to SHOW FULL PROCESSLIST statement) use the --verbose option.

mysqladmin -u root -p'123456' --verbose processlist

Kill Process - The kill command will kill one or more threads by Process ID. Multiple Process ID values can be given in comma separated list (kill id1,id2).

mysqladmin -u root -p'123456' kill 35
mysqladmin -u root -p'123456' kill 35,36

Change MySQL User Password - The password command will set a new Password for the MySQL user that you use with mysqladmin command (user given with -u option).

mysqladmin -u root -p'123456' password abc123

As per the preceding example, Password of the root user account will be changed to 'abc123'.

Flush host_cache Table - MySQL and MariaDB host_cache table could block your host after multiple connection errors. To solve the problem you can clear host cache using mysqladmin flush-hosts Command.

mysqladmin -u root -p'123456' flush-hosts

Display the MySQL server system variables and their values.

mysqladmin -u root -p'123456' variables

Show Server Status Variables - The extended-status command Display the server status variables and their values.

mysqladmin -u root -p'123456' extended-status

Reload the grant tables - The flush-privileges will reload the grant tables. This is equivalent to the MySQL FLUSH PRIVILEGES Statement.

mysqladmin -u root -p'123456' flush-privileges

The mysqladmin command also can stop the Server with the shutdown command.

mysqladmin -u root -p'123456' shutdown

But it is not the best way to stop the MySQL Server, instead you should stop the mysql/mariadb service using either systemctl or service command.