Basic help on using HeidiSQL
This document aims to give users some basic help to get started with HeidiSQL. Over the years, the feature list has grown longer and longer. So, especially new users sometimes don't know where to look at for particular things. In such cases, you can watch out here for a first help. If you don't find what you're looking for, please register in the forum and post a question.
HeidiSQL runs fine on Windows XP, Vista and 7. Running HeidiSQL on Wine/Linux also works fine.
On Windows 2000 you might run into some "illegal function call into KERNEL.DLL", which is caused by the newer libmysql.dll which dropped Win2k support. You can fix that by placing this older one into the HeidiSQL directory, overwriting the original.
HeidiSQL does not run on Windows 95/98 or ME, as the Unicode extensions are not available on these systems.
Connecting to a server
HeidiSQL is a so called client application, only usable when you have some server available. So, make sure you have some MySQL, MS SQL or PostgreSQL server to connect to.
A simple setup is to have a MySQL server installed on localhost (equivalent to the special IP address 127.0.0.1). In HeidiSQL's session manager, you click on the "New" button to create a new connection, and most default settings are already set for you, except from the password, which is mostly not an empty one on a newly installed MySQL server:
You can organize your stored sessions in folders. To create a folder, click the dropdown arrow on the "New" button, then click "Folder in root folder" or "Folder in selected folder". Once you have a folder, you can create connections in it, or drag existing connections into that folder.
Setting up a SSH tunnel connection to MySQL
If your MySQL server is located on a remote machine which is only accessible via SSH, then you still can use HeidiSQL to connect to it. You just need the additional plink.exe from the PuTTY project, place it somewhere on your harddisk, and finally tell HeidiSQL where it is and the SSH credentials plus the MySQL credentials.
Note that the default host name for the SSH server is the one you entered in the "Settings" tab. HeidiSQL then advices plink.exe to connect to that host name, or, when you entered a SSH host name, that one is taken. Additionally, the host name on the "Settings" tab is always taken for the -L (listen) option in plink.exe.
- "Settings" tab:
- Hostname: "127.0.0.1"
- Password: [your mysql password]
- Port: "3306" in most cases
- "SSH tunnel tab:
- SSH Host: [your server name]
- Port: "22" in most cases
- Username: [your ssh user]
- Password: [your ssh password]
- Local port: "3307"
The following error, or a similar one, is mostly caused by a tunnel onto the official IP address of your remote server:
Lost connection to MySQL server at 'reading initial communication packet', system error: 0 "Internal error/check (Not system error)"In such cases, ensure you're using "127.0.0.1" in Settings > Hostname/IP, and the remote IP of your server in SSH tunnel > Host.
Command line switches
Although HeidiSQL is a pure GUI application, it can be automated for connecting and opening files via command line parameters. Parameter names are case sensitive and are based on those used by the MySQL command line applications, e.g. mysqldump. Be sure to call HeidiSQL with its full file name ("heidisql.exe"), not with the short version ("heidisql"). HeidiSQL's command line parser expects that this way. This should be fixed in the future.
|Short switch||Long switch||Description||Default value|
Network protocol type:
0 = MySQL (TCP/IP)
1 = MySQL (named pipe)
2 = MySQL (SSH tunnel)
3 = MSSQL (named pipe)
4 = MSSQL (TCP/IP)
5 = MSSQL (SPX/IPX)
6 = MSSQL (Banyan VINES)
7 = MSSQL (Windows RPC)
8 = PostgreSQL
|-S||--socket||Socket name, for connecting via named pipe|
|-W||--winauth||Use Windows authentication: 1 or 0. (MSSQL only). Added in r5108.||0|
|--ssl||Use SSL. (1=yes, 0=no)||0|
|--sslprivatekey||SSL private key|
|--sslcacertificate||SSL CA certificate|
|--psettings||Custom filename for portable settings. Ignored if file does not exist.||portable_settings.txt (if that file exists)|
- Start over using stored settings from session "xyz":
- c:\path\to\heidisql.exe -d=xyz
- c:\path\to\heidisql.exe -description=xyz
- Connect with different username or port:
- c:\path\to\heidisql.exe -d=xyz -u=OtherUser
- c:\path\to\heidisql.exe -d=xyz -P=3307
- Connect to a non-stored session:
- c:\path\to\heidisql.exe -h=localhost -u=root -p=Mypass -P=3307
- Open multiple .sql files in query tabs:
- c:\path\to\heidisql.exe fileA.sql path\to\fileB.sql fileC.sql ...
- Use custom portable settings file:
- c:\path\to\heidisql.exe --psettings=c:\temp\p.txt
The database tree
When you have a large amount of tables, views or whatever in your database(s), you probably want to group these by their type, for a better overview. Just right click the tree and activate Tree style options > Group objects by type:
You can also mark important items as so called favorites, by mouse click on the very left area of a table. Afterwards, you can limit the tree to show only favorites by a click on the new "Show only favorites" button at the top:
Creating a table
HeidiSQL comes with a feature-rich GUI for creating and editing a table structure. Just right click the datatabase in which you want to create a table, then point on "Create new", then click "Table":
Done that, you'll see the table editor like in the following picture:
Creating a view
Creating a stored procedure
Just right click the datatabase in which you want to create a procedure, then point on "Create new", then click "Procedure" or "Function". Done that, you'll see the procedure editor like in the following picture:
Creating a trigger
Creating a scheduled event
The Data tab
On the data tab, the contents of the currently selected table or view are displayed. This is one of the most useful and powerful features of HeidiSQL. You will see different colors for the various groups of data types. These colors are customizable in Tools > Preferences > Data appearance.
Pressing F2 or one-long-click in a grid cell will start the editor mode. This will allow you inserting ordinary values into a row. For inserting special values, such as SQL functions, NULL or GUIDs, right click a cell, and point to the Insert value > submenu.
Quick filters: Right click a value in the grid, then click Quick filter to get various one-click options to create a WHERE clause on the grid values. This filter can be base on either the focused cell in the grid, a prompted value, or on the contents of your clipboard.
In the Quick filter sub menu, you will find a More values sub-sub-menu. Pointing to that menu, HeidiSQL quickly collects and displays the top 30 items in the focused column, grouped by their value:
Finding specific values in such a grid can be a pain. For a simple client side filter, you can enter some value in the filter panel. Activate it in Edit > Filter panel (Ctrl+Alt+F):
HeidiSQL can also assists you with a Search and replace dialog (Find mode: Ctrl+F, Replace mode: Ctrl+R). That dialog can be used on SQL query tabs too.
Binary values, also called BLOBs, are normally displayed in hexadecimal format, which is mostly unreadable for a human being. So, HeidiSQL offers to display such contents as text instead of hex. You will want need to know which button to press in order to achieve this. See it in the following picture:
Probably you have a table with one or more integer columns which represent UNIX timestamps. HeidiSQL can display such integer columns as date/time values, so you can better read them:
Running SQL queries
HeidiSQL has a "Query" tab by default. You can create more than this default one by pressing Ctrl+T, or by right clicking the main tabs, then click "New query tab". In such a query tab, you can write your own database queries, or load a .sql file from your harddisk. Pressing F9, or the button with the blue "play" icon on it executes your query or queries.
HeidiSQL can execute a batch of queries (= multiple queries, separated by semicolon) in one go. That way, execution gets dramatically faster, especially when having tons of mini queries. To activate that "one go execution", just click the drop down menu of the blue "play" button, then click "Send batch in one go":
To see how your query performs in MySQL or MariaDB, you can activate the "Query profile" option in the helpers box on the right. Then, run your query or queries, and see what the profile timings show. This is basically what SHOW PROFILE in MySQL 5.0.37 and later releases does.
HeidiSQL supports parameterized SQL queries: Activate it per click on the checkbox "Bind parameter", and
start writing a query with parameters, e.g.
The first part of the bind parameter implementation was done by Adrian Granger.
HeidiSQL can generate nice SQL export files for you. This is basically what mysqldump also can do like a genius. However, you can also tell HeidiSQL to put the results of the export into
- an .sql file
- a ZIP compressed .sql file
- into your clipboard
- another database on the same server
- a new or existing database on a server on which you have a configured HeidiSQL session
Controls whether to a) drop an existing database on the target server first, and b) create it.
This is meant for the target server, not the source server!
- Same as above, just for tables, views, procedures etc. When checked, the table(s) will be dropped first, and/or created afterwards.
- How the rows are created on the target server. Select "No data" to make a structure-only export. Default is "INSERT".
- Max INSERT size
- Defines the maximum size of the extended INSERT statements, which can hold multiple rows in one statement. Note that a MySQL server limits the size of a query sent to the server by the server variable max_allowed_packet. Be sure to use a lower value in this dialog, otherwise MySQL will kill your connection when importing that file.
- Either select a filename (zip compressed or not), a directory in which HeidiSQL will place .sql files, clipboard, another database or a configured HeidiSQL (and a database name below).
- Filename, Directory, or Database
- Depends on what you selected in the "output" pulldown menu.
If HeidiSQL finds a "portable_settings.txt" (or the custom filename as noted above) it starts in portable mode. Which means basically that all settings are restored from that file and when exiting stored again into that file.
When you download the portable zip, that "portable_settings.txt" needs to be manually copied from your old directory, overwriting the empty default file.
Probably HeidiSQL saved you a lot of time and you like it. In this case you may make a donation here.
Author: Ansgar Becker (Germany)
Former development contributors:
- David Dindorp (Denmark)
- Adrien Granger (France)
- Francisco Ernesto Teixeira (Brazil)
Third party components and graphics:
- Sven Lorenz (graphics)
- Mike Lischke + Timo Tegtmeier (VirtualTreeView, GLPL)
- Mathias Rauen (madExcept)
- Maël Hörz (SynEdit, MPL)
- Serhiy Perevoznyk (JumpList)
- Jordan Russell (Inno Setup)
- Iztok Kacin (Cromis.DirectoryWatch, BSD License)
- Mark James (Silk icons, Creative Commons 2.5)
Thanks to Transifex.com for a free translation account, and the following translators:
- David Rodrigues (initiator and Portuguese)
- Richard van Laak (Dutch)
- Michele Locati (Italian)
- viskubov (Russian)
- Kirill (Russian)
- Francisco Alvarado (Spanish)
- TiGeR (Chinese)
- Matej Szendi (Czech)
- jeff.tu (Chinese/Taiwan)
- Rastislav Janosik (Slovak)
- renaud (French)
- Hichem BOUKSANI (French)
- s.d.w. (Korean)
- Alican Çubukçuoğlu, Murat Doğançay (Turkish)
- MichalDCK (Polish)
- Florin Chis (Romanian)
- udariza (Indonesian)
- Januar (Indonesian)
- Seiji Ogawa (Japanese)
- Микола Ковбаса (Ukrainian)
- mivzakim (Hebrew)
- Martin Bedrač (Slovenian)
- sith (Hungarian)
- Xtreme Power (Norwegian)
- Sasa Kostic (Serbian)
- Morne Gouws (Afrikaans)
- ... and more