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.
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 MySQL server is up and running, use the following command:
Determine MySQL server version
You can determine exact version of MySQL server by following command.
Check all running processes of MySQL server
Execute the below command to list all running processes of MySQL database queries.
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 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.
See all MySQL server variables and values
You can also see all the running variables and values of MySQL server using following mysqladmin command.
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.
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.
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 following command to shutdown MySQL server safely.
Type following commands to Start or Stop MySQL server.
To stop MySQL server
To 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.
Now, run the following command with 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 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
Following command will tell MySQL server to write debug information in MySQL log file.