Moving connections and instances between two computers

Question

I´ve got a mysql-server that I´m administrate remotely with MySQL Workbench.

Now I´ve got a new computer and I cant find any solution to move my connections and instances-profiles to my new computer. This can´t be an unsolved question, huh? Not the first time this would happen for someone else.

Correction: It´s not the server-instances that I want to move. I need to export/move/backup my many client-profiles/instances-connections in MySQL Workbench.

Lungworm

Answer 1

I had the same questions. I found a MySQL directory in %APPDATA%. Copy the entire directory to the same location on the new machine. You'll need clear your passwords and re-enter them. Once I did that, I was up and running again.

You can find your %APPDATA% folder in Windows by entering it in the address bar of Windows Explorer.

Waggon

You won't need the whole file, just connections.xml. You'll still need to enter in the passwords again for each connection. – Valletta

It doesn't look like you need to re-enter passwords anymore in version 6.0.8, awesome! – Nelsen

Windows 7 Location: C:\Users[user-name]\AppData\Roaming\MySQL\Workbench\ssh – Dibbell

On Linux the configuration are in ~/.mysql/workbench/ – Brittenybrittingham

@Nelsen apparently, in 6.3 need for re-entering password comes back. – Swatter

Super lame they made you have to reenter passwords, this makes me not want to ever upgrade again, my work copy of MySQL workbench has so many passwords saved... – Nelsen

Thanks. Didn't even need to retype the passwords. – Papule

Answer 2

You don't need to copy any files manually as other answers suggest. On both Windows and Mac you can export all your settings within MySQL Workbench and restore to another system.

Select Tools > Configuration > Backup Connections

This will export as a .zip. Then on your new new install just

Select Tools > Configuration > Restore Connections

That's it!

Flagman

@ Spaceships , @Glyn Jackson hi, I wander that how to execute the command in the windows.Thanks – Pithecanthropus

sadly, it does not backup your passwords – Erastus

What I got by trying this: Error during "Restore connections from a backup file". error calling Python module function PyWbUtils.restoreConnections – Younglove

@Younglove i have the same problem, Workbench version 8.0.25 – Madelene

@Younglove Same issue. Looks like its been a bug since Feb 2021 – Tunstall

Bug here: bugs.mysql.com/bug.php?id=103579 – Qktp

I had the same problem while migrating from Windows to Windows and I managed to work around the Python error. Here is what I did: Downgraded the workbench on both source and target computers to 8.0.12. Created a backup zip on the source computer, restored it on the target computer, and then updated the workbench on target to the latest. – Qktp

Answer 3

Found it on a mac in /Users/Username/Library/Application Support/MySQL/Workbench/

file called connections.xml

Perjury

Thanks! I was able to copy the connections.xml and server_instances.xml from a PC to a Mac, and all my connections from the PC were then available on my Mac. – Bowl

On Mac OS X High Sierra & MysqlWorkbench 6.3, the file ~/Library//Application Support/MySQL/Workbench/connections.xml is empty. – Amanda

Answer 4

Backup and restore connections using the menus Tools > Configuration > Backup Connections and Tools > Configuration > Restore Connections is the easiest way, however it does not copy the passwords.

Extracting the passwords is possible in the following case:

1. Old PC should be a Windows installation.
2. You should be able to login using the Windows account who originally saved the passwords in Workbench, i.e. without having the Windows account's password reset by an admin.

If the above requirements are met, one can log into the old PC and run the decrypt tool found on http://www.donationcoder.com/forum/index.php?topic=41860.msg391762#msg391762

The C++ code to decrypt is shown below (credits: f0dder)

std::vector<unsigned char> decrypt(BYTE *input, size_t length) {
        DATA_BLOB inblob { length, input };
        DATA_BLOB outblob;

        if (!CryptUnprotectData(&inblob, NULL, NULL, NULL, NULL, CRYPTPROTECT_UI_FORBIDDEN, &outblob)) {
                throw std::runtime_error("Couldn't decrypt");
        }

        std::vector<unsigned char> output(length);
        memcpy(&output[0], outblob.pbData, outblob.cbData);

        return output;
}

Incumber

Answer 5

In Linux (Ubuntu), the location was changed to $HOME/.mysql/workbench

Tested on Ubuntu 14.04 LTS.

Venation

Verified on Ubuntu 18.04.1 – Actinium

For me it was gedit ~/snap/mysql-workbench-community/current/.mysql/workbench/connections.xml on Ubuntu 20.04. – Reft

Answer 6

Before replacing the connection.xml, make sure you close the Workbench application first, or the application will overwrite/save on the xml file on exit.

Electorate

heh glad you wrote it :) – Millrun

Answer 7

just copy them!

in Linux, they're at $HOME/.mysqlgui/

Luxurious

Answer 8

If you want to recover the passwords that are encrypted in "workbench_user_data.dat" file using Python in Windows, you can use the below snippet.

import os,win32crypt
encrypted_data = open(f"C:\\Users\\{os.getlogin()}\\AppData\\Roaming\\MySQL\\Workbench\\workbench_user_data.dat", "rb").read()
clear_data = win32crypt.CryptUnprotectData(encrypted_data, None, None, None, 0)
print(clear_data)

Necessarily

Please explain what your code is doing and why it answers the question – Unaccountable

Answer 9

Sadly, on the latest version of the MySQL Workbench (8.0.25, 8.0.27 in windows at least), the backup and restore for .zip file formats does NOT work.

See here for details and a workaround: https://bugs.mysql.com/bug.php?id=102501

Tiemannite

Answer 10

With C# on .NET Framework 4.x on Windows, or with .NET Core - or .NET 5 or later using the Windows Compatibility Pack, just copy and paste this into a new console project:

using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;

static class Program
{
    static void Main()
    {
        String appDataRoaming = Environment.GetFolderPath( System.Environment.SpecialFolder.ApplicationData );
    //  String appDataRoaming = @"C:\Users\$you\AppData\Roaming\";

        FileInfo workbenchUserDataFileName = new FileInfo( Path.Combine( appDataRoaming, @"MySQL\Workbench\workbench_user_data.dat" ) );

        Byte[] ciphertext = File.ReadAllBytes( path: workbenchUserDataFileName.FullName );
        Byte[] cleartext  = ProtectedData.Unprotect( ciphertext, optionalEntropy: null, DataProtectionScope.CurrentUser );
        
        String text = Encoding.ASCII.GetString( cleartext ); // <-- You may need to change this to `Encoding.UTF8`
        text = text.Replace( '\u0002', '\n' ).Replace( '\u0003', '\n' );
        Console.WriteLine( text );
    }

}

Cow

Answer 11

I found the file "WbProfiles.xml" in path C:\Users\.sqlworkbench\WbProfiles.xml

Either copy the file or copy complete ".sqlworkbench" folder under same path in new machine.

Nervine

Answer 12

BACKUP work-around for this bug:

• copy these two files: connections.xml and server_instances.xml
• location of these files (in windows) is: C:\Users<user>\AppData\Roaming\MySQL\Workbench (replace with your own windows username)

To RESTORE: copy those above 2 files to the same location on the destination installation.

caveat: I don't know where the password "vault" is kept for the connections, so you may have to re-enter those, but the connections and server list are preserved.

Bonus: to restore your workspaces (all open .sql tabs, etc.) you can also copy the "sql_workspaces" directory underneath the "workbench" directory and it will restore those too!

Tiemannite