laravel5 mysql数据库读写分离
环境
项目在本地的LNMP环境中,自己的电脑使用的ubuntu18.04,nginx1.14,php7.2,mysql5,7,主数据库ip:192.168.1.114 从服务器ip:192.168.1.115(数据库服务器为之前配置的数据库主从分离)
实战
新建数据库
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
新建user表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_phone` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`user_nicename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`user_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`user_pwd` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
laravel配置
获取laravel 使用composer安装laravel
如果下速度太慢可以选择设置composer使用 国内镜像
composer create-project --prefer-dist laravel/laravel laravel
配置数据库
- .env文件
DB_CONNECTION=mysql
DB_HOST=192.168.1.114
DB_PORT=3306
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=123456
- config/databases.php
'mysql' => [
'read' => [
'host' => '192.168.80.115' /#从服务器
],
'write' => [
'host' => '192.168.80.114' /#主服务器
],
'driver' => 'mysql',
//'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
php artisan make:model Http/Model/User 命令创建模型
<?php
namespace App\Http\Model;
use Illuminate\Database\Eloquent\Model;
class User extends Model {
protected $table = 'user';
protected $primaryKey = 'id';
public $timestamps = false;
//允许批量赋值的字段
protected $fillable = [
'user_id',
'user_phone',
'user_nicename',
'user_email',
'user_pwd',
];
}
php artisan make:controller Index/IndexController创建控制器
<?php
namespace App\Http\Controllers\Index;
use App\Http\Model\User;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
class IndexController extends Controller {
//开启测试
public function index() {
echo '你好';
}
//查询测试
public function select() {
$user = User::all();
dd($user->toArray());
}
//插入测试
public function insert($p) {
$input = [
'user_phone' => '1234567890'.$p,
'user_nicename' => $p,
'user_email' => '123'.$p."@123.com",
'user_pwd' => md5($p)
];
$res = User::create($input);
dd($res);
}
}
设置路由
<?php
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::group(['middleware' => ['web']], function () {
// Route::get('/', function (){
// return view('welcome');
// });
Route::get('/', 'Index\IndexController@index');
Route::get('/select', 'Index\IndexController@select');
Route::get('/insert/{p}', 'Index\IndexController@insert');
});
如果出现无法连接远程服务器的错误,可能是因为root用户没有配置远程访问权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
使用mysql general log来验证数据库读写分离
主数据库开启general log
mysql> show global variables like '%general%';
+------------------+------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/work-VirtualBox.log |
+------------------+------------------------------------+
2 rows in set (0.03 sec)
mysql> set global general_log = on;
Query OK, 0 rows affected (0.01 sec)
mysql> quit;
Bye
查看主数据库日志
sudo cat /var/lib/mysql/work-VirtualBox.log
从数据库开启general log
mysql> show global variables like '%general%';
+------------------+------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/work-VirtualBox.log |
+------------------+------------------------------------+
2 rows in set (0.03 sec)
mysql> set global general_log = on;
Query OK, 0 rows affected (0.02 sec)
mysql> quit;
Bye
查看从数据库日志
sudo cat /var/lib/mysql/work-VirtualBox.log
执行写操作,主服务器日志写入内容,执行查询操作,从服务器日子写入信息
关闭日志
mysql> set global general_log = off; //关闭日志
Query OK, 0 rows affected (0.01 sec)
网友评论