MySQL InnoDB Cluster + MySQL Router 8 + Async replication

扩展自： https://blog.csdn.net/GW569453350game/article/details/103172972

MySQL 可扩展的 InnoDB 方案:

在同一机房，或者邻近机房至少3台机器组成 InnoDB Cluster(网络连接较好)

在网络连接较差的机房使用传统的 Master + Slave 异步 Replication, 其中的Master来之 InnoDB Cluster 中的任意一台机器。

[mysqld]
skip-host-cache
skip-name-resolve
datadir = /var/lib/mysql/
general_log = OFF
event_scheduler=ON
bind-address = 0.0.0.0
port  = 17571
mysqlx_port = 17572  # default 33060
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

innodb_buffer_pool_size = 4G
innodb_log_file_size = 200M
innodb_flush_log_at_trx_commit = 2
sync_binlog = 50

log-error = error.log
log-bin = mysql-bin.log


# Server configuration

gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
binlog_format=ROW
max_binlog_size = 500M


# Group configuration

# transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
loose-group_replication_group_name="785fe3ed-1e54-48a7-9bec-038e21ba8119"
loose-group_replication_start_on_boot=off

# Different in each MySQL Server
# 以下这些配置每台机器都不一样，需要从docker中mount到本地文件系统中做修改

#server_id = 17
#loose-group_replication_local_address= "172.30.0.51:33061"
#loose-group_replication_group_seeds= "172.30.0.50:33061, 172.30.0.51:33061, 172.30.0.52:33061"
#loose-group_replication_ip_whitelist= "172.30.0.50, 172.30.0.51, 172.30.0.52"
#report_host = "172.30.0.51"

MySQL Shell

shell.connect('root@172.30.0.50:17571')
var cluster = dba.createCluster('myCluster', {'localAddress':'172.30.0.50:33061'})
cluster.addInstance('root@172.30.0.51:17571',{'localAddress':'172.30.0.51:33061'});

MySQL Router

mysqlrouter --bootstrap --force root@172.30.0.50:17571 --directory ./mysql_router/
mysqlrouter -c ./mysqlrouter.conf

# 如果又其他读写配置 append 如下配置：
[routing:ro_route_mode]
bind_port = 7002
destinations = 120.131.8.111:17571, 42.81.57.111:17571
routing_strategy=round-robin
protocol=classic

mysql 默认开启 auto_commit，也就是每一条执行的语句都是默认一个transaction.
mysql 默认的transaction isolation是 REPEATABLE-READ, 即每次mysql读取数据之前都会读取快照。不会受到其他session改动的影响。

注：如果是使用传统异步复制，并且将其中的slave的master只想Inno Cluster中的任意一个。
需要在 slave 的 my.cnf 中加上

replicate-ignore-db="ch1:mysql_innodb_cluster_metadata"

然后配置master IP 信息是加上 CHANNEL 信息：

CHANGE MASTER TO MASTER_HOST='183.2.233.62',MASTER_PORT=17511,MASTER_USER='slave',MASTER_PASSWORD='agora-repl',MASTER_AUTO_POSITION = 1, GET_MASTER_PUBLIC_KEY=1 FOR CHANNEL "ch1";
CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(mysql_innodb_cluster_metadata) FOR CHANNEL "ch1";
START SLAVE FOR CHANNEL "ch1";