【MySQL】CentOS7实现MySQL读写分离环境搭建

短命女 2023-10-05 13:58 54阅读 0赞

#### MySQL主从搭建 ####

*   *  基础环境
     *  数据库主从搭建
     *   *  主机Master配置
         *  Slave 配置

### 基础环境 ###

四台已安装MySQL服务或者MariaDB的CentOS虚拟机，安装请参考[CentOS7安装MySQL数据库及MariaDB][CentOS7_MySQL_MariaDB]，主机及ip如下所示

<table> 
 <thead> 
  <tr> 
   <th align="center">主机</th> 
   <th align="center">IP地址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><strong>CentOS</strong>(主机)</td> 
   <td align="center">192.168.239.136</td> 
  </tr> 
  <tr> 
   <td align="center"><strong>CentOSA</strong>(从机A)</td> 
   <td align="center">192.168.239.137</td> 
  </tr> 
  <tr> 
   <td align="center"><strong>CentOSB</strong>(从机B)</td> 
   <td align="center">192.168.239.138</td> 
  </tr> 
  <tr> 
   <td align="center"><strong>CentOSC</strong>(从机C)</td> 
   <td align="center">192.168.239.139</td> 
  </tr> 
 </tbody> 
</table>

### 数据库主从搭建 ###

#### 主机Master配置 ####

①修改主机CentOS下的`/etc/my.cnf`文件

vim /etc/my.cnf

[mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    #server-id必须唯一
    server-id=1
    log-bin=mysql-bin
    binlog-do-db=dora
    binlog-do-db=yddm
    binlog-ignore-db=mysql
    binlog-ignore-db=test
    expire_logs_days=10
    auto_increment_increment=2
    auto_increment_offset=1
    
    # 不需要修改以下内容
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

②重启MySQL服务

[root@CentOS ~]# service mysqld restart
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]

③登录MySQL主机查看状态

#### Slave 配置 ####

① 修改CentOSA/B/C三台从机的`/etc/my.cnf`文件

vim /etc/my.cnf

[mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    # server-id 需唯一
    server-id=2
    log-bin=mysql-bin
    replicate-do-db=dora
    replicate-do-db=yddm
    replicate-ignore-db=mysql
    replicate-ignore-db=test
    expire_logs_days=10
    auto_increment_increment=2
    auto_increment_offset=2
    
    # 不需要修改以下内容
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

③ 重启MySQL服务

[root@CentOS ~]# service mysqld restart
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]

④ MySQL配置从机，在从机上配置

[root@CentOS ~]# mysql -uroot -proot
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.1.73-log Source distribution
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    # 关闭slave节点
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    # 设置主节点
    mysql> change master to master_host='主机IP',master_user='root',master_password='root',master_log_file='主机的File',master_log_pos=主机的Position;
    Query OK, 0 rows affected (0.03 sec)
    
    # 开启slave节点
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    # 查看slave节点状态
    # 看到Slave_IO_Running及Slave_SQL_Running为Yes即代表成功配置
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: CentOSB
                      Master_User: root
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 106
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 251
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: dora,yddm
              Replicate_Ignore_DB: mysql,test
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 106
                  Relay_Log_Space: 407
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
    1 row in set (0.00 sec)

[CentOS7_MySQL_MariaDB]: https://blog.csdn.net/qq_44664231/article/details/109805512