Discussion:
mysqldump with single-transaction option.
geetanjali mehra
2014-09-23 11:14:00 UTC
Permalink
Can anybody please mention the internals that works when we use mysqldump
as follows:


*mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql*

MySQL manual says:

This backup operation acquires a global read lock on all tables at the
beginning of the dump (using *FLUSH TABLES WITH READ LOCK
<http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as this lock
has been acquired, the binary log coordinates are read and the lock is
released. If long updating statements are running when the FLUSH
<http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is issued,
the backup operation may stall until those statements finish. After that,
the dump becomes lock-free and does not disturb reads and writes on the
tables.

Can anyone explain it more? Please.



Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
shawn l.green
2014-10-07 00:52:56 UTC
Permalink
Hello Geetanjali,
Post by geetanjali mehra
Can anybody please mention the internals that works when we use mysqldump
*mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql*
This backup operation acquires a global read lock on all tables at the
beginning of the dump (using *FLUSH TABLES WITH READ LOCK
<http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as this lock
has been acquired, the binary log coordinates are read and the lock is
released. If long updating statements are running when the FLUSH
<http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is issued,
the backup operation may stall until those statements finish. After that,
the dump becomes lock-free and does not disturb reads and writes on the
tables.
Can anyone explain it more? Please.
Which part would you like to address first?

I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works
but I want to be certain before answering.

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
geetanjali mehra
2014-10-07 03:52:45 UTC
Permalink
It seems to me that once the read lock is acquired, only the binary log
coordinates are read. Soon after binary log coordinates are read, lock is
released. Is there anything else that happens here?

It means that after lock is released, dump is made while the read and write
activity is going on. This dump then, would be inconsistent. So, to make
this dump a consistent one when restoring it, binary log will be applied
starting from the binary log coordinates that has been read earlier.

This is what I understand. Please correct me if my understanding is wrong.

Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
Post by shawn l.green
Hello Geetanjali,
Post by geetanjali mehra
Can anybody please mention the internals that works when we use mysqldump
*mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql*
This backup operation acquires a global read lock on all tables at the
beginning of the dump (using *FLUSH TABLES WITH READ LOCK
<http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as this lock
has been acquired, the binary log coordinates are read and the lock is
released. If long updating statements are running when the FLUSH
<http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is issued,
the backup operation may stall until those statements finish. After that,
the dump becomes lock-free and does not disturb reads and writes on the
tables.
Can anyone explain it more? Please.
Which part would you like to address first?
I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but
I want to be certain before answering.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
yoku ts.
2014-10-07 06:25:04 UTC
Permalink
Hello,

If you use any *NOT InnoDB* storage engine, you're right.
mysqldump with --single-transaction doesn't have any consistent as you say.

If you use InnoDB all databases and tables, your dumping process is
protected by transaction isolation level REPEATABLE-READ.

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction

Regards,
Post by geetanjali mehra
It seems to me that once the read lock is acquired, only the binary log
coordinates are read. Soon after binary log coordinates are read, lock is
released. Is there anything else that happens here?
It means that after lock is released, dump is made while the read and write
activity is going on. This dump then, would be inconsistent. So, to make
this dump a consistent one when restoring it, binary log will be applied
starting from the binary log coordinates that has been read earlier.
This is what I understand. Please correct me if my understanding is wrong.
Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
Post by shawn l.green
Hello Geetanjali,
Post by geetanjali mehra
Can anybody please mention the internals that works when we use
mysqldump
Post by shawn l.green
Post by geetanjali mehra
*mysqldump --single-transaction --all-databases >
backup_sunday_1_PM.sql*
Post by shawn l.green
Post by geetanjali mehra
This backup operation acquires a global read lock on all tables at the
beginning of the dump (using *FLUSH TABLES WITH READ LOCK
<http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as this lock
has been acquired, the binary log coordinates are read and the lock is
released. If long updating statements are running when the FLUSH
<http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is
issued,
Post by shawn l.green
Post by geetanjali mehra
the backup operation may stall until those statements finish. After
that,
Post by shawn l.green
Post by geetanjali mehra
the dump becomes lock-free and does not disturb reads and writes on the
tables.
Can anyone explain it more? Please.
Which part would you like to address first?
I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works
but
Post by shawn l.green
I want to be certain before answering.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
geetanjali mehra
2014-10-07 06:44:51 UTC
Permalink
So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump be
of any useful?

Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
Post by yoku ts.
Hello,
If you use any *NOT InnoDB* storage engine, you're right.
mysqldump with --single-transaction doesn't have any consistent as you say.
If you use InnoDB all databases and tables, your dumping process is
protected by transaction isolation level REPEATABLE-READ.
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction
Regards,
Post by geetanjali mehra
It seems to me that once the read lock is acquired, only the binary log
coordinates are read. Soon after binary log coordinates are read, lock is
released. Is there anything else that happens here?
It means that after lock is released, dump is made while the read and write
activity is going on. This dump then, would be inconsistent. So, to make
this dump a consistent one when restoring it, binary log will be applied
starting from the binary log coordinates that has been read earlier.
This is what I understand. Please correct me if my understanding is wrong.
Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
Post by shawn l.green
Hello Geetanjali,
Post by geetanjali mehra
Can anybody please mention the internals that works when we use
mysqldump
Post by shawn l.green
Post by geetanjali mehra
*mysqldump --single-transaction --all-databases >
backup_sunday_1_PM.sql*
Post by shawn l.green
Post by geetanjali mehra
This backup operation acquires a global read lock on all tables at the
beginning of the dump (using *FLUSH TABLES WITH READ LOCK
<http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as this lock
has been acquired, the binary log coordinates are read and the lock is
released. If long updating statements are running when the FLUSH
<http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is
issued,
Post by shawn l.green
Post by geetanjali mehra
the backup operation may stall until those statements finish. After
that,
Post by shawn l.green
Post by geetanjali mehra
the dump becomes lock-free and does not disturb reads and writes on the
tables.
Can anyone explain it more? Please.
Which part would you like to address first?
I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works
but
Post by shawn l.green
I want to be certain before answering.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
yoku ts.
2014-10-07 07:35:41 UTC
Permalink
Maybe no, as you knew.
Post by geetanjali mehra
It means that after lock is released, dump is made while the read and write
activity is going on. This dump then, would be inconsistent.
Not only binary logs, each tables in your dump is based the time when
mysqldump began to dump *each* tables.
It means, for example, table1 in your dump is based "2014-10-07 00:00:00",
and next table2 is based "2014-10-07 00:00:01", and next table3 is ..

I don't have a motivation for restoring its consistency..


Regards,
Post by geetanjali mehra
So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump be
of any useful?
Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
Post by yoku ts.
Hello,
If you use any *NOT InnoDB* storage engine, you're right.
mysqldump with --single-transaction doesn't have any consistent as you
say.
Post by yoku ts.
If you use InnoDB all databases and tables, your dumping process is
protected by transaction isolation level REPEATABLE-READ.
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction
Post by yoku ts.
Regards,
Post by geetanjali mehra
It seems to me that once the read lock is acquired, only the binary log
coordinates are read. Soon after binary log coordinates are read, lock
is
Post by yoku ts.
Post by geetanjali mehra
released. Is there anything else that happens here?
It means that after lock is released, dump is made while the read and write
activity is going on. This dump then, would be inconsistent. So, to make
this dump a consistent one when restoring it, binary log will be applied
starting from the binary log coordinates that has been read earlier.
This is what I understand. Please correct me if my understanding is
wrong.
Post by yoku ts.
Post by geetanjali mehra
Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
Post by shawn l.green
Hello Geetanjali,
Post by geetanjali mehra
Can anybody please mention the internals that works when we use
mysqldump
Post by shawn l.green
Post by geetanjali mehra
*mysqldump --single-transaction --all-databases >
backup_sunday_1_PM.sql*
Post by shawn l.green
Post by geetanjali mehra
This backup operation acquires a global read lock on all tables at
the
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
beginning of the dump (using *FLUSH TABLES WITH READ LOCK
<http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as
this
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
lock
has been acquired, the binary log coordinates are read and the lock
is
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
released. If long updating statements are running when the FLUSH
<http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is
issued,
Post by shawn l.green
Post by geetanjali mehra
the backup operation may stall until those statements finish. After
that,
Post by shawn l.green
Post by geetanjali mehra
the dump becomes lock-free and does not disturb reads and writes on
the
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
tables.
Can anyone explain it more? Please.
Which part would you like to address first?
I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works
but
Post by shawn l.green
I want to be certain before answering.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Trianon 33
2014-10-07 12:24:05 UTC
Permalink
All,

Normallu just lurking, but as a newbie with MYSQL looking for a
direction for this issue:

I have a table with a filed called year, integre, 5 positions. I have
another field in the same table called yearanddate, which has a date format.

Values from yearanddate look like this: 2013-12-11 00:00:00. I want to
copy the 2013 and put that into the yearfield, for each record.

Can that be done by just using SQL statements?

Alternatively can this be done by means of PHPMYADMIN?

Thanks for pointing me,

Best regards, Hans.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Johan De Meersman
2014-10-07 14:10:16 UTC
Permalink
----- Original Message -----
Subject: Need a short directive
Values from yearanddate look like this: 2013-12-11 00:00:00. I want to
That's only a display format; internally it's an integer (well, presumably a struct time_t) counting the seconds since epoch. Not especially relevant except to say that, since it's only an output format, it can easily be changed.
copy the 2013 and put that into the yearfield, for each record.
Can that be done by just using SQL statements?
I believe the year() function is pretty much what you're looking for; complexer things can be handled through date_format().

Something along the lines of

UPDATE table
SET year = year(yearanddate);

should do nicely; I'm sure you can fix up a where clause as appropriate.
--
Unhappiness is discouraged and will be corrected with kitten pictures.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Trianon 33
2014-10-07 15:49:03 UTC
Permalink
Thanks, later on I'm going to try this, will post results here.

Bye, Hans.
Post by Johan De Meersman
----- Original Message -----
Subject: Need a short directive
Values from yearanddate look like this: 2013-12-11 00:00:00. I want to
That's only a display format; internally it's an integer (well, presumably a struct time_t) counting the seconds since epoch. Not especially relevant except to say that, since it's only an output format, it can easily be changed.
copy the 2013 and put that into the yearfield, for each record.
Can that be done by just using SQL statements?
I believe the year() function is pretty much what you're looking for; complexer things can be handled through date_format().
Something along the lines of
UPDATE table
SET year = year(yearanddate);
should do nicely; I'm sure you can fix up a where clause as appropriate.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Trianon 33
2014-10-08 17:12:29 UTC
Permalink
A big thanks to Johan, who pointed me in the right direction.

I had indeed to fix a where clause, but in the end al went very well.

BR, hans.
Post by Johan De Meersman
----- Original Message -----
Subject: Need a short directive
Values from yearanddate look like this: 2013-12-11 00:00:00. I want to
That's only a display format; internally it's an integer (well, presumably a struct time_t) counting the seconds since epoch. Not especially relevant except to say that, since it's only an output format, it can easily be changed.
copy the 2013 and put that into the yearfield, for each record.
Can that be done by just using SQL statements?
I believe the year() function is pretty much what you're looking for; complexer things can be handled through date_format().
Something along the lines of
UPDATE table
SET year = year(yearanddate);
should do nicely; I'm sure you can fix up a where clause as appropriate.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Andrew Moore
2014-10-08 11:12:29 UTC
Permalink
We will tend to use binary backups (Xtrabackup) for full consistent dataset
restore (think slave provisioning and disaster recovery) and logical
backups to perform single table restores in the event that a rollback may
need to occur if someone drops a table or carries out an insane update. We
will also use mydumper instead of mysqldump due to the features of
compression and encryption. Mysqldump stops being useful on full|large
datasets due to it's single-threaded-ness.
Post by yoku ts.
Maybe no, as you knew.
Post by geetanjali mehra
It means that after lock is released, dump is made while the read and
write
Post by geetanjali mehra
activity is going on. This dump then, would be inconsistent.
Not only binary logs, each tables in your dump is based the time when
mysqldump began to dump *each* tables.
It means, for example, table1 in your dump is based "2014-10-07 00:00:00",
and next table2 is based "2014-10-07 00:00:01", and next table3 is ..
I don't have a motivation for restoring its consistency..
Regards,
Post by geetanjali mehra
So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump
be
Post by geetanjali mehra
of any useful?
Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
Post by yoku ts.
Hello,
If you use any *NOT InnoDB* storage engine, you're right.
mysqldump with --single-transaction doesn't have any consistent as you
say.
Post by yoku ts.
If you use InnoDB all databases and tables, your dumping process is
protected by transaction isolation level REPEATABLE-READ.
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction
Post by geetanjali mehra
Post by yoku ts.
Regards,
2014-10-07 12:52 GMT+09:00 geetanjali mehra <
Post by geetanjali mehra
It seems to me that once the read lock is acquired, only the binary
log
Post by geetanjali mehra
Post by yoku ts.
Post by geetanjali mehra
coordinates are read. Soon after binary log coordinates are read, lock
is
Post by yoku ts.
Post by geetanjali mehra
released. Is there anything else that happens here?
It means that after lock is released, dump is made while the read and write
activity is going on. This dump then, would be inconsistent. So, to make
this dump a consistent one when restoring it, binary log will be
applied
Post by geetanjali mehra
Post by yoku ts.
Post by geetanjali mehra
starting from the binary log coordinates that has been read earlier.
This is what I understand. Please correct me if my understanding is
wrong.
Post by yoku ts.
Post by geetanjali mehra
Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Consultant and Database Security
Specialist
On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green <
Post by shawn l.green
Hello Geetanjali,
Post by geetanjali mehra
Can anybody please mention the internals that works when we use
mysqldump
Post by shawn l.green
Post by geetanjali mehra
*mysqldump --single-transaction --all-databases >
backup_sunday_1_PM.sql*
Post by shawn l.green
Post by geetanjali mehra
This backup operation acquires a global read lock on all tables at
the
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
beginning of the dump (using *FLUSH TABLES WITH READ LOCK
<http://dev.mysql.com/doc/refman/5.6/en/flush.html>*). As soon as
this
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
lock
has been acquired, the binary log coordinates are read and the lock
is
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
released. If long updating statements are running when the FLUSH
<http://dev.mysql.com/doc/refman/5.6/en/flush.html> statement is
issued,
Post by shawn l.green
Post by geetanjali mehra
the backup operation may stall until those statements finish. After
that,
Post by shawn l.green
Post by geetanjali mehra
the dump becomes lock-free and does not disturb reads and writes on
the
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Post by geetanjali mehra
tables.
Can anyone explain it more? Please.
Which part would you like to address first?
I have a feeling it's more about how FLUSH TABLES WITH READ LOCK
works
Post by geetanjali mehra
Post by yoku ts.
Post by geetanjali mehra
but
Post by shawn l.green
I want to be certain before answering.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work
Together.
Post by geetanjali mehra
Post by yoku ts.
Post by geetanjali mehra
Post by shawn l.green
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Loading...