Linux Programming – Part 13: Accessing MySQL

It’s been a while since I last updated the Linux Programming series. Since my passion for Linux is rekindled, it’s time to explore the never-ending story of the Linux world!!

As I mentioned in a previous post, I started working on a new project in April that involves writing numerous bash scripts over the next few months. Given the nature of the work, it also requires a certain level of proficiency in both Linux operating systems and SQL.

Although I find bash scripting to be an enjoyable language to experiment with, I am still eager to gain a more comprehensive understanding of the operating system. To that end, I believe that practicing with the C language would be ideal. While scripting languages like bash and Python have their place, as I mentioned earlier, I am interested in delving deeper into the underlying workings of the operating system as a whole.

And here’s an example of a code accessing the MySQL database on a remote server and inserting the retrieved data into a file.

Coding:

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

int main(int argc, char *argv[]) {
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    char *server = "ip_address";
    char *user = "user_name";
    char *password = "password";
    char *database = "db_name";
    char *query = "SELECT * FROM tbl_name";
    int i, num_fields;
    FILE *fp;

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

    // Execute SELECT statement
    if (mysql_query(conn, query)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(1);
    }

    // Get result set and number of fields
    res = mysql_store_result(conn);
    num_fields = mysql_num_fields(res);

    // Write result set to file
    fp = fopen("output.txt", "w");
    if (fp == NULL) {
        fprintf(stderr, "Unable to create file\n");
        exit(1);
    }
    while ((row = mysql_fetch_row(res))) {
        for (i = 0; i < num_fields; i++) {
            fprintf(fp, "%s,", row[i] ? row[i] : "NULL");
        }
        fprintf(fp, "\n");
    }
    fclose(fp);

    // Free memory and close connection
    mysql_free_result(res);
    mysql_close(conn);

    return 0;
}

To make it compilable, you need to install the libmysqlclient-dev module:

sudo apt-get install libmysqlclient-dev

And to compile it, do it as follows:

gcc -o file_name.c file_name.c -lmysqlclient

MySQL Access:

If you have some trouble accessing MySQL on a remote server, follow the below instructions:

Edit mysqld.cnf.

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Change this line to…

bind-address = 127.0.0.1

A wild card IP address like this one.

bind-address = 0.0.0.0

Restart MySQL server:

sudo systemctl restart mysql

Login to the remote MySQL server as root:

mysql -u root -p

Create a new user from your client PC/Server

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED BY 'password';

Grant permission to the newly created user:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;

Execute the fluxh command to apply the changes:

FLUSH PRIVILEGES;

Allow access to the MySQL server by opening the 3306 port to your specific PC/Server’s IP address:

sudo ufw allow from remote_IP_address to any port 3306

Afterthohgts:

After following the above steps, you’ll likely be able to retries data from your remote MySQL server and insert it into output.txt.

Hope you can enjoy coding in C.

Via:

DigitalOcean