patternsqlMinor
PHPMyAdmin for multiple servers w/ single pma database
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?
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
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.
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=pmaThat 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=pmaContext
StackExchange Database Administrators Q#7180, answer score: 2
Revisions (0)
No revisions yet.