#!/bin/bash
# installsql.sh : a simple MySQL and MariaDB installer by Brain 0verride
# https://twitter.com/brain0verride
# https://www.christophe-casalegno.com

replacer='sed' 				# command used for replacement 
repoptions='-i' 			# options for the replacer
downloader='apt -y -f -d install'
installer='apt -y -f install' 

sources_list='/etc/apt/sources.list.d'
auto='debconf-set-selections'
export DEBIAN_FRONTEND=noninteractive

memory=$(free | grep -v -i swap |awk 'FNR == 2 {print $4}' |awk '{ byte = $1 /1024/1024 ; byte =$1 /1024/2 ; print byte}' |cut -d "." -f1)
uptime=$(uptime -p)
loadavg=$(cat /proc/loadavg)
ncores=$(grep processor /proc/cpuinfo |grep -vc name)
memory_used=$(free | awk 'FNR == 2 {print 100-(($2-$3)/$2)*100}' |cut -d "." -f1)

initsql="init.sql"

db_type=$1
version=$2
mariadb_version="$version"
mysql_version="$version"
serverid="10"

needps='pwgen screen libdbd-mysql-perl libwww-perl librsync1 librsync-dev rdiff-backup pigz curl bc vim rsync lsb-release apt-transport-https ca-certificates net-tools iotop htop wget curl lynx dirmngr software-properties-common'

function deploy()
{
  installer="$1"
  packs="$2"

  echo "Installing packages : $packages..."
  $downloader $packs
  $installer $packs
}

function genpass()
{
  charx="$1"
  pwgen -A -B "$charx" 1
}

function sourcesupdate()
{
  apt-get update
}

function writefile()

{
option=$1
file2write="$2"
contentfile="$3"

if [ "$option" = "add" ]
then

cat >>$file2write <<EOF
$contentfile
EOF

fi

if [ "$option" = "create" ]
then

cat >$file2write <<EOF
$contentfile
EOF

fi
}

sourcesupdate
apt -y upgrade

deploy "$installer" "$needps"

apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xF1656F24C74CD1D8 # add key db install
apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 5072E1F5


if [ "$db_type" = "mariadb" ]

  then
      
      add-apt-repository "deb [arch=amd64,i386,ppc64el] http://ftp.utexas.edu/mariadb/repo/$mariadb_version/debian stretch main" # add Mariadb repository

else

  if [ "$db_type" = "mysql" ]
 
    then
        add-apt-repository "deb http://repo.mysql.com/apt/debian/ stretch mysql-$mysql_version main " # add MySQL repository
  fi
fi


if [ "$db_type" = "mariadb" ]
	
	then

		commonp='mariadb-server mariadb-client'
else 

	if [ "$db_type" = "mysql" ]
	
		then
		
			commonp='mysql-community-server mysql-community-client'
	fi

fi

auto='debconf-set-selections'

export DEBIAN_FRONTEND=noninteractive

# Mysql Server
$auto <<< "mysql-server mysql-server/root_password password $mysqlroot_pass"
$auto <<< "mysql-server mysql-server/root_password_again password $mysqlroot_pass"
$auto <<< "mysql-community-server mysql-community-server/root-pass password $mysqlroot_pass"
$auto <<< "mysql-community-server mysql-community-server/re-root-pass password $mysqlroot_pass"
$auto <<< "mysql-community-server mysql-server/default-auth-override select Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)"
$auto <<< "mysql-community-server mysql-community-server/default-auth-override select Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)"

# MariaDB Server
$auto <<< "mariadb-server-$mariadb_version mysql-server/root_password_again password $mysqlroot_pass"
$auto <<< "mariadb-server-$mariadb_version mysql-server/root_password_again password $mysqlroot_pass"
$auto <<< "mariadb-server mysql-server/root_password_again password $mysqlroot_pass"
$auto <<< "mariadb-server mysql-server/root_password_again password $mysqlroot_pass"

sourcesupdate

deploy "$installer" "$commonp"

mysqlroot_pass=$(genpass "12")

echo "$mysqlroot_pass" > .p 

if [ "$db_type" = "mariadb" ]
then

max_heap_table_size=$((memory/16))M
tmp_table_size=$((memory/16))M
innodb_buffer_pool_size=$((memory/2))M

mysql_conf='/etc/mysql/my.cnf'

content_mycnf="
# StackX configuration by ScalarX Technology - https://scalarx.com
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
server-id=$serverid 
log-bin=mysql-bin
skip-external-locking
bind-address            = *
key_buffer_size         = 256M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 256
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 128M
max_connections         = 128
connect_timeout         = 10
wait_timeout            = 600
table_cache = 4096
table_open_cache = 4096
table_definition_cache = 4096
max_heap_table_size = $max_heap_table_size
sort_buffer_size        = 32M
bulk_insert_buffer_size = 16M
tmp_table_size          = $tmp_table_size
open_files_limit = 128000


log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_buffer_pool_size = $innodb_buffer_pool_size
innodb_log_buffer_size  = 16M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=16
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit=2

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer_size         = 16M

!includedir /etc/mysql/conf.d/
"

myname=$(uname -n)

content_genmysql1="
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('$mysqlroot_pass');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('$mysqlroot_pass');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('$mysqlroot_pass');
SET PASSWORD FOR 'root'@'$myname' = PASSWORD('$mysqlroot_pass');
FLUSH PRIVILEGES;
"

writefile "create" "/root/init.sql" "$content_genmysql1"

writefile "create" "$mysql_conf" "$content_mycnf" # Mysql configuration

service mysql restart

mysql --database=mysql < /root/init.sql

service mysql restart

fi

if [ "$db_type" = "mysql" ]
then
max_heap_table_size=$((memory/16))M
tmp_table_size=$((memory/16))M
innodb_buffer_pool_size=$((memory/2))M

mysql_conf='/etc/mysql/my.cnf'

if [ "$mysql_version" = "8.0" ]

then
	lc="#lc-messages-dir = /usr/share/mysql"
	ql="#query_cache_limit       = 1M"
	qcs="#query_cache_size        = 128M"
	dap="default-authentication-plugin=mysql_native_password"
else
	lc="lc-messages-dir = /usr/share/mysql"
	ql="query_cache_limit       = 1M"
	qcs="query_cache_size        = 128M"
	dap="#default-authentication-plugin=mysql_native_password"
fi


content_mycnf="
# StackX configuration by ScalarX Technology - https://scalarx.com
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
$lc
$dap
server-id=$serverid 
log-bin=mysql-bin
skip-external-locking
bind-address            = *
key_buffer_size         = 256M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 256
$ql 
$qlc 
max_connections         = 128
connect_timeout         = 10
wait_timeout            = 600
table_open_cache = 4096
table_definition_cache = 4096
max_heap_table_size = $max_heap_table_size
sort_buffer_size        = 32M
bulk_insert_buffer_size = 16M
tmp_table_size          = $tmp_table_size
open_files_limit = 128000


log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_buffer_pool_size = $innodb_buffer_pool_size
innodb_log_buffer_size  = 16M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=16
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit=2

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer_size         = 16M

!includedir /etc/mysql/conf.d/
"
myname=$(uname -n)

if [ "$mysql_version" = "8.0" ] 
then

content_genmysql1="
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '$mysqlroot_pass';
FLUSH PRIVILEGES;
"

else

content_genmysql1="
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('$mysqlroot_pass');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('$mysqlroot_pass');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('$mysqlroot_pass');
SET PASSWORD FOR 'root'@'$myname' = PASSWORD('$mysqlroot_pass');
FLUSH PRIVILEGES;
"
fi

writefile "create" "/root/init.sql" "$content_genmysql1"
writefile "create" "$mysql_conf" "$content_mycnf" 

service mysql restart

mysql --database=mysql < /root/init.sql

service mysql restart

fi