Not the answer you need?
Register and ask your own question!

root user doesn't have privileges for administration

ipcmlripcmlr Current User Role Contributor
I have a newly installed mysql instance.
I need to have the root user be the administrator.
Version is mysql  Ver 8.0.20-11 for Linux on x86_64 (Percona Server (GPL), Release 11, Revision 5b5a5d2)
Running in centos 7.8.2003(core)

I did a sudo start service mysql and that created all the data file/directories.
I log in as root to mysql but I have hardly any privs and cant even see the mysql database.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| [email protected]%         |
+----------------+
1 row in set (0.00 sec)

mysql> select user();
+--------------------+
| user()             |
+--------------------+
+--------------------+
1 row in set (0.01 sec)

mysql> show grants for root
    -> ;
+-------------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                     |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%'                                                                      |
| GRANT ALL PRIVILEGES ON `mplustemp`.`ExpertReviewLatestCompleteDates` TO 'root'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mplustemp`.`LatestCompletingDates` TO 'root'@'%' WITH GRANT OPTION           |
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'130.14.165.30' for table 'user'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mplustemp          |
+--------------------+
2 rows in set (0.44 sec)



Answers

  • jriverajrivera Percona Support Engineer Percona Staff Role
    @ipcmlr I can't seem to reproduce your case. I have the same setup running Percona Server for MySQL 8.0.20 on CentOS 7 and started the mysqld instance. Logged in and was required to changed root temp password.
    I would suggest initializing the data directory after move the data directory since your setup seems like a fresh instance.
    systemctl stop mysql
    mv /var/lib/mysql /var/lib/mysql-old
    mkdir /var/lib/mysql && chown -R mysql:mysql /var/lib/mysql
    mysqld --initialize --user=mysql
    systemctl start mysql

  • ipcmlripcmlr Current User Role Contributor
    @jrivera thanks. It worked.
    Looking back now I think it may have been something to do with me changing the root password and using mysql_config_editor for passwordless login.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.