mariadb 11.0.6-shell安装

!/bin/bash

centos7 install mariadb 11.0.6 use binary packages

author jacker 20240726

version 1.0.0

1.Define mariadb environment variable

host ip

HOST_IP=$(ip a | grep -A 3 ens33: | grep inet | awk '{print $2}' | awk -F '/' '{print $1}')
OS_VER=$(cat /etc/centos-release | awk '{print $4}'|awk -F '.' '{print $1}')

mariadb version

MARDB_VERSION_BIG="11.0"
MARDB_VERSION_SML="6"
MARDB_VERSION="${MARDB_VERSION_BIG}.${MARDB_VERSION_SML}"

mariadb storage path

MARDB_SRC_TGZ="/opt/mariadb-$MARDB_VERSION-linux-systemd-x86_64.tar.gz"
MARDB_SRC_DIR="/opt/mariadb-$MARDB_VERSION-linux-systemd-x86_64"
MARDB_INS_DIR="/usr/local/mysql"
MARDB_DATA_DIR="$MARDB_INS_DIR/data"
MARDB_LOG_DIR="$MARDB_INS_DIR/log"

mariadb database root user password

MARDB_ROOT_PWD="123456"
MARDB_PORT=3306

2. configure YUM source

IF_YUM_SOURCE=$(grep -E 'hnuaweicloud.com|aliyun.com' /etc/yum.repos.d/CentOS-Base.repo |wc -l)
if [ $IF_YUM_SOURCE -eq 0 ]; then
if [[ $OS_VER -eq 7 ]]; then
sudo cp -f /etc/yum.repos.d/{CentOS-Base.repo,CentOS-Base.repo.bakdate +%F}
sudo curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.huaweicloud.com/repository/conf/CentOS-7-anon.repo
elif [$OS_VER -eq 8 ]; then
sudo cp -f /etc/yum.repos.d/{CentOS-Linux-BaseOS.repo,CentOS-Linux-BaseOS.repo.backupdate +%F}
sudo cp -f /etc/yum.repos .d/{CentOS-Linux-AppStream.repo,CentOS-Linux-AppStream.repo.backupdate +%F}
sudo curl -o /etc/yum.repos.d/CentOS-Linux-BaseOS.repo http://mirrors.aliyun.com/repo/Centos-8.repo
sudo curl -o /etc/yum.repos.d/CentOS-Linux-AppStream.repo http://mirrors.aliyun.com/repo/Centos-8.repo
else
echo "system is not Cenos7 or Cenos8"
exit 1
fi
if [[ ! $? -eq 0 ]]; then
echo "network disconnect"
exit 1
fi
fi
yum clean all && yum makecache
rpm -qa | grep wget || sudo yum install -y wget

3.start downloading mariadb-$MARDB_VERSION.tar.gz installation packages

cd /opt/
if [ ! -e $MARDB_SRC_TGZ ];
then
echo "start downloading mariadb-$MARDB_VERSION.tar.gz installation packages，please wait for..."
wget -t 4 https://downloads.mariadb.com/MariaDB/mariadb-$MARDB_VERSION_BIG/bintar-linux-systemd-x86_64/mariadb-$MARDB_VERSION-linux-systemd-x86_64.tar.gz -P /opt
if [ $? -eq 0 ];
then
echo "mariadb安装包下载成功......."
else

fi

fi
echo "Begining install mariadb"

3.remove mariadb old installation packages

rpm -qa | grep -E "mysql|mariadb" && rpm -e --nodeps rpm -qa | grep -E "mysql|mariadb"

4.Copy the installation files to installation path after decompression completed.

cd /opt/
tar zxf $MARDB_SRC_TGZ
echo "decompression completed....."
cp -a $MARDB_SRC_DIR $MARDB_INS_DIR

5.create mysql group and mysql user

id mysql || groupadd mysql;useradd -r -g mysql -s /bin/false mysql -d $MARDB_DATA_DIR

create data，log directory

create lv lv_mariadb and mount /data/mysql directory

mkdir $MARDB_DATA_DIR
mkdir $MARDB_LOG_DIR

if lsblk | grep -q sdb;then
mkdir $MARDB_DATA_DIR
pvcreate /dev/sdb1
vgcreate vg_mariadb /dev/sdb1
lvcreate -n lv_mariadb -L 9.99G vg_mariadb
mkfs.xfs /dev/vg_mariadb/lv_mariadb
mount /dev/vg_mariadb/lv_mariadb /data/mysql/
grep $MARDB_DATA_DIR &>/dev/null && echo "mount $MARDB_DATA_DIR successful" || "mount $MARDB_DATA_DIR failed"

else
echo "sdb disk not exist,fisrt add sdb disk"
fi
chown -R mysql:mysql $MARDB_INS_DIR

6.create mariadb my.cnf cconfigure file

cat > /etc/my.cnf <<EOF
[mysqld]

基础设置

user = mysql
basedir = $MARDB_INS_DIR
datadir = $MARDB_DATA_DIR
socket = /var/lib/mysql/mysql.sock
port = 3306

字符集设置

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

性能和日志设置

log_error = $MARDB_LOG_DIR/error.log
slow_query_log = 1
slow_query_log_file = $MARDB_LOG_DIR/slow-query.log
long_query_time = 2

缓存和连接设置

key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

排序和临时表设置

sort_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M

并发和超时设置

max_connections = 100
wait_timeout = 28800
interactive_timeout = 28800

InnoDB设置

innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

其他可选设置...

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

EOF

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

8.execute installation script to install mariadb database

cd $MARDB_INS_DIR
./scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
cp -a $MARDB_INS_DIR/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

8.开始安全初始化

sudo yum install epel-release && sudo yum install expect
expect -c "
set timeout 20
spawn mysql_secure_installation

expect "Enter current password for root (enter for none):"
send "s\r"

expect "Set root password?"
send "y\r"

expect "New password:"
send "$MARDB_ROOT_PWD\r"

expect "Re-enter new password:"
send "$MARDB_ROOT_PWD\r"

expect "Remove anonymous users?"
send "y\r"

expect "Disallow root login remotely?"
send "n\r"

expect "Remove test database and access to it?"
send "y\r"

expect "Reload privilege tables now?"
send "y\r"

expect eof

8.开启防火墙

firewall-cmd --zone=public --add-port=$MARDB_PORT/tcp --permanent &> /dev/null && firewall-cmd --reload
mariadb -uroot -p"$MARDB_ROOT_PWD" -e "GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '$MARDB_ROOT_PWD' WITH GRANT OPTION;"
mariadb -uroot -p"$MARDB_ROOT_PWD" -e "FLUSH PRIVILEGES;"
mariadb -uroot -p"MYSQL_NEW_PWD" -h $HOST_IP -P $MYSQL_PORT -e "SHOW DATABASES;"
if[ $? -eq 0 ];then
echo "DB set PRIVILEGES successful! "
else
echo "DB set PRIVILEGES failed! "
fi