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?
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
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./')
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.
Thank you for the detailed explanation and adding this to the next beta!
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?
Log in to post a comment.