Windows下Mysql主从配置

Windows下MySQL的主从配置

准备

主master：127.0.0.1:53306

从slave：127.0.0.1:53307

版本：mysql-5.7.31

前置

• 在主库生成一个用来进行主从同步的账号

  GRANT REPLICATION SLAVE ON *.* to 'test'@'127.0.0.1' identified by '123456';
  flush privileges;
  

  在这里就是生成的一个test的用户，密码为123456

配置

1. 配置主库：

   1. 修改主库的配置文件my.ini

      [mysqld]
      #主库的server_id
      server-id=1
      #开启二进制日志
      log-bin=mysql-bin
      #需要同步的库，可以有多个，但不能写成 testdb,testdb1形式
      binlog-do-db=testdb
      #binlog-do-db=testdb1
      #不需要同步的数据库，与binlog-do-db二选一即可
      #binlog-ignore-db=mysql
      #binlog-ignore-db=testdb2
      #--------------------------
      ###基础配置
      ##skip-grant-tables=1
      port = 53306
      basedir=D:\mysql-5.7.31
      datadir=D:\mysql-5.7.31\data
      max_connections=200
      character-set-server=utf8
      default-storage-engine=INNODB
      sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
      [mysql]
      default-character-set=utf8
      

      这里有个问题要注意的就是server-id这些主从的配置需要放在最前面，否则会报错

   2. 重启mysql服务

      net stop mysql53306		  	//停止主库mysql服务
      net start mysql53306		//开启主库mysql服务
      

   3. 查询主服务器的日志名和当前的偏移量

      show master status
      +------------------+----------+--------------+------------------+-------------------+
      | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
      +------------------+----------+--------------+------------------+-------------------+
      | mysql-bin.000002 |      154 |              |                  |                   |
      +------------------+----------+--------------+------------------+-------------------+
      

2. 配置从库

   1. 修改从库的配置文件my.ini

      [mysqld]
      #从库的server-id一般是以从库最后一个ip的数字来定，这里只是不同端口号，所以我用2
      server_id=2
      #开启二进制文件
      log-bin=mysql-bin
      #------------------------------------
      ##基础配置
      ##skip-grant-tables=1
      port = 53307
      basedir=D:\mysql-5.7.31-1
      datadir=D:\mysql-5.7.31-1\data
      max_connections=200
      character-set-server=utf8
      default-storage-engine=INNODB
      sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
      [mysql]
      default-character-set=utf8
      

   2. 与主库建立关联关系

      change master to 
      	master_host='127.0.0.1', 
      	master_port=53306, 
      	master_user='test',
      	master_password='123456', 
      	master_log_file='mysql-bin.000002',
          master_log_pos=154;
      

   3. 启动从库的进程

      mysql> start slave;
      Query OK, 0 rows affected (0.09 sec)
      

   4. 查看从库的状态

      mysql> show slave status \G;
      *************************** 1. row ***************************
                     Slave_IO_State: Waiting for master to send event
                        Master_Host: 127.0.0.1
                        Master_User: test
                        Master_Port: 53306
                      Connect_Retry: 60
                    Master_Log_File: mysql-bin.000002
                Read_Master_Log_Pos: 154
                     Relay_Log_File: admin-PC-relay-bin.000002
                      Relay_Log_Pos: 320
              Relay_Master_Log_File: mysql-bin.000002
                   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: 154
                    Relay_Log_Space: 530
                    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: 254a65d6-139e-11eb-8ca7-f0761c0a8ffb
                   Master_Info_File: D:\mysql-5.7.31-1\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)
      
      ERROR:
      No query specified
      

      当Slave_IO_Running: Yes与Slave_SQL_Running: Yes当2个都为yes的时候，即代表主从同步配置已经完成。

   出现的问题记录

   • 未将server-id等主从配置放置最前面

     出现Undefined variable server-id

   • 在与主库建立连接的之后，后面出现错误，比如slave_io_running:no 或者 slave_sql_runing:no，就需要重置连接

     mysql > stop slave;		//停止slave服务
     mysql > reset slave;	//重置slave
     
     ###主库操作
     mysql > flush logs;		//重新生成mmysql-bin.000003，文件名数字往后面加1
     mysql > show master status;	//重新查看主库的状态
     
     ###从库--重新建立连接
     mysql > change master to 
     	master_host='127.0.0.1', 
     	master_port=53306, 
     	master_user='test',
     	master_password='123456', 
     	master_log_file='mysql-bin.000003',
         master_log_pos=154;