Distributed Database Architecture Using MySQL Replication

A distributed database system allows applications to access data from local and remote databases. Distributed databases using MySQL replication we use a client/server architecture to process information requests.

The term replication refers to the operation of copying and maintaining database objects in multiple databases belonging to a distributed system.

The architecture is based around a series of delivery servers reporting back to a central database.

The server farm can be scaled by adding more delivery servers.

The architecture provides good redundancy, as a failure of any single server will not disable the system. The load balancer will handle a delivery server outage by distributing the extra load across the other delivery servers.

With this basic architecture you will be able to scale to serve billions of page hits/impressions each month. This architecture will both eliminate most single points of failure, and allow almost unlimited scalability.

Used in Trackers, Ad Networks, Logging Engines which are write intensive and with the Distributed Database Architecture we can balance the writes across the web servers.

Also to have full High Availability and Redundancy we can use a Master-Master replication at the Admin/Central Database.

Replication Documentation

http://dev.mysql.com/doc/refman/5.5/en/replication.html

Steps For MySQL Replication

  • Enable binary logging on the master: my.cnf

    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days= 10
    max_binlog_size = 100M
    binlog_do_db = adnetwork,adserver

  • (or just mysql-bin ) ( can use some different name for security reasons)

    server-id = 1

  • if innodb

    innodb_flush_log_at_trx_commit=1

    sync_binlog=0

  • Enable Networking (comment the below line)

    #bind-address = 127.0.0.1

  • RESTART MYSQL after my.cnf file changes

  • replicate privileges

    CREATE USER ‘replicationuser’@’10.%’ IDENTIFIED BY ‘password’;

    GRANT REPLICATION SLAVE ON *.* TO ‘replicationuser’@’10.%’;

  • Flush all the tables and block write statements on the master by executing a

    FLUSH TABLES WITH READ LOCK;

    SHOW MASTER STATUS \G;

  • Create a dump of the database and copy it to the slaves.

    mysqldump -u mysqluser -p adnetwork > adnetwork.sql;

    UNLOCK TABLES;

  • Slave setup (give different server-id for each slave 2,3,4)

    [mysqld]

    server-id = 2

  • import the data to slave using the dump from the master

    CREATE DATABASE `adserver` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin_ci;

    mysql -u mysqluser -p adserver < adserver.sql

  • Make the slave replicate

    CHANGE MASTER TO

    MASTER_HOST=’10.0.0.1′,

    MASTER_USER=’replicationuser’,

    MASTER_PASSWORD=’password’,

    MASTER_LOG_FILE=’mysql-bin.000002′,

    MASTER_LOG_POS=107;

    START SLAVE;

    SHOW SLAVE STATUS;

Salil Kothadia Chief Architect, Consumerization of IT.He has played multiple roles including technical architect, database developer, performance tester, system administrator etc. He is particularly interested in client/server web application and relational database design using open source technologies. He loves working on PHP / C++ / Python and has exposure to Java and C#.He has worked on many areas in software development.He has worked on architecting high traffic web application in multiple domains.

Leave a comment