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.

Thank you for the clarification. It seems like the authentication method for the root user in your MariaDB installation is set to use socket authentication, which allows the root user to access the database without providing a password.

To set a password for the root user and switch to native authentication, as you mentioned, you can use the following SQL command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

Replace 'new_password' with the password you want to set for the root user.

After executing this command, the root user will be required to provide a password when logging in to MariaDB using the mysql command-line client or other tools.

Thank you for providing the correct approach for setting the root password in your MariaDB installation.

Exactly my error and your solution helped. Thanks million!

This helped me too, thanks! Ubuntu 18.04

Thanks for sharing this information.

The var/run/mysqld should be present and have the exact ownership. A common set of permissions is 750, which gives read, write, and execute permissions to the owner (mysql), and read and execute permissions to the group.

Regards

Heya,

You can review the MariaDB error log (/var/lib/mysql/*.err) for any error messages or warnings that might indicate the cause of the issue. Look for any entries related to password changes or authentication failures.

You can try resetting the root password again using the mysql_secure_installation script or by manually updating the password in the MySQL user table.

Regards

Edit your my.cnf file (probably at /etc/mysql/mysql.conf.d/mysqld.cnf) to add the line skip-grant-tables

Then restart mysql service normally and follow steps above starting with mysql -u root

Remember to remove skip-grant-tables later (security!!!)

Command: sudo nano -w /etc/mysql/mysql.conf.d/mysqld.cnf More info: https://www.codero.com/knowledge-base/content/33/296/en/how-to-reset-your-root-mysql-password.html

You can review the MySQL error log file (error.log) for any error messages or warnings that might indicate the cause of the connection issue. The error log file is typically located in the MySQL data directory (/var/log/mysql/error.log or /var/log/mariadb/mariadb.log).

If the MySQL or MariaDB service is running but you’re still unable to connect, there may be an issue with the MySQL configuration. Check the MySQL configuration file (my.cnf or my.ini) to ensure that the socket file path is correct. The socket file path is typically specified under the [mysqld] section. If the path is incorrect, update it to point to the correct location.

Regards

The error message “A mysqld process already exists” indicates that there is already a MySQL or MariaDB server process running on your system. You need to stop the existing MySQL/MariaDB server before running mysqld_safe with --skip-grant-tables. You can stop the MySQL/MariaDB server using the following command:

sudo systemctl stop mysql

Once the server is stopped, you can proceed with running mysqld_safe:

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

This command should start the MySQL/MariaDB server with the grant tables skipped.

After running mysqld_safe with --skip-grant-tables, you should be able to log in to MySQL/MariaDB as the root user without a password. If you’re still encountering an “Access denied” error, it’s possible that there is another issue with the MySQL/MariaDB configuration. Try restarting the MySQL/MariaDB service and then attempt to log in again:

sudo systemctl restart mysql mysql -u root

If you continue to encounter the access denied error, there may be other configuration issues that need to be addressed.

To exit the MariaDB command-line interface, simply type exit and press Enter:

exit

This will exit the MariaDB command-line interface and return you to the terminal prompt.

There might have been updates to the tutorial to cover some minor issues that may occur. I believe the article should work just fine in it’s current revision.

Regards

You can review the MySQL error log file (error.log) for any error messages or warnings that might indicate the cause of the connection issue. The error log file is typically located in the MySQL data directory (/var/log/mysql/error.log or /var/log/mariadb/mariadb.log).

If the MySQL or MariaDB service is running but you’re still unable to connect, there may be an issue with the MySQL configuration. Check the MySQL configuration file (my.cnf or my.ini) to ensure that the socket file path is correct. The socket file path is typically specified under the [mysqld] section. If the path is incorrect, update it to point to the correct location.

Regards

There might be a difference between the MySQL and MariaDB commands syntax so this is something to always keep in mind.

Regards

Thanks for sharing this information as it may help other users who run into the same problem. Also thank you for taking your time to share the solution. You can always reach out in our community whenever you want to share something about SySadmin, DigitalOcean and beyond.

https://www.digitalocean.com/community/questions

Regards

You can reset the MySQL root password if the one in the file is not working or simply re-deploy the phpMyAdmin installation again.

We have an article on how to reset the MySQL/MariaDB root password which you can check here

https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password

Regards

My first approach will be to check the MySQL error log file for any additional information about the error. The error log file is typically located at /var/log/mysql/error.log. Review the contents of this file for any error messages or warnings that might help diagnose the problem.

You can check this article on how to troubleshoot the most common MySQL errors

https://www.digitalocean.com/community/tutorial-series/how-to-troubleshoot-issues-in-mysql

Regards

The kill command is used to send signals to processes. The correct syntax for killing a process with a known PID is sudo kill PID, where PID is the process ID of the process you want to terminate. It seems like you provided a file path (/var/run/mysqld/mysqld.pid) instead of a PID.

If you’re trying to stop a running process and don’t have the PID, you can use the killall command to kill processes by name. For example, to kill the MySQL server process, you can use:

sudo killall mysqld

This command will terminate all processes with the name mysqld.

Regards

In such cases, it’s common for installation scripts to modify MySQL user accounts and configurations.

Since you were able to connect to your database using the admin account with a new password, you’ve already taken a good step forward. However, it’s important to ensure that your MySQL or MariaDB installation is in a secure state, especially if unexpected changes were made during the failed installation.

Regards

If running a MySQL or MariaDB command with an ampersand (&) at the end doesn’t send the process to the background as expected, and you find that the prompt is stuck in the terminal window, it suggests that there may be some issues with the way the command is being executed.

You can check if there are any custom shell configurations or aliases that might be affecting the behavior of the ampersand (&). Some configurations or aliases may override the default behavior of the ampersand.

Regards

The error message indicates that the MySQL client is unable to connect to the MySQL server through the Unix socket /opt/bitnami/mariadb/tmp/mysql.sock.

You can double-check the path to the MySQL Unix socket file (mysql.sock). The default location for the MySQL socket file is usually /var/run/mysqld/mysqld.sock. If your MySQL server is configured to use a different location, ensure that the MySQL client is configured to use the correct socket path.

Also eview the MySQL configuration file (my.cnf or my.ini) to verify the socket path configuration. Look for the socket option in the [mysqld] section of the configuration file. If the socket option is set to a different path, update it to the correct path.

After making any changes to the MySQL configuration, restart the MySQL service to apply the changes:

sudo service mysql restart

The socket file should be owned by the MySQL user and group, and its permissions should allow the MySQL client to access it.

Regards