How to Administrate MySQL Server with mysqladmin commands?

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.

Set MySQL Root password

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.

Change MySQL Root Password

Check MySQL server is up and running

If you want to check whether the MySQL server is up and running, use the following command:

Check MySQL Server Is Up And Running

Determine MySQL server version

You can determine the exact version of the MySQL server by following the command.

Check MySQL Running Version

Check all running processes of the MySQL server

Execute the below command to list all running processes of MySQL database queries.

Check All MySQL Server running Process

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 Current Status of MySQL Server

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.

Check Status Of All Mysql Server Variables And Values

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.

See All MySQL Server Variables And Values

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.

Create A Database In MySQL Server

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.

Drop A Database In MySQL Server

Connect to remote MySQL server

You can connect to remote MySQL server using -h argument following by IP address of remote MySQL server.

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

Reload MySQL Privileges

Refresh MySQL Privileges

Shutdown, Stop, or Start MySQL server

Type the following command to shut down the MySQL server safely.

Shutdown MySQL Server Safely

Type the following commands to Start or Stop the MySQL server.

To stop the MySQL server

Stop MySQL Server

To start the MySQL server

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

Check Sleeping MySQL Client Process

Now, run the following command with the kill command and process ID as shown below.

Kill Sleeping MySQL Client Process

Run multiple mysqladmin commands together

You can execute multiple ‘mysqladmin‘ commands together as follows.

Run Multiple Mysqladmin Commands Together

Useful MySQL flush commands

flush-hosts flushes all host information from the host cache.

Flush-Hosts In MySQL

flush-tables flushes all tables.

Flush-Tables In MySQL

flush-threads flushes all threads cache.

Flush-Threads In MySQL

flush-logs flushes all information logs.

Flush-Logs In MySQL

flush-privileges reloads the grant tables.

Flush-Privileges In MySQL

flush-status clears status variables.

Flush-Status In MySQL

Store MySQL server debug information to logs

The following command will tell the MySQL server to write debug information in MySQL log file.

Store MySQL Server Debug Information To Logs

Was this answer helpful?

« Back