Need help writing to multiple database through single Magento installation

Question

I'm new to Magento (I am loving it) and developing a desktop application to run side-by-side with our Magento CE 1.7 store. The desktop application will have it’s own database but we need to fetch almost all information from magento.

So, I have already copied the whole database of magento and used it in the application’s database. What I need is to write in the application’s database too when there is something written in the magento’s database through magento’s site. We do not need to read from application’s database, only write.

What changes will I need to do and where? I would really appreciate if someone can provide me some in-depth guide. Please help me.

Thank you.

Endocranium

Why would one duplicate the db? Magento has an excellent API to which you can connect your application to. – Pegram

Answer 1

To handle more than one database you should do the following :

1°) Create the database connection in /app/etc/local.xml :

You have something like :

        <default_setup>
            <connection>
                <host><![CDATA[localhost]]></host>
                <username><![CDATA[user]]></username>
                <password><![CDATA[password]]></password>
                <dbname><![CDATA[dbname]]></dbname>
                <active>1</active>
            </connection>
        </default_setup>

To handle your mysql db. Add another node here to handle the new connexion

 <mysetup>
<connection>
<host>192.168.5.10</host> <!-- host of my local server -->

<username>user</username>
<password>passwd</password>
<dbname>d:\pathtomydb\bin\dbname.FDB</dbname>
<active>1</active>
   </connection>
   </mysetup>

Now you have another connexion.

Mine is a firebird, so you may adapt some changes to this, but here is how I handle it :

Connexion :

 $config = Mage::getConfig()->getResourceConnectionConfig('mysetup');

        $dbConfig = array(
        'host'      => $config->host,
        'username'  => $config->username,
        'password'  => $config->password,
        'dbname'    => $config->dbname
    );
        $connexion = Zend_Db::factory('Firebird', $dbConfig);

Replace Firebird with the kind of connexion you need (like Pdo_Mysql or something like that). Queries :

 $connexion->fetchAll($request);
 $connexion->update('table',array('field'=>'value'),$where);

etc.

Churrigueresque

Thanks for a great answer. I have a query hope u'll get this in time. I got few crons and reports which I want to read from another server. Thats more like slave to master. But we are using master only for all queries. If I set the connection right is it possible for me to run crons and reports without doing a lot of code change?? e.g. like using Magento ORM seamlessly ?? Thanks for your help. – Vargo

Answer 2

Can Magento natively handle read-only slaves

Magento is natively capable of splitting off reads/writes to different database servers (with the exception of a few broken releases, eg. EE 1.11) - allowing you to offset select load to an additional (or more) server(s); and forwarding all the update/write queries to a single master.

Configuring the slaves

First configure your slaves. We're big advocates of the Percona utilities and MySQL branches - they have an ideal tool for taking hot backups of your existing DB - innobackupex. There is a good write up here.

On the master

Replace $TIMESTAMP or tab complete.

mysql
> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$slaveip' IDENTIFIED BY '$slavepass';
> quit;
innobackupex --user=username --password=password /path/to/backupdir
innobackupex --user=username --password=password /
       --apply-log /path/to/backupdir/$TIMESTAMP/

rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP TheSlave:/path/to/mysql/
scp /etc/mysql/my.cnf TheSlave:/etc/mysql/my.cnf

On the slave

/etc/init.d/mysql stop
mv /path/to/mysql/datadir /path/to/mysql/datadir_bak
mv /path/to/mysql/$TIMESTAMP /path/to/mysql/datadir
chown -R mysql:mysql /path/to/mysql/datadir
sed -i 's#server-id=1#server-id=2#g' /etc/mysql/my.cnf
/etc/init.d/mysql start
cat /var/lib/mysql/xtrabackup_binlog_info
> TheMaster-bin.000001     481

mysql
> CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.000001', MASTER_LOG_POS=481;
> START SLAVE;

Then once your slave is operational, in practice, it only takes a few additional lines of code to achieve.

In ./app/etc/local.xml

<default_read>
  <connection>
    <use/>
    <host><![CDATA[host]]></host>
    <username><![CDATA[username]]></username>
    <password><![CDATA[password]]></password>
    <dbname><![CDATA[dbname]]></dbname>
    <type>pdo_mysql</type>
    <model>mysql4</model>
    <initStatements>SET NAMES utf8</initStatements>
    <active>1</active>
  </connection>
</default_read>

Sources

Krug

Answer 3

If your DNS supports, you can create a record set using weighted routing policy. Like AWS Route 53. And to avoid downtimes you can associate with health checks.

One example: https://aws.amazon.com/pt/blogs/aws/domain-name-health-checks-for-route-53/

Bobbee