Select Database -> Create Database(Ctrl+D) or use the Object Browser context menu to create a new database. Enter the Database name and click Create button. The created database gets currently selected. The charset and collation options lets you define the default charset and collation for a database. Tables created in the database will be created with the selected charset and collation unless otherwise specified.

Alter Databse

This option lets you change the default charset and collation for a database. New tables created after that will be created with the selected charset and collation unless otherwise specified. Default settings for existing tables and data stored in existing columns are not affected by this. It changes only defaults for the database and as such has only effect for future tables and data.

How to invoke the dialogue: Select Database -> Alter Database (or press F6 or use Database context menu) to alter database.

Select Database -> More Database Operations -> Empty Database to drop all the tables and other type of objects (Stored Procedures, Views etc.) of a Database. However the Database itself is not dropped – it remains as an ’empty shell’ only. This is practical for import of a SQL Dump with no CREATE DATABASE statement and no USE statement.

Drop Database

Select Database -> More Database Operations -> Drop Database (Del) to drop the active database along with all associated data objects.

Truncate Database

Select Database -> More Database Operations -> Truncate Database (Shift+Del) to truncate (make the tables empty) all the tables of a database (however, the Tables and other objects (Stored Procedures, Views etc.) are not dropped).

Execute SQL Script

SmartMYSQL allows you to execute a batch file of any size (yes….any size – limited by the OS) created using mysqldump or SmartMYSQL’s Export Database As Batch Script option. Select Tools -> Execute SQL Script (Ctrl+Shift+Q). Select the file and press Execute. If SmartMYSQL encounters an error it stops further execution and logs the query as well as the error message in SmartMYSQL.err located in the SmartMYSQL installation folder.

SmartMYSQL provides Import External Data to streamline the transfer of data from any ODBC compliant data source to MySQL. To Import data from any ODBC compliant data source, select Powertools -> Import External Data or press Ctrl+Alt+O.

The import mechanism provides powerful and effective features, such as:

• Incremental import: Using special SQL statementsand Triggers you can perform conditional data import. For example, you can run the import procedure daily and order it only to import data that appeared during the last day.
• Import sessions storage for future use: You can save your import session with all its settings and preferences and later use it repeatedly.
• Import sessions scheduling: You can order your import session to be scheduled, and SmartMYSQLwill automatically create a standard MS Windows scheduled task where you can specify your schedule preferences depending on your needs.

Import External Data requires an appropriate ODBC-driver (matching the data source) installed on the system where SmartMYSQL is running. There are two ways how SmartMYSQL can use such driver:

• by using a DSN setting. This option can be used for all ODBC data sources no matter if the data source is available on local or a remote system.
• or by specifying a data source file. In this case the ODBC driver details will be passed to the program transparently for user. This option can be used only if the data soruce is on local system and only for specific type of data sources. Currently supported files are: plain text files (.txt, .csv etc.) Microsoft Excel files (.xls) and Microsoft Access files (.mdb).

This option is available from Powertools -> Scheduled Backups (or) right click on database->Backup/Export->Scheduled Backups  (or press  Ctrl+ALT+S)

SmartMYSQL’s Scheduled Backup Wizard allows you to schedule Exporting of Data as SQL Dumps. You can use this wizard to export data into a single file or as multiple files.

Starting Scheduled Database Backups

Startup Scheduled Backup Wizard found in the Powertools menu or use keyboard shortcut Ctrl+Alt+S to start the tool. The first screen you will get is:

This page allows you to start up a backup session or to edit an existing session.

Select Start a new Session and click Next to start a new backup session.

In this page you need to provide details about your MySQL database.

There are also are two different options to backup ALL databases (but it will of course only backup the databases that this user can access on the server). One of those options includes the `mysql` database and the other does not. You should be careful about backing up and restoring the `mysql` database unless you are the server administrator. For most users best option is to backup ‘ALL user data’ (without `mysql` database). Note that both ALL options will not back up the Information_Schema database and Performance_Schema. These databases are read-only databases and cannot be restored. However , if you want, you may select it individually for export.

Note that it’s not required to have SmartMYSQL currently connected to any database to execute the operation. The session establishes all required connections during the operation execution.

Selecting object(s) that you want to export

In this page, you choose whether you want to create backup of all the objects or selected object(s).

Select Export All Objects to export all the objects. If you want to export only selected object(s) then select Export Selected Object(s) and select the corresponding object(s) in the list box.

Here in the dialog box, first select the object(s) like tables, views, stored procs, functions, triggers and events to be copied from the source database. You may Select All / Deselect All by clicking on tree node checkboxes to select the object(s) quickly.

How do you want to generate the script

SmartMYSQL allows you make a complete dump in one single dump file or across separate files in a directory. If you choose to export to separate files then SmartMYSQL will generate obj_{objectname}.sql in the folder specified.

Select the option and provide the corresponding File name or Folder name accordingly.

You can even tell SmartMYSQL to prefix the specified file name with the current timestamp and configure it to overwrite or append to existing script files.

Compress backup files: When this option is checked SmartMYSQL will create a zip-archive of the backup.

Options

The next two pages of the wizard allow you to set various options for the backup process. SmartMYSQL 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 a  “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 SmartMYSQL 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

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 Sqldump.

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  an 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 Foreign 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.
• 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 where 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.

Error Handling

This window allows you to configure SmartMYSQL about how to handle situations when an error occurs.

Abort on error: If you select this option then SmartMYSQL will stop the export process as soon as it faces an error.

Email notifications: SmartMYSQL’s Scheduled Backup has an option where it can send you report of the export process over mail. You can configure SmartMYSQL to send you the report only if an error occurs or every time the process executes.

To setup SmartMYSQL to send notifications over email, just select the option Email Notifications and press Next. In the next window as shown below, provide your SMTP server details to send you  an email.

Executing a Job

Selecting Run immediately starts up SJA as a child process to execute the query(s) and

send the resultset(s) over email, if requested. The complete detail of the process is shown in the last step of the wizard.

Check the option Saving Options if you need to use the job repeatedly.

• Select Save Job File if in the future you suppose to run the job manually (for this select the option Edit a saved Session at the Wizard’s first page or use the Job File as a command_line argument for SJA.exe). With this option, at the following page of the Wizard you should indicate the name for the Job File which will be saved in the XML format.
• Select Windows Schedule if in the future you need the job to be launched automatically according to a certain schedule. In this case at the following page of the Wizard you should indicate the name for the Job File which will be saved in the XML format and the name for a MS Windows Schedule which will be automatically created for you by SmartMYSQL as soon as the job is saved. The Properties window will appear and you will be able to schedule it as necessary.
• In the box at the bottom of the window, specify  the folder path where the SmartMYSQL Log file must be saved.

Finishing Up The Scheduled Export Wizard

Selecting Run immediately commands SmartMYSQL to launch the export session and show you the Wizard’s log page where you can monitor the process.

Selecting Save Configuration file brings up the below dialog. Specify the file name and press Finish; SmartMYSQL will then save your session.

Selecting Save & Schedule It using Windows Scheduler you will get the below dialog. You need to save the session details in a XML file before you can schedule it. Clicking on Finish button starts up the Windows Task Scheduler dialog.