How Can I Reset Root User Privileges in MySQL and PostgreSQL After Accidental Lockout?

Resetting Root User Privileges Using --skip-grant-tables

If you've accidentally locked yourself out of your MySQL database as the root user, fear not. Restoring all privileges to the root user can be accomplished by starting MySQL with the --skip-grant-tables option.

Windows Environment

1. Stop the MySQL service from the Administrator tools > Services.

2. Edit the MySQL configuration file (my.ini) located at:

   • For MySQL 5.5 and earlier: C:Program FilesMySQLMySQL Server 5.5my.ini
   • For MySQL 5.6 and later: C:ProgramDataMySQLMySQL Server 5.6my.ini

3. Under the [mysqld] section, add the following line:

   skip-grant-tables

4. Start the MySQL service.

PostgreSQL Environment

1. Stop the PostgreSQL service.
2. Modify the postgresql.conf file (typically located at /etc/postgresql/postgresql.conf).

3. Under the [postgresql] section, add the following line:

   fsync = off

4. Start the PostgreSQL service.

Note: The --skip-grant-tables option should be used with caution, as it temporarily disables authentication. Once you have regained access to your database, be sure to remove this option and restart MySQL.

The above is the detailed content of How Can I Reset Root User Privileges in MySQL and PostgreSQL After Accidental Lockout?. For more information, please follow other related articles on the PHP Chinese website!