generating federated tables dynamically

Generating federated tables dynamically.

1. writing the shell scripts fed1.sh

vi fed1.sh

#!/bin/sh
USER=kiran
DATABASE_NAME=datamodel
password1=kiran@123
# The default connection string to use
CONNECTION="mysql://username:secret@10.10.10.10:3306/test2"
# Get the list of table names
TABLE_LIST=`mysql -u$USER -p$password1 $DATABASE_NAME -e 'SHOW TABLES \G' | sed -n "/^Tables_in_${DATABASE_NAME}:/s/^.*: //p"`
#echo $TABLE_LIST
# For each table in the list
for TABLE in $TABLE_LIST; do
        # Get the SHOW CREATE TABLE
        mysqldump -u$USER -p$password1 --no-data $DATABASE_NAME $TABLE | sed "s!^) ENGINE=.*!) ENGINE=FEDERATED CONNECTION='${CONNECTION}/${TABLE}';!"
done

2) step run the fed.sh file

sh fed1.sh > fed-tables.sql

3) remove the unwanted lines

 sed  '/*!40/d' fed-tables.sql > fed-tables1.sql

4) execute the sql file

mysql -u kiran -pkiran databasename  < fed-tables1.sql