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'@'' for table 'user'
mysql> show databases;
| Database           |
| information_schema |
| mplustemp          |
2 rows in set (0.44 sec)


  • 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.