Hi,
Regarding backups, mysqlhotcopy locks all MyISAM tables named for backup
before copying the files. As a result, they are in a consistant state.
InnoDB and BDB tables need to be handled differently though, so you're
looking at 1 of 4 methods:
1. Shut the database down and copy the table space and logs.
2. Use mysqldump
3. Get a copy of InnoDB Hot Backup for InnoDB and MyISAM
4. Set up replication and shutdown the slave when you need to do a
backup and use method 1 on it.
Regarding mysqldump, it handles binary data through escaping the
required characters. pg_dump is similar if memory serves me correctly.
Regards,
Chris
Matthew Stanfield wrote:
> Hi,
>
> I've used both PostgreSQL and MySQL on a Linux server and found both
> setting up (using RPM) and maintaining them very easy (MySQL was
> slightly easier to set up but I set it up after I was already
> proficient on PostgreSQL, so perhaps comparing the ease of setup is
> unfair). Both are well documented and each have very active mailing
> lists where list subscribers are helpful and quick to respond. I have
> no idea how the commercial support services compare in quality and price.
>
> I have not used the more 'advanced' features, that you mentioned, of
> either (such as: 'Triggers, Stored Procedures, User-Defined
> Functions') so can't comment on them.
>
> Backups on both are straightforward using pg_dump and mysqldump. Both
> of these allow you to dump databases as files containing the relevant
> sql commands to recreate the entire database quickly and easily.
>
> Another responder mentioned a possible problem dumping MySQL databases
> containing binary data, I have no idea whether this is an issue with
> pg_dump as well, as I have no tables at all with binary data, but
> suspect dumping these kind of backup files is generally inconsistent
> with binary data (unless the dumping utilities do something clever,
> which I don't know about, like uuencoding binary data). Anyway both
> servers can be backed up by copying the actual database table files
> (on the local linux filesystem). To achieve this the PostgreSQL server
> must be shut down (making it an inferior backup technique to dumping
> which does not require a shutdown). MySQL, however, has something
> called 'mysqlhotcopy' which will lock and flush tables and copy the
> files using 'cp' and does not require a server shutdown, it allows
> queries by different threads to continue, blind to the backup in
> progress. --I must admit to being a bit wary of this as I don't know
> enough about the underlying file systems and so personally have no
> intention of using mysqlhotcopy. --Perhaps someone that knows more can
> explain whether this is always safe to use and why??
>
> Both PostgreSQL and MySQL have free GUI frontends (pgAdmin and Control
> Center, respectively) that I have used on Windows 2000; both have been
> adequate for my basic needs, EG. Quick database and table creation and
> deletion, sql commands, and basic administration such as vacuuming
> (PostgreSQL) and optimizing (MySQL). Of these PostgeSQL's is better
> (far more features), while MySQL's is still at 0.9.3 beta (stable but
> feature poor).
>
> My use of both has been low scale and is client program orientated
> using .net and C# from Win2000. The ODBC drivers for Windows both
> function well. The only annoying thing I can think of, from a
> programming perspective, is MySQL's lack of a Boolean type - the
> manual says use TINYINT(1) which works fine but is slightly annoying
> because of the extra type conversion needed every time you use it.
> Apparently MySQL will be implementing the Boolean type soon in
> accordance with whatever SQL standard requires it. Quite why it still
> has not been implemented, even though MySQL is into version 4, I have
> no idea - as a programmer I find this a staggering omission but
> presumably they have their reasons and perhaps most people are happy
> with TINYINT(1), but for clarity of code TINYINT(1) is inferior to a
> Boolean type.
>
> I hope this helps,
>
> ..matthew
>
>
> Jerry Apfelbaum wrote:
>
>> Hello.
>>
>> I have been tasked with evaluating open source databases for a large
>> upcoming project: e-commerce, B2B, high availability.
>>
>> The O/S is most likely to be Linux, although FreeBSD could possibly
>> be used
>> (lower probability).
>>
>> So far, it seems that MySQL, MaxDB, PostgreSQL, and
>> Interbase/Firebird are
>> possible candidates.
>>
>> Does anyone know why we should or should not use any of these? Does
>> anyone
>> know of other possibilities?
>>
>> I’d very much appreciate hearing your comments and recommendations.
>>
>> I have only recently started these evaluations. BTW, my own
>> background is
>> from the Oracle DBA world.
>>
>> MySQL is certainly popular and seems to have very good performance,
>> but I am
>> concerned that the lack of Triggers, Stored Procedures, User-Defined
>> Functions, and Views (to a lesser degree ) will be a disadvantage.
>>
>> MaxDB "appears" to be more feature-rich and possibly more
>> industrial-strength. How does its performance and stability compare
>> to the
>> others?
>>
>> Many Thanks.
>> Jerry Apfelbaum
>> Toronto
>>
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
