MySQL Server Installation

Begin by installing the MySQL database server on your Linux system using the distribution's package manager:

sudo apt update
sudo apt install mysql-server

Once installed, start the MySQL service:

sudo systemctl start mysql

Verify that the service is running correctly:

sudo systemctl status mysql

Instlaling MySQL Development Libraries

To compile C programs that interact with MySQL, install the development headers and libraries:

sudo apt-get install libmysqlclient-dev

Creating a Database Application

Below is a complete C program that demonstrates database connectivity and operations:

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

int main() {
    MYSQL *connection;
    MYSQL_RES *result;
    MYSQL_ROW record;
    
    const char *host = "localhost";
    const char *db_user = "devuser";
    const char *db_pass = "securepass"; // Replace with your actual password
    const char *db_name = "sampledb";   // Replace with your database name
    
    char sql_statement[256];

    // Initialize MySQL connection structure
    connection = mysql_init(NULL);
    if (connection == NULL) {
        fprintf(stderr, "MySQL initialization failed: %s\n", mysql_error(connection));
        return EXIT_FAILURE;
    }
    
    // Establish connection to database server
    if (!mysql_real_connect(connection, host, db_user, db_pass, db_name, 0, NULL, 0)) {
        fprintf(stderr, "Connection error: %s\n", mysql_error(connection));
        mysql_close(connection);
        return EXIT_FAILURE;
    }

    // Create a sample table if it doesn't exist
    snprintf(sql_statement, sizeof(sql_statement), 
             "CREATE TABLE IF NOT EXISTS employees ("
             "emp_id INT AUTO_INCREMENT PRIMARY KEY, "
             "name VARCHAR(100), "
             "department VARCHAR(50), "
             "salary DECIMAL(10,2))");
    
    if (mysql_query(connection, sql_statement)) {
        fprintf(stderr, "Table creation error: %s\n", mysql_error(connection));
        mysql_close(connection);
        return EXIT_FAILURE;
    }

    // Insert sample data into the table
    snprintf(sql_statement, sizeof(sql_statement), 
             "INSERT INTO employees (name, department, salary) "
             "VALUES ('John Doe', 'Engineering', 75000.00)");
    
    if (mysql_query(connection, sql_statement)) {
        fprintf(stderr, "Data insertion error: %s\n", mysql_error(connection));
        mysql_close(connection);
        return EXIT_FAILURE;
    }

    // Query and display the data
    snprintf(sql_statement, sizeof(sql_statement), "SELECT * FROM employees");
    if (mysql_query(connection, sql_statement)) {
        fprintf(stderr, "Query execution error: %s\n", mysql_error(connection));
        mysql_close(connection);
        return EXIT_FAILURE;
    }

    result = mysql_store_result(connection);
    printf("ID\tNAME\t\tDEPARTMENT\tSALARY\n");
    printf("------------------------------------------------\n");
    
    while ((record = mysql_fetch_row(result)) != NULL) {
        printf("%s\t%-15s\t%-12s\t%s\n", record[0], record[1], record[2], record[3]);
    }
    
    // Free result set and close connection
    mysql_free_result(result);
    mysql_close(connection);

    return EXIT_SUCCESS;
}

Compiling the Application

Compile the C program with GCC, linking against the MySQL client library:

gcc -o db_app main.c $(mysql_config --cflags --libs)

For CMake users, create a CMakeLists.txt file with the following content:

cmake_minimum_required(VERSION 3.10)
project(database_app)

# Find MySQL configuration tool
find_program(MYSQL_CONFIG_EXECUTABLE mysql_config)

# Retrieve MySQL compilation flags
execute_process(COMMAND ${MYSQL_CONFIG_EXECUTABLE} --cflags 
                OUTPUT_VARIABLE MYSQL_INCLUDE_DIRS)
execute_process(COMMAND ${MYSQL_CONFIG_EXECUTABLE} --libs 
                OUTPUT_VARIABLE MYSQL_LINK_LIBRARIES)

# Remove whitespace and newline characters
string(STRIP "${MYSQL_INCLUDE_DIRS}" MYSQL_INCLUDE_DIRS)
string(STRIP "${MYSQL_LINK_LIBRARIES}" MYSQL_LINK_LIBRARIES)

# Add executable
add_executable(db_app main.c)

# Apply MySQL compilation flags
target_include_directories(db_app PRIVATE ${MYSQL_INCLUDE_DIRS})
target_link_libraries(db_app PRIVATE ${MYSQL_LINK_LIBRARIES})

Database Setup

Create a dedicated database user with appropriate privileges:

sudo mysql -u root
CREATE DATABASE sampledb;
CREATE USER 'devuser'@'localhost' IDENTIFIED BY 'securepass';
GRANT ALL PRIVILEGES ON sampledb.* TO 'devuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Test the new user connection:

mysql -u devuser -p

Managing MySQL Service

Enabling Automatic Startup

To configure MySQL to start automatically at boot:

sudo systemctl enable mysql

To disable automatic startup:

sudo systemctl disable mysql

Manual Service Control

Start the MySQL service manually:

sudo systemctl start mysql
# Alternative commands:
# sudo systemctl start mysql.service
# sudo systemctl start mariadb.service

Stop the MySQL service:

sudo systemctl stop mysql
# Alternative commands:
# sudo systemctl stop mysql.service
# sudo systemctl stop mariadb.service

Check the current service status:

sudo systemctl status mysql
# Alternative commands:
# sudo systemctl status mysql.service
# sudo systemctl status mariadb.service