Tuesday, January 09, 2007

MySQL Embedded Server

MySQL Embedded Server Application
MySQL is an Open Source Relational Database Management System. Its fast, reliable, secure RDBMS. To add to this it has a unique feature which allows the whole DBMS to be embedded into the application itself, known as the MySQL Embedded Server.

MySQL Embedded Server
Whole MySQL Database server is bundled into a library called MySQL Embedded Server. This means that a single Dynamic linking Library (a .dll on Windows, a .so on Unix) that acts as a server and the client all by itself. Using this library one can run a Full featured Database server within the client application.

Benifits of using Embedded Server
The developer doesnt have to worry about database
  • Installation
  • Availabilty
  • Connectivity
  • Portability
  • Speed
With embedded Server we dont need MySQL server to be installed on the target machine. Next the developer does not have to think about the availabilty and connectivity to the server. The embedded server resides within the application and uses Interprocess Communication (or pipes) instead of using the network (TCP/IP) in a conventional DBMS.It also the makes the application portable. Portable in the sense data can be moved from one machine to another easily. As the server is running within the client application the speed factor is also taken care of.

Working with the Embedded Server
The embedded server resides within the application, so it has to be started and shutdown by the application. Once the server is initialized (i,e started) we can use the regular MySQL API to perform database transactions as in any Database application. To use the MySQL embedded server we have to just link the application with libmysqld library and just add four(two in most cases, unless you are developing a multithreaded application) extra function calls to the existing MySQL client server application. The extra functions to be called being
mysql_server_init() - to start the server
mysql_thread_init() - to be called in each new thread that accesses MySQL
mysql_thread_end() - to be called before exiting
mysql_server_end() - to stop the server

Starting the Embedded Server
The Embedded server is initialzed using the mysql_server_init() function with the following function signature

int mysql_server_init(int argc, char **argv, char **groups)
Where argc and argv are same as the the main() function's argc and argv. The last argument is used to specify the active groups of the options file.

Options file
MySQL server startup can is customized with a list of command line options. If the number of arguments are large then the command line becomes very big. To avoid this problem we can put all the options in a file and instruct MySQL to read this "Options File" while starting up. The same holds good for the embedded server also. In a single options file we can have multiple groups of configuration. So we will have to "activate" a certain group using the third agrument in the mysl_server_init() function. By default MySQL looks at certain places to locate the options file. On Windows it looks at the following locations:

C:\my.cnf Global options
WINDIR\my.ini Global options
INSTALLDIR\my.ini Global options

WINDIR is where Windows is installed. It’s normally C:\WINDOWS or C:\SYSROOT.
INSTALLDIR is the folder where MySQL is installed.

On UNIX it looks at the following locations:

/etc/my.cnf Global options
DATADIR/my.cnf Server specific options
~/.my.cnf User specific options

We can override this default behaviour with the "--defaults-file" option in the command line argument(in the argv argument to the mysql_server_init function). The best and easy way of using this in an embedded server application is to ship a options file with the installation and use the --defaults-file argument to point to this options file. Once the embedded server has been started we can proceed further using the database with the regular MySQL C/C++ API.

Bare minimum Options File
The bare minimum options file has to contain two groups namely "[server]" and "[client]". Then this can be followed by other custom groups. Under server group we must have atleast the following two enteries:
datadir - The location where data has to stored
language - Path to the language files.
The client group has to have the language entry. In the options file if we are going to have just one set of options then we can skip server and client groups.

Sample Options File

[test_libmysqld_SERVER]
language = C:/Path/to/language/files
datadir = C:/path/to/data

[test_libmysqld_CLIENT]
language = C:/Path/to/language/files

Note that the Windows path needs to contain forward slashes (/) and not the backslash (\). To be on the safer side use absolute path. The above example is for Windows, some holds good for UNIX also, just change the path. Copy the language files from the MySQL installation to workspace.

Stopping the server
We have to stop the server when we are done with using the database. This is done using the mysql_server_end() function call. This function has no arguments and shutsdown the embedded server.

Example
Lets take a look at a simple example for using MySQL Embedded Server in C.

#include
#ifdef WIN32
#define WIN32_LEAN_AND_LEAN
#include
#include
#endif
#include

int main()
{
static char *server_options[] = {"mysql_test", "--defaults-file=my.cnf"};
int num_elements = sizeof(server_options) / sizeof(char *);
static char *server_groups[] = {"libmysqd_server", "libmysqd_client"};

MYSQL *mysql;
MYSQL_RES* result;
MYSQL_ROW row;

/*Initialize the server*/
if(!mysql_server_init(num_elements, server_options, server_groups))
{
printf("Error initializing the server\n");
return 1;
}

/*Initialize the MYSQL structure*/
mysql = mysql_init(NULL);

/*
* Set connection type to MYSQL_OPT_USE_EMBEDDED_CONNECTION
* When we do a real connect, then a embedded server connection is used
* instead of a conventional client server connection.
*/
mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

/*
* Establish a connection to the database. By default user authentication
* is disabled in a embedded server. If user authentication is required then use
* --with-embedded-privilege-control in the server_options.
*/
if(mysql_real_connect(mysql, NULL, NULL, NULL, "test", 0, NULL, 0) == NULL)
{
printf("Unable to connect to the database\n");
return 1;
}

/* Now that we are connect we can use the C API to proceed further */
mysql_query(mysql, "SELECT UserName FROM USERS");
result = mysql_store_result(mysql);
while((row = mysql_fetch_row(result) != NULL)
{
printf("UserName : %s\n", row[0]);
}

/*We are done with the database. Clean up!*/
mysql_free_result(result);

/*Close the connection*/
mysql_close(mysql);

/*Shutdown the server as well*/
mysql_server_end();
return 0;
}

The above code is taken from MySQL tutorial located at http://dev.mysql.com/tech-resources/articles/embedding-mysql-server.html and modified as required. Next step is to compile the program. Following is a makefile that works on UNIX.

##############################
# Makefile to build the MySQL Embedded #
# server example on UNIX #
##############################
CC=gcc
CFLAGS=`mysql_config --include`
LDFLAGS=`mysql_config --libmysqld-libs`

all: embeddedserver

embeddedserver: embeddedserver.o
$(CC) embeddedserver.o -o embeddedserver $(LDFLAGS)
embeddedserver.o: embeddedserver.c
$(CC) embeddedserver.c -o embeddedserver.o $(CFLAGS)

clean:
rm -f embeddedserver.o embeddedserver

##############################
# Makefile to build the MySQL Embedded #
# server example on Windows #
##############################
CFLAGS=/O2 /G5 /GA /I "C:\MySQL\include" /D "WIN32" /D "_WINDOWS" /D "_CONSOLE" /D "_DEBUG" /D "_MBCS" /FD /EHsc /MT /GS /Fo"Release/" /Fd"Release/vc70.pdb" /W3 /nologo /c /TP
LDFLAGS=/OUT:"Release/article.exe" /INCREMENTAL /NOLOGO /LIBPATH:"c:\MySQL\Embedded\DLL\release" /LIBPATH:"D:\MySQL\lib\opt" /NODEFAULTLIB:"libcmtd.lib" /SUBSYSTEM:CONSOLE /MACHINE:X86 libmysqld.lib mysys.lib kernel32.lib user32.lib

all: "Release/embeddedserver.exe"

Release/embeddedserver.exe : Release/embeddedserver.obj
link Release/embeddedserver.obj $(LDFLAGS)

Release/embeddedserver.obj : embeddedserver.c
cl embeddedserver.c -c $(CFLAGS)

clean:
del Release\embeddedserver.*