Wednesday, March 4, 2009

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;

No comments: