mariaDB数据库安装配置-集群搭建步骤5

        MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。

        在配合slurm搭建集群的过程中，推荐利用数据库来存储slurm的作业记账信息以及作业完成信息等数据，数据库存储稳定、高效。此次搭建集群过程中，使用mariaDB数据库来存储作业信息。

slurmdbd服务是控制作业统计的进程，一般在集群中只需要在主控节点配置slurmdbd服务即可，因此数据库服务也同样在主控节点配置即可。此次搭建过程中选择cn03作为主控节点来部署mariaDB数据库服务。

系统版本：centos7.9

主控节点: 192.168.1.203（cn03）

1、mariaDB数据库安装

mariaDB数据库需安装以下软件包：

yum install mariadb-server mariadb-devel

安装完成后需启动mariadb服务并加入开机自启：

systemctl enable mariadb
systemctl start mariadb

2、添加slurm用户

        首次启动mariadb服务root用户密码默认为空，进入数据库之前需先通过命令行进入mariadb数据库设置root密码，执行以下命令即可。        

mysql_secure_installation

        出现如下界面：         

         当前root密码为空，直接回车，进入下一步，按照屏幕输出的提示一步步设置密码即可：

         后续的选项可以直接回车跳过。  

        设置完root密码之后，需进入数据库添加slurm用户以及创建slurm记账数据表。  

mysql -u root -ppassword   #password为之前你设置的root密码

         进入数据库之后执行以下命令添加slurm用户及数据表：  

# 生成slurm用户，以便该用户操作slurm_acct_db数据库，其密码是SomePassWD，可自行设定
create user 'slurm'@'localhost' identified by 'SomePassWD';

# 生成账户数据库slurm_acct_db
create database slurm_acct_db;
# 赋予slurm从本机localhost采用密码SomePassWD登录具备操作slurm_acct_db数据下所有表的全部权限
grant all on slurm_acct_db.* TO 'slurm'@'localhost' identified by 'SomePassWD' with grant option;
# 赋予slurm从cn0采用密码SomePassWD登录具备操作slurm_acct_db数据下所有表的全部权限
grant all on slurm_acct_db.* TO 'slurm'@'cn0' identified by 'SomePassWD' with grant option;

# 生成作业信息数据库slurm_jobcomp_db
create database slurm_jobcomp_db;
# 赋予slurm从本机localhost采用密码SomePassWD登录具备操作slurm_jobcomp_db数据下所有表的全部权限
grant all on slurm_jobcomp_db.* TO 'slurm'@'localhost' identified by 'SomePassWD' with grant option;
# 赋予slurm从cn0采用密码SomePassWD登录具备操作slurm_jobcomp_db数据下所有表的全部权限
grant all on slurm_jobcomp_db.* TO 'slurm'@'cn0' identified by 'SomePassWD' with grant option;

3、修改数据库配置文件  

        mariadb默认的配置文件为/etc/my.cnf，

        编辑内容如下：

# The following options will be passed to all MySQL clients
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8mb4

# Here follows entries for some specific programs
[mariadb_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

# The MySQL server
[mariadb]
# explicit_defaults_for_timestamp = true
datadir=/var/lib/mysql
port = 3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

interactive_timeout=1200
wait_timeout=1800

skip_name_resolve=OFF
innodb_file_per_table=ON
max_connections=2048
max_connect_errors=1000000
max_allowed_packet=16M
sort_buffer_size=512K
net_buffer_length=16K
read_buffer_size=512K
read_rnd_buffer_size=512K
character_set_server=utf8mb4
collation_server=utf8mb4_bin
thread_stack=256K
thread_cache_size=384
tmp_table_size=96M
max_heap_table_size=96M

#open slow query
slow_query_log=OFF
slow_query_log_file=/var/lib/mysql/mysql-slow-query.log
#set slow time to default 10 second, minimum value 0
long_query_time=4

local_infile=OFF

# binary logging is required for replication
#log_bin=mysql-bin

#master - slave syncronized setting
log_slave_updates=ON

server-id=1

log-bin=mysql-bin
sync_binlog=1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
sync_binlog=1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all


# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
event_scheduler=ON
default_storage_engine=InnoDB
innodb_buffer_pool_size=1024M  #64M # 1024M
innodb_purge_threads=1
innodb_log_file_size=128M
innodb_log_buffer_size=2M
innodb_lock_wait_timeout=900  #120

bulk_insert_buffer_size=32M
myisam_sort_buffer_size=8M

#MySQL rebuild index allowed maxmum cache file
myisam_max_sort_file_size=4G
myisam_repair_threads=1
lower_case_table_names=0

[mysqldump]
quick
max_allowed_packet=16M

#[isamchk]
#key_buffer = 16M
#sort_buffer_size = 16M
#read_buffer = 4M
#write_buffer = 4M

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=4M
write_buffer=4M

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

        修改完配置之后需重启mariadb服务，重启过程中发现启动失败，通过journalctl -xe命令查看报如下错误：  

        mariadb InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880  

        错误原因是因为my.cnf配置文件中修改了innoDB的日志文件大小，此时需进入innodb的日志文件目录，把原来的日志文件删除即可，这里用mv做了备份；        

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

        然后重启mariadb服务即可。

systemctl restart mariadb