• MySQL5.7主从复制搭建


    MySQL主从复制原理是什么?


     Mysql复制大体有3个步骤:

    1.master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);

    2.slave将master的binary log events拷贝到它的中继日志(relay log);

    3.slave重做中继日志中的事件,将改变反映它自己的数据;


    Mysql复制的基本原理过程如下:

    (1)Slave上面的IO线程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

    (2)Master接收到来自Slave的IO线程的请求后,通过负责复制的IO线程根据请求信息读取指定日志指定位置之后的日志信息,返回给Slave端的IO线程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息在Master端binary log文件的名称以及在Binary log中的位置;

    (3)Slave的IO线程收到信息后,将接收到的日志内容依次写入到Slave端的RelayLog文件(mysql-relay-lin.xxxxx)的最末端,并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”

    (4)Slave的SQL线程检测到Relay Log中新增加了内容后,会马上解析该Log文件中的内容成为在Master端真实执行时候的那些可执行的查询或操作语句,并在自身执行那些查询或操作语句,这样,实际上就是在master端和Slave端执行了同样的查询或操作语句,所以两端的数据是完全一样的;

    Mysql主从复制的优点

    <1> 如果主服务器出现问题,可以快速切换到从服务器提供的服务,水平扩展数据库负载能力;

    <2> 可以在从服务器上执行查询操作,降低主服务器的访问压力;

    <3> 可以在从服务器上执行备份,以避免备份期间影响主服务器的服务;

    服务器环境说明

    操作系统: Centos 7

    主服务器:172.30.93.121

    从服务器:172.30.93.120

    Mysql版本:5.7.26

    安装MySQL

    详见作者之前文章    MySQL二进制安装

    安装NTP服务

    详见作者之前文章    NTP服务器搭建

    主服务器修改配置文件

    [root@Mike-Node1 ~]# vim /etc/my.cnf
    
    server-id = 121
    log-bin =/data/mysql/mysql_bin.log
    binlog_format = row [root@Mike
    -Node1 ~]# [root@Mike-Node1 ~]# /etc/init.d/mysql restart Shutting down MySQL.. [ OK ] Starting MySQL.. [ OK ] [root@Mike-Node1 ~]#

    主服务器配置远程复制用户

    [root@Mike-Node1 ~]# 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.26-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2019, 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> grant replication slave on *.* to 'repl001'@'172.30.93.120' identified by 'repl001mysql';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    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 |      902 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> 

    若主库已经运行了一段时间,有业务数据在,而从库刚刚初始化完成,此时则需要备份主库的数据,然后导入从库,使得主从数据一致

    如果需要同步已有主数据库里的数据到从数据库,需要如下操作(如果新数据库则不需要)

    设置数据库读锁

    mysql> flush tables with read lock;
    Query OK, 0 rows affected (0.00 sec)

    备份要同步的数据库

    [root@Mike-Node1 ~]# mysqldump -u root -p --all-databases > all.sql

    解锁数据库:

    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    复制备份数据库到从服务器:

    [root@Mike-Node1 ~]# scp -r all.sql root@172.30.93.120:/root
    root@172.30.93.120's password: 
    all.sql                                                                                             100%  775KB 132.4MB/s   00:00    
    [root@Mike-Node1 ~]#

    从服务器配置操作

    恢复主库数据库

    [root@Mike-Node2 ~]# mysql -uroot -p < all.sql 
    Enter password: 
    [root@Mike-Node2 ~]# 

    这里你需要进入到数据库里检查一下恢复的数据是否正常

    修改配置文件

    [root@Mike-Node2 ~]# vim /etc/my.cnf
     
    server-id = 120
    relay-log =/data/logs/mysql/mysql_relay.log
    
    [root@Mike-Node2 ~]#
    [root@Mike-Node2 ~]# /etc/init.d/mysql restart
    Shutting down MySQL..                                      [  OK  ]
    Starting MySQL..                                           [  OK  ]
    [root@Mike-Node2 ~]# 

    配置同步数据库

    [root@Mike-Node2 ~]# 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.26-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2019, 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> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> change master to master_host='172.30.93.120',master_user='repl001',master_password='repl001mysql',master_log_file='mysql_bin.000003',master_log_pos=1483;
    Query OK, 0 rows affected, 2 warnings (0.02 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.30.93.121
                      Master_User: repl001
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql_bin.000003
              Read_Master_Log_Pos: 2385
                   Relay_Log_File: mysql_relay.000002
                    Relay_Log_Pos: 1222
            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: 2385
                  Relay_Log_Space: 1425
                  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: 121
                      Master_UUID: 186963b2-4ef8-11eb-8a92-00163e012c5c
                 Master_Info_File: /data/mysql/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)
    
    mysql> 

    注意:master_log_file和master_log_pos是由主服务器执行 show master status; 获取文件日志名和偏移量,具体数据由主服务器产生,此值应和主服务器数据对应

    当时主服务器执行情况:

    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql_bin.000003 |     1483 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    如果出现错误,很有可能就是密码不对,或者是因为没有关闭防火墙,关闭防火墙后再show下

    检查主从复制是否同步

    在主数据库上创建表和删除表,发现从服务器都可以同步

    然后再查看内容是否同步,如果同步证明已经主从成功

    还有一种GTID模式来搭建主从复制是5.6以后的新特性

    大致操作步骤和主从复制差不多,大致步骤如下:

    # 主库参数配置 要有以下参数
    vim /etc/my.cnf 
    [mysqld] 
    server-id = 121
    log-bin =/data/mysql/mysql_bin.log  
    binlog_format = row 
    gtid-mode = ON                   //开启gtid模式
    enforce-gtid-consistency = ON   //强制gtid一致性,用于保证启动gitd后事务的安全 
    
    # 从库建议配置以下参数
    vim /etc/my.cnf 
    [mysqld] 
    server-id = 120
    log-bin =/data/mysql/mysql_bin.log  
    binlog_format = row 
    gtid-mode = ON 
    enforce-gtid-consistency = ON 
    relay-log =/data/logs/mysql/mysql_relay.log
    
    
    创建同步账号,保持主从库数据一致,需要先同步数据库
    # 主库创建同步账号
    create user 'repl001'@'%' identified by 'repl001mysql';
    grant replication slave on *.* to 'repl'@'%';
    
    # 若主库刚初始化或保留有完整二进制文件 则无需执行下面步骤
    # 全备主库数据
    mysqldump -u root -p --all-databases > all.sql
    # 从库端恢复
    mysql -uroot -p < all.sql
    
    进入从库,开启主从复制
    # 进入从库MySQL命令行 执行change master语句连接主库
    
    CHANGE MASTER TO MASTER_HOST='MySQL主服务器IP地址',
        MASTER_PORT=3306,
        MASTER_USER='repl',
        MASTER_PASSWORD='123456',
        MASTER_AUTO_POSITION = 1;
     
    # 开启主从复制 并坚持状态
    start slave;
    show slave status G

    建议:

    1.主从两端数据库版本尽量保持一致
    2.主从库参数建议相同,比如字符集、sql_mode这类参数要设置一样
    3.从库服务器性能不能过于落后主库,以免因服务器性能产生主从延迟
    4.所有表强制拥有主键,因为无主键表同步到从库极易产生主从延迟
    5.建议从库设为read only,以防人为误操作从库数据
    6.监控主从延迟及状态,及时解决同步中断或延迟问题

    本文分享完毕,感谢支持点赞~~

  • 相关阅读:
    python遍历字典元素
    Asp.net网站优化【转】
    三、HTTP协议
    二、 OSI模型的实现TCP 、IP
    一、OSI模型
    JVM原理:4 运行期优化
    JS:jquery插件表格单元格合并.
    28-语言入门-28-1的个数
    27-语言入门-27-成绩转换
    26-语言入门-26-兄弟郊游问题
  • 原文地址:https://www.cnblogs.com/mike666/p/14236186.html
Copyright © 2020-2023  润新知