MySQL

MySQL Database Administration

The MySQL server comes with the user root , who can do everything on the MySQL server.

sudo mysql

You can define less powerful users who have only the privileges they need to get their jobs done.

Creating the database

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.08 sec)

Once you’ve created the database, the next step is to use it—that is, choose it as the
database you’re working with.

mysql> use test;
Database changed

Understanding Users and Privileges

MySQL users are distinct from the operating system users on the server computer.
When you set up your machine, you automatically create superuser accounts that allow configuration of the server — the root user on a Linux.

Now create a new user called test, we’ll grant this user all privileges on all tables in the database test and assign the password test123:

mysql> GRANT ALL ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test123';

The above means the user has the name test and can connect to the server only from the localhost , the machine on which the database server is installed. There’s a 16-character limit on user names.
The optional IDENTIFIED BY 'test123' component sets the user’s password to test123 .
Let’s experiment with our new user:

$ mysql --user=test --password=test123

Create a table:

CREATE TABLE dns (
device_id SMALLINT(5) NOT NULL DEFAULT 0,
device_name CHAR(128) DEFAULT NULL,
ip CHAR(128) DEFAULT NULL,
owner_id CHAR(128) DEFAULT NULL,
PRIMARY KEY (device_id)
);

mysqladmin

mysqladmin is the primary tool for database administration under MySQL.

MySQL Connector/C

MySQL Connector/C is a client library that implements the C API for client/server communication.

The C API provides low-level access to the MySQL client/server protocol and enables C programs to access database contents. The C API code is distributed with MySQL and implemented in the libmysqlclient library.

To obtain the C API header and library files required to build C API client programs, install a MySQL Server distribution.
You can install a binary distribution that contains the C API files pre-built, or you can use a source distribution and build the C API files yourself.
Typically, the MySQL client library is installed when MySQL is installed.

Installing Connector/C (libmysqlclient) binary

sudo apt-get install libmysqlclient-dev

After installation, use the following to dump the development information:

$ mysql_config
Usage: /usr/bin/mysql_config [OPTIONS]
Compiler: GNU 7.4.0
Options:
        --cflags         [-I/usr/include/mysql ]
        --cxxflags       [-I/usr/include/mysql ]
        --include        [-I/usr/include/mysql]
        --libs           [-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -latomic -ldl]
        --libs_r         [-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -latomic -ldl]
        --plugindir      [/usr/lib/mysql/plugin]
        --socket         [/var/run/mysqld/mysqld.sock]
        --port           [0]
        --version        [5.7.27]
        --libmysqld-libs [-L/usr/lib/x86_64-linux-gnu -lmysqld -lpthread -lz -lm -lrt -latomic -lcrypt -ldl -laio -llz4 -lnuma]
        --variable=VAR   VAR is one of:
                pkgincludedir [/usr/include/mysql]
                pkglibdir     [/usr/lib/x86_64-linux-gnu]
                plugindir     [/usr/lib/mysql/plugin]

An exmplay code:

#include <sys/time.h>
#include <stdio.h>
#include <mysql.h>
#include <m_string.h>

int main(){
    MYSQL  mysql;
    char db_name[]="test", user[]="test", password[]="test123";
    char query[NAME_LEN + 100];
    MYSQL_ROW rrow;
    ulong rowcount = 0L;
    
    /* Initialize global variables, and thread handler in thread-safe programs */
    mysql_init(&mysql);
    
    if (!(mysql_real_connect(&mysql, NULL, user, password,
                           db_name,
                           0, NULL, 0))) {
        fprintf(stderr, " %s\n",  mysql_error(&mysql));
        exit(1);
    }
    
    snprintf(query, sizeof(query), "SELECT COUNT(*) FROM dns");
    if (!(mysql_query(&mysql, query))) {
        MYSQL_RES *rresult;
        if ((rresult = mysql_store_result(&mysql))) {
            rrow = mysql_fetch_row(rresult);
            rowcount += (ulong)my_strtoull(rrow[0], (char **)0, 10);
            mysql_free_result(rresult);
        }
        printf("%10lu rows\n", rowcount);
    }
    mysql_close(&mysql);
    printf("Done\n");
}

Build the code for testing:

gcc mysql.c -I/usr/include/mysql -L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -latomic -ldl  -o mysql_test

Installing Connector/C++ from Source

To clone the Connector/C++ code from the source code repository located on GitHub at https://github.com/mysql/mysql-connector-cpp, use this command:

git clone https://github.com/mysql/mysql-connector-cpp.git

If necessary, use git checkout in the source directory to select the desired branch. For example, to build Connector/C++ 8.0:

cd mysql-connector-cpp
git checkout 8.0

MySQL C API

Examples

Many of the clients in MySQL source distributions are written in C, such as mysql, mysqladmin, and mysqlshow. If you are looking for examples that demonstrate how to use the C API, take a look at those clients: Obtain a source distribution and look in its client directory.

Building and Running C API Client Programs

Compiling MySQL Clients on Unix:

-I/usr/local/mysql/include -L/usr/local/mysql/lib -lmysqlclient