Backup Database as SQL Dump (Batch Scripts)

You can dump a database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table. To generate a dump select the database or table in the Object Browser and select Database -> Backup/Export -> Backup Database As SQL Dump… This option is also available in Table -> Backup/Export -> Backup Database As SQL Dump... or just press Ctrl+Alt+E.

Database name: Select the source Database from the list of available databases.

Export as SQL: Specify the required option depending on what you need to export: only the database structure, only the data or both.

Export to file: Specify the Export file name.

Now check the following options as required by you:

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.
  • Flush logs before dump: Using this option will ensure that all data changes pending will be written to disk before the backup starts.
  • 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:

  • Include "USE database" statement: To insert the USE database name in the script.
  • Include "CREATE database" statement: To insert the CREATE DATABASE in the script.
  • 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.
  • Create Bulk Insert statement(s): 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.
  • Include "DROP" statement(s): Inserts DROP statement(s) in the script to drop the named object(s) first before restoring the script. The option does have effect on the database - only tables and other objects (Views, 'Stored Programs').
  • 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..

Advance Options:

  • Prefix with Timestamp: This will ensure that the timestamp i.e. date and time of the backup is added to the name  of the .sql file created.
  • File per Object: This options allows you to backup data multiple files across SQL files. SQLyog will generate a file named tbl_(file_name).sql for each file.
    If both are selected, then a folder will be created by the name of the file with the timestamp in the name of that folder and each of the files inside that folder will have the timestamp as the prefix in the name.

In SQL Dump dialog box, first select the object(s) like tables, views, stored procs, functions, triggers and events to be backedup from the source database. You may Select All/Deselect All by clicking on tree node checkboxes to select the object(s) quickly. Now select the file where you want to export the data.

Click Export to create the script (batch) file. SQLyog does the export in a different thread so you can stop the export process anytime.

You can even schedule the Backup process using Scheduled Backups option.