isp-tools

Unnamed repository; edit this file 'description' to name the repository.
Log | Files | Refs

commit dbb809194d5b2c44fc9d366eb89e587bd60972a6
parent 41d15bb208d1d2a0687372c1f8679872316c7325
Author: Jan <devnull@localhost>
Date:   Wed, 16 Mar 2016 21:02:58 +0100

Update-Skript für Datenbank-Konfiguration hinzugefuegt.

Diffstat:
update_database | 122+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 122 insertions(+), 0 deletions(-)

diff --git a/update_database b/update_database @@ -0,0 +1,122 @@ +#!/bin/bash + +# Erzeugt die Virtual-Host-Konfigurationen fuer den Apache-Webserver. +# + + +# Konfiguration: + +CONFIG=/etc/default/ispconfig + +if [ ! -f $CONFIG ]; then + echo "File $CONFIG not found" + exit 4; +fi + +source $CONFIG + + +if [ ! -d $HTTPD_VHOST_DIR ]; then + echo "Directory $HTTPD_VHOST_DIR not found" + exit 4; +fi + +# Ggf. Mercurial-Repository anlegen. +if [ ! -d $HTTPD_VHOST_DIR/.hg ]; then + hg -R $HTTPD_VHOST_DIR init +fi + + +function sql { + sql=$1 + mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -se "$sql" $MYSQL_DATABASE +} + +function sql_admin { + sql=$1 + #echo "SQL: $sql" + #return + mysql -u root -pss19a -se "$sql" $MYSQL_DATABASE +} + + +sql "select domain,CONCAT('db_',LEFT(REPLACE(domain,'.','_'),60)) FROM domain WHERE active=1"|while read domain dbname; do + + # Den Benutzer zur Domain zu finden. Es ist der (hoffentlich einzige) <Benutzername>@webmaster..., der für die Domain berechtigt ist. + db=`sql_admin "SELECT schema_name from information_schema.schemata where schema_name='$dbname'"` + + #echo "Datenbank für $domain ist $db" + if [ ! "$db" ]; then + echo "Datenbank $dbname für Domain $domain wird angelegt" + sql_admin "CREATE DATABASE $dbname CHARACTER SET utf8 COLLATE utf8_general_ci" + dbpass=`cat /dev/urandom| tr -dc A-Z-a-z-0-9 | head -c12` + dbuser=u_`echo $dbname|md5sum|head -c6` + + #echo "# Auto-generated by update_database - DO NOT CHANGE" > /etc/apache2/sites-mysql/$domain.conf + #echo "php_value mysql.default_user $dbuser" >> /etc/apache2/sites-mysql/$domain.conf + #echo "php_value mysql.default_password $dbpass" >> /etc/apache2/sites-mysql/$domain.conf + #echo "php_value mysql.default_host localhost" >> /etc/apache2/sites-mysql/$domain.conf + + echo "# Auto-generated by update_database - DO NOT CHANGE" > /etc/apache2/sites-mysql-passwd/$domain.passwd + echo "mysql_user=$dbuser" >> /etc/apache2/sites-mysql-passwd/$domain.passwd + echo "mysql_password=$dbpass" >> /etc/apache2/sites-mysql-passwd/$domain.passwd + chmod 600 /etc/apache2/sites-mysql-passwd/$domain.passwd + + + user=`sql "SELECT substring_index(username,'@',1) as user FROM domain_admins where domain='$domain' and username like '%@webmaster%'"` + #echo "User: $user ==> Domain: $domain" + + # Zuerst dem Eigentümer der Domain alle Rechte für die Datenbank geben. + if [ "$user" ]; then + echo "Berechtige $user für Datenbank" + sql_admin "GRANT ALL PRIVILEGES ON $dbname.* TO '$user'@'localhost'" + fi + + + + + + echo "Lege Benutzer an: $dbuser" + sql_admin "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass'" + sql_admin "GRANT ALL PRIVILEGES ON $dbname.* TO '$dbuser'@'localhost'" + #echo "u_$name:$pass" >> /home/$user/passwd + #pass=`cat /dev/urandom| tr -dc _A-Z-a-z-0-9 | head -c${1:-12}` + #sql_admin "CREATE USER 'r_$name'@'localhost' IDENTIFIED BY '$pass'" + #sql_admin "GRANT SELECT ON db_$name.* TO 'r_$name'@'localhost'" + #echo "r_$name:$pass" >> /home/$user/passwd + echo "Lade Rechte neu" + sql_admin "FLUSH PRIVILEGES" + fi +done + + +# Cleanup +sql_admin "SELECT schema_name from information_schema.schemata where schema_name like 'db_%'"|while read dbname; do + + #echo "Prüfe $dbname auf Notwendigkeit" + # Existiert die Domain noch in der Verwaltung? + exist=`sql "SELECT domain FROM domain where CONCAT('db_',LEFT(REPLACE(domain,'.','_'),60))='$dbname'"` + #exist= + + if [ ! "$exist" ]; then + + echo "" + echo "Datenbank $dbname wird entfernt, da diese in der Verwaltung nicht mehr existiert" + + sql_admin "select distinct grantee as dbuser FROM information_schema.schema_privileges WHERE grantee regexp '^.?[ur]_.+\$' AND table_schema='$dbname'"|while read dbuser; do + + echo "Lösche $dbuser" + sql_admin "DROP USER $dbuser" + done + + echo "Lösche Datenbank $dbname" + sql_admin "DROP DATABASE $dbname" + + + sql_admin "FLUSH PRIVILEGES" + + fi +done + + +