MySQL Database Advanced Settings

Advanced Settings contains Source Settings ( Source Server ), Destination Settings ( Destination Server or File ), InnoDB, Statements, Insert, Master and Slave and Others. We advise you to use the default values.

Source Settings: It only works for the source server.
1. Flush tables before starting: Flush tables in server before backing up or restoring. Check the option to maintain data consistency. Please uncheck it, if you want to allow others access the source server when backing up or restoring.
2. Lock tables before starting: Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole backing up or restoring. Check the option to maintain data consistency. Please uncheck it, if you want to allow others access the source server when backing up or restoring.
3. Sorts each table's rows by primary key: Sorts each table's rows by primary key, or first unique key, if such a key exists. Useful when dumping a MyISAM table to be loaded into an InnoDB table, but will make the dump itself take considerably longer.
4. Quote names within '`' characters: Quote table and column names with backticks (`).
5. Set connection character set: The MySQL server automatically set the character set for connect the characters. It's used for nonEnglish ( German, French, Italian, Russian, Chinese, Japanese, Korean, etc. ).
6. SET TIME_ZONE as UTC: SET TIME_ZONE = '+00:00' to allow it of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones.
7. Flush MySQL log files before starting: Flush MySQL logs file in server before backing up or restoring. Please note that if you export many databases at once, the MySQL logs will be flushed for each database exported.
8. Net buffer length: The buffer size for TCP / IP and socket communication.
9. Max allowed packet: The maximum packet length to send to or receive from server.
10. Single transaction ( InnoDB ): Creates a consistent snapshot by exporting all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning ( currently only InnoDB does ); the dump is NOT guaranteed to be consistent for other storage engines. While a Single transaction dump is in process, to ensure a valid exported file ( correct table contents and binary log position ), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off Lock tables before starting.
11. Continue even if an error occurs during running: Continue even if we get an SQL error.
12. SELECT...FROM...WHERE: Export only the selected records base on the WHERE statement. For example, `id` > 100 AND name LIKE "Mike %".
Destination Settings: It only works for the destination server, including SQL files and MySQL server.
1. General:
  1. Add CREATE DATABASE statement: Add a CREATE DATABASE statement before each exporting to the output.
  2. Add LOCK TABLES statement: Add the LOCK TABLES and UNLOCK statements around INSERT statements to the output.
  3. Add DELETE all records statement: Add a DELETE all records statement before each create to the output.
  4. Add DROP TABLE statement: Add a DROP TABLE statement before each create to the output.
  5. Add SET NAMES default_character_set: Add the 'SET NAMES default_character_set' statements to the output.
  6. Add SET SQL_NOTES: Add the SET SQL_NOTES statements to the output.
2. InnoDB:
  1. Add SET UNIQUE_CHECKS ( InnoDB ): Add the 'SET UNIQUE_CHECKS' statements for exporting the InnoDB databases.
  2. Add SET AUTOCOMMIT ( InnoDB ): Add the 'SET AUTOCOMMIT' statements for exporting the InnoDB databases.
  3. Add SET FOREIGN_KEY_CHECKS ( InnoDB ): Add the 'SET FOREIGN_KEY_CHECKS' statements for exporting the InnoDB databases.
3. Statements:
  1. Fields terminated by: Fields in the output file are terminated by the given string. The default character is comma (,).
  2. Fields enclosed by: Fields in the output file are enclosed by the given character. The default character is double quote (").
  3. Fields optionally enclosed by: Fields in the output file are optionally enclosed by the given character. The default character is forward quote (').
  4. Fields escaped by: Fields in the output file are escaped by the given character. The default character is backslash (\).
  5. Lines terminated by: Lines in the output file are terminated by the given string. The default character is line breaks (\r\n).
4. Insert:
  1. Use complete INSERT statements: Use complete INSERT statements to the output ( INSERT [INTO] <table> [column] VALUES [data_values] ).
  2. Use INSERT DELAYED statements: Insert rows with INSERT DELAYED syntax.
  3. Use INSERT IGNORE statements: Insert rows with INSERT IGNORE syntax.
  4. Use REPLACE instead of INSERT statements: Use REPLACE syntax instead of INSERT statements. REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
  5. Surround the INSERT statements with ALTER TABLE table_name DISABLE KEYS: Add the DISABLE KEYS statements to avoid the primary keys and foreign keys check when restoring.
  6. Use multiple-row syntax some rows per INSERT statements: Use multiple-row INSERT syntax that include several VALUES lists.
5. Master and Slave :
  1. Make a slave server from the master server: Export a SQL script to create a MySQL slave server from the master server.
  2. Add 'STOP SLAVE' and 'START SLAVE' statements: Add the 'STOP SLAVE' and 'START SLAVE' statements to the output.
  3. Make a new slave server from the original slave server: Export a SQL script to create a new slave server from the original slave server.
    1. Include master host and port: Add 'MASTER_HOST=<host>, MASTER_PORT=<port>' to the output.
  4. Delete master logs after running: Delete logs on master after backing up.
6. Others:
  1. Allow creation of column names that are keywords: Allow creation of column names that are keywords ( e.g. TABLE ).
  2. Dump binary columns ( BINARY, VARBINARY, BLOB, BIT ) using hexadecamal notation: Dump binary strings (BINARY, VARBINARY, BLOB) in hexadecimal format ( e.g 0xFE62696E... ).
  3. Surround all of statements with comment ( /*!40100...*/ ): Surround all of statements with comment ( /*!40100...*/ ) in the output.
  4. Add FLUSH PRIVILEGES statement: Emit a FLUSH PRIVILEGES statement after backing up or restoring the mysql database. This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restore.

Reset to default: Reset all of the Advanced Settings to the default values.