本文记录搭建mysql innodb cluster的过程和基本的测试。
1、环境描述
| 主机名 | 主机IP | 部署 |
| tidb60 | 192.168.68.60 | MySQL8.0.20 |
| tidb61 | 192.168.68.61 | MySQL8.0.20 |
| tidb62 | 192.168.68.62 | MySQL8.0.20 |
| tidb63 | 192.168.68.63 | MySQL-shell,MySQL-route，mysql-client |
2、下载安装包
https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-8.0.20-linux-glibc2.12-x86_64.tar.xz
https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz
2、环境部署
2.1 MySQL8.0.20 实例部署，以tidb60主机上为例。
mkdir -p /home/mysql_cluster/my3366/{socket,data,logs,dump_dir}
cd /home/mysql_cluster
chown -R mysql:mysql my3366
/usr/local/mysql8020/bin/mysqld --defaults-file=/home/mysql_cluster/my3366/my3366.cnf --initialize
nohup /usr/local/mysql8020/bin/mysqld_safe --defaults-file=/home/mysql_cluster/my3366/my3366.cnf --user=mysql &
/usr/local/mysql8020/bin/mysql -S /home/mysql_cluster/my3366/socket/mysql3366.sock -uroot -p
create user dba@'%' identified WITH mysql_native_password by 'dba';
grant all on *.* to dba@'%' with grant option;
flush privileges;
参数文件/home/mysql_cluster/my3366/my3366.cnf
[client] port = socket = /home/mysql_cluster/my3366/socket/mysql3366.sock [mysql] prompt="\u@\h R:m:s [\d]>" no-auto-rehash port = 3366 socket = /home/mysql_cluster/my3366/socket/mysql3366.sock default_character_set = utf8mb4 [mysqladmin] port = 3366 socket = /home/mysql_cluster/my3366/socket/mysql3366.sock [mysqldump] port = 3366 socket = /home/mysql_cluster/my3366/socket/mysql3366.sock [xtrabackup] port = 3366 socket = /home/mysql_cluster/my3366/socket/mysql3366.sock [mysqlbackup] port = 3366 socket = /home/mysql_cluster/my3366/socket/mysql3366.sock safe_slave_backup_timeout = 0 [mysqld] # basic settings # user = mysql port = 3366 default_storage_engine = InnoDB server_id = 168603366 sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION socket = /home/mysql_cluster/my3366/socket/mysql3366.sock pid_file = /home/mysql_cluster/my3366/socket/mysql3366.pid basedir = /usr/local/mysql8020 datadir = /home/mysql_cluster/my3366/data character_set_server = utf8mb4 default_time_zone = +08:00 back_log = 1024 thread_stack = 512K external_locking = FALSE open_files_limit = 65535 max_allowed_packet = 128M lower_case_table_names = 1 explicit_defaults_for_timestamp = 1 read_only = 0 super_read_only = 0 # connection # lock_wait_timeout = 3600 skip_name_resolve = 1 max_connect_errors = 1000000 max_connections = 5000 # table cache performance settings# table_open_cache = 4096 table_open_cache_instances = 64 table_definition_cache = 4096 # CACHES AND LIMITS # tmp_table_size = 32M max_heap_table_size = 32M bulk_insert_buffer_size = 64M # session memory settings # sort_buffer_size = 4M join_buffer_size = 4M read_buffer_size = 8M read_rnd_buffer_size = 4M thread_cache_size = 3000 # log settings # log_error_verbosity = 3 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_error = /home/mysql_cluster/my3366/logs/mysql-error.log log_timestamps = SYSTEM log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = /home/mysql_cluster/my3366/logs/mysql-slow.log long_query_time = 0.5 # innodb settings # innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_log_buffer_size = 32M innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 ##机械盘设置为500,SSD盘设置为10000-20000## innodb_io_capacity = 500 ##机械盘设置为2000,SSD盘设置为10000-20000## innodb_io_capacity_max = 1000 ##机械盘设置为1,SSD盘设置为0## innodb_flush_neighbors = 1 innodb_write_io_threads = 16 innodb_read_io_threads = 16 innodb_purge_threads = 4 innodb_page_cleaners = 16 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_lru_scan_depth = 4096 innodb_lock_wait_timeout = 10 innodb_checksum_algorithm = crc32 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0 innodb_flush_method = O_DIRECT innodb_log_files_in_group = 3 innodb_log_file_size = 1G innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4 # undo settings # innodb_undo_directory = /home/mysql_cluster/my3366/data innodb_undo_tablespaces = 95 innodb_undo_log_truncate = 1 innodb_purge_rseg_truncate_frequency = 128 innodb_max_undo_log_size = 4G innodb_status_file = 1 innodb_status_output = 0 innodb_status_output_locks = 0 # performance_schema # performance_schema = 1 performance_schema_instrument = '%=on' # innodb monitor # innodb_monitor_enable="module_innodb,module_server,module_dml,module_ddl,module_trx,module_os,module_purge,module_log,module_lock,module_buffer,module_index,module_ibuf_system,module_buffer_page,module_adaptive_hash" # safe # secure_file_priv = /home/mysql_cluster/my3366/dump_dir default_password_lifetime = 0 # replication settings # skip_slave_start = 1 relay_log = /home/mysql_cluster/my3366/logs/relay-bin slave_pending_jobs_size_max = 128M log_slave_updates = 1 log_bin = /home/mysql_cluster/my3366/logs/mysql-bin expire_logs_days = 7 sync_binlog = 1 gtid_mode = ON enforce_gtid_consistency = 1 binlog_cache_size = 4M max_binlog_size = 512M max_binlog_cache_size = 2G slave_preserve_commit_order= 1 binlog_format = row relay_log_purge = 1 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 16 master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = ON binlog_gtid_simple_recovery = 1 # group replication & innodb cluster # disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" master_info_repository = table binlog_checksum = NONE relay_log_info_repository = table gtid_mode = ON enforce_gtid_consistency = ON binlog_checksum = NONE log_slave_updates = ON binlog_format = ROW transaction_write_set_extraction = XXHASH64 slave_parallel_workers = 4 slave_preserve_commit_order = 1 slave_parallel_type = LOGICAL_CLOCK binlog_transaction_dependency_tracking = WRITESET_SESSION # clone #
2.2 配置检查
在tidb63主机上部署mysqlshell，下载后解压，配置环境变量即可使用，部署路径跟进实际环境即可。
检查节点1：tidb60:3366实例
[root@tidb63 bin]# pwd
/home/jm/data/mysql-shell-8.0.20/bin
[root@tidb63 bin]# ./mysqlsh dba@tidb60:3366
Please provide the password for 'dba@tidb60:3366': ***
MySQL Shell 8.0.20
Copyright (c) 2016, 2020, 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.
Creating a session to 'dba@tidb60:3366'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 36698
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL tidb60:3366 ssl JS > dba.checkInstanceConfiguration("dba@tidb60:3366");
Please provide the password for 'dba@tidb60:3366': ***
Validating MySQL instance at tidb60.com:3366 for use in an InnoDB cluster...
This instance reports its own address as tidb60.com:3366
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'tidb60.com:3366' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}
MySQL tidb60:3366 ssl JS >
检查节点2：tidb61:3366实例
MySQL tidb60:3366 ssl JS > dba.checkInstanceConfiguration("dba@tidb61:3366");
Please provide the password for 'dba@tidb61:3366': ***
Validating MySQL instance at tidb61.com:3366 for use in an InnoDB cluster...
Please provide the password for 'dba@tidb61:3366': ***
Validating MySQL instance at tidb61.com:3366 for use in an InnoDB cluster...
This instance reports its own address as tidb61.com:3366
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
Instance configuration is compatible with InnoDB cluster
The instance 'tidb61.com:3366' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}
MySQL tidb60:3366 ssl JS >
"status": "ok"
}
MySQL tidb60:3366 ssl JS >
检查节点3：tidb62:3366实例
MySQL tidb60:3366 ssl JS > dba.checkInstanceConfiguration("dba@tidb62:3366");
Please provide the password for 'dba@tidb62:3366': ***
Validating MySQL instance at tidb62.com:3366 for use in an InnoDB cluster...
Please provide the password for 'dba@tidb62:3366': ***
Validating MySQL instance at tidb62.com:3366 for use in an InnoDB cluster...
This instance reports its own address as tidb62.com:3366
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
Instance configuration is compatible with InnoDB cluster
The instance 'tidb62.com:3366' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}
MySQL tidb60:3366 ssl JS >
"status": "ok"
}
MySQL tidb60:3366 ssl JS >
2.3 创建集群
可以在任一 一个节点上执行，根据测试发现初次加入集群的这节点最终是是master节点，即可以R/W
[root@tidb63 bin]# pwd
/home/jm/data/mysql-shell-8.0.20/bin
[root@tidb63 bin]# ./mysqlsh dba@tidb60:3366
Please provide the password for 'dba@tidb60:3366': ***
MySQL Shell 8.0.20
/home/jm/data/mysql-shell-8.0.20/bin
[root@tidb63 bin]# ./mysqlsh dba@tidb60:3366
Please provide the password for 'dba@tidb60:3366': ***
MySQL Shell 8.0.20
Copyright (c) 2016, 2020, 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.
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.
Creating a session to 'dba@tidb60:3366'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 37770
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL tidb60:3366 ssl JS > var cluster = dba.createCluster('lixd');
A new InnoDB cluster will be created on instance 'tidb60:3366'.
Creating a session to 'dba@tidb60:3366'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 37770
Server version: 8.0.20 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL tidb60:3366 ssl JS > var cluster = dba.createCluster('lixd');
A new InnoDB cluster will be created on instance 'tidb60:3366'.
Disabling super_read_only mode on instance 'tidb60.com:3366'.
Validating instance configuration at tidb60:3366...
Validating instance configuration at tidb60:3366...
This instance reports its own address as tidb60.com:3366
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'tidb60.com:33661'. Use the localAddress option to override.
NOTE: Group Replication will communicate with other members using 'tidb60.com:33661'. Use the localAddress option to override.
Creating InnoDB cluster 'lixd' on 'tidb60.com:3366'...
Adding Seed Instance...
NOTE: Metadata schema found in target 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.
NOTE: Metadata schema found in target 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.
MySQL tidb60:3366 ssl JS >
添加tidb61:3366实例到集群lixd中
MySQL tidb60:3366 ssl JS > cluster.addInstance('dba@tidb61:3366')
Please provide the password for 'dba@tidb61:3366': ***
Please provide the password for 'dba@tidb61:3366': ***
NOTE: The target instance 'tidb61.com:3366' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'tidb61.com:3366' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'tidb61.com:3366' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
NOTE: Group Replication will communicate with other members using 'tidb61.com:33661'. Use the localAddress option to override.
Validating instance configuration at tidb61:3366...
This instance reports its own address as tidb61.com:3366
Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
WARNING: User 'mysql_innodb_cluster_168613366'@'%' already existed at instance 'tidb60.com:3366'. It will be deleted and created again with a new password.
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: tidb61.com:3366 is being cloned from tidb60.com:3366
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY:
NOTE: tidb61.com:3366 is shutting down...
NOTE: tidb61.com:3366 is being cloned from tidb60.com:3366
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY:
NOTE: tidb61.com:3366 is shutting down...
* Waiting for server restart... ready
* tidb61.com:3366 has restarted, waiting for clone to finish...
* Clone process has finished: 1.12 GB transferred in 9 sec (125.00 MB/s)
* tidb61.com:3366 has restarted, waiting for clone to finish...
* Clone process has finished: 1.12 GB transferred in 9 sec (125.00 MB/s)
State recovery already finished for 'tidb61.com:3366'
The instance 'tidb61:3366' was successfully added to the cluster.
添加tidb62:3366实例到集群lixd中
MySQL tidb60:3366 ssl JS > cluster.addInstance('dba@tidb62:3366')
Please provide the password for 'dba@tidb62:3366': ***
Please provide the password for 'dba@tidb62:3366': ***
NOTE: The target instance 'tidb62.com:3366' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'tidb62.com:3366' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'tidb62.com:3366' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
NOTE: Group Replication will communicate with other members using 'tidb62.com:33661'. Use the localAddress option to override.
Validating instance configuration at tidb62:3366...
This instance reports its own address as tidb62.com:3366
Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
WARNING: User 'mysql_innodb_cluster_168623366'@'%' already existed at instance 'tidb60.com:3366'. It will be deleted and created again with a new password.
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: tidb62.com:3366 is being cloned from tidb61.com:3366
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: tidb62.com:3366 is being cloned from tidb61.com:3366
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: tidb62.com:3366 is shutting down...
* Waiting for server restart... ready
* tidb62.com:3366 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 1.13 GB transferred in 9 sec (125.00 MB/s)
* tidb62.com:3366 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 1.13 GB transferred in 9 sec (125.00 MB/s)
State recovery already finished for 'tidb62.com:3366'
The instance 'tidb62:3366' was successfully added to the cluster.
MySQL tidb60:3366 ssl JS >
查看集群的状态
MySQL tidb60:3366 ssl JS > var cluster = dba.getCluster('lixd')
MySQL tidb60:3366 ssl JS > cluster.status()
{
"clusterName": "lixd",
"defaultReplicaSet": {
"name": "default",
"primary": "tidb60.com:3366",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"tidb60.com:3366": {
"address": "tidb60.com:3366",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"tidb61.com:3366": {
"address": "tidb61.com:3366",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"tidb62.com:3366": {
"address": "tidb62.com:3366",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "tidb60.com:3366"
}
MySQL tidb60:3366 ssl JS >
MySQL tidb60:3366 ssl JS > cluster.status()
{
"clusterName": "lixd",
"defaultReplicaSet": {
"name": "default",
"primary": "tidb60.com:3366",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"tidb60.com:3366": {
"address": "tidb60.com:3366",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"tidb61.com:3366": {
"address": "tidb61.com:3366",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"tidb62.com:3366": {
"address": "tidb62.com:3366",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "tidb60.com:3366"
}
MySQL tidb60:3366 ssl JS >
mysqlroute 部署
[root@tidb63 bin]# pwd
/opt/mysql-route-8.0.20/bin
[root@tidb63 bin]# ./mysqlrouter --bootstrap dba@tidb60:3366 -d /opt/mysql-route-lixd --conf-use-sockets --user=mysql
Please enter MySQL password for dba:
# Bootstrapping MySQL Router instance at '/opt/mysql-route-lixd'...
/opt/mysql-route-8.0.20/bin
[root@tidb63 bin]# ./mysqlrouter --bootstrap dba@tidb60:3366 -d /opt/mysql-route-lixd --conf-use-sockets --user=mysql
Please enter MySQL password for dba:
# Bootstrapping MySQL Router instance at '/opt/mysql-route-lixd'...
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /opt/mysql-route-lixd/mysqlrouter.conf
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /opt/mysql-route-lixd/mysqlrouter.conf
# MySQL Router configured for the InnoDB Cluster 'lixd'
After this MySQL Router has been started with the generated configuration
$ ./mysqlrouter -c /opt/mysql-route-lixd/mysqlrouter.conf
the cluster 'lixd' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446, /opt/mysql-route-lixd/mysql.sock
- Read/Only Connections: localhost:6447, /opt/mysql-route-lixd/mysqlro.sock
- Read/Only Connections: localhost:6447, /opt/mysql-route-lixd/mysqlro.sock
## MySQL X protocol
- Read/Write Connections: localhost:64460, /opt/mysql-route-lixd/mysqlx.sock
- Read/Only Connections: localhost:64470, /opt/mysql-route-lixd/mysqlxro.sock
- Read/Only Connections: localhost:64470, /opt/mysql-route-lixd/mysqlxro.sock
[root@tidb63 bin]#
启动mysqlroute
[root@tidb63 mysql-route-lixd]# pwd
/opt/mysql-route-lixd
[root@tidb63 mysql-route-lixd]# ls -l
总用量 16
drwx------. 2 mysql mysql 39 5月 9 15:06 data
drwx------. 2 mysql mysql 29 5月 9 15:06 log
-rw-------. 1 mysql mysql 1400 5月 9 15:06 mysqlrouter.conf
-rw-------. 1 mysql mysql 92 5月 9 15:06 mysqlrouter.key
drwx------. 2 mysql mysql 6 5月 9 15:06 run
-rwx------. 1 mysql mysql 329 5月 9 15:06 start.sh
-rwx------. 1 mysql mysql 173 5月 9 15:06 stop.sh
[root@tidb63 mysql-route-lixd]# sh start.sh
[root@tidb63 mysql-route-lixd]# PID 124156 written to '/opt/mysql-route-lixd/mysqlrouter.pid'
logging facility initialized, switching logging to loggers specified in configuration
/opt/mysql-route-lixd
[root@tidb63 mysql-route-lixd]# ls -l
总用量 16
drwx------. 2 mysql mysql 39 5月 9 15:06 data
drwx------. 2 mysql mysql 29 5月 9 15:06 log
-rw-------. 1 mysql mysql 1400 5月 9 15:06 mysqlrouter.conf
-rw-------. 1 mysql mysql 92 5月 9 15:06 mysqlrouter.key
drwx------. 2 mysql mysql 6 5月 9 15:06 run
-rwx------. 1 mysql mysql 329 5月 9 15:06 start.sh
-rwx------. 1 mysql mysql 173 5月 9 15:06 stop.sh
[root@tidb63 mysql-route-lixd]# sh start.sh
[root@tidb63 mysql-route-lixd]# PID 124156 written to '/opt/mysql-route-lixd/mysqlrouter.pid'
logging facility initialized, switching logging to loggers specified in configuration
[root@tidb63 mysql-route-lixd]#
[root@tidb63 mysql-route-lixd]#
[root@tidb63 mysql-route-lixd]#
查看route对应的日志
[root@tidb63 log]# pwd
/opt/mysql-route-lixd/log
[root@tidb63 log]# more mysqlrouter.log
2020-05-09 15:08:25 routing INFO [7f929cc76700] [routing:lixd_ro] started: listening on 0.0.0.0:6447, routing strategy = round-robin-with-fallback
2020-05-09 15:08:25 routing INFO [7f92977fe700] [routing:lixd_x_ro] started: listening on 0.0.0.0:64470, routing strategy = round-robin-with-fallback
2020-05-09 15:08:25 routing INFO [7f9296ffd700] [routing:lixd_x_rw] started: listening on 0.0.0.0:64460, routing strategy = first-available
2020-05-09 15:08:25 routing INFO [7f9297fff700] [routing:lixd_rw] started: listening on 0.0.0.0:6446, routing strategy = first-available
2020-05-09 15:08:25 routing INFO [7f929cc76700] [routing:lixd_ro] started: listening using /opt/mysql-route-lixd/mysqlro.sock
2020-05-09 15:08:25 routing INFO [7f92977fe700] [routing:lixd_x_ro] started: listening using /opt/mysql-route-lixd/mysqlxro.sock
2020-05-09 15:08:25 routing INFO [7f9296ffd700] [routing:lixd_x_rw] started: listening using /opt/mysql-route-lixd/mysqlx.sock
2020-05-09 15:08:25 routing INFO [7f9297fff700] [routing:lixd_rw] started: listening using /opt/mysql-route-lixd/mysql.sock
2020-05-09 15:08:25 metadata_cache INFO [7f929d477700] Starting Metadata Cache
2020-05-09 15:08:25 metadata_cache INFO [7f929d477700] Connections using ssl_mode 'PREFERRED'
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] Starting metadata cache refresh thread
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] Potential changes detected in cluster 'lixd' after metadata refresh
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] Metadata for cluster 'lixd' has 1 replicasets:
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] 'default' (3 members, single-master)
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] tidb60.com:3366 / 33060 - role=HA mode=RW
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] tidb61.com:3366 / 33060 - role=HA mode=RO
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] tidb62.com:3366 / 33060 - role=HA mode=RO
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_x_ro listening on 64470 and named socket /opt/mysql-route-lixd/mysqlxro.sock got request to disconnect invalid connections
: metadata change
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_x_rw listening on 64460 and named socket /opt/mysql-route-lixd/mysqlx.sock got request to disconnect invalid connections:
metadata change
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_rw listening on 6446 and named socket /opt/mysql-route-lixd/mysql.sock got request to disconnect invalid connections: meta
data change
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_ro listening on 6447 and named socket /opt/mysql-route-lixd/mysqlro.sock got request to disconnect invalid connections: me
tadata change
[root@tidb63 log]#
/opt/mysql-route-lixd/log
[root@tidb63 log]# more mysqlrouter.log
2020-05-09 15:08:25 routing INFO [7f929cc76700] [routing:lixd_ro] started: listening on 0.0.0.0:6447, routing strategy = round-robin-with-fallback
2020-05-09 15:08:25 routing INFO [7f92977fe700] [routing:lixd_x_ro] started: listening on 0.0.0.0:64470, routing strategy = round-robin-with-fallback
2020-05-09 15:08:25 routing INFO [7f9296ffd700] [routing:lixd_x_rw] started: listening on 0.0.0.0:64460, routing strategy = first-available
2020-05-09 15:08:25 routing INFO [7f9297fff700] [routing:lixd_rw] started: listening on 0.0.0.0:6446, routing strategy = first-available
2020-05-09 15:08:25 routing INFO [7f929cc76700] [routing:lixd_ro] started: listening using /opt/mysql-route-lixd/mysqlro.sock
2020-05-09 15:08:25 routing INFO [7f92977fe700] [routing:lixd_x_ro] started: listening using /opt/mysql-route-lixd/mysqlxro.sock
2020-05-09 15:08:25 routing INFO [7f9296ffd700] [routing:lixd_x_rw] started: listening using /opt/mysql-route-lixd/mysqlx.sock
2020-05-09 15:08:25 routing INFO [7f9297fff700] [routing:lixd_rw] started: listening using /opt/mysql-route-lixd/mysql.sock
2020-05-09 15:08:25 metadata_cache INFO [7f929d477700] Starting Metadata Cache
2020-05-09 15:08:25 metadata_cache INFO [7f929d477700] Connections using ssl_mode 'PREFERRED'
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] Starting metadata cache refresh thread
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] Potential changes detected in cluster 'lixd' after metadata refresh
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] Metadata for cluster 'lixd' has 1 replicasets:
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] 'default' (3 members, single-master)
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] tidb60.com:3366 / 33060 - role=HA mode=RW
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] tidb61.com:3366 / 33060 - role=HA mode=RO
2020-05-09 15:08:25 metadata_cache INFO [7f92a05b2700] tidb62.com:3366 / 33060 - role=HA mode=RO
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_x_ro listening on 64470 and named socket /opt/mysql-route-lixd/mysqlxro.sock got request to disconnect invalid connections
: metadata change
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_x_rw listening on 64460 and named socket /opt/mysql-route-lixd/mysqlx.sock got request to disconnect invalid connections:
metadata change
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_rw listening on 6446 and named socket /opt/mysql-route-lixd/mysql.sock got request to disconnect invalid connections: meta
data change
2020-05-09 15:08:25 routing INFO [7f92a05b2700] Routing routing:lixd_ro listening on 6447 and named socket /opt/mysql-route-lixd/mysqlro.sock got request to disconnect invalid connections: me
tadata change
[root@tidb63 log]#
通过mysqlroute访问mysql读操作（端口6447，可以见route对应的日志中的提示）
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6447 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb61.com |
+------------+
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6447 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb62.com |
+------------+
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6447 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb61.com |
+------------+
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6447 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb62.com |
+------------+
[root@tidb63 ~]#
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb61.com |
+------------+
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6447 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb62.com |
+------------+
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6447 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb61.com |
+------------+
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6447 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb62.com |
+------------+
[root@tidb63 ~]#
轮询访问每个只读的实例，这里是轮询访问tidb61:3366和tidb62:3366实例。
通过route访问master实例（端口是6446，可以在router日志里的提示查看）
[root@tidb63 ~]# mysql -htidb63 -udba -pdba -P6446 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb60.com |
+------------+
[root@tidb63 ~]#
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| tidb60.com |
+------------+
[root@tidb63 ~]#
到此，一个简单mysql innodb cluster环境部署完成。