Retrieving MySQL root passwords

By using a MySQL database such as phpMyAdmin on your web server, you can manage your database. Though phpMyAdmin poses security threats and thus some prefer SSH which you can use on a Windows machine through the PuTTY software (terminal) which you have to download. On Mac or Linux, there is a built-in terminal see below.

The example above shows a user logging into MySQL with the root password. Always keep the password safe. In case you forget it (hence why you are reading this), you can retrieve it as explained below.

1. Using SSH, log into your server as root. For a safer method, run the following commands as a root user: su/sudo.
2. Run the command cd /etc/my.cnf to access the MySQL config file
3. Using command or any text editor, view the my.cnf file by running cat my.cnf

Resetting MySQL root password

1. Login to your server using root user privileges. Use SSH.
2. Stop the database service by running service mysql stop
3. Run the command –skip-grant-tables to bypass password authorization after restarting your database, though this is considered highly insecure; thus next commands have to be within a brief period. Use the symbol ‘&’ at the end to ensure that the command runs parallel with commands coming after: mysqld_safe --skip-grant-tables &
4. Login to your database mysql -u root
5. At the prompt, change the password by running UPDATE mysql.user SET Password=password('NEWSTRONGPASSWORD') WHERE User='root'; replace with password of your choice
6. Next, run the command at the MariaDB> prompt
   FLUSH PRIVILEGES;
exit;
7. As the database server shuts down, you will be asked to type the root password at which point you will key in the new password
   mysqladmin -u root -p shutdown
8. Start MySQL normally
   service mysql start
9. Update the MySQL root password in the file /etc/my.cnf to be able to add new databases or users. This is only needed on certain versions. Assuming your file does not have the password in clear text, like figure 2 above, you can ignore this step.

Other Basic MySQL Commands

See available databases, so you can see a list.
SHOW DATABASES;

Creating a database, so you can build your tables.
CREATE DATABASE DatabaseNameHere;

Delete a database, when you no longer need it.
DROP DATABASE DatabaseNameHere;

Use a particular database, to run your SQL statements against
USE DatabaseNameHere;

Find out the database you are currently using, so you don’t run a query on the wrong one!
SELECT database();

Create a MySQL account, so you can assign a user to access a database
CREATE USER 'UsernameHere'@'localhost' IDENTIFIED BY 'PasswordHere';

Grant the user privileges, allowing the user to perform a role.
GRANT ALL PRIVILEGES ON DatabaseNameHere.* TO 'UsernameHere'@'localhost';

Find more information at www.mariadb.org