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

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

（1）下载地址：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

[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

（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连接时出问题

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)

（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)

mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      155 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

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)