2

读写分离好处是提高数据库的高可用,水平扩展性能:

  • 水平扩展数据库的负载能力。
  • 容错,高可用。
  • 数据备份。

至于原理,大家可以自行百度,也可以参考下这篇文章:
mysql读写分离原理介绍
http://blog.csdn.net/soar_awa...
下面,开始数据库配置读写分离。

  1. 本机模拟两个数据库。分别为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
  2. 配置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)
    
  3. 为了方便演示,直接在两个库中建立相同的数据库test和表linkdb(id,value)。
  4. 配置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
  5. 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,收工!


DoINsiSt
167 声望9 粉丝

很简单,在一个领域保持顶尖水平,比在一两个领域保持领先水平和五六个领域保持一般水准都要更有价值、并且收益更好。