Multi instance implementation of mariadb based on binary installation
1. mariadb version: 10.2.23
2. shell script for binary installation of mariadb
#!/bin/bash
id mysql &>/dev/null
if [ `echo $?` -ne 0 ];then
userdel -r mysql &>/dev/null
useradd -r -u 336 -s /sbin/nologin -d /data/mysql mysql &>/dev/null
else
useradd -r -u 336 -s /sbin/nologin -d /data/mysql mysql &>/dev/null
fi
rpm -q libaio &>/dev/null
[ `echo $?` -ne 0 ] && yum -y install libaio
rpm -q expect &>/dev/null
[ `echo $?` -ne 0 ] && yum -y install expect
if [ -e mariadb-10.2.23-linux-x86_64.tar.gz ];then
#This file may cause impact, so empty it first
> /etc/my.cnf
tar xf mariadb-10.2.23-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mariadb-10.2.23-linux-x86_64/ mysql
chown -R root.root /usr/local/mysql/
echo 'PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
mkdir /data/mysql -p
chown mysql.mysql /data/mysql/
cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
mkdir -p /etc/mysql
cp /usr/local/mysql/support-files/my-huge.cnf /etc/mysql/my.cnf
#Disable hostname resolution, recommended
sed -ri '/^\[mysqld\]/askip_name_resolve = on' /etc/mysql/my.cnf
sed -ri '/^\[mysqld\]/adatadir=\/data\/mysql' /etc/mysql/my.cnf
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
expect <<EOF
spawn mysql_secure_installation
expect {
"Enter current password for root" { send "\n";exp_continue }
"Set root password" { send "\n";exp_continue }
"New password" { send "123456\n";exp_continue }
"Re-enter new password" { send "123456\n";exp_continue }
"Remove anonymous users" { send "y\n";exp_continue }
"Disallow root login remotely" { send "y\n";exp_continue }
"Remove test database and access to it" { send "y\n";exp_continue }
"Reload privilege tables now" { send "y\n" }
}
#expect "]#" { send "exit\n" }
expect eof
EOF
mysqladmin -uroot -p123456 ping &>/dev/null
[ `echo $?` -eq 0 ] && echo 'mysql is running !' || echo 'mysql is stopped'
else
echo -e "mariadb-10.2.23-linux-x86_64.tar.gz is not exist"
exit
fi
3. Implement multiple examples
1. Create a directory structure corresponding to multiple instances
mkdir -p /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
chown -R mysql.mysql /mysql/
2. Create a multi instance database file
/usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3306/data/ --user=mysql
/usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3307/data/ --user=mysql
/usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3308/data/ --user=mysql
3. Create the corresponding configuration file
cp /etc/mysql/my.cnf /mysql/3306/etc/
vim /mysql/3306/etc/my.cnf
[mysqld]
port=3306 Add one line
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid
#3307 and 3308 synchronous modification
cp /mysql/3306/etc/my.cnf /mysql/3307/etc/my.cnf
/mysql/3307/etc/my.cnf modify
cp /mysql/3306/etc/my.cnf /mysql/3308/etc/my.cnf
/mysql/3308/etc/my.cnf modify
4. Prepare the startup script of each instance and add the execution permission
vi /mysql/{3306,3307,3308}/bin/mysqld
cat /mysql/3306/bin/mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="123456"
#cmd_path="/mysql/3306/bin"
cmd_path="/usr/local/mariadb-10.2.23-linux-x86_64/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
#Add execution permission
chmod +x /mysql/{3306,3307,3308}/bin/mysqld
5. Start the service and secure it (if not, there is a problem with the stop script and the password is incorrect)
/mysql/{3306,3307,3308}/bin/mysqld start
#Safety reinforcement
/Usr / local / MySQL / bin / MySQL ﹣ secure ﹣ installation - S / MySQL / {330633073308} / socket / mysql.sock (the password entered is consistent with the startup script)
6. Test connection
mysql -uroot -p123456 -S /mysql/{3306,3307,3308}/socket/mysql.sock