Question 63726 in PhpMyAdmin Question posted in
The official documentation can be found here.

How to create super user in MariaDB MySQL – Phpmyadmin

I have MyCloudMirror NAS box and I accidentally delete root user from MySQL now I can’t login to mysql.

I have uninstall and reinstall the phpmyadmin app but that did not make any different

I also try following but nothing is working

mysqld_safe --skip-grant-tables &

below command show that all permission are assign

CREATE USER 'root'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

this doesnt assign all permission the trigger are uncheck

CREATE USER 'root'@'%' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

If I try to check triger from myphpadmin and click on apply i get error message

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ REQUIRE NONE WITH GRANT
OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; MySQL said:
Documentation

#1045 – Access denied for user ‘root’@’localhost’ (using password: NO)

enter image description here
enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is how I fixed the issue

    Run /usr/bin/mysql_reset.py --reset

    The above command reset all settings on DB


  2. 0

    GRANT ALL PRIVILEGES is not enough.

    On MySQL 8+ you must execute the next statements:

    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, 
      FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, 
      CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, 
      REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, 
      CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE 
ON *.* TO `root`@`localhost` WITH GRANT OPTION;

GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,
      AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,
      CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,
      FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,
      INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,
      PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,
      RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,
      SERVICE_CONNECTION_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,
      SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN 
ON *.* TO `root`@`localhost` WITH GRANT OPTION;

GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION;

FLUSH PRIVILEGES;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search