lnmp+laravel+mysql主从复制，读写分离，今天前几天复制到阈值了加第三台服务器
有人看过我架构栏目的文章吗？看过的话那你对这篇文章比较好理解
没有错就是线上加第三台服务器了，申请已批，一台变两台，两台变三台，熟悉的操作，丝滑啊
既然有线上有三台机器了很多功能就可以有操作的空间了，ng的负载均衡，mysql的主从复制及读写分离，redis集群等等redis先不说，
mysql主从复制我之前有写一篇文章的自行查阅 比较简单具体操作就不写了 就流程描述一下
2.因为我是已有数据表所以锁一下主服务器表然后备份下来
flush tables with read lock;3.创建主从同步用户
master create user 'sync'@'192.0.1.%' identified by 'htzxxx';
grant replication slave on *.* to 'sync'@'192.0.1.%';
4.查看状态 记录 file/Position 从服务器用 重启mysql服务 解锁
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000122 | 44200 | db | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
unlock tables;
5.用scp 把数据库sql文件发给新服务器（自行查阅我之前的文章有写的）
6.在新服务器恢复：mysql -uroot -p cardbb < xxx.sql (当然你新服务器把环境装好)
7.设置从服务器 设置同步主服 看底下那两个Yes 为准
mysql> change master to master_host='192xx',master_port=3306,master_user='sync',master_password='xx',master_log_file='mysql-bin.000122',master_log_pos=2883350;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000122
Read_Master_Log_Pos: 3144156
Relay_Log_File: x-relay-bin.000002
Relay_Log_Pos: 261126
Relay_Master_Log_File: mysql-bin.000122
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
完事了 随便在主服务器找个表随便改点数据 在从服务器查一下 有就ok了
也可以在主服上看看 底下就是我的两个从服
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 79348
User: sync
Host: xx1:53180
db: NULL
Command: Binlog Dump
Time: 1117
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 97143
User: sync
Host: xx2:55768
db: NULL
Command: Binlog Dump
Time: 45
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 3. row ***************************
Id: 97798
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 4. row ***************************
Id: 97845
User: root
Host: xx:48018
db: db
Command: Sleep
Time: 0
State:
Info: NULL
4 rows in set (0.00 sec)
ERROR:
No query specifie
laravel5.7 配置读写分离 一台写两台读
1.app/config/database.php
'connections' => [
'mysql' => [
'read' => [
['host' => env('DB_HOST_NEW', '127.0.0.1'),'password' => env('DB_PASSWORD_NEW', '')],
['host' => env('DB_HOST_ONE', '127.0.0.1'),'password' => env('DB_PASSWORD_NEW', '')],
],
'write' => [
'host' => env('DB_HOST', '127.0.0.1'),'password' => env('DB_PASSWORD', '')
],
'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_general_ci',
'prefix' => 'lara_',
'prefix_indexes' => true,
'strict' => false,
'engine' => null,
],
.env 自行设计配置 这是我的测试服 上线后改成线上服就ok了
DB_CONNECTION=mysql
DB_HOST=192.168.1.140
DB_PORT=3306
DB_DATABASE=db
DB_USERNAME=root
DB_PASSWORD=!@#$
DB_HOST_NEW=192.168.1.111
DB_HOST_ONE=192.168.1.222
DB_PASSWORD_NEW=!@#$然后在负载里面加一下新服务器（自行查阅我之前的文章哦）
最后直接用show FULL processlist测试就ok了
在最后别忘记了开启慢查询清理一下慢查询哦
有点迟了 几天没有写 有项目在重构 哈哈 晚安
相关文章