vlambda博客
学习文章列表

每个节点设置hostnamectl set-hostname mgr1hostnamectl set-hostname mgr2hostnamectl set-hostname mgr3

 [root@localhost 3306]# cat /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 localhost localhost.localdomain localhost6 localhost6.localdomain610.0.2.31 mgr110.0.2.32 mgr210.0.2.33 mgr3

### my.cnf for 8.0版本#[client]port = 3306socket = /data/mysql_mgr/3306/mysql.sock

[mysqld]user = mysqlport = 3306#主从复制或MGR集群中，server_id记得要不同#另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同#server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以server_id = 313306basedir = /usr/local/mysql_8datadir = /data/mysql_mgr/3306/datasocket = /data/mysql_mgr/3306/mysql.sockpid_file = mysqldb.pidcharacter_set_server = UTF8MB4skip_name_resolve = 1#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数default_time_zone = "+8:00"#启用admin_port，连接数爆满等紧急情况下给管理员留个后门admin_address = '127.0.0.1'admin_port = 33062mysqlx_port=33060#lower_case_table_names=1

#user authdefault_authentication_plugin = mysql_native_password

#performance setttingslock_wait_timeout = 3600open_files_limit = 65535back_log = 1024max_connections = 512max_connect_errors = 1000000table_open_cache = 1024table_definition_cache = 1024thread_stack = 512Ksort_buffer_size = 4Mjoin_buffer_size = 4Mread_buffer_size = 8Mread_rnd_buffer_size = 4Mbulk_insert_buffer_size = 64Mthread_cache_size = 768interactive_timeout = 600wait_timeout = 600tmp_table_size = 32Mmax_heap_table_size = 32M
#log settingslog_timestamps = SYSTEMlog_error = /data/mysql_mgr/3306/data/error.loglog_error_verbosity = 3slow_query_log = 1log_slow_extra = 1slow_query_log_file = /data/mysql_mgr/3306/slowlog/slow.loglong_query_time = 0.1log_queries_not_using_indexes = 1log_throttle_queries_not_using_indexes = 60min_examined_row_limit = 100log_slow_admin_statements = 1log_slow_slave_statements = 1log_bin = /data/mysql_mgr/3306/binlog/mybinlogbinlog_format = ROWsync_binlog = 1 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gbinlog_rows_query_log_events = 1binlog_expire_logs_seconds = 604800#MySQL 8.0.22前，想启用MGR的话，需要设置binlog_checksum=NONE才行binlog_checksum = CRC32gtid_mode = ONenforce_gtid_consistency = TRUE
#myisam settingskey_buffer_size = 32Mmyisam_sort_buffer_size = 128M
#replication settingsrelay_log_recovery = 1slave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = 2 #可以设置为逻辑CPU数量的2倍binlog_transaction_dependency_tracking = WRITESETslave_preserve_commit_order = 1slave_checkpoint_period = 2
#mgr settingsloose-plugin_load_add = 'mysql_clone.so'loose-plugin_load_add = 'group_replication.so'#loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"loose-group_replication_group_name= "014a705d-9def-45b0-997f-327e46158102"#MGR本地节点IP:PORT，请自行替换loose-group_replication_local_address = "10.0.2.31:33061"#MGR集群所有节点IP:PORT，请自行替换loose-group_replication_group_seeds = "10.0.2.31:33061,10.0.2.32:33061,10.0.2.33:33061,10.0.2.31:33061"loose-group_replication_start_on_boot = OFFloose-group_replication_bootstrap_group = OFFloose-group_replication_exit_state_action = READ_ONLYloose-group_replication_flow_control_mode = "DISABLED"loose-group_replication_single_primary_mode = ONloose-group_replication_start_on_boot = off


#innodb settingstransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 512Minnodb_buffer_pool_instances = 1innodb_data_file_path = ibdata1:12M:autoextendinnodb_flush_log_at_trx_commit = 0 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能innodb_log_buffer_size = 32Minnodb_log_file_size = 256M #如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小innodb_log_files_in_group = 3innodb_max_undo_log_size = 1G# 根据您的服务器IOPS能力适当调整# 一般配普通SSD盘的话，可以调整到 10000 - 20000# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_open_files = 65535innodb_flush_method = O_DIRECTinnodb_lru_scan_depth = 4000innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_online_alter_log_max_size = 4Ginnodb_print_ddl_logs = 1innodb_status_file = 1#注意: 开启 innodb_status_output & innodb_status_output_locks 后,可能能会导致log_error文件增长较快innodb_status_output = 0innodb_status_output_locks = 1innodb_sort_buffer_size = 67108864innodb_adaptive_hash_index = OFF
#innodb monitor settingsinnodb_monitor_enable = "module_innodb"innodb_monitor_enable = "module_server"innodb_monitor_enable = "module_dml"innodb_monitor_enable = "module_ddl"innodb_monitor_enable = "module_trx"innodb_monitor_enable = "module_os"innodb_monitor_enable = "module_purge"innodb_monitor_enable = "module_log"innodb_monitor_enable = "module_lock"innodb_monitor_enable = "module_buffer"innodb_monitor_enable = "module_index"innodb_monitor_enable = "module_ibuf_system"innodb_monitor_enable = "module_buffer_page"#innodb_monitor_enable = "module_adaptive_hash"
#pfs settingsperformance_schema = 1#performance_schema_instrument = '%memory%=on'performance_schema_instrument = '%lock%=on'
## disables engine disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMOR"
[mysqldump]quick

port=3306server_id=313306loose-group_replication_group_name= "014a705d-9def-45b0-997f-327e46158102"
loose-group_replication_local_address = "10.0.2.31:33061"
loose-group_replication_group_seeds = "10.0.2.31:33061,10.0.2.32:33061,10.0.2.33:33061,10.0.2.31:33061"group_replication_start_on_boot=offgroup_replication_bootstrap_group=off

set sql_log_bin=0;create user 'myshell'@'%' identified by 'myshell!123';GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'myshell'@'%' WITH GRANT OPTION;GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'myshell'@'%' WITH GRANT OPTION;GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'myshell'@'%' WITH GRANT OPTION;GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'myshell'@'%' WITH GRANT OPTION;GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'myshell'@'%' WITH GRANT OPTION;set sql_log_bin=1;

mysql> show plugins;+---------------------------------+----------+--------------------+----------------------+---------+| Name | Status | Type | Library | License |+---------------------------------+----------+--------------------+----------------------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL || mysqlx | ACTIVE | DAEMON | NULL | GPL |········| clone | ACTIVE | CLONE | mysql_clone.so | GPL || group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |+---------------------------------+----------+--------------------+----------------------+---------+47 rows in set (0.00 sec)

[root@localhost opt]# tar -xzf mysql-shell-8.0.27-linux-glibc2.12-x86-64bit.tar.gz [root@localhost opt]# lsmysql-8.0.27-linux-glibc2.12-x86_64 mysql-shell-8.0.27-linux-glibc2.12-x86-64bit mysql-shell-8.0.27-linux-glibc2.12-x86-64bit.tar.gz[root@localhost opt]# ln -s /opt/mysql-shell-8.0.27-linux-glibc2.12-x86-64bit /usr/local/mysql_shell && echo 'export PATH=/usr/local/mysql_shell/bin:$PATH' >> /etc/profile [root@localhost opt]# ll /usr/local/[root@localhost opt]# mysqlsh

[root@mgr1 3306]# mysqlsh --uri [email protected]:3306 Please provide the password for '[email protected]:3306': ***********Save password for '[email protected]:3306'? [Y]es/[N]o/Ne[v]er (default No): yMySQL Shell 8.0.27 ### 生产环境不建议保存密码
Copyright (c) 2016, 2021, Oracle and/or its affiliates.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 '[email protected]:3306'Fetching schema names for autocompletion... Press ^C to stop.Your MySQL connection id is 12Server version: 8.0.27 MySQL Community Server - GPLNo default schema selected; type \use <schema> to set one. MySQL 10.0.2.31:3306 ssl JS >

[root@mgr1 3306]# mysqlsh --uri [email protected]:3306
 MySQL 10.0.2.31:3306 ssl JS > dba.configureInstance();  ### 进行检测该节点是否满足Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as mgr1:3306Clients 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.
applierWorkerThreads will be set to the default value of 4.
The instance 'mgr1:3306' is valid to be used in an InnoDB cluster.The instance 'mgr1:3306' is already ready to be used in an InnoDB cluster.
Successfully enabled parallel appliers. MySQL 10.0.2.31:3306 ssl JS >

MySQL 10.0.2.31:3306 ssl JS > \connect myshell@10.0.2.32:3306Creating a session to '[email protected]:3306'Please provide the password for '[email protected]:3306': ***********Save password for '[email protected]:3306'? [Y]es/[N]o/Ne[v]er (default No): y
 MySQL 10.0.2.32:3306 ssl JS > dba.configureInstance();

MySQL 10.0.2.32:3306 ssl JS > \connect myshell@10.0.2.33:3306Creating a session to '[email protected]:3306'Please provide the password for '[email protected]:3306': ***********Save password for '[email protected]:3306'? [Y]es/[N]o/Ne[v]er (default No): yMySQL 10.0.2.33:3306 ssl JS > dba.configureInstance();

MySQL 10.0.2.31:3306 ssl JS > var l = dba.createCluster('sunday_lee');A new InnoDB cluster will be created on instance '10.0.2.31:3306'.
Validating instance configuration at 10.0.2.31:3306...
This instance reports its own address as mgr1:3306
Instance configuration is suitable.NOTE: Group Replication will communicate with other members using 'mgr1:33061'. Use the localAddress option to override.
Creating InnoDB cluster 'sunday_lee' on 'mgr1:3306'...
Adding Seed 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 toone server failure.

 MySQL 10.0.2.31:3306 ssl JS > var l =dba.getClustergetCluster() getClusterSet() MySQL 10.0.2.31:3306 ssl JS > var l =dba.getCluster(); MySQL 10.0.2.31:3306 ssl JS > l<Cluster:sunday_lee>

 MySQL 10.0.2.31:3306 ssl JS > l.addInstance('10.0.2.32:3306')##### 添加节点命令，可自动补全


WARNING: A GTID set check of the MySQL instance at 'mgr2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
mgr2:3306 has the following errant GTIDs that do not exist in the cluster:96c9d0ee-a01e-11ec-b610-080027cdfa8a:1
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of mgr2:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
Please select a recovery method [C]lone/[A]bort (default Abort): C##### 选择源端节点recovery 方式

Validating instance configuration at 10.0.2.32:3306...
This instance reports its own address as mgr2:3306
Instance configuration is suitable.NOTE: Group Replication will communicate with other members using 'mgr2:33061'. Use the localAddress option to override.
A new instance will be added to the InnoDB cluster. Depending on the amount ofdata on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
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 theserver does not support the RESTART command or does not come back after awhile, you may need to manually start it back.
* Waiting for clone to finish...NOTE: mgr2:3306 is being cloned from mgr1:3306** Stage DROP DATA: Completed ** Clone Transfer  FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed
NOTE: mgr2:3306 is shutting down...
* Waiting for server restart... ready * mgr2:3306 has restarted, waiting for clone to finish...** Stage RESTART: Completed* Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s)
State recovery already finished for 'mgr2:3306'
The instance 'mgr2:3306' was successfully added to the cluster.

 MySQL 10.0.2.31:3306 ssl JS > l.addInstance('10.0.2.33:3306')
Please select a recovery method [C]lone/[A]bort (default Abort): C..........State recovery already finished for 'mgr3:3306'
The instance 'mgr3:3306' was successfully added to the cluster

l.describe(); # 查看集群结构l.status(); #查看集群详细状态

[root@mgr2 soft]# tar xf mysql-router-8.0.27-linux-glibc2.12-x86_64.tar.xz -C /opt/[root@mgr1 local]# useradd mysqlrouter[root@mgr1 local]# ln -s /opt/mysql-router-8.0.27-linux-glibc2.12-x86_64/ /usr/local/mysql_router
[root@mgr2 local]# chown -R mysqlrouter.mysqlrouter mysql_router/[root@mgr2 local]# chown -R mysqlrouter.mysqlrouter mysql_router

export PATH=$PATH:/usr/local/mysql_8/binexport PATH=/usr/local/mysql_shell/bin:/usr/local/mysql_router/bin:$PATH

[root@mgr1 mysql_router]# mysqlrouter --bootstrap [email protected]:3306 --user=mysqlrouterPlease enter MySQL password for myshell: # Bootstrapping system MySQL Router instance...
- Creating account(s) (only those that are needed, if any)- Using existing certificates from the '/opt/mysql-router-8.0.27-linux-glibc2.12-x86_64/var/lib/mysqlrouter' directory- 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-router-8.0.27-linux-glibc2.12-x86_64/mysqlrouter.conf
Existing dynamic state backed up to '/opt/mysql-router-8.0.27-linux-glibc2.12-x86_64/var/lib/mysqlrouter/state.json.bak'
# MySQL Router configured for the InnoDB Cluster 'sunday_lee'
After this MySQL Router has been started with the generated configuration
 $ /etc/init.d/mysqlrouter restartor $ systemctl start mysqlrouteror $ mysqlrouter -c /opt/mysql-router-8.0.27-linux-glibc2.12-x86_64/mysqlrouter.conf
InnoDB Cluster 'sunday_lee' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:6448- Read/Only Connections: localhost:6449-

[root@mgr1 3306]# cd /usr/local/mysql_router/

[root@mgr1 mysql_router]# ls mysqlrouter.conf mysqlrouter.key mysqlrouter.log

 $ mysqlrouter -c /opt/mysql-router-8.0.27-linux-glibc2.12-x86_64/mysqlrouter.conf &

 [root@mgr1 mysql_router]# mysqlsh --uri [email protected]:3306  MySQL 10.0.2.31:3306 ssl JS > var l=dba.getCluster()  MySQL 10.0.2.31:3306 ssl JS > l.status(); { "clusterName": "sunday_lee",  "defaultReplicaSet": { "name": "default",  "primary": "mgr1:3306",  "ssl": "REQUIRED",  "status": "OK",  "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",  "topology": { "mgr1:3306": { "address": "mgr1:3306",  "memberRole": "PRIMARY", ### 角色 "mode": "R/W",  "readReplicas": {},  "replicationLag": null,  "role": "HA",  "status": "ONLINE",  "version": "8.0.27" },  "mgr2:3306": { "address": "mgr2:3306",  "memberRole": "SECONDARY",  "mode": "R/O",  "readReplicas": {},  "replicationLag": null,  "role": "HA",  "status": "ONLINE",  "version": "8.0.27"...... "topologyMode": "Single-Primary" },  "groupInformationSourceMember": "mgr1:3306"}

## MySQL Classic protocol
- Read/Write Connections: localhost:6446- Read/Only Connections: localhost:6447

[root@mgr1 mysql_router]# mysql -h 10.0.2.31 -P 6446 -utest -ptest
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6310Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> create database tb1;Query OK, 1 row affected (0.03 sec)
MySQL [(none)]> use tb1;Database changedMySQL [tb1]> create table t1(id int ,primary key(id));Query OK, 0 rows affected (0.05 sec)MySQL [tb1]> exitBye

[root@mgr1 mysql_router]# mysql -h 10.0.2.31 -P 6447 -utest -ptest
Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 54Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> insert into tb1.t1 select 1;ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statementMySQL [(none)]> select * from tb1.t1;Empty set (0.01 sec)