public
Last active

Scripts for manage SQL files - useful for VCS. NOTE: Use mysql_create.sh to create working set of sql files before

  • Download Gist
mysql_alter.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12 13 14
#!/bin/bash
# Alter selected table by differance between shemas
shema1=$1
shema2=$2
echo "" > sql/$shema1"-alter.sql"
 
for table in $(echo "show tables" | mysql -uroot $shema1 | tail -n +2 )
do
diff sql/$shema1/$table"-create.sql" sql/$shema2/$table"-create.sql" | grep -e '^> \W' | sed 's/^>/ALTER TABLE `'$table'` ADD /g;s/\s*,$/;/g' > sql/$shema1/$table"-alter-add.sql"
cat sql/$shema1/$table"-alter-add.sql" >> sql/$shema1"-alter.sql"
 
diff sql/$shema1/$table"-create.sql" sql/$shema2/$table"-create.sql" | grep -e '^> \W' | sed 's/^>/ALTER TABLE `'$table'` MODIFY /g;s/\s*,$/;/g' > sql/$shema1/$table"-alter-modify.sql"
cat sql/$shema1/$table"-alter-modify.sql" >> sql/$shema1"-alter.sql"
done
mysql_create.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12
#!/bin/bash
shema=$1;
mkdir -p sql/$shema
echo "" > sql/$shema"-create.sql"
# echo "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '$schema' ORDER BY column_name;" | mysql -uroot
for table in $(echo "show tables" | mysql -uroot $shema | tail -n +2 )
do
# Sort 'show create table'
echo "show create table $table;" | mysql -uroot $shema | sed -e 's/\\n/\n/mg;s/.*CREATE TABLE/CREATE TABLE IF NOT EXISTS/gm' | tail -n +2 > sql/$shema/$table"-create.sql"
cat sql/$shema/$table"-create.sql" | head -n -1 | sort -nr >> sql/$shema"-create.sql"
cat sql/$shema/$table"-create.sql" | tail -n -1 >> sql/$shema"-create.sql"
done
mysql_data.sh
Shell
1 2
#!/bin/bash
mysqldump --compact -tcnuroot $1 | sed 's$),($),\n($g;s$\x60\s($\x60\n($g' > sql/$1-data-`date +%s`.sql
mysql_diff_tables.sh
Shell
1 2 3 4 5 6 7 8
#!/bin/bash
t=$1;
d1=$2;
d2=$3;
echo "show create table $t;" | mysql -uroot $d1 | sed -e 's/\\n/\n/mg' > a1
echo "show create table $t;" | mysql -uroot $d2 | sed -e 's/\\n/\n/mg' > a2
meld a1 a2;
rm -f a1 a2;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.