mysql主从基础

2021-07-29 00:47:24

目录

1.概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

主从复制的逻辑有以下几种：

一主一从：单向主从同步模式，只能在Master端写入数据。

一主多从：提高系统的读性能。

双主复制：此架构可以在Master1或Master2进行数据写入，或者两端同时写入（特殊设置）。

多主一从（MySQL5.7开始支持）

级联复制

mysql主从基础

为什么mysql要设置主从复制？

  • 数据库数据是一个公司或者集团企业最为重要的资产，以防数据的丢失和损坏，需要进行备份
  • 当用户的访问量越来越高的时候，一旦查询也就是读取数据的操作太频繁了，势必网站崩掉，服务器宕机，很影响用户的体验度
  • 提高数据库系统的可用性

2.mysql主从复制原理

mysql主从基础

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

从服务器slave为什么不能直接存储二进制日志文件里面的数据（为什么不开启bin-log日志）？

本来做数据的主从同步就是为了让计算机快速的进行读写操作，而且是大批量的数据，一旦大量数据进行写入或者更新数据，从数据库slave如果直接从二进制日志来接收，数据是以队列形式进行传输的，若队列的数据没有快速处理，堆积起来，从服务器可能也会崩溃宕机，所以从性能上考虑，从服务器slave创建了I/O线程对象将数据转到中继日志，起个缓存功能。

过程分析

  • 在从库上执行change master to；会将主库的信息保存到从库中的master.info文件中
  • 在从库执行start slave;开启io_thread, sql_thread;
  • io_thread工作；io_thread通过master.info文件中主库的连接信息去连接主库；连接成功后主库就会开启dump_thread;
  • dump_thread读取主库新产生的二进制日志；然后投递给io_thread；
  • io_thread接收dump_thread投递的新的二进制日志，将日志写入到relay log(中继日志)；
  • io_thread就会等待主库dump_thread主动把新产生的二进制日志投递；
  • sql_thread会将relay log新产生的日志恢复到数据库（relay重放），写到磁盘

3.主从状态详解

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.190.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 194
               Relay_Log_File: localhost-relay-bin.000004
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000011
             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: 194
              Relay_Log_Space: 705
              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: 77cf5494-00ce-11ec-9c86-000c290c7222
             Master_Info_File: /data/mysqldata/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: 77cf5494-00ce-11ec-9c86-000c290c7222:1-4
            Executed_Gtid_Set: 77cf5494-00ce-11ec-9c86-000c290c7222:1-4
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)


Master_Host: 192.168.190.128 #主库ip
Master_User: repl #主库用户
Master_Port: 3306 #主库端口号

Master_Log_File: mysql-bin.000011 #当前从库io_thread正在读取主库的二进制日志文件
Read_Master_Log_Pos: 194 #当前从库io_thread正在读取主库二进制日志文件的位置

Relay_Log_File: localhost-relay-bin.000004 #当前从库sql_thread正在读取从库的中继日志文件
Relay_Log_Pos: 407 #当前sql_thread正在读取从库中继日志文件的位置

Relay_Master_Log_File: mysql-bin.000011 #当前从库sql_thread从relay log中读取的正在进行的sql语句，对应主库的sql语句是在哪个binlog中；
Exec_Master_Log_Pos: 194 #从库sql_thread当前执行的事件，对应主库的binlog中的position

Seconds_Behind_Master: 0 #主从复制延迟的时间；如果是0表示主从无延迟

Last_IO_Error #显示io线程错误信息
Last_SQL_Error #显示sql线程错误信息

SQL_Delay: 0 #延迟复制

Retrieved_Gtid_Set: #当前io_thread已经接受到的binlog
Executed_Gtid_Set:  #当前从库sql_thread执行的二进制日志位置

4.主从所需前提

前提条件：

  • 两个实例不同的server_id

  • 主库开启binlog

  • 主库创建replication slave权限的用户

例如：

Master主机配置：

cat /home/data/mysql3306/my.cnf

server_id = 1

log_bin = /home/data/mysql3306/mysql-bin

binlog_format=MIXED

上面两个是必须配置的，其他参数根据自己的MySQL安装目录和业务情况自行配置

Slave主机配置：

cat /home/data/mysql3306/my.cnf

server_id = 2

Slave主机的binlog不是必须开启的，其他参数根据自己的MySQL安装目录和业务情况自行配置，如果有级联复制的需求，才进行开启，一般主从架构不开启，以节省磁盘I/O

5.主从配置示例

两个数据库实例

? 192.168.190.128（主）

? 192.168.190.128（从）

开始前，一定要确保主从数据一致

如果主从库数据差距大，先把主库的备份文件在从库上恢复，在change master时指定备份的pos的偏移量和使用的binlog文件

5.1配置文件

主：

server_id = 1
log_bin=mysql-bin

从：

server-id=2

5.2主库创建一个用于复制的账号

create user ‘repl‘@‘192.168.190.129‘ identified by ‘123456‘;
grant replication slave on *.* to ‘repl‘@‘192.168.190.129‘;

#或者写成一句
grant replication slave on *.* TO ‘repl‘@‘192.168.190.129‘ identified by ‘123456‘;

#刷新权限
flush privileges;

5.3从库启动主从复制

获取帮助信息

mysql> help change master to;

CHANGE MASTER TO
  MASTER_HOST=‘master2.example.com‘,
  MASTER_USER=‘replication‘,
  MASTER_PASSWORD=‘password‘,
  MASTER_PORT=3306,
  MASTER_LOG_FILE=‘master2-bin.001‘,
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

配置并开启主从

因为没有都是数据的新库，直接从头开始

gtid:

CHANGE MASTER TO
  MASTER_HOST=‘192.168.190.128‘,
  MASTER_USER=‘repl‘,
  MASTER_PASSWORD=‘123456‘,
  MASTER_PORT=3306,
  master_auto_position=1,
  MASTER_CONNECT_RETRY=10;
start slave;

#要使用`master_auto_position=1,`必须开启gtid,否则
show slave status \G; 查看从库状态
MASTER_CONNECT_RETRY*连接*失败*重试*的时间间隔，单位为秒

如果是有数据的，从库恢复全备之后，找到全备文件的binlog文件和偏移量，或者在锁库时show master status;，稍稍更改即可

传统主从：

CHANGE MASTER TO
  MASTER_HOST=‘192.168.190.128‘,
  MASTER_USER=‘repl‘,
  MASTER_PASSWORD=‘123456‘,
  MASTER_PORT=3306,
  MASTER_LOG_FILE=‘mysql_bin.000001‘,
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;

6.常用指令

	start slave; 

#开启io_thread和sql_thread
?	start slave io_thread;
?	start slave sql_thread;

?	stop slave; 

#关闭io_thread和sql_thread
?	stop slave io_thread;
?	stop slave sql_thread;

#查看进程
?	show processlist;

#删除master.info,relay-log.info数据；删除所有relay log；将延迟选项master_delay设为0；
?	reset slave; 

#删除所有的二进制日志文件
?	reset master;

?	show binary logs;	#显示系统中的所有二进制日志。
?	show master status;	#正在使用的binlog

mysql主从基础

上一篇：adb-查看包的信息和启动时间


下一篇：java.sql public interface ResultSet