How to Administrate MySQL Server with mysqladmin commands? Print

  • 0

By using mysqladmin commands, you can perform basic MySQL tasks very easily, such as setting/changing 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 MySQL server doesn’t require any password to connect it as 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 MySQL server is up and running, use the following command:

Check MySQL Server Is Up And Running

Determine MySQL server version

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

Check MySQL Running Version

Check all running processes of MySQL server

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

Check All MySQL Server running Process

Find out current status of MySQL server

If you wish to find out 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 status of all MySQL server variables and values

Sometimes you require to check the status of all running MySQL server variables and its 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 MySQL server using following mysqladmin command.

See All MySQL Server Variables And Values

Create a database in 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 MySQL server

Sometimes you require to drop a database in MySQL server, in that case you can use the following command. Press ‘y‘ when you are asked for the 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 following command to shutdown MySQL server safely.

Shutdown MySQL Server Safely

Type following commands to Start or Stop MySQL server.

To stop MySQL server

Stop MySQL Server

To start 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 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 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

Following command will tell MySQL server to write debug information in MySQL log file.

Store MySQL Server Debug Information To Logs

Was this answer helpful?

« Back