MySQL mostly used commands! :)

MySQL is a popular choice of database for use in web applications.When using MySQL you will come across many commands that you can use. I will be listing some of it, which will be often used.


How to log in to MySQL and run commands from the terminal?

mysql -u newuser -p


Steps to change the password -
mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

You can exit by using exit or quit commands

There are some alternative ways to log in too..

mysql -unewuser -pnewuser; 
mysql --user=newuser --password=newuser;
mysql --user=newuser -pnewuser;
mysql -unewuser --password=newuser ; 

The password appears in these commands so you can always avoid using them.


How to execute an SQL script file?

mysql> source  path/To/The/SQL/Script/filename.sql

How to remove and create a MySQL DB?

mysql> drop database databaseName;

mysql> create database databaseName;


How to grant permission in MySQL?

GRANT ALL PRIVILEGES ON databaseName.* TO userName@machineIP IDENTIFIED BY 'password';

or

GRANT ALL ON databaseName.* TO userName@'%' IDENTIFIED BY 'password';
'%' means all the relevant IPs


Why do we use flush privileges command?
To tell the server to reload the grant tables!

FLUSH PRIVILEGES;


How to shutdown the server using terminal?

mysqladmin -u root -p shutdown;
You will be asked for the password. After entering the password you can see the MySQL server has been shut down.