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.
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.