windows下mysql单向主从同步配置

配置分为两部分，主服务器部分，从服务器部分

本次测试的两台机器的主从同步，环境是windows(linux同理) 

主服务器ip:192.168.1.199

从服务器ip:192.168.1.198

1）主服务器端的配置

找到MySQL的配置文件my.ini文件

在[mysqld]内开启日志功能

log-bin=mylog-bin名字可以任意取

server-id=199本次去的ip最后三个数字

此时重启主服务器数据库服务

net stop mysql停止mysql服务

net start mysql开启mysql服务

然后进入数据库

执行如下命令

mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'admin';//此处必须大写，

mysql>show master status;//查看主服务器日志文件状态

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      439 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

　　从服务器需要使用Position的值

到这里，主服务器的配置就算完成了。

 

2）从服务器的配置

同样需要修改my.ini中的值

在[mysqld]下修改

server-id=198

从服务器的日志文件不是必须开启，如果配置的是双向同步的话，此出的log-bin需要配置

重启从服务器的myql服务

登录从服务器，执行以下命令

mysql>change master to master_host='192.168.145.199',master_user='mysync',master_password='admin',master_log_file='mysql-bin.000001',master_log_pos=439;

mysql>show slave status\G;//检查从服务器复制功能的状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.199 //主服务器地址
                  Master_User: mysync //主服务器创建的同步帐号
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001 //同步的日志文件
          Read_Master_Log_Pos: 935
               Relay_Log_File: PC05-relay.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001 
             Slave_IO_Running: Yes //此处必须是yes
            Slave_SQL_Running: Yes //此处必须是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: 935
              Relay_Log_Space: 1184
              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: 199
                  Master_UUID: 516cb325-a148-11e9-b3df-02004c4f4f50
             Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\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)

　　

到这里从服务器的配置也完成了，剩下就是创建数据库和表进行测试了。

3）测试

主服务器创建数据库

mysql>create database scrb;

mysql> create database scrb;
Query OK, 1 row affected (0.01 sec)

创建表

mysql> create table user(id int(3),name varchar(15));
Query OK, 0 rows affected (0.02 sec)

从服务器查看同步过来没有

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysync             |
| performance_schema |
| scrb               |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

查看表

mysql> show tables;
+----------------+
| Tables_in_scrb |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

可以看见，已经成了。

 

到此windows下的单向主从同步配置已经完成了。

如果想关掉主从同步，只需要在从服务端执行一下命令即可

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.199
                  Master_User: mysync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1278
               Relay_Log_File: PC05-relay.000003
                Relay_Log_Pos: 663
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No //已经关闭同步复制功能
            Slave_SQL_Running: No
              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: 1278
              Relay_Log_Space: 1527
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 199
                  Master_UUID: 516cb325-a148-11e9-b3df-02004c4f4f50
             Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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)

　　

 可以对比上边的开始状态，可知已经关闭了。

 

转载于:https://www.cnblogs.com/yjp372928571/p/11328177.html