单机多实例手动部署GreatSQL-8.0.32-25

一、环境信息

操作系统：AnolisOS8.8  #cat /etc/os-release

数据库版本：GreatSQL-8.0.32-25

软件目录：/usr/local/GreatSQL

数据目录：/data/greatsql/

二、安装准备

GreatSQL数据库支持RPM包、二进制包、Docker、Ansible、源码编译共5种安装方法，此示例以二进制包方式进行部署(3个实例端口分别为：3311、3312、3313)。

1、关闭防火墙

systemctl stop firewalld

systemctl disable firewalld

systemctl is-enabled firewalld  #查看是否禁用服务

2、禁用SELINUX

setenforce 0

vi /etc/selinux/config

SELINUX=disabled

或者执行命令替换配置： sed -i '/^SELINUX=/c'SELINUX=disabled /etc/selinux/config

3、关闭swap

echo "vm.swappiness = 0">> /etc/sysctl.conf

sysctl -p

swapoff -a  #禁用swap后还需要注释掉 /etc/fstab 中关于swap加载项

free -h

4、修改数据盘IO策略

将 数据库的数据存放目录所在磁盘的IO测试设置为 noop / deadline

先查看当前设置（nvme0n1为磁盘分区）

cat /sys/block/nvme0n1/queue/scheduler

这样没问题，如果不是 noop 或 deadline，可以执行如下命令修改：

echo 'noop' > /sys/block/nvme0n1/queue/scheduler

这样修改后立即生效，无需重启。

5、确认CPU性能模式设置

先检查当前的设置模式，执行如下命令查看;

cpupower frequency-info --policy

如果输出内容不是 The governor "performance" 而是 The governor "powersave" 的话，则要注意了。

The governor "powersave" 表示 cpufreq 的节能策略使用 powersave，需要调整为 performance 策略。

如果是虚拟机或者云主机，则不需要调整，命令输出通常为 Unable to determine current policy。

6、关闭透明大页

建议关闭透明大页（Transparent Huge Pages / THP）。

OLTP型数据库内存访问模式通常是稀疏的而非连续的。当高阶内存碎片化比较严重时，分配 THP 页面会出现较高的延迟，反而影响性能。

先检查当前设置：

cat /sys/kernel/mm/transparent_hugepage/enabled

如果输出结果不是 never 的话，则需要执行下面的命令关闭：

echo never > /sys/kernel/mm/transparent_hugepage/enabled

echo never > /sys/kernel/mm/transparent_hugepage/defrag

7、优化内核参数

建议调整优化下面几个内核参数：

echo "fs.file-max = 1000000" >> /etc/sysctl.conf

echo "net.core.somaxconn = 32768" >> /etc/sysctl.conf

echo "net.ipv4.tcp_syncookies = 0" >> /etc/sysctl.conf

echo "vm.overcommit_memory = 1" >> /etc/sysctl.conf

sysctl -p

8、修改mysql用户使用资源上限

修改 /etc/security/limits.conf 系统文件，调高mysql系统账户的上限：

vim /etc/security/limits.conf   #添加如下配置项

mysql           soft    nofile         65535

mysql           hard    nofile         65535

mysql           soft    stack          32768

mysql           hard    stack          32768

mysql           soft    nproc          65535

mysql           hard    nproc          65535

9、确认NUMA模式

推荐开启NUMA模式以获得更好的性能表现。

以CentOS为例，打开/etc/default/grub文件，确保文件内容中没有 NUMA=OFF 字样，如果有的话就删掉：

如果修改了 /etc/default/grub 文件，需要重新生成UEFI启动文件：

grub2-mkconfig -o /boot/efi/EFI/centos/grub.cfg

然后重启操作系统，使之生效。

操作系统层开启NUMA后，还要记得修改GreatSQL配置选项 innodb_numa_interleave = ON，确保InnoDB在分配内存时使用正确的NUMA策略。

如果采用手动方式启动GreatSQL服务进程，还可以在启动时加上 numactl --interleave=all，例如：

numactl --interleave=all /usr/local/GreatSQL-8.0.32-25-Linux-glibc2.28-x86_64/bin/mysqld &

如果采用 systemd 来启动 GreatSQL服务进程，则可以修改 /etc/systemd/system.conf 配置文件，增加如下的相关配置项：

[Manager]

NUMAPolicy=interleave

#NUMAMask=

修改完毕后，重新加载 systemd 配置，确保NUMA策略生效：

systemctl daemon-reload

10、时间服务及时区设置

时间服务建议使用chrony。

yum install -y chrony

systemctl start chronyd

systemctl is-enabled chronyd  #systemctl enable chronyd

查看时间同步：

chronyc sources

显示当前语言环境状态：localectl status

查看LANG语言环境变量：echo $LANG

列出可用的语言环境：localectl list-locales | grep zh

注：如果需要可设置语言环境，相关命令如下：

编辑/etc/locale.conf文件

vi /etc/locale.conf

#中文 LANG=zh_CN.UTF-8

#英文 LANG=en_US.UTF-8

或，执行如下命令设置简体中文语言环境：

localectl  set-locale  LANG=zh_CN.UTF-8

简体英文语言环境：

localectl  set-locale  LANG=en_US.UTF-8

加载语言环境

source  /etc/locale.conf

三、安装部署

二进制包文件下载地址：https://gitee.com/GreatSQL/GreatSQL/releases/tag/GreatSQL-8.0.32-25

注：查看机器的glibc版本，以选择正确的安装包：ldd --version

如果您的glibc版本为2.28或更高版本，请选择带有"glibc2.28"标识的安装包；如果您的glibc版本为2.17，请选择带有"glibc2.17"标识的安装包。

1、创建mysql用户

groupadd mysql

useradd -g mysql mysql -d /dev/null -s /sbin/nologin

id mysql

2、安装jemalloc

建议采用jemalloc代替glibc自带的malloc库，其优势在于减少内存碎片和提升高并发场景下内存的分配效率，提高内存管理效率的同时还能降低数据库运行时发生OOM的风险。

yum install gcc gcc-c++ cmake tar bzip2 vim tree

tar  -xvf  jemalloc-5.3.0.tar.bz2

cd jemalloc-5.3.0

./configure

make && make install

vi /etc/ld.so.conf.d/greatsql.conf

/usr/local/lib/

/sbin/ldconfig

ldconfig -p | grep libjemalloc

添加/修改系统文件 /etc/sysconfig/greatsql

LD_PRELOAD=/usr/local/lib/libjemalloc.so

THP_SETTING=never

注：启动greatsql服务后，可执行命令查看是否使用的jemalloc内存管理：lsof -n | grep libjemalloc

3、安装依赖包

yum install -y pkg-config perl libaio-devel numactl-devel numactl-libs net-tools openssl openssl-devel perl-Data-Dumper perl-Digest-MD5 python2 perl-JSON perl-Test-Simple

建议提前安装DBA常用的辅助工具包：

yum install -y net-tools perf sysstat iotop tmux tree

4、创建数据目录

mkdir -p /data/greatsql/greatsqldata331{1,2,3}/{mydata,binlog,innodb_log,innodb_ts,innodb_dblwr,innodb_undo,innodb_temp,relaylog,log,sock,tmpdir}

5、解压安装文件

将二进制安装包文件解压到/usr/local目录下，并建立软连接/usr/local/GreatSQL：

tar -xvf GreatSQL-8.0.32-25-Linux-glibc2.28-x86_64.tar.xz -C /usr/local/

ln -s /usr/local/GreatSQL-8.0.32-25-Linux-glibc2.28-x86_64 /usr/local/GreatSQL

#相关目录属主属性修改

chown -R mysql.mysql /data/greatsql /usr/local/GreatSQL-8.0.32-25-Linux-glibc2.28-x86_64 /usr/local/GreatSQL

6、配置环境变量

在配置文件/etc/profile中添加PATH信息：

vi /etc/profile

# set for GreatSQL

export MYSQL_HOME=/usr/local/GreatSQL

export MYSQL_USER=mysql

export PATH=$PATH:$MYSQL_HOME/bin

source /etc/profile

mysql -V

7、配置文件/etc/my.cnf

创建数据库配置文件：/etc/my.cnf

vi /etc/my.cnf

[mysql]

no-auto-rehash

loose-skip-binary-as-hex

prompt = "(\\D)[\\u@GreatSQL][\\d]>"

#prompt='\\u@\\h:\\d \\v \\r:\\m:\\s> '

default-character-set = utf8mb4

show-warnings

[mysqld]

user = mysql

basedir = /usr/local/GreatSQL

character-set-server = utf8mb4

skip_name_resolve = 1

default_time_zone = "+8:00"

bind_address = "0.0.0.0"

#performance setttings

lock_wait_timeout = 3600

open_files_limit = 65535

back_log = 1024

max_connections = 1024

max_connect_errors = 1000000

table_open_cache = 2048

table_definition_cache = 2048

thread_stack = 512K

sort_buffer_size = 4M

join_buffer_size = 4M

read_buffer_size = 8M

read_rnd_buffer_size = 4M

bulk_insert_buffer_size = 64M

thread_cache_size = 768

interactive_timeout = 600

wait_timeout = 600

tmp_table_size = 96M

max_heap_table_size = 96M

max_allowed_packet = 64M

net_buffer_shrink_interval = 180

#GIPK

loose-sql_generate_invisible_primary_key = ON

#log settings

log_timestamps = SYSTEM

#设置slow log文件大小1G及总文件数10

max_slowlog_size = 1073741824

max_slowlog_files = 10

long_query_time = 1

log_queries_not_using_indexes = 1

log_throttle_queries_not_using_indexes = 60

min_examined_row_limit = 100

log_slow_admin_statements = 1

log_slow_slave_statements = 1

log_slow_verbosity = FULL

#myisam settings

key_buffer_size = 32M

myisam_sort_buffer_size = 128M

#replication settings

relay_log_recovery = 1

slave_parallel_type = LOGICAL_CLOCK

#并行复制线程数可以设置为逻辑CPU数量的2倍

slave_parallel_workers = 64

binlog_transaction_dependency_tracking = WRITESET

slave_preserve_commit_order = 1

slave_checkpoint_period = 2

#启用InnoDB并行查询优化功能

loose-force_parallel_execute = OFF

#设置每个SQL语句的并行查询最大并发度

loose-parallel_default_dop = 8

#设置系统中总的并行查询线程数，可以和最大逻辑CPU数量一样

loose-parallel_max_threads = 8

#并行执行时leader线程和worker线程使用的总内存大小上限，可以设置物理内存的5-10%左右

loose-parallel_memory_limit = 2G

#parallel load data

loose-gdb_parallel_load_chunk_size = 4M

#rapid engine

loose-rapid_memory_limit = 12G

loose-rapid_worker_threads = 32

loose-rapid_hash_table_memory_limit = 30

loose-secondary_engine_parallel_load_workers = 16

#innodb settings

#提醒：当需要用CLONE加密特性时，不要选用O_DIRECT模式，否则会比较慢

innodb_flush_method = O_DIRECT

innodb_lru_scan_depth = 4000

innodb_lock_wait_timeout = 10

innodb_rollback_on_timeout = 1

innodb_print_all_deadlocks = 1

innodb_online_alter_log_max_size = 4G

innodb_print_ddl_logs = 1

innodb_status_file = 1

innodb_status_output = 0

innodb_status_output_locks = 1

innodb_sort_buffer_size = 64M

innodb_adaptive_hash_index = 0

#开启NUMA支持

innodb_numa_interleave = ON

innodb_print_lock_wait_timeout_info = 1

#自动杀掉超过5分钟不活跃事务，避免行锁被长时间持有

kill_idle_transaction = 300

#异步清理大表

innodb_data_file_async_purge = ON

#pfs settings

performance_schema = 1

#performance_schema_instrument = '%memory%=on'

performance_schema_instrument = '%lock%=on'

[mysqld@3311]

server-id=1013311

port=3311

pid_file=/data/greatsql/greatsqldata3311/sock/greatsql.pid

socket=/data/greatsql/greatsqldata3311/sock/greatsql.sock

datadir=/data/greatsql/greatsqldata3311/mydata

tmpdir=/data/greatsql/greatsqldata3311/tmpdir

slave-load-tmpdir=/data/greatsql/greatsqldata3311/tmpdir

general_log_file=/data/greatsql/greatsqldata3311/log/general.log

log_error=/data/greatsql/greatsqldata3311/log/error.log

slow_query_log_file=/data/greatsql/greatsqldata3311/log/slow-query.log

log_bin=/data/greatsql/greatsqldata3311/binlog/greatsql-bin

log_bin_index=/data/greatsql/greatsqldata3311/binlog/greatsql-bin.index

binlog_format = ROW

sync_binlog = 2

binlog_cache_size = 4M

max_binlog_cache_size = 6G

max_binlog_size = 1G

#控制binlog总大小，避免磁盘空间被撑爆

binlog_space_limit = 200G

binlog_rows_query_log_events = 1

binlog_expire_logs_seconds = 604800

binlog_checksum = CRC32

relay_log_index=/data/greatsql/greatsqldata3311/relaylog/greatsql-relay-bin.index

relay_log=/data/greatsql/greatsqldata3311/relaylog/greatsql-relay-bin

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

innodb_buffer_pool_size=16G

innodb_buffer_pool_instances = 8

innodb_doublewrite_dir=/data/greatsql/greatsqldata3311/innodb_dblwr

innodb_undo_directory=/data/greatsql/greatsqldata3311/innodb_undo

innodb_temp_tablespaces_dir=/data/greatsql/greatsqldata3311/innodb_temp

innodb_data_home_dir=/data/greatsql/greatsqldata3311/innodb_ts

innodb_data_file_path=ibdata1:4096M:autoextend

innodb_log_group_home_dir=/data/greatsql/greatsqldata3311/innodb_log

innodb_log_buffer_size = 32M

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_flush_log_at_trx_commit=2

innodb_redo_log_capacity = 6G

innodb_doublewrite_files = 2

innodb_max_undo_log_size = 4G

# 根据您的服务器IOPS能力适当调整

# 一般配普通SSD盘的话，可以调整到 10000 - 20000

# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_open_files = 65534

innodb_force_recovery=0

gtid_mode = ON

enforce_gtid_consistency = TRUE

[mysqld@3312]

server-id=1013312

port=3312

pid_file=/data/greatsql/greatsqldata3312/sock/greatsql.pid

socket=/data/greatsql/greatsqldata3312/sock/greatsql.sock

datadir=/data/greatsql/greatsqldata3312/mydata

tmpdir=/data/greatsql/greatsqldata3312/tmpdir

slave-load-tmpdir=/data/greatsql/greatsqldata3312/tmpdir

general_log_file=/data/greatsql/greatsqldata3312/log/general.log

log_error=/data/greatsql/greatsqldata3312/log/error.log

slow_query_log_file=/data/greatsql/greatsqldata3312/log/slow-query.log

log_bin=/data/greatsql/greatsqldata3312/binlog/greatsql-bin

log_bin_index=/data/greatsql/greatsqldata3312/binlog/greatsql-bin.index

binlog_format = ROW

sync_binlog = 2

binlog_cache_size = 4M

max_binlog_cache_size = 6G

max_binlog_size = 1G

#控制binlog总大小，避免磁盘空间被撑爆

binlog_space_limit = 200G

binlog_rows_query_log_events = 1

binlog_expire_logs_seconds = 604800

binlog_checksum = CRC32

relay_log_index=/data/greatsql/greatsqldata3312/relaylog/greatsql-relay-bin.index

relay_log=/data/greatsql/greatsqldata3312/relaylog/greatsql-relay-bin

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

innodb_buffer_pool_size=16G

innodb_buffer_pool_instances = 8

innodb_doublewrite_dir=/data/greatsql/greatsqldata3312/innodb_dblwr

innodb_undo_directory=/data/greatsql/greatsqldata3312/innodb_undo

innodb_temp_tablespaces_dir=/data/greatsql/greatsqldata3312/innodb_temp

innodb_data_home_dir=/data/greatsql/greatsqldata3312/innodb_ts

innodb_data_file_path=ibdata1:4096M:autoextend

innodb_log_group_home_dir=/data/greatsql/greatsqldata3312/innodb_log

innodb_log_buffer_size = 32M

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_flush_log_at_trx_commit=2

innodb_redo_log_capacity = 6G

innodb_doublewrite_files = 2

innodb_max_undo_log_size = 4G

# 根据您的服务器IOPS能力适当调整

# 一般配普通SSD盘的话，可以调整到 10000 - 20000

# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_open_files = 65534

innodb_force_recovery=0

gtid_mode = ON

enforce_gtid_consistency = TRUE

[mysqld@3313]

server-id=1013313

port=3313

pid_file=/data/greatsql/greatsqldata3313/sock/greatsql.pid

socket=/data/greatsql/greatsqldata3313/sock/greatsql.sock

datadir=/data/greatsql/greatsqldata3313/mydata

tmpdir=/data/greatsql/greatsqldata3313/tmpdir

slave-load-tmpdir=/data/greatsql/greatsqldata3313/tmpdir

general_log_file=/data/greatsql/greatsqldata3313/log/general.log

log_error=/data/greatsql/greatsqldata3313/log/error.log

slow_query_log_file=/data/greatsql/greatsqldata3313/log/slow-query.log

log_bin=/data/greatsql/greatsqldata3313/binlog/greatsql-bin

log_bin_index=/data/greatsql/greatsqldata3313/binlog/greatsql-bin.index

binlog_format = ROW

sync_binlog = 2

binlog_cache_size = 4M

max_binlog_cache_size = 6G

max_binlog_size = 1G

#控制binlog总大小，避免磁盘空间被撑爆

binlog_space_limit = 200G

binlog_rows_query_log_events = 1

binlog_expire_logs_seconds = 604800

binlog_checksum = CRC32

relay_log_index=/data/greatsql/greatsqldata3313/relaylog/greatsql-relay-bin.index

relay_log=/data/greatsql/greatsqldata3313/relaylog/greatsql-relay-bin

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

innodb_buffer_pool_size=16G

innodb_buffer_pool_instances = 8

innodb_doublewrite_dir=/data/greatsql/greatsqldata3313/innodb_dblwr

innodb_undo_directory=/data/greatsql/greatsqldata3313/innodb_undo

innodb_temp_tablespaces_dir=/data/greatsql/greatsqldata3313/innodb_temp

innodb_data_home_dir=/data/greatsql/greatsqldata3313/innodb_ts

innodb_data_file_path=ibdata1:4096M:autoextend

innodb_log_group_home_dir=/data/greatsql/greatsqldata3313/innodb_log

innodb_log_buffer_size = 32M

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_flush_log_at_trx_commit=2

innodb_redo_log_capacity = 6G

innodb_doublewrite_files = 2

innodb_max_undo_log_size = 4G

# 根据您的服务器IOPS能力适当调整

# 一般配普通SSD盘的话，可以调整到 10000 - 20000

# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_open_files = 65534

innodb_force_recovery=0

gtid_mode = ON

enforce_gtid_consistency = TRUE

8、实例初始化

chown mysql.mysql /etc/my.cnf

#3311实例初始化

/usr/local/GreatSQL/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/GreatSQL --datadir=/data/greatsql/greatsqldata3311/mydata --innodb_data_home_dir=/data/greatsql/greatsqldata3311/innodb_ts --innodb_data_file_path=ibdata1:4096M:autoextend --innodb_log_group_home_dir=/data/greatsql/greatsqldata3311/innodb_log --innodb-doublewrite-dir=/data/greatsql/greatsqldata3311/innodb_dblwr --innodb-undo-directory=/data/greatsql/greatsqldata3311/innodb_undo --innodb_temp_tablespaces_dir=/data/greatsql/greatsqldata3311/innodb_temp --initialize-insecure

#3312实例初始化

/usr/local/GreatSQL/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/GreatSQL --datadir=/data/greatsql/greatsqldata3312/mydata --innodb_data_home_dir=/data/greatsql/greatsqldata3312/innodb_ts --innodb_data_file_path=ibdata1:4096M:autoextend --innodb_log_group_home_dir=/data/greatsql/greatsqldata3312/innodb_log --innodb-doublewrite-dir=/data/greatsql/greatsqldata3312/innodb_dblwr --innodb-undo-directory=/data/greatsql/greatsqldata3312/innodb_undo --innodb_temp_tablespaces_dir=/data/greatsql/greatsqldata3312/innodb_temp --initialize-insecure

#3313实例初始化

/usr/local/GreatSQL/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/GreatSQL --datadir=/data/greatsql/greatsqldata3313/mydata --innodb_data_home_dir=/data/greatsql/greatsqldata3313/innodb_ts --innodb_data_file_path=ibdata1:4096M:autoextend --innodb_log_group_home_dir=/data/greatsql/greatsqldata3313/innodb_log --innodb-doublewrite-dir=/data/greatsql/greatsqldata3313/innodb_dblwr --innodb-undo-directory=/data/greatsql/greatsqldata3313/innodb_undo --innodb_temp_tablespaces_dir=/data/greatsql/greatsqldata3313/innodb_temp --initialize-insecure

9、多实例管理脚本

多实例管理脚本和单实例有所区别：多了%I参数及@分隔符号。

vim /lib/systemd/system/greatsql@.service

[Unit]

Description=GreatSQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

Type=notify

TimeoutSec=0

PermissionsStartOnly=true

#for single instance

#ExecStartPre=/usr/local/GreatSQL/bin/mysqld_pre_systemd

#ExecStart=/usr/local/GreatSQL/bin/mysqld $MYSQLD_OPTS

#for multi instance

#ExecStartPre=/usr/local/GreatSQL/bin/mysqld_pre_systemd %I

ExecStart=/usr/local/GreatSQL/bin/mysqld --defaults-group-suffix=@%I $MYSQLD_OPTS

EnvironmentFile=-/etc/sysconfig/greatsql

LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

重新加载systemd，使其生效：

systemctl daemon-reload

四、测试验证

手动部署完成后，启动数据库服务验证安装成果。

1、启动数据库服务

使用systemctl启停数据库服务：

systemctl start greatsql@3311

systemctl start greatsql@3312

systemctl start greatsql@3313

systemctl -l | grep greatsql

#查看日志

journalctl -ex

tail -f /data/greatsql/greatsqldata3311/log/error.log

tail -f /data/greatsql/greatsqldata3312/log/error.log

tail -f /data/greatsql/greatsqldata3313/log/error.log

2、登录数据库

使用套接字本地登录数据库实例(root用户初始密码为空)

mysql -h localhost -u root -S /data/greatsql/greatsqldata3311/sock/greatsql.sock

mysql -h localhost -u root -S /data/greatsql/greatsqldata3312/sock/greatsql.sock

mysql -h localhost -u root -S /data/greatsql/greatsqldata3313/sock/greatsql.sock

修改root用户密码

mysql> alter user root@localhost identified with mysql_native_password by 'root@PWD123';

3、操作数据库

创建测试数据库、表，执行增删改查询测试验证。

show databases;

create database testdb;

create table t1(id int not null primary key, name varchar(30), crte_time timestamp);

insert into t1 values(1, '张三', now()), (2, '李四', now()), (3, '王五', now());

select * from t1;