目录
2.4 创建mysql日志、binlog 等文件存放路径及授权
6.1 loose-group_replication_group_name不一致
一 环境配置
1.1 版本说明
| 软硬件信息 | 参数 | 下载地址 |
| 配置 | 8C/32G | |
| 操作系统版本 | CentOS Linux release 7.6.1810 (Core) | |
| 内核版本 | 3.10.0-957.el7.x86_64 | |
| MySQL 版本 | MySQL 8.0.22 | https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar |
| MySQL Shell版本 | MySQL 8.0.22 | https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.22-1.el7.x86_64.rpm |
| MySQL Router版本 | MySQL 8.0.22 | https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.0.22-1.el7.x86_64.rpm |
1.2 服务规划
| 服务器 | IP | 端口 | 角色 |
| node1 | 172.29.38.221 | 6446 | shell，router |
| node2 | 172.29.38.222 | 3306 | master |
| node3 | 172.29.38.223 | 3306 | Slave |
| node4 | 172.29.38.224 | 3306 | Slave |
1.3 关闭selinux和防火墙
所有节点都执行下述操作
systemctl stop firewalld
systemctl disable firewalld
setenforce 0 && sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config1.4 配置IP域名映射
cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.29.38.221 node1
172.29.38.222 node2
172.29.38.223 node3
172.29.38.224 node41.5 更改I/O调度模式
echo deadline > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler1.6 操作系统调优
cat>>/etc/sysctl.conf <<EOF
vm.swappiness = 0
fs.aio-max-nr = 1048576
fs.file-max = 681574400
kernel.shmmax = 137438953472
kernel.shmmni = 4096
kernel.sem = 250 32000 100 200
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
cat>>/etc/security/limits.conf <<EOF
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
EOF
cat>>/etc/profile<<EOF
if [ $USER = "mysql" ]; then
ulimit -u 16384 -n 65536
fi
EOF
二 部署Mysql
2.1 下载mysql安装包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
2.2 rpm 安装mysql，执行顺序不能出错
rpm -ivh mysql-community-common-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.22-1.el7.x86_64.rpm
2.3 初始化mysql 服务
mysqld --initialize --console
2.3 授权mysql 目录权限
chown -R mysql:mysql /var/lib/mysql/2.4 初始化mysql服务并修改账号密码
mysqld --initialize --console
systemctl start mysqld2.5 查看mysql默认密码
2.4 创建mysql日志、binlog 等文件存放路径及授权
mkdir -pv /var/log/mysql/{data,logs,binlog,tmp,redo}
chown -R mysql:mysql /var/log/mysql/2.5 修改mysql 配置 /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysql]
prompt ="\u@mysql \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
user = mysql
port = 3306
default_authentication_plugin = mysql_native_password
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
slow_query_log_file = /var/log/mysql/mysql_slow_query.log
log-error = /var/log/mysql/mysqld.log
log-bin = /var/log/mysql/binlog/binlog
relay_log = /var/log/mysql/binlog/relaylog
innodb_log_group_home_dir = /var/log/mysql/redo/
innodb_redo_log_archive_dirs = /var/log/mysql/redo
innodb_doublewrite = 1
pid-file = mysql.pid
character-set-server = utf8
skip_name_resolve = 1
default_time_zone = "+8:00"
open_files_limit = 65535
back_log = 1024
max_connections = 1000
max_user_connections = 1000
max_connect_errors = 10000
autocommit = 1
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 4
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
innodb_sort_buffer_size = 64M
thread_cache_size = 384
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 6
log_timestamps = SYSTEM
long_query_time = 0.1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
auto_increment_offset = 1
auto_increment_increment = 1
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_buffer_pool_load_at_startup = ON
innodb_old_blocks_time = 1000
innodb_thread_concurrency = 0
innodb_compression_level = 6
innodb_adaptive_max_sleep_delay = 150000
innodb_lock_wait_timeout = 50
innodb_purge_threads = 1
innodb_compression_failure_threshold_pct = 5
innodb_compression_pad_pct_max = 50
innodb_online_alter_log_max_size = 134217728
innodb_purge_rseg_truncate_frequency = 128
innodb_stats_persistent_sample_pages = 20
innodb_ft_total_cache_size = 640000000
innodb_purge_batch_size = 300
innodb_sync_array_size = 1
innodb_stats_method = nulls_equal
innodb_flush_method = O_DIRECT
innodb_deadlock_detect = ON
innodb_write_io_threads = 4
innodb_checksum_algorithm = crc32
innodb_table_locks = ON
innodb_stats_persistent = ON
innodb_ft_enable_diag_print = OFF
innodb_ft_enable_stopword = ON
innodb_io_capacity = 20000
innodb_max_dirty_pages_pct = 75
innodb_disable_sort_file_cache = ON
innodb_lru_scan_depth = 1024
innodb_ft_result_cache_limit = 2000000000
innodb_ft_cache_size = 8000000
innodb_read_io_threads = 4
innodb_autoextend_increment = 64
innodb_stats_transient_sample_pages = 8
innodb_random_read_ahead = OFF
innodb_status_output = OFF
innodb_log_compressed_pages = OFF
innodb_open_files = 3000
innodb_buffer_pool_dump_at_shutdown = ON
innodb_max_purge_lag = 0
innodb_buffer_pool_dump_pct = 25
innodb_max_dirty_pages_pct_lwm = 0
innodb_print_all_deadlocks = OFF
innodb_change_buffer_max_size = 25
innodb_optimize_fulltext_only = OFF
innodb_ft_max_token_size = 84
innodb_status_output_locks = OFF
innodb_adaptive_hash_index = OFF
innodb_sync_spin_loops = 30
innodb_max_undo_log_size = 1073741824
innodb_read_ahead_threshold = 56
innodb_thread_sleep_delay = 10000
innodb_old_blocks_pct = 37
innodb_adaptive_flushing_lwm = 10
innodb_log_checksums = ON
innodb_ft_sort_pll_degree = 2
innodb_stats_on_metadata = OFF
innodb_sort_buffer_size = 1048576
innodb_page_cleaners = 1
innodb_buffer_pool_size = 20480M
innodb_spin_wait_delay = 6
innodb_rollback_segments = 128
innodb_change_buffering = all
innodb_flush_sync = ON
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 5000
innodb_io_capacity_max = 40000
innodb_strict_mode = OFF
innodb_ft_min_token_size = 3
innodb_autoinc_lock_mode = 2
innodb_rollback_on_timeout = OFF
innodb_ft_num_word_optimize = 2000
innodb_max_purge_lag_delay = 0
innodb_cmp_per_index_enabled = OFF
innodb_stats_auto_recalc = ON
innodb_adaptive_flushing = ON
innodb_flush_neighbors = 0
innodb_buffer_pool_instances = 8
loose_multi_blocks_ddl_count=0
loose_performance_schema_max_table_instances=1000
loose_performance_schema_max_index_stat=10000
loose_ccl_queue_hot_delete=OFF
loose_rds_audit_log_event_buffer_size=2048
loose_recycle_bin=OFF
loose_innodb_log_optimize_ddl=ON
loose_performance_schema_max_prepared_statements_instances=1000
loose_innodb_log_write_ahead_size=4096
loose_performance_schema_max_socket_instances=1000
loose_performance_schema_error_size=10000
loose_performance_schema_accounts_size=10000
loose_sql_safe_updates=OFF
loose_optimizer_trace_features=greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
loose_performance_schema_hosts_size=10000
loose_ccl_queue_bucket_count=4
loose_innodb_numa_interleave=ON
loose_performance_schema_max_cond_instances=10000
loose_performance_schema_max_table_lock_stat=10000
loose_performance_schema_max_table_handles=10000
loose_ignore_index_hint_error=OFF
loose_performance_schema_max_metadata_locks=10000
loose_innodb_rds_faster_ddl=ON
loose_performance_schema_setup_actors_size=10000
loose_performance_schema_setup_objects_size=10000
loose_rds_kill_connections=20
loose_performance_schema_digests_size=10000
loose_opt_rds_last_error_gtid=ON
loose_performance_schema_users_size=10000
loose_performance_schema_max_mutex_instances=10000
loose_internal_tmp_mem_storage_engine=MEMORY
loose_thread_pool_enabled=ON
loose_optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
loose_performance_schema_max_program_instances=10000
loose_information_schema_stats_expiry=86400
loose_ccl_queue_bucket_size=64
loose_ccl_queue_hot_update=OFF
loose_thread_pool_size=2
loose_performance_schema_max_rwlock_instances=10000
loose_performance_schema_max_file_instances=1000
loose_validate_password_length=8
loose_thread_pool_oversubscribe=32
loose_innodb_parallel_read_threads=1
loose_recycle_bin_retention=604800
loose_opt_rds_enable_show_slave_lag=ON
loose_performance_schema_max_thread_instances=10000
loose_optimizer_trace=enabled=off,one_line=off
slave_preserve_commit_order = 1
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 604800
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 0
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
innodb_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"
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 2
binlog_group_commit_sync_no_delay_count = 10
log-slave-updates = 1
binlog_transaction_dependency_tracking = writeset_session
binlog_checksum = NONE
master_info_repository = TABLE
relay_log_info_repository = TABLE
server-id = 1
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = '9ac06b4e-13aa-11e7-a62e-5254004347f9'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '172.29.38.222:33061'
loose-group_replication_group_seeds = '172.29.38.222:33061,172.29.38.223:33061,172.29.38.224:33061'
loose-group_replication_bootstrap_group = OFF
[mysqldump]
quick
max_allowed_packet = 64M
2.6 修改mysql默认密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
#设置允许远程访问
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;三 配置MGR
3.1 配置MGR信息
#以下步骤所有node2、node3、node4节点均执行
mysql #登陆数据库
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安装插件
mysql> SET SQL_LOG_BIN=0; #关闭binlog
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl'; #创建复制账号
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; #授权
mysql> FLUSH PRIVILEGES; #刷新权限
mysql> SET SQL_LOG_BIN=1; #开启binlog
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; #创建复制
3.2 启动MGR
# 启动MGR，在node2上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
将其他节点加入MGR，在从库172.29.38.223、172.29.38.224上执行
mysql> START GROUP_REPLICATION;
#查看MGR组信息
root@mysql 17:50: [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 134c4465-f8c2-11eb-9f37-005056b05884 | node3 | 3306 | ONLINE | SECONDARY | 8.0.22 |
| group_replication_applier | 24f28e5a-f8c0-11eb-bb9e-005056b073fa | node2 | 3306 | ONLINE | PRIMARY | 8.0.22 |
| group_replication_applier | 4d5cb37f-fb0d-11eb-8e91-005056b03386 | node1 | 3306 | ONLINE | SECONDARY | 8.0.22 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
四 配置 Innodb Cluster
4.1 创建MySQL Shell管理账号
create user 'dba'@'%' identified by 'dba';
grant all on *.* to 'dba'@'%' with grant option;
FLUSH PRIVILEGES;
4.2 安装mysql-shell
rpm -ivh mysql-shell-8.0.22-1.el7.x86_64.rpm4.3 连接master节点
[root@node1 ~]# mysqlsh -h 172.29.38.222 -udba
MySQL Shell 8.0.22
Copyright (c) 2016, 2020, 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 'dba@172.29.38.222'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 293754 (X protocol)
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL 172.29.38.222:33060+ ssl JS >
4.4 检查实例配置
AdminAPI提供dba.checkInstanceConfiguration()方法以验证实例是否满足组复制要求，该方法不会更改实例上的任何配置，也不会检查实例上的任何数据。下面是检查172.29.38.222:3306的命令和输出
MySQL 172.29.38.222:3306 ssl JS > dba.checkInstanceConfiguration('dba@172.29.38.222:3306')
Please provide the password for 'dba@172.29.38.222:3306': ***
Save password for 'dba@172.29.38.222:3306'? [Y]es/[N]o/Ne[v]er (default No):
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as node2:3306
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 'node2:3306' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}
4.5 创建集群
准备好实例后，使用dba.createCluster()函数创建集群，参数为自定义的集群名称。在创建集群之前，MySQL Shell必须连接到一个实例，并将该实例用作群集的种子实例。种子实例将被复制到添加到群集的其它实例，从而使它们成为种子实例的副本。当发出dba.createCluster(name)时，MySQL Shell会创建与连接到的服务器实例的会话。下面是以172.29.38.222作为种子实例创建名为testCluster集群的命令及输出：
MySQL 172.29.38.222:3306 ssl JS > dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance '172.29.38.222:3306'.
Validating instance configuration at 172.29.38.222:3306...
This instance reports its own address as node2:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'node2:33061'. Use the localAddress option to override.
Creating InnoDB cluster 'testCluster' on 'node2: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 to
one server failure.
<Cluster:testCluster>
4.5 加入slave节点
使用Cluster.addInstance(instance)函数向集群添加更多实例，其中instance是已配置实例的连接信息。下面是将172.29.38.222:3306实例添加到集群的命令及输出：
MySQL 172.29.38.222:3306 ssl JS > var cluster = dba.getCluster()
MySQL 172.29.38.222:3306 ssl JS > cluster.addInstance('dba@172.29.38.223:3306')
Please provide the password for 'dba@172.29.38.223:3306': ***
Save password for 'dba@172.29.38.223:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: A GTID set check of the MySQL instance at 'node3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
node3:3306 has the following errant GTIDs that do not exist in the cluster:
d58a4141-c415-11ea-99a1-000c29652de7:1-3
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of node2: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
NOTE: Group Replication will communicate with other members using 'node2:33061'. Use the localAddress option to override.
Validating instance configuration at 172.29.38.223:3306...
This instance reports its own address as node3:3306
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.
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 the
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: node3:3306 is being cloned from node1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY: \
NOTE: node3:3306 is shutting down...
* Waiting for server restart... ready
* node2:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 1.12 GB transferred in 5 sec (224.16 MB/s)
State recovery already finished for 'node3:3306'
The instance '172.29.38.222:3306' was successfully added to the cluster.
4.6 查看集群状态
MySQL 172.29.38.222:33060+ ssl JS > cluster.status()
{
"clusterName": "testCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node2:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"node2:3306": {
"address": "node1:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
},
"node3:3306": {
"address": "node2:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
},
"node4:3306": {
"address": "node3:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.22"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node2:3306"
}
MySQL 172.29.38.222:33060+ ssl JS > 五 配置 Mysql router
5.1 mysql-router 安装
rpm -ivh mysql-router-community-8.0.22-1.el7.x86_64.rpm5.2 初始化 mysql-router配置
mysqlrouter --user=root --bootstrap dba@172.29.38.222:33065.3 启动 mysql-router
mysqlrouter --user=root -c /etc/mysqlrouter/mysqlrouter.conf &六 常见问题
6.1 loose-group_replication_group_name不一致
master和slave 启动loose-group_replication_group_name不一致问题，这个问题是因为log-bin 中版本号不一致导致的，数据在同步时出现问题。解决方案 登录slave各数据库执行
reset master;
reset slave;
614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
