update_database (4202B)
1 #!/bin/bash 2 3 # Erzeugt die Virtual-Host-Konfigurationen fuer den Apache-Webserver. 4 # 5 6 7 # Konfiguration: 8 9 CONFIG=/etc/default/ispconfig 10 11 if [ ! -f $CONFIG ]; then 12 echo "File $CONFIG not found" 13 exit 4; 14 fi 15 16 source $CONFIG 17 18 19 if [ ! -d $HTTPD_VHOST_DIR ]; then 20 echo "Directory $HTTPD_VHOST_DIR not found" 21 exit 4; 22 fi 23 24 # Ggf. Mercurial-Repository anlegen. 25 if [ ! -d $HTTPD_VHOST_DIR/.hg ]; then 26 hg -R $HTTPD_VHOST_DIR init 27 fi 28 29 30 function sql { 31 sql=$1 32 mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -se "$sql" $MYSQL_DATABASE 33 } 34 35 function sql_admin { 36 sql=$1 37 #echo "SQL: $sql" 38 #return 39 mysql -u $MYSQL_ADMIN_USER -p$MYSQL_ADMIN_PASSWORD -se "$sql" $MYSQL_DATABASE 40 } 41 42 43 sql "select domain,CONCAT('db_',LEFT(REPLACE(domain,'.','_'),60)) FROM domain WHERE active=1 AND x_db=1"|while read domain dbname; do 44 45 # Den Benutzer zur Domain zu finden. Es ist der (hoffentlich einzige) <Benutzername>@webmaster..., der für die Domain berechtigt ist. 46 db=`sql_admin "SELECT schema_name from information_schema.schemata where schema_name='$dbname'"` 47 48 #echo "Datenbank für $domain ist $db" 49 if [ ! "$db" ]; then 50 echo "Datenbank $dbname für Domain $domain wird angelegt" 51 sql_admin "CREATE DATABASE $dbname CHARACTER SET utf8 COLLATE utf8_general_ci" 52 dbpass=`cat /dev/urandom| tr -dc A-Z-a-z-0-9 | head -c12` 53 dbuser=u_`echo $dbname|md5sum|head -c6` 54 55 #echo "# Auto-generated by update_database - DO NOT CHANGE" > /etc/apache2/sites-mysql/$domain.conf 56 #echo "php_value mysql.default_user $dbuser" >> /etc/apache2/sites-mysql/$domain.conf 57 #echo "php_value mysql.default_password $dbpass" >> /etc/apache2/sites-mysql/$domain.conf 58 #echo "php_value mysql.default_host localhost" >> /etc/apache2/sites-mysql/$domain.conf 59 60 echo "# Auto-generated by update_database - DO NOT CHANGE" > /etc/apache2/sites-mysql-passwd/$domain.passwd 61 echo "mysql_user=$dbuser" >> /etc/apache2/sites-mysql-passwd/$domain.passwd 62 echo "mysql_password=$dbpass" >> /etc/apache2/sites-mysql-passwd/$domain.passwd 63 chmod 600 /etc/apache2/sites-mysql-passwd/$domain.passwd 64 65 66 user=`sql "SELECT substring_index(username,'@',1) as user FROM domain_admins where domain='$domain' and username like '%@webmaster%'"` 67 #echo "User: $user ==> Domain: $domain" 68 69 # Zuerst dem Eigentümer der Domain alle Rechte für die Datenbank geben. 70 if [ "$user" ]; then 71 echo "Berechtige $user für Datenbank" 72 sql_admin "GRANT ALL PRIVILEGES ON $dbname.* TO '$user'@'localhost'" 73 fi 74 75 76 77 78 79 echo "Lege Benutzer an: $dbuser" 80 sql_admin "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass'" 81 sql_admin "GRANT ALL PRIVILEGES ON $dbname.* TO '$dbuser'@'localhost'" 82 #echo "u_$name:$pass" >> /home/$user/passwd 83 #pass=`cat /dev/urandom| tr -dc _A-Z-a-z-0-9 | head -c${1:-12}` 84 #sql_admin "CREATE USER 'r_$name'@'localhost' IDENTIFIED BY '$pass'" 85 #sql_admin "GRANT SELECT ON db_$name.* TO 'r_$name'@'localhost'" 86 #echo "r_$name:$pass" >> /home/$user/passwd 87 echo "Lade Rechte neu" 88 sql_admin "FLUSH PRIVILEGES" 89 fi 90 done 91 92 93 # Cleanup 94 sql_admin "SELECT schema_name from information_schema.schemata where schema_name like 'db_%'"|while read dbname; do 95 96 #echo "Prüfe $dbname auf Notwendigkeit" 97 # Existiert die Domain noch in der Verwaltung? 98 exist=`sql "SELECT domain FROM domain where CONCAT('db_',LEFT(REPLACE(domain,'.','_'),60))='$dbname' AND x_db=1"` 99 #exist= 100 101 if [ ! "$exist" ]; then 102 103 echo "" 104 echo "Datenbank $dbname wird entfernt, da diese in der Verwaltung nicht mehr existiert" 105 106 tablecount=`sql_admin "SELECT count(*) from information_schema.tables where table_schema='$dbname'"` 107 if [ $tablecount -gt 0 ]; then 108 echo "Datenbank $dbname ist nicht leer und wird nicht geloescht!" 109 else 110 echo "Datenbank $dbname ist leer und wird gelöscht." 111 112 sql_admin "select distinct grantee as dbuser FROM information_schema.schema_privileges WHERE grantee regexp '^.?[ur]_.+\$' AND table_schema='$dbname'"|while read dbuser; do 113 114 echo "Lösche $dbuser" 115 sql_admin "DROP USER $dbuser" 116 done 117 118 echo "Lösche Datenbank $dbname" 119 sql_admin "DROP DATABASE $dbname" 120 121 122 sql_admin "FLUSH PRIVILEGES" 123 fi 124 fi 125 done 126 127 128