CakePHP+Mysql热备来实现读写分离

实现cakePHP读写分离，首先配置数据库连接
app/Config/database.php
class DATABASE_CONFIG {
    //可读可写权限
    public $default = array (
        'datasource' => 'Database/Mysql',
        'persistent' => false, // 是否使用持久化
        'host' => 'localhost',
        'login' => 'root',
        'password' => '1234',
        'database' => 'shop',
        // 'prefix' => 'app_',
        'encoding' => 'utf8'
    );
//只读权限
    public $readonly = array (
        'datasource' => 'Database/Mysql',
        'persistent' => false, // 是否使用持久化
        'host' => 'localhost',
        'login' => 'root2',
        'password' => '123456',
        'database' => 'shop',
        // 'prefix' => 'app_',
        'encoding' => 'utf8'
    );
    }
然后修改model的父文件 appModelAppModel.php，
 
/**
 * Application model for Cake.
 *
 * This file is application-wide model file. You can put all
 * application-wide model-related methods here.
 *
 * PHP 5
 *
 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
 * Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
 *
 * Licensed under The MIT License
 * Redistributions of files must retain the above copyright notice.
 *
 * @copyright     Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
 * @link          http://cakephp.org CakePHP(tm) Project
 * @package       app.Model
 * @since         CakePHP(tm) v 0.2.9
 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
 */
App::uses('Model', 'Model');
/**
 * Application model for Cake.
 *
 * Add your application-wide methods in the class below, your models
 * will inherit them.
 *
 * @package       app.Model
 */
class AppModel extends Model {
    //合并为一个sql 插入数据库
//（如果是字符串类型，必须提前用单引号把内容引起来，否则当成了int类型处理）
    public function createMany($data){
        $ca_sql="INSERT INTO ".$this->useTable;
        $ca_sql_arr=array();
        $keys=array();
        foreach($data as $k=>$v){
            if(empty($keys)){
                $a_keys = array_keys($v);
                foreach($a_keys as $kv){
                    $keys[]="`".$kv."`";
                }
            }
            $ca_sql_arr[]="(".implode(",", $v).")";
        }
        if(!empty($ca_sql_arr)){
            $sql=$ca_sql." (".implode(",", $keys).")  VALUES".implode(",", $ca_sql_arr);
            $this->query($sql);
        }
    }

    /**
     * 直接执行sql语句，如果是已select开头，则默认使用只读实例
     * */
    public function query($sql) {
        $sindex = stripos($sql,"select");
        if($sindex ===0){
            $this->beforeFind(null);
        }
        $params = func_get_args();
        $db = $this->getDataSource();
        $results= call_user_func_array(array(&$db, 'query'), $params);
        return $this->afterFind($results);
    }
/**查询数据之前，切换为只读数据库**/
    public function beforeFind($queryData) {
        $this->useDbConfig="readonly";
        return true;
    }
/**查询数据之后，还原为可读可写，防止之后的查询权限不够**/
    public function afterFind($results, $primary = false) {
        $this->useDbConfig="default";
        return $results;
    }
/**强制设置为可读可写权限，防止权限不够**/
    public function beforeSave($options = array()) {
        $this->useDbConfig="default";
        return true;
    }
/**强制设置为可读可写权限，防止权限不够**/
    public function beforeDelete($cascade = true) {
        $this->useDbConfig="default";
        return true;
    }
}
 上面的操作仅限单表操作，如果模型中，包含 belongsTo hasOne hasMany hasAndBelongsToMany，在查询的时候，无法连接(join)查询
因为在libCakeModelDatasourceDboSource.php 中的read 方法中判断了两个的数据库是否一致，
foreach ($_associations as $type) {
            foreach ($model->{$type} as $assoc => $assocData) {
                $linkModel = $model->{$assoc};
                $external = isset($assocData['external']);
                $linkModel->getDataSource();
                if ($model->useDbConfig === $linkModel->useDbConfig) {//这里判断，主model和关联model的数据库一致，才关联查询
                    if ($bypass) {
                        $assocData['fields'] = false;
                    }
                    if (true === $this->generateAssociationQuery($model, $linkModel, $type, $assoc, $assocData, $queryData, $external, $null)) {
                        $linkedModels[$type . '/' . $assoc] = true;
                    }
                }
            }
        }
因此，解决数据库不一致才是关键
由于有这种关联关系的model，他们必定是同一个数据库中的数据，因此，可以关联设置model的数据源
foreach ($_associations as $type) {
            foreach ($model->{$type} as $assoc => $assocData) {
                $linkModel = $model->{$assoc};
                $external = isset($assocData['external']);
                //@2015-12-10 修改如果关联模型的数据库不一致，则默认设置为主数据库
                if($model->useDbConfig !== $linkModel->useDbConfig){
                    $linkModel->setDataSource($model->useDbConfig);//强制设置关联model的数据源跟主model一致
                }
                $linkModel->getDataSource();
                if ($model->useDbConfig === $linkModel->useDbConfig) {
                    if ($bypass) {
                        $assocData['fields'] = false;
                    }
                    if (true === $this->generateAssociationQuery($model, $linkModel, $type, $assoc, $assocData, $queryData, $external, $null)) {
                        $linkedModels[$type . '/' . $assoc] = true;
                    }
                }
            }
        }
修改lib/Cake/Model/Model.php 中save方法
由于在save之前，系统会主动查询一次数据库，所以把useDbConfig切换成了readonly
public function save($data = null, $validate = true, $fieldList = array()) {
       
        $defaults = array('validate' => true, 'fieldList' => array(), 'callbacks' => true);
        $_whitelist = $this->whitelist;
        $fields = array();
        if (!is_array($validate)) {
            $options = array_merge($defaults, compact('validate', 'fieldList', 'callbacks'));
        } else {
            $options = array_merge($defaults, $validate);
        }
        if (!empty($options['fieldList'])) {
            if (!empty($options['fieldList'][$this->alias]) && is_array($options['fieldList'][$this->alias])) {
                $this->whitelist = $options['fieldList'][$this->alias];
            } else {
                $this->whitelist = $options['fieldList'];
            }
        } elseif ($options['fieldList'] === null) {
            $this->whitelist = array();
        }
        $this->set($data);
        if (empty($this->data) && !$this->hasField(array('created', 'updated', 'modified'))) {
            return false;
        }
        foreach (array('created', 'updated', 'modified') as $field) {
            $keyPresentAndEmpty = (
                isset($this->data[$this->alias]) &&
                array_key_exists($field, $this->data[$this->alias]) &&
                $this->data[$this->alias][$field] === null
            );
            if ($keyPresentAndEmpty) {
                unset($this->data[$this->alias][$field]);
            }
        }
        $exists = $this->exists();//执行了一次查询操作，数据库已经切换为readonly，
        $dateFields = array('modified', 'updated');
        if (!$exists) {
            $dateFields[] = 'created';
        }
        if (isset($this->data[$this->alias])) {
            $fields = array_keys($this->data[$this->alias]);
        }
        if ($options['validate'] && !$this->validates($options)) {
            $this->whitelist = $_whitelist;
            return false;
        }
        $this->beforeSave(null);//强制切换为默认数据库（可读可写）
        $db = $this->getDataSource();
        foreach ($dateFields as $updateCol) {
            if ($this->hasField($updateCol) && !in_array($updateCol, $fields)) {
                $default = array('formatter' => 'date');
                $colType = array_merge($default, $db->columns[$this->getColumnType($updateCol)]);
                if (!array_key_exists('format', $colType)) {
                    $time = strtotime('now');
                } else {
                    $time = $colType['formatter']($colType['format']);
                }
                if (!empty($this->whitelist)) {
                    $this->whitelist[] = $updateCol;
                }
                $this->set($updateCol, $time);
            }
        }
        if ($options['callbacks'] === true || $options['callbacks'] === 'before') {
            $event = new CakeEvent('Model.beforeSave', $this, array($options));
            list($event->break, $event->breakOn) = array(true, array(false, null));
            $this->getEventManager()->dispatch($event);
            if (!$event->result) {
                $this->whitelist = $_whitelist;
                return false;
            }
        }
        if (empty($this->data[$this->alias][$this->primaryKey])) {
            unset($this->data[$this->alias][$this->primaryKey]);
        }
        $fields = $values = array();
        foreach ($this->data as $n => $v) {
            if (isset($this->hasAndBelongsToMany[$n])) {
                if (isset($v[$n])) {
                    $v = $v[$n];
                }
                $joined[$n] = $v;
            } else {
                if ($n === $this->alias) {
                    foreach (array('created', 'updated', 'modified') as $field) {
                        if (array_key_exists($field, $v) && empty($v[$field])) {
                            unset($v[$field]);
                        }
                    }
                    foreach ($v as $x => $y) {
                        if ($this->hasField($x) && (empty($this->whitelist) || in_array($x, $this->whitelist))) {
                            list($fields[], $values[]) = array($x, $y);
                        }
                    }
                }
            }
        }
        $count = count($fields);
        if (!$exists && $count > 0) {
            $this->id = false;
        }
        $success = true;
        $created = false;
        if ($count > 0) {
            $cache = $this->_prepareUpdateFields(array_combine($fields, $values));
            if (!empty($this->id)) {
                $success = (bool)$db->update($this, $fields, $values);
            } else {
                $fInfo = $this->schema($this->primaryKey);
                $isUUID = ($fInfo['length'] == 36 &&
                    ($fInfo['type'] === 'string' || $fInfo['type'] === 'binary')
                );
                if (empty($this->data[$this->alias][$this->primaryKey]) && $isUUID) {
                    if (array_key_exists($this->primaryKey, $this->data[$this->alias])) {
                        $j = array_search($this->primaryKey, $fields);
                        $values[$j] = String::uuid();
                    } else {
                        list($fields[], $values[]) = array($this->primaryKey, String::uuid());
                    }
                }
                if (!$db->create($this, $fields, $values)) {
                    $success = $created = false;
                } else {
                    $created = true;
                }
            }
            if ($success && !empty($this->belongsTo)) {
                $this->updateCounterCache($cache, $created);
            }
        }
        if (!empty($joined) && $success === true) {
            $this->_saveMulti($joined, $this->id, $db);
        }
        if ($success && $count > 0) {
            if (!empty($this->data)) {
                $success = $this->data;
                if ($created) {
                    $this->data[$this->alias][$this->primaryKey] = $this->id;
                }
            }
            if ($options['callbacks'] === true || $options['callbacks'] === 'after') {
                $event = new CakeEvent('Model.afterSave', $this, array($created, $options));
                $this->getEventManager()->dispatch($event);
            }
            if (!empty($this->data)) {
                $success = Set::merge($success, $this->data);
            }
            $this->data = false;
            $this->_clearCache();
            $this->validationErrors = array();
        }
        $this->whitelist = $_whitelist;
        return $success;
    }
 
 同理，预防在savemany之前查询了数据库，现在强制切换数据库连接
public function saveMany($data = null, $options = array()) {
        if (empty($data)) {
            $data = $this->data;
        }
        $options = array_merge(array('validate' => 'first', 'atomic' => true, 'deep' => false), $options);
        $this->validationErrors = $validationErrors = array();
        if (empty($data) && $options['validate'] !== false) {
            $result = $this->save($data, $options);
            return !empty($result);
        }
        if ($options['validate'] === 'first') {
            $validates = $this->validateMany($data, $options);
            if ((!$validates && $options['atomic']) || (!$options['atomic'] && in_array(false, $validates, true))) {
                return $validates;
            }
            $options['validate'] = true;
        }
        if ($options['atomic']) {
            $this->beforeSave(null);//强制设置数据库连接
            $db = $this->getDataSource();
            $transactionBegun = $db->begin();
        }
        $return = array();
        foreach ($data as $key => $record) {
            $validates = $this->create(null) !== null;
            $saved = false;
            if ($validates) {
                if ($options['deep']) {
                    $saved = $this->saveAssociated($record, array_merge($options, array('atomic' => false)));
                } else {
                    $saved = $this->save($record, $options);
                }
            }
            $validates = ($validates && ($saved === true || (is_array($saved) && !in_array(false, $saved, true))));
            if (!$validates) {
                $validationErrors[$key] = $this->validationErrors;
            }
            if (!$options['atomic']) {
                $return[$key] = $validates;
            } elseif (!$validates) {
                break;
            }
        }
        $this->validationErrors = $validationErrors;
        if (!$options['atomic']) {
            return $return;
        }
        if ($validates) {
            if ($transactionBegun) {
                return $db->commit() !== false;
            } else {
                return true;
            }
        }
        $db->rollback();
        return false;
    }