newMariaDB Administration

#!/bin/bash

#################################################
### Whith this script you can simply create a new mysql database.
###
### To create new database, user and garant privileges do:
### newMariaDB newDB
###
### For first use of mysql do:
### newMariaDB mariaInitial



# trap ctrl-c and call ctrl_c()
trap ctrl_c INT
SCRIPT_PATH="$(realpath $0)"

function ctrl_c() {
   exit
}

green() {
   echo -e "\033[0;32m$1\033[0m"
}

red() {
   echo -e "\033[0;31m$1\033[0m"
}

echoOrange() {
   echo -en '\033[0;33m'
}

echoNC() {
   echo -en '\033[0m'
}

mariaDirDisableCoW() {
   if df -T / | cut -d " " -f2 | grep -wq "btrfs"; then
      green "mariaDirDisableCoW"
      d=/var/lib/mysql
      f=/var/lib/mysql_old
      fs=/var/lib/mysql_old.s_back
      [[ -d $f ]] && [[ -d $fs ]] && sudo rm -r $fs
      [[ -d $f ]] && sudo mv $f $fs
      sudo mv $d $f || red "failed to move $d $f"
      sudo mkdir $d || red "failed to mkdir $d"
      sudo chattr -R +C $d || red "failed to chattr $d"
      sudo cp -a --reflink=never $f/. $d || red "failed to cp files"
      #sudo rm -rf $f
      red "if everything went well you can do: rm -rf $f"
      [[ -d $fs ]] && red "if everything went well you can do: rm -rf $fs"
   fi
}

mariaInitial() { #help
   if mysql -uroot -sse "SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = '$DBUSER')"; then
      red "User root already exist"
   else
      green "First Maria usage"
      green "if system = btrfs && disable Copy-on-Write for /var/lib/mysql"
      mariaDirDisableCoW
      green "mariadb-install-db"
      sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
   fi
}

maria() {
   mysql -u root -p"${rootpasswd}" -e "$1"
}

echoMaria() {
   echoOrange
   echo "$mariasCode"
   echoNC
   maria "$mariasCode"
}

readMaria() {
   echoOrange
   echo -n "$mariasCode"
   echoNC
   read
   maria "$mariasCode"
}

users() { #help
   green "List all users:"
   mariasCode="SELECT DISTINCT User FROM mysql.user;"
   echoMaria
}

databases() { #help
   green "List all databases:"
   mariasCode="SHOW DATABASES"
   echoMaria
}

userDatabases() { #help
   green "Get databases of user"
   unlockMaria
   getUser
   mariasCode="SELECT Db FROM mysql.db WHERE User=\"$user\";"
   echoMaria
}

createUser() { #help
   green "Create new maria user"
   unlockMaria
   getUser
   green "Please enter db-password"
   read PASSWDDB
   mariasCode="CREATE USER \`${user}\`@'localhost' IDENTIFIED BY '${PASSWDDB}';" 
   readMaria
   users
}

createDatabase() { #help
   green "Create new database"
   unlockMaria
   getDatabase
   mariasCode="CREATE DATABASE \`${database}\`;"
   readMaria
   databases
}

grantPrivileges() { #help
   green "Grant all privileges on database for user:"
   unlockMaria
   [ -z $user ] && getUser
   [ -z $database ] && getDatabase
   mariasCode="GRANT ALL PRIVILEGES ON \`${database}\`.* TO '${user}'@'localhost';"
   readMaria
   mariasCode="FLUSH PRIVILEGES;"
   readMaria
}

alterUserPassword() { #help
   red "Change user password:"
   unlockMaria
   getUser
   getPassword
   mariasCode="ALTER USER \"$user\"@localhost IDENTIFIED BY \"$password\";"
   readMaria
}

dropUser() { #help
   red "Drop user"
   unlockMaria
   getUser
   mariasCode="DROP USER IF EXISTS \`${user}\`@'localhost';"
   readMaria
   users
}

dropDatabase() { #help
   red "Drop database"
   unlockMaria
   getDatabase
   mariasCode="DROP DATABASE \`${database}\`;"
   readMaria
   databases
}

getUser() {
   users
   echo "Enter a username: "
   read user
}

getPassword() {
   green -n "Enter a password: "
   read password
}

getDatabase() {
   databases
   green "Enter a database name"
   echo -n "db-name: "
   read database
}

unlockMaria() {
   [ -z "$rootpasswd" ] && getMariaRootPw
}
getMariaRootPw() {
   green "Enter a root user MySQL password!"
   echo "Note: password will be hidden when typing"
   echo -n "MySQL root password: "
   read -s rootpasswd
   maria
   echo ""
}

newDB() { #help
   green "Create new Maria database and user"
   unlockMaria
   createUser
   createDatabase
   grantPrivileges
}

list() { #help
   green "List users and databases:"
   unlockMaria 
   users
   databases
}

--help() {
   echo "## Help"
   cat $SCRIPT_PATH | grep -A1 "() { #help" | sed "s/() {//g" | sed "s/ red //g" | sed "s/ green //g" | sed "s/#help//g" | head -n -2
}

-h() {
 --help
}
[[ ! -z ${@} ]] && ${@} || --help