345 lines
8.5 KiB
Bash
Executable File
345 lines
8.5 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
# Mysql management using mariadb
|
|
|
|
databaseName="mariadb"
|
|
databasePackages="mariadb-server mariadb-client"
|
|
dbName=
|
|
dbUser=
|
|
dbRemote=localhost
|
|
dbPass=
|
|
|
|
step_1_info() {
|
|
echo "Installation of ${databaseName} packages:"
|
|
echoinfo "$databasePackages"
|
|
echoinfo "(Consider step \"latest\" first to setup official repository with the latest version)"
|
|
}
|
|
step_1_alias() { ALIAS=install; }
|
|
step_1() {
|
|
exe apt update
|
|
exe apt install $databasePackages
|
|
endReturn -o $? "Error instaling $databaseName"
|
|
}
|
|
|
|
step_2_info() { echo "Secure ${databaseName} installation"; }
|
|
step_2() {
|
|
exe mysql_secure_installation
|
|
}
|
|
|
|
step_3_info() { echo "${databaseName} configuration"; }
|
|
step_3() {
|
|
addConf -c "$mariadbConfig" "$mariadbConfigLoc"
|
|
|
|
echo -n "Restarting mysql ... "
|
|
exe service mysql restart && echo "ok"
|
|
}
|
|
mariadbConfigLoc="/etc/mysql/mariadb.conf.d/90-myconfig.cnf"
|
|
mariadbConfig="[mysqld]
|
|
innodb_large_prefix=on
|
|
innodb_file_format=barracuda
|
|
innodb_file_per_table=true
|
|
|
|
lower_case_table_names=0
|
|
|
|
# coming from friendica warning 2020.07
|
|
table_definition_cache=1400
|
|
|
|
#innodb_force_recovery=6"
|
|
|
|
step_8_info() { echo "Setup ubuntu $databaseName repository"; }
|
|
step_8_alias() { ALIAS="latest"; }
|
|
step_8() {
|
|
exe apt install curl
|
|
exep "curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --skip-maxscale --skip-tools"
|
|
}
|
|
|
|
step_10_info() {
|
|
echoinfoArgs "[OPTIONS]"
|
|
echo "Create mysql database without specific characterset"
|
|
echoinfo " [OPTIONS]"
|
|
echoinfo " --charset,-c <utf8|utf8mb4> : character set and collate"
|
|
echoinfo " --database, -d : database name"
|
|
echoinfo " [OPTIONS] used in following steps"
|
|
echoinfo " --user, -u : user name"
|
|
echoinfo " Manual password entry for non existing user"
|
|
echoinfo " --remote, -r : ip of allowed remote host"
|
|
}
|
|
step_10_alias() { ALIAS="createdb"; }
|
|
step_10() {
|
|
local arg
|
|
local dbOption=
|
|
|
|
shift
|
|
for arg in "$@" ; do
|
|
case "$1" in
|
|
--charset|-c)
|
|
dbOption="$2"
|
|
shift 2;;
|
|
--database|-d)
|
|
dbName="$2"
|
|
shift 2 ;;
|
|
--user|-u)
|
|
dbUser="$2"
|
|
shift 2 ;;
|
|
--remote|-r)
|
|
dbRemote="$2"
|
|
shift 2 ;;
|
|
*)
|
|
break ;;
|
|
esac
|
|
done
|
|
|
|
case "$dbOption" in
|
|
utf8)
|
|
dbOption="CHARACTER SET utf8 COLLATE utf8_unicode_ci";;
|
|
utf8mb4)
|
|
dbOption="CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";;
|
|
*)
|
|
dbOption="";;
|
|
esac
|
|
|
|
if [ -z "$dbName" ] ; then
|
|
echo " [I] Existing mysql databases:"
|
|
exe mysql -u root -e 'SHOW DATABASES;'
|
|
|
|
read -p "Enter database name: " dbName
|
|
fi
|
|
endCheckEmpty dbName "database name"
|
|
exe mysql -u root -e 'CREATE DATABASE '$dbName' '"$dbOption"';'
|
|
endReturn -o $? "Cannot create database $dbName"
|
|
}
|
|
|
|
step_11_info() {
|
|
echoinfoArgs "[OPTIONS]"
|
|
echo "Create mysql user"
|
|
echoinfo " [OPTIONS]"
|
|
echoinfo " --user, -u : user name"
|
|
echoinfo " with manual password entry for non existing user"
|
|
echoinfo " --remote, -r : ip of allowed remote host"
|
|
}
|
|
step_11_alias() { ALIAS="createuser"; }
|
|
step_11() {
|
|
local arg
|
|
|
|
shift
|
|
for arg in "$@" ; do
|
|
case "$1" in
|
|
--charset|-c)
|
|
dbOption="$2"
|
|
shift 2;;
|
|
--database|-d)
|
|
dbName="$2"
|
|
shift 2 ;;
|
|
--user|-u)
|
|
dbUser="$2"
|
|
shift 2 ;;
|
|
--remote|-r)
|
|
dbRemote="$2"
|
|
shift 2 ;;
|
|
*)
|
|
break ;;
|
|
esac
|
|
done
|
|
|
|
if [ -z "$dbUser" ] ; then
|
|
echo " [I] Existing mysql user:"
|
|
exe mysql -u root -e 'SELECT User, Host FROM mysql.user;'
|
|
read -p "Enter mysql user name: " dbUser
|
|
fi
|
|
endCheckEmpty dbUser "user name"
|
|
|
|
if ! echo "SELECT COUNT(*) FROM mysql.user WHERE user = '$dbUser' AND host = '$dbRemote';" | mysql | grep 1 &>/dev/null; then
|
|
# User does not exist
|
|
if [ $DRY -eq 0 ]; then
|
|
read -s -p "Enter mysql user password: " dbPass
|
|
endCheckEmpty dbPass "password"
|
|
else
|
|
echoseq "Enter mysql password: ...skipped..."
|
|
fi
|
|
exe mysql -u root -e 'CREATE USER '"'"$dbUser"'"'@'"'"$dbRemote"'"' IDENTIFIED BY '"'"$dbPass"'"';'
|
|
endReturn -o $? "Error creating mysql user"
|
|
fi
|
|
}
|
|
|
|
step_12_info() {
|
|
echoinfoArgs "[OPTIONS]"
|
|
echo "Grant privileges"
|
|
echoinfo " [OPTIONS]"
|
|
echoinfo " --database, -d : 'database name'.*"
|
|
echoinfo " --user, -u : user name"
|
|
echoinfo " --remote, -r : ip of allowed remote host"
|
|
}
|
|
step_12_alias() { ALIAS="grant"; }
|
|
step_12() {
|
|
local arg
|
|
|
|
shift
|
|
for arg in "$@" ; do
|
|
case "$1" in
|
|
--charset|-c)
|
|
dbOption="$2"
|
|
shift 2;;
|
|
--database|-d)
|
|
dbName="$2"
|
|
shift 2 ;;
|
|
--user|-u)
|
|
dbUser="$2"
|
|
shift 2 ;;
|
|
--remote|-r)
|
|
dbRemote="$2"
|
|
shift 2 ;;
|
|
*)
|
|
break ;;
|
|
esac
|
|
done
|
|
|
|
exe mysql -u root -e 'GRANT ALL PRIVILEGES ON '$dbName'.* TO '"'"$dbUser"'"'@'"'"$dbRemote"'"';'
|
|
endReturn -o $? "Error assigning privileges on database"
|
|
|
|
exe mysql -u root -e 'FLUSH PRIVILEGES;'
|
|
}
|
|
|
|
step_14_info() {
|
|
echoinfoArgs "[OPTIONS]"
|
|
echo "Revoke all granted privilegs"
|
|
echoinfo " [OPTIONS]"
|
|
echoinfo " --user, -u : user name"
|
|
echoinfo " --remote, -r : ip of allowed remote host"
|
|
}
|
|
step_14_alias() { ALIAS="revokeall"; }
|
|
step_14() {
|
|
local arg
|
|
|
|
shift
|
|
for arg in "$@" ; do
|
|
case "$1" in
|
|
--user|-u)
|
|
dbUser="$2"
|
|
shift 2 ;;
|
|
--remote|-r)
|
|
dbRemote="$2"
|
|
shift 2 ;;
|
|
*)
|
|
break ;;
|
|
esac
|
|
done
|
|
|
|
exe mysql -u root -e 'REVOKE ALL, GRANT OPTION FROM '"'"$dbUser"'"'@'"'"$dbRemote"'"';'
|
|
endReturn -o $? "Error revoking privileges for user $dbUser"
|
|
|
|
exe mysql -u root -e 'FLUSH PRIVILEGES;'
|
|
}
|
|
|
|
step_30_info() { echo "List mysql databases"; }
|
|
step_30_alias() { ALIAS="listdb"; }
|
|
step_30() {
|
|
exe mysql -u root -e 'SHOW DATABASES;'
|
|
echo -e "\nDrop userdb by: mysql -u root -e 'DROP DATABASE userdb;'"
|
|
}
|
|
|
|
step_32_info() { echo "List mysql user"; }
|
|
step_32_alias() { ALIAS="listuser"; }
|
|
step_32() {
|
|
exe mysql -u root -e 'SELECT User, Host FROM mysql.user;'
|
|
echo -e "\nDrop dbuser by: mysql -u root -e 'DROP USER dbuser@localhost;'"
|
|
}
|
|
|
|
step_34_info() {
|
|
echoinfoArgs "[OPTIONS]"
|
|
echo "Show privileges"
|
|
echoinfo " [OPTIONS]"
|
|
echoinfo " --user, -u : user name"
|
|
echoinfo " --remote, -r : ip of allowed remote host"
|
|
}
|
|
step_34_alias() { ALIAS="listprivileges"; }
|
|
step_34() {
|
|
local arg
|
|
|
|
shift
|
|
for arg in "$@" ; do
|
|
case "$1" in
|
|
--user|-u)
|
|
dbUser="$2"
|
|
shift 2 ;;
|
|
--remote|-r)
|
|
dbRemote="$2"
|
|
shift 2 ;;
|
|
*)
|
|
break ;;
|
|
esac
|
|
done
|
|
|
|
exe mysql -u root -e 'SHOW GRANTS FOR '"'"$dbUser"'"'@'"'"$dbRemote"'"';'
|
|
}
|
|
|
|
step_36_info() {
|
|
echoinfoArgs "[DATABASE_NAME]"
|
|
echo "Size of database"
|
|
}
|
|
step_36_alias() { ALIAS="sizedb"; }
|
|
step_36() {
|
|
if [ -z "$2" ]; then
|
|
echo "Please provide a database name. e.g. $0 sizedb mydb_db"
|
|
else
|
|
exe mysql -u root -e 'SELECT table_schema "DB Name",
|
|
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
|
|
FROM information_schema.tables
|
|
WHERE table_schema="'$2'"
|
|
GROUP BY table_schema;'
|
|
fi
|
|
}
|
|
|
|
step_50_info() {
|
|
echoinfoArgs "<DATABASE NAME> <TARGET DIR>"
|
|
echo "Backup (dump) a mysql database"
|
|
}
|
|
step_50_alias() { ALIAS="backup"; }
|
|
step_50() {
|
|
shift # step number not used
|
|
if [ -z $1 ] ; then
|
|
echoerr " [E] No database name provided"
|
|
return 1
|
|
fi
|
|
local dbName="$1"
|
|
local buTarget="$2"
|
|
if [ -z "$2" ] ; then
|
|
echoerr " [W] No target directory provided. Using home of current user"
|
|
buTarget="$HOME"
|
|
elif [ ! -e "$2" ]; then
|
|
endReturn -o 1 -f "$2 does not exist"
|
|
fi
|
|
|
|
echo " [I] Dumping database $dbName to $buTarget"
|
|
exep "mysqldump --single-transaction $dbName > \"$(realpath $buTarget)/${dbName}_backup_$(date +%Y%m%d-%H%M%S).sql\""
|
|
endReturn -o $? "Error creating $dbName backup"
|
|
}
|
|
|
|
step_52_info() { echo "Restore a mysql database"; }
|
|
step_52_alias() { ALIAS="restore"; }
|
|
step_52() {
|
|
echo "Restore with:"
|
|
echo " mysql -e \"DROP DATABASE nextcloud_db\""
|
|
echo " mysql -e \"CREATE DATABASE nextcloud_db\""
|
|
echo " mysql nextcloud_db < NextcloudBackup_DB_20170912.sql"
|
|
}
|
|
|
|
# Read database information dbname/user/pass if empty
|
|
readDatabaseInfos() {
|
|
if [ "$dbName" == "" ] ; then
|
|
read -p "Enter postgres database name: " dbName
|
|
endCheckEmpty dbName "database"
|
|
fi
|
|
if [ "$dbUser" == "" ] ; then
|
|
read -p "Enter postgres user name: " dbUser
|
|
endCheckEmpty dbUser "user name"
|
|
fi
|
|
if [ "$dbPass" == "" ] ; then
|
|
read -s -p "Enter postgres password: " dbPass
|
|
endCheckEmpty postgresPass "password"
|
|
fi
|
|
echo
|
|
}
|
|
|
|
VERSION_SEQREV=14
|
|
. /usr/local/bin/sequencer.sh
|