Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Well part of the point of this is add extra confidence actions through phpmyadmin won't perform any writes. Super can't be applied to just a specific database. Also don't want the phpmyadmin user to be able the change master to, kill. dev.mysql.com/doc/refman/5.5/en/… –  atxdba Oct 25 '11 at 3:05

I have no idea why this two year old question appeared for me, but newer versions of phpMyAdmin have the ability to store those tables on a different database server altogether. You appear to have worked around it pretty well, but for the sake of thoroughness:

Using $cfg['Servers'][$i]['controlhost'], you can actually set your phpMyAdmin Configuration Storage database up on another server entirely.

share|improve this answer
    
Yes, I have since been using that :) –  atxdba Jan 23 at 23:28
    
I'm delighted! Kind regards –  ibennetch Jan 23 at 23:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.