Advantages of MySql replication:

1. If there is a problem with the primary server, you can quickly switch to the services provided by the slave server.
2. Query operations can be performed from the slave server to reduce the access pressure of the primary server.
3. Backup can be performed from the server to avoid affecting the service of the primary server during backup

Note: Generally, only the data that are not updated frequently or that are not required to be real-time can be queried from the server. The data with high real-time requirements still need to be obtained from the main server.

1. Test environment

The configuration settings also apply to Centos, Centos configuration file / etc/my.cnf

service mysqld stop #Stop the database
service mysqld start #Start the database 
service mysqld restart #clear shutdown and startup

2. Database Installation

Reference can be made to previous articles: Install MySql and modify the initial password

Here's a log of Slave database installation

Microsoft Windows [Edition 6.1.7601]
//Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Windows\system32>cd C:\Program Files\mysql-5.7.18-win32-slave\bin

C:\Program Files\mysql-5.7.18-win32-slave\bin>mysqld  --initialize

C:\Program Files\mysql-5.7.18-win32-slave\bin>cd  ../data

C:\Program Files\mysql-5.7.18-win32-slave\data>TYPE Javen-PC.err
2017-06-29T02:41:51.068120Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is
 deprecated. Please use --explicit_defaults_for_timestamp server option (see doc
umentation for more details).
2017-06-29T02:41:51.573560Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-06-29T02:41:51.643760Z 0 [Warning] InnoDB: Creating foreign key constraint
system tables.
2017-06-29T02:41:51.699920Z 0 [Warning] No existing UUID has been found, so we a
ssume that this is the first time that this server has been started. Generating
a new UUID: 818f5c2f-5c74-11e7-8dff-000c29b2597f.
2017-06-29T02:41:51.699920Z 0 [Warning] Gtid table is not ready to be used. Tabl
e 'mysql.gtid_executed' cannot be opened.
2017-06-29T02:41:51.713960Z 1 [Note] A temporary password is generated for root@
localhost: =rc%=eBVg0AY

C:\Program Files\mysql-5.7.18-win32-slave\data>cd ..

C:\Program Files\mysql-5.7.18-win32-slave>cd bin

C:\Program Files\mysql-5.7.18-win32-slave\bin>mysqld -install MySQL2
Service successfully installed.

C:\Program Files\mysql-5.7.18-win32-slave\bin>net start MySQL2
MySQL2 The service is starting .
MySQL2 The service has been launched successfully.

C:\Program Files\mysql-5.7.18-win32-slave\bin>mysql -u root -p=rc%=eBVg0AY -P3308

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT
 OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH  PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

1. Create my.ini initialization configuration file in mysql-5.7.18-win32-slave root directory

[mysqld]
# set basedir to your installation path
basedir=C:\\Program Files\\mysql-5.7.18-win32-slave
# set datadir to the location of your data directory
datadir=C:\\Program Files\\mysql-5.7.18-win32-slave\\data
port = 3308

2. Initialize the database file by entering the bin directory mysqld-initialize
3. TYPE Javen-PC.err View Processing Initialization Password. Javen-PC.err is the name of your computer. The initialization password here is = rc%=eBVg0AY.
4. Register mysql service mysqld-install mysql2
5. Start the service net start MySQL2
6. Log on to local mysql: mysql-u root-p=rc%=eBVg0AY-P3308
7. Modify the local root user password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

8. Authorize remote login (need to close firewall or configure specified ports to be accessible)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH  PRIVILEGES;

3. Configuring Master

3.1 Setting Authorized Users to Slave Servers (Creating Duplicate Accounts)

Set up an account javen, and only allow login from 192.168.1.103, password 123456.

mysql> grant replication slave on *.* to 'javen'@'192.168.1.103' identified by '123456';
mysql> flush privileges;

3.2 Master configuration for primary server

[mysqld]
# set basedir to your installation path
basedir=C:\\Program Files\\mysql-5.7.18-win32
# set datadir to the location of your data directory
datadir=C:\\Program Files\\mysql-5.7.18-win32\\data
port = 3306

log-bin           = mysql-bin   #[Must] Enable binary logging
server-id         = 1           #[Must] Server unique ID, default 1
expire-logs-days  = 7           #Keep only 7 days of binary logs in case the disk is full of logs
#replicate-do-db   = test        #The name of the database that needs to be replicated; there is no database that configures backup only.
binlog-ignore-db  = mysql       #Non-backup database
binlog-ignore-db  = information_schema
binlog-ignore-db  = performation_schema
binlog-ignore-db  = sys
#binlog-do-db=test #The name of the database that needs to be replicated

3.3 Restart MySQL Service and Set Read Lock

net stop MySQL
net start MySQL

Setting the read lock on the primary server is valid to ensure that there is no database operation in order to get a consistent snapshot

mysql -u root -proot -P3306
mysql> flush tables with read lock;

3.4 View the current binary log name and offset value on the primary server

mysql> show master status;
+------------------+----------+--------------+----------------------------------
----------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB
                | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------
----------------+-------------------+
| mysql-bin.000001 |     2519 |              | mysql,information_schema,performa
tion_schema,sys |                   |
+------------------+----------+--------------+----------------------------------
----------------+-------------------+
1 row in set (0.00 sec)

File and Position are used when configuring Salve, Binlog_Do_DB represents the database to be synchronized, Binlog_Ignore_DB represents the database of Ignore, which are specified when configuring.

Additionally, if this step is always Empty set(0.00 sec), then my.init is not configured correctly.

4. Configuring Slave from Server

4.1 Modify the configuration from the database

[mysqld]
# set basedir to your installation path
basedir=C:\\Program Files\\mysql-5.7.18-win32-slave
# set datadir to the location of your data directory
datadir=C:\\Program Files\\mysql-5.7.18-win32-slave\\data
port = 3308


log-bin=mysql-bin
server-id=3
binlog-ignore-db  = mysql       #Non-backup database
binlog-ignore-db  = information_schema
binlog-ignore-db  = performation_schema
binlog-ignore-db  = sys
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

4.2 Restart slave database and set Slave database

net stop MySQL2
net start MySQL2

Log in from the database and do the following settings

mysql> stop slave;  #Close Slave
mysql> change master to master_host='192.168.1.103',master_user='javen',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos= 2519;

mysql> start slave;  #Open Slave

Note: Master information is specified here. master_log_file is the File option when configuring Master. master_log_pos is the Postion option when configuring Master. Here we need to correspond.

4.3 View Slave configuration information

show slave status to view configuration information:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.103
                  Master_User: mysync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1192
               Relay_Log_File: Javen-PC-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           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: 1192
              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: 818f5c2f-5c74-11e7-8dff-000c29b2597f
             Master_Info_File: C:\Program Files\mysql-5.7.18-win32-slave\data\ma
ster.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
dates
           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

mysql>

5. Close the read lock of the main database

mysql> unlock tables;

6. Create a table in the main database and add data tests

If you have any questions, please leave a message. .