Basic mysql admin commands

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';
FLUSH PRIVILEGES;
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 192.168.2.10 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:  http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/     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)