Posts Tagged ‘Privileges’

How to change MySQL server root password

Friday, July 29th, 2011

MySQL pass dialog Debian

I had to change my mysql root password for one of the servers since during the install I mispasted the password in the MySQL password prompt I needed the pwd to be changed.

Here is how I changed it to my desired one:

linux:~# /usr/bin/mysqladmin -u root -p'OLD_PASSWORD_STRING' password NEW_PASSWORD_STRING
linux:~#

The password gets changed immediately 😉

If a new password has to be set to a passwordless mysql server, the command to be issued is:

linux:~# /usr/bin/mysqladmin -u root password PASSWORD_STRING

Changing the MySQL password is also possible with mysql cli, after connecting to the sql server, though this method is a bit more time consuming. Here is how to do it from mysql console:

linux:~# mysql -u root -p
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW_PASSWORD”) where User=’root’;mysql> flush privileges;

Of course it’s possible to do change the root pass via phpmyadmin
Cheers 😉

How to delete MySQL user using mysql cli command

Monday, September 24th, 2012

I decided to clean up a bit my MySQL obsolete users. I use to test free software every now and then and often in the hurry I forgot to clean up the respective soft database and created user in database/table mysql.user.

This is how this tiny article get born. Deleting users in version MySQL 5.0 and higher use command:

mysql> DROP USER username@localhost;

On older MySQL versions; 3.x / 4.x the SQL query is a bit longer:

mysql> DELETE FROM user WHERE User= 'type_your_user_name_here' AND Host= 'localhost'; mysql> FLUSH PRIVILEGES;

Well that's all, now user is wiped out, Enjoy 🙂