Mysql master-slave replication breaks when issuing commands from master's phpmyadmin

Question

I have successfully configured MYSQL slave from existing master SQL server using this https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

The setup works fine and changes made to master through MySQL shell are perfectly reflected on the slave. But if I issue commands from PHPMyAdmin, the system breaks down. it shows following error

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ***.***.***.***
                  Master_User: *****
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.0000**
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.00000*
                Relay_Log_Pos: 2407
        Relay_Master_Log_File: mysql-bin.000**
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'phpmyadmin.pma_table_uiprefs' doesn't exist' on query. Default database: ''. Query: 'REPLACE INTO `phpmyadmin`.`pma_table_uiprefs` VALUES ('**', '******', '******', '{"sorted_col":"`******`.`date` DESC","CREATE_TIME":"2016-12-19 09:35:35","col_order":["1","2","3","4","5","0","6","7","8","9"]}', NULL)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2261
              Relay_Log_Space: 8323
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'phpmyadmin.pma_table_uiprefs' doesn't exist' on query. Default database: ''. Query: 'REPLACE INTO `phpmyadmin`.`pma_table_uiprefs` VALUES ('******', '******', '******', '{"sorted_col":"`******`.`date` DESC","******":"2016-12-19 09:35:35","col_order":["1","2","3","4","5","0","6","7","8","9"]}', NULL)'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

Phpmyadmin is not configured on the slave(I installed it without configuring it to slave parameters) and in the master configuration, I've explicitly set PHPmyadmin to not get replicated on slave through the following command.

binlog_ignore_db    = phpmyadmin

How to remove this error

'phpmyadmin.pma_table_uiprefs' doesn't exist'

please guide.

score 0 · Answer 1 · answered Nov 17 '17 at 08:08

0

The problem it's that the system it's trying to replicate the phpadmin tables that doesn't exist in your replica. To make it work, you need to exclude the phpmyadmin db.

In the slave mysqld.cnf file, you need to add this:

replicate-ignore-db=phpmyadmin

and restart the replica

answered Nov 17 '17 at 08:08

nacho

5,280
2
25
34

Of course, this could also be a sign that replication was not set up properly, and this suggestion only masks that problem. – Michael - sqlbot Nov 17 '17 at 12:50
@nacho, I had set binlog_ignore_db = phpmyadmin on master already, that means it should not replicate phpmyadmin. Also after making changes as suggested by you, I'm getting following error "Last_SQL_Error: Error 'Table 'phpmyadmin.pma_recent' doesn't exist'" – jugadengg Nov 19 '17 at 18:09

score 0 · Accepted Answer · answered Nov 20 '17 at 08:40

So I have solved the issue by following steps

installing MySQL and PHPMyAdmin on slave through following guide http://usefulangle.com/post/35/how-to-install-linux-apache-mysql-php-phpmyadmin-lamp-stack-on-ubuntu-16-04
importing PHPMyAdmin tables from my master DB to slave DB. The problems were occurring due to the mismatch in table names in master and slave phpmyadmin tables.

What I've not understood is why slave was replicating PHPMyAdmin tables despite setting binlog_ignore_db = phpmyadmin in master configuration.

Mysql master-slave replication breaks when issuing commands from master's phpmyadmin

2 Answers2