Hi Rory, Humbled, impressed and very thankful.
Regards Rudolf Von: [email protected] [mailto:[email protected]] Im Auftrag von Rory Gesendet: Dienstag, 30. August 2011 13:45 An: User questions and discussions about OTRS. Betreff: Re: [otrs] Otrs with lighttpd + fastcgi Hi Rudolf, Here's the quick answer. Copy the text of the script into a text file and save it as SQLOptimize.sh (or whatever you want to call it). Change the permissions to make it executable, something like 'chmod 755 ./SQLOptimize.sh" Execute the script by typing ./SQLOptimize.sh at the command prompt. (note: the ./ specifies that the file is in the same directory as you are working in). You will be prompted for the username and then the password. For the mysql part, the original line was this; TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 | grep -v Tables_in_) I changed this to; TABLES=$(echo "SHOW TABLES;" | mysql -D$db -u$1 --password=$2 | grep -v Tables_in_) By removing the "USE $db" from the echo command and using the -D option in the mysql client it should now be running one less command on the database for every iteration of the for-loop. This is assuming the -D option is not using the USE $db command anyway. ################ ################ Here's the really really long answer to your questions which hopefully will help you better understand shell scripting in general. Forgive me for going over basic stuff that you probably already know. The original script has the following line; TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 | grep -v Tables_in_) Breaking this apart we get the following; A variable is created called 'TABLES' and is assigned the value from the result of the commands to the right of the equals. The entire right hand side of the line is enclose by $( ). This groups everything together so that the result passed to the variable is whatever the contents between the brackets evaluates to. So the following line is executed by the shell; echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 | grep -v Tables_in_ Regarding the pipes, (the | character), this takes the output of the command on the left of the pipe and uses it as the input to the command on the right. The first part of the line; echo "USE $db; SHOW TABLES;" outputs the text between the quotations but substitues the content of the variable 'db' (variables are referenced using a '$'). Assuming the variable 'db' contains the string 'otrs' you would get the following output; USE otrs; SHOW TABLES; The output from the above text is 2 mysql commands, seperated by semi-colons. The first tells mysql to focus on a particular database and the second tells it to list all the tables in the database it is focused on. So the output of the echo command (the 2 mysql commands) is piped into the next command as input. The next part which is; mysql -u$1 --password=$2 Starts the mysql client (note: it doesn't use the absolute for mysql so it has to be in the PATH for this command to work). It uses the username which is stored in the variable $1 and the password stored in the variable $2. These numbered variable are special because they are gotten form the command used to initially start the script on the command line. If you're script was called SQLOptimize.sh and you used the following from the command line (I'm using $> to represent the shell prompt, you don't enter); $> ./SQLOptimize.sh dbuser dbpass dbname dbport blah The following variables would be available to your script $1 == dbuser $2 == dbpass $3 == dbname $4 == dbport $5 == blah In the script only $1 and $2 are used but you can see how you can get much more information from the user running the script just by separating the script parameters by a space. Back to the command at hand, the mysql client has been started and logged in using the username and password provided by the user. It then takes the input which has been piped through from the echo command, "USE otrs; SHOW TABLES;", and runs this in the mysql client. Note that this is 2 commands. Be cause this is in a loop within the script it must be run at every iteration of the loop. By changing the mysql command to include the -D option; mysql -D$db -u$1 --password=$2 The mysql client logs straight into the database specified in the $db variable. This means we don't need the "USE $db" command in the echo command from earlier which means that we have reduced them number of commands run by the mysql client in the loop by half. This should reduce the amount of resources used during the script. I also did the same thing in the nested for-loop as it also contains a call to the mysql client and had a USE $db entry. In my previous mail I had mentioned this might not make any difference because logging into the mysql client with the -D option may just run the USE $db command anyway. The output from the mysql portion of the line is as follows; Tables_in_otrs article article_attachment article_flag article_plain article_search ..<SNIP>.. web_upload_cache xml_storage This is piped into the next part of the command as input; grep -v Tables_in_ Grep is a pattern matching tool. You can use it to search line by line to find a string in a file or in this case a string in the output of another command. The '-v' option tells grep to invert the results. So if the string *is* found on a line then *do not* return the line and if the string *is not* found on the line then *do* return the line. So in the grep command from the script it will return every line that does not contain the string Tables_in_ So the output will be; article article_attachment article_flag article_plain article_search ..<SNIP>.. web_upload_cache xml_storage This results in only the list of tables being returned and no extra lines that are not tables. ######## For your question about how to call the script there are a few different elements to this to understand what's happening. Firstly, I'm assuming you're doing everything from a shell prompt/command line and not through a GUI. The script is a shell script i.e. you could write it out in the Linux shell and it'd work. It's just put in a script for convenience. A shell script will normally have the extension .sh, i.e. SQLOptimize.sh To make the script below usable you create a file using a regular editor such as 'vim' or 'nano', paste in the code and save with an appropriate name and extension, SQLOptimize.sh (you don't *need* the .sh extension, it'll work without it, but it helps to have it for when you are looking at a list of files) Before you can run the script you need to make it executable. This is a file permission and in Linux these can be edited using the command 'chmod'. It is used in the following format; chmod <options> <permission> <filename> The <options> are things like -R for recurrsive. The <filename> can use wildcards i.e. *.sh The <permission> is something that needs a bit more detail. Have a look at this webpage which gives some detail on how Unix permissions are written and how to set them using the chmod command; http://www.acm.uiuc.edu/webmonkeys/html_workshop/unix.html When calling a script often you won't need to do anything more than just enter the filename. So just entering SQLOptimize.sh at the shell prompt might work. This would work because there is an entry in you PATH for . (just a dot on its own). **** Aside: This is actually a security risk so hopefully it is not in your PATH. Consider if a malicious site downloaded a file called ifconfig to a folder on your machine. If your working directory was the same as the location the file was downloaded you would run the malicious ifconfig instead of the real one which would allow it to do whatever it likes as your user. Especially dangerous if you're logged in as root e.g. it could run "rm -rf /" deleting everything. **** In order to be specific about the file you wish to run you can use the relative or absolute path. The relative path uses your current working directory as the starting point to find the file you want to run. The absolute path is the full location of the file you want to run from the root directory. Your current working directory is specified by a dot on its own. So if you entered whats between the commas here 'cd .' you would be telling the system to change directory to your current working directory. If you entered 'cd ./scripts' then you're telling the system to change to the scripts directory which is a sub directory of your current working directory. Lets say your script is located at /home/user1/scripts/SQLOptimize.sh And your current working directory is /home/user1/scripts To run the script using a relative path you would use ./SQLOptimize.sh To run the script using the absolute path you would use /home/user1/scripts/SQLOptimize.sh If you run the script using the absolute path then it doesn't matter what your current working directory is. Because of this it is best to use absolute addressing when writing a script that references any outside files. ############################# #!/bin/bash echo "Enter DB User: " read user echo "Enter DB Password: " read -s pass if [ -z "${user}" ]; then echo "Username is blank. Exiting script" exit elif [ -z "${pass}" ]; then echo "Password is blank. Exiting script" exit fi for db in $(echo "SHOW DATABASES;" | mysql -u$user --password=$pass | grep -v -e "Database" -e "information_schema") do echo "Switching to database $db" TABLES=$(echo "SHOW TABLES;" | mysql -D$db -u$user --password=$pass | grep -v Tables_in_) for table in $TABLES do echo -n " * Optimizing table $table ... " result=$(echo "OPTIMIZE TABLE $table" | mysql -D$db -u$user --password=$pass 2>&1) if [[ $result == *ERROR* ]] then echo "FAILED" echo ".... $result" else echo "Success" fi done done ############################# Sorry for the mammoth mail, I just thought I'd write all that out because it covers some fundamentals when working with Unix based systems and you did say you were just dabbling so I thought it might help. Rory On 29 August 2011 14:29, Rudolf Bargholz <[email protected]<mailto:[email protected]>> wrote: Hi Rory, For those of us that are not so well versed on Linux, could you perhaps post an example how you call your script, with the appropriate parameters. This makes it easier for us Windows users (me) dabbling in Linux to actually get your script working. The one sentence I do not understand is "I added the database to the mysql command where appropriate to reduce the number of commands run once the mysql client was started as an optimization". An example would probably help resolve my questions. Regards Rudolf
--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
