Mysql主从复制搭建与读写分离

迷南。 2022-04-24 01:00 224阅读 0赞

### Mysql主从复制搭建 ###

*  准备条件
 *  Master-Server 配置
 *   *  修改配置
     *  重启Mysql
     *  配置主从复制账户
 *  Slave-Server 配置
 *   *  修改配置
     *  重启Mysql
     *  配置Master信息开启主从复制
 *  读写分离
 *   *  Master-Server 配置
     *  Slave-Server 配置

# 准备条件 #

准备2台机器先安装MySQL 5.7.20  
Master-Server : 192.168.0.100  
Slave-Server : 192.168.0.99

# Master-Server 配置 #

## 修改配置 ##

vim /etc/my.conf

添加以下配置

log-bin=mysql-bin
    binlog_format=mixed
    skip-name-resolve
    innodb_file_per_table
    
    server-id = 1

## 重启Mysql ##

/etc/init.d/mysqld restart

## 配置主从复制账户 ##

[root@iZbp1ajd582c8dtiadg2ggZ bin]# /opt/bin/mysql/bin/mysql -uroot -p'password'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.7.20-log MySQL Bright Thought
    
    Copyright (c) 2000, 2017, 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.
    
    mysql> CREATE USER 'repl'@'192.168.0.99' IDENTIFIED BY 'password';
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.99';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000003 |     1457 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

# Slave-Server 配置 #

## 修改配置 ##

vim /etc/my.conf

添加以下配置

log-bin=mysql-bin
    binlog_format=mixed
    relay-log=relay-log
    relay-log-index=relay-log.index
    
    skip-name-resolve
    innodb_file_per_table
    
    server-id = 2
    #slave_skip_errors = 1032,1062,1064
    slave_parallel_type = LOGICAL_CLOCK
    slave_parallel_workers = 2

## 重启Mysql ##

/etc/init.d/mysqld restart

## 配置Master信息开启主从复制 ##

[root@iZbp1c0kqbtw5ac7nkw1pfZ bin]# /opt/bin/mysql/bin/mysql -uroot -p'password'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.7.20-log MySQL Bright Thought
    
    Copyright (c) 2000, 2017, 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.
    
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100',MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1457,MASTER_CONNECT_RETRY=10;
    Query OK, 0 rows affected, 2 warnings (0.04 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.0.100
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 10
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 1457
                   Relay_Log_File: relay-log.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               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: 1457
                  Relay_Log_Space: 521
                  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: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 67a9c6c4-5c14-11e9-ad77-00163e098a07
                 Master_Info_File: /opt/data/db/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified

# 读写分离 #

## Master-Server 配置 ##

CREATE USER 'developer'@'192.168.0.%' IDENTIFIED BY 'password';
    GRANT SELECT,INSERT,UPDATE ON *.* TO 'developer'@'192.168.0.%';
    show grants for developer@'192.168.0.%';

## Slave-Server 配置 ##

REVOKE INSERT, UPDATE ON *.* from 'developer'@'192.168.0.%';
    
    show grants for developer@'192.168.0.%';