What is a MySQL connection? Why might I need to create more than one?
A MySQL connection links (connects) Workbench to a MySQL server. Most actions performed within Workbench are then performed against the connected MySQL server. Each MySQL connection contains its own set of definitions, so you might define multiple MySQL connections in Workbench. For example, the connections might connect to different MySQL servers, or the same MySQL server with different user names, or enable SSL for one, or you might set up a connection to a remote MySQL server (on your web host?) using the SSH options, and so on.
As for multiple connections to the same local MySQL server, you might have one connection using "root" with another using a less privileged user. Depending on how you set up the users, they may (or may not) both have rights to see and use the same databases (information). For example, you might use Workbench to configure and use the less-privileged user that you use for your web application.
So to summarize, connections simply connect to the MySQL server. If two connections use the same exact information then the results in Workbench will be identical. However, that is not a common use case. For additional information about MySQL connections in MySQL Workbench, see Chapter 5, MySQL Connections.
How do I create a MySQL database (schema) in MySQL Workbench?
Other options include clicking the "Create Schema" icon on the main navigation bar, or executing a "CREATE SCHEMA your_db_name" query in the SQL editor.
Is there an easy way to select all data from a table, and then see the results?
From the schema navigator, hover over the table and click the icon. This executes a "SELECT * FROM schema.table" query and loads the results into the result grid. From there you can view or edit the data.
Alternatively, right-click on a table and select Select Rows - Limit 1000 form the context menu.
How do I use the SSL Certificate wizard to enable SSL for both my MySQL server and MySQL client?
Execute the wizard to generate the SSL certificates, and then
modify your MySQL server's configuration file
Failed SSL connections are logged in the MySQL Workbench log file. For additional information about the log file's location, see Section 3.3, “MySQL Workbench Settings and Log Files”.
For additional information, see Section 5.3.4, “SSL Wizard (Certificates)”.
How do I copy my saved MySQL connections in Workbench to a different computer?
From the main navigation menu, choose Tools, Configuration, and then Backup Connections to create a Zip file with your configured MySQL connections. Next, load this file into your new Workbench instance by using the related Restore Connections option.
How can I view my MySQL Workbench query history?
In bottom pane, change Action Output to History and then choose the appropriate date.
The SQL statement history is stored as plain text on your system
under your user's
MySQL Workbench configuration path in the
Can I preserve a results tab rather than have it refresh every time I execute a statement?
Yes, you can pin the results tab to force it to remain and be unaffected by UPDATE and other statements. Do that by right-clicking the result tab and choose "Pin Tab" from the context-menu, or left-click the little pin icon to toggle it. Now, execute your other queries and then refresh the pinned tab (there is a "refresh" icon in the result grid's menu).
How does the embedded web browser functionality work? For example, clicking Workbench Forum on the Home screen opens the forum in its own embedded MySQL Workbench tab.
The Webkit system library is used on OS X, Internet Explorer is used on Windows, and Linux opens the default browser externally rather than an embedded browser. Pressing Modifier + Arrow moves the browser history forward and back.
Additionally, for information about creating your own Home screen links, see Section 3.4, “Tutorial: Add a Custom Link to the Home Page”.
How does MySQL Workbench increase import performance?
When a model is exported (Database, Forward Engineer...), some MySQL server variables are temporarily set to enable faster SQL import by the server. The statements added at the start of the code are:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
These statements function as follows:
These server variables are then reset at the end of the script using the following statements:
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Why do my query results sometimes say Read Only but other times I can edit data in the results grid?
Data in the query results grid is only editable when the query results includes a primary key. For example, "SELECT type FROM food" will be read-only if "type" is not a primary key, but "SELECT id, type FROM food" will be editable when "id" is a primary key. Typically, "SELECT *" syntax is used in Workbench which often includes query results with a primary key.
For additional information, hover over the "Read Only" icon to reveal a tooltip that explains why your result set is in read-only mode.
I'm attempting to execute a DELETE query but the query fails with an "Error Code: 1175" error. How do I proceed?
By default, Workbench is configured to not execute DELETE or UPDATE queries that do not include a WHERE clause on a KEY column. To alter this behavior, open your Workbench Preferences, select the SQL Editor section, and disable the following preference:
[ ] "Safe Updates". Forbid UPDATEs and DELETEs with no key in WHERE clause or no LIMIT clause.
Changing this preference requires you to reconnect to your MySQL server before it can take affect.
My MySQL server connection is timing out with an error like "Error Code: 2013. Lost connection to MySQL server during query". Can I adjust the timeout?
Yes, go to Preferences, SQL Editor, and adjust the DBMS connection read time out option that defaults to 600 seconds. This sets the maximum amount of time (in seconds) that a query can take before MySQL Workbench disconnects from the MySQL server.
What do the column flag acronyms (PK, NN, UQ, BIN, UN, ZF, AI) in the MySQL Workbench Table Editor mean?
Checking these boxes will alter the table column by assigning the checked constraints to the designated columns.
How do I import comma-separated values (CSV) data into MySQL using Workbench?
Importing CSV data into a new or existing table: the Table Data Import wizard imports configurable CSV data into a new or existing table. This option was added in MySQL Workbench 6.3.
Importing CSV data into a result set: the Import records from external file wizard imports CSV data directly into a result set's view.
Alternatively, the Data Import wizard imports your saved MySQL files into your MySQL server. For additional information, see Section 6.5, “Data Export and Import”.
If you are importing Excel files, then consider using the official MySQL for Excel Add-on for Excel.
How do I export MySQL data to a plain text file with a format such as CSV, JSON, or XML?
The results view panel in Workbench has an "Export recordset to an external file" option that exports your result set to a wide variety of formats. For additional information, see Export a Result Set.
This is different than the Data Export wizard that exports your MySQL data to standard MySQL formats. For additional information about that, see Section 6.5, “Data Export and Import”.
If you are exporting to Excel, then consider using the official MySQL for Excel Add-on for Excel.
How to export (save) a MySQL database to a text file?
Open a MySQL connection, and select Server from the main navigation menu and choose Data Export to open the data export wizard. Alternatively, choose Data Export from the left Management pane for the desired MySQL selection.
Here you can choose which databases to export, whether or not to include the data, dump to a single file or multiple files (one per table), and more. For additional details, see Section 6.5, “Data Export and Import”.
I'm forced to use MySQL Workbench 5.2.x, is its documentation available?
Although the 5.2.x branch is no longer maintained, its documentation is archived at http://dev.mysql.com/doc/index-archive.html.