One way master-slave synchronous configuration of mysql under windows

Keywords: Database MySQL Windows Linux

;

View mysql profile address

mysql --help|grep 'my.cnf'

ps aux|grep mysql|grep 'my.cnf'

The configuration is divided into two parts, the master server part and the slave server part

In this test, the master and slave of the two machines are synchronized, and the environment is windows (the same as Linux)

Main server ip:192.168.1.199

Slave server ip:192.168.1.198

1) Configuration of the primary server

Find the configuration file for MySQL my.ini file

Turn on the logging function in [mysqld]

Log bin = mylog bin name can be arbitrarily selected

Server id = 199 the last three digits of the current ip

Restart the main server database service at this time

net stop mysql stop mysql service

net start mysql open mysql service

Then enter the database

Execute the following command


mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'admin';//Must be capitalized here,

mysql>show master status;//View primary server log file status
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      439 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

The value of Position is required from the server

At this point, the configuration of the primary server is complete.

2) Configuration from server

It also needs to be modified my.ini Value in

Modify under [mysqld]

server-id=198

The log file of the slave server does not have to be opened. If it is configured for bidirectional synchronization, the log bin output needs to be configured

Restart the myql service from the server

Log in from the server and execute the following command

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; / / check the status of the copy function from the server

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.199 //Primary server address
                  Master_User: mysync //Synchronization account created by the master server
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001 //Synchronized log files
          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 //Must be yes here
            Slave_SQL_Running: Yes //Must be yes here
              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)

So far, the configuration of the server has been completed. The rest is to create the database and table for testing.

3) Testing

Master server create database

mysql>create database scrb;

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

Create table

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

Check the synchronization from the server

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

View table

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

As you can see, it has become.

The one-way master-slave synchronization configuration under this windows has been completed.

If you want to turn off master-slave synchronization, just execute the following command on the slave server

stop slave: turn off the synchronization function of slave server

start slave: enable the synchronization function of slave server

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 //Synchronous replication has been turned off
            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)

It can be compared with the start state above, and it can be seen that it has been closed.

Posted by busnut on Sun, 07 Jun 2020 19:52:27 -0700