MySQL主从复制配置案例

最新推荐文章于 2024-05-10 10:03:22 发布

王野也不野

最新推荐文章于 2024-05-10 10:03:22 发布

阅读量351

点赞数

分类专栏： DB 文章标签： mysql 数据库 dba

本文链接：https://blog.csdn.net/weixin_45994575/article/details/120962138

版权

DB 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1、配置Master

修改mysql配置文件my.cnf

vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

重启mysql，查看server_id

show variables like 'server_id';

## 如果没有生效手动设置
set global server_id=2;

创建slave复制binlog所用的用户

CREATE USER 'repl'@'192.168.199.198' IDENTIFIED WITH mysql_native_password BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

2、配置Slave

修改my.cnf配置文件，操作同上，注意server_id不能和master相同
设置主节点参数

CHANGE MASTER TO
MASTER_HOST='ip',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=1024;

启动slave

start slave;

# 查看slave状态
show slave status\G

3、slave状态信息详情介绍

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.44.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1627
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: No
            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: 1627
              Relay_Log_Space: 156
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13117
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 211025 06:49:23
     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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

指标	注释
Slave_IO_State: Waiting for master to send event	表示IO线程的状态
Master_Host: 192.168.50.3	Master的IP地址
Master_User: repl	主从复制的用户
Master_Port: 3306	Master的端口
Connect_Retry: 60	IO thread重连次数
Master_Log_File: mysql-bin.000001	IO thread正在读取的master上的binlog文件
Read_Master_Log_Pos: 728	正在读取的master上的binlog文件位置
Relay_Log_File: relay-bin.000002	存放的本地relay log文件名
Relay_Log_Pos: 941	存放的本地relay log位置
Relay_Master_Log_File: mysql-bin.000001	SQL线程正在执行的master binlog file
Slave_IO_Running: Yes	IO thread状态
Slave_SQL_Running: Yes	SQL thread状态
Replicate_Do_DB:	指定复制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: 728	执行masterbinlog的位置
Relay_Log_Space: 1142	所有relay log字节数总和
Until_Condition: None	指定复制条件
Until_Log_File:	指定复制到某个文件
Until_Log_Pos: 0	指定复制到某个位置
Seconds_Behind_Master: 0	从库落后主库时间，单位秒
Last_IO_Errno: 0	最近一次IO thread错误
Last_IO_Error	最近一次IO thread错误详细信息
Last_SQL_Errno: 0	最近一次sql thread错误
Last_SQL_Error:	最近一次sql thread错误详细信息
Replicate_Ignore_Server_Ids:	复制忽略server_id为xxx的实例
Master_Server_Id: 1003306	master server_id
Master_UUID: dff47046-3ad5-11e8-86fe-080027de0e0e	master uuid
Master_Info_File: /storage/mysql/data/master.info	记录master信息文件
SQL_Delay: 0	sql延迟多少时间，延迟复制会用到
SQL_Remaining_Delay: NULL	当sql thread状态为Waiting until MASTER_DELAY seconds after master executed event，那么该值表示剩下延迟多少时间
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates	sql thread状态
Master_Retry_Count: 86400	主从复制断开时slave最多能尝试重新连接的次数
Master_Bind:	绑定网卡
Last_IO_Error_Timestamp:	io thread 最近一次的错误时间
Last_SQL_Error_Timestamp:	sql thread 最近一次的错误时间
Retrieved_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3	IO thread获取到的GTID
Executed_Gtid_Set: dff47046-3ad5-11e8-86fe-080027de0e0e:1-3	SQL thread执行的GTID
Auto_Position: 1	使用auto_position，建议还是开启比较方便
Replicate_Rewrite_DB:	复制DB对应关系
Channel_Name:	多源复制的channel name

王野也不野

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL主从复制配置案例

1、配置Master修改mysql配置文件my.cnfvi /etc/my.cnf[mysqld]log-bin=mysql-binserver-id=1重启mysql，查看server_idshow variables like 'server_id';## 如果没有生效手动设置set global server_id=2;创建slave复制binlog所用的用户CREATE USER 'repl'@'192.168.199.198' IDENTIFIED WIT.
复制链接

扫一扫