读写分离好处是提高数据库的高可用,水平扩展性能:
- 水平扩展数据库的负载能力。
- 容错,高可用。
- 数据备份。
至于原理,大家可以自行百度,也可以参考下这篇文章:
mysql读写分离原理介绍
http://blog.csdn.net/soar_awa...
下面,开始数据库配置读写分离。
- 本机模拟两个数据库。分别为phpstudy以及centos安装的mysql。
ip1(CENTOS):192.168.92.128
ip2:192.168.99.1
准备用ip1作为master,ip2作为slave.
我用sqlyog链接centos上的数据库时遇到经常碰到的问题,“mysql"Access denied for user'root'@'IP地址'"”,解决参考:ACCESS DENIED -
配置master库
vim /etc/my.cnf bind-address = 192.168.92.128 #your master ip server-id = 1 #在master-slave架构中,每台机器节点都需要有唯一的server-id log_bin = /var/log/mysql/mysql-bin.log #开启binlog
重启:service mysql restart
创建主从同步用户:
$ mysql -u root -p Password: ##创建slave2用户,并指定该用户只能在主机192.168.99.1(slave)上登录。 mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave2'@'%' IDENTIFIED BY '111111' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
- 为了方便演示,直接在两个库中建立相同的数据库test和表linkdb(id,value)。
-
配置slave库
bind-address = 192.168.99.1 #your slave ip,不绑定的话可以不配 server-id = 2 #master-slave结构中,唯一的server-id log_bin = /var/log/mysql/mysql-bin.log #开启binlog
-
slave上建立链接
$ mysql -u root -p Password: mysql> STOP SLAVE; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.92.128', -> MASTER_USER='slave2', -> MASTER_PASSWORD='111111', -> MASTER_LOG_FILE='mysql-bin.000018', -> MASTER_LOG_POS=372; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec)
MASTER_LOG_FILE='mysql-bin.000018'与MASTER_LOG_POS=372的值,是从上面的 SHOW MASTER STATUS 得到的。
正常情况下,在slave上执行`SHOW PROCESSLIST`如下:
Id User Host db Command Time State Info
------ ----------- --------------- ------ ------- ------ --------------------------------------------------------------------------- ------------------
4 root localhost:51955 test Query 0 (NULL) SHOW PROCESSLIST
5 root localhost:51956 (NULL) Sleep 1396 (NULL)
15 system user (NULL) Connect 735 Waiting for master to send event (NULL)
16 system user (NULL) Connect -28385 Slave has read all relay log; waiting for the slave I/O thread to update it (NULL)
正常情况下,在slave上执行SHOW SLAVE STATUS
如下:
Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id
-------------------------------- -------------- ----------- ----------- ------------- ---------------- ------------------- -------------------------------- ------------- --------------------- ---------------- ----------------- --------------- ------------------- ------------------ ---------------------- ----------------------- --------------------------- ---------- ---------- ------------ ------------------- --------------- --------------- -------------- ------------- ------------------ ------------------ ------------------ --------------- ----------------- -------------- --------------------- ----------------------------- ------------- ------------- -------------- -------------- --------------------------- ------------------
Waiting for master to send event 192.168.92.128 slave2 3306 60 mysql-bin.000018 2341 XB-201703192016-relay-bin.000002 2222 mysql-bin.000018 Yes Yes 0 0 2341 2388 None 0 No 0 No 0 0 1
以下两个参数很重要,只有都为YES的时候才说明配置无误。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果Slave_IO_Running为connecting,检查:A.slave链接时,通过master创建的帐号是否可以连上master。B.网络是否通畅。
正常情况下,在master上执行SHOW PROCESSLIST
如下:
Id User Host db Command Time State Info
------ ------ ------------------ ------ ----------- ------ --------------------------------------------------------------------- ------------------
3 root 192.168.92.1:52056 mysql Query 0 (NULL) SHOW PROCESSLIST
5 slave2 192.168.92.1:52060 (NULL) Binlog Dump 1066 Master has sent all binlog to slave; waiting for binlog to be updated (NULL)
tips:由于我本机上安了vmwaer和docker等等...通过cmd-ipconfig获得本机的局域网ip时搞错了好几次,当这种情况出现时,总结了一个比较好的方法,见获得本机局域网ip
lz当然是配置成功啦,小试一下:
master上INSERT INTO test
.linkdb
(id
, value
) VALUES ('4', '3');
slave刷新表更新成功。删除、修改同样OK,收工!
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。