HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

PHPMyAdmin for multiple servers w/ single pma database

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
serverspmaphpmyadmindatabasesingleformultiple

Problem

We have PHPMyAdmin setup for our various MySQL instances. The annoying part is the current setup is requiring an individual pma database on each instance. Because of this the severs that are slaves cannot run in readonly mode. (PHPMyAdmin needs to write to the pma database).

Can someone offer advice or point me to an appropriate walk through on how to set this up?

Solution

I have a little surprise you : Did you know that if you have the SUPER privilege, you can write to a read_only database ?

According to the MySQL Documentation on read_only:

This variable is off by default. When it is enabled, the server
permits no updates except from users that have the SUPER privilege or
(on a slave server) from updates performed by slave threads. In
replication setups, it can be useful to enable read_only on slave
servers to ensure that slaves accept updates only from the master
server and not from clients

All you need to do is connect to the slave as a DB user with the SUPER privilege and write to the pma database to your heart's content.
UPDATE 2011-10-24 23:33 EDT

On the slave servers, you must add this /etc/my.cnf and restart mysql

[mysqld]
replicate-ignore-db=pma


That way, each pma database can be specific to the local DB instance. As for writing, you must use SUPER privilege to bypass the read_only restriction. Only other alternative is to drop the read_only option from /etc/my.cnf and restat mysql.

Code Snippets

[mysqld]
replicate-ignore-db=pma

Context

StackExchange Database Administrators Q#7180, answer score: 2

Revisions (0)

No revisions yet.