CODEWITHSUNDEEP
mysqlsymfonydoctrine-ormmaster-slave
George Irimiciuc
George Irimiciuc

Reputation: 4633

Automatically connect to MySQL master if slave not working in Symfony2

I've set up a master-slave connection that works just fine. It seems to read from the slave and write to the master. For the MySQL part I used this guide. Any modifications done to the master are immediately applied to the slave as well. Both databases use the 5.6 version.

doctrine:
    dbal:
        default_connection:   default
        connections:
            default:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8

                slaves:
                    slave1:
                        host:     "%sdatabase_host%"
                        port:     "%sdatabase_port%"
                        dbname:   "%sdatabase_name%"
                        user:     "%sdatabase_user%"
                        password: "%sdatabase_password%"

However, if the slave's mysql server is down (or badly configured), wouldn't it make sense for the connection to be made to the master? In this case, the whole app crashes and I find that a bit annoying.

I'd like to know if there is a way to connect to the master if the slave is not working. Or please tell me if it's a bad practice.

I believe it has to do with MasterSlaveConnection since that one seems to be used when slaves are configured for a dbal connection, otherwise Connection is used.

Upvotes: 3

Views: 1452

Answers (1)

eRIZ
eRIZ

Reputation: 1507

I think the most comfortable option would be (at this context) to set-up a HAProxy instance in front of MySQL-slave server. Then specify HAProxy as slave server in Symfony configuration. If proxy detects it's (slave) down, it will transparently pass-through whole connection to the master server.

See some examples how to setup HAProxy with behavior I described:

https://www.digitalocean.com/community/tutorials/how-to-use-haproxy-to-set-up-mysql-load-balancing--3

Upvotes: 1

Related Questions