mysql c-api使用示例

整理旧资料，收拾新东西，有些db状态不是mysql客户端能模拟出来的，所以记录下打样过程，如果以后有需要也不用从头看起

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <mysql/mysql.h>

MYSQL *conn;

void print_err()

{

fprintf(stderr, "%d %s", mysql_errno(conn),mysql_error(conn));

}

void init_db()

{

conn = mysql_init(NULL);

if(conn == NULL)

{

fprintf(stderr, "mysql_init() failed");

exit(EXIT_FAILURE);

}

mysql_real_connect(conn, "192.168.1.10", "test1234", "test1234", "db1", 3306, NULL, 0);

}

void query(char* sql, char* param1)

{

int ret = -1;

MYSQL_STMT *stmt;

MYSQL_BIND bind[1];

long length;

stmt = mysql_stmt_init(conn);

if(conn == NULL)

{

fprintf(stderr, "mysql_stmt_init() failed\n");

exit(EXIT_FAILURE);

}

ret = mysql_stmt_prepare(stmt, sql, strlen(sql));

if(ret != 0)

{

fprintf(stderr, "mysql_stmt_prepare() failed\n");

print_err();

exit(EXIT_FAILURE);

mysql_stmt_execute(stmt);

}else{

fprintf(stdout, "mysql_stmt_prepare() success\n");

}

if(mysql_stmt_reset(stmt))

{

fprintf(stderr, "mysql_stmt_reset() failed\n");

print_err();

}else{

fprintf(stdout, "mysql_stmt_reset() success\n");

}

ret = mysql_stmt_prepare(stmt, sql, strlen(sql));

if(ret != 0)

{

fprintf(stderr, "mysql_stmt_prepare() failed\n");

print_err();

exit(EXIT_FAILURE);

mysql_stmt_execute(stmt);

}else{

fprintf(stdout, "mysql_stmt_prepare() success\n");

}

memset(bind, 0, sizeof(bind));

/*普通bind*/

bind[0].buffer_type = MYSQL_TYPE_STRING;

bind[0].buffer = (char *)param1;

bind[0].buffer_length = strlen(param1);

ret = mysql_stmt_bind_param(stmt, bind);

if(ret != 0)

{

fprintf(stderr, "mysql_stmt_bind_param() failed\n");

exit(EXIT_FAILURE);

mysql_stmt_execute(stmt);

}else{

fprintf(stdout, "mysql_stmt_bind_param() success\n");

}

/*发送long data*/

// bind[0].buffer_type= MYSQL_TYPE_STRING;

// bind[0].length= &length;

// bind[0].is_null= 0;

// ret = mysql_stmt_bind_named_param(stmt, bind, 1, NULL);

// if(ret != 0)

// {

// fprintf(stderr, "mysql_stmt_bind_named_param() failed\n");

// print_err();

// }else{

// fprintf(stdout, "mysql_stmt_bind_named_param() success\n");

// }

if(mysql_stmt_send_long_data(stmt,0,"MySQL",5))

{

fprintf(stderr, "mysql_stmt_send_long_data() failed\n");

print_err();

}else{

fprintf(stdout, "mysql_stmt_send_long_data() success\n");

}

if(mysql_stmt_send_long_data(stmt,0," - The most popular Open Source database",40))

{

fprintf(stderr, "mysql_stmt_send_long_data() failed\n");

print_err();

}else{

fprintf(stdout, "mysql_stmt_send_long_data() success\n");

}

/***/

ret = mysql_stmt_execute(stmt);

if(ret != 0)

{

fprintf(stderr, "mysql_stmt_execute() failed\n");

print_err();

}else{

fprintf(stdout, "mysql_stmt_execute() success\n");

}

mysql_stmt_close(stmt);

}

void close_db()

{

mysql_close(conn);

}

int main()

{

init_db();

query("select md5(?) from dual", "123");

close_db();

}

gcc -o foo mysql_c_demo.c -I/home/mysql8/mysql8.0.22_run/include -L/home/mysql8/mysql8.0.22_run/lib -lmysqlclient

mysql odbc-bench

很久没有更新了，每天七七八八的琐碎事情占据了所有时间，是不是该暂停一下了 1，安装好unixODBC，安装完之后通过odbcinst -j查看配置文件路径，如下实例

# odbcinst -j

unixODBC 2.3.7

DRIVERS............: /usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini

FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

# cat /usr/local/etc/odbcinst.ini

[MYSQL]

Description = ODBC for MySQL

Driver = /usr/lib/libmyodbc5w.so

Setup = /usr/lib/libodbcmyS.so

Driver64 = /usr/lib64/libmyodbc5w.so

Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

# cat /usr/local/etc/odbc.ini

[tpcc2]

Description = MySQL test database

Driver = MYSQL

SERVER = 10.21.12.235

USER = test1234

PASSWORD = test1234

PORT = 15051

DATABASE = tpcc2

TraceFile = /tmp/odbc.trace

Trace = ON

2，构建和使用odbc-bench git路径是这个https://github.com/openlink/odbc-bench [crayon-676d […]

shell统计日志中的时间戳差值

比如这样的日志，需要统计后一条记录和前一条记录之间的时间差值 [2024-10-11 10:42:24 023047] DEBUG 478832,readermgn.cpp:17:run,tid:0x7f80697fa700,479378,reader mgn running [2024-10-11 10:42:30 329273] DEBUG 478832,readermgn.cpp:17:ru […]

python下的数据库连接池dbutils

最近在测试业务的时候，出现长时间并发线程跑sql的时候建立链接耗时的问题，于是搜罗了一下python下是否有链接池的功能，于是乎找到dbutils这个模块，官方路径是在这里 https://webwareforpython.github.io/DBUtils/ 基础使用相对不麻烦，使用pymysql作为连接器进行演示验证

#coding: utf-8

import pymysql

from dbutils.pooled_db import PooledDB

import threading

from concurrent.futures import ThreadPoolExecutor

pool = PooledDB(

creator=pymysql,

maxconnections=16, # 可同时创建的最大连接数，如果并发很快这里可能会出现超过最大链接数的报错

mincached=4,

maxcached=12,

maxshared=128,

blocking=False,

maxusage=None,

setsession=["set autocommit=0"], #用于设置session的前导语句

ping=1,

reset=True,

# mysql登陆参数

host="192.30.2.19",

port=3306,

user="test1234",

passwd="test1234",

database="mysql",

charset="utf8mb4",

cursorclass=pymysql.cursors.DictCursor, # 这里设置返回cursor类型, DictCursor会返回字典类型，带有字段名

)

def run_test(num):

tid = threading.currentThread().ident

conn = pool.connection()

cursor = conn.cursor()

cursor.execute("begin")

cursor.execute(f"insert into db1.t1(id,tid) values({num}, '{tid}')")

cursor.execute(f"select {num} as num, '{tid}' as tid from dual")

print(cursor.fetchall())

cursor.execute("commit")

conn.close()

for i in range(30):

# 每次最多创建16个线程

threadpool = ThreadPoolExecutor(16)

for j in range(16):

threadpool.submit(run_test, *(i*16 + j,))

threadpool.shutdown(wait=True)

&nbsp […]

docker部署elasticsearch和kibana

1、下载官网镜像因为要在外网隔离环境部署，所以需要将image下载后再搬迁过去 docker pull docker.elastic.co/elasticsearch/elasticsearch:8.14.2 docker pull docker.elastic.co/kibana/kibana:8.14.2 docker image ls docker save da0e65225d10 -o […]

jconsole远程链接

虚虚实实仅记录一下使用方法，往往复复，也不会经常使用到了加上下面的启动参数运行jar包 /opt/TencentKona-21.0.3.b1/bin/java -jar -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9527 -Dcom.sun.management.jmxremote.rmi.port=9 […]

umount -l卸载无响应的nfs挂载

某些时候由于nfs服务卡住或者网络问题导致umount -f也无法卸载时，可以临时使用umount -l模式完成卸载 -l, --lazy Lazy unmount. Detach the filesystem from the filesystem hierarchy now, and cleanup all references to the filesystem as soon as it […]

crc/md5/sha1碰撞数据

为了校验功能，从网上搜罗到的一组碰撞数据 crc32: b5a7b602ab754d7ab30fb42c4fb28d82 d19f2e9e82d14b96be4fa12b8a27ee9f md5: 0x0e306561559aa787d00bc6f70bbdfe3404cf03659e704f8534c00ffb659c4c8740cc942feb2da115a3f4155cbb860749738 […]

mowblog

一群散乱的记忆

作者： mowblog

mysql c-api使用示例

mysql odbc-bench

shell统计日志中的时间戳差值

python下的数据库连接池dbutils

docker部署elasticsearch和kibana

jconsole远程链接

umount -l卸载无响应的nfs挂载

crc/md5/sha1碰撞数据

2024 年 12 月
一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31