Plesk Error: Table mysql.servers doesn’t exist

You may receive a “Table ‘mysql.servers’ doesn’t exist” error message while adding a database user in Plesk or while restarting the MySQL service. The complete error message is:

Error: Connection to the database server has failed:
Table 'mysql.servers' doesn't exist

OR

Can't open and lock privilege tables:
Table 'mysql.servers' doesn't exist

This problem occurs when MySQL server is upgraded from an older to a newer version and the upgrade remains incomplete. Since MySQL often introduces new tables with the newer versions, you need to run the “mysql_fix_privilege_tables” script located in the “/usr/bin/” directory so that the MySQL administrative database is updated with the latest contents thus fixing the privileges of the database users as well.

To fix the issue, SSH to your server as root and execute the command:

# mysql_fix_privilege_tables --user=admin --password=`cat /etc/psa/.psa.shadow` --verbose

This will resolve the issue and allow new user additions and MySQL configuration changes.

Lost MySQL Password

If you have accidentally lost or mis-placed your MySQL password, you may reset it as follows:

1. Login to your server as root.

2. Stop MySQL: /etc/init.d/mysqld stop

3. Start MySQL in SafeMode: /usr/bin/mysqld_safe –skip-grant-tables &

4. Connect to MySQL: mysql -h localhost

5. Select the MySQL management database: use mysql;

6. Update the MySQL password: update user set password = password(‘newpassword’) where user = ‘root’ and host=’localhost’;

Note: Change newpassword to your new password.

7. Exit MySQL: quit

8. Stop MySQL in safe mode by running: ps -aux | grep mysql, stop the MySQL service using: kill <service ID>

9. Restart MySQL: /etc/init.d/mysqld start

10. Login to your MySQL instance using your new password: mysql -uroot -p, done!

PHP 5.2.x & MySQL 5.0.x Upgrade for Plesk 8/9

Plesk generally runs a version or sub-version behind in their available PHP builds for Plesk. To safely upgrade PHP and MySQL on a production Plesk 8 or 9 server, please follow these instructions.

Step 1) Install the Atomic Channel:

$ wget -q -O – http://www.atomicorp.com/installers/atomic.sh | sh

Step 2) Upgrade PHP and MySQL:

$ yum upgrade php mysql

Step 3) Replace the PHP 4 php.ini with PHP 5.2.x’s (if applicable):

$ cp /etc/php.ini /etc/php.ini~backup

$ mv /etc/php.ini.rpmnew /etc/php.ini Continue reading “PHP 5.2.x & MySQL 5.0.x Upgrade for Plesk 8/9”

MySQL CLI for Beginners

Introduction

I find that the majority of webmasters (and even some sysadmins!) who work with MySQL use phpMyAdmin as a web-based front-end management tool for MySQL. Whilst phpMyAdmin makes database management a breeze, for those interested in some ‘command line interface’ experience with MySQL, I’ve pieced together some beginner notes from various websites…

Creating a Database

In order to create a database you need to have the PRIVILEGES- this may be because you are the root user or you (or you systems administrator) has created an admin user that has ALL PRIVILEGES over all databases. In these examples a user called ‘admin’ has been created precisely for this purpose. Creating a database is fairly straightforward.

Logging In

A reminder of how to start the MySQL Client Software, and as we are not concerned with manipulating just one database we don’t have to specify a database as part of our startup command.

$ mysql -u <username> -p
Enter password: Continue reading “MySQL CLI for Beginners”