Friday, August 10, 2018

Using MySQL Database with C++ on Various Platforms Including ARM Single Board Computers

In this article, I would like to discuss about using MySQL database with C++. MySQL is an open source database management system from Oracle. It not only has freely available version but it is also robust and works on most platforms. MySQL server can run even on a small 32 bit ARM Single Board Computer (SBC) like Odroid. It has a lot of functionalities, and user interface too. That is why, I like to use MySQL database server as an example to be used with C++.

MySQL server can be used as a traditional relational database or a document store where there is no need to define schema, called NoSQL. In a relational database, all the columns of the tables need to be defined in advance. But in a document store, the schema is flexible where it is represented by JSON objects. In order to utilize the advantages of power of relational model and flexibility of the document store, using X DevAPI in C++ applications is discussed.

  1. Installation
  2. Installing from Source
  3. Resetting Root Password
  4. Configuration
  5. Testing MySQL server
  6. Installing MySQL Connector/C++ from source
  7. References
You can create a Linux virtual machine to run MySQL server. As an example, Ubuntu Linux is used for MySQL server and a client C++ application is created and run on a Single board computer (SBC) to connect to the MySQL server. On a more powerful SBC like Odroid 32 bit ARM SBC, it is also possible to build MySQL server from source and run on it.


MySQl SERVER 8.0 has X Plugin installed which supports X protocol. To install MySQL server 8.0 on Ubuntu Linux, download apt config package called "Ubuntu / Debian (Architecture Independent), DEB Package" at its page Download MySQL APT Repository ( [Ora18c, Bou17]။ In this example, the current latest release "mysql-apt-config_0.8.10-1_all.deb" is used. Thereafter, you will be asked whether to create the Oracle Web account or not. That step can be simply skipped.

Figure. Getting the latest MySQL apt config package.

Figure. Skipping the step of creating Oracle Web account , and starting the download.

Navigate to the directory where the file is downloaded. The following dpkg command can be used to install the apt configure package.
$ sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

Figure. Installing the Apt config package.

After configuring, click OK to go back to the command prompt. As we want to use the new API of mysql called X DevAPI, we chose mysql-8.0 as shown in the following figure. The other Tools and preview packages are enabled also.

Figure. Choosing mysql-8.0 .

The following command can be used to install MySQL server. You can enter root password if it is asked.
$ sudo apt update
$ sudo apt install mysql-server
MySQL client and MySQL workbench can also be installed as follows.
$ sudo apt install mysql-client mysql-workbench

Choosing Major Release Version

After MySQL installation, if you want to change the major release version, you still can change it as follows.
$ sudo dpkg-reconfigure mysql-apt-config
$ sudo apt-get update

Installing from Source


For some platforms like armhf, there is no supported installer for MySQL Server 8.0. In that case, MySQL can be built from source and install. To build the source, the following develapment tools are required in the machine. GCC, CMAKE, Git, ncurses and bison can be installed as follows.
$ sudo apt update
$ sudo apt install build-essential
$ sudo apt install cmake
$ sudo apt install git
$ sudo apt install libncurses5-dev
$ sudo apt install bison
To install Boost, Version 1.66.0 boost_1_66_0.tar.bz2 is available for downloading at the following link.

Thereafter, the following commands can be used to install it in /usr/local.
$ cd /usr/local
$ sudo tar --bzip2 -xf ~/Downloads/boost_1_66_0.tar.bz2
Having Boost header files, you can include the following option in CMAKE configurations when building MySQL.
To avoid 'SSL libraries not found' error, SSL libraries should also be installed as follow.
$ sudo apt install libssl-dev

Preconfiguration setup

Before building MySQL, perform the following preconfiguration setup [Ora18d]. If there is no user and group to run MySQL server, also called mysqld, you need to create them. mysql user and group can be created as follows. You can use other name if you don't want the name mysql. For some Linux systems, the syntax for groupadd and useradd can be addgroup and adduser instead.
$ sudo groupadd mysql
$ sudo useradd -r -g mysql -s /bin/false mysql
The user is required only for ownership and not for login, that is why -r and -s /bin/false options are used in useradd command to create a user without login permissions. These options can be neglected on the systems that do not support them. Then, go to, select Source Code in Operating System drop down box, select Generic Linux (Architecture Independent) for Operating System, and download Compressed TAR Archive as follow. Oracle Account can be signed up or skipped by clicking 'No thanks, just start my download'.

Figure. Getting MySQL Source.


After extracting the resulting source file mysql-8.0.11.tar.gz, we will build and install MySQL server.
$ tar zxvf ~/Downloads/mysql-8.0.11.tar.gz
$ cd mysql-8.0.11
$ mkdir bld
$ cd bld
$ cmake .. -DWITH_BOOST=/usr/local/boost_1_66_0
$ make
$ sudo make install

Postinstallation setup

After installing MySQL, for the postinstallation setup, it is required to initialized the data directory that contains tables in mysql system database . For that, we first navigate to installed MySQL directory where we can find several files and directories. In the 'bin' directory, there are utilities for server and client.
$ cd /usr/local/mysql
For 'secure_file_priv' system variable which limits import/export operations in a directory, we will create a directory and define its ownership, group and permissions.
$ sudo mkdir mysql-files
$ sudo chown mysql:mysql mysql-files
$ sudo chmod 750 mysql-files
Initialize the data directory for mysql database that contains initial MySQL grant tables defining permissions for user to connect and use MySQL database.
$ sudo bin/mysqld --initialize --user=mysql
Thereafter, a temporary password for root user is generated and you can take note for it.

Figure. A temporary password for MySQL root user.

If you want the support for secure connections, use mysql_ssl_rsa_setup utility to create default SSL and RSA files.
$ sudo bin/mysql_ssl_rsa_setup
Start MySQL server by using mysqld_safe as follows.
$ sudo bin/mysqld_safe --user=mysql &
# Next command is optional
$ sudo cp support-files/mysql.server /etc/init.d/mysql.server
It is important to use a non-root account when running MySQL server. For that, you can run mysqld_safe with root account and using --user option. If not, you need to log in as mysql and run. After installation, the temporary root password of MySQL server will be expired. Therefore, you need to run mysql_secure_installation to reconfigure as discussed in the previous section.
$ sudo bin/mysql_secure_installation


For Linux systems, MySQL binary or source distributions include a script called mysql.server which starts the server using mysqld_safe. To use mysql.server to start or stop the server manually, you can use the following commands.
$ cd /usr/local/mysql
$ sudo support-files/mysql.server start
$ sudo support-files/mysql.server stop
For automatic starting, you need to copy mysql.server as mysql into /etc/init.d directory and make it executable.
$ cd /usr/local/mysql
$ sudo cp support-files/mysql.server /etc/init.d/mysql
$ sudo chmod +x /etc/init.d/mysql
Then, use update-rc.d to activate running it at system startup.
$ sudo update-rc.d mysql defaults
For some Linux, if you don't want to use update-rc.d, you can add the following command in /etc/rc.local.
/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'
mysql.server reads its options from [mysql.server] and [mysqld] sections of its option file. If you want to add or change the options, you can edit /etc/my.cnf. An example of /etc/my.cnf is shown below.

Open MySQL workbench as shown in the following figure, and configure 'Limit to Hosts Matching' as % to allow connecting as a root user from other machines.

Figure. Using MySQL Workbench.

Figure. Modifying Limit to Hosts Matching.

Resetting Root Password

If you need to reset the root password for some reasons after installation, stop mysql service first.
$ sudo service mysql status
$ sudo service mysql stop
To reset root password, start mysql safe daemon with --skip-grant-tables option which allows anyone full privileges without asking for password. Using --skip-grant-tables option on server is not safe, that is why --skip-networking will be automatically enabled to prevent remote connection.
$ sudo mysqld_safe --skip-grant-tables &
If you get an error like 'mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists', you can perform the following steps.
$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld
Thereafter, re-entering the command
$ sudo mysqld_safe --skip-grant-tables &
will show 'starting mysqld daemon', and when a cursor is appeared, you can use mysql as a root user without password.
mysql -u root
In mysql prompt, you can now use sql command which always ends with semicolon. Use your own password instead of 'my-new-password' in the following command.
> use mysql;
> update user set authentication_string=PASSWORD("my-new-password") where User="root";
> flush privileges;
> quit
Reboot the machine.


MySQL server will be running after installation. You can check its status as follow.
$ sudo service mysql status

Figure. Checking MySQL Service status.

To see the port listening by the server, you can use the following command. Then, mysqld is listening at 3306 and also listening at 33060 for xdevapi as shown in the figure below.
$ sudo netstat -npl

Figure. Checking network ports.

Then, run mysql_secure_installation to define security updates for the server.
$ mysql_secure_installation
If you built MySQL from source, you need to navigate to the directory where there are binary utilities in order to run it.
$ cd /usr/local/mysql
$ sudo bin/mysql_secure_installation
You need to enter root password when running the utility, and then you can define configurations for it. VALIDATE PASSWORD PLUGIN checks the strength of passwords to improve security. Enter y to add the plugin, or other key for no. Root password can be changed also. Anonymous user allows connecting MySQL without having a user account. You can also disallow remote connection for root. Test database can be removed. Then, you can reload privilege tables to take effect the changes immediately.

Testing MySQL server

The utilities for MySQL can be found in bin directory of /usr/local/mysql. Naviate to that directory and use 'ls' command to list them.


mysqladmin is a client to administer MySQL server. For example, you can use it to check server version as follow.
$ sudo mysqladmin -u root -p version
If you built MySQL from source, you can go to the installation directory first and run it as follows.
$ cd /usr/local/mysql
$ sudo bin/mysqladmin -u root -p version
Use mysqlshow to see databases.
$ sudo bin/mysqlshow -u root -p version


mysql command line tool is a simple SQL shell. Use the following command to use mysql. In its prompt, mysql command can be entered which ends with semicolon. If MySQL server is installed using apt, you do not need to go to binary directory, and you can use mysql command directly.
$ cd /usr/local/mysql
$ sudo bin/mysql -u root -p
Databases can be seen as follow.
As an example, create a database called mytest.
When you shows the databases again, you can see the newly created database. To define mytest as default database for next commands, enter 'USE mytest' as follow.
> USE mytest;
Use 'CREATE TABLE' to create a table.
id INT unsigned NOT NULL AUTO_INCREMENT, # unique id for the record
name   VARCHAR(150) NOT NULL, # name
birthday DATE NOT NULL, # birthday
PRIMARY KEY (id) # make the id the primary key
The newly created table can be seen by using 'SHOW TABLES'.
Use 'DESCRIBE' to see the columns of a table.
> DESCRIBE tbl1;
To quit MySQL to go back to bash prompt, use 'exit'.
$ exit
Installed plugins of MySQL can be seen as follow.
$ mysql -u root -p -e "show plugins"

Installing MySQL Connector/C++ from source

To use MySQL server with C++ applications, MySQL Connector/C++ 8.0 provides an API driver for MySQL and also supports new X DevAPI for development with MySQL server 8.03 [Ora18a]. With Connector C++ 8.0, C++ applications can use X DevAPI to perform plain SQL queries or to use document store by connecting to MySQL server which supports X Plugin.

Build Tools

To build Connector C++ from source, you need a compiler that supports C+11 standard. Cross platform build tool 'CMake' is also required. If you want to get the source from its Git repository, you need to install git too. You can use the following commands to install these tools.
$ sudo apt update
$ sudo apt install build-essential
$ sudo apt install cmake git
If you intend to use Legacy JDBC connector, Boost C++ libraries, boost_1_66_0, needs to be installed as discussed in the previous section.

Getting the Source

To get the source for Connector C++ 8.0, you can visit to Download Connector/C++ Web Page, choose 'source' in the dropdown box, and then download and extract it. Alternatively, you can use 'git clone' too. Use a command to extract it like the one shown below. And, navigate to the extracted directory.
$  tar zxvf mysql-connector-c++-8.0.11-src.tar.gz
$ cd mysql-connector-c++-8.0.11-src
Cloning from its Git repository is shown below.
$ git clone
Thereafter, change to that directory and checkout 8.0 branch.
$ cd mysql-connector-cpp
$ git checkout 8.0
Generally, legacy JDBC connector is not built. If you also want to include the support for it, you need to do submodule initialization as follow.
$ git submodule update --init


CMake is used to configure and build Connector C++ 8.0. Create a directory for the build, and change to that directory.
$ mkdir build
$ cd build
Default install location is /usr/local/mysql/connector-c++-8.0. You can change the installation location by using CMAKE_INSTALL_PREFIX. By default, dynamic (shared) libraries are built. To build static libraries, you can include the option -DBUILD_STATIC=ON. Legacy JDBC connector is not built by default. Use the following option to include it.
For that, Boost C++ libraries are also required and boost_1_66_0 can be installed as discussed in previous section. Then, include the following option.
Thereafter, define the cmake configuration as shown below.
$ cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/connector-c++-8.0 \
-DWITH_BOOST=/usr/local/boost_1_66_0  -DWITH_JDBC=ON ..


After configuring, you can build the source using the following command. You can define Release or Debug configuration using --config option. By default, Release configuration is built.
$ cmake --build . --config Release
After building,
connector library will be in the build directory.


To install Connector C++ 8.0 libraries, use the following command.
$ sudo cmake --build . --target install --config Release
Then, create a directory called 'lib64' in /usr/local/mysql/connector-c++-8.0/ if it is not created yet. On 32 bit machines, the name should be 'lib' instead of 'lib64'.
$ sudo mkdir /usr/local/mysql/connector-c++-8.0/lib64
If you included Legacy JDBC connector in the build, lib64 directory will be created automatically and
will be found in /usr/local/mysql/connector-c++-8.0/lib64. Thereafter, copy the library as follows.
$ sudo cp /usr/local/mysql/connector-c++-8.0/lib64/
$ sudo cp /usr/local/mysql/connector-c++-8.0/lib64/
Create a file called mysqlcppconn8.conf in /etc/ and edit it like
$ sudo nano /etc/
When the file is opened in the editor, add the following line, save and exit by pressing ctrl+o and ctrl+x if you are using nano editor.
Run ldconfig as follow.
$ sudo ldconfig

Building Connector C++ Applications

With MySQL Connector C++ 8.0, you can use exciting new features of MySQL Server 8.0. For example, you can efficiently use MySQL Document Store for simple database tasks by combining with the power of C++. It also supports X Protocol, therefore, an example C++ program using X DevAPI is demonstrated [Ora18b]. Include the following code to use Connector C++ at the start of the C++ program.
#include < mysqlx/xdevapi.h >
using namespace mysqlx;
For database connection, you can create a logical session with a MySQL server which supports X Plugin. Applications using session object can be used for single server or database cluster without modifying the code. To create a database connection, you can use URI type string
Session my_session("user:password@host:port");
Session my_session("host",port,"user","password");
After a session is established, the example lists the databases in the server . For that, getSchemas() method can be used to get the list of databases.
std::list schemaList = my_session.getSchemas();
The example mysqltest.cpp is shown in the following list.
#include < iostream >
#include < mysqlx/xdevapi.h >
using namespace std;
using namespace mysqlx;

int main()
try {
  cout <<"Getting session..." < schemaList = sess.getSchemas();
  cout<<"Available schemas in this session:"<< endl;

  //loop over all available schemas and print their name
  for(Schema schema : schemaList) {
    cout << schema.getName() << endl;
catch (const mysqlx::Error &err)
  cout << "ERROR: " << err << endl;
To build the application, the same tools that were used to build MySQL Connector C++ 8.0 should be used. Compiler version, runtime library, runtime linker configuration settings must be the same. To support C++11 standard, the option '-std=c++11' should be included also. Build configuration for Release or Debug option must be same as Connector C++. Thereafter, it can be built and run as follows. Library path for 64 bit machines should be lib64.
$ g++ -std=c++11 -I /usr/local/mysql/connector-c++-8.0/include -L /usr/local/mysql/connector-c++-8.0/lib mysqltest.cpp -lmysqlcppconn8 -o mysqltest
$ ./mysqltest
You can create make file or a simple scrpit for building the application. The output of the application is shown below.

Figure. The output of mysqltest.cpp.

Using Legacy Connector C++ 1.1

Using Legacy connector C++ 1.1 does not require MySQL server to have X Plugin. It uses normal MySQL port number 3306 . In C++ program, headers files for connector C++ 1.1 can be included as follows.
#include "jdbc/mysql_connection.h"
#include < jdbc/cppconn/driver.h >
#include < jdbc/cppconn/exception.h >
#include < jdbc/cppconn/resultset.h >
#include < jdbc/cppconn/statement.h >
To establisha connection with MySQL server, you need to get sql::Connection instance from sql::mysql::MySQL_Driver object.
sql::Driver *driver;
sql::Connection *con;

driver = get_driver_instance();
con = driver->connect("tcp://", "username", "password");
sql::Connection::isValid() can be used to check the connection whether it is alive. Use sql::Connection::reconnect() to reconnect the lost connection.
For simple SQL queries, sql::Statement::execute() can be used when there is no result or more than one result. For queries with a single result, sql::Statement::executeQuery() can be used.
sql::mysql::MySQL_Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
driver = sql::mysql::get_mysql_driver_instance();
con = driver-> connect("tcp://", "user", "password");
stmt = con-> createStatement();
stmt-> execute("USE " EXAMPLE_DB);
stmt-> execute("DROP TABLE IF EXISTS test");
stmt-> execute("CREATE TABLE test(id INT, label CHAR(1))");
stmt-> execute("INSERT INTO test(id, label) VALUES (1, 'a')");
If a query has a single result set, it returns sql::ResultSet objects.
// ...
sql::Connection *con;
sql::Statement *stmt;
sql::ResultSet *res;
// ...
stmt = con->createStatement();
// ...
res = stmt-> executeQuery("SELECT id, label FROM test ORDER BY id ASC");
while (res-> next()) {
// You can use either numeric offsets...
cout << "id = " << res->getInt(1); // getInt(1) returns the first column
// ... or column names for accessing results.
// The latter is recommended.
cout << ", label = '" << res->getString("label") << "'" << endl;
The objects for sql::Connection, sql::Statement, and sql::ResultSet must be explicitly free using delete. For mysql::MySQL_Driver object, Connecter C++ will take care to free it and you do not need to explicitly free it.
delete res;
delete stmt;
delete con;
An example for using Connector C++ 1.1 , con11test.cpp, is shown below.
#include < stdlib.h >
#include < iostream >

#include " jdbc/mysql_connection.h "
#include < jdbc/cppconn/driver.h >
#include < jdbc/cppconn/exception.h >
#include < jdbc/cppconn/resultset.h >
#include < jdbc/cppconn/statement.h >

using namespace std;

int main(void)
cout << endl;
cout << "Running ELECT Hello World! AS _message ..." << endl;

try {
  sql::Driver *driver;
  sql::Connection *con;
  sql::Statement *stmt;
  sql::ResultSet *res;

  /* Create a connection */
  driver = get_driver_instance();
  con = driver-> connect("tcp://", "root", "password");
  /* Connect to the MySQL test database */
  con-> setSchema("mytest");

  stmt = con->createStatement();
  res = stmt->executeQuery("SELECT 'Hello World!' AS _message");
  while (res->next()) {
    cout << "\t... MySQL replies: ";
    /* Access column data by alias or column name */
    cout << res-> getString("_message") << endl;
    cout << "\t... MySQL says it again: ";
    /* Access column data by numeric offset, 1 is the first column */
    cout << res-> getString(1) << endl;
  delete res;
  delete stmt;
  delete con;

} catch (sql::SQLException &e) {
  cout << "# ERR: SQLException in " << __FILE__;
  cout << "(" << __FUNCTION__ << ") on line "<< __LINE__ << endl;
  cout << "# ERR: " << e.what();
  cout << " (MySQL error code: " << e.getErrorCode();
  cout << ", SQLState: " << e.getSQLState() << " )" << endl;

cout << endl;

To build Connector C++ 1.1 applications, the path for Boost headers must be in the include path also. Then, for the connector library, the old library 'mysqlcppconn' must be linked instead. The script,, to build and run the example is shown below.
g++ -std=c++11 -I /usr/local/mysql/connector-c++-8.0/include \
  -I /usr/local/boost_1_66_0 -L /usr/local/mysql/connector-c++-8.0/lib \
    con11test.cpp -lmysqlcppconn -o con11test
The output of the program can be seen in the following figure.

Figure. The output of con11test.cpp.

Using Legacy JDBC API

Using Legacy JDBC-based API and Connector C++ 1.1 with C++ application, and building, running the application is discussed. An example for using legacy JDBC API, jdbc_test.cpp can be found at the source directory (mysql-connector-c++-8.0.11-src) of Connector C++ 8 in testapp directory. It is shown below.
#include < stdlib.h >
#include < iostream >
#include < sstream >
#include < stdexcept >

#include < boost/scoped_ptr.hpp >
#include < jdbc/mysql_connection.h >
#include < jdbc/mysql_driver.h >
#include < jdbc/cppconn/resultset.h >
#include < jdbc/cppconn/statement.h >

#define DEFAULT_URI "tcp://"
#define EXAMPLE_USER "root"
#define EXAMPLE_PASS "password"
#define EXAMPLE_DB "mytest"

using namespace std;

int main(int argc, const char **argv)
  const char   *url = (argc > 1 ? argv[1] : DEFAULT_URI);
  const string user(argc >= 3 ? argv[2] : EXAMPLE_USER);
  const string pass(argc >= 4 ? argv[3] : EXAMPLE_PASS);
  const string database(argc >= 5 ? argv[4] : EXAMPLE_DB);

  cout << endl;
  cout << "Connector/C++ standalone program example..." << endl;
  cout << endl;

  try {

    sql::Driver * driver = sql::mysql::get_driver_instance();

    /* Using the Driver to create a connection */

    cout << "Creating session on " << url << " ..."
         << endl << endl;

    boost::scoped_ptr< sql::Connection >
      con(driver-> connect(url, user, pass));
    con-> setSchema(database);

    boost::scoped_ptr < sql::Statement > stmt(con-> createStatement());
    boost::scoped_ptr < sql::ResultSet >
      res(stmt-> executeQuery("SELECT 'Welcome to Connector/C++' AS _message"));
    cout << "\t... running 'SELECT 'Welcome to Connector/C++' AS _message'"
         << endl;

    while (res-> next())
      cout << "\t... MySQL replies: " << res-> getString("_message") << endl;
      cout << "\t... say it again, MySQL" << endl;
      cout << "\t....MySQL replies: " << res-> getString(1) << endl;

  catch (sql::SQLException &e)
      The JDBC API throws three different exceptions:

    - sql::MethodNotImplementedException (derived from sql::SQLException)
    - sql::InvalidArgumentException (derived from sql::SQLException)
    - sql::SQLException (derived from std::runtime_error)

    cout << "# ERR: SQLException in " << __FILE__;
    cout << "(" << "EXAMPLE_FUNCTION" << ") on line " << __LINE__ << endl;

    /* Use what() (derived from std::runtime_error) to fetch the error message */

    cout << "# ERR: " << e.what();
    cout << " (MySQL error code: " << e.getErrorCode();
    cout << ", SQLState: " << e.getSQLState() << " )" << endl;

    return EXIT_FAILURE;

  cout << endl;
  cout << "... find more at" << endl;
  cout << endl;
  return EXIT_SUCCESS;
Similar to Connector C++ 1.1, the path for Boost headers must be included and the old connector library must be linked. A script file,, to build and run the application is shown below.
g++ -std=c++11 -I /usr/local/mysql/connector-c++-8.0/include \
  -I /usr/local/boost_1_66_0 -L /usr/local/mysql/connector-c++-8.0/lib \
  jdbc_test.cpp -lmysqlcppconn -o jdbc_test
The output of the program is illustrated in the figure below.

Figure. The output of jdbc_test.cpp.


[Bou17] Brian Boucheron. How To Install the Latest MySQL on Ubuntu 16.04. 2017.

[Ora18a] Oracle. MySQL Connector/C++ 8.0 Developer Guide. 2018.

[Ora18b] Oracle. X DevAPI User Guide. 2018.

[Ora18c] Oracle. A Quick Guide to Using the MySQL APT Repository. 2018.

[Ora18d] Oracle. Installing MySQL Using a Standard Source Distribution. 2018.

Related Posts:

1. Cool Emerald. MySQL Connector C++ 8.0 with wxWidgets. 2018.

No comments:

Post a Comment

Comments are moderated and don't be surprised if your comment does not appear promptly.