Options

The next two pages of the wizard allow you to set various options for the backup process. SQLyog provides you with maximum flexibility and options to generate scripts as you want.

Structure and Data: Select this option if you want to backup both structure and data information for a table.

Structure Only: Select this option if you want to backup only structure information for a table.

DDL Options:

-- Include "CREATE database" statement: Select this option if you want to add "Create database" statement in the output.

-- Include "USE database" statement: Select this option if you want to add "Use database" statement in the output.

-- Include "DROP" statement: Select this option if you want to add "Drop Object(s)" statement in the output.

-- Ignore Definer: It is an option to Ignore DEFINER clause for database objects. Note that the target server will then create DEFINER as current SQLyog user when creating the object.

DML Options:

-- Generate Bulk INSERT statement: When this option is checked data from more rows will be written into a single INSERT statement. Each INSERT statement will be allowed to grow to the size specified in the 'Max. size of BULK INSERTS' setting.

-- Generate complete INSERT statement: Select this option if you want to backup data of a table as complete insert statements. Checking this option will generate SQL statements like: insert into tablename ( col1, col2, col3 ) values ( 1, 2, 3 ). Keeping the option unchecked will result in SQL statement being generated as: insert into tablename values ( 1, 2, 3 ).

-- Generate INSERT DELAYED statement: Select this option if you want to generate INSERT DELAYED statements. For more information on INSERT DELAYED, please visit the MySQL online documentation page.

Note that to ensure the integrity of your final export result; most of the options in this section are disabled if at least one of the DB's tables was not selected for export at the previous stages of the process. For more info Backup Database as SQL Dump.

This window allows you to set various other options while exporting data.

FLUSH Options:

  • Flush Logs before dump: Using this option will ensure that all data changes pending will been written to disk before the backup starts.
  • Flush master log and Flush slave logs: Leave them unchecked if the server is not a replicating master or slave. An attempt to FLUSH either if it does not exists will return an error and will/may abort the job

Options having effect on source:

  • LOCK All Tables for read: The LOCK will be generated for one table (the one for which INSERT statements is currently generated) at a time.
  • Single Transaction: Option (similar to 'mysqldump' "-–single-transaction") has only effect with Transactional storage engines (like InnoDB). All tables will be backed up from their state at the time the backup started. This option will ensure consistency across tables with Foreing Key CONSTRAINTs. The Single transaction option and the LOCK ALL Tables option are mutually exclusive.

Options written to file:

  • Set FOREIGN_KEY_CHECKS=0: This option should always be checked if tables with Foreign Keys are backed up as there is no way to ensure that a 'parent' table is written to the file before a 'child table'. If they are written in opposite order restore will fail if the option is not selected.
  • Add Lock around INSERT statement(s): This will ensure that no other clients have WRITE access to tables during restore as long as they are not fully restored.
  • Add DISABLE/ENABLE keys around INSERT statement(s): If this option is checked the server will not maintain indexes during the restore process (while executing INSERT statements) but generate indexes for each table when the restore of the table is complete. This may result in performance improvements during restore.
  • Max. size (in KB) for BULK INSERT statement(s): Here user may specify how large BULK INSERTS are allowed to be. Note: Any specification larger than 16MB will 'rounded down' to 16 MB. The size should be specified not larger than the max_allowed_packet setting on the server whre you want to restore the dump. If you try to execute BULK INSERT statement(s) larger than the server setting the server will disconnect the client and you will get the error 'The MySQL Server has gone away'.
  • Chunk size (in rows) during batch exports: The Chunk setting specifies how many rows will be fetched for every SELECT statement sent when retrieving data for generation of SELECT statements.  Default is 1000. With very 'wide' and tables with large BLOB/TEXT columns you may get better performance with a lower setting - in particular when HTTP tunneling to a shared MySQL server where there may be a restriction on the memory a user is allowed to use. 

Note:This feature is available in SQLyog Enterprise and Ultimate.