Menu
▾
▴
Re: [Bacula-devel] bpipe mysqldump problem
|
From: Kern S. <ke...@si...> - 2009-05-28 10:40:22
|
You might look carefully at the mysqldump documentation. They probably have a
command line option that prevents timeouts or sets the timeout periods.
Normally if one writes C code such as Bacula, we must explicitly enable what
is called "reconnect=1". We set it in the initialization packet. We also
set the following variables --
wait_timeout=691200
interactive_timeout=691200
using SQL commands.
Pehaps it is possible to set them in the my.ini file.
Regards,
Kern
On Thursday 28 May 2009 11:43:08 Ulrich Leodolter wrote:
> Hello,
>
> I found it is a mysqldump problem, but only if called via bpipe.
> Got this message on bconsole:
>
> *mysqldump: Error 2013: Lost connection to MySQL server during query
> when dumping table `File` at row: 26695285
>
> Don't understand, because i have no problems running the standard
> BackupCatalog using the
> RunBeforeJob = "/opt/bacula/scripts/make_daily_backup"
> option.
>
> Wrote also a little C code, this program has no
> problem dumping 14G bacula database.
>
> ---------------------------------
> #include <stdio.h>
> #include <stdlib.h>
> #include <errno.h>
> #include <string.h>
>
> char buf[65536];
>
> int
> main(int argc, char *argv[])
> {
> int status;
> FILE *p = popen("mysqldump -f --opt bacula", "r");
>
> if (!p)
> {
> fprintf(stderr, "popen failed: %s\n", strerror(errno));
> exit(1);
> }
> while ((status = fread(buf, 1, sizeof(buf), p)) > 0)
> {
> fwrite(buf, 1, status, stdout);
> }
> if (status == 0 && ferror(p))
> {
> fprintf(stderr, "Pipe read error: ERR=%s\n", strerror(errno));
> }
> fclose(p);
> exit(0);
> }
> ---------------------------------
>
>
> What is different when bacula calls mysqldump via bpipe plugin?
>
>
> Thanks
> Ulrich
>
> On Wed, 2009-05-27 at 20:25 +0200, Ulrich Leodolter wrote:
> > Hello,
> >
> > On Wed, 2009-05-27 at 16:44 +0200, Kern Sibbald wrote:
> > > Nice that you ruled out problems with 4GB
> > >
> > > In looking at your plugin command line, I recommend:
> > > try turning off gzip on the plugin command line -- I don't think it
> > > will work correctly with the plugin (we have never used that option).
> > > Then make sure there is no compression in the Bacula FileSet. If that
> > > works, try turning on compression in the Bacula FileSet. I think it
> > > will work, but it needs testing. It is better to try things one step
> > > at a time when you have such problems.
> > >
> > > In any case, you do not want to compress on the plugin command line AND
> > > in the Bacula FileSet.
> >
> > The 4th field after ":" in Plugin line is only used for Restore.
> >
> > I tried without any compression, below are Backup and Restore log.
> >
> > >From the bacula point of view everything looks ok, but saved
> >
> > data read from "mysqldump -f --opt bacula" is much too small (about 1G).
> >
> > As i said earlier, running "mysqldump -f --opt bacula > bacula.sql" from
> > shell results in file > 10G.
> >
> > Running "mysqldump -f --opt bacula" via system call popen() from
> > bpipe-fd.so results in about 1G stored data (compressed or uncompressed)
> >
> >
> > FileSet {
> > Name = CatalogPipe
> > Ignore FileSet Changes = yes
> > Include {
> > Options {
> > signature = MD5
> > }
> > Plugin = "bpipe:/MYSQL/bacula.sql:mysqldump -f --opt bacula:cat >
> > /var/tmp/bacula.sql" }
> > }
> >
> > troll-dir JobId 25220: Start Backup JobId 25220,
> > Job=Backup-CatalogPipe.2009-05-27_19.02.35_26
> > troll-dir JobId 25220: Using Device "FileStorage"
> > troll-sd JobId 25220: Volume "Backup-0615" previously written, moving to
> > end of data. troll-sd JobId 25220: Ready to append to end of Volume
> > "Backup-0615" size=35723280678 troll-sd JobId 25220: Job write elapsed
> > time = 00:01:28, Transfer rate = 11.38 M bytes/second troll-dir JobId
> > 25220: Bacula troll-dir 3.0.1 (30Apr09): 27-May-2009 19:04:05 Build OS:
> > i686-pc-linux-gnu redhat Enterprise release JobId:
> > 25220
> > Job: Backup-CatalogPipe.2009-05-27_19.02.35_26
> > Backup Level: Full
> > Client: "troll-fd" 3.0.1 (30Apr09)
> > i686-pc-linux-gnu,redhat,Enterprise release FileSet:
> > "CatalogPipe" 2009-05-27 11:30:04
> > Pool: "DiskBackup" (From Job resource)
> > Catalog: "MyCatalog" (From Client resource)
> > Storage: "File" (From Job resource)
> > Scheduled time: 27-May-2009 19:02:28
> > Start time: 27-May-2009 19:02:37
> > End time: 27-May-2009 19:04:05
> > Elapsed time: 1 min 28 secs
> > Priority: 11
> > FD Files Written: 1
> > SD Files Written: 1
> > FD Bytes Written: 1,002,270,971 (1.002 GB)
> > SD Bytes Written: 1,002,271,148 (1.002 GB)
> > Rate: 11389.4 KB/s
> > Software Compression: None
> > VSS: no
> > Encryption: no
> > Accurate: no
> > Volume name(s): Backup-0615
> > Volume Session Id: 927
> > Volume Session Time: 1242371530
> > Last Volume Bytes: 36,726,157,806 (36.72 GB)
> > Non-fatal FD errors: 0
> > SD Errors: 0
> > FD termination status: OK
> > SD termination status: OK
> > Termination: Backup OK
> >
> > troll-dir JobId 25220: Begin pruning Jobs.
> > troll-dir JobId 25220: No Jobs found to prune.
> > troll-dir JobId 25220: Begin pruning Files.
> > troll-dir JobId 25220: No Files found to prune.
> > troll-dir JobId 25220: End auto prune.
> >
> >
> >
> > troll-dir JobId 25222: Start Restore Job
> > RestoreFiles.2009-05-27_19.07.08_29
> > troll-dir JobId 25222: Using Device "FileStorage"
> > troll-sd JobId 25222: Ready to read from volume "Backup-0615" on device
> > "FileStorage" (/disk0/bacula/files). troll-sd JobId 25222: Forward
> > spacing Volume "Backup-0615" to file:block 8:1363542310. troll-sd JobId
> > 25222: End of Volume at file 8 on device "FileStorage"
> > (/disk0/bacula/files), Volume "Backup-0615" troll-sd JobId 25222: End of
> > all volumes.
> > troll-dir JobId 25222: Bacula troll-dir 3.0.1 (30Apr09): 27-May-2009
> > 19:12:46 Build OS: i686-pc-linux-gnu redhat Enterprise
> > release JobId: 25222
> > Job: RestoreFiles.2009-05-27_19.07.08_29
> > Restore Client: troll-fd
> > Start time: 27-May-2009 19:07:10
> > End time: 27-May-2009 19:12:46
> > Files Expected: 1
> > Files Restored: 1
> > Bytes Restored: 1,002,270,971
> > Rate: 2982.9 KB/s
> > FD Errors: 0
> > FD termination status: OK
> > SD termination status: OK
> > Termination: Restore OK
> >
> > troll-dir JobId 25222: Begin pruning Jobs.
> > troll-dir JobId 25222: No Jobs found to prune.
> > troll-dir JobId 25222: Begin pruning Files.
> > troll-dir JobId 25222: No Files found to prune.
> > troll-dir JobId 25222: End auto prune.
> >
> >
> > [root@troll ~]# ls -l /var/tmp/bacula.sql
> > -rw-r----- 1 root bacula 1002270971 May 27 19:12 /var/tmp/bacula.sql
> >
> >
> >
> > Regards
> > Ulrich
> >
> > > Regards,
> > >
> > > Kern
> > >
> > > On Wednesday 27 May 2009 16:12:25 Ulrich Leodolter wrote:
> > > > Hello,
> > > >
> > > > On Wed, 2009-05-27 at 14:28 +0200, Kern Sibbald wrote:
> > > > > It is quite possible that mysqldump creates a much larger dump file
> > > > > particularly if you have compression turned on.
> > > > >
> > > > > Perhaps you filled up your output filesystem.
> > > >
> > > > definitely NO
> > > >
> > > > > We have tested this many times and never had such problems. You
> > > > > might try testing with a much smaller database to ensure that you
> > > > > are using the proper syntax, ... before working with such a large
> > > > > database.
> > > > >
> > > > > One possibility is that some value in the plugin is limited to 32
> > > > > bits (i.e. a bit less than 4GB).
> > > >
> > > > I tried to dump single bacula catalog table,
> > > > it worked without problems, FD Bytes Written are than 4 GB,
> > > >
> > > > Plugin = "bpipe:/MYSQL/bacula.sql:mysqldump -f --opt bacula File:gzip
> > > > > /var/tmp/bacula-File.sql.gz"
> > > >
> > > >
> > > > I feel helpless, dumping large File table (more than 10GB
> > > > uncompressed) works in bpipe mode. But dumping the whole bacula
> > > > database using bpipe results in incomplete dump.
> > > >
> > > >
> > > > Here is the job log using the above Plugin line.
> > > >
> > > >
> > > > troll-dir JobId 25217: Start Backup JobId 25217,
> > > > Job=Backup-CatalogPipe.2009-05-27_15.08.03_11 troll-dir JobId 25217:
> > > > Using Device "FileStorage"
> > > > troll-sd JobId 25217: Volume "Backup-0615" previously written, moving
> > > > to end of data. troll-sd JobId 25217: Ready to append to end of
> > > > Volume "Backup-0615" size=30604524317 troll-sd JobId 25217: Job write
> > > > elapsed time = 00:25:26, Transfer rate = 2.696 M bytes/second
> > > > troll-dir JobId 25217: Bacula troll-dir 3.0.1 (30Apr09): 27-May-2009
> > > > 15:33:31 Build OS: i686-pc-linux-gnu redhat Enterprise release JobId:
> > > > 25217
> > > > Job: Backup-CatalogPipe.2009-05-27_15.08.03_11
> > > > Backup Level: Full
> > > > Client: "troll-fd" 3.0.1 (30Apr09)
> > > > i686-pc-linux-gnu,redhat,Enterprise release FileSet:
> > > > "CatalogPipe" 2009-05-27 11:30:04
> > > > Pool: "DiskBackup" (From Job resource)
> > > > Catalog: "MyCatalog" (From Client resource)
> > > > Storage: "File" (From Job resource)
> > > > Scheduled time: 27-May-2009 15:07:59
> > > > Start time: 27-May-2009 15:08:05
> > > > End time: 27-May-2009 15:33:31
> > > > Elapsed time: 25 mins 26 secs
> > > > Priority: 11
> > > > FD Files Written: 1
> > > > SD Files Written: 1
> > > > FD Bytes Written: 4,114,114,420 (4.114 GB)
> > > > SD Bytes Written: 4,114,114,606 (4.114 GB)
> > > > Rate: 2696.0 KB/s
> > > > Software Compression: 70.1 %
> > > > VSS: no
> > > > Encryption: no
> > > > Accurate: no
> > > > Volume name(s): Backup-0615
> > > > Volume Session Id: 924
> > > > Volume Session Time: 1242371530
> > > > Last Volume Bytes: 34,721,566,627 (34.72 GB)
> > > > Non-fatal FD errors: 0
> > > > SD Errors: 0
> > > > FD termination status: OK
> > > > SD termination status: OK
> > > > Termination: Backup OK
> > > >
> > > > troll-dir JobId 25217: Begin pruning Jobs.
> > > > troll-dir JobId 25217: No Jobs found to prune.
> > > > troll-dir JobId 25217: Begin pruning Files.
> > > > troll-dir JobId 25217: No Files found to prune.
> > > > troll-dir JobId 25217: End auto prune.
> > > >
> > > >
> > > > Regards
> > > > Ulrich
> > > >
> > > > > Regards,
> > > > >
> > > > > Kern
> > > > >
> > > > > On Wednesday 27 May 2009 13:35:53 Ulrich Leodolter wrote:
> > > > > > Hello,
> > > > > >
> > > > > > I tried to setup bpipe plugin for Catalog backup.
> > > > > > Below you can see FileSet/Job config, Backup/Restore logs.
> > > > > > Everything looks ok, but mysqldump is incomplete!
> > > > > >
> > > > > > Running "mysqldump -f --opt bacula > bacula.sql" from shell
> > > > > > results in 14G bacula.sql file.
> > > > > >
> > > > > > /MYSQL/bacula.sql is only 3,325,644,283 bytes as u can see in
> > > > > > restore log.
> > > > > >
> > > > > > Here are the last lines of restored file /var/tmp/bacula.sql.gz,
> > > > > > last lines look ok, but dump file is definitely incomplete.
> > > > > >
> > > > > > A A ou BAA I BKEH+e BEE6La BJWk5Q A A
> > > > > > E','dBR5u4Hk9YoQc9qFPSQEfvDv4Rg'); /*!40103 SET
> > > > > > TIME_ZONE=@OLD_TIME_ZONE */;
> > > > > >
> > > > > > /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
> > > > > > /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
> > > > > > /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
> > > > > > /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
> > > > > > /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
> > > > > > /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
> > > > > > /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
> > > > > >
> > > > > > -- Dump completed on 2009-05-27 10:56:00
> > > > > >
> > > > > >
> > > > > >
> > > > > > It looks like mysqldump called from bpipe terminates/stops
> > > > > > while dumping File.
> > > > > >
> > > > > > [root@troll tmp]# zgrep "CREATE TABLE" /var/tmp/bacula.sql.gz
> > > > > > CREATE TABLE `BaseFiles` (
> > > > > > CREATE TABLE `CDImages` (
> > > > > > CREATE TABLE `Client` (
> > > > > > CREATE TABLE `Counters` (
> > > > > > CREATE TABLE `Device` (
> > > > > > CREATE TABLE `File` (
> > > > > > [root@troll tmp]#
> > > > > >
> > > > > >
> > > > > > Regards
> > > > > > Ulrich
> > > > > >
> > > > > > -------
> > > > > >
> > > > > >
> > > > > > FileSet {
> > > > > > Name = CatalogPipe
> > > > > > Ignore FileSet Changes = yes
> > > > > > Include {
> > > > > > Options {
> > > > > > signature = MD5
> > > > > > compression = GZIP
> > > > > > }
> > > > > > Plugin = "bpipe:/MYSQL/bacula.sql:mysqldump -f --opt
> > > > > > bacula:gzip > /var/tmp/bacula.sql.gz" }
> > > > > > }
> > > > > >
> > > > > > Job {
> > > > > > Name = Backup-CatalogPipe
> > > > > > Type = Backup
> > > > > > Level = Full
> > > > > > Client = troll-fd
> > > > > > FileSet = "CatalogPipe"
> > > > > > Storage = File
> > > > > > Messages = StandardMailOnError
> > > > > > Pool = DiskBackup
> > > > > > Write Bootstrap = "/opt/bacula/working/%n.bsr"
> > > > > > Priority = 11
> > > > > > SpoolData = no
> > > > > > Allow Duplicate Jobs = no
> > > > > > }
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > troll-dir JobId 25211: Start Backup JobId 25211,
> > > > > > Job=Backup-CatalogPipe.2009-05-27_12.48.41_59
> > > > > > troll-dir JobId 25211: Using Device "FileStorage"
> > > > > > troll-sd JobId 25211: Volume "Backup-0615" previously written,
> > > > > > moving to end of data. troll-sd JobId 25211: Ready to append to
> > > > > > end of Volume "Backup-0615" size=29564841571 troll-sd JobId
> > > > > > 25211: Job write elapsed time = 00:07:17, Transfer rate = 2.290 M
> > > > > > bytes/second troll-dir JobId 25211: Bacula troll-dir 3.0.1
> > > > > > (30Apr09): 27-May-2009 12:56:00 Build OS: i686-pc-linux-gnu
> > > > > > redhat Enterprise release JobId:
> > > > > > 25211
> > > > > > Job:
> > > > > > Backup-CatalogPipe.2009-05-27_12.48.41_59 Backup Level:
> > > > > > Full
> > > > > > Client: "troll-fd" 3.0.1 (30Apr09)
> > > > > > i686-pc-linux-gnu,redhat,Enterprise release FileSet:
> > > > > > "CatalogPipe" 2009-05-27 11:30:04
> > > > > > Pool: "DiskBackup" (From Job resource)
> > > > > > Catalog: "MyCatalog" (From Client resource)
> > > > > > Storage: "File" (From Job resource)
> > > > > > Scheduled time: 27-May-2009 12:48:34
> > > > > > Start time: 27-May-2009 12:48:43
> > > > > > End time: 27-May-2009 12:56:00
> > > > > > Elapsed time: 7 mins 17 secs
> > > > > > Priority: 11
> > > > > > FD Files Written: 1
> > > > > > SD Files Written: 1
> > > > > > FD Bytes Written: 1,001,064,037 (1.001 GB)
> > > > > > SD Bytes Written: 1,001,064,218 (1.001 GB)
> > > > > > Rate: 2290.8 KB/s
> > > > > > Software Compression: 69.9 %
> > > > > > VSS: no
> > > > > > Encryption: no
> > > > > > Accurate: no
> > > > > > Volume name(s): Backup-0615
> > > > > > Volume Session Id: 918
> > > > > > Volume Session Time: 1242371530
> > > > > > Last Volume Bytes: 30,566,617,501 (30.56 GB)
> > > > > > Non-fatal FD errors: 0
> > > > > > SD Errors: 0
> > > > > > FD termination status: OK
> > > > > > SD termination status: OK
> > > > > > Termination: Backup OK
> > > > > >
> > > > > > troll-dir JobId 25211: Begin pruning Jobs.
> > > > > > troll-dir JobId 25211: No Jobs found to prune.
> > > > > > troll-dir JobId 25211: Begin pruning Files.
> > > > > > troll-dir JobId 25211: No Files found to prune.
> > > > > > troll-dir JobId 25211: End auto prune.
> > > > > >
> > > > > >
> > > > > >
> > > > > > troll-dir JobId 25212: Start Restore Job
> > > > > > RestoreFiles.2009-05-27_12.57.43_02
> > > > > > troll-dir JobId 25212: Using Device "FileStorage"
> > > > > > troll-sd JobId 25212: Ready to read from volume "Backup-0615" on
> > > > > > device "FileStorage" (/disk0/bacula/files). troll-sd JobId 25212:
> > > > > > Forward spacing Volume "Backup-0615" to file:block 6:3795037795.
> > > > > > troll-sd JobId 25212: End of Volume at file 7 on device
> > > > > > "FileStorage"
> > > > > > (/disk0/bacula/files), Volume "Backup-0615" troll-sd JobId 25212:
> > > > > > End of all volumes.
> > > > > > troll-dir JobId 25212: Bacula troll-dir 3.0.1 (30Apr09):
> > > > > > 27-May-2009 13:00:19 Build OS: i686-pc-linux-gnu
> > > > > > redhat Enterprise release JobId: 25212
> > > > > > Job: RestoreFiles.2009-05-27_12.57.43_02
> > > > > > Restore Client: troll-fd
> > > > > > Start time: 27-May-2009 12:57:45
> > > > > > End time: 27-May-2009 13:00:19
> > > > > > Files Expected: 1
> > > > > > Files Restored: 1
> > > > > > Bytes Restored: 3,325,644,283
> > > > > > Rate: 21595.1 KB/s
> > > > > > FD Errors: 0
> > > > > > FD termination status: OK
> > > > > > SD termination status: OK
> > > > > > Termination: Restore OK
> > > > > >
> > > > > > troll-dir JobId 25212: Begin pruning Jobs.
> > > > > > troll-dir JobId 25212: No Jobs found to prune.
> > > > > > troll-dir JobId 25212: Begin pruning Files.
> > > > > > troll-dir JobId 25212: No Files found to prune.
> > > > > > troll-dir JobId 25212: End auto prune.
> >
> > -------------------------------------------------------------------------
> >----- Register Now for Creativity and Technology (CaT), June 3rd, NYC. CaT
> > is a gathering of tech-side developers & brand creativity professionals.
> > Meet the minds behind Google Creative Lab, Visual Complexity, Processing,
> > & iPhoneDevCamp as they present alongside digital heavyweights like
> > Barbarian Group, R/GA, & Big Spaceship.
> > http://p.sf.net/sfu/creativitycat-com
> > _______________________________________________
> > Bacula-devel mailing list
> > Bac...@li...
> > https://lists.sourceforge.net/lists/listinfo/bacula-devel
|