Skip to content

Instantly share code, notes, and snippets.

@iegik
Last active January 27, 2021 07:33
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save iegik/1268222 to your computer and use it in GitHub Desktop.
Scripts for manage SQL files - useful for VCS. NOTE: Use mysql_create.sh to create working set of sql files before
#!/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
#!/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
#!/bin/bash
mysqldump --compact -tcnuroot $1 | sed 's$),($),\n($g;s$\x60\s($\x60\n($g' > sql/$1-data-`date +%s`.sql
#!/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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment