-- ENV - OS cat /etc/*release Oracle Linux Server release 7.1 NAME="Oracle Linux Server" VERSION="7.1" ID="ol" VERSION_ID="7.1" PRETTY_NAME="Oracle Linux Server 7.1" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:7:1" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://bugzilla.oracle.com/" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7" ORACLE_BUGZILLA_PRODUCT_VERSION=7.1 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=7.1 Red Hat Enterprise Linux Server release 7.1 (Maipo) Oracle Linux Server release 7.1 - server date: 2019-06-10 20:06:14 +0530 build-date: 2019-06-10 16:43:41 +0200 short: 8ccfc51 branch: mysql-5.7.27-release MySQL source 5.7.27 -- export PATH=$PATH:/export/umesh/server/binaries/GABuilds/mysql-5.7.27/bin:/export/umesh/server/binaries/GABuilds/mysql-shell-8.0.17/bin [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/mysql-shell-8.0.17: bin/mysqlsh --log-level=debug3 MySQL Shell 8.0.17 Copyright (c) 2016, 2019, 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. MySQL JS > dba.deploySandboxInstance(3310) A new MySQL sandbox instance will be created on this host in /home/umshastr/mysql-sandboxes/3310 Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks. Please enter a MySQL root password for the new instance: Deploying new MySQL instance... Instance localhost:3310 successfully deployed and started. Use shell.connect('root@localhost:3310'); to connect to the instance. MySQL JS > dba.configureLocalInstance('root@dummy_host:3310', {clusterAdmin: "'icadmin'@'%'", clusterAdminPassword: 'mysql123'}); Please provide the password for 'root@dummy_host:3310': Save password for 'root@dummy_host:3310'? [Y]es/[N]o/Ne[v]er (default No): Configuring local MySQL instance listening at port 3310 for use in an InnoDB cluster... NOTE: Instance detected as a sandbox. Please note that sandbox instances are only suitable for deploying test clusters for use within the same host. This instance reports its own address as 127.0.0.1:3310 The instance 'dummy_host:3310' is valid for InnoDB cluster usage. Cluster admin user 'icadmin'@'%' created. The instance 'dummy_host:3310' is already ready for InnoDB cluster usage. MySQL JS > MySQL JS > \connect icadmin@dummy_host.no.oracle.com:3310 Creating a session to 'icadmin@dummy_host.no.oracle.com:3310' Please provide the password for 'icadmin@dummy_host.no.oracle.com:3310': ******** Save password for 'icadmin@dummy_host.no.oracle.com:3310'? [Y]es/[N]o/Ne[v]er (default No): Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 8 Server version: 5.7.27-log MySQL Community Server (GPL) No default schema selected; type \use to set one. MySQL dummy_host.no.oracle.com:3310 ssl JS > MySQL dummy_host.no.oracle.com:3310 ssl JS > cluster = dba.createCluster("myCluster") A new InnoDB cluster will be created on instance 'dummy_host.no.oracle.com:3310'. Validating instance at dummy_host.no.oracle.com:3310... NOTE: Instance detected as a sandbox. Please note that sandbox instances are only suitable for deploying test clusters for use within the same host. This instance reports its own address as 127.0.0.1:3310 Instance configuration is suitable. WARNING: Instance 'dummy_host.no.oracle.com:3310' cannot persist Group Replication configuration since MySQL version 5.7.27 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the .configureLocalInstance() command locally to persist the changes. Creating InnoDB cluster 'myCluster' on 'dummy_host.no.oracle.com:3310'... 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. - -- router session [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/mysql-router-8.0.17: bin/mysqlrouter --bootstrap icadmin@dummy_host.no.oracle.com:3310 --directory /export/umesh/server/binaries/GABuilds/Bug9665 Please enter MySQL password for icadmin: # Bootstrapping MySQL Router instance at '/export/umesh/server/binaries/GABuilds/Bug9665'... - Checking for old Router accounts - No prior Router accounts found - Creating mysql account mysql_router1_33qlnb2we3ho@'%' for cluster management - Storing account in keyring - Adjusting permissions of generated files - Creating configuration /export/umesh/server/binaries/GABuilds/Bug9665/mysqlrouter.conf # MySQL Router configured for the InnoDB cluster 'myCluster' After this MySQL Router has been started with the generated configuration $ bin/mysqlrouter -c /export/umesh/server/binaries/GABuilds/Bug9665/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 [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/mysql-router-8.0.17: - cd /export/umesh/server/binaries/GABuilds/Bug9665 [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/Bug9665: ls -l total 16 drwx------ 2 umshastr common 49 Sep 11 10:16 data drwx------ 2 umshastr common 36 Sep 11 10:16 log -rw------- 1 umshastr common 1419 Sep 11 10:16 mysqlrouter.conf -rw------- 1 umshastr common 117 Sep 11 10:16 mysqlrouter.key drwx------ 2 umshastr common 10 Sep 11 10:16 run -rwx------ 1 umshastr common 219 Sep 11 10:16 start.sh -rwx------ 1 umshastr common 248 Sep 11 10:16 stop.sh [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/Bug9665: vi mysqlrouter.conf cat mysqlrouter.conf # File automatically generated during MySQL Router bootstrap [DEFAULT] logging_folder=/export/umesh/server/binaries/GABuilds/Bug9665/log runtime_folder=/export/umesh/server/binaries/GABuilds/Bug9665/run data_folder=/export/umesh/server/binaries/GABuilds/Bug9665/data keyring_path=/export/umesh/server/binaries/GABuilds/Bug9665/data/keyring master_key_path=/export/umesh/server/binaries/GABuilds/Bug9665/mysqlrouter.key connect_timeout=15 read_timeout=30 dynamic_state=/export/umesh/server/binaries/GABuilds/Bug9665/data/state.json [logger] level = DEBUG [keepalive] interval = 60 [routing:myCluster_default_rw] bind_address = 0.0.0.0 bind_port=6446 destinations = dummy_host.no.oracle.com:3310 mode = read-write connect_timeout = 10 max_connect_errors = 100 [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/Bug9665: ./start.sh [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/Bug9665: PID 21536 written to '/export/umesh/server/binaries/GABuilds/Bug9665/mysqlrouter.pid' logging facility initialized, switching logging to loggers specified in configuration [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/Bug9665: ps aux|grep mysqlrouter umshastr 21536 0.0 0.0 291952 5164 pts/7 Sl 10:23 0:00 /export/umesh/server/binaries/GABuilds/mysql-router-8.0.17/bin/mysqlrouter -c /export/umesh/server/binaries/GABuilds/Bug9665/mysqlrouter.conf umshastr 21544 0.0 0.0 112632 968 pts/7 S+ 10:24 0:00 grep --color=auto mysqlrouter [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/Bug9665: [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/Bug9665: -- CLI bin/mysql -uicadmin -p --protocol=tcp -hdummy_host.no.oracle.com --port=6446 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28 Server version: 5.7.27-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3310 | +---------------+-------+ 1 row in set (0.00 sec) mysql> \q Bye [umshastr@dummy_host]/export/umesh/server/binaries/GABuilds/mysql-5.7.27: # -- trigger too many connection error bin/mysqlslap -uroot -S /home/umshastr/mysql-sandboxes/3310/sandboxdata/mysqld.sock --concurrency=500 --iterations=20 --number-int-cols=2 --number-char-cols=3 Benchmark Average number of seconds to run all queries: 0.049 seconds Minimum number of seconds to run all queries: 0.017 seconds Maximum number of seconds to run all queries: 0.129 seconds Number of clients running queries: 500 Average number of queries per client: 0 - Once it reaches max connection error - mysql router log flooded with 2019-09-11 12:31:52 routing DEBUG [7f4c95af4700] Trying server dummy_host.no.oracle.com:3310 (index 0) 2019-09-11 12:31:52 routing DEBUG [7f4c946f2700] [routing:myCluster_default_rw] fd=9 connected 127.0.0.1:38074 -> dummy_host.no.oracle.com:3310 as fd=7 2019-09-11 12:31:52 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] fd=9 Pre-auth socket failure 127.0.0.1:38074: Copy client->server failed: unexpected connection close 2019-09-11 12:31:52 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] 27 connection errors for 127.0.0.1:38074 (max 100) 2019-09-11 12:31:52 routing DEBUG [7f4c946f2700] [routing:myCluster_default_rw] fd=9 connection closed (up: 82b; down: 0b) Copy client->server failed: unexpected connection close 2019-09-11 12:31:52 routing DEBUG [7f4c95af4700] [routing:myCluster_default_rw] fd=9 connection accepted at 0.0.0.0:6446 2019-09-11 12:31:52 routing DEBUG [7f4c95af4700] Trying server dummy_host.no.oracle.com:3310 (index 0) 2019-09-11 12:31:52 routing DEBUG [7f4c946f2700] [routing:myCluster_default_rw] fd=8 connected 127.0.0.1:38075 -> dummy_host.no.oracle.com:3310 as fd=7 2019-09-11 12:31:52 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] fd=8 Pre-auth socket failure 127.0.0.1:38075: Copy client->server failed: unexpected connection close 2019-09-11 12:31:52 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] 28 connection errors for 127.0.0.1:38075 (max 100) 2019-09-11 12:31:52 routing DEBUG [7f4c946f2700] [routing:myCluster_default_rw] fd=8 connection closed (up: 0b; down: 0b) Copy client->server failed: unexpected connection close 2019-09-11 12:31:52 routing DEBUG [7f4c95af4700] [routing:myCluster_default_rw] fd=8 connection accepted at 0.0.0.0:6446 2019-09-11 12:31:52 routing DEBUG [7f4c95af4700] Trying server dummy_host.no.oracle.com:3310 (index 0) 2019-09-11 12:31:52 routing DEBUG [7f4c946f2700] [routing:myCluster_default_rw] fd=9 connected 127.0.0.1:38076 -> dummy_host.no.oracle.com:3310 as fd=7 2019-09-11 12:31:52 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] fd=9 Pre-auth socket failure 127.0.0.1:38076: Copy client->server failed: unexpected connection close 2019-09-11 12:31:52 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] 29 connection errors for 127.0.0.1:38076 (max 100) 2019-09-11 12:31:52 routing DEBUG [7f4c946f2700] [routing:myCluster_default_rw] fd=9 connection closed (up: 82b; down: 0b) Copy client->server failed: unexpected connection close 2019-09-11 12:31:53 routing DEBUG [7f4c946f2700] [routing:myCluster_default_rw] fd=8 connected 127.0.0.1:38077 -> dummy_host.no.oracle.com:3310 as fd=7 2019-09-11 12:31:53 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] fd=8 Pre-auth socket failure 127.0.0.1:38077: Copy client->server failed: unexpected connection close 2019-09-11 12:31:53 routing INFO [7f4c946f2700] [routing:myCluster_default_rw] 30 connection errors for 127.0.0.1:38077 (max 100) 2019-09-11 12:31:53 routing DEBUG [7f4c946f2700] [routing:myC . . 2019-09-11 12:37:08 routing DEBUG [7f4c945f1700] [routing:myCluster_default_rw] fd=8 connected 127.0.0.1:39123 -> dummy_host.no.oracle.com:3310 as fd=7 2019-09-11 12:37:08 routing DEBUG [7f4c95af4700] Trying server dummy_host.no.oracle.com:3310 (index 0) 2019-09-11 12:37:08 routing INFO [7f4c945f1700] [routing:myCluster_default_rw] fd=8 Pre-auth socket failure 127.0.0.1:39123: Copy client->server failed: unexpected connection close 2019-09-11 12:37:08 routing INFO [7f4c945f1700] [routing:myCluster_default_rw] 34 connection errors for 127.0.0.1:39123 (max 100) 2019-09-11 12:37:08 routing DEBUG [7f4c945f1700] [routing:myCluster_default_rw] fd=8 connection closed (up: 0b; down: 0b) Copy client->server failed: unexpected connection close 2019-09-11 12:37:08 routing DEBUG [7f4c945f1700] [routing:myCluster_default_rw] fd=9 connected 127.0.0.1:39124 -> dummy_host.no.oracle.com:3310 as fd=7 2019-09-11 12:37:08 routing INFO [7f4c945f1700] [routing:myCluster_default_rw] fd=9 Pre-auth socket failure 127.0.0.1:39124: Copy client->server failed: unexpected connection close 2019-09-11 12:37:08 routing INFO [7f4c945f1700] [routing:myCluster_default_rw] 35 connection errors for 127.0.0.1:39124 (max 100) 2019-09-11 12:37:08 routing DEBUG [7f4c945f1700] [routing:myCluster_default_rw] fd=9 connection closed (up: 0b; down: 0b) Copy client->server failed: unexpected connection close 2019-09-11 12:37:58 keepalive INFO [7f4c964f5700] keepalive ^C -- server's error log 2019-09-11T10:31:52.141179Z 15700 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.170678Z 15701 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.208518Z 15702 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.238097Z 15703 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.267821Z 15704 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.297316Z 15705 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.326870Z 15706 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.356627Z 15707 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.386155Z 15708 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.415580Z 15709 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.445143Z 15710 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.474613Z 15711 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.504150Z 15712 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.533742Z 15713 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.563549Z 15714 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.593299Z 15715 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.623050Z 15716 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.652567Z 15717 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.682110Z 15718 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.711645Z 15719 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.741434Z 15720 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.771082Z 15721 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.800676Z 15722 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.830144Z 15723 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.859907Z 15724 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.889467Z 15725 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.919135Z 15726 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.948586Z 15727 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:52.978218Z 15728 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES) 2019-09-11T10:31:53.007713Z 15729 [Note] Access denied for user 'ROUTER'@'dummy_host.no.oracle.com' (using password: YES)