Your problem stems from deleteing all the root users first.
When you create a user using CREATE USER
, it simply enters a rwo in the mysql.user table. You needed to issue this command right afterwards:
CREATE USER root@localhost;
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'whatever';
If you do not issue GRANT right away, all the db Privileges are disabled (i.e., in the mysql.user table, update_priv='N', delete_priv='N', etc.)
If you have root@localhost and still cannot access it you may have to hack into it like this:
STEP 01) Restart mysql like this
service mysql restart --skip-grant-tables --skip-networking
STEP 02) Enter mysql from the command line (no password needed at this point)
# mysql
STEP 03) Do desc mysql.user
- You will see 17 rows for MySQL 4.1
- You will see 37 rows for MySQL 5.0
- You will see 39 rows for MySQL 5.1
- You will see 42 rows for MySQL 5.5
You will have to tweak each privilege since the GRANT command does not work will skip-grant-tables
is enabled
Example
For MySQL 5.1, you can enter a new root@localhost whose password is 'whatever' as follows:
DELETE FROM mysql.user WHERE user='root' AND host='localhost';
INSERT INTO mysql.user SET
Host = 'localhost',
User = 'root',
Password = PASSWORD('whatever'),
Select_priv = 'Y',
Insert_priv = 'Y',
Update_priv = 'Y',
Delete_priv = 'Y',
Create_priv = 'Y',
Drop_priv = 'Y',
Reload_priv = 'Y',
Shutdown_priv = 'Y',
Process_priv = 'Y',
File_priv = 'Y',
Grant_priv = 'Y',
References_priv = 'Y',
Index_priv = 'Y',
Alter_priv = 'Y',
Show_db_priv = 'Y',
Super_priv = 'Y',
Create_tmp_table_priv = 'Y',
Lock_tables_priv = 'Y',
Execute_priv = 'Y',
Repl_slave_priv = 'Y',
Repl_client_priv = 'Y',
Create_view_priv = 'Y',
Show_view_priv = 'Y',
Create_routine_priv = 'Y',
Alter_routine_priv = 'Y',
Create_user_priv = 'Y',
Event_priv = 'Y',
Trigger_priv = 'Y',
ssl_type = '',
ssl_cipher = '',
x509_issuer = '',
x509_subject = '',
max_questions = 0,
max_updates = 0;
STEP 04) restart mysql
service mysql restart
You can login as root@localhost from here with the password 'whatever'.
Feel free to replace whatever with the password you want.
Give it a Try !!!
UPDATE 2012-04-10 11:28 EDT
When you did the fresh installation, you will see anonymous users in mysql.user table. The anonymous users are the users that are blank. DELETE THEM IMMEDIATELY BECAUSE THAT CAN PRESENT A SECURITY RISK !!!
Here is why : Anonymous users have access to any database whose first 4 letters are test
. You can perform lots of CRUD intensive things in a test database. You may also want to rename the test databases to something completely different. Please read these links because I have addressed this issue before in the DBA StackExchange.
To confirm the need to do this, please note what MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints:
On Unix, MySQL comes with a mysql_secure_installation script that can
perform several helpful security-related operations on your
installation. The script has the following capabilities:
- Set a password for the root accounts
- Remove any remotely accessible root accounts.
- Remove the anonymous user accounts. This improves security because
it prevents the possibility of anyone connecting to the MySQL server
as root from a remote host. The results is that anyone who wants to
connect as root must first be able to log in on the server host, which
provides an additional barrier against attack.
- Remove the test database (If you remove the anonymous accounts, you
might also want to remove the test database to which they have
access).
UPDATE 2012-04-10 12:50 EDT
On a fresh installation, you can just run mysql_secure_installation . If you have loaded your users in already, you can run the bullet points yourself (suppose you want to set them to myckrit. Just run these commands if you ae not sure what mysql_secure_installation is doing):
UPDATE mysql.user SET password=password('myckrit') WHERE user='root' AND password='';
DELETE FROM mysql.user WHERE host='%';
DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.db WHERE LEFT(db,4) = 'test';
FLUSH PRIVILEGES;
Assuming your source data contained only English and Greek characters, and was mis-exported with an ISO-8859-1 to UTF-8 conversion (rather than an ISO-8859-7 to UTF-8 conversion), you can get your data back by first repeating the missed conversion the other way around, and then doing the right one.
You could use iconv
for this (available on pretty much all Linux distributions, and on Windows via gnuwin32).
You'd do something like:
$ iconv -f UTF-8 -t ISO-8859-1 < bad_dump_file.sql | \
iconv -f ISO-8859-7 -t UTF-8 > good_dump_file.sql
As an example:
$ echo -n $'\xC3'$'\x90' | iconv -f UTF-8 -t ISO-8859-1 | \
iconv -f ISO-8859-7 -t UTF-8 | hexdump -C
00000000 ce a0 |Î |
00000002
And 0xCE 0xA0
is the right UTF-8 encoding for Π.
Best Answer
You obviously have anonymous users in mysql.user
Just run the following:
I have past posts on this subject
Aug 14, 2011
: Cannot drop anonymous user from mysql.userJan 18, 2012
: MySQL error: Access denied for user 'a'@'localhost' (using password: YES)Feb 17, 2012
: MySQL : Why are there "test" entries in mysql.db?Apr 09, 2012
: Mysql users deletedJun 09, 2012
: Why ALL PRIVILEGES ON `db`.* gives access to all databases?Jun 26, 2012
: Best practices to secure mysqlAnother observation: You tried to run
mysql> drop user project;
By mysql's viewpoint, a user is identified as
user@host
To see all defined users, please run this query:
You should see something like this:
Thus, when you drop a user, it is best to use this style format as shown.
For example, to drop user
icdb
, you must run