Couldnt open File 'sessions.MYI' in /opt/rt3/var/session_data

First post didn’t get a single reply, so please let me
know if I’m not posting correctly. I’ve read the FAQs
and posted on this list before.

The system is a 3 year old RT (RT 3.0.4 I believe)
that has been running without problems. The /var
partition filled up at some point in time and this
error occurred sometime around the same time. I could
give all the sys info but it got no response last
time.

I did a find / -name “sessions.MYI” and don’t see the
file it says it cant open. I’ve CHMOD’ed all the
directories to 777 that are listed in the error,
restarted a couple times and /var is under 70%
utilization. The session_data directory is 777, and
there are no files inside of it. I tried to “touch”
sessions.MYI (when the search showed it didn’t exist
anywhere) within the session_data file (and even
restarted Apache in case it mattered) with no result.

Below is a System Error summary you get when you put
in the URL of the server running RT:

error: RT Couldn’t write to session directory
‘/opt/rt3/var/session_data’: DBD::mysql::st execute
failed: Can’t open file: ‘sessions.MYI’. (errno: 145)
at
/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Store/DBI.pm
line 42.

Stack:
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Store/DBI.pm:42]
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:515]
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:462]
[/opt/rt3/share/html/Elements/SetupSessionCookie:49]
[/opt/rt3/share/html/autohandler:51]
. Check that this dir ectory’s permissions are
correct. at
/opt/rt3/share/html/Elements/SetupSessionCookie line
64.

context: …
60: };
61: undef $cookies{‘RT_SID’};
62: }
63: else {
64: die “RT Couldn’t write to session directory
‘$RT::MasonSessionDir’: $@. Check that this dir
ectory’s permissions are correct.”;
65: }
66: }
67:
68: if ( !$cookies{‘RT_SID’} ) {

code stack:
/opt/rt3/share/html/Elements/SetupSessionCookie:64
g /opt/rt3/share/html/autohandler:51
g

Don’t get soaked. Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather

.MYI… That is the index file for the sessions table? Might it be too
big?
http://wiki.bestpractical.com/view/CleanupSessions

John Schubert wrote:

First post didn’t get a single reply, so please let me
know if I’m not posting correctly. I’ve read the FAQs
and posted on this list before.

The system is a 3 year old RT (RT 3.0.4 I believe)
that has been running without problems. The /var
partition filled up at some point in time and this
error occurred sometime around the same time. I could
give all the sys info but it got no response last
time.

I did a find / -name “sessions.MYI” and don’t see the
file it says it cant open. I’ve CHMOD’ed all the
directories to 777 that are listed in the error,
restarted a couple times and /var is under 70%
utilization. The session_data directory is 777, and
there are no files inside of it. I tried to “touch”
sessions.MYI (when the search showed it didn’t exist
anywhere) within the session_data file (and even
restarted Apache in case it mattered) with no result.

Below is a System Error summary you get when you put
in the URL of the server running RT:


error: RT Couldn’t write to session directory
‘/opt/rt3/var/session_data’: DBD::mysql::st execute
failed: Can’t open file: ‘sessions.MYI’. (errno: 145)
at
/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Store/DBI.pm
line 42.

Stack:
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session/Store/DBI.pm:42]
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:515]
[/usr/lib/perl5/site_perl/5.6.1/Apache/Session.pm:462]
[/opt/rt3/share/html/Elements/SetupSessionCookie:49]
[/opt/rt3/share/html/autohandler:51]
. Check that this dir ectory’s permissions are
correct. at
/opt/rt3/share/html/Elements/SetupSessionCookie line
64.

context: …
60: };
61: undef $cookies{‘RT_SID’};
62: }
63: else {
64: die “RT Couldn’t write to session directory
‘$RT::MasonSessionDir’: $@. Check that this dir
ectory’s permissions are correct.”;
65: }
66: }
67:
68: if ( !$cookies{‘RT_SID’} ) {

code stack:
/opt/rt3/share/html/Elements/SetupSessionCookie:64
g /opt/rt3/share/html/autohandler:51
g


Don’t get soaked. Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Drew Barnes
Applications Analyst
Network Resources Department
Raymond Walters College
University of Cincinnati

John -
If your mysql database is located on /var and /var filled up, you’re
hosed. (If there’s a database engine anywhere that can deal gracefully
with an inability to write to its backing filestore during normal
operations, it’s definitely not MySQL.)

Your best hope is probably to restore /var from a backup made before

the partition choked.

Sorry, 
	Ole

[…]

The system is a 3 year old RT (RT 3.0.4 I believe)
that has been running without problems. The /var
partition filled up at some point in time and this
error occurred sometime around the same time. I could
give all the sys info but it got no response last
time.
[…]

/Ole Craig
Security Engineer
Team lead, customer support

ocraig@stillsecure.com
303-381-3824 direct
303-381-3802 support
303-381-3880 fax

www.stillsecure.com

Thanks Ole,
I checked and with the standard install my database is
indeed in /var. I’m playing with myisamchk to see if
there’s a way to restore.

I asked the IT department to back this server up for
years, but they’ve ignored me. This is the only
machine I have permissions, otherwise I’d have just
backed it up myself. I didn’t backup to itself just
cause the harddrive isn’t terribly large.

I’ll keep digging through MySQL and see if I can
overcome the odds and get the DB back up. If I’m
successful, I’ll post what I did.

Thanks again,
John— Ole Craig ocraig@stillsecure.com wrote:

John -
If your mysql database is located on /var and
/var filled up, you’re
hosed. (If there’s a database engine anywhere that
can deal gracefully
with an inability to write to its backing filestore
during normal
operations, it’s definitely not MySQL.)

Your best hope is probably to restore /var from

a backup made before
the partition choked.

Sorry,
Ole

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more.
http://mobile.yahoo.com/go?refer=1GNXIC

John Schubert wrote:

Thanks Ole,
I checked and with the standard install my database is
indeed in /var. I’m playing with myisamchk to see if
there’s a way to restore.
Just a thought, what else is in /var and can be moved, temporarily, to
another place so that you can start mysql and recover the database.
There is nothing of interest in the sessions table so just dropping it
and recreating it should be no problem one you get into mysql.
I have had a similar experience with mysql, running cacti, that filled
up on its self and came out of it without (too much) damage.

Good luck.

Joop

Are there any resources you’ve seen that were written
well and described MySQL DB recovery? I’ve been
digging through the MySQL documentation and
Yahoo/Googled for “How-To” articles, but most seem to
assume a certain level of knowledge I don’t possess.

I feel dumb. I can compile, run and maintain Apache,
and admin Solaris, but I can’t seem to get my hands
around managing a simple DB.

Thanks for any pointers,
John— Ole Craig ocraig@stillsecure.com wrote:

John -
If your mysql database is located on /var and
/var filled up, you’re
hosed. (If there’s a database engine anywhere that
can deal gracefully
with an inability to write to its backing filestore
during normal
operations, it’s definitely not MySQL.)

Your best hope is probably to restore /var from

a backup made before
the partition choked.

Sorry,
Ole

Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.

Since it seems it is only the sessions table that is borked, you could
try dropping that table and recreating from the definitions in the RT
package you grab from BP’s website.

John Schubert wrote:

Are there any resources you’ve seen that were written
well and described MySQL DB recovery? I’ve been
digging through the MySQL documentation and
Yahoo/Googled for “How-To” articles, but most seem to
assume a certain level of knowledge I don’t possess.

I feel dumb. I can compile, run and maintain Apache,
and admin Solaris, but I can’t seem to get my hands
around managing a simple DB.

Thanks for any pointers,
John

John -
If your mysql database is located on /var and
/var filled up, you’re
hosed. (If there’s a database engine anywhere that
can deal gracefully
with an inability to write to its backing filestore
during normal
operations, it’s definitely not MySQL.)

Your best hope is probably to restore /var from

a backup made before
the partition choked.

Sorry,
Ole


Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.
Yahoo | Mail, Wetter, Suche, Nachrichten, Finanzen, Sport & mehr


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Drew Barnes
Applications Analyst
Network Resources Department
Raymond Walters College
University of Cincinnati

Drew’s right, if it’s only the sessions table that’s gone you’re in
luck, since that only tracks things related to who’s currently logged
in.

And (since I well remember my first attempt to do something with

mysql administration that was not scripted by an RPM installer… :slight_smile:
before you do that, it’d probably be worthwhile to run through the
“Creating and Using a Database” tutorial at
http://dev.mysql.com/doc/refman/5.1/en/database-use.html – then you’ll
have a handle on what Drew means by “dropping the table and recreating
it from the definitions”. Apologies if you’re already beyond this level.

Good luck,
	OleOn Fri, 2007-07-13 at 11:59 -0400, Drew Barnes wrote:

Since it seems it is only the sessions table that is borked, you could
try dropping that table and recreating from the definitions in the RT
package you grab from BP’s website.

John Schubert wrote:

Are there any resources you’ve seen that were written
well and described MySQL DB recovery? I’ve been
digging through the MySQL documentation and
Yahoo/Googled for “How-To” articles, but most seem to
assume a certain level of knowledge I don’t possess.

I feel dumb. I can compile, run and maintain Apache,
and admin Solaris, but I can’t seem to get my hands
around managing a simple DB.

/Ole Craig
Security Engineer
Team lead, customer support

ocraig@stillsecure.com
303-381-3824 direct
303-381-3802 support
303-381-3880 fax

www.stillsecure.com

Ole,
Thank you so much. I haven’t used my skills with
computers in quite awhile and I never was really a
MySQL guru, so no, your idea isn’t below my level. I
understood every word Drew said, but had no idea how
to do it.

I will go through and practice with the tutorial
before I mess with the real one. I’d read through the
MAN and Docs for MySQL, but somehow missed that
tutorial.

I really do appreciate the help you’ve given.

Regards,
John

Drew's right, if it's only the sessions table

that’s gone you’re in
luck, since that only tracks things related to who’s
currently logged
in.

And (since I well remember my first attempt to

do something with
mysql administration that was not scripted by an RPM
installer… :slight_smile:
before you do that, it’d probably be worthwhile to
run through the
“Creating and Using a Database” tutorial at

http://dev.mysql.com/doc/refman/5.1/en/database-use.html

– then you’ll
have a handle on what Drew means by “dropping the
table and recreating
it from the definitions”. Apologies if you’re
already beyond this level.

Good luck,
Ole

Since it seems it is only the sessions table that
is borked, you could
try dropping that table and recreating from the
definitions in the RT
package you grab from BP’s website.

John Schubert wrote:

Are there any resources you’ve seen that were
written
well and described MySQL DB recovery? I’ve been
digging through the MySQL documentation and
Yahoo/Googled for “How-To” articles, but most
seem to
assume a certain level of knowledge I don’t
possess.

I feel dumb. I can compile, run and maintain
Apache,
and admin Solaris, but I can’t seem to get my
hands
around managing a simple DB.


/Ole Craig
Security Engineer
Team lead, customer support

ocraig@stillsecure.com
303-381-3824 direct
303-381-3802 support
303-381-3880 fax

www.stillsecure.com

Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/

I went through the MySQL tutorials and created test
tables to delete, within test databases and got that
down. I also went ahead and practiced importing and
populating fields within a table, from a flat file.

I’ve gone through all the the RT documentation,
researched the archives, and even went through all of
the files in my version’s archive ( tar file ).
However I can’t find the command that creates the
table during initial installation. I believe the
sessions table is actually something very simple from
a post I found in the email archives.

So, I’m asking for a little help since I’m again at a
standstill.

Where is the script that creates the DB to begin with?
Maybe I could use something from initial-database to
manually create it myself.

Can someone do a show tables and then show what the DB
is expecting (char, number, etc) for fields within the
table ‘sessions’? I could just make the command
myself to create if I know what is being passed to
MySQL.

Thanks again. It feels like I’m only a step or two
from getting this back up. If I get it back up, I’ll
create a simple “How-To” on how I recovered from this
for the next noob…and how to avoid.

Respectfully,
John

Drew's right, if it's only the sessions table

that’s gone you’re in
luck, since that only tracks things related to who’s
currently logged
in.

And (since I well remember my first attempt to

do something with
mysql administration that was not scripted by an RPM
installer… :slight_smile:
before you do that, it’d probably be worthwhile to
run through the
“Creating and Using a Database” tutorial at

http://dev.mysql.com/doc/refman/5.1/en/database-use.html

– then you’ll
have a handle on what Drew means by “dropping the
table and recreating
it from the definitions”. Apologies if you’re
already beyond this level.

Good luck,
Ole

Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.

OK - here you go:

mysql> describe sessions;
| Field | Type | Null | Key | Default | Extra |
| id | varchar(32) | NO | PRI | NULL | |
| a_session | longtext | YES | | NULL | |
| LastUpdated | timestamp | YES | | CURRENT_TIMESTAMP | |
3 rows in set (0.00 sec)

James Moseley

         John Schubert                                                 
         <johnschubert@yah                                             
         oo.com>                                                    To 
         Sent by:                  Ole Craig <ocraig@stillsecure.com>, 
         rt-users-bounces@         Drew Barnes                         
         lists.bestpractic         <barnesaw@ucrwcu.rwc.uc.edu>        
         al.com                                                     cc 
                                   RT-Users@lists.bestpractical.com,   
                                   John Schubert                       
         07/18/2007 03:38          <johnschubert@yahoo.com>            
         PM                                                    Subject 
                                   Re: [rt-users] Couldnt open File    
                                   'sessions.MYI' in                   
                                   /opt/rt3/var/session_data           

I went through the MySQL tutorials and created test
tables to delete, within test databases and got that
down. I also went ahead and practiced importing and
populating fields within a table, from a flat file.

I’ve gone through all the the RT documentation,
researched the archives, and even went through all of
the files in my version’s archive ( tar file ).
However I can’t find the command that creates the
table during initial installation. I believe the
sessions table is actually something very simple from
a post I found in the email archives.

So, I’m asking for a little help since I’m again at a
standstill.

Where is the script that creates the DB to begin with?
Maybe I could use something from initial-database to
manually create it myself.

Can someone do a show tables and then show what the DB
is expecting (char, number, etc) for fields within the
table ‘sessions’? I could just make the command
myself to create if I know what is being passed to
MySQL.

Thanks again. It feels like I’m only a step or two
from getting this back up. If I get it back up, I’ll
create a simple “How-To” on how I recovered from this
for the next noob…and how to avoid.

Respectfully,
John

Drew's right, if it's only the sessions table

that’s gone you’re in
luck, since that only tracks things related to who’s
currently logged
in.

And (since I well remember my first attempt to

do something with
mysql administration that was not scripted by an RPM
installer… :slight_smile:
before you do that, it’d probably be worthwhile to
run through the
“Creating and Using a Database” tutorial at

http://dev.mysql.com/doc/refman/5.1/en/database-use.html

– then you’ll
have a handle on what Drew means by “dropping the
table and recreating
it from the definitions”. Apologies if you’re
already beyond this level.

       Good luck,
                   Ole

Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.

http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

BINGO!! That’s all it took. 2 weeks of down time to
do about 5 minutes of work. You might ask, “How did
they put up with it?” New manager had gone behind me
and started development of a ticketing system within
our Sales database. Also, I had tons of other things
to get done.

Anyway, here’s what it took to fix it.

  1. Clean out /var partition. I am not a mail guru.
    I can do /etc/aliases and basic admin but not really
    good at configuring and this is a machine no one logs
    into (so no checking mail). Mail had filled up /var.

  2. Stopped Apache. I didn’t want anyone trying to do
    things while I was recovering. Using a typical
    redhat, it was: /usr/local/apache/bin/apachectl stop

  3. Dropped table called sessions.
    [root@localhost john]# mysql -p #entered password at
    prompt

mysql> use rt3
Database changed
mysql> DROP TABLE sessions;

  1. Create the fixed version, which Google found the
    command for here:
    http://www.netrifuge.com/rt/etc/schema.mysql

mysql> CREATE TABLE sessions (
→ id char(32) NOT NULL,
→ a_session LONGTEXT,
→ LastUpdated TIMESTAMP,
→ PRIMARY KEY (id)
→ );

  1. Started Apache ( /usr/local/apache/bin/apachectl
    start), opened up IE (felt the earth shudder), typed
    in the URL and, PRESTO!

My description is a little different, but RT seems ok.
Here is what my description of ‘sessions’ looks like
with that command.

| Field | Type | Null | Key | Default
| Extra |
| id | varchar(32) | | PRI |
| |
| a_session | longtext | YES | | NULL
| |
| LastUpdated | timestamp(14) | YES | | NULL
| |
3 rows in set (0.00 sec)

So, for now all is well. Now to go back and do the
tutorial on DB back up and do it myself, and then
figure out how to automate pushing it to another place
on an all Microsoft shop (on which I have no
permissions anywhere).

Thanks again to everyone.

Regards,
John

jmoseley@corp.xanadoo.com wrote:

OK - here you go:

mysql> describe sessions;

| Field | Type | Null | Key | Default
| Extra |

| id | varchar(32) | NO | PRI | NULL
| |
| a_session | longtext | YES | | NULL
| |
| LastUpdated | timestamp | YES | |
CURRENT_TIMESTAMP | |

3 rows in set (0.00 sec)

James Moseley

         John Schubert                          
                  
         <johnschubert@yah                      
                  
         oo.com>                                
               To 
         Sent by:                  Ole Craig

ocraig@stillsecure.com,
rt-users-bounces@ Drew Barnes

         lists.bestpractic        

barnesaw@ucrwcu.rwc.uc.edu
al.com
cc

RT-Users@lists.bestpractical.com,
John Schubert

         07/18/2007 03:38         

johnschubert@yahoo.com
PM
Subject
Re:
[rt-users] Couldnt open File

‘sessions.MYI’ in

/opt/rt3/var/session_data

I went through the MySQL tutorials and created test
tables to delete, within test databases and got that
down. I also went ahead and practiced importing and
populating fields within a table, from a flat file.

I’ve gone through all the the RT documentation,
researched the archives, and even went through all
of
the files in my version’s archive ( tar file ).
However I can’t find the command that creates the
table during initial installation. I believe the
sessions table is actually something very simple
from
a post I found in the email archives.

So, I’m asking for a little help since I’m again at
a
standstill.

Where is the script that creates the DB to begin
with?
Maybe I could use something from initial-database to
manually create it myself.

Can someone do a show tables and then show what the
DB
is expecting (char, number, etc) for fields within
the
table ‘sessions’? I could just make the command
myself to create if I know what is being passed to
MySQL.

Thanks again. It feels like I’m only a step or two
from getting this back up. If I get it back up, I’ll
create a simple “How-To” on how I recovered from
this
for the next noob…and how to avoid.

Respectfully,
John

Drew's right, if it's only the sessions table

that’s gone you’re in
luck, since that only tracks things related to
who’s
currently logged
in.

And (since I well remember my first attempt to

do something with
mysql administration that was not scripted by an
RPM
installer… :slight_smile:
before you do that, it’d probably be worthwhile to
run through the
“Creating and Using a Database” tutorial at

http://dev.mysql.com/doc/refman/5.1/en/database-use.html

– then you’ll
have a handle on what Drew means by “dropping the
table and recreating
it from the definitions”. Apologies if you’re
already beyond this level.

       Good luck,
                   Ole

Be a better Globetrotter. Get better travel answers
from someone who knows.
Yahoo! Answers - Check it out.


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from
O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online.

John Schubert wrote:

So, for now all is well. Now to go back and do the
tutorial on DB back up and do it myself, and then
figure out how to automate pushing it to another place
on an all Microsoft shop (on which I have no
permissions anywhere).
Just a thought, can’t you put it on your Windows home directory?
I would make a cronjob on the linux server which would do the backup,
zip it and then have a windows task ftp it from the linux machine to my
home directory, which I presume is being backupped.

Just my 2 cents.

Joop

Regarding #2, I have found that this works WONDERS for keeping people
from trying to use the UI while I do a little tinkering.
http://wiki.bestpractical.com/view/MaintenanceMode

John Schubert wrote:

BINGO!! That’s all it took. 2 weeks of down time to
do about 5 minutes of work. You might ask, “How did
they put up with it?” New manager had gone behind me
and started development of a ticketing system within
our Sales database. Also, I had tons of other things
to get done.

Anyway, here’s what it took to fix it.

  1. Clean out /var partition. I am not a mail guru.
    I can do /etc/aliases and basic admin but not really
    good at configuring and this is a machine no one logs
    into (so no checking mail). Mail had filled up /var.

  2. Stopped Apache. I didn’t want anyone trying to do
    things while I was recovering. Using a typical
    redhat, it was: /usr/local/apache/bin/apachectl stop

Drew Barnes
Applications Analyst
Network Resources Department
Raymond Walters College
University of Cincinnati

Now to go back and do the
tutorial on DB back up and do it myself, and then
figure out how to automate pushing it to another place
on an all Microsoft shop (on which I have no
permissions anywhere).

I just had to figure this out myself. Here’s the script I worked up, with
some help from the wiki. Cron kicks it off every night. It’s been running
correctly for a few days…

I’m no shell scripting expert, and there’s no error catching whatsoever
right now. All the usual warnings apply: no guarantees, no warranties, YMMV.
And of course, everyone, feel free to tell me exactly where I made horrid
mistakes!

----- CUT HERE -----
#!/bin/bash

rtbackup.sh

Backup RT database & config files

Scott Hill, 7/16/2007

Based in part on backupRT.sh by Michael Erana

(BackupRT - Request Tracker Wiki)

Script Variables

SCRTMPDIR=/etc/request-tracker3.6/backup
dumpfile=rtdb-date +%Y%m%d.sql
rtdbuser=rtuser
rtdbpwd=password
rtdbname=rt
backupshare=//server/share
backupmountpoint=/mnt/server
shareuser=username
sharepass=password
backupdir=/mnt/server/backups
backupfn=rtbackup.zip

Mount backup target volume

smbmount $backupshare $backupmountpoint -o
username=$shareuser,password=$sharepass

Make/Verify target dirs

if [ ! -d $backupdir ]; then
mkdir $backdir;
fi

if [ ! -d $SCRTMPDIR ]; then
mkdir $SCRTMPDIR;
fi

Rename any existing files, keeping a week’s worth of files

rm $backupdir/$backupfn.6
mv $backupdir/$backupfn.5 $backupdir/$backupfn.6
mv $backupdir/$backupfn.4 $backupdir/$backupfn.5
mv $backupdir/$backupfn.3 $backupdir/$backupfn.4
mv $backupdir/$backupfn.2 $backupdir/$backupfn.3
mv $backupdir/$backupfn.1 $backupdir/$backupfn.2
mv $backupdir/$backupfn $backupdir/$backupfn.1

MySQL dump

mysqldump --opt --add-drop-table --single-transaction -u $rtdbuser
-p$rtdbpwd $rtdbname > $SCRTMPDIR/$dumpfile

Zip up the dumpfile and the /etc/request-tracker3.6 dir

zip -9 $backupdir/$backupfn $SCRTMPDIR/$dumpfile /etc/request-tracker3.6/*

Cleanup

rm $SCRTMPDIR/$dumpfile
smbumount $backupmountpoint

----- CUT HERE -----

Scott Hill shill@metro-dentalcare.com
Network Administrator
Metro Dentalcare
(612) 861-9119

Scott -

Throw in some error checking - it can’t hurt. Also, do a check to see
if the MySQL database is online (you never know) before trying to back
up the database within. Here’s an example:

/path/to/mysqladmin --user=$USER --password=$PASS ping | grep “mysqld
is alive” >/dev/null 2>&1
if [ $? = 0 ]; then
echo “alive”
else
echo “dead!”
exit 1
fi

Use the “if [ $? = 0 ]” after all of your commands (ie smbmount,
mysqldump, etc) to check for errors. You may also want to do a ‘rm -f’
instead of just ‘rm’.

Just my two cents. Hope this helps.

GregOn 7/19/07, Scott Hill shill@metro-dentalcare.com wrote:

On 7/18/07 5:58 PM, “John Schubert” johnschubert@yahoo.com wrote:

Now to go back and do the
tutorial on DB back up and do it myself, and then
figure out how to automate pushing it to another place
on an all Microsoft shop (on which I have no
permissions anywhere).

I just had to figure this out myself. Here’s the script I worked up, with
some help from the wiki. Cron kicks it off every night. It’s been running
correctly for a few days…

I’m no shell scripting expert, and there’s no error catching whatsoever
right now. All the usual warnings apply: no guarantees, no warranties, YMMV.
And of course, everyone, feel free to tell me exactly where I made horrid
mistakes!

----- CUT HERE -----
#!/bin/bash

rtbackup.sh

Backup RT database & config files

Scott Hill, 7/16/2007

Based in part on backupRT.sh by Michael Erana

(BackupRT - Request Tracker Wiki)

Script Variables

SCRTMPDIR=/etc/request-tracker3.6/backup
dumpfile=rtdb-date +%Y%m%d.sql
rtdbuser=rtuser
rtdbpwd=password
rtdbname=rt
backupshare=//server/share
backupmountpoint=/mnt/server
shareuser=username
sharepass=password
backupdir=/mnt/server/backups
backupfn=rtbackup.zip

Mount backup target volume

smbmount $backupshare $backupmountpoint -o
username=$shareuser,password=$sharepass

Make/Verify target dirs

if [ ! -d $backupdir ]; then
mkdir $backdir;
fi

if [ ! -d $SCRTMPDIR ]; then
mkdir $SCRTMPDIR;
fi

Rename any existing files, keeping a week’s worth of files

rm $backupdir/$backupfn.6
mv $backupdir/$backupfn.5 $backupdir/$backupfn.6
mv $backupdir/$backupfn.4 $backupdir/$backupfn.5
mv $backupdir/$backupfn.3 $backupdir/$backupfn.4
mv $backupdir/$backupfn.2 $backupdir/$backupfn.3
mv $backupdir/$backupfn.1 $backupdir/$backupfn.2
mv $backupdir/$backupfn $backupdir/$backupfn.1

MySQL dump

mysqldump --opt --add-drop-table --single-transaction -u $rtdbuser
-p$rtdbpwd $rtdbname > $SCRTMPDIR/$dumpfile

Zip up the dumpfile and the /etc/request-tracker3.6 dir

zip -9 $backupdir/$backupfn $SCRTMPDIR/$dumpfile /etc/request-tracker3.6/*

Cleanup

rm $SCRTMPDIR/$dumpfile
smbumount $backupmountpoint

----- CUT HERE -----


Scott Hill shill@metro-dentalcare.com
Network Administrator
Metro Dentalcare
(612) 861-9119


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Hey all,

So there are a number of ways you can handle this - either take a ‘cold
backup’ of the database, ie just copy/tar/zip all the data files off
somewhere, mind you to do this you will need to either shutdown the
database or ensure no updates take place (lock the tables). Or you can
take the mysqldump, though it is slower and will lock the tables its
dumping so you won’t be able to update them anyhow. If you can spare
the time (or are doing it from a slave), I highly suggest doing the
former option. Restoring one table out of a tarball is way easier than
sorting through a huge dump file (unless you’re running innodb all in
one file…).

if doing a dump, you might not want the username/password on the command
line … I suggest running mysqldump with “–defaults-file=” (
as the first arguement, gets pissy otherwise) and having the contents of
look like:

[mysqldump]
user=rtdumper
password=alice_bob

You should adjust the permissions on that file to deny world reading…

you can also just pipe the mysqldump to gzip/bzip2, this will make it
much faster… eg: mysqldump | bzip2 -9 > mysqldump.bz2.

You should also set your umask to at least 007 before hand, as creating
a dump / backup of your database world readable kinda defeats the
purpose of the database permissions…

Another trick is if you’re only going to keep a weeks worth of backups,
just use the day of week when creating the file, then you don’t have to
worry about rotating them (and mentally set $AmbiguousDayInPast to 1…)

just some random thought…
.r’

Scott Hill wrote:> On 7/18/07 5:58 PM, “John Schubert” johnschubert@yahoo.com wrote:

Now to go back and do the
tutorial on DB back up and do it myself, and then
figure out how to automate pushing it to another place
on an all Microsoft shop (on which I have no
permissions anywhere).

I just had to figure this out myself. Here’s the script I worked up, with
some help from the wiki. Cron kicks it off every night. It’s been running
correctly for a few days…

I’m no shell scripting expert, and there’s no error catching whatsoever
right now. All the usual warnings apply: no guarantees, no warranties, YMMV.
And of course, everyone, feel free to tell me exactly where I made horrid
mistakes!

----- CUT HERE -----
#!/bin/bash

rtbackup.sh

Backup RT database & config files

Scott Hill, 7/16/2007

Based in part on backupRT.sh by Michael Erana

(BackupRT - Request Tracker Wiki)

Script Variables

SCRTMPDIR=/etc/request-tracker3.6/backup
dumpfile=rtdb-date +%Y%m%d.sql
rtdbuser=rtuser
rtdbpwd=password
rtdbname=rt
backupshare=//server/share
backupmountpoint=/mnt/server
shareuser=username
sharepass=password
backupdir=/mnt/server/backups
backupfn=rtbackup.zip

Mount backup target volume

smbmount $backupshare $backupmountpoint -o
username=$shareuser,password=$sharepass

Make/Verify target dirs

if [ ! -d $backupdir ]; then
mkdir $backdir;
fi

if [ ! -d $SCRTMPDIR ]; then
mkdir $SCRTMPDIR;
fi

Rename any existing files, keeping a week’s worth of files

rm $backupdir/$backupfn.6
mv $backupdir/$backupfn.5 $backupdir/$backupfn.6
mv $backupdir/$backupfn.4 $backupdir/$backupfn.5
mv $backupdir/$backupfn.3 $backupdir/$backupfn.4
mv $backupdir/$backupfn.2 $backupdir/$backupfn.3
mv $backupdir/$backupfn.1 $backupdir/$backupfn.2
mv $backupdir/$backupfn $backupdir/$backupfn.1

MySQL dump

mysqldump --opt --add-drop-table --single-transaction -u $rtdbuser
-p$rtdbpwd $rtdbname > $SCRTMPDIR/$dumpfile

Zip up the dumpfile and the /etc/request-tracker3.6 dir

zip -9 $backupdir/$backupfn $SCRTMPDIR/$dumpfile /etc/request-tracker3.6/*

Cleanup

rm $SCRTMPDIR/$dumpfile
smbumount $backupmountpoint

----- CUT HERE -----