乐趣区

关于mysql:技术分享-将GreatSQL添加到系统systemd服务

欢送来到 GreatSQL 社区分享的 MySQL 技术文章,如有疑难或想学习的内容,能够在下方评论区留言,看到后会进行解答

  • GreatSQL 社区原创内容未经受权不得随便应用,转载请分割小编并注明起源。

1、对于 systemd

systemd 是 Linux 系统启动和服务器守护过程管理器,负责在系统启动或运行时,激活系统资源,服务器过程和其它过程,systemd 被设计用来改良原来 sysvinit 中的多个毛病。

CentOS 7 的 systemd 服务程序脚本寄存在 /usr/lib/systemd/ 目录下,并辨别 system 和 user,每一个服务程序脚本以 .service 结尾,例如 /usr/lib/systemd/system/sshd.service。

2、编辑 systemd 服务程序脚本

设定 GreatSQL 二进制文件放在 /usr/local/GreatSQL-8.0.23-14/ 目录下,即设定 basedir 为此目录,先进入到这个工作目录中。

[root@greatsql~]# cd /usr/local/GreatSQL-8.0.23-14/

复制 support-files/greatsql.server 程序脚本到 /usr/lib/systemd/system/ 目录下:

[root@greatsql~]# cp -f ./support-files/greatsql.server /usr/lib/systemd/system/

该脚本内容如下,基本上不须要再批改什么内容:

[root@greatsql~]# cat /usr/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
ExecStartPre=/usr/local/GreatSQL-8.0.23-14/bin/mysqld_pre_systemd
ExecStart=/usr/local/GreatSQL-8.0.23-14/bin/mysqld $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 10000
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false

3、筹备 my.cnf 及其他配置文件

复制 support-files/my.cnf 到 /etc/ 目录下,替换原来的配置文件(原来的 /etc/my.cnf 倡议先备份),并确认 datadir、port、server_id 等参数是否要批改:

[root@greatsql~]# cp -f ./support-files/my.cnf /etc/my.cnf

[root@greatsql~]# cat /etc/my.cnf
#my.cnf
[mysqld]
user = mysql
port = 3306
server_id = 3306
basedir=/usr/local/GreatSQL-8.0.23-14
datadir = /data/GreatSQL
socket = /data/GreatSQL/mysql.sock
pid-file = mysql.pid
character-set-server = UTF8MB4
skip_name_resolve = 1
#若你的 MySQL 数据库次要运行在境外,请务必依据理论状况调整本参数
default_time_zone = "+8:00"

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
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 = 32M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_error = /data/GreatSQL/error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /data/GreatSQL/slow.log
long_query_time = 0.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_bin = /data/GreatSQL/binlog
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
#MySQL 8.0.22 前,想启用 MGR 的话,须要设置 binlog_checksum=NONE 才行
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

#replication settings
master_info_repository = TABLE
relay_log_info_repository = TABLE
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

#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#MGR 本地节点 IP:PORT,请自行替换
loose-group_replication_local_address = "172.16.16.10:33061"
#MGR 集群所有节点 IP:PORT,请自行替换
loose-group_replication_group_seeds = "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
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 = 65535
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 & innodb_status_output_locks 后, 可能会导致 log_error 文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864

#innodb monitor settings
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"

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

再复制 support-files/sysconfig/mysql 文件到 /etc/sysconfig 目录下。

[root@greatsql~]# cp -f ./support-files/sysconfig/mysql /etc/sysconfig/mysql

[root@greatsql~]# cat /etc/sysconfig/mysql
LD_PRELOAD=/usr/lib64/libjemalloc.so.1

这是为了能让 GreatSQL 启动时加载 jemalloc,取得更好的内存治理性能。

4、启动 GreatSQL 服务

上述筹备工作实现后,还要将 GreatSQL 服务正式加载到 systemd 列表中:

[root@greatsql~]# systemctl daemon-reload

这就会主动将 GreatSQL 这个新服务加载进来了。

而后开始启动服务:

[root@greatsql~]# systemctl start greatsql

#如果加上 .service 后缀也能够,例如
[root@greatsql~]# systemctl start greatsql.service

如果启动失败,可能会输入相似的报错提醒:

[root@greatsql~]# systemctl start greatsql
Job for greatsql.service failed because the control process exited with error code. See "systemctl status greatsql.service" and "journalctl -xe" for details.

依照谬误提醒,执行 systemctl status greatsql.service 或 journalctl -xe 查看失败的起因,亦可查看系统日志 /var/log/messages 或 GreatSQL 日志 /data/GreatSQL/error.log 确认失败起因。

第一次会比较慢,因为还要初始化 InnoDB 零碎表空间、redo、undo 日志等。初始化时,还会为 root 生成长期明码,例如这样:

...
[MY-010454] [Server] A temporary password is generated for root@localhost: dofI,0HE#E>C
...

首次登入须要用这个明码,并立刻批改成新密码,新密码还必须合乎平安标准才行,不能是弱明码:

# 先登入
[root@greatsql~]# mysql -uroot -p -S/data/GreatSQL/mysql.sock
Enter password: ************

# 而后批改明码,否则不能做其余操作
[root@GreatSQL][(none)]> ALTER USER CURRENT_USER() IDENTIFIED BY 'GreatSQL3##)^';

这是因为用默认加载了 validate_password 组件(component),对明码有合规性要求。

[root@GreatSQL][(none)]> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn                      |
+--------------+--------------------+------------------------------------+
|            1 |                  1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+

如果嫌麻烦能够卸载掉(但十分不倡议这么做):

[root@GreatSQL][(none)]> UNINSTALL COMPONENT 'file://component_validate_password';

最初,介绍 systemctl 次要几个的命令:

  • 重载零碎服务:systemctl daemon-reload
  • 启用开机启动:systemctl enable greatsql
  • 禁用开机启动:systemctl disable greatsql
  • 启动服务:systemctl start greatsql
  • 进行服务:systemctl stop greatsql
  • 重启服务:systemctl restart greatsql
  • 查看服务状态:systemctl status greatsql

全文完。

Enjoy GreatSQL :)

文章举荐:

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6…

万答 #12,MGR 整个集群挂掉后,如何能力主动选主,不必手动干涉
https://mp.weixin.qq.com/s/07…

『2021 数据技术嘉年华·ON LINE』:《MySQL 高可用架构演进及实际》
https://mp.weixin.qq.com/s/u7…

一条 sql 语句慢在哪之抓包剖析
https://mp.weixin.qq.com/s/AY…

万答 #15,都有哪些状况可能导致 MGR 服务无奈启动
https://mp.weixin.qq.com/s/in…

技术分享 | 为什么 MGR 一致性模式不举荐 AFTER
https://mp.weixin.qq.com/s/rN…

对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支,专一于晋升 MGR 可靠性及性能,反对 InnoDB 并行查问个性,是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

Bilibili:
https://space.bilibili.com/13…

微信 &QQ 群:
可搜寻增加 GreatSQL 社区助手微信好友,发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群:533341697
微信小助手:wanlidbc

本文由博客一文多发平台 OpenWrite 公布!

退出移动版