Let's start with the fact that there are times when you need to make an application with a database connection. This is done in order not to delve too much into back-end development and concentrate on the front-end due to a lack of hands and skills. I'm not going to say that my solution will be safe, but it works.
Since I don't like to pay for hosting, I used the network at my work, there is a white IP. Here is its structure:
I have access to several computers, more precisely to 192.168.1.2 (aka 192.168.0.2) there is Linux and 192.168.0.3 with Windows. In general, for my application, I chose mysql and looked at what is on Linux. It was already installed there, but no one knows the password, and those who knew forgot (those who worked before me). After learning that no one needs it, I deleted it and tried to install it again. There was not enough memory, and since to fix this error I would have to connect a monitor and a keyboard with a mouse to it, I decided to quit this business. Moreover, a Windows machine is much more powerful, and plus everything, I myself have it on my laptop at home. In principle, I will not describe the installation itself, there are a lot of manuals and videos about it. Having installed mysql on a Windows machine, I decided to backup tables from a laptop to a workstation.
It is done like this (in my case):
mysqldump -uroot -p your_base > dump_file.sql
Next, we create a database in the new database and restore the backup on the “new” machine.
mysql -h localhost -u root -p
create database your_base;
use your_base;
mysql -uroot -p your_base < dump_file.sql
show tables;
The file with the backup must be put on a new machine, and if not in the directory with the utility, then the full path to it is possible. (I just uploaded a backup to github and cloned it to a new machine). I would add how the tables themselves are created, but I didn’t save the screenshots, and I think it’s not difficult even for a 2-3 year student.
When I restored all the tables, it's time to make remote access to the database available. In general, such commands did not lead to success (given only the right to read select)
create user 'client'@'%' IDENTIFIED by 'client';
grant select on your_base . * to 'client'@'%';
flush privileges;
More precisely, I could only connect to the database with the command,
mysql -h localhost -u client -pclient
and this one could not
mysql -h 192.168.0.3 -u client -pclient
It didn't suit me, I also couldn't connect via this address as root.
The mysql workbench program helped, there in the settings you change localhost to% and it works, although the client did not help. Now you can connect to the database from the console or from the code from any address.
You also need to make a home or enterprise network and turn off the Windows firewall, otherwise you won’t even be able to ping this machine (not that it will connect to the database).
Half of the work is done, then I need to be able to connect to the database from home.
As you can see from the network diagram, you need to go to the Internet from 192.168.0.3 to 192.168.1.1 (router), let's go in the opposite direction. Let's set up a route from 192.168.1.1 to 192.168.1.2 like this:
In general, the picture does not show - I will write with my hands:
route add 192.168.0.0 mask 255.255.255.0 gateway 192.168.1.2
This can only be done in one subnet, that is, you cannot immediately forward to the address 192.168.0.2 or 192.168.0.3
This is necessary so that the router knows where the 192.168.0.0/24 subnet is located (learn the basics of networks is useful).
Now we add port forwarding 3306 (mysql port by default (if you didn’t change it during installation)) to the address 192.168.1.2
Now the most difficult thing to do is to do forwarding on a Linux machine (it has two network cards 192.168.1.2 (enp3s1 interface) and 192.168.0.2 (enp3s0 interface) so that the network cards know what to transfer them from 192.168.1.2 to 192.168.0.2, and then to our Windows machine with MySql.
sudo iptables -A FORWARD -i enp3s1 -o enp3s0 -p tcp --syn --dport 3306 -m conntrack --ctstate NEW -j ACCEPT
sudo iptables -A FORWARD -i enp3s1 -o enp3s0 -m conntrack --ctstate ESTABLISHED,RELATED -j ACCEPT
sudo iptables -A FORWARD -i enp3s0 -o enp3s1 -m conntrack --ctstate ESTABLISHED,RELATED -j ACCEPT
sudo iptables -t nat -A PREROUTING -i enp3s1 -p tcp --dport 3306 -j DNAT --to-destination 192.168.0.3
sudo iptables -t nat -A POSTROUTING -o enp3s0 -p tcp --dport 3306 -d 192.168.0.3 -j SNAT --to-source 192.168.1.2
и последняя строчка сохраняет введенные команды чтобы они при перезапуске оси не стерлись
sudo service iptables-persistent save
Those. The 1st line means that we accept the first connection, the 2nd and 3rd that it is possible to send packets in both directions, the 4th and 5th means the replacement of the destination and source address. And voila, you can connect from home via mysql. And finally, my C++ code that does this:
//DataBaseConnection.cpp
#include "DataBaseConnection.h"
DataBaseConnection::DataBaseConnection()
{
}
void DataBaseConnection::Connect()
{
// Получаем дескриптор соединения
conn = mysql_init(NULL);
if (conn == NULL)
{
// Если дескриптор не получен – выводим сообщение об ошибке
fprintf(stderr, "Error: can'tcreate MySQL-descriptorn");
//exit(1); //Если используется оконное приложение
}
// Подключаемся к серверу
if (!mysql_real_connect(conn, "192.168.0.3", "root", "password", "your_base", NULL, NULL, 0))
{
// Если нет возможности установить соединение с сервером
// базы данных выводим сообщение об ошибке
fprintf(stderr, "Error: can't connect to database: %sn", mysql_error(conn));
}
else
{
// Если соединение успешно установлено выводим фразу - "Success!"
fprintf(stdout, "Success!n");
}
}
std::vector<std::string> DataBaseConnection::Query()
{
vectordrum.clear();
std::string query = "SELECT * FROM drum where id=0";
const char * q = query.c_str();
qstate = mysql_query(conn, q);
if (!qstate)
{
res = mysql_store_result(conn);
while (row = mysql_fetch_row(res))
{
//printf("ID: %s,Position: %s, Image: %sn", row[0], row[1], row[2]);
vectordrum.push_back(row[2]);
}
}
else
{
std::cout << "Query failed:" << mysql_error(conn) << std::endl;
}
return vectordrum;
}
void DataBaseConnection::Close()
{
// Закрываем соединение с сервером базы данных
mysql_close(conn);
}
DataBaseConnection::~DataBaseConnection()
{
vectordrum.clear();
}
//DataBaseConnection.h
#pragma once
#include <iostream>
#include <mysql.h>
#include <vector>
#pragma comment(lib,"mysqlcppconn.lib")
#pragma comment(lib,"libmysql.lib")
class DataBaseConnection
{
public:
DataBaseConnection();
void Connect();
std::vector<std::string> Query();
void Close();
~DataBaseConnection();
MYSQL *conn;
MYSQL_ROW row;
MYSQL_RES *res;
int qstate;
std::vector<std::string> vectordrum;
};
Now you can safely throw off this program to anyone, and you do not need to rewrite it so that it works locally.
Source: habr.com