MySQL useful commands

Login to mysql root user

#mysql –u root –p

or

#mysql -u root -h myserver-sever.com -p

Create new database

>CREATE DATABASE DUMPDB;

Provide privileges to the database and users

>GRANT ALL ON DUMPDB.* TO demo1@localhost IDENTIFIED BY ‘root’;

>mysql -u demo1 –p

Password:

To change mysql user password:

>SET PASSWORD FOR 'user name'@'localhost' = PASSWORD('root');

>FLUSH PRIVILEGES;

>exit

To login without password

mysql -u $MYSQL_ROOT -p $MYSQL_PASS -h 192.168.1.31

Create the .my.cnf and add

#vi  ~/my.cnf/

[client] user=alice password=alice_passwd host=192.168.1.31

to add privileges  authentication

#chmod 0600  /etc/my.cnf

Forgot password recovery :

To recover it by typing the below commands.

#/etc/init.d/mysqld stop

#mysqld_safe --skip-grant-tables &

#mysql –u root

>use mysql;

>update user set password=PASSWORD("password") where User='root';

>flush privileges;

>exit

#/etc/init.d/mysqld restart