27.6 libmysqld, the Embedded MySQL Server Library

27.6.1 Compiling Programs with libmysqld

In precompiled binary MySQL distributions that include 'libmysqld', the embedded server library, MySQL builds the library using the appropriate vendor compiler if there is one.

To get a 'libmysqld' library if you build MySQL from source yourself, you should configure MySQL with the '-DWITH_EMBEDDED_SERVER=1' option. See *note source-configuration-options::.

When you link your program with 'libmysqld', you must also include the system-specific 'pthread' libraries and some libraries that the MySQL server uses. You can get the full list of libraries by executing *note 'mysql_config --libmysqld-libs': mysql-config.

The correct flags for compiling and linking a threaded program must be used, even if you do not directly call any thread functions in your code.

To compile a C program to include the necessary files to embed the MySQL server library into an executable version of a program, the compiler needs to know where to find various files and needs instructions on how to compile the program. The following example shows how a program could be compiled from the command line, assuming that you are using 'gcc', use the GNU C compiler:

 gcc mysql_test.c -o mysql_test \
 `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`

Immediately following the 'gcc' command is the name of the C program source file. After it, the '-o' option is given to indicate that the file name that follows is the name that the compiler is to give to the output file, the compiled program. The next line of code tells the compiler to obtain the location of the include files and libraries and other settings for the system on which it is compiled. The *note 'mysql_config': mysql-config. command is contained in backticks, not single quotation marks.

On some non-'gcc' platforms, the embedded library depends on C++ runtime libraries and linking against the embedded library might result in missing-symbol errors. To solve this, link using a C++ compiler or explicitly list the required libraries on the link command line.

 File: manual.info.tmp, Node: libmysqld-restrictions, Next: libmysqld-options, Prev: libmysqld-compiling, Up: libmysqld

27.6.2 Restrictions When Using the Embedded MySQL Server

The embedded server has the following limitations:

• No loadable functions.

• No stack trace on core dump.

• You cannot set this up as a source or a replica (no replication).

• Very large result sets may be unusable on low memory systems.

• You cannot connect to an embedded server from an outside process with sockets or TCP/IP. However, you can connect to an intermediate application, which in turn can connect to an embedded server on the behalf of a remote client or outside process.

• 'libmysqld' does not support encrypted connections. An implication is that if an application linked against 'libmysqld' establishes a connection to a remote server, the connection cannot be encrypted.

• 'InnoDB' is not reentrant in the embedded server and cannot be used for multiple connections, either successively or simultaneously.

• The Event Scheduler is not available. Because of this, the 'event_scheduler' system variable is disabled.

• The Performance Schema is not available.

• The embedded server cannot share the same 'secure_file_priv' directory with another server. As of MySQL 5.7.8, the default value for this directory can be set at build time with the 'INSTALL_SECURE_FILE_PRIV_EMBEDDEDDIR' 'CMake' option.

Some of these limitations can be changed by editing the 'mysql_embed.h' include file and recompiling MySQL.

 File: manual.info.tmp, Node: libmysqld-options, Next: libmysqld-example, Prev: libmysqld-restrictions, Up: libmysqld

27.6.3 Options with the Embedded Server

Any options that may be given with the *note 'mysqld': mysqld. server daemon, may be used with an embedded server library. Server options may be given in an array as an argument to the 'mysql_library_init()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-library-init.html), which initializes the server. They also may be given in an option file like 'my.cnf'. To specify an option file for a C program, use the '--defaults-file' option as one of the elements of the second argument of the 'mysql_library_init()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-library-init.html) function. See mysql_library_init() (https://dev.mysql.com/doc/c-api/5.7/en/mysql-library-init.html), for more information on the 'mysql_library_init()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-library-init.html) function.

Using option files can make it easier to switch between a client/server application and one where MySQL is embedded. Put common options under the '[server]' group. These are read by both MySQL versions. Client/server-specific options should go under the '[mysqld]' section. Put options specific to the embedded MySQL server library in the '[embedded]' section. Options specific to applications go under section labeled '[ApplicationName_SERVER]'. See *note option-files::.

 File: manual.info.tmp, Node: libmysqld-example, Prev: libmysqld-options, Up: libmysqld

27.6.4 Embedded Server Examples

These two example programs should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes are needed, mostly with file paths. These examples are designed to give enough details for you to understand the problem, without the clutter that is a necessary part of a real application. The first example is very straightforward. The second example is a little more advanced with some error checking. The first is followed by a command-line entry for compiling the program. The second is followed by a GNUmake file that may be used for compiling instead.

Example 1

'test1_libmysqld.c'

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

 MYSQL *mysql;
 MYSQL_RES *results;
 MYSQL_ROW record;

 static char *server_options[] = \
        { "mysql_test", "--defaults-file=my.cnf", NULL };
 int num_elements = (sizeof(server_options) / sizeof(char *)) - 1;

 static char *server_groups[] = { "libmysqld_server",
                                  "libmysqld_client", NULL };

 int main(void)
 {
    mysql_library_init(num_elements, server_options, server_groups);
    mysql = mysql_init(NULL);
    mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
    mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

    mysql_real_connect(mysql, NULL,NULL,NULL, "database1", 0,NULL,0);

    mysql_query(mysql, "SELECT column1, column2 FROM table1");

    results = mysql_store_result(mysql);

    while((record = mysql_fetch_row(results))) {
       printf("%s - %s \n", record[0], record[1]);
    }

    mysql_free_result(results);
    mysql_close(mysql);
    mysql_library_end();

    return 0;
 }

Here is the command line for compiling the above program:

 gcc test1_libmysqld.c -o test1_libmysqld \
  `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`

Example 2

To try the example, create an 'test2_libmysqld' directory at the same level as the MySQL source directory. Save the 'test2_libmysqld.c' source and the 'GNUmakefile' in the directory, and run GNU 'make' from inside the 'test2_libmysqld' directory.

'test2_libmysqld.c'

 /*
  * A simple example client, using the embedded MySQL server library
 */

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

 MYSQL *db_connect(const char *dbname);
 void db_disconnect(MYSQL *db);
 void db_do_query(MYSQL *db, const char *query);

 const char *server_groups[] = {
   "test2_libmysqld_SERVER", "embedded", "server", NULL
 };

 int
 main(int argc, char **argv)
 {
   MYSQL *one, *two;

   /* mysql_library_init() must be called before any other mysql
    * functions.
    *
    * You can use mysql_library_init(0, NULL, NULL), and it
    * initializes the server using groups = {
    *   "server", "embedded", NULL
    *  }.
    *
    * In your $HOME/.my.cnf file, you probably want to put:

 [test2_libmysqld_SERVER]
 language = /path/to/source/of/mysql/sql/share/english

    * You could, of course, modify argc and argv before passing
    * them to this function.  Or you could create new ones in any
    * way you like.  But all of the arguments in argv (except for
    * argv[0], which is the program name) should be valid options
    * for the MySQL server.
    *
    * If you link this client against the normal mysqlclient
    * library, this function is just a stub that does nothing.
    */
   mysql_library_init(argc, argv, (char **)server_groups);

   one = db_connect("test");
   two = db_connect(NULL);

   db_do_query(one, "SHOW TABLE STATUS");
   db_do_query(two, "SHOW DATABASES");

   mysql_close(two);
   mysql_close(one);

   /* This must be called after all other mysql functions */
   mysql_library_end();

   exit(EXIT_SUCCESS);
 }

 static void
 die(MYSQL *db, char *fmt, ...)
 {
   va_list ap;
   va_start(ap, fmt);
   vfprintf(stderr, fmt, ap);
   va_end(ap);
   (void)putc('\n', stderr);
   if (db)
     db_disconnect(db);
   exit(EXIT_FAILURE);
 }

 MYSQL *
 db_connect(const char *dbname)
 {
   MYSQL *db = mysql_init(NULL);
   if (!db)
     die(db, "mysql_init failed: no memory");
   /*
    * Notice that the client and server use separate group names.
    * This is critical, because the server does not accept the
    * client's options, and vice versa.
    */
   mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test2_libmysqld_CLIENT");
   if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0))
     die(db, "mysql_real_connect failed: %s", mysql_error(db));

   return db;
 }

 void
 db_disconnect(MYSQL *db)
 {
   mysql_close(db);
 }

 void
 db_do_query(MYSQL *db, const char *query)
 {
   if (mysql_query(db, query) != 0)
     goto err;

   if (mysql_field_count(db) > 0)
   {
     MYSQL_RES   *res;
     MYSQL_ROW    row, end_row;
     int num_fields;

     if (!(res = mysql_store_result(db)))
       goto err;
     num_fields = mysql_num_fields(res);
     while ((row = mysql_fetch_row(res)))
     {
       (void)fputs(">> ", stdout);
       for (end_row = row + num_fields; row < end_row; ++row)
         (void)printf("%s\t", row ? (char*)*row : "NULL");
       (void)fputc('\n', stdout);
     }
     (void)fputc('\n', stdout);
     mysql_free_result(res);
   }
   else
     (void)printf("Affected rows: %lld\n", mysql_affected_rows(db));

   return;

 err:
   die(db, "db_do_query failed: %s [%s]", mysql_error(db), query);
 }

'GNUmakefile'

 # This assumes the MySQL software is installed in /usr/local/mysql
 inc      := /usr/local/mysql/include/mysql
 lib      := /usr/local/mysql/lib

 # If you have not installed the MySQL software yet, try this instead
 #inc      := $(HOME)/mysql-5.7/include
 #lib      := $(HOME)/mysql-5.7/libmysqld

 CC       := gcc
 CPPFLAGS := -I$(inc) -D_THREAD_SAFE -D_REENTRANT
 CFLAGS   := -g -W -Wall
 LDFLAGS  := -static
 # You can change -lmysqld to -lmysqlclient to use the
 # client/server library
 LDLIBS    = -L$(lib) -lmysqld -lm -ldl -lcrypt

 ifneq (,$(shell grep FreeBSD /COPYRIGHT 2>/dev/null))
 # FreeBSD
 LDFLAGS += -pthread
 else
 # Assume Linux
 LDLIBS += -lpthread
 endif

 # This works for simple one-file test programs
 sources := $(wildcard *.c)
 objects := $(patsubst %c,%o,$(sources))
 targets := $(basename $(sources))

 all: $(targets)

 clean:
         rm -f $(targets) $(objects) *.core

 File: manual.info.tmp, Node: c-api-info, Next: apis-php-info, Prev: libmysqld, Up: connectors-apis