Technical Notebook

Updating a password can be done within mysql, by root, thusly:

mysql>use mysql;
mysql>update user set password=PASSWORD(‘[Password]‘) where user=’[Name of user]‘;

Note, to change the password from the account you are already in, you do not need to specify user i.e.:

mysql>set password=PASSWORD(‘[password]‘)

The second designation of PASSWORD is actually a function call, one that encrypts, or hashes the password you supply.

Also, keep in mind that, after any password updates you must flush privileges:

mysql>flush privileges;

And jumping back out to the command line and restarting MySql is not a bad idea, either:

mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Info taken from here and here, and, if you really get into trouble, here.

–Joab Jackson

Adding a new user is a three-step process. First you add the user account, and then you grant privileges to whatever databases that person will be using.

1. To add a user, you use the Create User command. Log onto Mysql as root, then, at the prompt, type:

mysql>create user [name of new user];

[MYSQL BUG: The documentation says you can add password onto this line, i.e. "identified by '[password]‘ but YOU CAN’T!!! You could spend all morning wondering why your new client can’t log in because of this SNAFU. Instead, you have to add the password in a separate command (see the section on adding passwords). So, step 2 is:

2. Add password for new user (see separate blog entry).

3. To grant database privileges to the user, you use the grant command. First you stipulate the privileges you want to grant, specify the name of the databases these privileges would apply to, and finally whom all this should apply to. Intuitively backwards.

mysql>GRANT ALL ON [databasename].* TO ‘[username]‘@’[somehost]‘;
mysql>GRANT SELECT, INSERT ON *.* TO ‘[someuser]‘@’[somehost]‘;

Note, that somehost specifies where the host is working from, either remotely (over a network) or on the terminal of the machine where mysql resides (localhost). You can also specify specific IP numbers, I think. If you leave @ off altogether, mysql assumes the user can access these privileges from anywhere.

Also note that using a database usually involves working with multiple files, all under the name of the database but with different suffixes. So that it is usually good to use a wildcard for the suffix of these files for grant privileges.

[MYSQL BUG: The '%' wildcard is deceptive, insofar as it does not grant privileges from the local host, as the documentation states. Instead, you have to grant a set of privileges especially for the localhost, i.e. '[someuser]‘@’localhost’.]

* * *

Dropping a user account can be done thusly:

mysql>drop user [Name of user]

Taken from here

–Joab Jackson

Locked out of MySQL? here is how you recover access: You stop MySQL. Then, you start MySQL in safe mode, then start the MySQl database itself, set the new password, flush the privileges, exit. Stop and then restart the database in normal mode:

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

From this compilation of MySQL commands..

–Joab Jackson