初始化数据库（记住控制台上显示的初始密码，在最后一行的尾部）
mysqld –initialize –console –basedir=C:\\Lab\\MySQL\\innodb-cluster\\node-01 –datadir=C:\\Lab\\MySQL\\innodb-cluster\\node-01\\data
mysqld –initialize –console –basedir=C:\\Lab\\MySQL\\innodb-cluster\\node-02 –datadir=C:\\Lab\\MySQL\\innodb-cluster\\node-02\\data
mysqld –initialize –console –basedir=C:\\Lab\\MySQL\\innodb-cluster\\node-03 –datadir=C:\\Lab\\MySQL\\innodb-cluster\\node-03\\data
启动数据库
mysqld –defaults-file=”C:\\Lab\\MySQL\\innodb-cluster\\my-01.ini”
mysqld –defaults-file=”C:\\Lab\\MySQL\\innodb-cluster\\my-02.ini”
mysqld –defaults-file=”C:\\Lab\\MySQL\\innodb-cluster\\my-03.ini”
修改密码（问号处是上面的初始密码）
mysqladmin -uroot -P3721 -p password 12345678
mysqladmin -uroot -P3722 -p password 12345678
mysqladmin -uroot -P3723 -p password 12345678
客户端登录
mysql -P3721 -uroot -p12345678
mysql -P3722 -uroot -p12345678
mysql -P3723 -uroot -p12345678
/******************************************************************
* SQL操作部分，创建管理帐户和权限
******************************************************************/
======创建管理帐户 3721
登录3721数据库
mysql> select user,host from mysql.user;
mysql> create user clusteradmin@’localhost’ IDENTIFIED BY ‘12345678’;
mysql> GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT DELETE, INSERT, UPDATE ON mysql.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
======创建管理帐户 3722
登录3722数据库
mysql> create user clusteradmin@’localhost’ IDENTIFIED BY ‘12345678’;
mysql> GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT DELETE, INSERT, UPDATE ON mysql.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
======创建管理帐户 3723
登录3723数据库
mysql> create user clusteradmin@’localhost’ IDENTIFIED BY ‘12345678’;
mysql> GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT DELETE, INSERT, UPDATE ON mysql.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO ‘clusteradmin’@’localhost’ WITH GRANT OPTION;
/******************************************************************
* mysqlsh操作部分，创建集群
******************************************************************/
======主节点创建集群并初始化
C:\Users\hello>mysqlsh
MySQL JS > shell.connect(‘clusteradmin@localhost:3721’);
MySQL localhost:3721 ssl JS > dba.configureLocalInstance();
MySQL localhost:3721 ssl JS > dba.checkInstanceConfiguration(“clusteradmin@localhost:3721”);
MySQL localhost:3721 ssl JS > var cluster = dba.createCluster(‘myCluster’);
MySQL localhost:3721 ssl JS > cluster.status();
======从节点node2初始化
MySQL localhost:3721 ssl JS > shell.connect(‘clusteradmin@localhost:3722’);
MySQL localhost:3722 ssl JS > dba.configureLocalInstance();
======从节点node3初始化
MySQL localhost:3722 ssl JS > shell.connect(‘clusteradmin@localhost:3723’);
MySQL localhost:3723 ssl JS > dba.configureLocalInstance();
======转到主节点node1创建集群
MySQL localhost:3723 ssl JS > shell.connect(‘clusteradmin@localhost:3721’);
MySQL localhost:3721 ssl JS > var cluster = dba.getCluster(‘myCluster’);
MySQL localhost:3721 ssl JS > cluster.status();
===添加从节点node2
MySQL localhost:3721 ssl JS > cluster.addInstance(‘clusteradmin@localhost:3722’);
MySQL localhost:3721 ssl JS > cluster.status();
===添加从节点node3
MySQL localhost:3721 ssl JS > cluster.addInstance(‘clusteradmin@localhost:3723’);
MySQL localhost:3721 ssl JS > cluster.status();
/******************************************************************
* mysqlsh操作部分，测试验证
******************************************************************/
C:\Users\hello>mysqlsh
MySQL JS > shell.connect(‘clusteradmin@localhost:3721’);
MySQL localhost:3721 ssl JS > var cluster = dba.getCluster(‘myCluster’);
MySQL localhost:3721 ssl JS > cluster.status();
MySQL localhost:3721 ssl JS > cluster.options().defaultReplicaSet.topology
/******************************************************************
* mysqlrouter
******************************************************************/
在指定位置创建mysqlrouter目录
# mysqlrouter –bootstrap root@localhost:3721 –directory=/data/mysqlrouter/ –conf-use-sockets –user=msyql –force //*unix系统
# 初始化（windows系统）
mysqlrouter –bootstrap root@localhost:3721 –directory=C:\\Lab\MySQL\\innodb-cluster\\mysqlrouter –force
# 启动
mysqlrouter -c C:/Lab/MySQL/innodb-cluster/mysqlrouter/mysqlrouter.conf
[读写端口]
## 创建新root（感觉就是给原来的root赋权限）
mysql -uroot -p -P3721
mysql> create user clusteruser@’%’ identified by ‘12345678’;
mysql> grant all on *.* to clusteruser@’%’;
## 用clusteruser登录集群的读写端口
mysql -hlocalhost -P6446 -uclusteruser -p12345678
## 各种读写操作
create database demo;
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, email) VALUES (‘john.doe’, ‘john.doe@example.com’);
[读端口]
mysql -P6447 -uclusteruser -p12345678