RE: [Bacula-users] Migration from SQLite to MySQL.

Bennett, Silas (GE Indust, Security) Fri, 14 Apr 2006 20:31:06 -0700

Hi Kern,

I don't have enough knowledge of the intricacies of SQLite or MySQL to propose a proper migration solution. But the docs should be amended so as not to imply that it is trivial. The following (Inline & Attached) should work for people running 1.36.2. It should, if not just work, give people running other versions a point in the right direction.

Cheers,

Silas Bennett

=0)

From: http://www.bacula.org/rel-manual/Catalog_Maintenance.html#SECTION000239000000000000000

ORIGINAL:

Migrating from SQLite to MySQL</A>

</H2>

You may begin using Bacula with SQLite then later find that you want to switch

to MySQL for any of a number of reasons: SQLite tends to use more disk than

MySQL, SQLite apparently does not handle database sizes greater than 2GBytes,

... Several users have done so by first producing an ASCII "dump" of the

SQLite database, then creating the MySQL tables with the create_mysql_tables script that comes with Bacula, and finally feeding the

SQLite dump into MySQL using the -f command line option to continue past

the errors that are generated by the DDL statements that SQLite's dump

creates. Of course, you could edit the dump and remove the offending

statements. Otherwise, MySQL accepts the SQL produced by SQLite.

MODIFIED:

<H2><A NAME="SECTION000239000000000000000">
Migrating from SQLite to MySQL</A>
</H2>
<A NAME="13020"></A>
<A NAME="13021"></A>
<A NAME="13024"></A>

You may begin using Bacula with SQLite then later find that you want to switch
to MySQL for any of a number of reasons: SQLite tends to use more disk than
MySQL, SQLite apparently does not handle database sizes greater than 2GBytes,
... Several users have done so by the following proceduer:
 
 
&nbsp 1.) First Use the scripts distributed with bacula to create the MySQL database, and tables. 
&nbsp 2.) Next, produce an ASCII "dump" of the SQLite database using the following: 
<blockquote>
echo ".dump" | /usr/bin/sqlite bacula.db > bacula-sqlite.sql 
</blockquote>
&nbsp 3.) SQLite will produce some SQL that MySQL will not interpret correctly. The following
<a href="" will fix most
of these issues, but it might need to be modified slightly for use with other versions of bacula: 
 &#60code&#62<blockquote>
#! /bin/sh 
# 
# bacula-sqlite_2_mysqldump.sh 
# 
# Convert a Bacula 1.36.2 Sqlite database to MySQL 
# Originally Written by Nic Bellamy <[EMAIL PROTECTED]>, Sept/Oct 2003. 
# Modified by Silas Bennett <silas.bennett_AT_ge.com>, April 2006 for use with Bacula 1.36.2 
# 
 
if [ $1 == '-h' ] || [ $1 == '--help' ] ; then 
&nbsp&nbsp&nbsp&nbsp echo `basename "$0"`" Usage:" 
&nbsp&nbsp&nbsp&nbsp echo " "`basename $0`" takes a ASCII bacula sqlite database dump as an argument," 
&nbsp&nbsp&nbsp&nbsp echo " and writes an SQL dump suitable for use with MySQL to STDOUT." 
&nbsp&nbsp&nbsp&nbsp echo 
&nbsp&nbsp&nbsp&nbsp echo " Example Use: "`basename $0`" bacula.sqlite.sql > bacula.mysql.sql" 
&nbsp&nbsp&nbsp&nbsp echo " Example Use: "cat bacula.sqlite.sql | `basename $0`" - | mysql -p -u <user> baculadb" 
&nbsp&nbsp&nbsp&nbsp exit 
fi 
 
# If $1 is '-' then cat will read /dev/stdin 
cat $1 | 
awk '/^INSERT INTO / && $3 != "NextId" && $3 != "Version" { print $0 }' | 
sed '/^INSERT INTO [a-zA-Z]* VALUES(/s/(NULL)/(0)/g ; /^INSERT INTO [a-zA-Z]* VALUES(/s/(NULL,/(0,/g ; /^INSERT INTO [a-zA-Z]* VALUES(/s/,NULL,/,0,/g ; /^INSERT INTO [a-zA-Z]* VALUES(/s/,NULL,/,0,/g ; /^INSERT INTO [a-zA-Z]* VALUES(/s/,NULL)/,0)/g' 
</blockquote>&#60/code&#62 
&nbsp 4.) Next feed the SQL dump to MySQL: 
<blockquote>
mysql -p -u root baculadb< bacula.mysql.sql 
</blockquote>
Note you could do steps 2-4 in one shot using pipes. But if you are not using bacula 1.36.2 it would be a good idea to do this in steps storing each step to file.
The reason for this is that if your database is large the dumps, and conversions will take quite a while, and if you need to make modifications to the conversion script
then not having to do the sqlite dump repeatedly is conveniant. If you would like to do it all in one shot use the following oneliner: 
<blockquote>
echo ".dump" | /usr/bin/sqlite bacula.db | bacula-sqlite_2_mysqldump.sh - | mysql -p -u <user> baculadb 
</blockquote>

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Bennett, Silas (GE Indust, Security)
Sent: Thursday, April 13, 2006 7:03 PM
To: bacula-users@lists.sourceforge.net
Subject: [Bacula-users] Migration from SQLite to MySQL.

Hi All,

I just migrated from SQLite to MySQL with my Bacula setup. And I thought you might value some end user feedback. Things to note, the docmentation is a bit deficient (actually missleading) concerning such migrations. It might be a good Idea to update the Documentation to avoid leading people astray. From the Documentation:

<blockquote>

You may begin using Bacula with SQLite then later find that you want to switch to MySQL for any of a number of reasons: SQLite tends to use more disk than MySQL, SQLite apparently does not handle database sizes greater than 2GBytes, ... Several users have done so by first producing an ASCII "dump" of the SQLite database, then creating the MySQL tables with the create_mysql_tables script that comes with Bacula, and finally feeding the SQLite dump into MySQL using the -f command line option to continue past the errors that are generated by the DDL statements that SQLite's dump creates. Of course, you could edit the dump and remove the offending statements. Otherwise, MySQL accepts the SQL produced by SQLite.

</blockquote>

Following these directions resulted in a MySQL database in which PoolId's were incorrect and several other inconsistancies. I then found the following message in the mailing list archives.

http://article.gmane.org/gmane.comp.bacula.user/2142/match=bacula+sqlite+mysql+script

which had the following script attatched to it:

<shellscript>

#! /bin/sh
#
# Convert a Bacula 1.31a (1.32 should also be ok) Sqlite database to MySQL
#
# The MySQL database MUST be setup with the proper schema first.
#
# Written by Nic Bellamy <[EMAIL PROTECTED]>, Sept/Oct 2003.

echo .dump |
sqlite bacula.db |
awk '/^INSERT INTO / && $3 != "NextId" && $3 != "Version" { print $0 }' |
sed 's/^INSERT INTO Media /INSERT INTO Media (MediaId,VolumeName,Slot,PoolId,MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,VolCapacityBytes,VolStatus,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes) /; /INSERT INTO File VALUES$.*,NULL$/d' |
mysql -f bacula

</shellscript>

This script did a better job, but failed to insert my volumes properly. The SQL statements produced where:

INSERT INTO Media ('Bunch of Field Names') VALUES('Bunch of Values');

The problem was that there were more values in VALUES('Bunch of Values') then there were Field Names specified. Deleting the Field specifications and just doing the following fixed that problem:

INSERT INTO Media VALUES('Bunch of Values');

Also Restore Jobs were not inserted because both the PoolId and the FileSetId were 'NULL' and they needed to be '0' as the MySQL tables disallowed null values. So the SQL had

to be edited to replace the 'NULL' Values.

So clearly the above script needs some work, but it is servicable, and there should be some reference to guide people in the documentation.

Cheers,

Silas Bennett

=0)

bacula-sqlite_2_mysqldump.sh
Description: bacula-sqlite_2_mysqldump.sh

Catalog_Maintenance.html.diff
Description: Catalog_Maintenance.html.diff

RE: [Bacula-users] Migration from SQLite to MySQL.

Reply via email to