扩展自: 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";