Useful MySql Commands
In this article, I am going to demonstrate the most use and most useful Mysql commands which are daily used by SysAdmin. I will explain you each command which is used by Sysadmin in their day-to-day work.
We are going to learn MySQL commands through terminal. Make sure you have installed Mysql server on the system.
Let’s start with the Mysql Commands one-by-one.
1. How to login MySQL Server?
To login into MySQL server use -u option for username and -p for a password. when you enter this command it will prompt you to enter a password.
$ mysql -u [username] -p [password]
2. How create normal MySQL user?
To run this command you should have root privileges on the MySQL server.
$ CREATE USER 'username'@'%' IDENTIFIED BY 'password';
$ CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
‘%’ use when you have to provide access to the remote server.
‘localhost’ use when you have local MySQL server.
3. How to view privileges/Access/Rights for the user?
This Mysql command will show you the privileges of given user. What type of rights user has on database like insert,update,delete etc.
$ show grants for 'username'@'localhost';
4. How to give or grant privileges to the user?
With help of below command, you can give access rights to the user for the database. That user can able to or has access to insert, update or delete on only given database.
$ GRANT ALL PRIVILEGES ON DbName.* TO 'username'@'localhost' IDENTIFIED BY 'Password';
5. How to flush privileges?
After granting privilege to any user. you need to run flush privileges command which can help to refresh the user privileges.
$ FLUSH PRIVILEGES;
6. How to create a MySQL database?
To create MySQL database use below simple create database command.
$ CREATE DATABASE DbName;
7. How to check or list all databases on the server?
Below Mysql command used to list down the all available database on the server.
$ SHOW DATABASES;
8. How to check running processlist & Full processlist?
With show processlist command you will find the null process, time-consuming process, stuck processes etc. on the server.
$ SHOW PROCESSLIST;
$ SHOW FULL PROCESSLIST;
10. How to drop database?
To drop single database use below drop database command.
$ DROP DATABASE DbName;
11. How to change or update password?
All MySQL user data will be stored in the mysql database. so before changing or updating any user password. You need to first use mysql database then run update command to change the password.
$ use mysql;
$ update user set password=PASSWORD('your_new_password') where User='username';
12. How to delete a user?
If you want any mysql user which is not in use then run below drop user command.
$ DROP USER 'username'@'localhost';
13. How to take backup or dump of Mysql Database?
This is an important comma.nd for every sysadmin i.e. mysqldump.
with mysqldump command, you will able to take backup of the database in a .sql format
$ mysqldump -u username -p DbName > DbName_date.sql
14. How to restore database dump?
For restoring a .sql dump file run below command by specifying the database name.
$ mysql -u username -p DbName < DbName_date.sql
15. How to take backup in gzip format?
Below command help to take direct database backup in compress format .i.e. .gz
$ mysqldump -u userName -p DbName | gzip > DbName_date.sql.gz
16. How to restore dump which is in gzip format?
Restoration of database dump which is in .gz format use below gunzip command to a uncompressed file and then MySQL command to restore the database.
$ gunzip < DbName_date.sql.gz | mysql -u root -p DbName
17. How to Kill Sleep Mysql queries?
To kill any time-consuming or null process then use show processlist command. once processlist open then check for null process PID run kill command on that PID.
$ show processlist;
$ kill PID1 PID2 PID3;
18. How to check Mysql version?
Run below command to check mysql server version.
$ mysql -u root -p version
$ mysql -v
19. How to take a dump of the single table from the database?
Sometimes we need to take a dump of the single table from the database. just use table name next to the database name and run mysqldump command.
$ mysqldump -u username -p DbName tablename > tablename.sql
20. How to Start|Stop|Restart Mysql server?
Run below commands to start,stop,restart mysql service.
$ /etc/init.d/mysql start
$ /etc/init.d/mysql stop
$ /etc/init.d/mysql restart
We have tried our best to include almost all of ‘mysql‘ commands with their examples in this article which are daily use by SysAdmins, If still, we have missed anything, please do let us know via comments and don’t forget to share with your friends.