Mycli helper – more convenient to connect to MySQL via SSH tunnel

Time:2020-7-6

Mycli helper

github: https://github.com/fengjx/too…

purpose

It is more convenient to use MySQL command line client mycli to connect to remote MySQL through SSH tunnel remote server port forwarding.

The detailed usage of mycli can be found in the official website https://www.mycli.net/

Network topology

Mycli helper - more convenient to connect to MySQL via SSH tunnel

Usually, we can’t connect to the online production environment database. We need a server as a transit server. We can log in to this transit server and access MySQL through the transit server

Environmental dependence

  • python3
  • mycli
  • sshtunnel

Installation dependency

mycli https://www.mycli.net/install

pip install mycli 

sshtunnel https://github.com/pahaz/ssht…

pip install sshtunnel

PS: it is recommended that you use pyenv or other Python dependency management tools to manage your dependencies

configuration file

[user-mysql]
Desc = user database
remote_host=192.168.1.106
remote_port=22
remote_username=fengjx
remote_password=
remote_pkey=~/.ssh/id_rsa
remote_pkey_password=

mysql_host=192.168.1.106
mysql_port=3306
mysql_user=root
mysql_password=1234

Remote is the server configuration forwarded by SSH login proxy port_ Password and remote_ pkey、remote_ pkey_ Password two can choose to use password login or secret key login according to the actual situation.

MySQL is a remote MySQL login configuration

usage method

There is only one optional parameter,-cSpecifies the configuration file path. If not specified, the mycli.ini Configuration file.

python mycli-helper.py -h
usage: mycli helper [-h] [-c CONFIG]

optional arguments:
  -h, --help            show this help message and exit
  -c CONFIG, --config CONFIG
                        Configuration file, which is in the current directory by default mycli.ini

Examples

python mycli-helper.py -c mycli.ini
Load configuration: mycli.ini
Enter the MySQL instance number of the connection
[0] - user mysql, user database
[1] - order mysql, order database
1
connect to order-mysql, bind local port 64069
mycli mysql://root:[email protected]:64069
mysql 5.7.28
mycli 1.20.1
Chat: https://gitter.im/dbcli/mycli
Mail: https://groups.google.com/forum/#!forum/mycli-users
Home: http://mycli.net
Thanks to the contributor - Frederic Aoustin
mysql [email protected]:(none)>
mysql [email protected]:(none)> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set
Time: 0.026s
mysql [email protected]:(none)> use mydb;
You are now connected to database "mydb" as user "root"
Time: 0.004s
mysql [email protected]:mydb> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| t1             |
+----------------+
1 row in set
Time: 0.050s
mysql [email protected]:mydb>
mysql [email protected]:mydb> insert into t1(id, c) values (1, 1024);
Query OK, 1 row affected
Time: 0.013s
mysql [email protected]:mydb> select * from t1;
+----+------+
| id | c    |
+----+------+
| 1  | 1024 |
+----+------+
1 row in set
Time: 0.033s

Recommended Today

Build HTTP service with C + + Mongoose

Mongoose source code address:https://github.com/cesanta/mo… Mongoose user manual:https://www.cesanta.com/devel… Mngoose set up HTTP service #include <string> #include “mongoose.h” using namespace std; static const char *s_http_port = “8000”; static void ev_handler(mg_connection *nc, int ev, void *ev_data) { struct http_message *hm = (struct http_message *) ev_data; if (ev == MG_EV_HTTP_REQUEST) { std::string uri; if (hm->uri.p && hm->uri.p[0] == ‘/’) […]