Replication

[jaymc]

I just read this about mysql replication

 

http://www.howtoforge.com/mysql_database_replication

 

Im just wondering, does this consume a lot of resources on the MASTER, or, is the only process the SLAVE pulling the queries from the MASTER therefor soley resulting in file and network transfer

 

If thats the case, then good!

 

How can I take advantage of having a SLAVE database. I mean is it basically just a live backup, obviously if something goes bad on the master then they are both dead, but is that its main purpose or is it used for load balancing?

 

If it is used for load balancing, then any updates done MUST still all be targeted at the MASTER seens as updates to the slave will not filter to any other mysql servers and apply only to that

 

I would love to split the mysql load with an exact duplicate but on another server, Im just wondering how to get around the fact UPDATES and INSERTS must still be flooded to the master, thus still bombarding the primary MYSQL server which will not reduce load

 

Advise would be great! Cheers

[able]

The main reason master/slave replication is used is because most websites have a higher read than write ratio.

 

Removing the reads from the master and spreading over slaves cuts the load on the master considerably.

 

If the master dies, you can switch over to the slave and make it the master. As yet this isn't automatic however a little bit of wrapper code can allow it fairly easily.

 

What you can do is do two way master/slave replication. Each server is both a slave and a master. For a good discussion of the topic I recommend 'Advanced PHP' by George S of OmniIT

[jaymc]

If they where both slave and master, all writes would still be performed on each server which wouldnt reduce any of the load

 

Do you mean that replication is mainly to split the reads to different servers?

 

If so, how are you meant to determine which server to read from, do you need load balancing software, and if so, do you need that to interact with your php code to say

 

which server is busiest, if its server 1 then mysql_connect(server2), if server 2 is busiest then mysql_connect(server1)

[able]

If they where both slave and master, all writes would still be performed on each server which wouldnt reduce any of the load

 

Thats right, but I beleive you said you'd like a mirrow.

 

Do you mean that replication is mainly to split the reads to different servers?

 

Most applications of mysql replication are initially to split read loads to different servers yes. However, i'll come back to splitting for writes at the end.

 

If so, how are you meant to determine which server to read from, do you need load balancing software, and if so, do you need that to interact with your php code to say

 

In a dual master, then yes you can load balance however most seem to prefer a simple algorithm in the php code rather than load balancing, I cant remember of the top of my head the main reason(s) for this.

 

Ok splitting for writes, the only real way to do this in mysql is to partition/shard your data. One way would be to have all data for users whos names start with a-m on on master, and n-z on another. A simple function to determin which shard the data you need is in can be used, which then sets up a read and write connection as required (write to master of shard, reads to slave(s) of shard).

 

The function to establish which shard (master+slave(s)) is problematic in that when data is moved e.g. you shard on more complex grounds e.g. to balance loads based on user activity. Often a lookup table of user->shard is kept and replicated to all machines, or a dedicated master/slave(s) have the job purely of keeping track of what data is where.

 

As you can imagine, this is quite limited on 2 servers, it really comes into its own on slightly larger setups however it will work on 2.

 

[aschk]

Interestingly, I remember about reading about this a while ago.

You can load balance (in a simplistic manner) using DNS rotation.

 

The term is called round robin DNS and relies on using CNAME's instead of a hardware load balancer (too expensive ). Thus check out http://content.websitegear.com/article/load_balance_dns.htm

 

Then when you're connecting to MySQL you can use

<?php

mysql_connect("mysql.yourdomain.com","username","password");

?>

 

Your server should resolve the domain name to 1 of the 4 ips available to it.

You WILL have to set up MySQL users access from external addresses however (i.e. 'username'@<ips>).

 

It's worth saying however that this might have issues with availability, i.e. if 1 server goes down then the domain won't resolve and you'll get a timeout error.

[jaymc]

Cool, providing your servers dont die pretty simplistic way of splitting the load

[able]

Round robin dns is plagued with problems - most specifically, if a server does die, it wont be excluded.

 

There is a much more effective system called wackamole which also works on a multiple IP system, but can adjust to remove dead servers by rerouting ip's.