Here are two suggestions: 1) One option is to use source database to dump data into a MySQL readable format, then import/load the data into MySQL. 2) Another option is to use a language like C++/or Java and open simultaneous connections to each database. One dumping data, format the data then one connection to import the data.
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 3/4/04, 9:11:02 AM, "HACKATHORN," TODD "(SWBT)" <[EMAIL PROTECTED]> wrote regarding RE: Scripting: > I think that will help, I'll give it a try, I have had a lot of trouble > finding good tutorials for bash also. Having been a windows person for so > long it seems very foreign to me. I see how you connect to the local data > base, does anyone know how to import data from another non mySQL database > server? To make two connections and pull data from one to the other? > Thanks, > Todd Hackathorn > -----Original Message----- > From: Rhino [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 04, 2004 8:30 AM > To: HACKATHORN, TODD (SWBT); [EMAIL PROTECTED] > Subject: Re: Scripting > Todd, > I'm also pretty new to MySQL and Linux but I have years of DB2 experience on > various platforms. > I've bash scripts very useful and cron them when I have something that I > want to automate, such as a daily backup of my databases. Here are some > examples of bash scripts that I use with MySQL. > This example, which is in file RI.sql, creates two related tables in the > MySQL 'tmp' database, populates them, and displays the results. NOTE: The > 'Another Mistake' row will not be successfully inserted into the 'emp' table > because it has an invalid foreign key; there is no row for dept 'X99' in the > 'dept' table. > --- > use tmp; > drop table if exists dept; > create table dept( > deptno char(3) not null, > deptname varchar(36) not null, > mgrno char(6), > primary key(deptno) > ) Type=InnoDB; > drop table if exists emp; > create table emp( > empno char(6) not null, > firstnme char(12) not null, > midinit char(1), > lastname char(15) not null, > workdept char(3) not null, > salary dec(9,2) not null, > primary key(empno), > index(workdept), > foreign key(workdept) references dept(deptno) on delete restrict > ) Type=InnoDB; > insert into dept values > ('A00', 'Administration', '000010'), > ('D11', 'Manufacturing', '000020'), > ('E21', 'Education', '000030'); > insert into emp values ('000010', 'Christine', 'I', 'Haas','A00',50000.00); > insert into emp values ('000020', 'George', 'W', 'Bush', 'D11', 30000.00); > insert into emp values ('000030', 'Another', ' ', 'Mistake', 'X99', > 15000.00); > insert into emp values ('000040', 'John', ' ', 'Kerry', 'E21', 35000.00); > select * from dept; > select * from emp; > --- > You can run the preceding script from the mysql prompt by preceding its name > with '\.'. For example: > mysql> \. RI.sql > This script, called backup2.bash, is what I use to back up my databases each > night. It includes a comment showing the crontab entry I use to run it. > NOTE: We are using a Perl package called 'sendEmai'l instead of the > traditional 'sendmail' program. > --- > #!/bin/bash > #This script makes a separate database-level backup of each of the current > MySQL databases and > #deletes backups older than a certain number of days. > #This script is normally invoked via a cron job so that it runs once per day > in the middle of the night. > #The crontab entry looks like this: > #0 3 * * * sh /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out > 2>&1; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t > [EMAIL PROTECTED] -u "Daily Backup Report" > USERID="foo"; #The userid to use for creating the backup > PASSWORD="foopass"; #The password to use for creating the backup > BACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp > (YYYYMMDD-HHMMSS) of the backup > BACKUP_PATH="/home/rhino/MySQL/backup"; #The directory into which the backup > will be written > NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to > keep > echo "** REPORT BEGINS **"; > echo > echo "Program Name:" $0 > report_date=`/bin/date` > echo "Report Date:" $report_date; > echo > #Display the non-secret values used in this run. > echo "Backup Values:"; > echo " Backup timestamp is" $BACKUP_TIMESTAMP; > echo " Backup path is" $BACKUP_PATH; > echo " Number of daily backups to keep =" $NUMBER_OF_DAILY_BACKUPS_TO_KEEP; > #For each database currently in MySQL, take a database-level backup, then > list any backups older than a certain number of day > for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD` > do > echo > echo "Backing up database" $ONE_DBNAME; > /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} > ${ONE_DBNAME} -r ${BACKUP_PATH}"/"${ONE_DBNAME}"."${BACKUP_TI > echo ">> Deleting these old backups for this database..." > /usr/bin/find ${BACKUP_PATH} -mtime > +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old > backups (i > /usr/bin/find ${BACKUP_PATH} -mtime > +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';'; > #delete old > done > echo > echo "** REPORT ENDS **"; > --- > If you want to take a backup manually with this script (and omit the email > being sent to you), you can do this from the mysql prompt via: > mysql> \. /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out > 2>&1 > I think the biggest obstacle you're going to face in writing bash scripts is > the lack of good tutorials on it. There are umpteen bash references that > show you generic syntax and tell you the terminology used by bash but darned > few decent tutorials to show you how to write bash scripts. Some people can > do anything they want with only a reference but I'm one of those people who > need examples to really understand something so I've had real challenges > learning bash since the references are extremely skimpy on concrete > examples. > Good luck! > Rhino > ----- Original Message ----- > From: "HACKATHORN, TODD (SWBT)" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, March 04, 2004 8:57 AM > Subject: Scripting > > Hello, > > I am new to Linux, and mySQL. I am currently a MsSQL Server 2000 and VB > > programmer, but trying to break away from that side. I have my database > set > > up and working great, but I am really struggling on how to perform > scheduled > > updates, and data imports. Can anyone please point me to a good resource > to > > learn how to build a script, that can import data from an ODBC connection, > > and then maybe run multiple queries in the data one at a time. I don't > know > > what language I want to use because I don't know enough about it. Once I > > find a way to do what I need, I will learn that language. I think there > > might be a way to use php as cron'd scripts, but I don't know. Also I > > looked at perl, or just bash in linux. I am used to being able to do > > anything I needed to with DTS packages and stored procedures. I had ask a > > similar question a while back, and still can't get going with this. So > far > > the graphical packages for updates are the only thing I miss from SQL > > Server. > > > > Thanks in advance for any help. > > > > Todd Hackathorn > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
