Help save net neutrality! Learn more.

Table Exclusion via wildcard

  • Anonymous - 2011-09-13


    I'm looking for a way to exclude tables that are generated automatically by my application. I have the following in my config file:

    # List of tables to exclude, in the form db_name.table_name

    And a small sampling of the databases I want excluded:

    | Live_22_12_Sep_2011_167772170_1 |
    | Live_22_12_Sep_2011_167772171_1 |
    | Live_22_12_Sep_2011_167772172_1 |
    | Live_22_12_Sep_2011_167772173_1 |
    | Live_22_12_Sep_2011_168361985_1 |
    | Live_22_12_Sep_2011_169082881_1 |
    | Live_23_06_Sep_2011_167772170_1 |
    | Live_23_06_Sep_2011_167772171_1 |
    | Live_23_06_Sep_2011_167772172_1 |

    Is this possible to accomplish with automysql backup?

  • PittaGurneyi

    PittaGurneyi - 2011-09-13


    I just took a look at mysqldump, which gets the entries from CONFIG_table_exclude as parameters and it doesn't take wildcards. However there is an easy solution for you in case you are using bash >=4.x and mysql >=5.x

    Execute the following two commands on the bash shell (i.e. terminal/Konsole in KDE, etc.):

    mapfile -t CONFIG_table_exclude < <(mysql -batch -silent -user="YOUR_USERNAME" -password='YOUR_PASSWORD' -e "select table_name from information_schema.tables where table_schema='Foo' and table_name like 'Live_%';")
    declare -p CONFIG_table_exclude

    You have to copy the output from the last command and put it exactly AS IS into the config file instead of your line CONFIG_table_exclude=(Foo.Live_*)!
    By the way, ALWAYS put your table names, database names, etc. in single-quotes!!!!!!!!!!!!!!!

    The way you have done it, the array (like a collection of things) would have contained all files - if there were any - in the current working directory of the script, that means most likely where automysqlbackup is located, that begin with "Foo.Live_" because the * is a special character (a glob in bash) that means anything no matter how long.

    Should you then need to manually add a few more table_excludes, the simplest way to achieve your goal is to do the following:
    CONFIG_table_exclude+=('Foo.something' 'Foo.Somethingelse' '…')
    If I remember correctly bash 3.2 is necessary for the += operator to work with arrays…
    Changing the output of declare -p CONFIG_table_exclude directly is very problematic for many reasons - just DON'T DO IT!
    Remember to put manual changes always after the initial declare -ax CONFIG_table_exclude line generated by bash.

    This way - as far as I can overview the problem now - even this is possible with a bit of manual work!

    I hope you like AutoMySQLBackup

  • PittaGurneyi

    PittaGurneyi - 2011-09-13

    I forgot, that the mysql command only outputs table names and you need the database name as well … therefore we have to adjust this … sed -e 's/^/Foo./' adds "Foo." to the beginning of each line

    mapfile -t CONFIG_table_exclude < <(mysql -batch -silent -user="YOUR_USERNAME" -password='YOUR_PASSWORD' -e "select table_name from information_schema.tables where table_schema='Foo' and table_name like 'Live_%';" | sed -e 's/^/Foo./')

  • PittaGurneyi

    PittaGurneyi - 2011-09-14

    I added this functionality in the form you suggested, you just have to use single quotes, i.e.
    will work in the next beta version.

  • Anonymous - 2011-09-14

    Hi pittagurneyi,

    Thank you for the detailed explanation and adding this to the next beta!


  • Simon Wilkinson

    Simon Wilkinson - 2014-01-02

    I know you added this to beta, has it made it's way to stable yet? I assume so.

    If not, which beta was it added to?

    Also is there similar functionality for entire databases?



    Last edit: Simon Wilkinson 2014-01-02

Log in to post a comment.