mysql router安装教程_3.（MySQL Router+MGR)MySQL Router安装与配置

5. Using MySQL Router with InnoDB Cluster

--bootstrap

5.1 生成MySQL Router配置文件

[root@wn19testcdb1001 ~]# mysqlrouter --bootstrap root@192.168.136.128:13306 --directory /root/mysqlrouter --user=root --conf-use-sockets --force

Please enter MySQL password for root:

WARNING: The MySQL server does not have SSL configured and metadata used by the router may be transmitted unencrypted.

Bootstrapping MySQL Router instance at '/root/mysqlrouter'...

Checking for old Router accounts

Creating account mysql_router10_zdwlsgpzi879@'%'

MySQL Router has now been configured for the InnoDB cluster 'testCluster'.

The following connection information can be used to connect to the cluster after MySQL Router has been started with generated configuration..

Classic MySQL protocol connections to cluster 'testCluster':

- Read/Write Connections: localhost:6446

- Read/Write Connections: /root/mysqlrouter/mysql.sock

- Read/Only Connections: localhost:6447

- Read/Only Connections: /root/mysqlrouter/mysqlro.sock

X protocol connections to cluster 'testCluster':

- Read/Write Connections: localhost:64460

- Read/Write Connections: /root/mysqlrouter/mysqlx.sock

- Read/Only Connections: localhost:64470

- Read/Only Connections: /root/mysqlrouter/mysqlxro.sock

查看mysqlrouter配置文件：/etc/mysqlrouter/mysqlrouter.conf

[root@wn19testcdb1001 mysqlrouter]# cat mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap

[DEFAULT]

user=root

logging_folder=/root/mysqlrouter/log

runtime_folder=/root/mysqlrouter/run

data_folder=/root/mysqlrouter/data

keyring_path=/root/mysqlrouter/data/keyring

master_key_path=/root/mysqlrouter/mysqlrouter.key

connect_timeout=30

read_timeout=30

[logger]

level = INFO

[metadata_cache:testCluster]

router_id=10

bootstrap_server_addresses=mysql://wn19testcdb1001:13306,mysql://wn19testcdb1002:13306,mysql://wn19testcdb1003:13306

user=mysql_router10_zdwlsgpzi879

metadata_cluster=testCluster

ttl=0.5

[routing:testCluster_default_rw]

bind_address=0.0.0.0

bind_port=6446

socket=/root/mysqlrouter/mysql.sock

destinations=metadata-cache://testCluster/default?role=PRIMARY

routing_strategy=round-robin

protocol=classic

[routing:testCluster_default_ro]

bind_address=0.0.0.0

bind_port=6447

socket=/root/mysqlrouter/mysqlro.sock

destinations=metadata-cache://testCluster/default?role=SECONDARY

routing_strategy=round-robin

protocol=classic

[routing:testCluster_default_x_rw]

bind_address=0.0.0.0

bind_port=64460

socket=/root/mysqlrouter/mysqlx.sock

destinations=metadata-cache://testCluster/default?role=PRIMARY

routing_strategy=round-robin

protocol=x

[routing:testCluster_default_x_ro]

bind_address=0.0.0.0

bind_port=64470

socket=/root/mysqlrouter/mysqlxro.sock

destinations=metadata-cache://testCluster/default?role=SECONDARY

routing_strategy=round-robin

protocol=x

如果需要更新添加新instance需要修改bootstrap_server_addresses

默认有效的tcp端口:

6446 - for Classic MySQL protocol read-write sessions, which MySQL Router redirects incoming connections to primary server instances. 6447 - for Classic MySQL protocol read-only sessions, which MySQL Router redirects incoming connections to one of the secondary server instances. 64460 - for X Protocol read-write sessions, which MySQL Router redirects incoming connections to primary server instances. 64470 - for X Protocol read-only sessions, which MySQL Router redirects incoming connections to one of the secondary server instances.

注意端口配置：

--conf-base-port group_replication_single_primary_mode

5.2 开启

[root@wn19testcdb1001 mysqlrouter]# pwd

/root/mysqlrouter

[root@wn19testcdb1001 mysqlrouter]# sh start.sh

mysqlsh --uri root@192.168.136.128:6446

mysql-js> \sql

Switching to SQL mode... Commands end with ;

mysql-sql> select @@port;

[root@wn19testcdb1001 ~]# mysqlsh --uri root@192.168.136.128:6446

MySQL 192.168.136.128:6446 SQL > show variables like 'hostname' ;

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| hostname | wn19testcdb1001 |

+---------------+-----------------+

1 row in set (0.0021 sec)

[root@wn19testcdb1001 ~]# mysqlsh --uri root@192.168.136.128:6447

MySQL 192.168.136.128:6447 SQL > show variables like 'hostname' ;

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| hostname | wn19testcdb1003 |

+---------------+-----------------+

6. Testing High Availability

dba.killSandboxInstance(13306)

7. Working with InnoDB Cluster

7.1 Retrieving an InnoDB cluster

var cluster1 = dba.getCluster()

7.2 Checking the InnoDB Cluster Status

mysql-js> var cluster = dba.getCluster()

mysql-js> cluster.status()

7.3 Describing the Structure of the InnoDB Cluster

mysql-js> cluster.describe();

7.4 Super Read-only and Instances

super_read_only=OFF

dba.configureLocalInstance()

dba.createCluster()

dba.rebootClusterFromCompleteOutage()

dba.dropMetadataSchema()

mysql-js> var myCluster = dba.createCluster('testCluster')

7.5 Managing Sandbox Instances

dba.stopSandboxInstance(instance)

dba.killSandboxInstance(instance).

dba.startSandboxInstance(instance).

dba.killSandboxInstance(instance)

dba.deleteSandboxInstance(instance).

7.6 Removing Instances from the InnoDB Cluster

mysql-js> cluster.removeInstance('root@localhost:3310')

7.7 Customizing InnoDB clusters

dba.createCluster()

cluster.addInstance()

group_replication_group_name

dba.createCluster()

cluster.addInstance()

group_replication_local_address

group_replication_group_seeds

7.8 Rejoining a Cluster

cluster.rejoinInstance().

dba.configureLocalInstance()

dba.configureLocalInstance()

7.9 Restoring a Cluster from Quorum Loss

mysql-js> cluster = dba.getCluster("prodCluster")

mysql-js> cluster.forceQuorumUsingPartitionOf("localhost:3310")

8.0 Rebooting a Cluster from a Major Outage

mysql-js> dba.startSandboxInstance(3310)

mysql-js> dba.startSandboxInstance(3320)

mysql-js> dba.startSandboxInstance(3330)

mysql-js> shell.connect('root@localhost:3310');

mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();

8.1 Rescanning a Cluster

cluster.rescan()

8.2 Checking Instance State

mysql-js> cluster.checkInstanceState('ic@ic-4:3306')

8.3 Dissolving an InnoDB Cluster

Cluster.dissolve()

mysql-js> session

mysql-js> cluster.dissolve({force:true})

The cluster was successfully dissolved.

Replication was disabled but user data was left intact.

8.4 Securing your Cluster

8.5 Creating a Whitelist of Servers

mysql-js> cluster.addInstance("ic@ic-3:3306", {ipWhitelist: "203.0.113.0/24, 198.51.100.110"})

8.6 Using MySQL Shell to Execute a Script

shell> mysqlsh -f setup-innodb-cluster.js

Known Limitations page:2946 (2974 / 5214)

mysqlrouter 单主 读写分离

读写端口指定

[routing:masters]

bind_address=192.168.136.128

bind_port=7002

destinations=192.168.136.128:13306

mode=read-write

connect_timeout=2

protocol=classic

读端口指写

[routing:slaves]

bind_address=192.168.136.128

bind_port=7001

destinations=192.168.136.129:13306,192.168.136.130:13306

mode=read-only

connect_timeout=1

protocol=classic

mysqlrouter 多主(高可用failover)

[routing:mutili_rw]

bind_address=192.168.136.128

bind_port=7003

destinations=192.168.136.128:13306,192.168.136.129:13306,192.168.136.130:13306

mode=read-write

connect_timeout=2

protocol=classic

注：参数说明

bind_address 指定mysql router绑定的服务器

bind_port 指定绑定的端口

destinations 指定后端mysql server 列表 (ip:port 格式，使用逗号分隔)

mode 读写模式(read-write, read_only)

netstat -tln

SET GLOBAL group_replication_bootstrap_group=ON; #开启bootstrap the group

START GROUP_REPLICATION; #开启GR

SET GLOBAL group_replication_bootstrap_group=OFF; #关闭bootstrap the group

SELECT * FROM performance_schema.replication_group_members;

root@localhost:(none) 04:29:21>SELECT * FROM performance_schema.replication_group_members;

压测：

mysqlslap -uroot -pRoot@111 -h 192.168.136.128 -P 7003 --iterations=10 --concurrency=50,100,500 --number-of-queries=1 --create-schema='test' --query='insert into tt(b) values(1);commit;'

问题：

2019-01-25T00:35:25.504290-05:00 615 [ERROR] Slave SQL for channel 'group_replication_applier': Worker 1 failed executing transaction 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:78' at master log , end_log_pos 113; Error 'Table 'test.t' doesn't exist' on query. Default database: 'test'. Query: 'alter table t add column t varchar(20)', Error_code: 1146

2019-01-25T00:35:25.505389-05:00 614 [Warning] Slave SQL for channel 'group_replication_applier': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756

2019-01-25T00:35:25.505430-05:00 614 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed

2019-01-25T00:35:25.505479-05:00 614 [ERROR] Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'

2019-01-25T00:35:25.506812-05:00 611 [ERROR] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'

2019-01-25T00:35:25.506941-05:00 611 [ERROR] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'

2019-01-25T00:35:25.508051-05:00 611 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

解决办法：

root@localhost00:50:11[test]>set session SQL_LOG_BIN=off ;

Query OK, 0 rows affected (0.00 sec)

root@localhost00:50:42[test]>set global super_read_only =off ;

Query OK, 0 rows affected (0.00 sec)

root@localhost00:50:56[test]>CREATE TABLE t(

-> a int(11) NOT NULL,

-> primary key(a)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

Query OK, 0 rows affected (0.09 sec)

root@localhost00:51:11[test]>set global super_read_only =on ;

Query OK, 0 rows affected (0.00 sec)

root@localhost00:51:24[test]>set session SQL_LOG_BIN=on ;

Query OK, 0 rows affected (0.00 sec)

root@localhost00:51:32[test]>start group_replication ;

ERROR 3093 (HY000): The START GROUP_REPLICATION command failed since the group is already running.

root@localhost00:51:39[test]>stop group_replication ;

Query OK, 0 rows affected (1.00 sec)

root@localhost00:51:46[test]>start group_replication ;

Query OK, 0 rows affected (3.09 sec)

root@localhost00:51:53[test]>SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-----------------+-------------+--------------+

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-----------------+-------------+--------------+

| group_replication_applier | 360bfb62-db39-11e8-b641-000c29beb128 | wn19testcdb1001 | 13306 | ONLINE |

| group_replication_applier | 360bfb62-db39-11e8-b641-000c29beb129 | wn19testcdb1002 | 13306 | ONLINE |

| group_replication_applier | 360bfb62-db39-11e8-b641-000c29beb130 | wn19testcdb1003 | 13306 | ONLINE |

+---------------------------+--------------------------------------+-----------------+-------------+--------------+

3 rows in set (0.00 sec)

小结：

mysqlrouter :要指定端口实现读写分离(很失望)

mysqlrouter+mgr：支持(single-primary ,mutili-primary模式下的)在failover 高可用