Mysql主从复制

一、主从复制原理过程

 

1、Slave 服务器上执行 start slave，开启主从复制开关。

2、此时，Slave服务器的IO线程会通过在Master上授权的复制用户权限请求连接Master服务器，并请求从指定Binlog 日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行 change master 命令时指定的)之后发送 Binlog 日志内容。

3、Master 服务器接收到来自 Slave 服务器的 IO 线程的请求后，Master 服务器上负责复制的IO线程根据 Slave服务器的IO 线程请求的信息读取指定Binlog日志文件指定位置之后的Binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了 Binlog 日志内容外，还有本次返回日志内容后在 Master 服务器端的新的 Binlog 文件名称以及在 Binlog中的下一个指定更新位置。

4、当 Slave 服务器的IO 线程获取到来自 Master 服务器上 10 线程发送日志内容及日志文件及位置点后，将Binlog日志内容依次写入到 Slave 端自身的 Relay Log(即中继日志)文件(MySQL-relay-bin.xxxxxx)的最末端，并将新的 Binlog 文件名和位置记录到master-info文件中，以便下一次读取Master端新Binlog日志时能够告诉 Master服务器需要从新 Binlog日志的哪个文件哪个位置开始请求新的Binlog 日志内容。。

5、Slave 服务器端的SQL线程会实时的检测本地RelayLog中新增加的日志内容，然后及时的把Log文件中的内容解析成在 Master 端曾经执行的 SQL 语句的内容，并在自身 Slave服务器上按语句的顺序执行应用这些SQL语句，应用完毕后清理应用过的日志。

6、经过了上面的过程，就可以确保在 Master 端和 Slave 端执行了同样的 SQL 语句。当复制状态正常的情况下，Master端和Slave端的数据是完全一样的，MySQL 的同步机制是有一些特殊的情况的，具体请参考官方的说明。

 

二、主从环境搭建

1、准备两台服务器：

主服务器：192.168.1.70；从服务器：192.168.1.71

分别安装mysql版本5.7。安装mysql5.7参考：https://www.cnblogs.com/zhangguosheng1121/p/14945963.html

注意：备库主机如果是从主库主机克隆的，需修改server_uuid：将原来的server-uuid先删掉，然后重新启动mysql会生成新的UUID

2、修改主从配置文件

主库：修改/etc/my.cnf文件，添加一下8行

[mysqld]
log-bin=mysql-bin
server-id=70
sync_binlog=1
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

从库：修改/etc/my.cnf文件，添加一下10行

[mysqld]
log-bin=mysql-bin
server-id=71
read_only=1
relay-log=relay-log
sync_binlog=1
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

3、重启主从库

service mysql restart

4、查看主库二进制文件是否开启

show global variables like '%log%';

查看主库二进制日志列表：show master logs;

查看主库的server id：show global variables like '%server%';

5、在主库上建立帐户并授权slave

GRANT REPLICATION SLAVE ON *.* to 'tesunet'@'%' identified by 'tesunet123'

注：tesunet：用户 tesunet123：密码

6、主库导出sql文件

mysqldump -uroot -ppassword -A --master-data=2 --single-transaction>/tmp/masterdb.sql

 7、sql文件拷贝到从库

下载上传方式或者scp方式把主库sql文件拷贝到从库

8、从库执行主库sql

mysql -uroot -ppassword </tmp/masterdb.sql

9 、查询主库状态：

show master status;

10、配置从库Slave

change master to master_host='192.168.106.70',master_user='tesunet',master_password='tesunet123',master_log_file='mysql-bin.000001',master_log_pos=321;

注意：master_log_file与master_log_pos的数值是在主库上查询master状态的数据：show master status;

11、启动从库复制

start slave;

12、检查从服务器复制功能状态

show slave status\G

参数解释：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event  # 这里显示了当前slave I/O线程的状态(slave连接到master的状态)。
                  Master_Host: 192.168.106.70                    # mysql主库的ip地址
                  Master_User: tesunet                           # 这个是master上面的一个用户。用来负责主从复制的用户，创建主从复制的时候建立的（具有reolication slave权限）。
                  Master_Port: 3306                              # master服务器的端口  一般是3306
                Connect_Retry: 60                                # 连接中断后，重新尝试连接的时间间隔。默认值是60秒。
              Master_Log_File: mysql-bin.000002                  # 当前I/O线程正在读取的主服务器二进制日志文件的名称。
          Read_Master_Log_Pos: 1925                              # 当前I/O线程正在读取的二进制日志的位置。
               Relay_Log_File: relay-log.000005                  # 当前slave SQL线程正在读取并执行的relay log的文件名。
                Relay_Log_Pos: 633                               # 当前slave SQL线程正在读取并执行的relay log文件中的位置；
        Relay_Master_Log_File: mysql-bin.000002                  # 当前slave SQL线程读取并执行的relay log的文件中多数近期事件，对应的主服务器二进制日志文件的名称。（说白点就是我SQL线程从relay日志中读取的正在执行的sql语句，对应主库的sql语句记录在主库的哪个binlog日志中）
             Slave_IO_Running: Yes    # I/O线程是否被启动并成功地连接到主服务器上。
            Slave_SQL_Running: Yes    # SQL线程是否被启动。
              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:        # slave的SQL线程读取日志参数的的错误数量和错误消息。错误数量为0并且消息为空字符串表示没有错误。
                 Skip_Counter: 0      # SQL_SLAVE_SKIP_COUNTER的值，用于设置跳过sql执行步数。
          Exec_Master_Log_Pos: 1925   # slave SQL线程当前执行的事件，对应在master相应的二进制日志中的position。
              Relay_Log_Space: 800    # 所有原有的中继日志结合起来的总大小。
              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       # 这个值是时间戳的差值。是slave当前的时间戳和master记录该事件时的时间戳的差值。
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0    
                Last_IO_Error:         # 最后一次I/O线程的错误号和错误消息。
               Last_SQL_Errno: 0
               Last_SQL_Error:         # 最后一次SQL线程的错误号和错误消息。
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 70      # 主库服务器id号
                  Master_UUID: 04025823-b390-11eb-806c-000c29856060     # 主库服务器的UUID
             Master_Info_File: /usr/local/mysql/data/master.info        # 从库中保存主库服务器相关的目录位置。
                    SQL_Delay: 0                                        # 一个非负整数，表示秒数，Slave滞后多少秒于master。
          SQL_Remaining_Delay: NULL         # 当 Slave_SQL_Running_State 等待，直到MASTER_DELAY秒后，Master执行的事件，此字段包含一个整数，表示有多少秒左右的延迟。在其他时候，这个字段是NULL。
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it   # SQL线程运行状态：线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。
           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
1 row in set (0.00 sec)

注意：

Slave_IO_Running: Yes 

Slave_SQL_Running: Yes 

以上两者都为Yes，表示主从环境搭建成功，那下边建表测试是否实现主从同步

 

三、主从测试（root权限登录）

现在在主库新建数据库：test_ms

然后刷新备库：

 

四、测试从库read_only

1、从库创建普通用户，设置权限

①创建普通用户，可以远程连接：create user 'test'@'%' identified by '123456';

②授权所有库，只能查询操作：grant select on *.* to test@'%'identified by '123456';

这是授予所有权限：grant all privileges on *.* to test@'%'identified by '123456';

③刷新权限：flush privileges;

④查询用户：select user,host from mysql.user;

2、用此普通账号登录数据库进行修改操作

 

五、停止主从关系

在从库上执行：

stop slave;
reset master;
reset slave all;

六、删除复制用户

drop user 'tesunet'@'%';
flush privileges;