美文网首页
laravel mysql数据库读写分离实验

laravel mysql数据库读写分离实验

作者: 小山人 | 来源:发表于2018-06-01 14:54 被阅读0次

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

    相关文章

      网友评论

          本文标题:laravel mysql数据库读写分离实验

          本文链接:https://www.haomeiwen.com/subject/yxahsftx.html