Laravel 数据库读写分离

时间:2021-12-08 16:14:17

config/database.php

...
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
/* test master/slave */
'laravel_test_ms'=>array(
'read'=>array(
'host' => '10.0.13.46',
'username' => 'laravel_reader',
'password' => 'qwerqwer123',
),
'write'=>array(
'host' => '10.0.13.46',
'username' => 'laravel_writer',
'password' => '1qaz1qaz123',
),
'driver' => 'mysql',
'database' => 'laravel',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
),
/* 多数据库连接-读写分离-手动 */
'laravel_reader'=>array(
'driver' => 'mysql',
'host' => '10.0.13.46',
'database' => 'laravel',
'username' => 'laravel_reader',
'password' => 'qwerqwer123',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
),
'laravel_writer'=>array(
'driver' => 'mysql',
'host' => '10.0.13.46',
'database' => 'laravel',
'username' => 'laravel_writer',
'password' => '1qaz1qaz123',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
),
...

app/Http/routes.php

Route::get('tongji/info', 'TongjiController@info');
Route::get('tongji/multi', 'TongjiController@multi');
Route::get('tongji/ms', 'TongjiController@ms');

app/Http/Controllers/TongjiController.php

<?php
namespace App\Http\Controllers;
use App\Tongji;
class TongjiController extends Controller
{ /**
* 多数据库连接-读写分离-手动
* @access public
* @author zhaoyingnan 2016-09-18 17:05
* @return mix
* @note
**/
public function multi()
{
return Tongji::mMultiDBConfTest(2);
}
/**
* 框架自动读写分离
* @access public
* @author zhaoyingnan 2016-09-18 17:53
* @param int $iVar
* @return mix
* @note
**/
public function ms()
{
return Tongji::mMasterSlaveTest(1);
}
}
?>

app/Tongji.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
#use Illuminate\Support\Facades\DB;
use DB;
class Tongji extends Model
{
/**
* 1.读写分离-多数据库连接(database.php)-手动
* @access public
* @author zhaoyingnan 2016-09-18 17:00
* @param int $iType
* @return mix
* @note
**/
static function mMultiDBConfTest($iType = 1)
{
# 数据库权限设置
# grant select on laravel.* to laravel_reader@'%' identified by 'qwerqwer123';
# grant select,insert,update,delete on laravel.* to laravel_writer@'%' identified by '1qaz1qaz123';
# flush privileges;
if($iType == 1)
{
# reader
return DB::connection('laravel_reader')->select('select * from student limit 1');
}
else
{
# writer
return DB::connection('laravel_writer')->update('update student set `age`=:age where id=:id', array('age'=>22, 'id'=>1));
}
}
/**
* 2.读写分离-框架自动
* @access public
* @author zhaoyingnan 2016-09-18 17:41
* @param int $iType
* @return mix
* @note
**/
static function mMasterSlaveTest($iType = 1)
{
if($iType == 1)
{
# reader
return DB::connection('laravel_test_ms')->select('select * from student limit 1');
}
else
{
# writer
return DB::connection('laravel_test_ms')->update('update student set `age`=:age where id=:id', array('age'=>20, 'id'=>3));
}
}
}
?>