How To Find Mysql Database Password

Ever forgotten or just wanted to change your MySQL countersign? You've come to the right identify – in this guide, we'll walk you through how to set up, change and recover your MySQL root password as well every bit other useful MySQL commands.

RunCloud uses MariaDB, an enhanced drop-in replacement for MySQL, as a database. MariaDB is a community-driven parcel by the original developers of MySQL, with the promise of future compatibility, hence, terminal commands and software tools for MySQL database can be used for MariaDB besides.

On RunCloud, there are a few ways to manage your MySQL server and database. Y'all tin manage either by accessing the server using a terminal and run the commands from there or you tin can safely install a MySQL database manager like phpMyAdmin as a web app using the RunCloud script installer.

Some administrators do not like to use a public-facing web app similar phpMyAdmin because of the potential security vulnerabilities that it poses. Simply it remains popular as information technology is adequately elementary to use, fifty-fifty for not-technical people, and it provides an hands attainable tool that tin can relieve you fourth dimension when you lot just need to run some simple tasks.

However, if you want to avoid any of these risks so SSH is the style to go. If you have no prior feel managing MySQL, don't worry, with RunCloud, it is as easy every bit ABC.

Outset, you need a final to work on. On a Windows automobile, yous can download the PuTTY software. If yous are on a Mac or Linux, there is already a built-in Terminal app installed.

The MySQL root password that is displayed on your terminal screen at the stop of the RunCloud agent installation on your server (example below is installation on DigitalOcean) is meant to be kept for safekeeping elsewhere, but if y'all forgot to copy it down, you tin can still retrieve it.

Pic1: Go on the MySQL password somewhere safe!

How to retrieve MySQL root countersign

Log in every bit root into your server through SSH (eg: puTTY/terminal/fustigate). Alternatively, run the commands that follow as su or sudo as root user. This method is safer.
Navigate to /etc/mysql
```
/cd /etc/mysql
```
View the file my.cnf either using the command cat or use any text editing software (vi/vim/nano).
```
true cat my.cnf
```

Picture show 2: Viewing the MySQL root password

How to Reset/Modify MySQL Password

Login to your server using SSH.These commands must be run with root user privileges. Although you tin login to your server as root user, it is highly recommended not to. If you can, log in to your server as a system user with root privileges and run these commands every bit su or sudo commands using those authoritative privileges. You tin likewise close the incoming traffic for MySQL port (default port is 3306 unless you changed it to something else) temporarily at the firewall if y'all want to be extra careful.
Stop the MySQL service.
```
service mysql finish
```
Restart MySQL service in single-user mode and bypassing countersign authorization, past running the command below. Since the control --skip-grant-tables enables anyone to connect without a password, it is considered highly insecure, hence, all commands that follow must be done within a brief period. The ampersand (symbol &) at the end is required as information technology ensures that the control is being run parallel with the commands that follow afterwards.
```
mysqld_safe --skip-grant-tables &
```
Login into MySQL to connect.
```
mysql
```
At the MariaDB> prompt, modify the root password by running the commands below. Replace the NEWSTRONGPASSWORD with your chosen new strong password.
```
UPDATE mysql.user Prepare Password=password('NEWSTRONGPASSWORD') WHERE User='root';
```
At the MariaDB> prompt, run the following commands.
```
Flush PRIVILEGES; exit;
```
Stop the MySQL server using the following command. Earlier the MySQL server shuts downwardly, you will be prompted to key in the root password. Primal in your new root password.
```
mysqladmin -u root -p shutdown
```
Start the MySQL server in normal mode.
```
service mysql start
```
Verify that you are able to connect to MySQL by running the command mysql again. If you disable incoming traffic for the mysql port, please remember to enable information technology again.
Lastly, you need to update the MySQL root password in the file /etc/mysql/my.cnf (as shown in Pic2) or you might non be able to add new database or user. You can use vi, vim or nano to edit the file.
```
nano /root/.my.cnf
```

Moving picture iii: Resetting the MySQL root password

Other Bones MySQL Commands

Even though you can run some basic MySQL commands in the RunCloud control panel, it would not hurt to know how to do the same tasks in command line. Beneath are some of the basic commands that should get you going.

When you run the commands below, supplant the database name of your choice at the DatabaseNameHere.

To evidence the bachelor databases.

Prove DATABASES;

Creating a database.

CREATE DATABASE DatabaseNameHere;

Delete a database. You need a 'drop' privilege (or root user) to driblet a database and the database must non be used (open for reading or writing by any user) at the item time. Be conscientious when running this command every bit information technology will not prompt for confirmation and it will drib all the tables inside the database.

Drib DATABASE DatabaseNameHere;

To use or operate on a item database. The MySQL prompt will change to reverberate the database proper noun that you lot have specified.

USE DatabaseNameHere;

To discover out what database you are currently operating in, run the following command.

SELECT database();

To create a MySQL account. Change the UsernameHere and PasswordHere according to y'all preferences.

CREATE USER 'UsernameHere'@'localhost' IDENTIFIED BY 'PasswordHere';

To grant the user privileges. (This user will but be able to connect simply from the local host)

GRANT ALL PRIVILEGES ON *.* TO 'UsernameHere'@'localhost' ->     WITH GRANT OPTION;

These are just some of the bones commands that you tin run using the command line. The aforementioned commands above can also be done using the RunCloud control console. If you lot are new to administering a MySQL server using command line, getting used to these commands is a good start!

Categories: Tutorials, Server Management, Tips & Tricks