GreatSQL手工部署

GreatSQL手工部署

参考网址：

https://gitee.com/GreatSQL/GreatSQL
https://blog.51cto.com/imysql/3052284

一、配置yum源

# 注意，阿里云和腾讯云的yum源二选一即可
mv /etc/yum.repos.d/CentOS-Base.repo{,.orig}
#阿里云
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
#腾讯云
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.cloud.tencent.com/repo/centos7_base.repo
#替换完后，更新缓存
yum clean all
yum makecache

二、安装jemalloc

# 运行GreatSQL可能需要依赖jemalloc库，因此请先先安装上
cd /opt/
yum -y install jemalloc jemalloc-devel

#也可以把自行安装的lib库so文件路径加到系统配置文件中，例如：
[root@mgr131 ~]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
[root@mgr131 ~]#

而后执行下面的操作加载libjemalloc库，并确认是否已存在

[root@mgr131 ~]# ldconfig
[root@mgr131 ~]# ldconfig -p | grep libjemalloc
        libjemalloc.so.1 (libc6,x86-64) => /lib64/libjemalloc.so.1
        libjemalloc.so (libc6,x86-64) => /lib64/libjemalloc.so
[root@mgr131 ~]#

三、修改/etc/hosts

# 请修改/etc/hosts
cat <<EOF >>/etc/hosts
192.168.29.131  mgr131
192.168.29.132  mgr132
192.168.29.133  mgr133
EOF

四、修改主机名

# 修改主机名
hostnamectl set-hostname mgr131
hostnamectl set-hostname mgr132
hostnamectl set-hostname mgr133

五、安装系统依赖包

# 这里是Centos7最小化安装，所以将常用的都安装一下
yum -y install make gcc-c++ cmake bison-devel ncurses-devel  readline-devel >/dev/null 2>&1
yum -y install libaio-devel  libaio wget lrzsz vim libnuma* bzip2 xz tree >/dev/null 2>&1
yum -y install perl grep glibc  libgcc  libstdc++  numactl cyrus-sasl-lib coreutils-libs >/dev/null 2>&1
yum -y install epel-release >/dev/null 2>&1
yum -y install perl-DBD-MySQL >/dev/null 2>&1
yum -y install perl-Config-Tiny >/dev/null 2>&1
yum -y install perl-Digest-MD5 >/dev/null 2>&1
yum -y install perl-Log-Dispatch perl-Time-HiRes >/dev/null 2>&1
yum -y install perl-Parallel-ForkManager ntp perl cpan >/dev/null 2>&1
yum -y install perl perl-devel perl-Time-HiRes perl-DBD-MySQL >/dev/null 2>&1
yum -y install zlib-devel bzip2-devel openssl-devel tk-devel gcc make >/dev/null 2>&1
yum -y install gcc automake autoconf bzr bison libtool ncurses5-devel >/dev/null 2>&1

六、关闭selinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
setenforce  0 2>/dev/null

七、修改系统限制参数

cat <<EOF >> /etc/security/limits.conf
#
###########################custom########
#
*           soft   nproc        20480
*           hard   nproc        65535
*           hard   nofile       1000000
*           soft   nofile       1000000
##########################################
EOF

八、创建用户

groupadd mysql >/dev/null 2>&1
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql >/dev/null 2>&1

九、下载安装包

cd /opt/
wget https://product.greatdb.com/8.0.25-15/GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64.tar.xz

十、拷贝软件包

# 我这里是三台测试环境，为之后的mgr准备
[root@mgr131 opt]# for i in 192.168.29.132 192.168.29.133; do scp GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64.tar.xz root@$i:/opt ; done
GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64.tar.xz                    100%  506MB  58.8MB/s   00:08
GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64.tar.xz                    100%  506MB 107.4MB/s   00:04
[root@mgr131 opt]#

十一、创建目录并修改权限

mkdir -p /data/GreatSQL/mgr/data/{data,logs,tmp}
chown -R mysql.mysql /data/GreatSQL

十二、初始化数据库

# my.cnf配置文件具体内容详见下文附录部分

# 执行下面的命令进行MySQL实例初始化，会自动创建InnoDB系统表空间、redo log、undo log的文件：
cd /opt/
tar -xJf GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64.tar.xz -C /usr/local
cd /usr/local
ln -s GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql.mysql /data/GreatSQL
chown -R mysql.mysql GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64

初始化

#/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/GreatSQL/mgr/data/data --initialize &
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/GreatSQL/mgr/data/data --initialize-insecure &

十三、配置mysql命令环境

echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
source /etc/profile

十四、启动、停止、重启GreatSQL

cp /usr/local/mysql/support-files/mysql.server /usr/local/mysql/bin/
/usr/local/mysql/bin/mysql.server start
#/usr/local/mysql/bin/mysql.server stop
#/usr/local/mysql/bin/mysql.server restart

十五、登录测试

[root@mgr131 local]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.25-15 GreatSQL, Release 15, Revision c7feae175e0

Copyright (c) 2021-2021 GreatDB Software Co., Ltd
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 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 'h' for help. Type 'c' to clear the current input statement.

root@mysqldb 14:10:  [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

root@mysqldb 14:10:  [(none)]> exit
Bye
[root@mgr131 local]#

十六、常用账号示例

-- 同步账号
create user 'repl_user'@'192.168.%' IDENTIFIED with mysql_native_password BY 'A3bW^3s#6#yTV132xc6v';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl_user'@'192.168.%';

-- 监控账号
CREATE USER 'exporter'@'localhost' identified with mysql_native_password by 'GDMV8V!gXo0Vd4Xo020p';

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT,REPLICATION SLAVE, RELOAD ON *.* TO ''exporter''@'localhost';

CREATE USER 'exporter'@'127.0.0.1' identified with mysql_native_password by 'GDMV8V!gXo0Vd4Xo020p';

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT,REPLICATION SLAVE, RELOAD ON *.* TO ''exporter''@'127.0.0.1'; 

CREATE USER 'exporter'@'::1' identified with mysql_native_password by 'GDMV8V!gXo0Vd4Xo020p';

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT,REPLICATION SLAVE, RELOAD ON *.* TO 'exporter'@'::1';

-- 接入TiDB的账号
create user 'tidb_sync'@'192.168.%' IDENTIFIED with mysql_native_password BY 'CpW2$dLN@cmhgPcnDz&I';
GRANT SELECT, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'tidb_sync'@'192.168.%' ;

-- admin账号
create user 'admin_u'@'localhost' identified with mysql_native_password by '9ei0eRaHU4lD$oT&1Js9';
grant all privileges on *.* to 'admin_u'@'localhost' with grant option;

-- 本地root账号
alter user 'root'@'localhost' identified with mysql_native_password by 'jv&tzoKqjoDbZIf$lsuw';

-- 备份账号
create user 'bkpuser'@'localhost' identified with mysql_native_password by 'lQXjoWyGj5YgD$dE&xBO';
grant all privileges on *.* to 'bkpuser'@'localhost';

十七、账号配置文件

cat <<"EOF" >/root/.mysql.bkpuser.cnf
[client]
host=localhost
port="3306"
user=bkpuser
password="lQXjoWyGj5YgD$dE&xBO"
socket=/data/GreatSQL/mgr/data/tmp/mysql.3306.sock
EOF

cat <<"EOF" >/root/.mysql.root.cnf
[client]
host=localhost
port="3306"
user=root
password="jv&tzoKqjoDbZIf$lsuw"
socket=/data/GreatSQL/mgr/data/tmp/mysql.3306.sock
EOF

cat <<"EOF" >/root/.mysql.admin_u.cnf
[client]
host=localhost
port="3306"
user=admin_u
password="9ei0eRaHU4lD$oT&1Js9"
socket=/data/GreatSQL/mgr/data/tmp/mysql.3306.sock
EOF

十八、快捷方式

cat <<"EOF" >>~/.bashrc
alias mysql.root="/usr/local/mysql/bin/mysql --defaults-file=/root/.mysql.root.cnf"
alias mysql.admin_u="/usr/local/mysql/bin/mysql --defaults-file=/root/.mysql.root.cnf"
alias mysql.admin_u="/usr/local/mysql/bin/mysql --defaults-file=/root/.mysql.admin_u.cnf"
EOF

cat <<"EOF" >>/etc/rc.local
#/usr/local/mysql/bin/mysql.server start
#/usr/local/mysql/bin/mysql.server stop
#/usr/local/mysql/bin/mysql.server restart
EOF

source ~/.bashrc

附录：my.cnf

# /etc/my.cnf
[client]
port    = 3306
socket  = /data/GreatSQL/mgr/data/tmp/mysql.3306.sock
#ssl-ca=/data/GreatSQL/mgr/data/data/ca.pem
#ssl-cert=/data/GreatSQL/mgr/data/data/server-cert.pem
#ssl-key=/data/GreatSQL/mgr/data/data/server-key.pem

[mysql]
prompt="u@mysqldb R:m:s [d]> "
no-auto-rehash
loose-skip-binary-as-hex

[mysqld]
bind-address = *
#ssl-ca=/data/GreatSQL/mgr/data/data/ca.pem
#ssl-cert=/data/GreatSQL/mgr/data/data/server-cert.pem
#ssl-key=/data/GreatSQL/mgr/data/data/server-key.pem
user    = mysql
port    = 3306
basedir = /usr/local/mysql
datadir = /data/GreatSQL/mgr/data/data
socket  = /data/GreatSQL/mgr/data/tmp/mysql.3306.sock
pid-file = /data/GreatSQL/mgr/data/tmp/mysql.3306.pid
character-set-server = utf8mb4
skip_name_resolve = 1
lower_case_table_names = 1
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
default_authentication_plugin=mysql_native_password
admin_address = localhost
admin_port = 33062
create_admin_listener_thread = ON

#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"

open_files_limit    = 65535
back_log = 1024
max_connections = 2000
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 3000
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/GreatSQL/mgr/data/logs/slow.log
log-error = /data/GreatSQL/mgr/data/logs/mysql.error.log
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
server-id = 15910
log-bin = /data/GreatSQL/mgr/data/logs/mysql-binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 128
log_bin_trust_function_creators = 1
binlog_rows_query_log_events = 1

#注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项
binlog_expire_logs_seconds = 2592000

#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
binlog_row_image = full
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
relay_log=relay-log
relay-log-index = relay-log.index
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_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/GreatSQL/mgr/data/logs/undolog

# replication
# replicate-wild-ignore-table     = test.%
# slave_skip_errors=all

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 20000
innodb_io_capacity_max = 40000
innodb_flush_sync = OFF
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_undo_log_truncate = 1


#sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'


# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
#innodb_dedicated_server = 0

innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
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"
secure_file_priv =/tmp
binlog_transaction_dependency_tracking=WRITESET
tls_version='TLSv1.1,TLSv1.2,TLSv1.3'
admin_tls_version='TLSv1.1,TLSv1.2,TLSv1.3'
slave_preserve_commit_order = 1
slave_checkpoint_period = 2

#启用InnoDB并行查询优化功能
force_parallel_execute = ON
#设置每个SQL语句的并行查询最大并发度
parallel_default_dop = 8
#设置系统中总的并行查询线程数，可以和最大逻辑CPU数量一样
parallel_max_threads = 64
#并行执行时leader线程和worker线程使用的总内存大小上限，可以设置物理内存的5-10%左右
parallel_memory_limit = 12G

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
binlog_checksum = NONE

#mgr
loose-plugin_load_add='mysql_clone.so'
loose-plugin_load_add='group_replication.so'

#所有节点的group_replication_group_name值必须相同
#这是一个标准的UUID格式，可以手动指定，也可以用随机生成的UUID
loose-group_replication_group_name="0ad1ab74-e0df-a686-19b0-43389d1c9510"

#指定MGR集群各节点的IP+端口，这个端口是专用于MGR的，不是平常所说的mysqld实例端口
#如果是在多节点上部署MGR集群时，要注意这个端口是否会被防火墙拦截
loose-group_replication_group_seeds= "192.168.29.131:33061,192.168.29.132:33061,192.168.29.133:33061"

#不建议启动mysqld的同时也启动MGR服务
loose-group_replication_start_on_boot=off

#默认不要作为MGR集群引导节点，有需要时再手动执行并立即改回OFF状态
loose-group_replication_bootstrap_group=off

#当退出MGR后，把该实例设置为read_only，避免误操作写入数据
loose-group_replication_exit_state_action=READ_ONLY

#一般没什么必要开启流控机制
loose-group_replication_flow_control_mode = "DISABLED"

#【强烈】建议只用单主模式，如果是实验目的，可以尝试玩玩多主模式
loose-group_replication_single_primary_mode=ON


[mysqld_safe]
log-error=/data/GreatSQL/mgr/data/logs/mysql.error.log
pid-file=/data/GreatSQL/mgr/data/tmp/mysql.3306.pid

[mysqldump]
quick
max_allowed_packet = 64M