Useful commands for mysql server

here are some useful commands to manage mysql server I always have to google for.

reset root password

sudo /etc/init.d/mysql stop
/usr/sbin/mysqld --skip-grant-tables --skip-networking &
mysql -u root
   FLUSH PRIVILEGES;
   SET PASSWORD FOR root@'localhost' = PASSWORD('password');
   FLUSH PRIVILEGES;
   exit;

kill %1 (to kill mysql server)

service mysql start

add user and grant full access to selectedDB

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'complexpassword';
GRANT ALL PRIVILEGES ON selectedDB . * TO 'newuser'@'localhost';

restore data from dump

from mysql client - delete database
drop database selectedDB; 

restore database from command line

mysql -u root -p selectedDB  < dump.sql

delete record from table

mysql> use MYDB;
Database changed

mysql> DELETE FROM tutorial_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>