Groups | Search | Server Info | Login | Register

Groups > comp.databases.mysql > #682

Re: Can MySql database store images?

From The Natural Philosopher <tnp@invalid.invalid>
Newsgroups comp.databases.mysql
Subject Re: Can MySql database store images?
Date 2011-04-25 18:10 +0100
Organization albasani.net
Message-ID <ip49v4$e6p$1@news.albasani.net> (permalink)
References (8 earlier) <kikf88-1rv.ln1@squidward.dionic.net> <ip3ijr$f0q$1@dont-email.me> <a31g88-6ii.ln1@squidward.dionic.net> <ip3m17$39q$1@news.albasani.net> <8q7g88-dqd.ln1@squidward.dionic.net>

Show all headers | View raw

Tim Watts wrote:
> The Natural Philosopher wrote:
> 
>> Tim Watts wrote:
>>
>>> I suggest you top talking now and "man rsync". It does sync filesystems.
>>> It also has the option to backup (divert to another directory tree) any
>>> file that it is about to delete or update on the target. There are your
>>> incrementals. I've been running that method for 10 years - it works.
>>>
>> I can only talk from personal experience. I have been rsyncing an ISAM
>> based mySQL system containing images over a very slow connection.
>>
>>
>> I assumed at a first that if I added an image,, the whole table would be
>> rsynced, but this is not the case.
>>
>> It seems to be smart enough to send the changes only.
>>
>> I also expected that simply copyingh  a live system would result in a
>> pretty inconsistent database. It doesn't. I can dump the files into a
>> mysql directory structure and recreate the original database with 100%
>> accuracy.
>>
>> This is NOT a highly trafficed site, so its obviously  a function of
>> that, but it does suggest that rsync and mysql are better designed than
>> certain people give them credit for.
> 
> So you're doing a hot copy type backup? That is valid provided you can lock 
> the tables correctly. That's quite dependant on which engine is used in 
> MySQL isn't it? In PostgreSQL it wouldn't be valid to do that so you'd have 
> to do a full dump[1]. Having just checked, it is a suitable method for 
> MyISAM - but what would you do if using InnoDB?

Don't even lock. The backup is done for pure total loss purposes..at 5 
a.m. when only the odd Australian is online and they are read only..the 
data input is all in the working day.

ISAM yes, wouldnt expect innodB to work properly.

Not recommending this, merely saying its got me out of trouble twice 
when some twonker deleted a record and I was gratified to find the 
database came up on a second machine totally intact from the time of 
last sync.

And obviously not a way to mange a serious large trafficked site in an 
industrial strength env. either. But for small semi private and SOHO 
type apps it works very well and much better than expected.




> 
> [1] Admittedly you can play about with transferring WAL journals as an 
> incremental method, but from time to time you really need to get a fresh 
> full dump. Or you could use full DB replication if your system/version 
> supports it.
> 
> My argument here is, unless you really *need* to pull off having a massive 
> DB, why not keep it small and simpler if sticking binary lumps into a 
> filessystem makes for a smaller more efficient DB, quicker backups, quicker 
> restore and maintenance operations and so on?
> 

How massive is massive? I've probably got under 500M of images. Is that 
small, medium or large?


> Back to the case in point, the photos database:
> 
> If you have a disaster recovery scenario - you could have a DB restore of 
> metadata done fairly fast and the site back up. If you also needed to 
> restore the filesystem with the actual photos, this could be run afterwards 
> and the sites would gradually come back on by one. If you shove all the 
> photos into the database, you won't have anything back until the entire 
> restore is complete, unless you break the backup into 2 segments - one with 
> all the tables and metadata *except* the photos table, and another one for 
> the photos table.
> 
> I have never argued that a DB (MySQL or anything else) cannot handle photo 
> data, but I'm arguing "just because you can do it, doesn't mean you should".
> 
> I take a holistic approach as I'm the one running the servers and datastores 
> and backups - which means if backing up a heavily laden DB server is going 
> to take hours or days and thus require special handling, there had better be 
> a good reason it needs to. The whim of a DB application programmer wouldn't 
> be good enough unless they could demonstrate a technical reason for it.
> 
I think that last paragraph says it all. Until you have holistically 
examined all the issues, you cant say whether its the best approach or not.

I did take it, because it simplified a lot of stuff. I had an inherent 
relation between the images, their metadata and the other data in the DB 
without having to manage it explicitly through coding. It also allowed 
me to use the same security methodology to allow access to it or not. 
That is a problem with a flat file system. You MUST incorporate some 
.htaccess or a script  if you want to control image access.

Some of the data is peoples copyrighted design work as well as images. 
Use of a script to disseminate it means I can check their security 
credentials.

This  is not a problem everyone has, but it does make the point that you 
can fine tune access to data better with a script and SQL DB than raw 
files on a web server. And the more general point that there is no 
'right' answer, only one that is the optimal solution for the exact 
circumstances that obtain.

Back to comp.databases.mysql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-23 23:47 -0700
  Re: Can MySql database store images? "J.O. Aho" <user@example.net> - 2011-04-24 09:23 +0200
    Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 01:19 -0700
      Re: Can MySql database store images? "J.O. Aho" <user@example.net> - 2011-04-24 11:13 +0200
  Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-24 11:17 +0100
  Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 13:06 +0100
  Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 08:42 -0400
    Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 14:45 +0100
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 10:27 -0400
        Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 19:04 +0100
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:44 -0400
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 20:51 +0100
              Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:03 -0400
                Re: Can MySql database store images? Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2011-04-24 23:18 +0200
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 19:55 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:30 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:32 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 12:04 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:18 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:32 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:09 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:17 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:29 -0400
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:30 +0100
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:59 +0100
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 18:10 +0100
        Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-24 16:40 -0400
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:05 -0400
            Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-25 02:57 -0400
              Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:59 +0100
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-25 21:41 -0400
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-26 06:20 -0400
                Re: Can MySql database store images? dougatmilmacdotcom@example.com (Doug Miller) - 2011-04-26 14:10 +0000
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-26 22:24 -0400
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-27 08:24 +0100
                Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-27 06:41 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 12:32 +0100
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-27 15:07 +0100
                Re: Can MySql database store images? "Peter H. Coffin" <hellsop@ninehells.com> - 2011-04-27 07:11 -0500
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 13:34 +0100
                Re: Can MySql database store images? "Peter H. Coffin" <hellsop@ninehells.com> - 2011-04-27 09:25 -0500
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 16:32 +0100
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 10:28 -0400
        Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-24 16:43 -0400
          Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:36 +0100
            Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:34 -0400
              Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 11:50 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:20 -0400
          Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:42 +0100
    Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 16:27 +0200
      Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-24 15:38 +0100
        Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 16:41 +0200
        Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 19:07 +0100
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:46 -0400
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 21:01 +0100
              Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:10 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:23 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:39 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 12:00 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:27 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:42 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:12 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:23 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:31 -0400
                Re: Can MySql database store images? "Mr. B-o-B" <mr.chew.baka@gmail.com> - 2011-04-25 20:30 -0500
          Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:21 +0100
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:46 +0100
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 13:23 -0400
        Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 20:31 +0200
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:48 -0400
            Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 21:11 +0200
    Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-24 21:24 +0200
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:34 -0400
        Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 12:04 +0200
          Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:40 -0400
            Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 11:48 +0100
              Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:28 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:36 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:17 -0400
                Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:30 +0100
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 18:13 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:37 -0400
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:35 -0400
    Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 13:13 -0700
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:35 -0400
  Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-24 21:29 +0200
    Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 14:37 -0700
      Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 00:41 +0200
        Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:16 -0400
          Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 12:12 +0200
            Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:31 -0400
        Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:43 +0100
        Re: Can MySql database store images? Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2011-05-01 09:59 +0200
      Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:03 -0400
    Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:39 -0400
      Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 00:46 +0200
        Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:18 -0400
          Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 12:13 +0200
            Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:43 -0400
              Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 13:40 +0200
                Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:43 +0100
                Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:19 -0400
                Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 16:46 +0200

csiph-web