How To Reset Your MySQL or MariaDB Root Password

Please be more specific. At which step or steps did you encounter problems? Did you see any error messages? What happens when you try to restart the database?

The set of commands in the tutorial can result in setting a wrong root password by accident, but cannot result in corrupting the database and/or server configuration.

To help you with your problem more details are necessary.

do this before:

sudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld

It seems like there are two separate issues here: the missing directory for the UNIX socket file and the inability to start MySQL with the --skip-grant-tables option.

You can create the directory manually using the following command:

sudo mkdir -p /var/run/mysqld

Make sure to set the correct permissions for the directory:

sudo chmod 0755 /var/run/mysqld

After creating the directory, attempt to start MySQL again.

Once you have created the directory for the UNIX socket file, you can attempt to start MySQL with the --skip-grant-tables option again:

sudo mysqld_safe --skip-grant-tables --skip-networking &

If MySQL still fails to start with this option, there may be other issues with your MySQL configuration or installation.

You can heck the MySQL error log (/var/log/mysql/error.log or /var/log/mysqld.log) for any error messages that might indicate the cause of the failure.

You can also try starting MySQL without the --skip-grant-tables option to see if it starts successfully:

sudo systemctl start mysql

Hope that this helps!

There is a simple solution to login automatically, it’s to set your credentials in a file named .my.cnf in your user’s home directory.

nano ~/.my.cnf

And set your MySQL credentials like in the following example :

[client]
user = root
password = password

Then just use the command mysql to login into MySQL instead of mysql -u root -p. It also provide you the ability to use the other commands available with mysql-client like mysqldump or mysqladmin.

Regards

Thanks for sharing this information as other users might find it just helpful if they run into the same situation.

Regards

The note about the ALTER USER command not working and indicating a bigger problem typically suggests that there might be underlying issues with your MariaDB installation or configuration. Here are some potential reasons why the ALTER USER command might fail and how you can address them:

1. Incorrect Syntax: Double-check the syntax of the ALTER USER command to ensure that it is correct. Any typos or syntax errors can cause the command to fail. Pay attention to quotation marks, semicolons, and other syntax elements.

2. Insufficient Privileges: You need the appropriate privileges to execute the ALTER USER command. Make sure you are logged in as a user with the necessary privileges, such as the root user or another user with the ALTER USER privilege.

3. Socket Authentication: If your MariaDB installation is configured to use socket authentication for the root user, you might encounter issues when trying to set a password using the ALTER USER command. In such cases, you may need to switch to native authentication first using the mysql_native_password authentication plugin.

4. Database Corruption: In some cases, database corruption or other issues with the MySQL/MariaDB installation can prevent the ALTER USER command from working properly. You can try repairing the database or reinstalling MySQL/MariaDB to resolve any underlying issues.

5. Permissions and Ownership: Ensure that the MySQL/MariaDB data directory and files have the correct permissions and ownership. Improper permissions can prevent MariaDB from executing certain commands, including ALTER USER.

6. Version Compatibility: Make sure that the version of MariaDB you are using supports the ALTER USER command. Older versions may not support certain features or syntax used in the command.

If you encounter issues with the ALTER USER command, carefully review any error messages or warnings that are returned. These messages can provide valuable information about the underlying problem.

Regards

On the other hand I need to issue sudo kill $(sudo cat /var/run/mysqld/mysqld.pid) or actually sudo kill (sudo cat /var/run/mysqld/mysqld.pid) since my default shell is fish.