← ServerPilot Docs

How to Access MySQL with the MySQL Root User

Introduction

Normally, you access each MySQL database using a separate database user. In some cases—such as accessing many different databases from the command line—it is easier to have a single MySQL account that can access any database. This privileged MySQL account is the MySQL root user.

The MySQL root user is different from your SSH root user. The only similarity is in the name, as MySQL uses a completely separate authentication system than SSH or SFTP. For security reasons, though, you only have access to the MySQL root user's password when SSH'd in as root.

Accessing the MySQL Shell as root

While SSH'd into your server as root, you can open a MySQL shell as the MySQL root user with the command:

sudo -i mysql

The reason you do not need to enter a database username and password is because the MySQL root user's credentials are stored in the file:

/root/.my.cnf

This is a special file that MySQL reads when you run it from the command line. As the MySQL root user's credentials are in that file, you don't have to remember them or enter them yourself.

Finding the MySQL root User's Password

In some cases, you may want to know the MySQL root user's password. For example, you may want to log in to Adminer or phpMyAdmin and have access to every database at once.

To find the MySQL root user's password, SSH into your server and run the following command:

sudo cat /root/.my.cnf

The contents of that file look like this:

[client]
user = root
password = MYSQL_ROOT_PASSWORD_HERE

If you ever change the MySQL root user's password, be sure to update the password in that file.