Registering Existing MySQL Databases in Plesk

Many businesses use Plesk to manage their webserver, what happens though if you import databases from the command line rather than through Plesk? The databases will be valid, and will work but won't be visible in Plesk (meaning no PHPMyAdmin access).

There are a lot of solutions to this listed on the net, but all either seem to carry the potential for extended downtime or are quite mandrolic. This document details an alternative method, which should hopefully leave downtime at less than a minute, but only applies to Plesk running with a MySQL server (usually on Linux or similar).

So to summarise, we've imported a database by using something similar to

mysql < somefile.sql

from the shell, but now can't see the database in Plesk. We don't want the potential downtime involved in backing up, deleting and re-uploading the backup of a database and we want to avoid having to manually register the database in the Plesk database (psa).

To achieve this task, you'll need the following;

• PHPMyAdmin access to the mysql database (called mysql) - Only if you've already created the users too
• Shell access
• Database username/passwords for each user you wish to access the database(s)

I find the task easiest using a dual screen setup as it saves flicking between windows.

First login to the shell (usually via SSH), now we need to find out where the MySQL datadir is (usually /var/lib/mysql), so run the following;

grep datadir /etc/mysql/my.cnf
# If that fails, try grep datadir /etc/my.cnf

This should return something like datadir = /var/lib/mysql

Next change to the correct directory

cd /var/lib/mysql

If you list the contents of this folder, you should see all of your databases listed, using the name of the database you want to register in plesk run the following command (downtime starts here)

mv mydatabase mydatabase.bkup

Now in Plesk, under the domain you wish to register the database with select 'Add database'. Enter the database name and press Add.

If you run ls in the shell again you should see mydatabase and mydatabase.bkup. Next run

rm -rf mydatabase
mv mydatabase.bkup mydatabase

The database is now registered in Plesk, but you'll also need to create the database user. If you created the user from the command line when you restored the backup, you need to delete them! The easiest way to do so is to access the table 'user' in database mysql using PHPMyAdmin. Find the user and delete them.

Add the new user to the database in Plesk (Remember if you have already deployed database reliant code you must either use the username/password configured in those apps or reconfigure the apps with the new username/password).

The database is now registered and accessible through Plesk! Rinse and repeat for every database that needs to be registered.