Setting mysql usernames and passwords

Question

Hi

I have recently installed easyPHP at home to do some development work with my computer running as the server (Windows XP).

easyPHP sets up apache, php and mysql to work on your computer. I am having trouble setting the usernames and passwords for the mysql accounts (sadly, I've only ever done this through cPanel).

The easyPHP GUI lets you log into mySQL through what I assume is 'root' with no password but I'd like to change this ASAP.

Any advice? Thanks.

Answer 1

Next piece of code (copied from http://www.debian-administration.org/articles/39) creates a user paul (@localhost) with a password, and gives it full rights to the pauldb database. All is done from within the MySQL prompt:

mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> set password for paul = password('mysecretpassword');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
mysql> exit;

Answer 2

Well, if you're just looking for some way to change the root password, and you have a way to run SQL commands, you're looking for the "SET PASSWORD" command: http://dev.mysql.com/doc/refman/5.0/en/set-password.html

For creating other users and setting them up with access to particular databases, you're going to want to look through most of the parts of the Account Management chapter of the manual: http://dev.mysql.com/doc/refman/5.0/en/account-management-sql.html

Answer 3

you can create a user by this command:

create user user@host;

example:

create user farzad@localhost;

and then you my set a password for the user. you can use this line to create a password for current user you are logged in:

set password = password('mynewpassword');

in the above command, the password() function converts the string parameter to a hashed password that will be used by mysql to authenticate users. if you use this line:

set password = 'mynewpassword';

then you have entered your string as your password and it is not a hashed value, so you will not be able to login again later. so do not forget to use the password() function.

to change password for another user, use this command:

set password for user@host = password('userpassword');

example:

set password for farzad@localhost = password('dalkXfda23423');

after creating a user and securing login with a password, you need to set permissions for the user. use the GRANT command to do this. general syntax is like this:

GRANT {PERMISSIONS} ON db.table TO user@host;

in this command, permissions are a comma separated list of permissions, like SELECT, INSERT, CREATE, DROP, DELETE. you can use ALL as a high level permission so the user can do most of the things. db.table specifies the tables of a certain database where the user might do actions. so you can restrict a user to only a table of a database, or use the wildcard * to permit on all of tables or databases. example:

GRANT ALL ON myDb.* to farzad@localhost;

if you have not created a user, or set a password, by using the grant command you can create the user, set the password for him, and grant permissions all at once. syntax is like this:

GRANT {PERMISSIONS} ON db.table to new_user@host IDENTIFIED BY 'userpassword';

example:

GRANT ALL ON myDb.* to [email protected] IDENTIFIED BY 'dalkXfda23423';