Re: Scripting Issues
Hi,
Why don't you run the CREATE TABLE query with the IF NOT EXISTS clause
then your script could look like this
CREATE TABLE IF NOT EXISTS table_name ...;
ALTER TABLE table_name;
If the table already exists only the ALTER statement will be executed,
otherwise the ALTER statement will not do anything.
Check the MySQL Reference for more details
http://dev.mysql.com/doc/refman/4.1/en/create-table.html
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Friday 11 November 2005 14:14, Luke wrote:
> Hey all,
>
> I've got a question for you. What would be the best way to set up a set of
> srcipts to create\Update the databse when my app is installed?
>
> The problem is that I obviously can't just drop the database cause if the
> database is already installed then the client would lose all his data. I
> need a way to update the collumns in each table if they change but still
> keep the clients data. Stored Procedures, Views and Triggers are easy cause
> these I can drop then create again but tables are a bit more complex.
>
> I thought of writing something to the effect of :
>
> USE Information_Schema;
>
> IF (SELECT Table_Name FROM Tables WHERE TableName = '' AND
> Table_Schema = '') IS NULL THEN BEGIN
> {Create Table}
> END;
> ELSE
> BEGIN
> {Alter Table}
> END;
> END IF;
>
> Is there not a better way of doing things? We want to try and create\update
> the database through scripts which can then be deleted instead of writing
> an app.
>
> Any help would be greatly appreciated
>
> Thanx,
> Luke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scripting
I too am a Java guy. The nice part of Java is that it's cross platform
and cross database. Using JDBC and pure java JDBC drivers you can move
your applications to different databases on just about any relevant
platform. This will allow you to run your java applications on Windows,
Mac, Linux, Solaris and many others. If you wanted a simple scripting
language you could use the Rhino project from Mozilla and create a
basic java application that launches java script giving you a true
scripting language that can access all the goodness of java.
From what I understand there are many possibilities outside of java
such as perl, ruby, python and others.
I'm partial to Java but more than that I'm a big believer in cross
platform solutions. I don't believe you should ever tell a customer
what to use, they should tell you what they want to use. Which accounts
for my motivation to suggest cross platform scripting/language
solutions.
Joe
On Thursday, March 4, 2004, at 10:25 AM, Victor Medina wrote:
HI!
ODBC is a nice, cheap and easy way. JDBC is a nice option also (i am a
java guy, forgive me! =)
There are a few utilities around that may help, including some gui
managers for mysql, better yet, sitted behind your comfortable windows
desktop =)
A few utilities to try:
sqlyog: www.sqlyog.com
dbtools: www.dbtools.com.br
Best Regards!
On Thu, 2004-03-04 at 11:11, HACKATHORN, TODD (SWBT) wrote:
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', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I',
'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11',
3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', '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 u
Re: Scripting
Sorry, I don't know anything about PHP except that is supposed to be
somewhat Java-like. My main language these days is Java but I don't know PHP
at all.
For what it's worth, I checked my V7.2 DB2 manuals just now to see if PHP is
mentioned in the Application Development Guide. I found chapters on COBOL, C
and C++, Perl, Fortran, Java, and REXX but nothing on PHP.
I also checked the V8.1 DB2 manuals for Windows/Linux platforms and found
very little *except* for this little nugget, which is probably exactly what
you want:
a.. PHP. PHP can be used as a method to access DB2 from web-based
applications. PHP is a server-side, HTML-embedded, cross-platform scripting
language. It supports DB2 access using the Unified-ODBC access method, in
which the user-level PHP communicates to DB2 using ODBC calls. Unlike
standard ODBC, with the Unified-ODBC method, communication is directly to
the DB2 CLI layer, not through the ODBC layer. For more information about
using PHP with DB2, search the DB2 support site:
www.ibm.com/software/data/db2/udb/winos2unix/support
I followed the suggested link and did a search with the search term:
PHP && DB2
There were many hits but the most useful looking one was this:
http://www-106.ibm.com/developerworks/db2/library/techarticle/scott/0614_scott.html
The other good place to ask DB2 questions is the Usenet newsgroup
comp.databases.ibm-db2. The Toronto Lab, which develops DB2 for the Wintel
platforms monitors this newsgroup and often answers questions there.
Good luck!
Rhino
- Original Message -
From: "James Marcinek" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 04, 2004 9:32 AM
Subject: Re: Scripting
> Rhino,
>
> I know this is an off topic; however I see that you have DB2 experience.
> Most of the information I have a question you might be able to answer. Are
> you using PHP with MySQL? If so, have you tried to use PHP with DB2? I'd
> like to know what functions you call to connect or anything that could
> help? I'm new to the MySQL arena as well.
>
> Thanks,
>
> James
>
> > 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', '10'),
> > ('D11', 'Manufacturing', '20'),
> > ('E21', 'Education', '30');
> >
> > insert into emp values ('10', 'Christine', 'I',
> > 'Haas','A00',5.00);
> > insert into emp values ('20', 'George', 'W', 'Bush', 'D11',
3.00);
> > insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> > 15000.00);
> > insert into emp values ('40', '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
>
RE: Scripting
HI!
ODBC is a nice, cheap and easy way. JDBC is a nice option also (i am a
java guy, forgive me! =)
There are a few utilities around that may help, including some gui
managers for mysql, better yet, sitted behind your comfortable windows
desktop =)
A few utilities to try:
sqlyog: www.sqlyog.com
dbtools: www.dbtools.com.br
Best Regards!
On Thu, 2004-03-04 at 11:11, HACKATHORN, TODD (SWBT) wrote:
> 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', '10'),
> ('D11', 'Manufacturing', '20'),
> ('E21', 'Education', '30');
>
> insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00);
> insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
> insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> 15000.00);
> insert into emp values ('40', '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
> (MMDD-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
>
>
RE: Scripting
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', '10'),
> ('D11', 'Manufacturing', '20'),
> ('E21', 'Education', '30');
> insert into emp values ('10', 'Christine', 'I',
'Haas','A00',5.00);
> insert into emp values ('20', 'George', 'W', 'Bush', 'D11',
3.00);
> insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> 15000.00);
> insert into emp values ('40', '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
> (MMDD-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 **";
> ech
Re: Scripting
There's a whole list of all the functions at http://www.php.net
Curtis
On Thu, 4 Mar 2004, James Marcinek wrote:
> Rhino,
>
> I know this is an off topic; however I see that you have DB2 experience.
> Most of the information I have a question you might be able to answer. Are
> you using PHP with MySQL? If so, have you tried to use PHP with DB2? I'd
> like to know what functions you call to connect or anything that could
> help? I'm new to the MySQL arena as well.
>
> Thanks,
>
> James
>
> > 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', '10'),
> > ('D11', 'Manufacturing', '20'),
> > ('E21', 'Education', '30');
> >
> > insert into emp values ('10', 'Christine', 'I',
> > 'Haas','A00',5.00);
> > insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
> > insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> > 15000.00);
> > insert into emp values ('40', '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
> > (MMDD-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
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', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', '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
(MMDD-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_DBN
Re: Scripting
Rhino,
I know this is an off topic; however I see that you have DB2 experience.
Most of the information I have a question you might be able to answer. Are
you using PHP with MySQL? If so, have you tried to use PHP with DB2? I'd
like to know what functions you call to connect or anything that could
help? I'm new to the MySQL arena as well.
Thanks,
James
> 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', '10'),
> ('D11', 'Manufacturing', '20'),
> ('E21', 'Education', '30');
>
> insert into emp values ('10', 'Christine', 'I',
> 'Haas','A00',5.00);
> insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
> insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> 15000.00);
> insert into emp values ('40', '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
> (MMDD-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
> dar
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', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', '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
(MMDD-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 s
Re: Scripting MySQL Commands
At 12:25 -0500 3/2/03, Stephen Tiano wrote: Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me "incorrect database name". Of course. The first non-option argument, if there is one, is taken as the name of the default database. You need a < character before the filename if you want to redirect the input of the command to read from the file: /usr/local/bin/mysql --local-infile -u root -p < filename Well, yes, the script is to create a nonexistent database and then a table called "Temp" in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
I'm fairly new to MySQL as well (although, I do have experience with Oracle). Even though you create a new database doesn't mean that MySQL will automatically start using that database. After all you might want to create 10 or 15 databases all at once (why? I don't know), then start creating tables for the 5th one you created. So your script needs to do something like: create database 'Temp'; use 'Temp'; (You need the quotes [or maybe it's the backquotes] if you actually want a mixed case name.) I'm sure that if I'm off, someone here will correct me. jeff At 12:27 -0500 3/2/03, Stephen Tiano wrote: Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me "incorrect database name". Well, yes, the script is to create a nonexistent database and then a table called "Temp" in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Jeff Shapiro, Colorado Springs, CO, USA At work I *have* to use a Windows machine, at home I *get* to use a Mac. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
Guys, Guys-- I got it! I stopped being a dimwit long enough to realize that the change to "USE Temp;" below was for INSIDE the script. When I made that change and again went to a new shell and typed (actually, I dragged the file from the window in which it sat in the Finder into the Terminal after typing): /usr/local/bin/mysql -u [name] -p I entered my password, was presented with a new prompt and typed: /usr/local/bin/mysql -u [name] -p Again I was asked for my password, which I typed in. At the next prompt I typed "USE Temp". The database changed and at the prompt that followed, I typed "SHOW TABLES". "Test_Table" was there. I did a "SELECT * FROM Test_Table" and the test row of data was indeed there. Thank you all-- Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Scripting MySQL Commands
Hy Steve, > USE DATABASE Temp; try to change this into : USE Temp; and try it again, this should work. HTH Oliver for the filter : sql,query,queries,smallint - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
Paul, Thanks very much for responding. I tried what you suggested, but got: ERROR 1102: Incorrect database name '/Users/stephent/Sites/Temp.sql' I don't get this. What wrong database name? The script is supposed to create the database and a table in it. What an I too dense to see? Thanks again-- Steve Tiano Type the command at the shell prompt, not after you've already invoked mysql. The error message is from mysql itself; it indicates that you typed a shell command to mysql. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
At 8:53 -0500 3/2/03, [EMAIL PROTECTED] wrote: I'm doing all kinds of exercises and tutorials to get up to speed on MySQL on my PowerBook with OS X.2 running. I just tried an exercise that, for the first time, had me creating a table not right in the MySQL monitor, but, rather, via a script that I saved as a textfile--Temp.sql--and then call the script up from the command line in the MySQL monitor. Here's the script: CREATE DATABASE Temp; USE DATABASE Temp; CREATE TABLE Test_Table (Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Test_Name VARCHAR(30), Test_Date DATETIME, Test_Giver VARCHAR(30)); INSERT INTO Test_Table (Test_ID, Test_Name, Test_Date, Test_Giver) VALUES (NULL, 'Test','2000-03-02','Etienne'); The line I used to call the script up is: bin/mysql -p I didn't get the expected result, receiving the following message: ERROR 1064: You have an error in your SQL syntax near 'bin/mysql -p Can anyone see what I've done wrong? Type the command at the shell prompt, not after you've already invoked mysql. The error message is from mysql itself; it indicates that you typed a shell command to mysql. Thank you. Steve Tiano mail2web - Check your email from the web at http://mail2web.com/ . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
Oliver, Thanks for responding. Foolish of me to be in such a rush that I wasn't watching what I was copying and pasting. I actually did use the command you suggest below. And that's what drew the error message. So I'm back to the drawing board. Take care, and thanks again-- Steve Hy Steve, bin/mysql -p try the following command : bin/mysql -p HTH Oliver
Re: Scripting MySQL Commands
Hy Steve, > bin/mysql -p http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
