innodb cluster 和 mysqlsh
chenzuoqing Lv3
  2020-09-27 15:56:24 2020-09-27 15:56   2023-10-29 15:03:50      

innodb-cluster 和 mysqlsh

官网下载并安装下面几个包（不带过程）

1
mysql-server mysql-shell mysql-router

配置 innodb cluster

mysql 官网提供了一个很好的工具，叫 mysql-shell 以前没体会到妙处，在配置 innodb cluster 时发现非常方便，推荐尝试。

innodb 集群在 mysql-shell 中配置的大致命令，交互命令都是 js 的语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# 进入mysqlshell
shell> mysqlsh root@192.168.234.128:3306

# 检查实例配置，如果需要修复某些选项，提示中会建议执行 `dba.configureInstance()`
dba.checkInstanceConfiguration('[email protected]:3306')
dba.checkInstanceConfiguration('[email protected]:3307')

# 最好记录修复后的配置加到配置文件 -- 不确定
dba.configureInstance('[email protected]:3307')
dba.configureInstance('[email protected]:3306')

# 创建集群、获取集群，加入实例，实例必须已配置好
var cluster = dba.createCluster('mine_db')

cluster.addInstance('[email protected]:3306')
cluster.addInstance('[email protected]:3307')

# 查看集群，status中列出mode为R/W的为可读写的节点
cluster.describe()
cluster.status()

# 获取集群
var cluster = dba.getCluster('mine_db')

# 若集群节点都离线，会获取失败，提示:
Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (RuntimeError)

# 可以执行下面命令恢复集群
dba.rebootClusterFromCompleteOutage('mine_db')

# 切换多主、单主模式
cluster.switchToMultiPrimaryMode()
cluster.switchToSinglePrimaryMode()

# 有节点UNREACHABLE了不能做出冲裁时，写请求hang住，修复需要强制指定某活跃的节点
cluster.forceQuorumUsingPartitionOf('[email protected]:3307')

# mysql-router相关
cluster.listRouters()
cluster.removeRouterMetadata('192.168.234.128::system')

当前状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
cluster.status()
{
    "clusterName": "mine_db",  # 集群名称
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.234.128:3307",  # 主节点
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.",  # 由于测试只有2个节点，不能容忍任何一个故障
        "topology": {
            "192.168.234.128:3306": {
                "address": "192.168.234.128:3306", 
                "mode": "R/O",   # 只读
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.20"
            }, 
            "192.168.234.128:3307": {
                "address": "192.168.234.128:3307", 
                "mode": "R/W",  # 可读写
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.20"
            }
        }, 
        "topologyMode": "Single-Primary"  # 当前单主模式，cluster.switchToMultiPrimaryMode可切换
    }, 
    "groupInformationSourceMember": "192.168.234.128:3307"
}

增加 mysql-router

若router已经在 cluster.listRouters 中，需要 cluster.removeRouterMetadata 后才能添加，rpm包默认会创建mysqlrouter用户，并且service也以此用户身份运行

生成mysqlrouter配置，执行后可以 cluster.listRouters() 看到

1
2
shell> mysqlrouter --bootstrap [email protected]:3306 --user=mysqlrouter --force-password-validation --report-host 192.168.234.128
shell> systemctl start mysqlrouter.service

生成后提示有监听4个端口，两种协议的读写端口各不同

1
2
3
4
5
6
7
## MySQL Classic protocol
- Read/Write Connections: 192.168.234.128:6446
- Read/Only Connections:  192.168.234.128:6447

## MySQL X protocol
- Read/Write Connections: 192.168.234.128:64460
- Read/Only Connections:  192.168.234.128:64470

连接测试，R/O 模式的节点是 super-read-only 不可写的

1
2
3
4
5
6
7
mysql -uroot -p111111 -h192.168.234.128 -P6446 -e "select @@port"
mysql -uroot -p111111 -h192.168.234.128 -P6447 -e "select @@port"

# 测试语句
-- use test;
-- create table user (id int unsigned auto_increment primary key, name varchar(32), create_time datetime not null default current_timestamp);
-- insert into user (name) values ('aa'), ('bb');

中途停掉一个之后，通过 mysqlrouter 连接同样可以读写，mysqlshell中看到有实例状态是 MISSING 并且连接错误。启动后自动加入到了集群中，开始同步数据，恢复状态。

1
# mysqld_multi stop 3

测试配置

从 5.7 改的，不一定是优选，仅供参考

使用 mysqld_multi 配置，8.0.20 版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
[mysql]
#prompt="(\\u@\\h:\\p) [\\d]> "

[client]
user=root
# default use 3306 instance
socket  = /data/3306/mysql.sock

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password = your_password
log = /data/multi_mysqld.log

[mysqld]
########basic settings########
#server-id = 10
#port = 3306
user = mysql
bind_address = 192.168.234.128
socket = mysql.sock
#autocommit = 0
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
#datadir = /data/3306
transaction_isolation = REPEATABLE-READ
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
#max_allowed_packet = 16777216
max_allowed_packet = 100M
#sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
#report-host = 192.168.234.128
#report-port = 3306
# 并行复制
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=2    # 并行的Coordinator线程数量
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum = none
slave_preserve_commit_order = on

########innodb settings########
innodb_page_size = 16384
#innodb_buffer_pool_size = 512m
innodb_buffer_pool_size = 256m
innodb_buffer_pool_instances = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 100    # no more than innodb_io_capacity / innodb_buffer_pool_instances
innodb_lock_wait_timeout = 50
innodb_io_capacity = 800       # innodb_io_capacity_max * 80%
innodb_io_capacity_max = 1000  # current disk raid 5, max iops 1000+-
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 1
innodb_log_file_size = 128M
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
# - AFTER_SYNC 表示的是无损复制（ 5.7 默认）
# - AFTER_COMMIT 表示的是半同步复制
rpl_semi_sync_master_wait_point=AFTER_SYNC
# 超时五秒后换回异步的方式
#loose_rpl_semi_sync_master_timeout = 5000
########ssl settings########
ssl-ca = ca.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 256M
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32

[mysqld2]
report-host = 192.168.234.128
report-port = 3306
datadir = /data/3306
server-id = 10
port = 3306
socket = /data/3306/mysql.sock
pid-file = /data/3307/mysql2.pid

[mysqld3]
report-host = 192.168.234.128
report-port = 3307
datadir = /data/3307
server-id = 20
port = 3307
socket = /data/3307/mysql.sock
pid-file = /data/3307/mysql3.pid

innodb cluster 和 mysqlsh
2020/09/27/innodb-cluster-和-mysqlsh/
Author
chenzuoqing
Published
2020-09-27 15:56
License
 Comments
  1. innodb-cluster 和 mysqlsh
    1. 配置 innodb cluster
    2. 增加 mysql-router
    3. 测试配置