关于mysql:Windows环境下部署MySQL主从并模拟升级到80

44次阅读

共计 7164 个字符，预计需要花费 18 分钟才能阅读完成。

GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。

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

记录下明码

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

初始化数据如下图，默认是在安装程序的 data 目录上面

把初始化的数据拷贝到自定义的数据目录。
增加下 my.ini 配置

[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

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

C:\Users\Administrator>net start mysql3306
mysql3306 服务正在启动 .
mysql3306 服务曾经启动胜利。

——————————– 以上装置了一个 3306，依葫芦画瓢再装置一个 3307———————————-

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 服务曾经启动胜利。

查看服务信息

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

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)

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.

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

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;

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;

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

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.

d:\test\mysql-5.7.35-winx64\bin>net stop mysql3307
mysql3307 服务正在进行.
mysql3307 服务已胜利进行。

[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

net start mysql8_3307

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.

Enjoy GreatSQL :)

GreatSQL 季报（2021.12.26）
https://mp.weixin.qq.com/s/FZ…

技术分享 |sysbench 压测工具用法浅析
https://mp.weixin.qq.com/s/m1…

故障剖析 | linux 磁盘 io 利用率高，剖析的正确姿态
https://mp.weixin.qq.com/s/7c…

技术分享 | 闪回在 MySQL 中的实现和改良
https://mp.weixin.qq.com/s/6j…

万答 #20，索引下推如何进行数据过滤
https://mp.weixin.qq.com/s/pt…

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

Bilibili：
https://space.bilibili.com/13…

微信 &QQ 群：
可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

正文完

mysql

发表至： mysql

2022-02-10

0

关于mysql:技术同学必会的-MySQL-设计规约都是惨痛的教训

关于mysql:Rust简化版MybatisPlus-让你一天从Java转业Rust

关于mysql:记一次线上mysql死锁

关于mysql:mysql时区问题

关于javascript:zTree-jQuery-树插件

关于mysql:Windows环境下部署MySQL主从并模拟升级到80

一、部署实例

1. 下载 2 个软件

2. 解压一下

3. 初始化

4. 装置服务

5. 开启

6. 批改明码

二、建设主从

1. 主节点建表等

2. 迁徙数据

3. 主节点赋权

4. 看下 dump 文件的 pos 值

5. 从节点建设同步

6. 实现同步

三、降级 8.0

1. 署 mysql8

2. 停掉原来的

3. 批改下配置

4. 开启服务，会主动降级

5. 查看日志降级胜利了

文章举荐：

对于 GreatSQL

Just My Socks（注册教程内含优惠码）

关于mysql:Windows环境下部署MySQL主从并模拟升级到80

一、部署实例

1. 下载 2 个软件

2. 解压一下

3. 初始化

4. 装置服务

5. 开启

6. 批改明码

二、建设主从

1. 主节点建表等

2. 迁徙数据

3. 主节点赋权

4. 看下 dump 文件的 pos 值

5. 从节点建设同步

6. 实现同步

三、降级 8.0

1. 署 mysql8

2. 停掉原来的

3. 批改下配置

4. 开启服务，会主动降级

5. 查看日志降级胜利了

文章举荐：

对于 GreatSQL

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）