Results 1 to 7 of 7

Thread: Connect to remote MySQL database using SSH

  1. #1
    Join Date
    Jan 2015
    Location
    Russia, Moscow
    Posts
    6
    Thanks
    1
    Qt products
    Qt5
    Platforms
    Windows

    Question Connect to remote MySQL database using SSH

    Hello!
    I need your help with Qt database and SSH. I want to establish a connection with remore MySQL database using SSH. For SSH I use libssh and the folloing code:

    Qt Code:
    1. Settings settings; const Settings::SSH &ssh{settings.loadSSH()}; ssh_key key;
    2. int code = ssh_pki_import_privkey_file("key.ossh", ssh.password.toStdString().c_str(), NULL, NULL, &key);
    3. if(code != SSH_OK)
    4. {
    5. _logger->logOnce("Bad private key!");
    6. return false;
    7. }
    8.  
    9. ssh_options_set(_ssh, SSH_OPTIONS_HOST, ssh.host.toStdString().c_str());
    10. code = ssh_connect(_ssh);
    11. if(code != SSH_OK)
    12. {
    13. _logger->logSshErrorOnce("Unable to connect to host!", _ssh);
    14. return false;
    15. }
    16. code = ssh_userauth_publickey(_ssh, ssh.login.toStdString().c_str(), key);
    17. if(code != SSH_AUTH_SUCCESS)
    18. {
    19. _logger->logSshErrorOnce("Unable to authorize!", _ssh);
    20. return false;
    21. }
    22. code = ssh_channel_open_forward(_channel, ssh.host.toStdString().c_str(), 22, "localhost", 3307);
    23. if(code != SSH_OK)
    24. {
    25. _logger->logSshErrorOnce("Unable to create ssh-channel!", _ssh);
    26. return false;
    27. }
    To copy to clipboard, switch view to plain text mode 
    It works and there are no errors. BUT, what I should do next to transfer data from my Qt database to ssh tunnel? May be I need to use something in addition (classes, libs...)?
    If I write after the code above this:

    Qt Code:
    1. const Settings::Database &db{settings.loadDatabase()};
    2. _db = QSqlDatabase::addDatabase("QMYSQL", "my_database");
    3. _db.setPort(3307);
    4. _db.setHostName("localhost");
    5. _db.setDatabaseName("test_db");
    6. _db.setUserName(db.login);
    7. _db.setPassword(db.password);
    8.  
    9. _db.open();
    To copy to clipboard, switch view to plain text mode 

    then Qt says that there is a connection error...
    How to correctly make the connection? Help please.

    PS I dont want to use QProcess and run external tunneler like plink.exe.
    Last edited by _Dron_; 1st February 2016 at 08:56.

  2. #2
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Connect to remote MySQL database using SSH

    Have you verified that the tunnel works?
    E.g. by having your program open the tunnel and then connecting through it with a second program?

    I guess the remote host can resolve its own hostname, but when using SSH on the commandline, the "target" for a forwarding to the login host itself is usually just "localhost".
    Btw, the documentation for the sourcehost argument says "The numeric IP address of the machine from where the connection request originates."

    Cheers,
    _

  3. #3
    Join Date
    Jan 2015
    Location
    Russia, Moscow
    Posts
    6
    Thanks
    1
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Connect to remote MySQL database using SSH

    I dont know works it or not.
    I need to create SSH connection to database. HOW TO DO THIS?
    Noticed that there is no information about it in internet. Like it is a military secret.
    If somebody did this, please help! Give a manual, share your code...

  4. #4
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Connect to remote MySQL database using SSH

    Quote Originally Posted by _Dron_ View Post
    I dont know works it or not.
    The first step of debugging a multi layered problem is to debug its single layers.

    Quote Originally Posted by _Dron_ View Post
    I need to create SSH connection to database. HOW TO DO THIS?
    This is totally irrelevant until you have verified that the tunnel works.

    Cheers,
    _

  5. #5
    Join Date
    Jan 2015
    Location
    Russia, Moscow
    Posts
    6
    Thanks
    1
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Connect to remote MySQL database using SSH

    Even if it works - how to put data from QtDatabase object into SSH tunnel? Then, how to extract data from SSH tunnel and put it into QtDatabase object and after that fetch an answer?

    Have you ever written a SSH connection to remote db? If so, please show an example of your code.
    Thanks.

  6. #6
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Connect to remote MySQL database using SSH

    An SSH Tunnel is indistinguishable from the client and server perspective.

    The client connects to its side of the tunnel like it would connect to the server,
    The server gets connections from its side of the tunnel like it would from the clients.

    And the tunnel doesn't care what it is transporting. It just accepts connections one one end, creates a connection on the other end and then passes data back and forth.

    You can easily try this with a stand-alone SSH client that supports tunnels.

    Cheers,
    _

  7. #7
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: Connect to remote MySQL database using SSH

    In your example, you are trying to open a channel from localhost:3307 to remote:22. MYSQL isn't listening on port 22 on the remote, right? Assuming MYSQL is listening on its normal default port 3306, try this for line 22:
    Qt Code:
    1. code = ssh_channel_open_forward(_channel, ssh.host.toStdString().c_str(), 3306, "localhost", 3307);
    To copy to clipboard, switch view to plain text mode 
    Hope that helps.

    P.S. I should add that you have to write the code that listens on localhost:3307 and read/write data to/from the ssh channel you created. If you don't do this, there will be nobody listening on localhost:3307 and therefore when QSqlDatabase attempts to open the connection, it will fail, etc.

    One option would be to scrap the libssh code altogether and just use QProcess to create the tunnel with the ssh command. If you do that, all you have to do is start the QProcess, then open the MYSQL connection using localhost:3307. The ssh command would be:

    ssh -L <listening port>:<REMOTE_MYSQL_HOST>:<REMOTE_MYSQL_PORT> <SSH_USER>@<SSH_HOST>

    That might be a little easier to implement if you're not comfortable creating the socket, listening for connections, then writing the glue code for the libssh channel you created, etc.
    Last edited by jefftee; 7th February 2016 at 03:21.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!

Similar Threads

  1. How to Connect to MySQL database
    By johnL in forum Newbie
    Replies: 12
    Last Post: 16th May 2015, 17:43
  2. Replies: 2
    Last Post: 16th April 2012, 14:42
  3. Replies: 3
    Last Post: 8th March 2011, 08:57
  4. Questions about connect to remote database
    By stmk in forum Qt Programming
    Replies: 3
    Last Post: 11th November 2010, 11:02
  5. Replies: 2
    Last Post: 15th April 2010, 15:59

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.