List:General Discussion« Previous MessageNext Message »
From:Chris Nolan Date:December 15 2003 11:52pm
Subject:Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
View as plain text  
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
>

Thread
MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Jerry Apfelbaum14 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Sven Köhler15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Sven Köhler15 Dec
        • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
          • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Tobias Asplund15 Dec
            • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Jeremy Zawodny16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?David Garamond16 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Jeremy Zawodny16 Dec
        • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Sven Köhler15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Juergen Sauer15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Curtis Maurand15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
        • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield16 Dec
          • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Vinod Kumar Singh16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Dr. Frank Ullrich16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?David Griffiths16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?David Griffiths16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan17 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?mos16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield17 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Ed Leafe17 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Director General: NEFACOMP7 Jan
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies7 Jan
RE: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Joshua Thomas15 Dec
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?mos17 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies17 Dec
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?mos18 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies18 Dec