首先安装mysql服务器和客户端:
$sudo apt-get install mysql-server mysql-client
期间可能需要设置root密码。根据需要自行设定。
接着,MySQL开发包:
$sudo apt-get install libmysqlclient-dev
首次登录数据库使用root:
$mysql -h localhost -u root -p
输入密码登录后,创建一个数据库n2nvpn_db(含表myclass)和用户eta(密码为etapublic,可远程登录),并使eta对n2nvpn_db有增删查改的权限:
mysql>create database n2nvpn_db;
mysql>create table myclass(id int, name char(20));
mysql>grant select,insert,update,delete on n2nvpn_db.* to eta@"%" Identified by "etapublic";
mysql>flush privileges;
为了让MySQL支持远程登录,需要修改配置文件/etc/mysql/my.cnf。将 "bind 127.0.0.1"这行用#号注释掉:
#bind 127.0.0.1
保存退出。重启mysql:
$sudo /etc/init.d/mysql restart
最后,写一段小程序来测试:
/*
this is a test for mysql.
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>
#define BUFFLEN 1024
#define INSCNT 4
int main(void)
{
MYSQL mysql;
char sqlBuff[BUFFLEN]="";
char *bigThree[] = {"Stalin","Roosevelt","Churchill","Obama"};
mysql_init(&mysql);
if(mysql_real_connect(&mysql, "127.0.0.1",
"eta", "etapublic", "n2nvpn_db", MYSQL_PORT, NULL, 0)==NULL)
{
printf("cannot connect to MySql server!\n");
exit(1);
}
/* 增 */
memset(sqlBuff, 0x00, BUFFLEN);
int i;
for(i=0; i<INSCNT; i++)
{
snprintf(sqlBuff, BUFFLEN, "insert into myclass values(1, '%s');", bigThree[i]);
if(mysql_real_query(&mysql, sqlBuff, strlen(sqlBuff)))
{
printf("insert %s err!\n", bigThree[i]);
}
}
/* 删 */
memset(sqlBuff, 0x00, BUFFLEN);
snprintf(sqlBuff, BUFFLEN, "delete from myclass where name='%s';", bigThree[3]); //删除Obama.
if(mysql_real_query(&mysql, sqlBuff, strlen(sqlBuff)))
{
printf("delete %s err!\n", bigThree[3]);
}
/* 查 */
memset(sqlBuff, 0x00, BUFFLEN);
snprintf(sqlBuff, BUFFLEN, "select * from myclass;");
if(mysql_real_query(&mysql, sqlBuff, strlen(sqlBuff)))
{
printf("select err!\n");
}
char *my_res = mysql_store_result(&mysql);
printf("QUERY:[%s]", my_res);
/* 改 */
memset(sqlBuff, 0x00, BUFFLEN);
snprintf(sqlBuff, BUFFLEN, "update myclass set name='likai' where name='%s';", bigThree[1]); //修改Roosevelt.
if(mysql_real_query(&mysql, sqlBuff, strlen(sqlBuff)))
{
printf("update %s err!\n", bigThree[1]);
}
}
编译运行(libmysqlclient.so的位置可能不尽相同):
gcc testMysql.c -o testMysql -L/usr/lib/i386-linux-gnu/ -lmysqlclient -g
./testMysql
然后登录mysql验证可以看到程序成功执行了增删查改。