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

配置数据库

1. .env文件

DB_CONNECTION=mysql
DB_HOST=192.168.1.114
DB_PORT=3306
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=123456

2. 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)