Create a Script to iterate through MySQL databases

Discussion in 'Programming/Scripts' started by tmpinsnty, Aug 31, 2018.

Tags:
  1. tmpinsnty

    tmpinsnty Member

    Hello all for the record I have the Perfect Server Debian 8 LAMP version with MySQL 5.6.39
    In my MySql server I have about 150 databases that start with p_. These are databases for cloned sites.
    Is it possible to create a bash script that I can run when I need to that will:
    1) log into mysql as root
    2) loop through the "p_" databases
    3) run some sql queries (which will include a join with the main database)
    4) close the selection
     
    PavelPavlov likes this.
  2. tmpinsnty

    tmpinsnty Member

    This is what I have so far. I want to know that it should work and if not what should I change.

    # list of all databases
    all_dbs="$(mysql -u $USER -p$PASS -Bse 'SELECT schema_name FROM information_schema.schemata
    WHERE schema_name LIKE 'p\_%')"

    for db in $all_dbs
    do
    mysql -u$USER -p$PASS $db -sN -e "UPDATE products pt JOIN mymaindb.products st ON
    st.products_id=ft.products_id
    SET pt.products_price_w=st.products_price;"
    done

    markup="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_MARKUP\';')"
    rounding="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_ROUNDING\';')"

    if $rounding=1
    then
    mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=TRUNCATE((products_price_w *($markup + 1)), 2);)"
    elif $rounding=2
    then
    mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0)) - 0.01)+1;)"
    else
    mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0))+1; )"
    fi

    Will the $markup variable work or do I need to do it like PHP?
    I know the actually SQL statements work like they should
     
    PavelPavlov likes this.
  3. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    It is not possible to make sence of your script when line breaks are messed up. Put the script in code tags to preserve the line breaks.
    You can test your script by putting the commads in echo " " statements. That way they are not executed, just written on screen and you can see what would happen. And put
    Code:
    #!/bin/bash
    as first line to make it a bash script.
     
  4. tmpinsnty

    tmpinsnty Member

    Code:
    #!bin/bash
    #Script to update peturncatalog pricing all at once
    USER="user"
    PASS="pass"
    
    # list of all databases
    all_dbs="$(mysql -u $USER -p$PASS -Bse 'SELECT schema_name FROM information_schema.schemata
    WHERE schema_name LIKE 'p\_%')"
    
    for db in $all_dbs
         do
             mysql -u$USER -p$PASS $db -sN -e "UPDATE products pt JOIN foreverpets.products st ON
                                                      st.products_id=ft.products_id
                                                      SET pt.products_price_w=st.products_price;"
         done
    
    markup="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_MARKUP\';')"
    rounding="$(mysql -u $USER -p$PASS -Bse 'SELECT configuration_value FROM configuration WHERE configuration_key=\'STORE_ROUNDING\';')"
    
    if $rounding=1
    then
         mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=TRUNCATE((products_price_w *($markup + 1)), 2);)"
    elif $rounding=2
    then
         mysql -u$USER -p$PASS $db -sN -e "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0)) - 0.01)+1;)"
    else
         mysql -u$USER -p$PASS $db -sN -e  "(UPDATE products SET products_price=((TRUNCATE((products_price_w *($markup + 1)), 0))+1;    )"
    fi
     
    PavelPavlov likes this.
  5. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    If you are testint the script, I would put
    all_dbs = "onedb seconddb"
    and take a backup of those two databases before testing. If you have two databases that can be broken, even better. But this way you can test the script logic and only have to restore two databases if something goes wrong. When test runs OK you can put back the old all_dbs line.

    I propose adding these three lines at the beginning:
    Code:
    #!bin/bash
    #Script to update peturncatalog pricing all at once
    set -o nounset
    set -o errexit
    set -x
    
    USER="user"
    PASS="pass"
    
    See for example https://www.davidpashley.com/articles/writing-robust-shell-scripts/ for their meaning.
    The set -x makes every command echo on the standard output, so you can see what the script is doing. When you have finished testing comment out the set -x -line.
    I trust you have checked those mysql commands on the command line to see they return what you expect them to return?
     
    ahrasis likes this.
  6. tmpinsnty

    tmpinsnty Member

    Thanks for all your help
     
    Last edited: Sep 6, 2018
  7. Diego M. Rodrigues

    Diego M. Rodrigues New Member

    You can use Python to make your script.
     
  8. PavelPavlov

    PavelPavlov New Member

    And the better this python is php ...
    In this case?
    Another question, advise a text editor, which is better Sublime Text or Atom Edit
     
  9. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    If you suggest Python or PHP are better tools for writing this script, you can prove your point by supplying a version in your preferred programming language. I you just say the script can be written in some other language, I can say it can be written in Cobol.
    I do not want to take part in text editor wars. I use Emacs, and like it. https://en.wikipedia.org/wiki/Editor_war
    I suggest to everyone to use the editor that you know how to use.
    If there really is a need to ask question which is better. you have to define the criteria by which to evaluate the "betterness".
     

Share This Page