Hello,
Today i tried the bpipe plugin + mysqldump at home.
Ran into same problem like at work,
mysqldump: Error 2013: Lost connection to MySQL server during query when
dumping table `File` at row: ...
Then i tried without lock tables, here is the FileSet:
FileSet {
Name = CatalogPipe
Ignore FileSet Changes = yes
Include {
Options {
signature = MD5
}
Plugin = "bpipe:/MYSQL/bacula.sql:mysqldump -f --opt --skip-lock-tables
--user=bacula --password=xxxxxx bacula 2>/tmp/mysqldump.err:gzip >
/var/tmp/bacula.sql.gz"
}
}
Backup and Restore completed without problems.
Restore file /var/tmp/bacula.sql.gz contains all bacula tables.
It looks like there is a table lock problem which causes the
mysqldump Error 2013: Lost connection...
So what makes the difference running mysqldump as RunBeforeJob
(like default BackupCatalog) or running it using bpipe plugin?
Thanks
Ulrich
PS: I like the bpipe plugin because i don't need large temporary
space to hold bacula database dump, dump goes direct into file
or tape storage.
On Thu, 2009-05-28 at 12:40 +0200, Kern Sibbald wrote:
> 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.
> > >
> > >
> > > [r...@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_zo...@old_time_zone */;
> > > > > > >
> > > > > > > /*!40101 SET sql_mo...@old_sql_mode */;
> > > > > > > /*!40014 SET foreign_key_chec...@old_foreign_key_checks */;
> > > > > > > /*!40014 SET unique_chec...@old_unique_checks */;
> > > > > > > /*!40101 SET character_set_clie...@old_character_set_client */;
> > > > > > > /*!40101 SET character_set_resul...@old_character_set_results */;
> > > > > > > /*!40101 SET collation_connecti...@old_collation_connection */;
> > > > > > > /*!40111 SET sql_not...@old_sql_notes */;
> > > > > > >
> > > > > > > -- Dump completed on 2009-05-27 10:56:00
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > It looks like mysqldump called from bpipe terminates/stops
> > > > > > > while dumping File.
> > > > > > >
> > > > > > > [r...@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` (
> > > > > > > [r...@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
> > > [email protected]
> > > https://lists.sourceforge.net/lists/listinfo/bacula-devel
>
>
>
------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
Bacula-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
