Jump to content
Automation Theory

Guide: Resetting the root password in MySQL

Recommended Posts

The root password is something that is easy to forget about until you really need it. Then it becomes a wild goose chase looking through password managers, documentation systems, and any other place that password might be used. This guide is meant for the situation where database maintenance is required and the root password is unknown.
Items worth noting
  1. Every super admin account in Automate has the same permissions in the database as the root user.
  2. If your server has been around for a while (pre 10.5) it might be plain text in the registry under the key HKLM\Software\WOW6432NODE\LabTech\Setup
  3. There's nothing special about the root account -- it's very similar to Administrator in Windows. Best security practice is to create a new account with the super permission and not use root (unfortunately in the CW Automate world this best practice is difficult to implement).
IMPORTANT: This guide is actually going to instruct you how to create a new account with the super and grant permissions -- we won't actually be resetting the root password. The root password used at server install is encrypted and stored in the registry and is used by Automate patches and other support utilities. If you change the root password you're going to break things  in Automate down the road. An example, the 2020.2 patch:
Now, without further ado, the new super account creation process. To begin, here's a screenshot to show that we're locked out of a new MySQL 5.7 instance:
[In case you're wondering, MySQL 5.7 randomly generates a root password if one is not specified, and it's recorded in the error file.]
  1. Create a text file with the following commands, and save it as a .sql file:
    CREATE USER 'rootbeer'@'localhost' IDENTIFIED BY 'abc123!';
    GRANT ALL PRIVILEGES ON * . * TO 'rootbeer'@'localhost' WITH GRANT OPTION;
    GRANT PROXY ON ''@'' TO 'rootbeer'@'localhost' WITH GRANT OPTION;

    I'll be calling the account 'rootbeer' and using an insecure password for the sake of demonstration. Good security practice is strongly advised.

  2. Stop the service LabMySQL
  3. Open an administrative command prompt, and navigate to the bin folder of your MySQL install, wherever mysqld.exe is located.
  4. Start MySQL passing the script you created in step one as the init-file parameter.
    mysqld.exe --console --init-file C:\path\to\reset.sql


  5. When the server starts it should show in its output that it ran the script. Press CTRL+C to shutdown the server once you see this. As you can see in the other window, we're successfully able to login.
  6. Start LabMySQL again.
  7. Login as your new super account. As you can see here, ours has all global privileges and the DBA role.
Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/ 
  • Like 1

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...