mysql commands (1)

Following are some useful mysql commands that I learned today

1. Reset mysql root password

1.1 Stop mysql service

service mysqld stop

1.2 Start mysql in safe mode by skipping grant table

mysqld_safe --skip-grant-table

1.3 Enter as root user

mysql -u root

1.4 Change the root password

UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';

1.5 Flush mysql privileges

flush privileges;

1.6 Kill mysqld_safe and mysqld

2. Add a database (Login to mysql as root for all the below operations)

create database ;

3. Add a new user

The command below will add a user. But there is a catch here. The 'password' should be the hash value of the password. To get the hash of a password, use

select PASSWORD('MyPlainTextPassword');
It will output a value like "1b25e4042f3f780d". Use this in the next command.

grant all privileges on db_name.* to 'user'@'localhost' identified by password '1b25e4042f3f780d';

4. Drop a user

use mysql;
drop user;