Buy

Remote › Databases

Premium

The Remote databases page works with the databases on your servers, MySQL, MariaDB or PostgreSQL. You browse tables and edit rows, read the structure, run SQL, export, back up and restore. No tab on SFTP / SSH needs to be open for this.

Nothing is opened to the internet. Every query runs over the server's saved SSH connection: with the database client on the server (mysql, mariadb or psql) or through an SSH tunnel with the client on your Mac. The password never appears on a command line.

What you do here

Page layout

  • Header: Connections leads to SFTP / SSH, Add database creates an entry. Below it the notice Caution: working directly on the server.
  • On the left the Databases list: Search databases…, the projects of your connection list, and in them the servers with their database entries. A plus per server (Add database for …) creates an entry there. Servers without an entry are listed under Servers without a database. The pencil and Remove edit or remove an entry. The list folds away like on the other Remote pages.
  • On the right the chosen entry as a card: in the header the kind, Character set, Refresh, Backup, Restore and Database settings. Below it on the left the choice of database (with its size) and the tables (with an approximate row count, Filter tables…), on the right the views Data, Structure and SQL.

WebDonkey remembers the chosen entry across a restart. If you last opened a server on SFTP / SSH, the list jumps to it.

The same card appears in every tab on SFTP / SSH below the editor when the server has an entry, see The server's database.

Database backups land in WebDonkey's backup folder on your Mac unless you choose otherwise, and they are listed under Remote › Backups.

Free and Premium

Remote databases are part of Premium. Without Premium the page is covered by Remote is a Premium feature with the way to unlock it.

Add a database Premium

A database entry is the saved access to a database on one of your servers. It always belongs to an SSH connection, through which WebDonkey reaches the database. A server can have several entries, for example a MySQL and a PostgreSQL database.

Where to find it

Sidebar Remote › Databases, then Add database at the top right or the plus next to a server in the list on the left.

How to do it

  1. Click Add database. If there is no SSH connection yet, first add a server on SFTP / SSH.
  2. Choose the Server. The database is reached through this SSH connection.
  3. Under Database, choose the kind: MySQL, MariaDB or PostgreSQL. The port switches to 3306 or 5432.
  4. Enter Database host (as seen from the server), usually 127.0.0.1, plus Port, Database user and Database password.
  5. Optional: Database (optional) for a particular database, Display name (optional) for the list.
  6. Click Test access. On success it says Access works with the version of the database server.
  7. Click Save. The entry shows on the left under its server and opens on the right.

Fields and options

Field Meaning
Server the SSH connection through which the database is reached
Database MySQL, MariaDB or PostgreSQL. Default MySQL.
Database host (as seen from the server) Address of the database as the server sees it. Empty means 127.0.0.1.
Port Default 3306 (MySQL, MariaDB) or 5432 (PostgreSQL)
Database user The database user, not the SSH user
Database (optional) a database that is preselected. Empty shows all the user may see.
Database password stored encrypted. Leave it blank when editing to keep the stored one.
Display name (optional) Name in the list. Empty uses the database name or user@host.
Path to the database Automatic, Through the server (its database client) or Through an SSH tunnel (client on this Mac)

Path to the database

  • Automatic uses the database client on the server. If it is missing or cannot sign in, WebDonkey switches to an SSH tunnel with the client on your Mac. Nothing has to be installed on the server.
  • Through the server (its database client) only uses the client on the server.
  • Through an SSH tunnel (client on this Mac) only uses the client on your Mac. You get it by installing MySQL, MariaDB or PostgreSQL under Services.

The tunnel listens on your Mac only, and only while it is needed. When an entry runs through the tunnel, a sentence above the card says so.

Edit and remove

The pencil in the list or the gear Database settings in the card opens Edit database.

  • If you choose another Server there, the entry moves when you save. Password and settings stay, and the path to the database is worked out again for the new server.
  • Remove asks Remove database and takes the entry out of WebDonkey. Nothing is deleted on the server.

Troubleshooting

What you see Why What you do
No database client … on the server mysql or psql is missing on the server Set Path to the database to Automatic
A note about a missing plugin such as caching_sha2_password The client on the server does not match the login method, typically a MariaDB client against MySQL 8 Choose Automatic or install the MySQL client on the server
This Mac has no client for this database The tunnel needs a client on your Mac Install the matching database with Open Services
The server does not allow SSH forwarding AllowTcpForwarding no on the server Have forwarding allowed or use the client on the server
The server cannot reach the database at … Wrong host or port Check host and port, they are meant as seen from the server
Removing is refused A remote backup still backs up this database Take the database out of the backup set under Remote › Backups first

See also

Browse and edit data Premium

The Data view shows a table's rows, page by page, sortable and filterable. You insert, change and delete rows, copy values and build filters with a right-click. Every change takes effect on the server right away.

Where to find it

Sidebar Remote › Databases, choose an entry on the left, then a table in the card and the Data tab. The Database card in a tab on SFTP / SSH has the same view.

How to do it

  1. At the top left of the card, choose the database if the entry shows several. Each name is followed by its size.
  2. Choose a table on the left. Filter tables… searches the list, and the number after a table is its approximate row count.
  3. The rows appear on the right. Without a table it says Pick a table on the left to browse its rows.

Page, sort, filter

  • Rows come in pages. Rows per page sets 50, 100 (default), 250 or 500, Previous page and Next page turn the pages. Next to them you see which rows are shown and how many the table has.
  • A click on a column header sorts by that column, a second click reverses the order.
  • In the WHERE line you type a condition, such as `name` LIKE '%shirt%' (in PostgreSQL "name" ILIKE '%shirt%'), and confirm with Enter. Clear filter resets it. A condition must not contain ;.
  • The info line above the rows lists type, engine, collation, rows, the size of data and indexes, total, the next id, created, updated, the row format and the table's comment, as far as the database knows them.

Insert, change, delete rows

  • Row opens New row. For each column you choose Value, Default (leaves the column to the server) or SQL expression, such as NOW(). Computed columns are marked computed and read-only. Insert saves.
  • Right-clicking a row offers Edit row…, Duplicate row… and Delete row…, for a selection Delete n rows…. While editing, Changed columns keeps count, and Undo change resets a cell.
  • Deleting asks Delete n row(s)? and names the table and the server. Changes and deletions cannot be undone.

After saving, the card briefly reports Saved, Inserted or Deleted with the number of rows.

Copy and filter with a right-click

A cell's context menu also offers Copy value, Copy row(s) as CSV, Copy row(s) as JSON and Copy row(s) as INSERT. Filter: = this value and Filter: ≠ this value add a condition to the filter.

What happens in the background

WebDonkey finds a row by its primary key or by a unique key over columns without NULL. If the key matches more than one row, it changes nothing. You write binary values as hex (0x…).

Troubleshooting

What you see Why What you do
The table carries a note that it has no key and is read-only It has no primary key and no suitable unique key Change rows with SQL
The table carries a note that it is a view and read-only It is a view Edit the underlying table
The row no longer exists Someone deleted or changed it in the meantime Reload the view with Refresh
The filter must be a single condition The filter contains ; Enter a single condition without ;
Umlauts look broken Wrong character set See Character set

See also

Structure Premium

The Structure view shows how the chosen table is built: columns with their types and defaults, indexes and foreign keys. It only reads and changes nothing.

Where to find it

Sidebar Remote › Databases, choose an entry and a table, then the Structure tab. Without a chosen table the tab is disabled.

What the view shows

Section Content
Columns (n) per column Name, Type, Default, Key, Extra, Collation and Comment. Keys are marked as primary key or unique key.
Indexes (n) the index name, its Columns and the Kind
Foreign keys (n) the Columns and References with the target table and columns

If a section has nothing, it says None.

How to do it

  1. Choose the table on the left.
  2. Click Structure.
  3. To change the structure, switch to SQL and write the statement, such as ALTER TABLE.

See also

Run SQL Premium

The SQL tab runs your own statements in the chosen database: queries, changes to data or to the structure. The result appears as a table below the field.

Where to find it

Sidebar Remote › Databases, choose an entry, then the SQL tab. Below the field it says which database the statement runs in.

How to do it

  1. Choose the database at the top left.
  2. Write the statement into the field.
  3. Click Run or press ⌘↵.
  4. The result appears below the field.

The result

  • The result shows the duration in ms, and for changes Rows changed: n.
  • If the statement returns several result sets, the card shows the last one. With Result 1, Result 2 … you switch to the others.
  • Long results are cut, marked first n rows. For all rows use Export with All rows.
  • Without rows it says Done, and that no rows were returned.

Statements that change data

If SQL may change data, WebDonkey first asks Change data on the server? and shows the SQL. After Run it runs right away and cannot be undone. Back up the database first, see Back up a database.

SQL only counts as reading if every statement in it starts with SELECT, SHOW, DESCRIBE, DESC, EXPLAIN, WITH, VALUES, TABLE, HELP or USE. Everything else, such as INSERT, UPDATE, DELETE or ALTER, asks first. Reading SQL runs in a session that may only read.

History

History lists the last 30 statements of this connection. A click puts one of them back into the field.

Troubleshooting

  • Database error with the server's message: the statement is wrong or the user is not allowed to do this. Read the message, it comes from the database server.
  • The tab shows nothing: check that a database is chosen at the top left.

See also

Export Premium

Export writes table data out for other programs, such as a spreadsheet, a ticket or documentation. You choose whether only the shown rows or all rows of the table go out, the format, and whether to the clipboard or to a file.

Where to find it

Sidebar Remote › Databases, choose an entry and a table, Data tab. The Export button sits above the rows. A result in the SQL tab has it too.

How to do it

  1. Open the table, filter and sort it the way you need it.
  2. Click Export.
  3. Choose the scope:
    • Shown rows (n): exactly what the grid shows.
    • All rows: streamed from the server without the row limit, with the current filter and order.
  4. At the format you want, click the copy icon (Copy to clipboard) or the save icon (Save as file…).

Afterwards it says how many rows there were and how large the output is. For a file, Show in Finder opens the folder.

Formats

Format Extension Note
CSV .csv comma as separator
CSV (Excel) .csv semicolon as separator, with BOM, so Excel reads umlauts correctly
TSV .tsv tab as separator
JSON .json a list of the rows
SQL INSERT .sql INSERT statements for the table
Markdown .md a table for documentation
HTML .html an HTML table
XML .xml an XML document

Only Shown rows go to the clipboard. You save All rows as a file.

What happens in the background

With All rows, WebDonkey reads the table straight from the server and writes the file row by row, without holding everything in memory. An export from the SQL tab needs exactly one reading statement for this.

An export is not a backup and cannot be restored. For backups see Back up a database.

Troubleshooting

  • All rows is disabled for an SQL result: it only works for exactly one reading statement with one result set. Reduce the SQL to a single SELECT.

See also

Back up a database Premium

Backup saves a whole database of the server into a file on your Mac. An SQL backup can later go back with Restore, CSV and JSON are exports for other programs.

Where to find it

Sidebar Remote › Databases, choose an entry, then Backup in the card's header. The same button is in the Database card on SFTP / SSH.

How to do it

  1. At the top left of the card, choose the database you want to back up.
  2. Click Backup. The Back up database dialog opens.
  3. Choose the Format (see below). The default is SQL (gzip).
  4. Check File name and Save to.
  5. Click Create backup. The progress shows the phase and the amount, Cancel stops it.
  6. At the end the dialog reports Backup created with the size. Show in Finder opens the folder.

Fields and options

Format

Format Content Restorable
SQL (gzip) complete dump, compressed yes
SQL complete dump as plain text, readable in any editor yes
PostgreSQL archive pg_dump's own format (.dump), compressed. PostgreSQL only. yes, here or with pg_restore
CSV one CSV file per table, packed as ZIP no
JSON all tables in one JSON document no

File name

The name is a pattern. The buttons at the field insert placeholders at the cursor: the name of the Database, the Server, Date and Time. Default name restores WebDonkey's pattern. WebDonkey adds the extension itself. Under File: you see the name the file would get now.

Save to

The default is WebDonkey's backup folder. Choose folder… picks another folder on your Mac, Back to the default folder resets it.

Nothing is overwritten: a name that is taken gets -2, -3 … WebDonkey remembers format, name and folder for this database.

What happens in the background

  • WebDonkey creates the dump over the SSH connection with a backup program that fits the database server (mysqldump, mariadb-dump or pg_dump in at least the server's version), on the server or through the SSH tunnel with the programs on your Mac. It does not use a program of another kind or an older version, because its dumps cannot be restored reliably.
  • If a dump ends before its last line, WebDonkey discards it and keeps no half backup.
  • The backup appears with your other backups under Remote › Backups. CSV and JSON are listed there as exports, because they cannot be restored.

Troubleshooting

What you see What you do
No backup program on the server fits … Set the entry's Path to the database to Automatic or the tunnel. WebDonkey then uses its own programs on your Mac.
No fitting program for … on this Mac Install the matching database under Services, its programs come with it.
The dump is incomplete Back up again. If it stops again, check the connection and the space on the server.
Choose a folder that exists on this Mac. Pick an existing folder or connect the drive.

See also

Restore a database Premium

Restore plays an SQL dump into a database on the server. This overwrites data: tables from the backup replace the current ones in the target database. That is why WebDonkey always backs up the target database first and asks you twice.

Where to find it

Sidebar Remote › Databases, choose an entry, then Restore in the card's header. The same button is in the Database card on SFTP / SSH.

How to do it

  1. Click Restore. The Restore database dialog opens with the warning A restore overwrites data.
  2. Choose the Target database.
  3. Choose the Source:
    • A backup made by WebDonkey: pick it under Choose a backup…. Each line names the label, date, size, format and whether it lies on this Mac or on the server. If there is none, it says No SQL backups yet.
    • A file (.sql, .sql.gz, .dump): pick it with Choose….
  4. If the backup is an archive from Remote › Backups, pick the matching dump in it under Dump in the archive (Choose a dump…).
  5. Click Restore….
  6. The confirmation Overwrite …? names the server, the source, the date and the database the backup comes from. Type the name of the target database into the field Type the name of the target database to confirm.
  7. Click Overwrite. The dialog behind it shows the progress.

No backup is preselected, not even the one of the target database. Which dump goes back is your decision. If the backup comes from another database than the target, a note in yellow says so.

The safety backup

Below the source it says The target database is backed up first. This is not a choice, it always happens. The safety backup is an SQL backup of the current state, listed like any other and marked with ⟲. It lets you undo the restore. Keep it until you have checked the result.

Questions during the run

  • Restore into another version? The dump comes from a server with another version. Across versions a restore can fail or turn out differently (data types, defaults, functions). Restore anyway plays it in.
  • Rewrite DEFINER? Views, routines, triggers or events in the backup run under an account the target cannot create them for. Rewrite DEFINER to … makes them run under the account that restores. The file itself stays unchanged. Cancel restores nothing.

Progress and cancelling

The progress shows the phases Checking file, Fetching the dump from the archive (for archives), Safety backup and Uploading.

  • Cancel stops the upload. What has already reached the server stays applied. Then play in the safety backup to get the previous state back.
  • If the restore fails, WebDonkey puts the database back to the state before from the safety backup (Putting the database back) and names the reason.

At the end it says Restore finished.

What happens in the background

  • WebDonkey reads from the start of the file which server and version it comes from. A backup only goes back into the same kind of database server: MySQL into MySQL, MariaDB into MariaDB, PostgreSQL into PostgreSQL. For another version series the question above appears.
  • A file that switches the database itself, creates one or drops one (such as a mysqldump --databases) is not played in, because it could change another database than the target.
  • Backups with folders and databases are restored through Remote › Backups.

Troubleshooting

What you see What you do
Only SQL backups (.sql, .sql.gz, .dump) can be restored. Choose an SQL backup, CSV and JSON are exports
A message that the file switches the database itself Use a dump of only this database (mysqldump without --databases, pg_dump without -C)
A message that the file is a backup of another kind of database Choose a dump of the same kind
The safety backup failed Nothing was restored. Check space and access, then try again.
This backup belongs to another connection. Restore it as a file (A file)
This archive holds no dump that fits this database. Choose another archive or a file

See also

Character set Premium

Old shops and websites often do not store text as UTF-8. Umlauts then show up broken, such as "ä" instead of "ä". The Character set choice in the card's header sets how WebDonkey reads and writes the data of this database.

Where to find it

Sidebar Remote › Databases, choose an entry. The Character set choice sits in the card's header, next to the kind of database.

How to do it

  1. Open a table with text.
  2. If umlauts look double-encoded, the card shows the note Umlauts look double-encoded. Click Use Latin-1 there.
  3. If � appears, characters could not be decoded. Choose the character set the data was stored in under Character set.
  4. Check the rows. If they look right, the choice stays saved for this entry.

Fields and options

Choice For
Auto (UTF-8) default, for all newer databases
Latin-1 / Windows-1252 Western European text of old shops
Latin-2 (ISO-8859-2) Central and Eastern European text
Windows-1250 Central and Eastern European text (Windows)
Windows-1251 Cyrillic (Windows)
Greek (ISO-8859-7) Greek
UTF-8 (old servers, utf8mb3) older MySQL servers with the old UTF-8
Shift-JIS Japanese
GBK Simplified Chinese
Big5 Traditional Chinese
EUC-KR Korean

What happens in the background

The character set applies to the connection WebDonkey reads and writes with. It does not change the data stored in the database. WebDonkey remembers the choice per database entry.

See also

Cookie preferences

This website uses cookies to ensure the best experience possible. Your choice applies to webdonkey.io and the shop at shop.webdonkey.io. More information...

Technically required

Cookies required for this website to function:

Always on
WebDonkey
WebDonkey
Provider: BuI Hinsche GmbH, Neumarkt 7, 06712 Zeitz, Germany
Purpose: Your consent, colour scheme and language for website and shop, price switches; with wd_debug in the address, the measurement events in the developer console for troubleshooting
Cookies and storage: Cookie wd_consent on .webdonkey.io, shared with shop.webdonkey.io (12 months); cookies wd_theme and wd_lang on .webdonkey.io, shared with shop.webdonkey.io, only when you switch colour scheme or language yourself (365 days); localStorage wd:consent, wd:theme, wd:lang; sessionStorage wd:acc, wd:term, wd:debug (only with wd_debug in the address; until the tab closes)

Statistics

Shows us which pages and products people look at on webdonkey.io and in the shop, where visitors come from and how they get to a purchase (with order number, amount and products, without name or email address). Provider: Google. Transfer to the USA possible (EU-US Data Privacy Framework). Your choice applies to webdonkey.io and the shop together.

Google Tag Manager, Google Analytics 4
Google Tag Manager
Provider: Google Ireland Limited, Gordon House, Barrow Street, Dublin 4, Ireland
Purpose: Loads the services you agreed to, here and in the shop. Sets no cookies of its own.
Cookies and storage: none
Privacy policy
Google Analytics 4
Provider: Google Ireland Limited, Gordon House, Barrow Street, Dublin 4, Ireland
Purpose: Usage statistics: page views, clicks, scroll depth, sources, devices, load times; in the shop also basket, checkout, purchases, registration and sign-in
Cookies and storage: _ga, _ga_<ID> (2 years)
Privacy policy

Marketing

Measures whether our ads on Facebook, Instagram and TikTok lead to visits and downloads on webdonkey.io and to registrations and purchases in the shop, and builds audiences for ads. Providers: Meta and TikTok, with whom we are jointly responsible for this. With this consent Google may also set advertising cookies (_gcl_…). Transfer to the USA and other countries outside the EU possible. Your choice applies to webdonkey.io and the shop together.

Google Tag Manager, Meta Pixel (Facebook, Instagram), TikTok Pixel
Google Tag Manager
Provider: Google Ireland Limited, Gordon House, Barrow Street, Dublin 4, Ireland
Purpose: Loads the services you agreed to, here and in the shop. Sets no cookies of its own.
Cookies and storage: none
Privacy policy
Meta Pixel (Facebook, Instagram)
Provider: Meta Platforms Ireland Limited, Merrion Road, Dublin 4, D04 X2K5, Ireland
Purpose: Ad measurement and target groups on Facebook and Instagram, also in the shop (basket, checkout, purchases, registration)
Cookies and storage: _fbp (3 months), _fbc (3 months, only if you arrive through an ad), fr on facebook.com (3 months)
Privacy policy
TikTok Pixel
Provider: TikTok Technology Limited, 10 Earlsfort Terrace, Dublin, D02 T380, Ireland
Purpose: Ad measurement and target groups on TikTok, also in the shop (basket, checkout, purchases, registration)
Cookies and storage: _ttp, _tt_enable_cookie (13 months)
Privacy policy