Connect to MySQL server in C

Connecting to a mysql database from C is a fairly straightforward process. The following instructions should work on any Linux distro or UNIX computer.

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
32
33
34
35
36
37
38
39
#include <mysql.h>
#include <stdio.h>

main() {
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

char *server = "localhost";
char *user = "USER"; /* Enter your mysql username */
char *password = "PASSWORD"; /* Enter your mysql password */
char *database = "mysql";

conn = mysql_init(NULL);

/* Connect to the mysql database */
if (!mysql_real_connect(conn, server,
user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}

/* send SQL query */
if (mysql_query(conn, "show tables")) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}

res = mysql_use_result(conn);

/* output all table names */
printf("MySQL Tables in mysql database:\n");
while ((row = mysql_fetch_row(res)) != NULL)
printf("%s \n", row[0]);

/* close our connection */
mysql_free_result(res);
mysql_close(conn);
}

MySQL comes with a script called mysql_config. It provides useful information for compiling your MySQL client and connecting it to a MySQL database server. You need to use following two options.

Pass the libs option i.e. ‘Libraries’ to show required Libraries to link with the MySQL client library.

1
$ mysql_config --libs

Output:

1
-L/usr/local/Cellar/mysql/8.0.13/lib -lmysqlclient -lssl -lcrypto

Pass cflags option ‘Compiler flags’ to find include files and critical compiler flags and defines used when compiling the libmysqlclient library.

1
$ mysql_config --cflags

Output:

1
-I/usr/local/Cellar/mysql/8.0.13/include/mysql

You need to pass above two options to your compiler. So to compile above program, enter:

1
$ gcc $(mysql_config --cflags) mysql.c $(mysql_config --libs)

Now execute program:

1
$ ./a.out

Output:

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
32
33
34
MySQL Tables in mysql database:
columns_priv
component
db
default_roles
engine_cost
func
general_log
global_grants
gtid_executed
help_category
help_keyword
help_relation
help_topic
innodb_index_stats
innodb_table_stats
password_history
plugin
procs_priv
proxies_priv
role_edges
server_cost
servers
slave_master_info
slave_relay_log_info
slave_worker_info
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user

You have successfully connected to and retrieved information from your MySQL database from within a C environment.

^