CODEWITHSUNDEEP
mysqlmagentoreplication
perler
perler

Reputation: 183

magento mysql Master/Slave replication - Slave not used

I set up a MySQL slave and added the neccessary code to /app/etc/local.xml:

<default_read>
<connection>
<host><![CDATA[host]]></host>
<username><![CDATA[useronslave]]></username>
<password><![CDATA[secret]]></password>
<dbname><![CDATA[dbname]]></dbname>
<active>1</active>
</connection>
</default_read>

problem is, it appears the slave machine is never used for requests (using mysql monitoring)

PAT

Upvotes: 1

Views: 3456

Answers (2)

HenryHayes
HenryHayes

Reputation: 389

One of my clients uses Amazon AWS RDS Aroura (MySQL 8.x Compatible) with a cluster, read and write instance.

It took me a while to figure out why it was not using the <default_read>/<connection>. The config.xml file has <use>default_setup</use>. So no matter what you put in <default_read>/<connection> section, it's overridden by <use>default_setup</use>.

So you have to do this

<default_read>                 
    <connection>               
        <use/>
        ...
    </connection>              
</default_read>

... followed by your read instance credentials.

Setting <use/> like this overrides the config.xml's <use>default_setup</use> and solves the issue.

Upvotes: 0

Jaro
Jaro

Reputation: 3887

I think you can try this setup

You have to use Slave database for read (select queries) Operations and Master database for write( insert and update queries). Make changes in the following config file of magento: app/etc/local.xml 

<default_setup>                
    <connection>               
        <host><![CDATA[Master-host]]></host>
        <username><![CDATA[user]]></username>
        <password><![CDATA[pass]]></password>
        <dbname><![CDATA[magentodb]]></dbname>
        <active>1</active>     
    </connection>              
</default_setup>               
<default_read>                 
    <connection>               
        <use/>                 
        <host><![CDATA[Slave-host]]></host>
        <username><![CDATA[user]]></username>
        <password><![CDATA[pass]]></password>
        <dbname><![CDATA[magento]]></dbname>
        <type>pdo_mysql</type> 
        <model>mysql4</model>  
        <initStatements>SET NAMES utf8</initStatements>
        <active>1</active>     
    </connection>              
</default_read>

Prior to this setup , you must have to configure your mysql master and slave server. Configuration for master server edit /etc/my.cnf

add below content in the file: 

[mysqld]                       
server-id       = 1            
log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10       
max_binlog_size     = 100M     
binlog_do_db        = magento_demo
binlog_ignore_db    = mysql    
then restart your mysql server.
Configuration for slave server 
edit /etc/my.cnf

add below content in the file: 

[mysqld]                       
server-id=2                    
log-bin=mysql-bin              
master-host=192.168.1.2        
master-user=username           
master-password=111111         
master-port=3306               
replicate-do-db=magento_demo   
replicate-ignore-db=mysql      
master-connect-retry=60

then restart your mysql server. Let me know Jaro.

Upvotes: 2

Related Questions