I am facing problem with mysql non root/admin user, I am following the below steps for creating user and its privileges, correct me if i am doing wrong,
i am installing mysql
on RHEL 5.7 64bit
, packages are mentioned below, once i done the rpm install
we are
- creating mysql db using
mysql_install_db
, then - starting the mysql service then
- using
mysql_upgrade
also we are doing to the server.
After this process i can login as root
but with a non-root user I am not able to log into the server:
[root@clustertest3 ~]# rpm -qa | grep MySQL
MySQL-client-advanced-5.5.21-1.rhel5
MySQL-server-advanced-5.5.21-1.rhel5
[root@clustertest3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@clustertest3 ~]# ls -ld /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Nov 30 11:09 /var/lib/mysql/mysql.sock
mysql> CREATE USER 'golden'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT USER(),CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)
[root@clustertest3 ~]# mysql -ugolden -p
Enter password:
ERROR 1045 (28000): Access denied for user 'golden'@'localhost' (using password: YES)
This is the problem I am facing, is there any solution to this?
Do not grant all privileges over all databases to a non-root user, it is not safe (and you already have "root" with that role)
This statement creates a new user and grants selected privileges to it. I.E.:
Take a look at the docs to see all privileges detailed
EDIT: you can look for more info with this query (log in as "root"):
To see what happened
Just add computer name instead of 'localhost' in hostname or MySQL Host address.
connect your server from mysqlworkbench and run this command-> ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpassword';
If you are connecting to the MySQL using remote machine(Example workbench) etc., use following steps to eliminate this error on OS where MySQL is installed
Try logging into the MYSQL instance.
This worked for me to eliminate this error.
I also have the similar problem, and later on I found it is because I changed my hostname (not
localhost
).Therefore I get it resolved by specifying the
--host=127.0.0.1
Make sure the user has a localhost entry in the users table. That was the problem I was having. EX: