SET MYSQL root password mysqladmin -u root password NEWPASSWORD change mysql root password mysqladmin -u root -p'oldpassword' password newpass   Add MySQL users and assign privilege

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
Delete MySQL User
DROP USER ‘demo’@‘localhost’;

Change MySQL password for other users

mysqladmin -u vivek -p oldpassword password newpass

Changing MySQL root user password using MySQL sql command

1) Login to mysql server, type the following command at shell prompt: $ mysql -u root -p 2) Use mysql database (type command at mysql> prompt):
mysql> use mysql;
3) Change password for user vivek, enter:
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';
4) Finally, reload the privileges:
mysql> flush privileges;
mysql> quit
  Login to mysql from localhost
  • mysql -uusername -ppassword
eg: mysql -uroot -panjani     here username is root and password is anjani.
  • mysql -uusername -p
Enter password: Login to mysql from remote host
  • mysql -uusername -ppassword -hhostname/ip db_name
  • eg: mysql -uroot -panjani -h192.168.2.10 anj_db  where username is root, password is anjani, hostname is and db name is anj_db
Basic commands
  • create database anj_db;
  • show databases;
  • use anj_db;
  • CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
  • INSERT INTO authors (id,name,email) VALUES(2,”Priya”,”[email protected]”);
  • INSERT INTO authors (id,name,email) VALUES(3,”Tom”,”[email protected]”);
  • show tables;
  • select * from authors;
  • delete from server where server_id=3;
  • commit;
  • mysql> SELECT epoints_id, shop_id, name,registered FROM publishers;     [SELECT column lists FROM table_name;]
To Find Database size in MB
  • SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”FROM information_schema.TABLES GROUP BY table_schema ;
Change mysql root password
  • mysqladmin -u root -p’oldpassword’ password newpass
Create Database Dump
  • mysqldump -uroot -ppassword -h192.168.2.10 anj_db > anj_db.sql    [if taking dump from remote host]
  • mysqldump -uroot -ppassword  anj_db > anj_db.sql  [if taking dump from local machine]
Restoring from Database Dump
  • mysql -uroot -ppassword -h192.168.2.10 anj_db  <  anj_db.sql  [if restoring database dump on remote host]
  • mysql -uroot -ppassword  anj_db <  anj_db.sql [if restoring database dump on local host]
reference:     Few Other commands: Theme: select * from table where date =today
  • SELECT * FROM myTable WHERE DATE(myDate) = DATE(NOW())
The functions used in the MySQL query are: * DATE() returns the date without time * NOW() returns the current date & time (note we’ve used the DATE() function in this query to remove the time)