By using mysqladmin commands, you can perform basic MySQL tasks very easily, such as setting/changing the root password, monitoring MySQL processes, checking MySQL server status, etc. Here are some useful ‘mysqladmin‘ commands that can be used to administrate the MySQL server.
Set MySQL root password
If you would like to set a MySQL password for the root user, use the following command. Please note that the fresh installation of the MySQL server doesn’t require any password to connect it as the root user.
Change MySQL root password
If you would like to change or update MySQL root password, then you need to type the following command. It will change your old password to the new one.
Check MySQL server is up and running
If you want to check whether the MySQL server is up and running, use the following command:
Determine MySQL server version
You can determine the exact version of the MySQL server by following the command.
Check all running processes of the MySQL server
Execute the below command to list all running processes of MySQL database queries.
Find out the current status of the MySQL server
If you wish to find out the current status of the MySQL server, use the following command. This command shows the uptime status with running threads and queries.
Check the status of all MySQL server variables and values
Sometimes you require to check the status of all running MySQL server variables and their values; in that case, type the following command.
See all MySQL server variables and values
You can also see all the running variables and values of the MySQL server using the following mysqladmin command.
Create a database in the MySQL server
If you would like to create a new database in the MySQL server, use the command as shown below.
Drop a database in the MySQL server
Sometimes you require to drop a database in the MySQL server; in that case, you can use the following command. Press ‘y‘ when you are asked for confirmation.
Connect to remote MySQL server
You can connect to remote MySQL server using -h argument following by IP address of remote MySQL server.
Refresh MySQL Privileges
You can use the 'reload' command to refresh mysql privileges. The reload command tells MySQL server to reload the grant tables and refresh command flushes all tables and log files.
Shutdown, Stop, or Start MySQL server
Type the following command to shut down the MySQL server safely.
Type the following commands to Start or Stop the MySQL server.
To stop the MySQL server
To start the MySQL server
Kill sleeping MySQL client processes
First, find out sleeping MySQL client processes. Use the following command to identify the sleeping MySQL client process.
Now, run the following command with the kill command and process ID as shown below.
Run multiple mysqladmin commands together
You can execute multiple ‘mysqladmin‘ commands together as follows.
Useful MySQL flush commands
flush-hosts flushes all host information from the host cache.
flush-tables flushes all tables.
flush-threads flushes all threads cache.
flush-logs flushes all information logs.
flush-privileges reloads the grant tables.
flush-status clears status variables.
Store MySQL server debug information to logs
The following command will tell the MySQL server to write debug information in MySQL log file.