为什么需要读写分离？PHP实现的Mysql读写分离主要特性：
主要是数据库压力过大的情况下的解决方案。写操作会把表锁定，而导致很多查询相继锁定而等候，导致程序处于等候状态。
读写分离是建立在mysql的replication基础上的，master服务器仅负责写操作，slave机器仅提读操作，相对而言提高了读写性能，也可以增加mysql的承受能力。
- 简单的读写分离
- 一个主数据库，可以添加更多的只读数据库
- 读写分离但不用担心某些特性不支持
- 缺点：同时连接两个数据库
- 可能的bug：请反馈
为了方便，试试建立了google的一个项目：
http://code.google.com/p/mysql-rw-php/
PHP代码：
mysql_rw_php.class.php
代码
1 <?php
2 /****************************************
3 *** mysql-rw-php version 0.1 @ 2009-4-16
4 *** code by hqlulu#gmail.com
5 *** http://www.aslibra.com
6 *** http://code.google.com/p/mysql-rw-php/
7 *** code modify from class_mysql.php (uchome)
8 ****************************************/
9 class mysql_rw_php {
10 //查询个数
11 var $querynum = 0;
12 //当前操作的数据库连接
13 var $link = null;
14 //字符集
15 var $charset;
16 //当前数据库
17 var $cur_db = '';
18 //是否存在有效的只读数据库连接
19 var $ro_exist = false;
20 //只读数据库连接
21 var $link_ro = null;
22 //读写数据库连接
23 var $link_rw = null;
24 function mysql_rw_php(){
25 }
26 function connect($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0, $halt = TRUE) {
27 if($pconnect) {
28 if(!$this->link = @mysql_pconnect($dbhost, $dbuser, $dbpw)) {
29 $halt && $this->halt('Can not connect to MySQL server');
30 }
31 } else {
32 if(!$this->link = @mysql_connect($dbhost, $dbuser, $dbpw)) {
33 $halt && $this->halt('Can not connect to MySQL server');
34 }
35 }
36
37 //只读连接失败
38 if(!$this->link && !$halt) return false;
39
40 //未初始化rw时，第一个连接作为rw
41 if($this->link_rw == null)
42 $this->link_rw = $this->link;
43 if($this->version() > '4.1') {
44 if($this->charset) {
45 @mysql_query("SET character_set_connection=$this->charset, character_set_results=$this->charset, character_set_client=binary", $this->link);
46 }
47 if($this->version() > '5.0.1') {
48 @mysql_query("SET sql_mode=''", $this->link);
49 }
50 }
51 if($dbname) {
52 $this->select_db($dbname);
53 }
54 }
55 //连接一个只读的mysql数据库
56 function connect_ro($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0){
57 if($this->link_rw == null)
58 $this->link_rw = $this->link;
59 $this->link = null;
60 //不产生halt错误
61 $this->connect($dbhost, $dbuser, $dbpw, $dbname, $pconnect, false);
62 if($this->link){
63 //连接成功
64 //echo "link ro sussess!<br>";
65 $this->ro_exist = true;
66 $this->link_ro = $this->link;
67 if($this->cur_db){
68 //如果已经选择过数据库则需要操作一次
69 @mysql_select_db($this->cur_db, $this->link_ro);
70 }
71 }else{
72 //连接失败
73 //echo "link ro failed!<br>";
74 $this->link = &$this->link_rw;
75 }
76 }
77 //设置一系列只读数据库并且连接其中一个
78 function set_ro_list($ro_list){
79 if(is_array($ro_list)){
80 //随机选择其中一个
81 $link_ro = $ro_list[array_rand($ro_list)];
82 $this->connect_ro($link_ro['dbhost'], $link_ro['dbuser'], $link_ro['dbpw']);
83 }
84 }
85 function select_db($dbname) {
86 //同时操作两个数据库连接
87 $this->cur_db = $dbname;
88 if($this->ro_exist){
89 @mysql_select_db($dbname, $this->link_ro);
90 }
91 return @mysql_select_db($dbname, $this->link_rw);
92 }
93 function fetch_array($query, $result_type = MYSQL_ASSOC) {
94 return mysql_fetch_array($query, $result_type);
95 }
96 function fetch_one_array($sql, $type = '') {
97 $qr = $this->query($sql, $type);
98 return $this->fetch_array($qr);
99 }
100 function query($sql, $type = '') {
101 $this->link = &$this->link_rw;
102 //判断是否select语句
103 if($this->ro_exist && preg_match ("/^(\s*)select/i", $sql)){
104 $this->link = &$this->link_ro;
105 }
106 $func = $type == 'UNBUFFERED' && @function_exists('mysql_unbuffered_query') ?
107 'mysql_unbuffered_query' : 'mysql_query';
108 if(!($query = $func($sql, $this->link)) && $type != 'SILENT') {
109 $this->halt('MySQL Query Error', $sql);
110 }
111 $this->querynum++;
112 return $query;
113 }
114 function affected_rows() {
115 return mysql_affected_rows($this->link);
116 }
117 function error() {
118 return (($this->link) ? mysql_error($this->link) : mysql_error());
119 }
120 function errno() {
121 return intval(($this->link) ? mysql_errno($this->link) : mysql_errno());
122 }
123 function result($query, $row) {
124 $query = @mysql_result($query, $row);
125 return $query;
126 }
127 function num_rows($query) {
128 $query = mysql_num_rows($query);
129 return $query;
130 }
131 function num_fields($query) {
132 return mysql_num_fields($query);
133 }
134 function free_result($query) {
135 return mysql_free_result($query);
136 }
137 function insert_id() {
138 return ($id = mysql_insert_id($this->link)) >= 0 ? $id : $this->result($this->query("SELECT last_insert_id()"), 0);
139 }
140 function fetch_row($query) {
141 $query = mysql_fetch_row($query);
142 return $query;
143 }
144 function fetch_fields($query) {
145 return mysql_fetch_field($query);
146 }
147 function version() {
148 return mysql_get_server_info($this->link);
149 }
150 function close() {
151 return mysql_close($this->link);
152 }
153 function halt($message = '', $sql = '') {
154 $dberror = $this->error();
155 $dberrno = $this->errno();
156 echo "<div style=\"position:absolute;font-size:11px;font-family:verdana,arial;background:#EBEBEB;padding:0.5em;\">
157 <b>MySQL Error</b><br>
158 <b>Message</b>: $message<br>
159 <b>SQL</b>: $sql<br>
160 <b>Error</b>: $dberror<br>
161 <b>Errno.</b>: $dberrno<br>
162 </div>";
163 exit();
164 }
165 }
166 ?>
example.php
<?php
/****************************************
*** mysql-rw-php version 0.1 @ 2009-4-16
*** code by hqlulu#gmail.com
*** http://www.aslibra.com
*** http://code.google.com/p/mysql-rw-php/
*** code modify from class_mysql.php (uchome)
****************************************/
require_once('mysql_rw_php.class.php');
//rw info
$db_rw = array(
'dbhost'=>'www.aslibra.com',
'dbuser'=>'aslibra',
'dbpw'=>'www.aslibra.com',
'dbname'=>'test'
);
$db_ro = array(
array(
'dbhost'=>'www.aslibra.com:4306',
'dbuser'=>'aslibra',
'dbpw'=>'www.aslibra.com'
)
);
$DB = new mysql_rw_php;
//connect Master
$DB->connect($db_rw[dbhost], $db_rw[dbuser], $db_rw[dbpw], $db_rw[dbname]);
//Method 1: connect one server
$DB->connect_ro($db_ro[0][dbhost], $db_ro[0][dbuser], $db_ro[0][dbpw]);
//Method 2: connect one server from a list by rand
$DB->set_ro_list($db_ro);
//send to rw
$sql = "insert into a set a='test'";
$DB->query($sql);
//send to ro
$sql = "select * from a";
$qr = $DB->query($sql);
while($row = $DB->fetch_array($qr)){
echo $row[a];
}
?>
