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進行維護。
  
  回覆