/root/bin/mysql_create_db.sh for /root/bin/update_sites.sh

#!/usr/bin/env bash

#
# create mysql database and user
# (c)2016 by jheine/AT/tecracer/DOT/de
#

################# settings ###################

report_path=/home/admin/
report_user=admin

. /root/mysql.txt

secret=xxx

log=/var/log/create_mysql.log

###############################################


[[ -z $1 ]] && {
  echo "usage: $0 <sitename>"
  exit 1
}
sitename=$1

[[ $sitename =~ ^(mysql|information_schema|innodb|performance_schema)$ ]] && {
  echo "ERROR: bad database name '$sitename'"
  exit 255
}

typo3_db_password=$(echo ${secret}${sitename}|sha1sum|base64|sed 's/.\{6\}$//')

typo3_db_host=$DBHOSTNAME
typo3_db_name=$sitename
typo3_db_user=${sitename:0:16}

touch $report_path/$sitename.php
chmod 600 $report_path/$sitename.php
chown $report_user:root $report_path/$sitename.php
echo "<?php

  # settings for site $sitename
  # generated at $(date)

  \$DBHOST = \"$typo3_db_host\";
  \$DBNAME = \"$typo3_db_name\";
  \$DBUSER = \"$typo3_db_user\";
  \$DBPASS = \"$typo3_db_password\";

?>
" > $report_path/$sitename.php

sql_commands="
  CREATE USER '$typo3_db_user'@'%' IDENTIFIED BY '$typo3_db_password';
  CREATE DATABASE IF NOT EXISTS \`$typo3_db_name\` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  GRANT USAGE ON * . * TO 'typo3_db_user'@'%' IDENTIFIED BY '$typo3_db_password' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
  CREATE DATABASE IF NOT EXISTS \`$typo3_db_name\` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  GRANT ALL PRIVILEGES ON \`$typo3_db_name\` . * TO '$typo3_db_user'@'%';
"

IFS=$'\n'
for sql in $sql_commands; do
  #echo "SQL: "$sql
  echo "$sql" | mysql -h $DBHOSTNAME -u $DBUSERNAME -p$DBPASSWORD &>> $log
done

# unset vars
secret=""
typo3_db_password=""