MySQL Databases and 2 servers?
Moderator: General Moderators
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
MySQL Databases and 2 servers?
Hello,
We are planning to reprogramm our search machine and website. Since we have call agents who query the same database as the customers do, we had the idea of splitting the database to 2 servers.
The call agents can update customer data with our intern webinterface, which not only concerns tables that are for the call agents, but also concerns tables of the search machine, holding company data such as address, search words, etc.
O.k., now my questions. Since the customer himself can administer his own data, as well as the call agent (or media agent), I was thinking of doing following:
* I have 2 servers. Server 1 is responsible for the search machine and just serves customers searching for a company or for products. The MySQL database is there just for querying.
* Server 2 serves call agents, as well customers who want to change their data, reading and writing.
* Since the search machine should of course show updated data the call agent or the customer have eventually changed, the data from server 2 must be synchronized with the MySQL databese of server 1. (e.g. at midnight)
Questions:
-------------
0. Is the above described the right way?
1. What do I have to consider when planning the database structure?
2. How do I synchronize? Can I copy specific tables from one server to another using cronjobs (crontables)? Since this is absolute newland to me, how?
Any expert knowledge or experience with MySQL replication or MySQL clusters? I think replication is just thought for mirroring, or am I mistaken?
Thanks a lot.
We are planning to reprogramm our search machine and website. Since we have call agents who query the same database as the customers do, we had the idea of splitting the database to 2 servers.
The call agents can update customer data with our intern webinterface, which not only concerns tables that are for the call agents, but also concerns tables of the search machine, holding company data such as address, search words, etc.
O.k., now my questions. Since the customer himself can administer his own data, as well as the call agent (or media agent), I was thinking of doing following:
* I have 2 servers. Server 1 is responsible for the search machine and just serves customers searching for a company or for products. The MySQL database is there just for querying.
* Server 2 serves call agents, as well customers who want to change their data, reading and writing.
* Since the search machine should of course show updated data the call agent or the customer have eventually changed, the data from server 2 must be synchronized with the MySQL databese of server 1. (e.g. at midnight)
Questions:
-------------
0. Is the above described the right way?
1. What do I have to consider when planning the database structure?
2. How do I synchronize? Can I copy specific tables from one server to another using cronjobs (crontables)? Since this is absolute newland to me, how?
Any expert knowledge or experience with MySQL replication or MySQL clusters? I think replication is just thought for mirroring, or am I mistaken?
Thanks a lot.
Re: MySQL Databases and 2 servers?
I'd say so.visionmaster wrote: Questions:
-------------
0. Is the above described the right way?
As long as you have one way only replication there's nothing special to consider. Just read the manual: http://dev.mysql.com/doc/mysql/en/Replication.htmlvisionmaster wrote: 1. What do I have to consider when planning the database structure?
Replication is a way to go.visionmaster wrote: 2. How do I synchronize? Can I copy specific tables from one server to another using cronjobs (crontables)? Since this is absolute newland to me, how?
To some extent, yes.visionmaster wrote: I think replication is just thought for mirroring, or am I mistaken?
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
Re: MySQL Databases and 2 servers?
Hello,
"Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data."
=> That means following: the master server is the server which is updated by inserts, updates and deletes (by customers and call agents). The search engine uses the slave server. Is that correct?
=> Or are selects from the slave server to slow, since the search machine is the interface which is used by the users and the "main business"?
=> What does this mean for my PHP programming? One script connects to the master, another script (search script) connects to the slave.
Thanks.
Is replication really the right solution?As long as you have one way only replication there's nothing special to consider. Just read the manual: http://dev.mysql.com/doc/mysql/en/Replication.html
"Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data."
=> That means following: the master server is the server which is updated by inserts, updates and deletes (by customers and call agents). The search engine uses the slave server. Is that correct?
=> Or are selects from the slave server to slow, since the search machine is the interface which is used by the users and the "main business"?
=> What does this mean for my PHP programming? One script connects to the master, another script (search script) connects to the slave.
Thanks.
Re: MySQL Databases and 2 servers?
Replication is nearly real-time solution. It's well-known, well-tested and wide-used. I don't know if it's "right"visionmaster wrote: Is replication really the right solution?
Yes. Moreover, you can pass your SELECTs from call agents to slave server as well.visionmaster wrote: "Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data."
=> That means following: the master server is the server which is updated by inserts, updates and deletes (by customers and call agents). The search engine uses the slave server. Is that correct?
Usually it's done using the technique like this:=> What does this mean for my PHP programming? One script connects to the master, another script (search script) connects to the slave.
- You have the db abstraction class/layer, don't you?

- Each instance of the db class maintains two connections, one to the slave and another to the master.
- query method determines the type of the query it received as its argument. It passes modifying (e.g. INSERTs, DELETEs, etc) queries to master and all other to the slave.
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
Re: MySQL Databases and 2 servers?
Hi,
Thanks for your reply.
Where can I read more about what happens in the background. Especially about passing modfying queries to the master and all other to the slave.
How can I imagine master and slave physically? Is the master on one server and the slave on another?
Thanks for help on this specific topic!
Thanks for your reply.
From your explanation below, this is done automatically, did I get that right?Yes. Moreover, you can pass your SELECTs from call agents to slave server as well.
O.k., so all this happens in the background. As a progammer I just do my usual db-connect and db-selects, inserts, etc., the rest is done automatically.[*]Each instance of the db class maintains two connections, one to the slave and another to the master.
[*]query method determines the type of the query it received as its argument. It passes modifying (e.g. INSERTs, DELETEs, etc) queries to master and all other to the slave.
[/list]
Where can I read more about what happens in the background. Especially about passing modfying queries to the master and all other to the slave.
What exactly is meant by "add several slaves and implement load balancing"?Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
How can I imagine master and slave physically? Is the master on one server and the slave on another?
Thanks for help on this specific topic!
Re: MySQL Databases and 2 servers?
Exactly.visionmaster wrote: From your explanation below, this is done automatically, did I get that right?
here's simple snippetWhere can I read more about what happens in the background. Especially about passing modfying queries to the master and all other to the slave.
Code: Select all
class dbmysql extends db {
var $_slave_connection;
var $_master_connection;
function dbmysql($config) {
$this->_slave_connection = $this->_connect($config['slave']);
$this->_master_connection = $this->_connect($config['master']);
}
function _connect($config) {
return mysql_connect(
$config['server'] . ':' . $config['port'],
$config['user'],
$config['password']
);
}
function _is_modifying($query) {
return preg_match('/^\s*(insert|delete|update)\s+/i', $query);
}
function query($query) {
return mysql_query(
$query,
$this->_is_modifying($query) ?
$this->_master_connection :
$this->_slave_connection
);
}
}It's how replication often used. Say we have some application which issues the modifying/non-modifying queries in 20/80 proportion. To achieve the maximum performance we would set one mysql host/server/box/bladeWhat exactly is meant by "add several slaves and implement load balancing"?Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
How can I imagine master and slave physically? Is the master on one server and the slave on another?
More info is available here: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html
Look for 'How can I use replication to improve performance of my system?' Q.
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
Re: MySQL Databases and 2 servers?
Hello,
Thanks again for your detailed response. Really appreciate it!
Thanks!
Thanks again for your detailed response. Really appreciate it!
Thanks for the explanation, understood that one. We are indeed planning to buy a set of blades.here's simple snippetIt's oversimplified and includes only methods and member vars necessary to perform queries.Code: Select all
class dbmysql extends db { var $_slave_connection; var $_master_connection; function dbmysql($config) { $this->_slave_connection = $this->_connect($config['slave']); $this->_master_connection = $this->_connect($config['master']); } function _connect($config) { return mysql_connect( $config['server'] . ':' . $config['port'], $config['user'], $config['password'] ); } function _is_modifying($query) { return preg_match('/^\s*(insert|delete|update)\s+/i', $query); } function query($query) { return mysql_query( $query, $this->_is_modifying($query) ? $this->_master_connection : $this->_slave_connection ); } }
Hmm, I think I still don't understand the basics. Do I have to implement the master/slave thing as a PHP progammer, or is that done automatically? O.k., I have a php script that queries the database, I mean does everything stay the same? I just write my "SELECT" and that is it or do I really have to include some kind of class, like your example. I really don't understand that.![]()
Here the same question, do I have to implement that as a PHP progammer, or is that done automatically? (Sorry if this question is "too simple"...)Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
It's how replication often used. Say we have some application which issues the modifying/non-modifying queries in 20/80 proportion. To achieve the maximum performance we would set one mysql host/server/box/bladeto be the master and four others to be slaves connected to the master. This way each box serves only 20% of queries, busting the performance of entire 'farm' up to 5 times comparing to single db server.
Thanks!
Re: MySQL Databases and 2 servers?
My bad, I didn't provide any sample code using that class! That was the source of confusion, IMO. As a PHP programmer you don't need to implement replication on itself, great folks from MySQL AB have done that for youvisionmaster wrote: Hmm, I think I still don't understand the basics. Do I have to implement the master/slave thing as a PHP progammer, or is that done automatically? O.k., I have a php script that queries the database, I mean does everything stay the same? I just write my "SELECT" and that is it or do I really have to include some kind of class, like your example. I really don't understand that.![]()
- Why do I need that class?. Because it provides db abstraction level.
- Why would I need that... how did you call it? prescription? description?... that thing, at last?
. Abstraction. I did call it abstraction. Because it's GOOD THING, imho. There are a lot of discussion on these forums on OOP, OOD and sometimes on OOA topics, you can search and read if you got free time. - Ok, couldn't I code that layer as a set of functions? You could. But that would require related vars ($slave_connection, $master_connection, etc) to be stored as globals, which is BAD THING (imo, of course
). - Well, why wouldn't you just code something like this:
You could. But I wouldn't suggest to code it like that.
Code: Select all
$slave = mysql_connect('slave.host:3306', 'user', 'password'); $master = mysql_connect('master.host:3306', 'anotheruser', 'anotherpassword'); $result1 = mysql_query("SELECT something FROM somewhere....", $slave); while($row = mysql_fetch_assoc($result1)) { if( met_something_important($row) ) if(!mysql_query("UPDATE important_counters SET met = met+1 WHERE thing = {$row['something']}", $master)) die("ALARM! ERROR! Update of important counter failed!"); } mysql_free_result($result1); echo "IMPORTANT COUNTERS AFTER PROCESSING:<br><pre>"; $result2 = mysql_query("SELECT * FROM important_counters", $slave); while($row = mysql_fetch_assoc($result2)) var_dump($row); echo "</pre>"; mysql_free_result($result2);
Well, the first reason would be "I don't like it". Secondly, what if some day you want to add two more slaves? You would need to add, say $slave1, $slave2 global vars, go through all of your code and change some of your $slave to either $slave1 or $slave2. What an awful way to spend weekend, isn't it? - Hey, you promised to show me some sample code using your class. Where is it? I did. Here is it:
Code: Select all
require_once 'That.mysql.class.php'; $mysql =& new dbmysql( array( 'master' => array( 'server' = > 'master.host', 'port' => 3306 'user' => 'user', 'password' => 'password' ), 'slave' => array( 'server' = > 'slave.host', 'port' => 3306 'user' => 'anotheruser', 'password' => 'anotherpassword' ) ) ); $result1 = $mysql->query("SELECT something FROM somewhere...."); while($row = mysql_fetch_assoc($result1)) { if( met_something_important($row) ) if(!$mysql->query("UPDATE important_counters SET met = met+1 WHERE thing = {$row['something']}")) die("ALARM! ERROR! Update of important counter failed!"); } mysql_free_result($result1); echo "IMPORTANT COUNTERS AFTER PROCESSING:<br><pre>"; $result2 = $mysql->query("SELECT * FROM important_counters"); while($row = mysql_fetch_assoc($result2)) var_dump($row); echo "</pre>"; mysql_free_result($result2); - Well, how that differ from what I coded? Significantly!
Look once more: in your code you had to specify the link to use on each query. For large script that would mean hudreds of queries. My sample class abstracts you from the fact that there are several servers involved almost totally (you need to specify connection params only once, then you just call $mysql->query() and don't care to choose where the query should be sent).
From php perspective you have to implement piece(s) of code which check if given query would modify data or wouldn't. Modifying queries are sent to master. Non-modifying queries are sent to some slave (concrete slave might be choosen sequentially from the pool of available slaves, or their current load might be metered and distributed equally, or... you say meHere the same question, do I have to implement that as a PHP progammer, or is that done automatically? (Sorry if this question is "too simple"...)Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
Last edited by Weirdan on Thu Jul 14, 2005 9:46 am, edited 2 times in total.
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
Re: MySQL Databases and 2 servers?
Hello,
Thanks again for your very detailed explanation!
Thanks for your time!
Thanks again for your very detailed explanation!
O.k., I understood that, good example.Code: Select all
require_once 'That.mysql.class.php'; $mysql =& new dbmysql( array( 'master' => array( 'server' = > 'master.host', 'port' => 3306 'user' => 'user', 'password' => 'password' ), 'slave' => array( 'server' = > 'slave.host', 'port' => 3306 'user' => 'anotheruser', 'password' => 'anotherpassword' ) ) ); $result1 = $mysql->query("SELECT something FROM somewhere...."); while($row = mysql_fetch_assoc($result1)) { if( met_something_important($row) ) if(!$mysql->query("UPDATE important_counters SET met = met+1 WHERE thing = {$row['something']}")) die("ALARM! ERROR! Update of important counter failed!"); } mysql_free_result($result1); echo "IMPORTANT COUNTERS AFTER PROCESSING:<br><pre>"; $result2 = $mysql->query("SELECT * FROM important_counters"); while($row = mysql_fetch_assoc($result2)) var_dump($row); echo "</pre>"; mysql_free_result($result2);
Hmm, I'm still a bit "mixed up". Could you just provide me one example code with the class and code using this class in one code snippet? Your class in a previous thread already takes care of modifying connections which are sent to the master. But what if I have more than one slave and want to disitribute the selects to e.g. 3 slaves? This also has to be implemented by the class, correct?From php perspective you have to implement piece(s) of code which check if given query would modify data or wouldn't. Modifying queries are sent to master. Non-modifying queries are sent to some slave (concrete slave might be choosen sequentially from the pool of available slaves, or their current load might be metered and distributed equally, or... you say me)
Thanks for your time!
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
Ok, here we go:
file: db.ini
it's basically the same script and the same class except the following:
Adding new slaves to pool is now made easy. Just add corresponding section to db.ini file. On next invocation your script will use that additional slave.
Code: Select all
<?php
class dbmysql /*extends db*/ {
var $_slave_connections = array();
var $_total_slaves;
var $_current_slave;
var $_master_connection;
function dbmysql($config_file) {
$config = $this->_get_config($config_file);
$this->_setup_slaves($configї'slaves']);
$this->_master_connection = $this->_connect($configї'master']);
}
function _setup_slaves($slaves) {
if( !count($slaves) ) // reuse master connection if there are no slaves
$this->_slave_connectionsї] = $this->_master_connection;
foreach($slaves as $host)
$this->_slave_connectionsї] = $this->_connect($host);
$this->_total_slaves = count($this->_slave_connections);
$this->_current_slave = rand(0, $this->_total_slaves - 1);
}
function _connect($config) {
//return rand();
return mysql_connect(
$configї'server'] . ':' . $configї'port'],
$configї'user'],
$configї'password']
);
}
function _get_config($file) {
$conf = parse_ini_file($file, true);
$ret = array('master'=>$confї'master'], 'slaves'=>array());
foreach($conf as $name => $section)
if( preg_match('/^slave_\d+$/', $name) )
$retї'slaves']ї] = $section;
return $ret;
}
function _is_modifying($query) {
return preg_match('/^\s*(insert|delete|update)\s+/i', $query);
}
function _select_slave() {
if($this->_current_slave > ($this->_total_slaves - 1) )
$this->_current_slave = 0;
return $this->_slave_connectionsї$this->_current_slave++];
}
function query($query) {
return mysql_query(
$query,
$this->_is_modifying($query) ?
$this->_master_connection :
$this->_select_slave()
);
}
}
//var_dump(dbmysql::_get_config('db.ini'));
/*
$mysql = new dbmysql('db.ini');
var_dump($mysql);
for($i=0;$i<30; $i++)
var_dump($mysql->_select_slave());
*/
?>Code: Select all
їmaster]
host = "master.example.com"
port = 3306
user = "master_user"
password = "master_password"
їslave_1]
host = "slave1.example.com"
port = 3306
user = "slave1_user"
password = "slave1_password"
їslave_2]
host = "slave2.example.com"
port = 3306
user = "slave2_user"
password = "slave2_password"
їslave_3]
host = "slave3.example.com"
port = 3306
user = "slave3_user"
password = "slave3_password"Code: Select all
<?php
require_once 'dbmysql.class.php';
$mysql =& new dbmysql('db.ini');
$result1 = $mysql->query("SELECT something FROM somewhere....");
while($row = mysql_fetch_assoc($result1)) {
if( met_something_important($row) )
if(!$mysql->query("UPDATE important_counters SET met = met+1 WHERE thing = {$rowї'something']}"))
die("ALARM! ERROR! Update of important counter failed!");
}
mysql_free_result($result1);
echo "IMPORTANT COUNTERS AFTER PROCESSING:<br><pre>";
$result2 = $mysql->query("SELECT * FROM important_counters");
while($row = mysql_fetch_assoc($result2))
var_dump($row);
echo "</pre>";
mysql_free_result($result2);
?>- class now reads its configuration from ini file
- class now supports 'no-slaves' configuration
- script is modified to reflect these changes
Adding new slaves to pool is now made easy. Just add corresponding section to db.ini file. On next invocation your script will use that additional slave.
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am