C语言接口远程读取MySQL数据实例

　　之前在服务器上搭建了VPN之后，顺便写了一个php的注册页面用于注册VPN的用户，使用MySQL保存用户数据。而pptpd和l2tpd服务则在另一台服务器运行，且通过文件进行用户验证。因此需要将用户密码表单远程从MySQL中取出并在VPN服务器保存为文件。我的解决方案是使用C语音调用MySQL接口然后使用文件流保存，这样的方案同样可用于解决架设多节点的VPN服务。

实例代码如下：

#include<iostream>
#include<fstream>
#include<time.h>
#include<unistd.h>
#include<mysql/mysql.h>

using namespace std;

int main(int argc,char *argv[])
{
	//获取当前时间
	time_t tval;
	struct tm *now;
	tval = time(NULL);
	now = localtime(&tval);
	char str[500];
	int i;

	MYSQL my_connection;
	MYSQL_RES *result;
	MYSQL_ROW row;
	int res;
	mysql_init(&my_connection);
	//创建MySQL连接
	if (mysql_real_connect(&my_connection, "88.88.88.88","user", "password", "database", 0, NULL, 0)) //在此确认您的SQL服务器地址，用户名，密码，库实例名称
	{
		//前台运行时输出连接数据库的时间
		printf("[%04d%02d%02d%02d%02d%02d] Connection success\n",now->tm_year+1900, now->tm_mon+1, now->tm_mday, now->tm_hour, now->tm_min, now->tm_sec);
		//读取有效期大于目前时间的记录
		res = mysql_query(&my_connection, "select userid,user_pass from usertable where validtime > now()");
		if(res == 0)
		{
			//定义文件输出
			ofstream ofs1("./outfile.txt");
			if(!ofs1)
			{
				cout << "Create file failed!" << str << endl;
				mysql_close(&my_connection);
				return 0;
			}
			result = mysql_store_result(&my_connection);
			//从数据库查询结果集中逐条读取记录
			while ((row = mysql_fetch_row(result)))
			{
				//存入文件并打印到前台(输出格式配合chap-secrets
				cout << row[0] << "\t" << "*" << "\t" << row[1] << "\t" << "*" << endl;
				ofs1 << row[0] << "\t" << "*" << "\t" << row[1] << "\t" << "*" << endl;
			}
			ofs1 << endl;
			ofs1.close();
			cout << "write file over." << endl;
			mysql_free_result(result);
		}
		else
		{
			cout << "mysql_query return " << res << endl;
		}
		mysql_close(&my_connection);
	}
	else
	{
		printf("[%04d%02d%02d%02d%02d%02d] Connection failed\n",now->tm_year+1900, now->tm_mon+1, now->tm_mday, now->tm_hour, now->tm_min, now->tm_sec);
	}
	return 0;
}

#include<iostream>

#include<fstream>

#include<time.h>

#include<unistd.h>

#include<mysql/mysql.h>

using namespace std;

int main(int argc,char *argv[])

{

//获取当前时间

time_t tval;

struct tm *now;

tval = time(NULL);

now = localtime(&tval);

char str[500];

int i;

MYSQL my_connection;

MYSQL_RES *result;

MYSQL_ROW row;

int res;

mysql_init(&my_connection);

//创建MySQL连接

if (mysql_real_connect(&my_connection, "88.88.88.88","user", "password", "database", 0, NULL, 0)) //在此确认您的SQL服务器地址，用户名，密码，库实例名称

{

//前台运行时输出连接数据库的时间

printf("[%04d%02d%02d%02d%02d%02d] Connection success\n",now->tm_year+1900, now->tm_mon+1, now->tm_mday, now->tm_hour, now->tm_min, now->tm_sec);

//读取有效期大于目前时间的记录

res = mysql_query(&my_connection, "select userid,user_pass from usertable where validtime > now()");

if(res == 0)

{

//定义文件输出

ofstream ofs1("./outfile.txt");

if(!ofs1)

{

cout << "Create file failed!" << str << endl;

mysql_close(&my_connection);

return 0;

}

result = mysql_store_result(&my_connection);

//从数据库查询结果集中逐条读取记录

while ((row = mysql_fetch_row(result)))

{

//存入文件并打印到前台(输出格式配合chap-secrets

cout << row[0] << "\t" << "*" << "\t" << row[1] << "\t" << "*" << endl;

ofs1 << row[0] << "\t" << "*" << "\t" << row[1] << "\t" << "*" << endl;

}

ofs1 << endl;

ofs1.close();

cout << "write file over." << endl;

mysql_free_result(result);

}

else

{

cout << "mysql_query return " << res << endl;

}

mysql_close(&my_connection);

}

else

{

printf("[%04d%02d%02d%02d%02d%02d] Connection failed\n",now->tm_year+1900, now->tm_mon+1, now->tm_mday, now->tm_hour, now->tm_min, now->tm_sec);

}

return 0;

}

Makefile文件内容如下：

all:
	c++ usercheck.cpp -ousercheck -lmysqlclient

1 2	all: c++ usercheck.cpp -ousercheck -lmysqlclient

然后只需要再写一个shell脚本添加到 crontab 定时运行该程式并将输出的文件移动为 chap-secrets 即可。

#!/bin/sh

cd /home/test/
./usercheck
if [ -f outfile.txt ]
then
mv ./outfile.txt /etc/ppp/chap-secrets
fi

#!/bin/sh

cd /home/test/

./usercheck

if [ -f outfile.txt ]

then

mv ./outfile.txt /etc/ppp/chap-secrets

简易的VPN用户管理系统就大功告成了~

======
kujou_rin

2 thoughts on “C语言接口远程读取MySQL数据实例”

大宝表示:

2015/06/0214:22

RO论坛无法修改密码
同时申请找回密码
zpjdb2
zpjdb3

回复
1. 九条凛表示:
  
  2015/06/0216:42
  
  你好，论坛和游戏是不同的网站。游戏的用户密码在ro.x777.co进行维护，论坛的用户密码需要在x777.co进行维护。
  
  回复