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.
Steps For MySQL Replication
Enable binary logging on the master: my.cnf
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog_do_db = adnetwork,adserver
(or just mysql-bin ) ( can use some different name for security reasons)
server-id = 1
Enable Networking (comment the below line)
#bind-address = 127.0.0.1
RESTART MYSQL after my.cnf file changes
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;
Slave setup (give different server-id for each slave 2,3,4)
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
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.