Feature Request: Autocommit In Database Backup

[DonQuichote]

When making an SQL dump of a database, it would be nice to have an option that would cause the result script to start with

SET AUTOCOMMIT=0;

and to end with

SET AUTOCOMMIT=1;

(or with the old value of AUTOCOMMIT). This setting will make sure that not every statement has its own tiny transaction and will radically speed up the script when imported. The difference is a matter of hours becoming minutes.

I know that a simple copy command can add those statements to the dump, but the speed-up is too useful to deny it to other users of SQLyog.

[peterlaursen]

We have been doing this for the last 6-9 months already. Just use the option to 'generate sync script' and you will see. Doesn't it? What version are you using?

(and there is not need to SET AUTOCOMMIT=1 at the end because it is a SESSION variable and Data Sync runs in a seperate connection. But we do COMMIT.)

A very simple sync job involving a single table with 8.13 beta:

/*

Copyright© Webyog Softworks Pvt. Ltd. All Rights Reserved.

MySQL – 5.0.83-community-nt

*********************************************************************

*/

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

/* SYNC DB : ******* */

SET AUTOCOMMIT = 0;

/* SYNC TABLE : `*******` */

insert into …..

COMMIT;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */

;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

[peterlaursen]

but there is a cosmetical issue with a semicolon!

[peterlaursen]

My mistake .. you were referring to backups.

We have this option in 'scheduled backup' – please see attached. The interactive backup feature from database/table menu/context menu we try to keep as simple as possible.

[attachment=1216:scdback.jpg]

[DonQuichote]

peterlaursen wrote on Jul 13 2009, 01:05 PM:

We have been doing this for the last 6-9 months already. Just use the option to 'generate sync script' and you will see. Doesn't it?

I downloaded the latest version (8.12 enterprise) and to find that option. I was using 8.06. I now see your answer to where the option is located. I just made a dump with tools>”Backup database as SQL dump”. I use SQLyog purely for development. Scheduled backup is done with scripts on the Linux servers. Thanks for the answer.

[Author]

Posts