工程师计划 数据库 mysql主从配置

mysql主从配置

前提

两台主机以安装mysql

ip :192.168.255.10 (主节点)

ip :192.168.255.12 (从节点)

主节点操作:

修改my.cnf

[mysqld]
#主数据库端ID号
server_id = 1
# #开启二进制日志                  
log-bin = binlog

重启mysql

systemctl restart mysql

给用户权限用于主从同步数据

mysql>CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
mysql>flush privileges;
# %:所有服务器都能使用这个用户(也可指定IP)

查看file名称和Position

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     2680 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从节点操作:

修改my.cnf

[mysqld]
## 唯一(不要与主的重复了)
server-id=2

重启mysql

systemctl restart mysql

配置slave

CHANGE MASTER TO MASTER_HOST='192.168.255.12',MASTER_USER='slave',MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000001',MASTER_LOG_POS=2680;

启动slave

start slave;

查看从节点状态信息

mysql> show slave status\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.255.10
                Master_User: backup
                Master_Port: 3306
               Connect_Retry: 60
              Master_Log_File: binlog.000001
            Read_Master_Log_Pos: 1719
              Relay_Log_File: controller-relay-bin.000003
               Relay_Log_Pos: 317
        Relay_Master_Log_File: binlog.000001
             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: 1719
             Relay_Log_Space: 529
             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: 5ed203ba-9ac6-11ec-b19d-000c29b3329c
             Master_Info_File: /usr/local/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)

至此配置成功

本文来自网络,不代表本网站立场,转载请注明出处。http://loadingok.com/2373.html
© 2021 京ICP备2021027871号-1
联系我们

联系我们

18513870113

在线咨询: QQ交谈

邮箱: 1140136143@qq.com

工作时间:周一至周五,9:00-17:30,节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部