Backup Procedure

Hi there –

I have RT running with a MySQL database backend and am in the process of setting
up a backup schedule for the application and its host server.

The software that I am using to for this purpose is Data Protector 5.1. I have
installed the agent program on the server and have done an initial backup of the
server in the following manner:

I have a scheduled cron job that shuts down the SQL server prior to the actual
backup taking place. The filesystems where RT and MySQL are located are backed
up to a remote tape device. After the backup is complete, another cron job is
automatically run to start the SQL server.

I know the mysqldump utility is an option to use when backing up the database.
My question is, should I use mysqldump in addition to the above procedure or is
what I am doing now sufficient? Thanks.

Kaplan, Andrew H. wrote:

I know the mysqldump utility is an option to use when backing up the
database. My question is, should I use mysqldump in addition to the above
procedure or is what I am doing now sufficient? Thanks.

Personally I would use mysqldump /instead/ of what you are doing now.
Many reasons, including the removal of the requirement to restore the
database to the exact same version, patchlevel, etc, of MySQL, and the
fact that if there are any kind of problems with the restore, mysqldump
gives you an ASCII file to rebuild the data from - much more flexible
than a binary file.

Keith

Hi Keith –

I thought I would combine the best of both approaches in the following manner:
1. Cron job /etc/init.d/mysqld stop
2. mysqldump --opt --add-drop-table --single-transaction

                -u <rt_user> -p <rt_password> - h <databasehost>

rt3 > rt3-mysql-backup
3. Backup of the filesystems on the RT server including the
directory where the flat file from step 2 is sent.
4. Cron job /etc/init.d/mysqld start

Your thoughts?From: Keith Edmunds [mailto:keith@midnighthax.com]
Sent: Thursday, October 27, 2005 3:20 PM
To: Kaplan, Andrew H.
Cc: rt-users@lists.bestpractical.com
Subject: Re: [SPAM?] [rt-users] Backup Procedure

Kaplan, Andrew H. wrote:

I know the mysqldump utility is an option to use when backing up the
database. My question is, should I use mysqldump in addition to the above
procedure or is what I am doing now sufficient? Thanks.

Personally I would use mysqldump /instead/ of what you are doing now.
Many reasons, including the removal of the requirement to restore the
database to the exact same version, patchlevel, etc, of MySQL, and the
fact that if there are any kind of problems with the restore, mysqldump
gives you an ASCII file to rebuild the data from - much more flexible
than a binary file.

Keith

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Greetings!

Kaplan, Andrew H. wrote:

I thought I would combine the best of both approaches in the following manner:
1. Cron job /etc/init.d/mysqld stop
2. mysqldump --opt --add-drop-table --single-transaction
-u <rt_user> -p <rt_password> - h
rt3 > rt3-mysql-backup
3. Backup of the filesystems on the RT server including the
directory where the flat file from step 2 is sent.
4. Cron job /etc/init.d/mysqld start

Your thoughts?

You will loose any email arrived between mysql stop and mysql start.
IMHO, “–single-transaction” should be enough, so steps 1 and 4 can be
skipped. Also, check default-charset options, I have troubles with
binary file attachments in backups until set it to “binary”.

I am also using gzip -9 on result file and scp to backupserver.

SY, Danial.


Danial Klimkin,
Falk eSolutions AG Russia
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDYiP02ce6OqXQ52QRAt5fAJ98UI9SRRmnkFZIvV0auKIRou0SbwCfSyPv
Sx9gJj/bGwky0iSJF1Y/V9k=
=EBXp
-----END PGP SIGNATURE-----

It has been my experience that I can build a new box and resolve the
dependencies with CPAN faster than I can spool from tape. I keep ASCII
backups from the first, fifteenth, and last day of each month, as well
as the entire current and one previous months. scp them off to one or
more other servers, and good to go.

Less space to backup, and no tape spooling makes me happy.

DB

Kaplan, Andrew H. wrote:

Hi Keith –

I thought I would combine the best of both approaches in the following manner:
1. Cron job /etc/init.d/mysqld stop
2. mysqldump --opt --add-drop-table --single-transaction

                -u <rt_user> -p <rt_password> - h <databasehost>

rt3 > rt3-mysql-backup
3. Backup of the filesystems on the RT server including the
directory where the flat file from step 2 is sent.
4. Cron job /etc/init.d/mysqld start

Your thoughts?

-----Original Message-----
From: Keith Edmunds [mailto:keith@midnighthax.com]
Sent: Thursday, October 27, 2005 3:20 PM
To: Kaplan, Andrew H.
Cc: rt-users@lists.bestpractical.com
Subject: Re: [SPAM?] [rt-users] Backup Procedure

Kaplan, Andrew H. wrote:

I know the mysqldump utility is an option to use when backing up the
database. My question is, should I use mysqldump in addition to the above
procedure or is what I am doing now sufficient? Thanks.

Personally I would use mysqldump /instead/ of what you are doing now.
Many reasons, including the removal of the requirement to restore the
database to the exact same version, patchlevel, etc, of MySQL, and the
fact that if there are any kind of problems with the restore, mysqldump
gives you an ASCII file to rebuild the data from - much more flexible
than a binary file.

Keith


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

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Buy your copy of our new book, RT Essentials, today!

Download a free sample chapter from http://rtbook.bestpractical.com

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

Greetings!

Kaplan, Andrew H. wrote:

I thought I would combine the best of both approaches in the following manner:
1. Cron job /etc/init.d/mysqld stop
2. mysqldump --opt --add-drop-table --single-transaction
-u <rt_user> -p <rt_password> - h
rt3 > rt3-mysql-backup
3. Backup of the filesystems on the RT server including the
directory where the flat file from step 2 is sent.
4. Cron job /etc/init.d/mysqld start

Your thoughts?

You will loose any email arrived between mysql stop and mysql start.
no, because while mysql is stopped RT web server reports errors to
mailgate and mailgate exits TEMP_FAIL, MTA should resubmit mail
latter. IMHO other behaviour is bug.

IMHO, “–single-transaction” should be enough, so steps 1 and 4 can be
ditto.

skipped. Also, check default-charset options, I have troubles with
binary file attachments in backups until set it to “binary”.
+1, IMHO you should always create binary backups of the RT DB, without
any specific charset.

I am also using gzip -9 on result file and scp to backupserver.

SY, Danial.


Danial Klimkin,
Falk eSolutions AG Russia

Best regards, Ruslan.