12

I did the following steps to use MySQL in Ubuntu:

sudo aptitude install php5-mysql mysql-server
sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &
sudo mysql -u root mysql

Change root password:

mysql> UPDATE mysql.user SET Password=PASSWORD('SecurePassword') WHERE
User='root';
mysql> FLUSH PRIVILEGES;
mysql> EXIT

Modify /etc/mysql/my.cnf:

[client]
user=root
password=SecurePassword
[mysqld]
...
default-time-zone = '+0:00'

Then:

sudo service mysql start
mysql -u root
mysql> SHOW GRANTS FOR root@localhost
+--------------------------------------------------------------------------------------------------+
 | Grants for root@localhost | 
+--------------------------------------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[here is the Securepassword]' |
 +-------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec) 

mysql>
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'  WITH GRANT OPTION;

and I receive an error:

Access denied for user 'root'@'localhost' (using password: YES)

Laurel
  • 5,965
  • 14
  • 31
  • 57
user2698684
  • 131
  • 1
  • 1
  • 4

3 Answers3

2

It was absolutely correct what you did, but I guess it's not working for one small reason.

You should use identified by password when you are going to grant privileges like this:

mysql> GRANT ALL PRIVILEGES ONE `*`.`*` TO 'root'@'localhost' IDENTIFIED BY PASSWORD
'*A4B6157319038724E3560894F7F932C8886EBFCF' WITH GRANT OPTION;
Laurel
  • 5,965
  • 14
  • 31
  • 57
Abhik Dey
  • 403
  • 4
  • 12
0

If you get an error message like this:

Warning: mysql_connect(): Access denied for user: ....

then the problem is that your application can not access the database. This can have several causes:

First of all, make sure the database settings in your db-config.php (connection file for your application) are correct, specifically the name and password of your MySQL user, the name of your database, and the name of your MySQL server.

If you're running your own server, you may need to give your MySQL user proper permissions. Log in to MySQL as the MySQL root user and issue these commands:

GRANT ALL PRIVILEGES ON database_name TO user@host IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Debug Diva
  • 26,058
  • 13
  • 70
  • 123
  • Please note that we expect answers to address the *specific* issues in the question. Please be sure to pay close attention to what language, platform, libraries, etc are being used, and be sure to look for solutions to the precise problem. For example; this question is **not** about PHP at all. – Andrew Barber Dec 18 '14 at 17:20
0

System like Ubuntu prefers to use auth_socket plugin for root account by default. It will try to authenticate by comparing your username in DB and process which makes mysql request; it is described in here

The socket plugin checks whether the socket user name (the operating system user name) matches the MySQL user name specified by the client program to the server, and permits the connection only if the names match.

Instead you may want to back with the mysql_native_password, which will require user/password to authenticate.

About the method to achieve that, I recommend you checking this out instead.

nguyen
  • 11
  • 2