首页 > 数据库 > mysql > 正文

使用主从读写分离 + memcached + eaccelerator + 页面缓存技术加速
2013-08-07 15:31:32 点击:

windows环境下:安装appserv 工具包。配置apache的虚拟主机:Include conf/extra/httpd-vhosts.conf编辑httpd-vhosts.conf:NameVirtualH...
windows环境下:
安装appserv 工具包。

配置apache的虚拟主机:
Include conf/extra/httpd-vhosts.conf

编辑httpd-vhosts.conf:
NameVirtualHost *:80

<VirtualHost *:80>
    DocumentRoot "D:/AppServ/www/"
    ServerName youhap
    ErrorLog "logs/youhap-error.log"
    CustomLog "logs/youhap-access.log" common
</VirtualHost>

<VirtualHost *:80>
    DocumentRoot "D:/AppServ/www/mysite/"
    ServerName center
    ErrorLog "logs/center-error.log"
    CustomLog "logs/center-access.log" common
</VirtualHost>

编辑hosts:
127.0.0.1    localhost
127.0.0.1    center
127.0.0.1    youhap

分别保存。


php编码,我使用的是codeigniter这个框架。
下载地址:http://219.239.26.9/download/832062/866311/1/zip/151/72/1252812646551_840/CodeIgniter_1.7.2.zip


mysql主从分离:

mysql主从配置及优化:
-------------Mysql Replication Setup------------
# The MySQL server
[mysqld]
port            = 3306
server-id = 1
#log-bin
master-host = 10.99.1.1
master-user = slave
master-password = slave
master-port = 3306
slave-skip-errors = 1050,1007,1051,1062
read-only
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
 
在master执行:
mysql>GRANT FILE ON *.* TO slave@'%' IDENTIFIED BY 'slave';
mysql>GRANT REPLICATION SLAVE ON *.*  TO slave@'%' IDENTIFIED BY 'slave';
mysql>flush privileges;
 
修改slave的my.cnf:
master-host     =  10.99.1.1
master-user     =  slave
master-password =  slave
master-port     =  3306
server-id       =  2
slave-skip-errors = 1050,1007,1051,1062
read-only

启动mysql以后,
在从上执行:slave start;
主从复制成功以后,继续。

一个主master,多个从slave
修改、插入连接master服务器,读取数据从slave上读取。
以下代码已经测试通过。
数据库连接设置:
<?php
$active_group = "master";
$active_record = TRUE;

$db['master']['hostname'] = "192.168.1.1";
$db['master']['username'] = "root";
$db['master']['password'] = "******************";
$db['master']['database'] = "dbname";
$db['master']['dbdriver'] = "mysql";
$db['master']['dbprefix'] = "";
$db['master']['pconnect'] = TRUE;
$db['master']['db_debug'] = TRUE;
$db['master']['cache_on'] = FALSE;
$db['master']['cachedir'] = "";
$db['master']['char_set'] = "utf8";
$db['master']['dbcollat'] = "utf8_general_ci";

$db['slave']['hostname'] = "192.168.1.2";
$db['slave']['username'] = "root";
$db['slave']['password'] = "********************";
$db['slave']['database'] = "dbname";
$db['slave']['dbdriver'] = "mysql";
$db['slave']['dbprefix'] = "";
$db['slave']['pconnect'] = TRUE;
$db['slave']['db_debug'] = TRUE;
$db['slave']['cache_on'] = FALSE;
$db['slave']['cachedir'] = "";
$db['slave']['char_set'] = "utf8";
$db['slave']['dbcollat'] = "utf8_general_ci";
?>

这个地方,主从已经分离。但是,如果只有主和从两台服务器的话,如果写数据库的操作所占比例较小的情况下,也可以让主服务器承担一部分查询操作。修改以上的配置:
$rand = mt_rand(1, 10);
/* 只有主从
 * 读取数据,主:30%,从70%
 * */
if( $rand < 4){
    $db['slave']['hostname'] = 'master_ip';
}else{
    $db['slave']['hostname'] = 'slave_ip';
}

/* 多个从,随机选择其中之一读取数据
 * $slaveGroup = array('slave1_ip' => 1, 'slave2_ip' => 2, 'slave3_ip' => 3);
 * $db['slave']['hostname'] = array_rand($slaveGroup, 1);
 * */

控制器:
<?php
 class Replication extends Controller{
     private $master;
     private $slave;

     function Replication(){
         parent::Controller();
         $this->master = $this->load->database('master', true, true);
        $this->slave = $this->load->database('slave', true, true);
     }
     function index(){
        //读写分离技术
        $this->master->set('awardName', '2008 olimpic games media');
        $this->master->set('awardSubTypeId', 1);
        $this->master->set('awardTypeId', 1);
        $this->master->insert('awards');

        $this->slave->from('t_login');
        $query2 = $this->slave->get();
     }

     function memcache(){
         $this->load->library('cache');
         $conn = $this->cache->useMemcache('127.0.0.1', 11211);
        $this->slave->from('t_login');
        $query = $this->slave->get();
        $this->cache->save('loginInfo', $query->result(), null, 3600);
        var_dump($this->cache->get('loginInfo'));

?>

安装memcached:
下载:http://jehiah.cz/projects/memcached-win32/files/memcached-1.2.1-win32.zip
安装:cmd --> memcached.exe -d install
启动:cmd --> memcached.exe -d start
启动成功以后。

在php.ini里面加载memcache.dll
下载:http://jp.php.net/distributions/pecl-5.2.6-Win32.zip(注意版本,我的php是5.2.6)
解压缩,找到memcache.dll放在ext目录下。

加载:打开php.ini:
extension=php_memcache.dll

重启apache。没有错误表示可以用memcache了。

使用:
<?php
$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");
$data = $memcache->get('view_data');
$memcache->set('view_data', $data, false, 3600) or die ("Failed to save data at the server");
$memcache->get('view_data');
?>


相关热词搜索:使用 主从 读写

上一篇:总结mysql服务器查询慢原因与解决方法
下一篇:mysql ,mysqladmin: command not found解决方法