msql 8.0.21版innodb cluster部署之详细步骤 -m6米乐安卓版下载

目录
一、环境分配情况
二、环境准备（所有主机均需要操作）	
三、mysql实例准备（所有主机均需要操作）	
四、mysql shell操作	
五、mysql router操作	
一、环境分配情况：
ip地址	主机名	角色	安装软件
19.168.0.16	innodbcluster1	cluester节点1	mysql8.0.21, mysql-shell
19.168.0.17	innodbcluster2	cluester节点2	mysql8.0.21, mysql-shell,mysql-router
19.168.0.18	innodbcluster3	cluester节点3	mysql8.0.21, mysql-shell,mysql-router
19.168.0.14	service-ip		keepalived搭建router高可用
二、环境准备（所有主机均需要操作）：
--检查操作系统版本
[root@dbtest1 mysql]#  cat /etc/redhat-release
red hat enterprise linux server release 6.6 (santiago)
[root@dbtest1 mysql]# 
--检查python是否安装
[root@dbtest1 mysql]#  python -v
python 2.6.6
--关闭iptables服务
[root@dbtest1 mysql]# chkconfig --list|grep iptables
iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@dbtest1 mysql]#
[root@dbtest1 mysql]# service iptables status
iptables: firewall is not running.
[root@dbtest1 mysql]#
--关闭selinux
[root@dbtest1 mysql]# cat /etc/sysconfig/selinux |grep "selinux"
# selinux= can take one of these three values:
selinux=disabled
# selinuxtype= can take one of these two values:
selinuxtype=targeted
[root@dbtest1 mysql]#
[root@dbtest1 mysql]# setenforce 0
setenforce: selinux is disabled
[root@dbtest1 mysql]# getenforce
disabled
--配置操作系统参数
[root@dbtest1 mysql]# cat>>/etc/sysctl.conf < fs.aio-max-nr = 1048576
> fs.file-max = 681574400
> kernel.shmmax = 137438953472
> kernel.shmmni = 4096
> kernel.sem = 250 32000 100 200
> net.ipv4.ip_local_port_range = 9000 65000
> net.core.rmem_default = 262144
> net.core.rmem_max = 4194304
> net.core.wmem_default = 262144
> net.core.wmem_max = 1048586
> eof
[root@dbtest1 mysql]# cat>>/etc/security/limits.conf < mysql soft nproc 65536
> mysql hard nproc 65536
> mysql soft nofile 65536
> mysql hard nofile 65536
> eof
--配置hosts信息
[root@dbtest1 mysql]# cat>>/etc/hosts < 19.168.0.16 dbtest1
> 19.168.0.17 dbtest2
> 19.168.0.18 dbtest3
> eof
[root@dbtest1 mysql]#
三、mysql实例准备（所有主机均需要操作）：
安装依赖
[root@dbtest1 mysql]# yum install  -y numactl libaio
loaded plugins: product-id, refresh-packagekit, security, subscription-manager
this system is not registered to red hat subscription management. you can use subscription-manager to register.
setting up install process
iso                                                                                                             | 4.1 kb     00:00 ...
package numactl-2.0.9-2.el6.x86_64 already installed and latest version
package libaio-0.3.107-10.el6.x86_64 already installed and latest version
nothing to do
[root@dbtest1 mysql]# rpm -qa|grep numactl
numactl-2.0.9-2.el6.x86_64
[root@dbtest1 mysql]# rpm -qa|grep libaio
libaio-0.3.107-10.el6.x86_64
[root@dbtest1 mysql]#
检查参数（确保server_id参数不同）：
#配置文件
[root@innodbcluster1  ~]# vi /opt/my.cnf
[root@innodbcluster1 base]# 
安装mysql
groupadd mysql
useradd -g mysql mysql
mkdir -p /mysql/data/3306
mkdir -p /mysql/tmp/3306
cd /mysql
xz -d mysql-commercial-8.0.21-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-commercial-8.0.21-linux-glibc2.12-x86_64.tar -c /mysql/
cd /mysql/
ln -s mysql-commercial-8.0.21-linux-glibc2.12-x86_64 base
chown mysql:mysql -r mysql-commercial-8.0.21-linux-glibc2.12-x86_64
chown mysql:mysql  -r /mysql/data/3306
chown mysql:mysql  -r /mysql/tmp/3306
cd base
bin/mysqld --defaults-file=/etc/my.cnf  --initialize --user=mysql
bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
cp support-files/mysql.server /etc/init.d/mysql.server
ls -lrt /mysql/data/3306
bin/mysql -uroot -p -s /mysql/data/3306/mysqld.sock
alter user 'root'@'localhost' identified with caching_sha2_password by 'innodb_136#';
创建innodbcluter管理用户
[root@dbtest1 base]# bin/mysql -uroot -p -s /mysql/data/3306/mysqld.sock
enter password:
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 11
server version: 8.0.21-commercial mysql enterprise server - commercial
米乐app官网下载 copyright (c) 2000, 2020, 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> set sql_log_bin=off;
query ok, 0 rows affected (0.00 sec)
mysql> create user icadmin@'%' identified by 'innodb_136#';
query ok, 0 rows affected (0.01 sec)
mysql> grant all on *.* to icadmin@'%' with grant option;
query ok, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=on;
query ok, 0 rows affected (0.00 sec)
mysql> exit
bye
[root@dbtest1 base]#
四、mysql shell操作（所有主机均需要操作）：
mysql shell安装
tar -zxvf mysql-shell-commercial-8.0.21-linux-glibc2.12-x86-64bit.tar.gz -c /mysql/
ln -s mysql-shell-commercial-8.0.21-linux-glibc2.12-x86-64bit mysql-shell
chown mysql:mysql -r mysql-shell-commercial-8.0.21-linux-glibc2.12-x86-64bit/
cd mysql-shell
vi /etc/profile
  export path=$path:/mysql/mysql-shell/bin
source /etc/profile
安装部署mgr
[root@dbtest1 mysql-shell]# mysqlsh --uri icadmin@dbtest1:3306
please provide the password for 'icadmin@dbtest1:3306': ***********
mysql shell 8.0.21-commercial
米乐app官网下载 copyright (c) 2016, 2020, 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 '\?' for help; '\quit' to exit.
creating a session to 'icadmin@dbtest1:3306'
fetching schema names for autocompletion... press ^c to stop.
your mysql connection id is 13
server version: 8.0.21-commercial mysql enterprise server - commercial
no default schema selected; type \use  to set one.
--检查实例配置是否满足要求
 mysql  dbtest1:3306 ssl  js >  dba.checkinstanceconfiguration('icadmin@dbtest1:3306')
please provide the password for 'icadmin@dbtest1:3306': ***********
validating local mysql instance listening at port 3306 for use in an innodb cluster...
this instance reports its own address as dbtest1:3306
clients and other cluster members will communicate with it through this address by default. if this is not correct, the report_host mysql system variable should be changed.
checking whether existing tables comply with group replication requirements...
no incompatible tables detected
checking instance configuration...
instance configuration is compatible with innodb cluster
the instance 'dbtest1:3306' is valid to be used in an innodb cluster.
{
    "status": "ok"
}
--配置实例
 mysql  dbtest1:3306 ssl  js > dba.configurelocalinstance('icadmin@dbtest1:3306')
please provide the password for 'icadmin@dbtest1:3306': ***********
configuring local mysql instance listening at port 3306 for use in an innodb cluster...
this instance reports its own address as dbtest1:3306
clients and other cluster members will communicate with it through this address by default. if this is not correct, the report_host mysql system variable should be changed.
the instance 'dbtest1:3306' is valid to be used in an innodb cluster.
the instance 'dbtest1:3306' is already ready to be used in an innodb cluster.
--创建mycluter mgr集群
 mysql  dbtest1:3306 ssl  js >  var cluster = dba.createcluster('mycluster');
a new innodb cluster will be created on instance 'dbtest1:3306'.
validating instance configuration at dbtest1:3306...
this instance reports its own address as dbtest1:3306
instance configuration is suitable.
note: group replication will communicate with other members using 'dbtest1:33061'. use the localaddress option to override.
creating innodb cluster 'mycluster' on 'dbtest1:3306'...
adding seed instance...
        cluster successfully created. use cluster.addinstance() to add mysql instances.
at least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
--添加dbtest1实例
 mysql  dbtest1:3306 ssl  js > cluster.addinstance('icadmin@dbtest1:3306');
please provide the password for 'icadmin@dbtest1:3306': ***********
cluster.addinstance: the instance 'dbtest1:3306' is already part of this innodb cluster (runtimeerror)
--添加dbtest2实例
 mysql  dbtest1:3306 ssl  js > cluster.addinstance('icadmin@dbtest2:3306');
please provide the password for 'icadmin@dbtest2:3306': ***********
warning: a gtid set check of the mysql instance at 'dbtest2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
dbtest2:3306 has the following errant gtids that do not exist in the cluster:
64bde02e-d216-11ea-be45-0050569c9edc:1
warning: discarding these extra gtid events can either be done manually or by completely overwriting the state of dbtest2:3306 with a physical snapshot from an existing cluster member. to use this method by default, set the 'recoverymethod' option to 'clone'.
having extra gtid events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
--提示数据不一致，选在clone方式做数据同步
please select a recovery method [c]lone/[a]bort (default abort): c
note: group replication will communicate with other members using 'dbtest2:33061'. use the localaddress option to override.
validating instance configuration at dbtest2:3306...
this instance reports its own address as dbtest2:3306
instance configuration is suitable.
a new instance will be added to the innodb cluster. depending on the amount of
data on the cluster this might take from a few seconds to several hours.
adding instance to the cluster...
monitoring recovery process of the new cluster member. press ^c to stop monitoring and let it continue in background.
clone based state recovery is now in progress.
note: a server restart is expected to happen as part of the clone process. if the
server does not support the restart command or does not come back after a
while, you may need to manually start it back.
* waiting for clone to finish...
note: dbtest2:3306 is being cloned from dbtest1:3306
** stage drop data: completed
** clone transfer
    file copy  ############################################################  100%  completed
    page copy  ############################################################  100%  completed
    redo copy  ############################################################  100%  completed
** stage recovery: |
note: dbtest2:3306 is shutting down...
* waiting for server restart... ready
* dbtest2:3306 has restarted, waiting for clone to finish...
* clone process has finished: 1.12 gb transferred in 9 sec (124.53 mb/s)
state recovery already finished for 'dbtest2:3306'
the instance 'dbtest2:3306' was successfully added to the cluster.
--添加dbtest3实例
mysql  dbtest1:3306 ssl  js > cluster.addinstance('icadmin@dbtest3:3306');
please provide the password for 'icadmin@dbtest3:3306': ***********
warning: a gtid set check of the mysql instance at 'dbtest3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
dbtest3:3306 has the following errant gtids that do not exist in the cluster:
60fc148d-d216-11ea-9cc8-0050569c39e9:1
warning: discarding these extra gtid events can either be done manually or by completely overwriting the state of dbtest3:3306 with a physical snapshot from an existing cluster member. to use this method by default, set the 'recoverymethod' option to 'clone'.
having extra gtid events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
--提示数据不一致，选在clone方式做数据同步
please select a recovery method [c]lone/[a]bort (default abort): c
note: group replication will communicate with other members using 'dbtest3:33061'. use the localaddress option to override.
validating instance configuration at dbtest3:3306...
this instance reports its own address as dbtest3:3306
instance configuration is suitable.
a new instance will be added to the innodb cluster. depending on the amount of
data on the cluster this might take from a few seconds to several hours.
adding instance to the cluster...
monitoring recovery process of the new cluster member. press ^c to stop monitoring and let it continue in background.
clone based state recovery is now in progress.
note: a server restart is expected to happen as part of the clone process. if the
server does not support the restart command or does not come back after a
while, you may need to manually start it back.
* waiting for clone to finish...
note: dbtest3:3306 is being cloned from dbtest1:3306
** stage drop data: completed
** clone transfer
    file copy  ############################################################  100%  completed
    page copy  ############################################################  100%  completed
    redo copy  ############################################################  100%  completed
** stage recovery: |
note: dbtest3:3306 is shutting down...
* waiting for server restart... ready
* dbtest3:3306 has restarted, waiting for clone to finish...
* clone process has finished: 1.12 gb transferred in 10 sec (112.08 mb/s)
state recovery already finished for 'dbtest3:3306'
the instance 'dbtest3:3306' was successfully added to the cluster.
 mysql  dbtest1:3306 ssl  js >
--检查cluster状态
 mysql  dbtest1:3306 ssl  js >  cluster.status()
{
    "clustername": "mycluster",
    "defaultreplicaset": {
        "name": "default",
        "primary": "dbtest1:3306",
        "ssl": "required",
        "status": "ok",
        "statustext": "cluster is online and can tolerate up to one failure.",
        "topology": {
            "dbtest1:3306": {
                "address": "dbtest1:3306",
                "mode": "r/w",
                "readreplicas": {},
                "replicationlag": null,
                "role": "ha",
                "status": "online",
                "version": "8.0.21"
            },
            "dbtest2:3306": {
                "address": "dbtest2:3306",
                "mode": "r/o",
                "readreplicas": {},
                "replicationlag": null,
                "role": "ha",
                "status": "online",
                "version": "8.0.21"
            },
            "dbtest3:3306": {
                "address": "dbtest3:3306",
                "mode": "r/o",
                "readreplicas": {},
                "replicationlag": null,
                "role": "ha",
                "status": "online",
                "version": "8.0.21"
            }
        },
        "topologymode": "single-primary"
    },
    "groupinformationsourcemember": "dbtest1:3306"
}
 mysql  dbtest1:3306 ssl  js >
mysql  dbtest1:3306 ssl  js > \q
bye!
五、mysql router操作（137、138上操作）：
mysql router安装
unzip v999372-01_mysql\ router\ 8.0.21\ tar\ for\ generic\ linux\ x86\ \(64bit\).zip
xz -d mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64.tar -c /mysql
ln -s mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64 mysql-router
chown mysql:mysql -r mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64
cd mysql-router
vi /etc/profile
  export path=$path:/mysql/mysql-router/bin/
source /etc/profile
mysql router配置(137,138上执行)
--初始化配制文件
[root@dbtest2 mysql-router]# mysqlrouter --bootstrap icadmin@dbtest1:3306 --user=mysql
please enter mysql password for icadmin:
# reconfiguring system mysql router instance...
- fetching password for current account (mysql_router1_xc1qqkdyg0yi) from keyring
- creating account(s) (only those that are needed, if any)
- verifying account (using it to run sql queries that would be run by router)
- storing account in keyring
- adjusting permissions of generated files
- creating configuration /mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/mysqlrouter.conf
existing configuration backed up to '/mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/mysqlrouter.conf.bak'
# mysql router configured for the innodb cluster 'mycluster'
after this mysql router has been started with the generated configuration
    $ /etc/init.d/mysqlrouter restart
or
    $ mysqlrouter -c /mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/mysqlrouter.conf
the cluster 'mycluster' can be reached by connecting to:
## mysql classic protocol
- read/write connections: localhost:6446
- read/only connections:  localhost:6447
## mysql x protocol
- read/write connections: localhost:64460
- read/only connections:  localhost:64470
--检查配置文件信息
[root@dbtest2 mysql-router]# cat /mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/mysqlrouter.conf
# file automatically generated during mysql router bootstrap
[default]
name=system
user=mysql
keyring_path=/mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/var/lib/mysqlrouter/keyring
master_key_path=/mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/var/lib/mysqlrouter/state.json
[logger]
level = info
[metadata_cache:mycluster]
cluster_type=gr
router_id=1
user=mysql_router1_xc1qqkdyg0yi
metadata_cluster=mycluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0
[routing:mycluster_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mycluster/?role=primary
routing_strategy=first-available
protocol=classic
[routing:mycluster_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://mycluster/?role=secondary
routing_strategy=round-robin-with-fallback
protocol=classic
[routing:mycluster_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://mycluster/?role=primary
routing_strategy=first-available
protocol=x
[routing:mycluster_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://mycluster/?role=secondary
routing_strategy=round-robin-with-fallback
protocol=x
[root@dbtest2 mysql-routerps]#
--更改目录属主
chown mysql:mysql -r /mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64
--启动mysql-router
mysqlrouter -c /mysql/mysql-router-commercial-8.0.21-linux-glibc2.12-x86_64/mysqlrouter.conf --user=mysql&

以下为个人公众号，欢迎扫码关注：