Thursday, November 22, 2007

Create MySQL User

To create a user in mysql, issue the following:
CREATE USER 'user'@'host' IDENTIFIED BY 'password';

To create a user in mysql and assign grants, issue the following:

grant privilege on database.table to 'user'@'host' [with grant option] [identified by "password"]

Available privileges:
ALL [PRIVILEGES]
ALTER
CREATE
DELETE
DROP
FILE
INDEX
INSERT
PROCESS
USAGE
RELOAD
SELECT
SHUTDOWN
UPDATE

To revoke privileges:
revoke privilege on database.table from user

To revoke grant option:
revoke grant option on
database.table from user


To change a password:
UPDATE mysql.user SET Password=PASSWORD('password')
WHERE User='someuser';

View current user:
select user();

Show user permissions:
show grants for 'username'@'localhost';


http://www.tech-faq.com/reset-mysql-password.shtml

Reset MYSQL root password

Reset the mysql "root" password:
1) Log in as UNIX root
2) Stop the MySQL Deamon (may vary depending on OS)
/etc/rc.d/init.d/mysqld stop
3) Start mysql in safe mode
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables
4) Log into the mysql schema as root in safe mode
/usr/local/mysql/bin/mysql --user=root mysql
5) Run the update to change the password
UPDATE user SET Password=PASSWORD('YOUR_PASSWORD')
WHERE Host='localhost' AND User='root';
flush privileges;
6) Restart the MySQL server deamon
/etc/rc.d/init.d/mysqld start


Method set a root password
mysqladmin -u root password 'new-password'

http://help.hardhathosting.com/question.php/200