Using MySQL commands with Plesk on Linux

As a server administrator, you may need to interact with your MySQL or MariaDB database server directly from the command line for various tasks such as diagnostics, backups, or advanced user management. This guide will show you how to use MySQL commands on your Plesk Linux server as the system’s root user, including how Plesk manages its MySQL administrator password and how you can set up passwordless MySQL access for convenience and scripting.

Understanding Plesk’s MySQL admin credentials

If you’ve ever encountered this error while attempting to use MySQL commands on Plesk:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

There is a reason behind it, and also, a very easy way to work around the error.

Plesk automatically creates an administrative user for MySQL ( named ‘admin’) when it’s installed. The password for this MySQL admin user is securely stored by Plesk in a special file. You’ll need this password to perform administrative tasks directly on the MySQL server.

The primary file where Plesk stores this MySQL administrator password is:

/etc/psa/.psa.shadow

This file is protected and typically only readable by the system’s root user. You can view the password using the cat command when logged in as root via SSH:

cat /etc/psa/.psa.shadow

The output will be the current MySQL admin password set by Plesk.

Accessing MySQL using Plesk’s admin password

To run MySQL commands, you’ll use the mysql command-line client. You need to specify the MySQL admin username (usually ‘admin’) and provide the password you retrieved from /etc/psa/.psa.shadow.

You can do this in a single command:

mysql -u admin -p`cat /etc/psa/.psa.shadow`

Let’s break this command down:

• mysql: The command-line client utility.
• -u admin: Specifies the MySQL user to connect as, which is ‘admin’ in this case (Plesk’s default admin user).
• -p`cat /etc/psa/.psa.shadow`: This part tells MySQL to use a password. The backticks (`) execute the cat /etc/psa/.psa.shadow command, and its output (the password) is immediately supplied to the -p option.

After running this command, you’ll be directly logged into the MySQL monitor, where you can execute SQL queries.

For example, to see all databases, once inside the MySQL monitor, you would type:

SHOW DATABASES;

To exit the MySQL monitor, type:

EXIT;

You can also execute single SQL commands directly from the bash prompt without entering the interactive MySQL monitor by using the -e option:

mysql -u admin -p`cat /etc/psa/.psa.shadow` -e "SHOW DATABASES;"

Enabling passwordless MySQL access for the root user

Constantly typing or pasting the password can be cumbersome and error-prone, especially when scripting. You can configure the system’s root user to automatically authenticate to MySQL using Plesk’s admin credentials by creating a .my.cnf configuration file in the root user’s home directory (/root/).

Creating the /root/.my.cnf file

First, retrieve the Plesk MySQL admin password:

cat /etc/psa/.psa.shadow

Copy this password. Next, create and open the /root/.my.cnf file using a text editor like nano or vim:

vim /root/.my.cnf

Add the following content to the file, replacing YOUR_PLESK_MYSQL_ADMIN_PASSWORD with the actual password you copied:

[client]
user=admin
password="YOUR_PLESK_MYSQL_ADMIN_PASSWORD"

Note: Ensure the password is enclosed in quotes, especially if it contains special characters.

Save the file and exit the text editor (Press ESC, then type :wq).

Securing the /root/.my.cnf file

Since this file contains sensitive credentials, it’s crucial to set strict permissions so that only the root user can read or write to it:

chmod 600 /root/.my.cnf

This command sets the permissions to read and write for the owner (root) only.

Using passwordless MySQL commands

With the /root/.my.cnf file configured, the system root user can now run MySQL commands without needing to specify the username or password. The mysql client will automatically read these credentials from the .my.cnf file.

To log into the MySQL monitor:

mysql

To execute a single command:

mysql -e "SHOW DATABASES;"

Other MySQL utilities like mysqlshow, mysqldump, and mysqladmin will also use these credentials automatically when run by the system root user.

For example, to list all databases using mysqlshow:

mysqlshow

To back up a specific database (e.g., my_database):

mysqldump my_database > my_database_backup.sql

Conclusion

You now know how to access MySQL as an administrator on your Plesk Linux server using both Plesk’s stored credentials directly and by setting up convenient passwordless access for the system root user via the /root/.my.cnf file. This allows for efficient database management and scripting directly from the command line. Always ensure that your /root/.my.cnf file is properly secured to protect your database credentials. If you run into any further issues, feel free to reach out to our support team.