Programming

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.

^