RunCloud Team
Ever forgotten or just wanted to change your MySQL password? You’ve come to the right place – in this guide, we’ll walk you through how to set, change and recover your MySQL root password as well as other useful MySQL commands.
RunCloud uses MariaDB, an enhanced drop-in replacement for MySQL, as a database. MariaDB is a community-driven package 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 as well.
On RunCloud, there are a few ways to manage your MySQL server and database. You can manage either by accessing the server using a terminal and run the commands from there or you 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 like phpMyAdmin because of the potential security vulnerabilities that it poses. But it remains popular as it is fairly simple to use, even for non-technical people, and it provides an easily accessible tool that can save you time when you just need to run some simple tasks.
However, if you want to avoid any of these risks then SSH is the way to go. If you have no prior experience managing MySQL, don’t worry, with RunCloud, it is as easy as ABC.
First, you need a terminal to work on. On a Windows machine, you can download the PuTTY software. If you 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 end of the RunCloud agent installation on your server (example below is installation on DigitalOcean) is meant to be kept for safekeeping elsewhere, but if you forgot to copy it down, you can still retrieve it.
Pic1: Keep the MySQL password somewhere safe!
How to retrieve MySQL root password
- Log in as root into your server through SSH (eg: puTTY/terminal/bash). Alternatively, run the commands that follow as
suorsudoas 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).
cat my.cnf
Pic 2: Viewing the MySQL root password
How to Reset/Change MySQL Password
- Login to your server using SSH.These commands must be run with root user privileges. Although you can 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 as su or sudo commands using those administrative privileges. You can also close the incoming traffic for MySQL port (default port is 3306 unless you changed it to something else) temporarily at the firewall if you want to be extra careful.
- Stop the MySQL service.
service mysql stop
- Restart MySQL service in single-user mode and bypassing password authorization, by running the command below. Since the command
--skip-grant-tablesenables 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 it ensures that the command 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, change the root password by running the commands below. Replace the NEWSTRONGPASSWORD with your chosen new strong password.
UPDATE mysql.user SET 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. Before the MySQL server shuts down, you will be prompted to key in the root password. Key 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 it again.
- Lastly, you need to update the MySQL root password in the file /etc/mysql/my.cnf (as shown in Pic2) or you might not be able to add new database or user. You can use vi, vim or nano to edit the file.
nano /root/.my.cnf
Pic 3: Resetting the MySQL root password
Other Basic 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. Below are some of the basic commands that should get you going.
When you run the commands below, replace the database name of your choice at the DatabaseNameHere.
To show the available databases.
SHOW DATABASES;
Creating a database.
CREATE DATABASE DatabaseNameHere;
Delete a database. You need a ‘drop’ privilege (or root user) to drop a database and the database must not be used (open for reading or writing by any user) at the particular time. Be careful when running this command as it will not prompt for confirmation and it will drop all the tables inside the database.
DROP DATABASE DatabaseNameHere;
To use or operate on a particular database. The MySQL prompt will change to reflect the database name that you have specified.
USE DatabaseNameHere;
To find 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 you preferences.
CREATE USER 'UsernameHere'@'localhost' IDENTIFIED BY 'PasswordHere';
To grant the user privileges. (This user will only be able to connect only from the local host)
GRANT ALL PRIVILEGES ON *.* TO 'UsernameHere'@'localhost' -> WITH GRANT OPTION;
These are just some of the basic commands that you can run using the command line. The same commands above can also be done using the RunCloud control panel. If you 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
