Create a Script to iterate through MySQL databases

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

Tags:
  1. tmpinsnty

    tmpinsnty Member HowtoForge Supporter

    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
     
  2. tmpinsnty

    tmpinsnty Member HowtoForge Supporter

    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
     
  3. Taleman

    Taleman Active 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 HowtoForge Supporter

    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
     
  5. Taleman

    Taleman Active 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 HowtoForge Supporter

    Thanks for all your help
     
    Last edited: Sep 6, 2018

Share This Page