Hi everyone.. i recently started working in shell scripting.. what i am looking for is in mysql we have an option to take mysqldump of the existing database number 1.. same way i will take mysqldump of another database 2. now i want to write a script which compares the two mysqldump sql files and write out changes to be made in mysql query formats.. :wall:
any help is most appreciated..
reagards,
vivek
What have you tried so far? Please post the script you currently have.
i havent made any much progress so far... the project which i am involved in has huge database.. we already have a solution for this issue.. we take the mysql dump of latest database and use it as input for reverse engineering mysql script in a software called " mysql testbench " which when done will check this sqldump with existing database in server and produce the delta... that is changes to be made in existing old database.. what i want to do is to avoid using mysql testbench for this function and develop shell script for this.. basically i am java developer. i was introduced to this recently.. i know few of the basics.. i know the code will be huge since it has to compare table names in for loop between two mysql dumps and if there are changes go to that particular function etc etc... anyways.
may be go through some of the tools listed here
thanks for the info i went thorugh the site which you have mentioned. but they have specified only softwares to find differences. i already have a software called mysql testbench which finds me the difference in mysql dumps.. let me explain the problem i am dealing with consider there are two myssql dumps.. that is .sql(which can be compared to a txt file). out of these two .sql files one is dump of latest database and one is old database. for eg here is the format of the files
Latest mysql dump in file1.sql
-- MySQL dump 10.13 Distrib 5.1.44, for unknown-linux-gnu (x86_64)
--
-- Host: localhost Database: release_c
-- ------------------------------------------------------
-- Server version 5.1.44-enterprise-commercial-pro-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `Blahblahblah`
--
use dabbDB;
DROP TABLE IF EXISTS `Blahblahblah`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Blahblahblah` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`action` enum('Change','Delete','Add','Locked','Unlocked') COLLATE utf8_unicode_ci DEFAULT 'Change',
`date` datetime DEFAULT NULL,
`reserved1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`reserved2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`zoneId` int(11) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
.
.
.
.
etc etc
--------------------------------------------------------------------------
OLD mysql dump in file2.sql
-- MySQL dump 10.13 Distrib 5.1.44, for unknown-linux-gnu (x86_64)
--
-- Host: localhost Database: release_c
-- ------------------------------------------------------
-- Server version 5.1.44-enterprise-commercial-pro-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `Blahblahblah`
--
use dabbDB;
DROP TABLE IF EXISTS `Blahblahblah`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Blahblahblah` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`action` enum('Change','Delete','Add','Locked','Unlocked') COLLATE utf8_unicode_ci DEFAULT 'Change',
`date` datetime DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
.
.
.
.etc etc
=====================================
obeserve that in old mysql dump file column names such as reserved1 reserved2 are missing.. what i want to do is found out these differences and list them out and if its missing go to a function called alter_query() where it inserts this one more coulmn to the database or may be write the query to a third file which we could use to run to manually.... or if one column name is missing in new database dump but present in old then this column must be deleted hence it goes to delete_query()function.. i know it will involve lot of loops and string comparison .. i dont know how to kick start.. i just somehow comeup with freading a file and printing them.. so have to start from scratch...
#!/bin/sh
echo enter file name
read fname
exec<$fname
value=0
while read line
do
# value=`expr $value + 1`;
# the abovel ine gives the number of line in the file
echo "$line"
done
# echo "$value";
---------- Post updated 15-11-11 at 12:03 PM ---------- Previous update was 14-11-11 at 12:03 PM ----------
there is a command as
diff filename1.sql filename2.sql > output_diff_file.sql
this above code will just show differences inthe files. i need differences with respect to their tables :wall::wall::wall::wall::wall::wall::wall::wall::wall::wall:
i have made come good progress.. here is the below piece of code. now i am racking my head to find out how to use methods or functions to find the create table strings in a line and to extract the table name to use it ininset column query and also i am worried cause the .sql file has hundreds of tables so how to select identifier and act search in specific... :wall::wall:
#!/bin/sh
#
# Here we are considering testdump1.sql as old mysql dump of database
# and testdump2.sql as latest mysql dump file to be changed to hence
# forth there will be two methods or functions to insert missing
# columns from new mysql dump to database, and also deleting new
# Columns from database which are not present in new mysql dump.
#
#
# Test purpose testdump1.sql and testdump2.sql has one table each with
# few columns names missing.
#
#
value1=0
value2=0
while read line1
do
value1=`expr $value1 + 1`
# the abovel ine gives the number of line in the file
done < testdump1.sql
while read line2
do
value2=`expr $value2 + 1`
# the abovel ine gives the number of line in the file
done < testdump2.sql
echo "value1 : $value1"
echo "value2 : $value2"
counter1=0
counter2=0
#
# this below piece of code is used to find columns not present in new database
# and hence will involve method for deleting that column.
#
while read line1
do
# echo "$line1"
counter2=0
while read line2
do
if [ "$line1" != "$line2" ]
then
counter2=`expr $counter2 + 1`
fi
if [ $counter2 -eq $value2 ]
then
echo "this line not present : $line1"
# here calling the delete method
fi
done < testdump2.sql
done < testdump1.sql
#
# this below piece of code is used to find columns not present in old database
# and hence will involve method for inserting that column.
#
while read line2
do
# echo "$line2"
counter1=0
while read line1
do
if [ "$line1" != "$line2" ]
then
counter1=`expr $counter1 + 1`
fi
if [ $counter1 -eq $value1 ]
then
echo "this line not present : $line2"
# here calling the insert method
fi
done < testdump1.sql
done < testdump2.sql
i have been working on this since a week... continuing on from my previous thread.. whose link is below
http://www.unix.com/shell-programming-scripting/171076-shell-scripting.html#post302573569
i have reached till here... (below is current code) its throwing up some error... dont know what it is..
#!/bin/sh
#
# Here we are considering testdump1.sql as old mysql dump of database
# and testdump2.sql as latest mysql dump file to be changed to hence
# forth there will be two functions namely to insert missing
# columns from new mysql dump to database, and also deleting new
# columns from database which are not present in new mysql dump.
#
#
# Test purpose testdump1.sql and testdump2.sql has one table each with
# few columns names missing.
#
#
value1=0
value2=0
while read line1
do
value1=`expr $value1 + 1`
# the abovel ine gives the number of line in the file
done < testdump1.sql
while read line2
do
value2=`expr $value2 + 1`
# the abovel ine gives the number of line in the file
done < testdump2.sql
echo "value1 : $value1"
echo "value2 : $value2"
counter1=0
counter2=0
len1=0
len2=0
#
#function for inserting missed columns
#
insert_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 ADD $2;
QUIT
EOF
if [ $? -eq 0 ]
then
echo "$2 added to $1 Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
#
#function for deleting the extra column
#
delete_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 drop $2;
QUIT
EOF
if [ $? -eq 0 ]
then
echo "$2 dropped from $1 Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
#
# this below piece of code is used to find columns not present in new database
# and hence will involve method for deleting that column.
#
echo "========printing lines not present in new mysql dump(delete)========="
echo ""
while read line1
do
# echo "$line1"
counter2=0
len1=${#line1}
while read line2
do
len2=${#line2}
char1=${line1:$len1-1:$len1}
char2=${line2:$len2-1:$len2}
if [ "$char1" = "," ]
then
line11=${line1:0:$len1-1}
else
line11=$line1
fi
if [ "$char2" = "," ]
then
line22=${line2:0:$len2-1}
else
line22=$line2
fi
if [ "$line11" != "$line22" ]
then
counter2=`expr $counter2 + 1`
fi
if [ $counter2 -eq $value2 ]
then
echo "this line not present : $line11"
# here calling the delete method
table_name=$( awk -F\` '/CREATE TABLE/{print $2}' testdump2.sql )
# column_name=$
# delete_column $table_name $column_name
fi
done < testdump2.sql
done < testdump1.sql
#
# this below piece of code is used to find columns not present in old database
# and hence will involve method for inserting that column.
#
echo ""
echo "========printing lines not present in old mysql dump(insert)========="
echo ""
while read line2
do
# echo "$line2"
counter1=0
len2=${#line2}
while read line1
do
len1=${#line1}
char1=${line1:$len1-1:$len1}
char2=${line2:$len2-1:$len2}
if [ "$char1" = "," ]
then
line11=${line1:0:$len1-1}
else
line11=$line1
fi
if [ "$char2" = "," ]
then
line22=${line2:0:$len2-1}
else
line22=$line2
fi
if [ "$line11" != "$line22" ]
then
counter1=`expr $counter1 + 1`
fi
if [ $counter1 -eq $value1 ]
then
echo "this line not present : $line22"
# here calling the insert method
table_name=$( awk -F\` '/CREATE TABLE/{print $2}' testdump2.sql )
insert_column $table_name $line22
fi
done < testdump1.sql
done < testdump2.sql
............
insert_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 ADD $2;
QUIT
EOF
.............
.............
delete_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 drop $2;
QUIT
EOF
.............
there must not be spaces in front of the "EOF"
must be..
............
insert_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 ADD $2;
QUIT
EOF
.............
delete_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF
ALTER TABLE $1 drop $2;
QUIT
EOF
.............
regards
ygemici
Just because you get no answer anymore in the old thread, please refrain from opening a new thread with the same issue as this is the same as bumping up and is not allowed by the rules of this forum here.
It's always a good idea to give the actual error message.
Oh okay sorry... will do that from now on... :-/
---------- Post updated at 11:04 AM ---------- Previous update was at 10:48 AM ----------
Output of above file
what is the syntax to invoke mysql statements in shell script dynamically by storing the acted tables and columns in a variable?
---------- Post updated at 11:28 AM ---------- Previous update was at 11:04 AM ----------
i have made correction as
ALTER TABLE $1 ADD COLUMN $2;
but its still showing the same error. i tried
ALTER TABLE '$1' ADD COLUMN '$2';
ALTER TABLE `$1` ADD COLUMN `$2`;
ALTER TABLE "$1" ADD COLUMN "$2";
but none worked 