Table of Contents
MySQL Workbench provides a visual GUI to administer your MySQL environment. The available visual tools help configure your MySQL servers, administer users, perform backup and recovery, inspect audit data, and view database health.
Manage your MySQL instances with a comprehensive view of your MySQL server connections. The visual tree based navigation provides detailed information about server and status variables, including the number of threads, bytes sent and received by clients, buffer allocations size, and more.
The Navigator panel has a Management tab with functionality to monitor and configure your selected MySQL connection.
The Navigator panel also has a Schemas tab for managing databases using your MySQL Connection. For information about the Schemas tab, see Section 7.2.1, “Object Browser and Editor Navigator”.
The Navigator Management tab is separated into the MANAGEMENT, INSTANCE, and PERFORMANCE sections, and the Commercial edition of MySQL Workbench also includes the MYSQL ENTERPRISE section. Each section offers several actions.
Manage and create MySQL connections.
To add a connection, click the [+] icon to the
right of the MySQL Connections title on the
Home screen. This opens the
Setup New Connection form:
The button (bottom left) opens an optional configuration wizard for setting shell commands on the host. For example, commands to start/stop the MySQL instance, or to edit configuration file. For more information, see Section 5.1.2.3.5, “Configure Server Management Wizard”.
Fill out the connection details and optionally click to execute the Server Management wizard. Click to save the connection.
When opening connections, MySQL Workbench automatically sets the
client character set to utf8. Manually changing
the client character set, such as using SET NAMES
, may cause MySQL Workbench to
not correctly display the characters. For additional information
about client character sets, see
Connection Character Sets and Collations.
...
New MySQL connections are added to the Home screen as a tile, and the Section 7.2.1, “Object Browser and Editor Navigator” describes several MySQL Workbench features to monitor and configure each connected MySQL server. A single MySQL Workbench instance can open one or multiple MySQL connections into individual tabs.
For a more detailed overview of this process, see the tutorial titled Section 5.1.2.2, “Creating A New MySQL Connection (Tutorial)”.
Launch MySQL Workbench. You will be presented with the Home window.
Our example already has two connections created, but let us create a new connection. From the MySQL Workbench Home window, click the [+] icon near the MySQL Connections label. This opens the Setup New Connection wizard.
Define the Connection Name value, which we will set to "MyFirstConnection" in this example.
The default connection values are for a typical local setup, so check them and enter the appropriate values. If you are unsure, click the button to check the connection parameters. Do not press .
Next, optionally click , which opens up the Configure Local Management wizard:
Read the Configure Local Management introduction, and press to begin defining the new connection parameters.
The connection will now be tested. You should see that the connection was successful. If not, click and check that you have entered the information correctly.
Toggle the to view additional details about the tested connection, then click .
Optionally, you may configure a method for remote management if a Remote Host was specified. Setting these options enables MySQL Workbench to determine the location of configuration files, and the correct start and stop commands to use for the connection.
SSH login based management and Native Windows remote management types are available. The Operating System and MySQL Installation Type are configured for the SSH login variant.
We are creating a local MySQL connection in this tutorial, so are skipping the Management and OS and SSH Configuration options, as they are used for configuring a remote MySQL connection.
On Microsoft Windows, select the appropriate MySQL service for the MySQL connection.
The wizard will now check its ability to access the start and stop commands, and check access to the MySQL Server configuration file.
You now have a chance to review the configuration settings. The information displayed varies slightly depending on platform, connection method, and installation type.
At the Review Settings prompt, choose "I'd like to review the settings again" to review the settings. Choosing "Continue" closes the "Configure Server Management" dialog.
Check the Change Parameters if you want to check or edit information about the MySQL configuration file. In our example we will check it, and click to continue.
Review the MySQL configuration file information. Click the Check buttons to perform the described checks, or optionally change the configuration file path.
Optionally, enter your own commands for starting, stopping, and checking the MySQL connection. Typically the default values are used, which means leaving these optional values blank.
Click to close the "Configure Server Management" dialog, which reveals the original Setup New Connection window.
After reviewing the Setup New Connection information, press again to make sure it still functions, and then to create the new MySQL connection.
Your new MyFirstConnection MySQL connection is now listed on the Home window.
From the Home window, click the new MySQL connection to open the SQL editor for this connection. The SQL editor is the default page, so now select the Server Status from the left Navigator panel to display the connected MySQL server's current status.
Test the other Navigator panel options that relate to your new MySQL connection. Check its status, MySQL logs, and measure its performance statistics from the Dashboard.
Notice the Management and Schemas tabs on the bottom of the Navigator panel. The Schemas view displays the schemas that are associated with your new MySQL connection. Alternatively, you can merge the Schemas and Management tabs by either clicking the merge icon on the top right of the Navigator panel, or by enabling the Show Management Tools and Schema Tree in a single tab SQL Editor preference.
This concludes the "Creating a MySQL connection" tutorial. For additional information about MySQL connections, see Section 5.1.2, “MySQL Connections”.
The Manage Server Connections dialog is another way to manage MySQL connections. This dialog is invoked by either selecting Edit Connection or selecting , from the main menu. It can also be invoked from any of the wizards requiring access to a live database.
After the MySQL connection manager is launched, you are presented with the following dialog, with the Connection tab open:
Connection Name: The name used to refer to this connection. This connection can then be selected from a list in other wizards requiring a connection.
Connection Method: Method used to connect to the RDBMS.
After you select a connection method, the fields available in the Parameters, SSL, and Advanced tabs change accordingly. More details about these options and parameters are available below.
The button will test the selected MySQL connection and report its connection status. It also reports whether or not SSL is enabled.
For testing remote connections, you might also use
ping to check the hostname, or
telnet to also check the port. If these fail,
then also check the firewall settings on each host, and also that
MySQL server is running on the remote host.
This connection method enables MySQL Workbench to connect to MySQL Server using TCP/IP.
The --skip-networking MySQL
server configuration option affects the TCP/IP connection
method. If disabled, use named pipes or shared memory (on
Windows) or Unix socket files (on Unix).
Parameters tab
Hostname: The host name or IP address of the MySQL server.
NoteThe host name "localhost" might resolve to "127.0.0.1" or "::1" on your host, so note this when checking permissions. For example, if a web application's user only has access to "127.0.0.1" on a host, and a defined connection uses "localhost" that resolves to "::1", this connection may lack the proper permissions to the aforementioned web application. Ping "localhost" on each host to determine where it resolves to.
Port: The TCP/IP port on which the MySQL server is listening (the default is 3306).
Username: User name to use for the connection.
Password: Optional password for the account used. If you enter no password here, you will be prompted to enter the password when MySQL Workbench attempts to establish the connection. MySQL Workbench can store the password in a vault (see Section 5.1.2.3.6, “The Password Storage Vault”).
Default Schema: When the connection to the server is established, this is the schema that will be used by default. It becomes the default schema for use in other parts of MySQL Workbench.
Advanced tab
More parameters can be set for the connection by using the Advanced tab.
The Advanced tab includes these check boxes:
Use compression protocol: If checked, the communication between the application and the MySQL server will be compressed, which may increase transfer rates. This corresponds to starting a MySQL command-line client with the
--compressoption. This option is unchecked by default.Use ANSI quotes to quote identifiers: Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With this option enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier. Note: If this option is checked, it overrides the server setting. This option is unchecked by default.
Enable Cleartext Authentication Plugin: Send the user password in cleartext. Required for some authentication methods. This option is unchecked by default.
Use the old authentication protocol: This option disables Connector/C++'s secure_auth option. This option unchecked by default.
It also includes these options:
SQL_MODE: Override the default
SQL_MODE used by the server.
Others: Other options for Connector/C++ as option=value pairs, one per line.
SSL tab
More parameters can be set for the connection by using the SSL tab.
SSL: This dropdown provides options related to enabling SSL encryption. Choose No to disable SSL, If available if the client library supports it, or Require to require SSL support for the MySQL connection to succeed. This option defaults to If available.
SSL CA File: Path to the Certification Authority file for SSL.
SSL CERT File: Path the Certificate file for SSL.
SSL Key File: Path to the Key file for SSL.
SSL Cipher: Optional list of permissible ciphers to use for SSL encryption.
This connection method enables MySQL Workbench to connect to MySQL Server using a socket file (on Unix) or a named pipe (on Windows).
Parameters
The unique field here is Socket/Pipe Path.
Enter the name of the socket or pipe here. If the field is left
blank, the default socket or pipe name is used. On Unix, the
default socket name is /tmp/mysql.sock. On
Microsoft Windows, the default pipe name is
MySQL.
This option can be seen in the following screenshot.
Advanced
These are the same options discussed in Section 5.1.2.3.1, “Standard TCP/IP Connection Method”, except there is not the Use compression protocol option.
SSL
These are the same options discussed in Section 5.1.2.3.1, “Standard TCP/IP Connection Method”.
This connection method enables MySQL Workbench to connect to MySQL Server using TCP/IP over an SSH connection.
Parameters
In addition to a number of parameters that are in common with Standard TCP/IP connections, this connection method features a number of specialized parameters. These are listed here:
SSH Hostname: This is the name of the SSH server. An optional port number can also be provided. For example, localhost:22.
SSH Username: This is the name of the SSH user name to connect with.
SSH Password: The SSH password. It is recommended that an SSH key file is also used.
SSH Key File: A path to the SSH key file.
If a remote host is missing from the system's list of known hosts, a prompt requires you to confirm the host's fingerprint before storing it. If your stored host fingerprint is different than the host's current fingerprint, then an error is generated and you will be required to handle the discrepancy from outside of MySQL Workbench before creating the connection. Prior to MySQL Workbench 6.1.6, the host SSH fingerprint was not saved by MySQL Workbench.
On Linux and OS X, SSH host fingerprints are stored in
~/.ssh/known_hosts. On Microsoft Windows,
they are stored in a file created by MySQL Workbench under the user's
application data folder (%appdata%), such as
C:\Users\.
[username]\AppData\Roaming\MySQL\Workbench\known_hosts
The SSH connection options are viewable in the following screenshot:
Advanced
The options here are the same as for the Standard TCP/IP connection. See Section 5.1.2.3.1, “Standard TCP/IP Connection Method”.
SSL
The options here are the same as for the Standard TCP/IP connection. See Section 5.1.2.3.1, “Standard TCP/IP Connection Method”.
The System Profile tab enables you to specify host-specific information. This is achieved primarily through selecting a System Type, along with its corresponding Installation Type. These profile settings contain standard information that is used in managing the host's MySQL instance.
Here are some of the available installation types:
FreeBSD, MySQL package or Custom
Linux, including several distributions including Fedora, Oracle, RHEL, SLES, Ubuntu, Generic, and Custom
OS X, MySQL package or Custom
OpenSolaris, MySQL package or Custom
Windows, with different installation methods, MySQL versions, and build architectures
After you select the System Type and
Installation Type, a number of default
parameters will be set that includes: commands used to start and
stop MySQL, commands to check the server status, the location of
the my.ini or my.cnf
configuration file, and on Windows, the Windows Service Name. You
may also customize these preset default values.
The Remote Management tab is available when connecting to MySQL remotely.
Clicking the [+] icon from the Home page launches the Setup New Connection wizard. The wizard provides a MySQL connection form to create a new MySQL connection, and includes a option as a step-by-step approach to creating a new MySQL server connection. This can also be executed later (on remote connections) when from the home page by clicking the top right corner of a MySQL connection tile:
Executing this wizard is required to perform tasks requiring shell access to the host. For example, starting/stopping the MySQL instance and editing the configuration file.
The steps presented in the wizard are as follows:
Test DB Connection
Management and OS
SSH Configuration
Windows Management
Test Settings
Review Settings
MySQL Config File
Specify Commands
Test DB Connection
On this page, MySQL Workbench tests your database connection and displays the results. If an error occurs, you are directed to view the logs, which can be done by clicking the button.
Management and OS
Used to specify a remote management type and target operating system, which is available when the Host Machine is defined as a remote host.
The SSH login based management option includes configuration entries for the Operating System and MySQL Installation Type.
SSH Configuration
If you specified a Remote Host on the Specify Host Machine page, you will be presented with the Host SSH Connection page, that enables you to use SSH for the connection to the server instance. This facility enables you to create a secure connection to remotely administer and configure the server instance. You must enter the host name and user name of the account that will be used to log in to the server for administration and configuration activities. If you do not enter the optional SSH Key for use with the server, then you will be prompted for the password when the connection is established by MySQL Workbench.
This connection is to enable remote administration and configuration of the MySQL Server itself. It is not the same as the connection used to connect to a server for general database manipulation.
You must use an SSH connection type when managing a remote server if you wish to start or stop the server or edit its configuration file. Other administrative functions do not require an SSH connection.
Windows Management
If a Windows server is used, then the Windows configuration parameters must be set. Windows management requires a user account with the required privileges to query the system status, and to control services. And read/write access to the configuration file is needed to allow editing of the file.
Test Settings
On the next page your settings are tested and the wizard reports back the results after attempting to connect to the server. If an error occurs, you are directed to view the logs, which can be done by clicking the button.
MySQL Workbench must know where the MySQL Server configuration file is located to be able to display configuration information. The wizard is able to determine the most likely location of the configuration file, based on the selection made on the Operating System page of the wizard. However, it is possible to test that this information is correct by clicking the and buttons. The wizard then reports whether the configuration file and server configuration section can in fact be accessed. It is also possible to manually enter the location of the configuration file, and the section pertaining to MySQL Server data; these manually entered values should be tested using the buttons provided. Click the button to continue.
Review Settings
The modified settings may be reviewed, which also includes the default values. Check the Change Parameters checkbox if the MySQL Config File section will be edited, and then click to continue.
MySQL Config File
Allows configuration of the MySQL server version. It also allows the editing and validation of the configuration file path, and validation of the server instance section. Click to continue.
Specify Commands
This page enables you to set the commands required to start, stop, and check the status of the running server instance. It is possible to customize the commands if required, but the defaults should be suitable in most cases. The defaults are set based on the options selected in the Operating System page of the wizard. Click to continue.
Complete Setup
On this page, you finally assign a name to the server instance. This name is used in various parts of the GUI to enable you to refer to this instance. After setting a suitable name, click to save the instance.
The vault provides a convenient secure storage for passwords used to access MySQL servers. By using the vault, you need not enter credentials every time MySQL Workbench attempts to connect to a server.
The hostname is used for storing password information. For example, a local connection might use "localhost", "127.0.0.1", or "::1", but these are stored separately in the password storage vault, even if they all resolve to the same place.
The vault is implemented differently on each platform:
Windows: The vault is an encrypted file in the MySQL Workbench
datadirectory. This is whereconnections.xmland related files are located. The file is encrypted using a Windows API which performs the encryption based on the current user, so only the current user can decrypt it. As a result it is not possible to decrypt the file on any other computer. It is possible to delete the file, in which case all stored passwords are lost, but MySQL Workbench will otherwise perform as expected. You then must re-enter passwords as required.OS X: The vault is implemented using the OS X Secure Keychain. The keychain contents can be viewed using the
Keychain Access.apputility.Linux: The vault works by storing passwords using the
gnome-keyringdaemon, which must be running for password persistence to work. The daemon is automatically started in GNOME desktops, but normally is not in KDE and others. Thegnome-keyringdaemon can be used for password storage in MySQL Workbench on non-GNOME platforms, but must be started manually.
Browse, view status, and connect to any MySQL instance in a Fabric Cluster.
This requires Connector/Python and MySQL Utilities 1.4.3+ installed, including the Python module. This feature also requires MySQL Workbench 6.2+.
To set up a managed Fabric connection, create a new MySQL connection with the new connection method. The connection tiles have a different look:
Clicking the new fabric group tile shows the managed connections:
The client connection browser lists the active and sleeping MySQL client connections, and adds the ability to kill statements and connections, and view additional connection details and attributes.
The connection details viewer requires MySQL 5.6 or greater. Only basic connection information is available for previous versions of MySQL, such as the connection hosts, database, and state.
Client Connections and Metadata locks
The Client Connections management window includes a for connections to MySQL 5.6 and above. These details are separated into three tabs:
Details: connection details such as Process ID, Type, User, Host, Instrumented, and additional information.
Locks: MySQL uses metadata locking to manage access to objects such as tables and triggers. Sometimes a query might be blocked while being manipulated by another connection from another user. The Locks feature utilizes these MySQL metadata locks (MDL) to show the locked connections that are blocked or being waiting on, and shows information about the locks, what they are waiting for, and what they hold.
NoteThe metadata lock information is provided in the performance schema as of MySQL server 5.7.3.
Attributes: these are connection attributes such as OS, Client Name, Client Version, and Platform.
The Server Logs page features two subtabs:
Error Log File
General MySQL errors, for more information see The Error Log
Slow Log File
Slow queries (when available), for more information see The Slow Query Log
The Startup / Shutdown functionality includes:
Viewing the Startup Message Log
Start up and shut down the MySQL instance
View the current status of the MySQL instance
The Options File editor is used to view and
edit the MySQL configuration
file (my.ini on Windows, or
my.cnf on Linux / OS X) by selecting check
boxes and other GUI controls, and then making edits. MySQL Workbench
divides the options file into its own groupings as a set of tabs
(such as General, Logging, InnoDB, and more). Make an edit and click
to commit the changes.
The options file editor has several components:
Option file groupings, as divided into convenient tabs by MySQL Workbench
A Locate option search field to search your MySQL options configuration file
Configuration File path, so you know the configuration file you are editing
An options file group selector, to select the option [group] to edit. Because the same option can be defined under multiple groupings, it is important to choose the correct group when making edits.
[mysqld](the MySQL server) is the default and most common group. For additional information about groups, see Using Option Files.
A screenshot with the General tab selected:
A listing of all users and privileges that relate to the MySQL connection. You may also manage (add) user accounts, adjust privileges, and expire passwords.
The Users and Privileges page has several sections:
User Accounts
Lists each user account that is associated to the active MySQL connection.
Login
Login information related to the selected user account.
Account Limits
Define limits for the user account, such as the maximum number of queries, updates, connections, and concurrent connections that an account can execute in one hour.
Administrative Roles
To aid in assigning privileges to MySQL Server users, MySQL Workbench
introduces the concept of Administrative Roles. Roles are a quick
way of granting a set of privileges to a user, based on the work the
user must carry out on the server. It is also possible to assign
multiple roles to a user. To assign roles, click the User Account
you wish to modify, then click the Administrative
Roles tab. Then click the check boxes according to the
roles you wish to allocate to the user. After you select a role to a
user, you will see the accumulated privileges in the
Global Privileges Assigned to User panel. For
example, if you select the role BackupAdmin, the
privileges granted include EVENT, LOCK
TABLES, SELECT, SHOW
DATABASES. If you also select the role of
ReplicationAdmin, the list of privileges expands
to include REPLICATION CLIENT,
REPLICATION SLAVE and SUPER.
These roles are available:
DBA: Grants all privileges
MaintenanceAdmin: Grants privileges to maintain the server
ProcessAdmin: Grants privileges to monitor and kill user processes
UserAdmin: Grants privileges to create users and reset passwords
SecurityAdmin: Grants privileges to manage logins and grant and revoke server privileges
MonitorAdmin: Grants privileges to monitor the server
DBManager: Grants privileges to manage databases
DBDesigner: Grants privileges to create and reverse engineer any database schema
ReplicationAdmin: Grants privileges to set up and manage replication
BackupAdmin: Grants privileges required to back up databases
Custom: Lists other (custom) privileges that are assigned to the user account
The Password Validation Plugin (available as of MySQL Server 5.6.6) is supported in Workbench. For information about what these settings mean, see The Password Validation Plugin.
Schema Privileges
Additional schema privileges that the account can use. For example,
the standard mysqlbackup user has "CREATE
TEMPORARY TABLES" on the mysql schema.
Get an immediate view into the basic health indicators and counters for your MySQL environment. This includes viewing the server's running state (stopped/running), available features, primary server directories, replication state, and security settings for authentication and SSL. Reports also include information and graphs to track memory usage, connections, hit rates, and more.
Status and System Variables: Lists all server variables for the MySQL connection. You may also copy all or selected variables to your clipboard.
Custom Variable Grouping
The status and system variables are each categorized by groups (such as InnoDB or Logging), and you may also create your own custom groups. Right-click on a variable, choose the custom group (or create a new one), and add the variable to the aforementioned group. Your custom groups are listed along with the pre-existing groups.
In the example below, we created a custom group titled "My
Variables" and are in the process of adding the
Created_tmp_variables variable to it.
This tab allows you to export your MySQL data. Select each schema you want to export, optionally choose specific schema objects/tables from each schema, and generate the export. Configuration options include exporting to a project folder or self-contained SQL file, optionally dump stored routines and events, or skip table data.
Alternatively, use Section 7.1.9, “Export a Result Set” to export a specific result set in the SQL editor to another format such as CSV, JSON, HTML, and XML.
Select the Database objects to export, and configure the related options.
Click to load the current objects.
Optionally open the tab that allows you to refine the export operation. For example, add table locks, use replace instead of insert statements, quote identifiers with backtick characters, and more.
Click to begin the export process:
This functionality uses the mysqldump command.
Restore exported data from the Data Export operation, or from other exported data from the mysqldump command.
Choose the project folder or self-contained SQL file, choose the schema that the data will be imported to, or choose to define a new schema.
You may only select specific data objects (tables) to import if the data export operation used project folders instead of a self-contained SQL file.
Click to begin the import process:
MySQL Workbench offers a GUI interface to the Audit Inspector.
The Audit Inspector interface was added in MySQL Workbench 6.0.0.
Initially, when you first load the Audit Inspector, you must use MySQL Workbench to cache the audit log for performance reasons. MySQL Workbench will then parse, index, and retrieve values from the encrypted cached file on your local computer. At this stage, you also set a password for the encrypted file that will be used when viewing this file. The initial screen looks similar to:
Generating the cache file can take a long time. If you press during the caching process, MySQL Workbench will save the results that were cached at the point you pressed .
After caching an audit log, the Audit Inspector page will display the results:
The search field offers criteria for narrowing the displayed events, including and , and defaults to . Custom filters are also available.
You can from the main Audit Inspector page:
Future uses of the will require the password that you set during the initial step. The login page:
MySQL Workbench offers a MySQL Enterprise Backup GUI interface, and supports MySQL Enterprise Backup 3.6.0 and above. It is listed in the Management Navigator tab for a MySQL connection. There are two MySQL Enterprise Backup related sections in the Navigator:
Online Backup: Sets a backup profile that defines
whatshould be backed up,wherethe backup should be stored, andwhen(the frequency) MySQL should be backed up.Restore: Restores the MySQL server to a specific point in time, typically by restoring a backup that was created by the Online Backup feature in MySQL Workbench.
The MySQL Enterprise Backup configuration is located on the MySQL server, and not locally. This information includes the MySQL Enterprise Backup configuration backup profiles, job scheduling, backup operations, and data. This also means that the backup operations are executed with (or without) MySQL Workbench running.
MySQL Enterprise Backup is a MySQL Enterprise Feature that is separate from MySQL Workbench. For more information about its functionality, see the MySQL Enterprise Backup documentation at http://dev.mysql.com/doc/mysql-enterprise-backup/en. MySQL Workbench provides an interface to MySQL Enterprise Backup, as described in the following documentation. In addition to having MySQL Enterprise Backup installed on your host, the following general requirements also apply:
Managing both local and remote MySQL instances is available on Linux and OS X, and managing local MySQL instances is available on Microsoft Windows. Remote management is configured using SSH Remote Management.
A MySQL connection with a root user.
The MySQL server configuration file path must be set and correct for the MySQL connection.
The user running MySQL Workbench must be a sudoer (Linux / OS X) that is able to execute the MySQL Enterprise Backup binary.
Additionally, the sudo user must keep the
HOMEenvironment variable when executing system commands, which means adding the following to/etc/sudoers:env_keep +="HOME"
And the Prerequisites set in the tab are:
A path to the MySQL Enterprise Backup executable. MySQL Enterprise Backup is available via eDelivery or My Oracle Support (MOS). MySQL Workbench attempts to locate the MySQL Enterprise Backup executable, so check the path and adjust it accordingly.
The path to the Backup Home Directory, where backup profiles and data is stored. This can be created from within Workbench from the Settings tab.
The MySQL account for the Backup Process. The available actions depends on the current state of this set up, with options including:
: Available if a backup user does not already exist.
: Available if a backup user does exist.
: Available if the user's privileges are invalid, which alters the user by adding the
RELOAD,SUPER, andREPLICATION CLIENT ON *.*privileges.
Uninstallation notes
The MySQL Workbench uninstallation process does not remove the associated MySQL Enterprise Backup backup tasks. To stop the scheduled backups, edit the related "Task Scheduler" entries on Windows, or remove the associated cron jobs on Linux and OS X.
The MySQL Workbench uninstallation process does not remove the MySQL Enterprise Backup master related configuration file, the configuration files generated for each defined profile, nor the MySQL backups.
The tab:
If any of the requirements are not met, then an error will be generated when attempting to use MySQL Enterprise Backup features.
Sets a backup profile that defines what should
be backed up, where the backup should be
stored, and when (the frequency) it should be
backed up. The main page:
The Online Backup page is separated into three sections:
Backup Jobs: Used for managing backup jobs for the MySQL server. A backup job (profile) is a configuration file used to store information about what is backed up, where the backup is stored, and optionally when backups will be performed.
Right-clicking on a Backup Job is an alternative way to access the available actions, such as
Configure Job,Delete Job, andExecute Backup. Right-clicking also offers two additional options:: Saves the backup to a single file, and prompts for the file name.
: Generates a command for executing the backup, and copies it to your clipboard. You might execute this command in the shell or terminal, which looks similar to:
/bin/mysqlbackup --defaults-file="/var/lib/meb/foo.cnf" --show-progress=stdout backup --with-timestamp.
Backup Job Details: Displays information about the state of a specific (selected) backup job. It includes information from the page, and information specific to the selected backup.
Recent Activity: Historical information about the backup operations performed on the server. View the backup log by right-clicking an entry and choosing
View Backup Log
Executing a backup will show a dialog with the progress of the backup operation.
Backup Jobs
Creating a backup job offers several elements. The following information applies to the operation, and is used to modify existing jobs.
The Backup Profile Name and its associated Comments field are used to identify the backup job's profile, and this name is listed on the main page.
The scheduling page separates the configuration information into four tabs. The Contents tab defines the schemas and tables to back up, and whether the job is a full or partial backup.
Full backup: All schemas and tables are backed up.Partial: Select the schemas and tables (objects) that you want to back up. Choose Select objects to included/excluded to open the table inclusion (and exclusion) options. For additional information about the include, exclude, and Transportable Tablespace options, see the MySQL Enterprise Backup documentation titled Partial Backup and Restore Options.
The Options tab includes settings to modify the default behavior of the backup process.
By default, the Backup Storage Directory is
stored under a sub-folder using the name of the Backup
Profile Name in the MySQL Backup Home
Directory setting. Additional options include
compression and apply-log, and the option to
Skip Unused Pages.
The Schedule tab optionally sets a backup schedule for both full and incremental backups. The schedule uses the Windows Tasks Scheduler on Microsoft Windows, and a cron job on Linux and OS X. It is scheduled using the operating system user that is scheduling the backup, which is typically the MySQL user.
A full backup is slower than the incremental backup that merges with a full backup. A common scenario is to set a full backup as weekly, and an incremental backup as daily. For additional information about backup performance, see Optimizing Backup Performance.
The Advanced tab allows you to pass in additional MySQL Enterprise Backup options.
These additional options are not validated.
To recover backups, see Section 5.7.3, “Backup Recovery”.
The Backup Recovery wizard is used to recover MySQL Enterprise Backup backups. For more information about creating MySQL Enterprise Backup backups using MySQL Workbench, see Section 5.7.2, “Online Backup”
The Backup Recovery wizard allows you to restore backups from folders, image files, and backup profiles created by Section 5.7.2, “Online Backup”.
In our example, we will restore a full backup that was created by MySQL Workbench Online Backup. After choosing the "backup (full)" profile that we created earlier, the next page lists each table that will be updated:
We unchecked Show System Schemas the option, which is enabled by default.
Clicking will open the Restore wizard. We then clicked to execute the restoration process, and toggled the message logs in our example below:
wbcopytables is a command line utility included in MySQL Workbench that allows you to copy table data from a supported source database server to MySQL. It is used by the Workbench Migration wizard to copy data after the schema is migrated and created in the target MySQL server.
wbcopytables can connect to the source database using either ODBC, the Python DBAPI, or the native MySQL client library.
The copy is performed by executing a SELECT
statement on the source database, and then INSERT
inserts the retrieved rows to the target MySQL server. Several
options allow you to control the data that is copied.
Table 5.1 File Location (Default)
| Operating System | Location |
|---|---|
| Linux | /usr/bin/wbcopytables |
| OS X | /Applications/MySQLWorkbench.app/Contents/MacOS/wbcopytables |
| Windows | C:\Program Files (x86)\MySQL\MySQL Workbench
6.2\wbcopytables.exe |
Options for the source connection are:
--odbc-source=ODBC_connection_string:
The syntax of the ODBC connection string uses standard ODBC
syntax. You can also use a ODBC data source name (DSN).
--mysql-source=MySQL_connection_string:
Use for MySQL sources (when doing a MySQL to MySQL
migration/copy). It uses the same syntax as the MySQL Utilities:
For TCP/IP connections:
username[:password]@host:portFor local socket connections:
username[:password]@::socket_path
You can pass the connection password by using the
--source-password option.
For the target connection, the option is:
--target=MySQL_connection_string.
You can use the --passwords-from-stdin option to
pass a passwords through STDIN. Source and target passwords must
be separated by a tab character.
You can use ODBC specific data source options from the source
RDBMS to specify the number of rows to fetch at a time for the
source SELECT statement.
One or more tables can be specified in the command line for the copy operation. There are two copy types:
Full table copy:
--tableRange copy:
--table-range
Both table copy types require a set of common arguments:
Source schema: The schema/catalog the table belongs to. If quoting is required, it must be done using the syntax from the source RDBMS. For example, SQL Server uses [square_brackets].
Source table: The table to copy. If the source RDBMS uses a schema name in addition to a catalog, both schema and table must be specified here and separated by a dot. For example, [dbo].[mytable].
Target schema: The name of the MySQL schema. If quoting is needed, it must use the MySQL backtick syntax. For example,
`sakila`.Target table: The name of the MySQL table.
Select expression: The list of fields to
SELECT. This will be inserted verbatim into the sourceSELECTstatement.CautionUse caution as this expression is copied directly into the source
SELECTstatement.
For the select expression, if both the source and target tables
have the same fields in the same order, and use compatible types,
you can simply pass * here, which will build a
query like "SELECT * FROM [dbo].[mytable]".
If not, you can specify the fields as you would in the
SELECT statement, which are comma (,) separated
and with proper escaping/quoting specific to the source RDBMS. You
can also specify typecasts and/or data conversions that the source
RDBMS supports. For example:
[client_id], [name], [address], AsText([location])
Because each option must be interpreted as a single option by the wbcopytables command, you must perform OS shell specific quoting whenever necessary. Usually, quoting your parameter values with 'single' or "double" quotes is enough. This is in addition to any database specific quoting you use.
Full Table Copy
This performs a full SELECT on the source
table, fetches records, and then inserts them into the target
table.
There are no additional arguments required.
The --table syntax is as follows:
--table Source_Schema Source_Table Target_Schema Target_Table Select_Expression
Range Copy
This performs a SELECT copy on the source table
for the specified range. The table must have a numeric
UNIQUE NOT NULL or PRIMARY
KEY that is used to create a WHERE
expression for the range.
The --table-range syntax is as follows:
--table-range Source_Schema Source_Table Target_Schema Target_Table Select_Expression Source_Key Range_Start Range_End
The generated expression is:
key_column >= range_start AND key_column <= range_end
If you specify -1 for Range_End, then the expression will be:
key_column >= range_start
--thread-count=Number: If you are copying more than one table, you can use this option to divide the tables across several threads. There is no support for dividing a single table across many threads.--count-only: Only perform aCOUNT(*)of theSELECTwhich would be generated by the--tableoption that was used. The target schema and table can be omitted in this case.--truncate-target: Execute a TRUNCATE TABLE command on each target table that is copied.
Because there is no way to temporarily disable triggers in MySQL
and they can affect the copy process, MySQL Workbench will backup and
drop all triggers from the target MySQL database before the copy
process starts, and then these triggers are restored after the
copy finishes. The triggers are backed up in the target schema
under a table named wb_tmp_triggers.
--disable-triggers-on=Schema_Name: Performs the backup and DROP process for all triggers in the specified schema.--reenable-triggers-on=Schema_Name: Restores triggers previously backed up to thewb_tmp_triggerstable.--dont-disable-triggers: Bypass the trigger disabling step.