InnoDBCluster安装

mysql code tracer 2021-04-25

617

InnoDB Cluster安装

3节点安装MySQL

创建所需目录与用户

groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
mkdir -p /service/logs/software/mysql_3306/
mkdir -p /service/databases/mysql_3306/data/
chown -R mysql. /service/logs/software/mysql_3306/
chown -R mysql. /service/databases/mysql_3306/data/
chown -R mysql. /service/software/mysql_3306/

配置文件

配置文件中需要注意的部分

disabled_storage_engines        = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
log_bin                        = /service/databases/mysql_3306/data/mysql-bin
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
binlog_transaction_dependency_tracking = WRITESET_SESSION
slave_parallel_workers             = 4
slave_preserve_commit_order        = 1
slave_parallel_type            = LOGICAL_CLOCK

完整版示例

[client]
port    = 3306
socket    = /service/databases/mysql_3306/data/mysql_3306.sock

[mysql]
prompt="\\u@\\h \R:\m:\s [\\d]>"
no-auto-rehash
port                           = 3306
socket                         = /service/databases/mysql_3306/data/mysql_3306.sock
default_character_set          = utf8mb4

[mysqladmin]
port                           = 3306
socket                         = /service/databases/mysql_3306/data/mysql_3306.sock

[mysqldump]
port                           = 3306
socket                         = /service/databases/mysql_3306/data/mysql_3306.sock

[xtrabackup]
port                           = 3306
socket                         = /service/databases/mysql_3306/data/mysql_3306.sock

[mysqlbackup]
port                           = 3306
socket                         = /service/databases/mysql_3306/data/mysql_3306.sock
safe_slave_backup_timeout      = 0

[mysqld]
# basic settings #
user                           = mysql
default_storage_engine         = InnoDB
server_id                      = 1901123306
sql_mode                       = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
socket                         = /service/databases/mysql_3306/data/mysql_3306.sock
pid_file                       = /service/databases/mysql_3306/data/mysql_3306.pid
basedir                        = /service/software/mysql_3306
datadir                        = /service/databases/mysql_3306/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                      = /service/logs/software/mysql_3306/mysql-error.log
log_timestamps                 = SYSTEM
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /service/logs/software/mysql_3306/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           = /service/databases/mysql_3306/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              = /service/databases/mysql_3306
default_password_lifetime     = 0

# replication settings #
skip_slave_start               = 1
relay_log                      = /service/databases/mysql_3306/data/relay-bin
slave_pending_jobs_size_max    = 128M
log_slave_updates = 1
log_bin                        = /service/databases/mysql_3306/data/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 #

系统 HOSTS 配置

修改/etc/hosts

vi /etc/hosts

10.101.190.112 kh-oms4-sit-innodbcluster-db01
10.101.190.13  kh-oms4-sit-innodbcluster-db02
10.101.190.179 kh-oms4-sit-innodbcluster-db03

三节点验证report-host是否设置正确

root@localhost 08:25:  [(none)]>SELECT coalesce(@@report_host, @@hostname) as r;
+--------------------------------+
| r                              |
+--------------------------------+
| kh-oms4-sit-innodbcluster-db01 |
+--------------------------------+
1 row in set (0.00 sec)

三节点创建用户

create user root identified by 'C******';
grant all on *.* to root with grant option;
create user u_autodba@'%' identified  WITH mysql_native_password by 'lx*********';
grant all on *.* to u_autodba@'%' with grant option;

安装mysqlshell

wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.19-linux-glibc2.12-x86-64bit.tar.gz
tar xvf mysql-shell-8.0.19-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-shell-8.0.19-linux-glibc2.12-x86-64bit mysqlshell
chown -R mysql. mysqlshell/
vim /root/.bash_profile

PATH=$PATH:$HOME/bin:/service/software/mysql_3306/bin:/service/software/mysqlshell/bin

mysqlshell连接一节点检查配置

连接节点

[root@kh-oms4-sit-innodbcluster-db01 software]# mysqlsh root@kh-oms4-sit-innodbcluster-db01:3306
## 输入密码,提示还会问是否保存密码- -

配置检测

检查node1

MySQL  kh-oms4-sit-innodbcluster-db01:3306 ssl  JS > dba.checkInstanceConfiguration("root@kh-oms4-sit-innodbcluster-db01:3306");
##查看输出是否返回OK，如果不是修改并重启MySQL
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as kh-oms4-sit-innodbcluster-db01: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 'kh-oms4-sit-innodbcluster-db01:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

检查node2

MySQL  kh-oms4-sit-innodbcluster-db01:3306 ssl  JS > dba.checkInstanceConfiguration("root@kh-oms4-sit-innodbcluster-db02:3306");
Please provide the password for 'root@kh-oms4-sit-innodbcluster-db02:3306': **********
Save password for 'root@kh-oms4-sit-innodbcluster-db02:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Validating MySQL instance at kh-oms4-sit-innodbcluster-db02:3306 for use in an InnoDB cluster...

This instance reports its own address as kh-oms4-sit-innodbcluster-db02: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 'kh-oms4-sit-innodbcluster-db02:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

检查node3

MySQL  kh-oms4-sit-innodbcluster-db01:3306 ssl  JS > dba.checkInstanceConfiguration("root@kh-oms4-sit-innodbcluster-db03:3306");
Please provide the password for 'root@kh-oms4-sit-innodbcluster-db03:3306': **********
Save password for 'root@kh-oms4-sit-innodbcluster-db03:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Validating MySQL instance at kh-oms4-sit-innodbcluster-db03:3306 for use in an InnoDB cluster...

This instance reports its own address as kh-oms4-sit-innodbcluster-db03: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 'kh-oms4-sit-innodbcluster-db03:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

节点1创建集群

MySQL  kh-oms4-sit-innodbcluster-db01:3306 ssl  JS > var cluster = dba.createCluster('oms4');

得到如下输出

A new InnoDB cluster will be created on instance 'kh-oms4-sit-innodbcluster-db01:3306'.

Validating instance configuration at kh-oms4-sit-innodbcluster-db01:3306...

This instance reports its own address as kh-oms4-sit-innodbcluster-db01:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'kh-oms4-sit-innodbcluster-db01:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'oms4' on 'kh-oms4-sit-innodbcluster-db01: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.

将2、3节点加入集群

node2加入集群

步骤中会问你是否克隆node1节点。

cluster.addInstance('root@kh-oms4-sit-innodbcluster-db02:3306');

得到如下输出

WARNING: A GTID set check of the MySQL instance at 'kh-oms4-sit-innodbcluster-db02:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

kh-oms4-sit-innodbcluster-db02:3306 has the following errant GTIDs that do not exist in the cluster:
8cd6aafa-7ac1-11ea-a904-0050568c2d7d:1-4

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of kh-oms4-sit-innodbcluster-db02: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 'kh-oms4-sit-innodbcluster-db02:33061'. Use the localAddress option to override.

Validating instance configuration at kh-oms4-sit-innodbcluster-db02:3306...

This instance reports its own address as kh-oms4-sit-innodbcluster-db02: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: kh-oms4-sit-innodbcluster-db02:3306 is being cloned from kh-oms4-sit-innodbcluster-db01:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: kh-oms4-sit-innodbcluster-db02:3306 is shutting down...

* Waiting for server restart... ready
* kh-oms4-sit-innodbcluster-db02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 1.12 GB transferred in 1 min 15 sec (14.94 MB/s)

State recovery already finished for 'kh-oms4-sit-innodbcluster-db02:3306'

The instance 'kh-oms4-sit-innodbcluster-db02:3306' was successfully added to the cluster.

node3添加进集群

cluster.addInstance('root@kh-oms4-sit-innodbcluster-db03:3306');

输出与node2类似略过

查看集群状态

MySQL  kh-oms4-sit-innodbcluster-db01:3306 ssl  JS > cluster.status()
{
    "clusterName": "oms4", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "kh-oms4-sit-innodbcluster-db01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "kh-oms4-sit-innodbcluster-db01:3306": {
                "address": "kh-oms4-sit-innodbcluster-db01:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.19"
            }, 
            "kh-oms4-sit-innodbcluster-db02:3306": {
                "address": "kh-oms4-sit-innodbcluster-db02:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.19"
            }, 
            "kh-oms4-sit-innodbcluster-db03:3306": {
                "address": "kh-oms4-sit-innodbcluster-db03:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.19"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "kh-oms4-sit-innodbcluster-db01:3306"
}

安装mysqlrouter

wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.19-linux-glibc2.12-x86_64.tar.xz
tar xvf mysql-router-8.0.19-linux-glibc2.12-x86_64.tar.xz
mv mysql-router-8.0.19-linux-glibc2.12-x86_64 mysqlrouter
chown -R mysql. mysqlrouter/
vim /root/.bash_profile

PATH=$PATH:$HOME/bin:/service/software/mysql_3306/bin:/service/software/mysqlshell/bin:/service/software/mysqlrouter/bin

配置mysqlrouter

## 指定目录/tmp/myrouter 自动生成配置文件与启停脚本
mysqlrouter --bootstrap root@kh-oms4-sit-innodbcluster-db01:3306 --directory /tmp/myrouter --conf-use-sockets --user=mysql

查看生成配置文件内容

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=mysql
logging_folder=/tmp/myrouter/log
runtime_folder=/tmp/myrouter/run
data_folder=/tmp/myrouter/data
keyring_path=/tmp/myrouter/data/keyring
master_key_path=/tmp/myrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/tmp/myrouter/data/state.json

[logger]
level = INFO

[metadata_cache:oms4]
cluster_type=gr
router_id=1
user=mysql_router1_lf3xhs2sx1p7
metadata_cluster=oms4
ttl=0.5
use_gr_notifications=0

[routing:oms4_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/tmp/myrouter/mysql.sock
destinations=metadata-cache://oms4/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:oms4_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/tmp/myrouter/mysqlro.sock
destinations=metadata-cache://oms4/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:oms4_x_rw]
bind_address=0.0.0.0
bind_port=64460
socket=/tmp/myrouter/mysqlx.sock
destinations=metadata-cache://oms4/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:oms4_x_ro]
bind_address=0.0.0.0
bind_port=64470
socket=/tmp/myrouter/mysqlxro.sock
destinations=metadata-cache://oms4/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

根据配置文件写端口为6446，读端口为6447。X写端口为64460，X读端口为64470

启动mysqlrouter

[root@kh-oms4-sit-innodbcluster-db01 myrouter]# /tmp/myrouter/start.sh

consul检查脚本

写端口6446

写json配置文件

[root@kh-oms4-sit-innodbcluster-db01 consul.d]# cat w-kh-oms4-sit-innodbcluster.json
{
  "services": [
    {
      "name": "w-kh-oms4-sit-innodbcluster",
      "tags": [
        "master,sit,mysql"
      ],
      "address": "10.101.190.112",
      "port": 6446,
      "checks": [
        {
          "args":["sh","-c","/service/databases/consul/data/shell/check_mysql_master_3306.sh 6446 "],
          "interval": "5s"
        }
      ]
    }
  ]
}

check脚本仍然抄用大师兄的shell脚本

[root@kh-oms4-sit-innodbcluster-db01 consul.d]# cat /service/databases/consul/data/shell/check_mysql_master_3306.sh
#!/bin/bash
port=$1
user="******"
passwod="******"

comm="/service/software/mysql_3306/bin/mysql -u$user -h 127.0.0.1 -P $port -p$passwod"
slave_info=`$comm -e "show slave status" |wc -l`
value=`$comm -Nse "select 1"`
get_read_only=`$comm -e "show variables like 'read_only'\G"| grep -w "Value" |awk '{print $NF}'`

if [ $slave_info -ne 0 ]
then
   echo "MySQL $port  Instance is Slave........"
   $comm -e "show slave status\G" | egrep -w "Master_Host|Master_User|Master_Port|Master_Log_File|Read_Master_Log_Pos|Relay_Log_File|Relay_Log_Pos|Relay_Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Exec_Master_Log_Pos|Relay_Log_Space|Seconds_Behind_Master"
   exit 2
fi

if [ -z $value ]
then
   exit 2
fi

if  [ $get_read_only = "ON" ]
then
    echo "MySQL $port  Instance is read only........"
    exit 2
fi

if  [ -n$value -a  $get_read_only = "OFF" ]
then
    echo "MySQL $port  Instance is Master........"
    exit 0
fi

exit 2

读端口6447

读配置文件

[root@kh-oms4-sit-innodbcluster-db01 consul.d]# cat r-kh-oms4-sit-innodbcluster.json
{
  "services": [
    {
      "name": "r-kh-oms4-sit-innodbcluster",
      "tags": [
        "sit,slave,mysql"
      ],
      "address": "10.101.190.112",
      "port": 6447,
      "checks": [
        {
          "args":["sh","-c","/service/databases/consul/data/shell/check_mysql_slave_3306.sh 6447 "],
          "interval": "5s"
        }
      ]
    }
  ]
}

check脚本，因为mysqlrouter无法展示 show slave status
输出，可以在组复制一致性设置为After， group_replication_consistency='AFTER'
，保证数据落地，用 read_only=on
作为从节点判断

#!/bin/bash
port=$1
user="***"***
passwod="******"

max_delay=60
comm="/service/software/mysql_3306/bin/mysql -u$user -h 127.0.0.1 -P $port -p$passwod"
value=`$comm -Nse "select 1"`
get_read_only=`$comm -e "show variables like 'read_only'\G"| grep -w "Value" |awk '{print $NF}'`


if [ -z $value ]
then
   exit 2
fi

if  [ $get_read_only = "OFF" ]
then
    echo "MySQL $port  Instance is read write........"
    exit 2
fi


if [ $get_read_only = "ON" ]
then
   echo "MySQL $port  Instance is Slave........"
   exit 0
fi

exit 2

总结

看到这里的都是真爱拉！总体难点在于配置文件 my.cnf
，5.7的配置文件与8.0的配置文件相差很多，剩下的就是几条命令的事情了，总体比MGR初始化简单很多。
看了官档，限制还是蛮多的，比如隔离级别，间隙锁之类。还需要继续深入理解。
感谢爱可生开源社区之前的innodbcluster扫盲公众推文与大师兄的consul blog
后面就是破坏性测试和压测了，一起来玩鸭！

数据库

文章转载自mysql code tracer，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

InnoDBCluster安装

InnoDB Cluster安装

3节点安装MySQL

创建所需目录与用户

配置文件

配置文件中需要注意的部分

完整版示例

系统 HOSTS 配置

修改/etc/hosts

三节点验证report-host是否设置正确

三节点创建用户

安装mysqlshell

mysqlshell连接一节点检查配置

连接节点

配置检测

节点1创建集群

将2、3节点加入集群

node2加入集群

node3添加进集群

查看集群状态

安装mysqlrouter

配置mysqlrouter

查看生成配置文件内容

启动mysqlrouter

consul检查脚本

写端口6446

读端口6447

总结

评论