Windows mysql-8.0.11 master-slave 集群（一）

不念不忘少年蓝@ 2022-05-23 10:48 108阅读 0赞

一、下载安装  mysql-8.0.11-winx64.zip：

（1）下载地址：[http://dev.mysql.com/downloads/mysql/][http_dev.mysql.com_downloads_mysql]

（2）安装目录：D:\\Servers\\mysql-8.0.11-3306

（3）数据目录：D:\\Servers\\mysql-8.0.11-3306\\data

（4）配置：D:\\Servers\\mysql-8.0.11-3306\\my.ini

\#\# skip-grant-tables

（5）初始化

D:\\Servers\\mysql-8.0.11-3306\\bin>mysqld --initialize  
  
  
D:\\Servers\\mysql-8.0.11-3306\\bin>mysqld install mysql3306  
Service successfully installed.  
  
  
D:\\Servers\\mysql-8.0.11-3306\\bin>net start mysql3306  
mysql3306 服务正在启动 ..

mysql3306 服务已经启动成功。

（6）查找初始密码 D:\\Servers\\mysql-8.0.11-3306\\data\\xxxxxxxxx.err

2018-06-07T05:11:17.447259Z 0 \[Warning\] \[MY-010915\] \[Server\] 'NO\_ZERO\_DATE', 'NO\_ZERO\_IN\_DATE' and 'ERROR\_FOR\_DIVISION\_BY\_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.  
2018-06-07T05:11:17.447403Z 0 \[System\] \[MY-013169\] \[Server\] D:\\Servers\\mysql-8.0.11-3306\\bin\\mysqld.exe (mysqld 8.0.11) initializing of server in progress as process 17092  
2018-06-07T05:11:23.095862Z 5 \[Note\] \[MY-010454\] \[Server\] A temporary password is generated for root@localhost: ,8P:cZgV;MT1  
2018-06-07T05:11:26.582370Z 0 \[System\] \[MY-013170\] \[Server\] D:\\Servers\\mysql-8.0.11-3306\\bin\\mysqld.exe (mysqld 8.0.11) initializing of server has completed  
2018-06-07T05:12:56.687215Z 0 \[Warning\] \[MY-010915\] \[Server\] 'NO\_ZERO\_DATE', 'NO\_ZERO\_IN\_DATE' and 'ERROR\_FOR\_DIVISION\_BY\_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.  
2018-06-07T05:12:56.687330Z 0 \[System\] \[MY-010116\] \[Server\] D:\\Servers\\mysql-8.0.11-3306\\bin\\mysqld (mysqld 8.0.11) starting as process 15880  
2018-06-07T05:12:58.554830Z 0 \[Warning\] \[MY-010068\] \[Server\] CA certificate ca.pem is self signed.

2018-06-07T05:12:58.619985Z 0 \[System\] \[MY-010931\] \[Server\] D:\\Servers\\mysql-8.0.11-3306\\bin\\mysqld: ready for connections. Version: '8.0.11'  socket: ''  port: 3306  MySQL Community Server - GPL.

D:\\Servers\\mysql-8.0.11-3306\\bin>mysql -uroot -P 3306 -p,8P:cZgV;MT1  
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 9  
Server version: 8.0.11  
  
  
Copyright (c) 2000, 2018, 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.  
  
(7)登录  
mysql> set password=password('root');  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password('root')' at line 1  
mysql> set password='root';  
Query OK, 0 rows affected (0.09 sec)  
  
  
mysql> exit  
Bye  
  
（8）设置远程登录及  
D:\\Servers\\mysql-8.0.11-3306\\bin>mysql -uroot -P 3306 -proot  
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 10  
Server version: 8.0.11 MySQL Community Server - GPL  
  
  
Copyright (c) 2000, 2018, 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> use mysql;  
Database changed  
mysql> update user set host='%' where user='root';  
Query OK, 1 row affected (0.12 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
  
mysql> flush privileges;  
Query OK, 0 rows affected (0.02 sec)  
  
  
mysql> quit;

Bye

使用navicat连接时出问题

![70][]

D:\\Servers\\mysql-8.0.11-3306\\bin>mysql -uroot -P3306 -proot  
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 12  
Server version: 8.0.11 MySQL Community Server - GPL  
  
  
Copyright (c) 2000, 2018, 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'@'%' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER;  
Query OK, 0 rows affected (0.12 sec)  
  
  
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql\_native\_password BY 'root';  
Query OK, 0 rows affected (0.03 sec)

![70 1][]

（9）使用同样的方式安装 端口3307 3308上mysql

二、Slave Master集群

master: mysql3306

slave: mysql3307, mysql3308

\[client\]  
port = 3306  
default-character-set = utf8  
  
  
\[mysqld\]  
port = 3306  
character\_set\_server = utf8  
basedir =  D:\\Servers\\mysql-8.0.11-3306  
datadir =  D:\\Servers\\mysql-8.0.11-3306\\data  
sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
  
  
\#\# skip-grant-tables  
  
  
\[client\]  
port = 3306  
default-character-set = utf8  
  
  
\[mysqld\]  
port = 3306  
character\_set\_server = utf8  
basedir =  D:\\Servers\\mysql-8.0.11-3306  
datadir =  D:\\Servers\\mysql-8.0.11-3306\\data  
sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
  
  
\#\# skip-grant-tables  
  
  
\#\# master 配置   
log-bin = mysql-bin \#\# 开启二进制日志  
server-id = 1 \#\# 定义一个id  
binlog-do-db = test \#\# 数据库  
binlog-ignore-db = mysql \#\#

D:\\Servers\\mysql-8.0.11-3306\\bin>mysql -uroot -proot  
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 8  
Server version: 8.0.11 MySQL Community Server - GPL  
  
  
Copyright (c) 2000, 2018, 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> CREATE USER 'repuser'@'localhost' IDENTIFIED BY 'root';  
Query OK, 0 rows affected (0.02 sec)  
myswql  
  
mysql> GRANT REPLICATION SLAVE ON \*.\* TO 'repuser'@'localhost';  
Query OK, 0 rows affected (0.03 sec)  
  
  
mysql> flush privileges  
    -> ;

Query OK, 0 rows affected (0.02 sec)

1 row in set (0.00 sec)

\[client\]  
port = 3307  
default-character-set = utf8  
  
  
\[mysqld\]  
port = 3307  
character\_set\_server = utf8  
basedir =  D:\\Servers\\mysql-8.0.11-3307  
datadir =  D:\\Servers\\mysql-8.0.11-3307\\data  
sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
  
  
\#\# skip-grant-tables  
  
  
\#\# Slave 配置

server-id = 2 \#\# 定义一个id

D:\\Servers\\mysql-8.0.11-3306\\bin>mysql -uroot -P3307 -proot  
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 8  
Server version: 8.0.11 MySQL Community Server - GPL  
  
  
Copyright (c) 2000, 2018, 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>  
mysql> CHANGE MASTER TO  
    -> MASTER\_HOST='localhost',  
    -> MASTER\_USER='repuser',  
    -> MASTER\_PASSWORD='root',  
    -> MASTER\_LOG\_FILE='mysql-bin.000004',  
    -> MASTER\_LOG\_POS=155;  
Query OK, 0 rows affected, 1 warning (0.23 sec)

[http_dev.mysql.com_downloads_mysql]: http://dev.mysql.com/downloads/mysql/
[70]: /images/20220523/ce8c3ad4754e4e879dc870dfe600a422.png
[70 1]: /images/20220523/f12e0a5037344d6f8aaac4a8c45fc65f.png