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.
Because I don't like paying for HostingI used the network at my work, which has a public IP. Here's its structure:
I have access to several computers, more precisely to 192.168.1.2 (aka 192.168.0.2) there is Linux and to 192.168.0.3 with Windows. So, for my application, I chose MySQL and looked at what was available. LinuxIt was already installed there, but no one knew the password, and those who knew had forgotten it (those who worked before me). After finding out that no one needed it, I deleted it and tried to install it again. I ran out of memory, and since fixing this error would require connecting a monitor, keyboard, and mouse to it, I decided to give up. Especially since the machine Windows It's much more powerful, and on top of that, I have it on my home laptop. I won't describe the installation itself, as there are tons of manuals and videos about it. Having installed MySQL on Windows car, I decided to make a backup of the tables from the laptop to the 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 set up a home or business network and turn off the firewall. Windows Otherwise, you won't even be able to ping this machine (let alone 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
The most difficult thing left to do is to do forwarding on the Linux machine (it has two network cards 192.168.1.2 (interface enp3s1) and 192.168.0.2 (interface enp3s0) so that the network cards know to forward them from 192.168.1.2 to 192.168.0.2, and then to ours 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
