[mythtv-users] Database internals

[Paul Gardiner]

On 03/01/2014 11:35, Simon Hobson wrote:
> Paul Gardiner <lists at glidos.net> wrote:
>
>>> What the words "exact copy of the database" actually means to you is
>>> something that actually varies quite a lot.
>>>
>>> It can't mean "a binary copy of the internal files" -- aside from endianness,
>>> you *don't want to know* how much difference there can be amongst different
>>> DB engines storing the same data.
>>>
>>> In point of fact, *the reason SQL exists* is precisely to insulate users and
>>> programmers from that.
>>
>> Yeah, good point: "exact copy" clearly wasn't a good phrase. So let's
>> say a copy that cannot be distinguished from the original by any
>> sequence of updates and queries.
>
> Or put another way, what the user wants is "a backup which when restored will produce a database that, as far as the user and his programs are concerned, is indistinguishable from the original".
>
> Yes the internals WILL be different, and the tables will be more compact (no cruft from years of additions and deletions), but the external view (ie what the client programs can see) will be the same.
>
> As an analogy, we don't care where a file is physically stored on a disk. So if we restore a system, we don't care about the data blocks going back in the same place (binary copy), and we probably don't even care if it's the same type of filesystem, we only care that when we look in a directory we find the same files that we had before. So we could upgrade a disk (replace a small one with a big one), partition it with different tools, format the filesystem differently (eg ZFS instead of EXT3), and put the files back - the user (and the programs he runs) won't see any difference, but the underlying data structures will be very different.
> Similarly, we don't really care what database engine is running the show, what table types it uses, only that the API to our client programs matches and the data is presented in the same way.

Yes exactly. Nicely put. What still seems uncertain is whether
mysqldump can do that, and if so, whether it does so as default.