How to execute more than one binlog?

[bengt...@gmail.com]

Section 7.5.2 of the MySQL 5.5 manual warns for using the following procedure during restoration:

"...If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Processing binary logs this way using different connections to the server causes problems if the first log file contains a CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports “unknown table.” ..."

My understanding is that it is dangerous to invoke the second command before the first command has been terminated and that it should be safe to wait for the first command to finish execution of binlog...1 and thereafter invoke the following command to execute binlog...2. Is this correct or are there other concerns?

[Axel Schwenke]

bengt...@gmail.com wrote:
>
> "If you have more than one binary log to execute on the MySQL server,
> the safe method is to process them all using a single connection to
> the server."

[snip]

> My understanding is that it is dangerous to invoke the second command
> before the first command has been terminated and that it should be safe
> to wait for the first command to finish execution

No. This is not the problem. Not at all. Did you actually *read* the
quoted paragraph from the manual?

> Is this correct or are there other concerns?

The difference is "executing all binlogs in a single connection" vs.
"executing each binlog in a new connection" or to put it in other
words "keeping the database connection open between binlogs or not".

The problem is connection scoped state like local variables, temporary
tables etc. Closing the connection will destroy this data, but the next
binlog in the line may depend on it.


XL

[bengt...@gmail.com]

Ok, believe I understand.

Assume this scenario:

- Restore the database from backup file ("backup_1").
- Flush logs (in order to start a new binlog "binlog...i").
- Enable user access to the database for some time.
- Flush logs regularily (in order to start new binlogs "binlog...i+1" to "binlog...i+j").
- Disable user access to the database.
- Stop and start MySQL (in order to remove temporary tables...).
- Flush logs (in order to start a new binlog "binlog...i+j+1").
- Enable user access to the database for some time.
- Flush logs regularily (in order to start new binlogs "binlog...i+j+2" to "binlog...i+j+k").

Is it then correct to restore the database from "backup_1" and "binlog...i" + "binlog...i+1" + ... + "binlog...i+j", make a new backup ("backup_2") and continue the restoration from "backup_2" + "binlog...i+j+1 to ...?

[Axel Schwenke]

bengt...@gmail.com wrote:

> Assume this scenario:
>
> - Restore the database from backup file ("backup_1").
> - Flush logs (in order to start a new binlog "binlog...i").
> - Enable user access to the database for some time.
> - Flush logs regularily (in order to start new binlogs "binlog...i+1" to "b
> inlog...i+j").
> - Disable user access to the database.
> - Stop and start MySQL (in order to remove temporary tables...).

This last point is nonsense. When a user session ends, temporary tables
will be released automatically.

> Is it then correct to restore the database from "backup_1" and "binlog...i"
> + "binlog...i+1" + ... + "binlog...i+j"

It is still required that all those binlogs are applied using s single
database connection, because a temporary table might exist when FLUSH
LOGS is executed. And then the new binlog started may (or maybe not)
depend on the existence of that temporary table for future statements.

This isn't hard to understand, IMHO.


XL

[bengt...@gmail.com]

>
>
> This isn't hard to understand, IMHO.
>

If I can make sure that there is no ongoing database access when FLUSH LOGS is made, is it then true that there will be no temporary files and therefore no dependencies between binlogs?

[Axel Schwenke]

bengt...@gmail.com wrote:

> If I can make sure that there is no ongoing database access when
> FLUSH LOGS is made, is it then true that there will be no temporary
> files and therefore no dependencies between binlogs?

I don't see where this discussion could lead.

What is your problem to apply binlogs like so:

mysqlbinlog log.000001 log.000002 ... log.xxxxxx | mysql -u root -p

instead of the discouraged and potentially dangerous

mysqlbinlog log.000001 | mysql -u root -p
mysqlbinlog log.000002 | mysql -u root -p
...
mysqlbinlog log.xxxxxx | mysql -u root -p

?

It's not even a common operation.


XL

PS: please don't send your replies by mail. You risk to end in my
blacklist. This would be permanent.

[bengt...@gmail.com]

Den måndagen den 8:e april 2013 kl. 20:26:17 UTC+2 skrev Axel Schwenke:
> bengt...@gmail.com wrote:
>
>
>
> > If I can make sure that there is no ongoing database access when
>
> > FLUSH LOGS is made, is it then true that there will be no temporary
>
> > files and therefore no dependencies between binlogs?
>
>
>
> I don't see where this discussion could lead.
>

Please be patient with me. As you understand I am a newcomer in this area and wish to understand the detailas.

Please answer my question above.

[Kees Nuyt]

On Mon, 15 Apr 2013 01:20:05 -0700 (PDT), bengt...@gmail.com
wrote:

The point is not whether logs are flushed or not.

Essentially, client sessions can span log files, and you have to
make sure all log files are applied at once, in the same
mysqlbinlog run.

As Axel said, the safe way is:

mysqlbinlog log.000001 log.000002 ... log.xxxxxx|mysql -u root -p

The unsafe way:

mysqlbinlog log.000001|mysql -u root -p
mysqlbinlog log.000002|mysql -u root -p
...
mysqlbinlog log.xxxxxx|mysql -u root -p

For reasons Axel explained earlier.
--
Kees Nuyt

[Axel Schwenke]

bengt...@gmail.com wrote:
> Axel Schwenke:
>> bengt...@gmail.com wrote:
>>
>> > If I can make sure that there is no ongoing database access when
>> > FLUSH LOGS is made, is it then true that there will be no temporary
>> > files and therefore no dependencies between binlogs?
>>
>> I don't see where this discussion could lead.
>
> Please be patient with me. As you understand I am a newcomer in
> this area and wish to understand the detailas.
> Please answer my question above.

That would make no sense. The questions you are asking and also
how you are asking them, indicates that you are on a wrong way.

Why don't you describe your *original* problem? So far you are
showing us what you believe to be a solution and ask us if you
are right. But how can we know without knowing the problem first?

Binlogs don't care about user sessions. They don't have to. It
doesn't matter for the primary purpose of a binlog (replication)
This however has consequences for other uses of binlogs, like
PITR. The manual just states this and I repeated it in more detail.


If you are interested in details, then RTFM!

http://dev.mysql.com/doc/refman/5.6/en/binary-log.html
http://dev.mysql.com/doc/internals/en/binary-log.html


XL