Windows环境下部署MySQL主从并模拟升级到8.0

http://ftp.kaist.ac.kr/mysql/Downloads/MySQL-5.7/mysql-5.7.35-winx64.zip
http://ftp.kaist.ac.kr/mysql/Downloads/MySQL-8.0/mysql-8.0.26-winx64.zip
1.
2.

C:\Users\Administrator>D:\test\mysql-5.7.35-winx64\bin\mysqld --initialize --console
2022-01-20T03:37:59.769162Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-01-20T03:38:03.808644Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-01-20T03:38:03.901775Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-01-20T03:38:04.079914Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5fd0428e-79a2-11ec-b336-00155d9ebf27.
2022-01-20T03:38:04.092275Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-01-20T03:38:05.824394Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-01-20T03:38:05.824844Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-01-20T03:38:05.826505Z 0 [Warning] CA certificate ca.pem is self signed.
2022-01-20T03:38:07.018691Z 1 [Note] A temporary password is generated for root@localhost: pE.Yu96:XQEp
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

[mysqld]
port=3306
character_set_server=utf8
basedir=D:/test/mysql-5.7.35-winx64
datadir=D:/test/data3306
server-id=1
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
innodb_file_per_table = 1
log_timestamps=SYSTEM

log-error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 5
log-bin = binlog
binlog_format = row
expire_logs_days = 15
log_bin_trust_function_creators = 1

[client]   
default-character-set=utf8
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

C:\Users\Administrator>D:\test\mysql-5.7.35-winx64\bin\mysqld --install mysql3306 --defaults-file=D:\test\data3306\my.ini
Service successfully installed.
1.
2.

C:\Users\Administrator>net start mysql3306
mysql3306 服务正在启动 .
mysql3306 服务已经启动成功。
1.
2.
3.

D:\test\mysql-5.7.35-winx64\bin\mysqld --initialize --console
2022-01-20T05:38:55.054718Z 1 [Note] A temporary password is generated for root@localhost: E)+H2UUpeJRf
D:\test\mysql-5.7.35-winx64\bin\mysqld --install mysql3307 --defaults-file=D:\test\data3307\my.ini
mysql3307 服务正在启动 .
mysql3307 服务已经启动成功。
1.
2.
3.
4.
5.

C:\Users\Administrator>cd /d d:\test\mysql-5.7.35-winx64\bin
d:\test\mysql-5.7.35-winx64\bin>mysql -uroot -ppE.Yu96:XQEp
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35-log
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 'GreatSQL';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# 然后重新登陆一下
d:\test\mysql-5.7.35-winx64\bin>mysql -uroot -p"GreatSQL" -P3307
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

mysql> create table t1(id int(1) not null auto_increment , name varchar(64),primary key(id));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(1,'GreatSQL');
Query OK, 1 row affected (0.01 sec)
1.
2.
3.
4.
5.

d:\test\mysql-5.7.35-winx64\bin>mysqldump.exe -uroot -pGreatSQL -P3306 --single-transaction --master_data=2 --set-gtid-purged=OFF --databases test > d:\test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

d:\test\mysql-5.7.35-winx64\bin>mysql -uroot -pGreatSQL -P3307 < d:\test.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
1.
2.
3.
4.
5.

CREATE USER `sync`@`127.0.0.1` IDENTIFIED BY 'sync';
GRANT Replication Slave ON *.* TO `sync`@`127.0.0.1`;
flush privileges;
1.
2.
3.

d:\test\mysql-5.7.35-winx64\bin>more d:\test.sql
-- MySQL dump 10.13  Distrib 5.7.35, for Win64 (x86_64)
......
-- Current Database: `test`
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1350;
1.
2.
3.
4.
5.

change master to master_host="127.0.0.1",master_port=3306,MASTER_USER='sync',MASTER_PASSWORD='sync',MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1350;
start slave; 
1.
2.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1959
               Relay_Log_File: YOS-DAAJTV9J5RF-relay-bin.000003
                Relay_Log_Pos: 926
        Relay_Master_Log_File: binlog.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: 1959
              Relay_Log_Space: 1143
              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: 5fd0428e-79a2-11ec-b336-00155d9ebf27
             Master_Info_File: D:\test\data3307\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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.

d:\test\mysql-5.7.35-winx64\bin>D:\test\mysql-8.0.26-winx64\bin\mysqld --install
mysql8_3307 --defaults-file=D:\test\data3306\my.ini
Service successfully installed.
1.
2.
3.

d:\test\mysql-5.7.35-winx64\bin>net stop mysql3307
mysql3307 服务正在停止.
mysql3307 服务已成功停止。
1.
2.
3.

[mysqld]
port=3307
character_set_server=utf8
basedir=D:/test/mysql-8.0.26-winx64  #路径改了
datadir=D:/test/data3307   
server-id=2
sql_mode=NO_ENGINE_SUBSTITUTION #sql_mode 改了
lower_case_table_names=1
innodb_file_per_table = 1
log_timestamps=SYSTEM

log-error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 5
log-bin = binlog
binlog_format = row
expire_logs_days = 15
log_bin_trust_function_creators = 1

[client]   
default-character-set=utf8
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

net start mysql8_3307
1.

2022-01-20T17:33:33.598491+08:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.2022-01-20T17:33:37.652733+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80026' started.2022-01-20T17:33:53.602051+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80026' completed.
1.