local mysql server for testing

example.my.cnf

[mysqld]
innodb_buffer_pool_size=10G
query_cache_size=64M
skip-name-resolve
max_heap_table_size=64M
tmp_table_size=64M
max_connections = 200

innodb_read_io_threads          = 64
innodb_write_io_threads         = 64
innodb_io_capacity             = 2500  # Depends on the storage tech - use 2000 for SSD, more for NVMe
innodb_io_capacity_max         = 5000  # Usually double the value of innodb_io_capacity

local-mysql.sh

#!/usr/bin/env bash

# Actually we should be able to use any directory as mysql data directory as long as current user has write access to that dir
# But it will not work for most of the Linux distribution.
# It will work only if we create a datadir in /tmp directory.
# It is because the apparmor system servive is blocking mysql from accessing any random files/directories even if the runnig user have enough permissions
# if you want to create datadir in your home dir , edit /etc/apparmor.d/local/usr.sbin.mysqld and give necessary permissions

# Usage ##

## First time initialisation
# >> local-mysql.sh init

## After initialization
# >> local-mysql.sh start # To start server 
# >> local-mysql.sh stop # To stop server 

srvid=$2
[[ -z $srvid ]] && srvid=1

workingDir="$(dirname $(readlink -f $0))"
projDir="$workingDir/mysql-tmp-$srvid"
confFile="$projDir/my.cnf"

[[ -d $projDir ]] || mkdir $projDir
[[ -f $confFile ]] || touch $confFile

datadir="$projDir/data"
logFile="$projDir/logs"

[[ -d $datadir ]] || mkdir -p $datadir
SRV_SOCKET=$datadir/mysql.sock;

initserver(){
  mariadb-install-db \
    --basedir=/usr \
    --user=$USER \
    --log-error=$logFile \
    --datadir=$datadir

  # mysqld \
  #   --defaults-file=$confFile \
  #   --basedir=/usr \
  #   --user=$USER \
  #   --datadir=$datadir \
  #   --pid-file=$datadir/clone.pid \
  #   --port=3310 \
  #   --server-id=$srvid \
  #   --socket=$datadir/mysql.sock \
  #   --tmpdir=$datadir \
  #   --initialize-insecure
}

startserver(){
  mysqld \
    --defaults-file=$confFile \
    --basedir=/usr \
    --datadir=$datadir \
    --pid-file=$datadir/clone.pid \
    --port=$(( 3310 + srvid )) \
    --server-id=$srvid \
    --socket=$SRV_SOCKET \
    --tmpdir=$datadir \
    --log-error=$logFile
}

stopserver(){
  kill $(cat $datadir/clone.pid)
}

cleanserver(){
  rm -rf $projDir/data
}

if [[ $1 == 'start' ]];then
  startserver &
fi

if [[ $1 == 'init' ]];then
  initserver
  echo "starting server for setting password..."
  startserver &
  sleep 3
  echo "set password for root@localhost = PASSWORD('secret');" | mysql -S "$SRV_SOCKET"
  echo "set password for $USER@localhost = PASSWORD('secret');" | mysql -S "$SRV_SOCKET"
  echo "CREATE USER 'root'@'%' IDENTIFIED BY 'secret'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';FLUSH PRIVILEGES;" | mysql -S "$SRV_SOCKET"
  echo "stopping server after setting password..."
  stopserver
fi

if [[ $1 == 'stop' ]];then
  stopserver
fi

if [[ $1 == 'clean' ]];then 
  cleanserver
fi