mysql主从配置实现一主一从读写分离

╰半夏微凉° 2022-01-11 05:43 287阅读 0赞

主从介绍  
Mysql主从又叫Replication、AB复制。简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，实现数据实时同步

mysql主从是基于binlog，主上需开启binlog才能进行主从

主从过程大概有3个步骤

主将更改操作记录到binlog里

从将主的binlog事件（sql语句） 同步本机上并记录在relaylog里

从根据relaylog里面的sql语句按顺序执行

主从作用

*  实时灾备，用于故障切换
 *  读写分离，提供查询服务
 *  备份，避免影响业务

主从形式  
![mysql主从配置实现一主一从读写分离][mysql]

* 一主一从
    * 主主复制
    * 一主多从---扩展系统读取的性能，因为读是在从库读取的
    * 多主一从---5.7版本开始支持
    * 联级复制

主从复制原理  
![mysql主从配置实现一主一从读写分离][mysql 1]

主从复制步骤

*  主库将所有的写操作记录在binlog日志中，并生成log dump线程，将binlog日志传给从库的I/O线程
 *  从库生成两个线程，一个是I/O线程，另一个是SQL线程
 *  I/O线程去请求主库的binlog日志，并将binlog日志中的文件写入relay log（中继日志）中
 *  SQL线程会读取relay loy中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终数据一致的目的

主从复制配置步骤：

1.  确保从数据库与主数据库里的数据一致
2.  在主数据库里创建一个同步账户授权给从数据库使用
3.  配合主数据库（修改配置文件）
4.  配置从数据库（修改配置文件）

需求  
搭建两台MYSQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明

<table> 
 <thead> 
  <tr> 
   <th>数据库角色</th> 
   <th>IP</th> 
   <th>应用与系统</th> 
   <th>有无数据</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主数据库</td> 
   <td>192.168.55.130</td> 
   <td>centos7 mysql-5.7</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>从数据库</td> 
   <td>192.168.55.129</td> 
   <td>centos7 mysql-5.7</td> 
   <td>无</td> 
  </tr> 
 </tbody> 
</table>

在两台服务器上都按装mysql

环境准备
    关闭防火墙以SELINUX
    [root@yanyinglai ~]# systemctl stop firewalld
    [root@yanyinglai ~]# systemctl disable firewalld
    [root@yanyinglai ~]#  sed -ri 's/(SELINUX=).*/\1disabled/g' /etc/selinux/config
    [root@yanyinglai ~]# setenforce 0
    
    安装mysql
    安装依赖包
    [root@yanyinglai ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel
    
    创建用户和组
    [root@yanyinglai ~]# groupadd -r -g 306 mysql
    [root@yanyinglai ~]# useradd -M -s /sbin/nologin -g 306 -u 306 mysql
    
    下载二进制格式的mysql软件包
    [root@yanyinglai ~]# cd /usr/src/
    [root@yanyinglai src]#wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
    
    解压软件至/usr/local/
    [root@yanyinglai src]# ls
    debug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
    [root@yanyinglai src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
    [root@yanyinglai src]#  ls  /usr/local/
    bin  etc  games  include  lib  lib64  libexec  mysql-5.7.22-linux-glibc2.12-x86_64  sbin  share  src
    [root@yanyinglai src]#  cd  /usr/local/
    [root@yanyinglai local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
    "mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"
    [root@yanyinglai local]# ll
    
    总用量 0
    drwxr-xr-x. 2 root root   6 11月  5 2016 bin
    drwxr-xr-x. 2 root root   6 11月  5 2016 etc
    drwxr-xr-x. 2 root root   6 11月  5 2016 games
    drwxr-xr-x. 2 root root   6 11月  5 2016 include
    drwxr-xr-x. 2 root root   6 11月  5 2016 lib
    drwxr-xr-x. 2 root root   6 11月  5 2016 lib64
    drwxr-xr-x. 2 root root   6 11月  5 2016 libexec
    lrwxrwxrwx. 1 root root  36 9月   7 22:20 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
    drwxr-xr-x. 9 root root 129 9月   7 22:19 mysql-5.7.22-linux-glibc2.12-x86_64
    drwxr-xr-x. 2 root root   6 11月  5 2016 sbin
    drwxr-xr-x. 5 root root  49 9月   3 23:02 share
    drwxr-xr-x. 2 root root   6 11月  5 2016 src
    
    修改目录/usr/locaal/mysql的属主属组
    [root@yanyinglai local]# chown -R mysql.mysql /usr/local/mysql
    [root@yanyinglai local]#  ll /usr/local/mysql -d
    lrwxrwxrwx. 1 mysql mysql 36 9月   7 22:20 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
    
    添加环境变量
    [root@yanyinglai local]# ls /usr/local/mysql
    bin  COPYING  docs  include  lib  man  README  share  support-files
    [root@yanyinglai local]# cd
    [root@yanyinglai ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
    [root@yanyinglai ~]# . /etc/profile.d/mysql.sh
    [root@yanyinglai ~]# echo $PATH
    /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
    
    建立数据存放目录
    [root@yanyinglai ~]# cd /usr/local/mysql
    [root@yanyinglai mysql]# mkdir /opt/data
    [root@yanyinglai mysql]#  chown -R mysql.mysql /opt/data/
    [root@yanyinglai mysql]#  ll /opt/
    总用量 0
    drwxr-xr-x. 2 mysql mysql 6 9月   7 22:25 data
    
    初始化数据库
    [root@yanyinglai mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
    //这个命令的最后会生成一个临时密码，此处密码是1EbNA-k*BtKo
    
    配置mysql
    [root@yanyinglai ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
    "/usr/local/include/mysql" -> "/usr/local/mysql/include/"
    [root@yanyinglai ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
    [root@yanyinglai ~]#  ldconfig -v
    
    生成配置文件
    [root@yanyinglai ~]# cat > /etc/my.cnf <<EOF
    > [mysqld]
    > basedir = /usr/local/mysql
    > datadir = /opt/data
    > socket = /tmp/mysql.sock
    > port = 3306
    > pid-file = /opt/data/mysql.pid
    > user = mysql
    > skip-name-resolve
    > EOF
    
    配置服务启动脚本
    [root@yanyinglai ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
    [root@yanyinglai ~]#  sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
    [root@yanyinglai ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
    
    启动mysql
    [root@yanyinglai ~]#  service mysqld start
    Starting MySQL.Logging to '/opt/data/yanyinglai.err'.
    .. SUCCESS!
    [root@yanyinglai ~]#  ps -ef|grep mysql
    root       4897      1  0 22:38 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
    mysql      5075   4897  6 22:38 pts/2    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=yanyinglai.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
    root       5109   4668  0 22:38 pts/2    00:00:00 grep --color=auto mysql
    [root@yanyinglai ~]# ss -antl
    State       Recv-Q Send-Q                     Local Address:Port                                    Peer Address:Port              
    LISTEN      0      128                                    *:22                                                 *:*                  
    LISTEN      0      100                            127.0.0.1:25                                                 *:*                  
    LISTEN      0      128                                   :::22                                                :::*                  
    LISTEN      0      100                                  ::1:25                                                :::*                  
    LISTEN      0      80                                    :::3306                                              :::*                  
    
    修改密码
    使用临时密码修改
    [root@yanyinglai ~]# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.22
    
    Copyright (c) 2000, 2018, 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> set password = password('123456');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> quit
    Bye
    
    mysql主从配置
    确保从数据库与主数据库的数据一样先在主数据库创建所需要同步的库和表
    [root@yanyinglai ~]# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.22 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. Al
    
    Oracle is a registered trademark of Oracle Corporation and
    affiliates. Other names may be trademarks of their respect
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the curr
    
    mysql> create database yan;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> create database lisi;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> create database wangwu;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use yan;
    Database changed
    mysql> create table tom (id int not null,name varchar(100)not null ,age tinyint);
    Query OK, 0 rows affected (11.83 sec)
    
    mysql> insert tom (id,name,age) values(1,'zhangshan',20),(2,'wangwu',7),(3,'lisi',23);
    Query OK, 3 rows affected (0.07 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from tom;
    +----+-----------+------+
    | id | name      | age  |
    +----+-----------+------+
    |  1 | zhangshan |   20 |
    |  2 | wangwu    |    7 |
    |  3 | lisi      |   23 |
    +----+-----------+------+
    3 rows in set (0.00 sec)
    
    备份主库
    备份主库时需要另开一个终端，给数据库上读锁，避免在备份期间有其他人在写入导致数据同步的不一致
    
    [root@yanyinglai ~]# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.22 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> flush tables with read lock;
    Query OK, 0 rows affected (0.76 sec)
    //此锁表的终端必须在备份完成以后才能退出（退出锁表失效）
    
    备份主库并将备份文件传送到从库
    [root@yanyinglai ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-20180907.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@yanyinglai ~]# ls /opt/
    all-20180907.sql  data
    [root@yanyinglai ~]# scp /opt/all-20180907.sql root@192.168.55.129:/opt/
    The authenticity of host '192.168.55.129 (192.168.55.129)' can't be established.
    ECDSA key fingerprint is SHA256:7mLj77SFk7sPkhjpMPfdK3nZ98hOuyP4OKzjXeijSJ0.
    ECDSA key fingerprint is MD5:a0:1b:eb:7f:f0:b6:7b:73:97:91:4c:f3:b1:89:d8:ea.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added '192.168.55.129' (ECDSA) to the list of known hosts.
    root@192.168.55.129's password:
    all-20180907.sql       100%  784KB 783.3KB/s   00:01    
    
    解除主库的锁表状态，直接退出交互式界面即可
    
    mysql> quit
    Bye
    
    在从库上恢复主库的备份并查看是否与主库的数据保持一致
    
    [root@yanyinglai ~]# mysql -uroot -p123456 < /opt/all-20180907.sql
    mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@yanyinglai ~]# mysql -uroot -p123456
    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 4
    Server version: 5.7.22 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | lisi               |
    | mysql              |
    | performance_schema |
    | sys                |
    | wangwu             |
    | yan                |
    +--------------------+
    7 rows in set (0.18 sec)
    
    mysql> use yan;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from tom;
    +----+-----------+------+
    | id | name      | age  |
    +----+-----------+------+
    |  1 | zhangshan |   20 |
    |  2 | wangwu    |    7 |
    |  3 | lisi      |   23 |
    +----+-----------+------+
    3 rows in set (0.06 sec)
    在主数据库创建一个同步账户授权给从数据使用
    [root@yanyinglai ~]# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 5.7.22 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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.55.129' identified by '123456';
    Query OK, 0 rows affected (5.50 sec)
    
    mysql> grant replication slave on *.* to 'repl'@'192.168.55.129';
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.09 sec)
    
    配置主数据库编辑配置文件
    [root@yanyinglai ~]# vim /etc/my.cnf
    [root@yanyinglai ~]# cat /etc/my.cnf
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /opt/data/mysql.pid
    user = mysql
    skip-name-resolve
    //添加以下内容
    log-bin=mysql-bin //启用binlog日志
    server-id=1 //主数据库服务器唯一标识符 主的必须必从大
    log-error=/opt/data/mysql.log
    
    重启mysql服务
    [root@yanyinglai ~]# service mysqld restart
    Shutting down MySQL..... SUCCESS!
    Starting MySQL.Logging to '/opt/data/mysql.log'.
    ............................... SUCCESS!
    [root@yanyinglai ~]# ss -antl
    State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
    LISTEN      0      128     *:22                  *:*                  
    LISTEN      0      100    127.0.0.1:25                  *:*                  
    LISTEN      0      128    :::22                 :::*                  
    LISTEN      0      100       ::1:25                 :::*                  
    LISTEN      0      80     :::3306               :::*
    
    查看主库的状态
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 |      154 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    配置从数据库
    编辑配置文件
    [root@yanyinglai ~]# cat /etc/my.cnf
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /opt/data/mysql.pid
    user = mysql
    skip-name-resolve
    //添加以下内容：
    server-id=2 //设置从库的唯一标识符 从的必须比主小
    relay-log=mysql-relay-bin //启用中继日志relay log
    error-log=/opt/data/mysql.log
    
    重启从库的mysql服务
    
    [root@yanyinglai ~]# service mysqld restart
    Shutting down MySQL.. SUCCESS!
    Starting MySQL.. SUCCESS!
    [root@yanyinglai ~]# ss -antl
    State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
    LISTEN      0      128     *:22                  *:*                  
    LISTEN      0      100    127.0.0.1:25                  *:*                  
    LISTEN      0      128    :::22                 :::*                  
    LISTEN      0      100       ::1:25                 :::*                  
    LISTEN      0      80     :::3306               :::*                  
    
    配置并启动主从复制
    mysql> change master to
        -> master_host='192.168.55.130',
        -> master_user='repl',
        -> master_password='123456',
        -> master_log_file='mysql-bin.000001',
        -> master_log_pos=154;
    Query OK, 0 rows affected, 2 warnings (0.28 sec)
    
    查看从服务器状态
    
    mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.55.130
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 154
                   Relay_Log_File: mysql-relay-bin.000003
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes                                     //此处必须是yes
                Slave_SQL_Running: Yes                                    //此处必须是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: 154
                  Relay_Log_Space: 527
                  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: 5abf1791-b2af-11e8-b6ad-000c2980fbb4
                 Master_Info_File: /opt/data/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
    测试验证在主服务器的yan库的tom表插入数据:
    mysql> use yan;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from tom;
    +----+-----------+------+
    | id | name      | age  |
    +----+-----------+------+
    |  1 | zhangshan |   20 |
    |  2 | wangwu    |    7 |
    |  3 | lisi      |   23 |
    +----+-----------+------+
    3 rows in set (0.09 sec)
    
    mysql> insert tom(id,name,age) value (4,"yyl",18);
    Query OK, 1 row affected (0.14 sec)
    
    mysql> select * from tom;
    +----+-----------+------+
    | id | name      | age  |
    +----+-----------+------+
    |  1 | zhangshan |   20 |
    |  2 | wangwu    |    7 |
    |  3 | lisi      |   23 |
    |  4 | yyl       |   18 |
    +----+-----------+------+
    4 rows in set (0.00 sec)
    在从数据库查看是否数据同步
    mysql> use yan;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from tom;
    +----+-----------+------+
    | id | name      | age  |
    +----+-----------+------+
    |  1 | zhangshan |   20 |
    |  2 | wangwu    |    7 |
    |  3 | lisi      |   23 |
    |  4 | yyl       |   18 |
    +----+-----------+------+
    4 rows in set (0.00 sec)

[mysql]: https://s1.51cto.com/images/blog/201809/09/aea74b8cc91ee5017fd5e21b805462cf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[mysql 1]: https://s1.51cto.com/images/blog/201809/09/9daf85dd575baf33371ffc0da44dfc08.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=