Need some advice for a master/slave cluster

MySQL & MariaDB Other MySQL® Questions
1

Hello all,

We have actually a MySQL enterprise edition 5.1.33 on a Solaris server with 2 ip instance on port 3306 (dev, 42Go and prod, 48G).
I was looking for a solution for a cluster configuration and found wsrep very promising.
Oracle is not going on this way… and make minimal support of MySQL (and yum repos are only for Oracle Linux !!! :()… so we using now the Percona solution (and if all works, certainly with a support).

The first problem was to migrate all the MyIsam databases… but during the tests with the new plateform (2 HP DL360p on CentOS) some users request us, and we want to :

  • migrate to 5.5
  • change to innodb_file_per_table (we have a + 30G ibdata1 on both instance)
  • make many change to the tuning

So the decision to use a new replication (wsrep) was reported with distinct phase of survey :

  1. Migration to 5.5 with innodb_file_per_table
  2. Change tuning and make a “classic” Master/Slave configuration
  3. Migrate MyIsam tables to InnoDb and use Wsrep

Phase 1 is not a problem (thx to xtrabackup ! :)) but phase 2 have this problem :

Clients query ask “ip_dev” and “ip_prod” on port 3306
We have a bind-address on these ip so.
Our 2 server have “ip_srv1” and “ip_srv2” on the same subnet.

Wsrep configuration was working with the following solution :

  • Management of ip_dev1 and 2 with pacemaker
  • Before start mysql, attach the 2 ip on loopback interface with a /32 netmask.
  • Mysql can start in all case thx to the “false” loopback ip.
  • Wsrep ask for connexion with ip_srv*
    It’s work :slight_smile:

Now the problem… with “classic” replication :

  • Slave can’t reach master because of wrong loopback route (try many route configuration… but none work :()
  • We can’t use a “bind-address=0.0.0.0” because dev and prod are using the same port
  • The PRM solution have same problem because of the port.

I’m a sys admin, not a dba… so i maybe make many mistakes…
Did someone have a solution for this type of config ?

Thanks in advance (and sorry for my bad English… i’m french with a lack of coffee ;))

2

Just for information, i make some change to have a working solution :

  • Delete my additional loopback ip (that was a true wrong good idea ;))
  • Make a bind-adress on 0.0.0.0 with differents ports for dev and prod
  • use iptables to redirect ip_dev:3306 and ip_prod:3306 on ip_srv1/2:dev_port and ip_srv1/2:prod_port

Simpler… better… i think :slight_smile: