MySQLDump for federate tables

Problem Description

As you probably already know federate tables have an special usage. They are not less flexible at manipulate, less performance and hard to join, you also rather have problems to make a backup for them, especially if you want to filter columns and such of things. I ran into this handicap too and below I describe how I've jumped across it.

Bases concepts

Probably you must know though concept is needed to understand what will be explained below. MySQL has the option of running queries by Mysql Cli via command line using just '-e', like:

mysql -u{user} -p{pass} {database_name} -e "{query}" > output.txt

This command also has the option to redirect the output into a file, having kind of the same structure for select queries like "INTO OUTFILE", separated by TAB and escaped by ".

Decorating our output

Lets play a bit more with the above command.

1. Skip column names

In my case I didn't need to use the column names to build my "fake" dump file, so you could add to the previous command the following argument to skip them.

--skip-column-names

2. Escaping row values

Due to some column values might contain  special characters which would provoke your dump file breaks, I recommend to escape every column on the SELECT query by the usage of QUOTE method, like:

mysql -u{user} -p{pass} {database_name} -e "SELECT QUOTE(col1), QUOTE(col2)..."

3. Sed Command Line

Output from the previous command will have an structure like the following one:

"value1row1"     "value2row1"     "value3row1"    "value4row1"
"value1row2"     ......

.....

and we likely want to something like this:

INSERT INTO {table_name} VALUES (value1row1, value2row1,value3row1,value4row1), (value1row2, ...) .....

How could we make it without difficulty and trying to achieve the closest way a original mysqldump works? My solution was to use sed command therefore we could parse the original output into a usual mysqldump file.

{ mysql command} | sed 's/\\\\/\\/g' | sed "s/\t/,/g;s/^/(/;s/$/)/;s/\n//g" | sed "s/\(.*\)/\1,/;1iINSERT INTO tmp_import VALUES " | sed '$s/.$/;/g'  > dump.sql

If you want to understand a bit more how to interpret those sed command regex I do it below:

1. sed 's/\\\\/\\/g'
Replace every double escaping which could have been produced by QUOTE function.

2.  sed "s/\t/,/g;s/^/(/;s/$/)/;s/\n//g" 
Replace every TAB into a comma, include parentheses at the beginning and the end of every line and finally add a semicolon at the last position of last line. 

3. sed "s/\(.*\)/\1,/;1iINSERT INTO tmp_import VALUES "
The easiest one, just add at the beginning "INSERT INTO tmp_import VALUES"


I hope that you may take advantage of all this work, at least for me it was the best way to create backup files from federate tables.

Thanks for reading