anders karlsson principal sales engineer, mysql mysql embedded - getting started with libmysqld
TRANSCRIPT
![Page 1: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/1.jpg)
Anders KarlssonPrincipal Sales Engineer, MySQL
MySQL Embedded - Getting started with libmysqld
![Page 2: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/2.jpg)
Agenda
• Who am I? (Good question!)• Why libmysqld?• What is libmysqld?
> What is MySQL Emmbedded? And is it different from libmysqld?
• Programming with libmysqld> API, Samples etc.
• Limitations of libmysqld• Questions? Answers?
![Page 3: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/3.jpg)
About Anders Karlsson• Sales Engineer with Sun / MySQL for 5+ years• I have been in the RDBMS business for 20+ years• I have worked for many of the major vendors and with most of the
vendor products• I’ve been in roles as
> Sales Engineer> Consultant> Porting engineer> Support engineer> Etc.
• Outside MySQL I build websites (www.papablues.com), develop Open Source software (MyQuery, ndbtop etc), am a keen photographer and drives sub-standard cars, among other things. Also, I am pushing for ZFS to become GPL: http://www.makezfsgpl.com
![Page 4: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/4.jpg)
Why libmysqld?• Libmysqld is compact and fast• Libmysqld is easily integrated into an application• Application installation is MUCH easier
> No separate server to install or run> No need to run as service or daemon
• Low hardware requirements• Zero maintenance• MySQL hidden from end user
![Page 5: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/5.jpg)
And why NOT libmysqld?• Only ONE application can connect at the time!• No server means less flexibility• Application itself has larger footprint
> Includes both the server and the application• Certain limitations exist• No Replication• No Client / Server connection• No authentication / security• More functions must be implemented in the
application> Any mysql Client operations such as dump, restore, check
etc.
![Page 6: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/6.jpg)
What is libmysqld and why is it different?
• “Normal” MySQL is a classic client/server based RDBMS> The database server is a separate process from the
client application> The client and the sever communicates using standard
networking (typically sockets or TCP/IP)> The Server may, and again may not, run in a separate
hardware environment from the client> The client talks to the server using a MySQL defined
protocol, typically implemented in a client interface or “driver” or Connector
![Page 7: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/7.jpg)
What is libmysqld and why is it different?
• MySQL in Client / Server configuration> MySQL Server runs on
machine on the network> Clients communicate with
Server using the MySQL Protocol
> Many clients talk to the same server
Clients usingMySQL Protocol
Server Disk
![Page 8: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/8.jpg)
What is libmysqld and why is it different?
• Libmysqld is a SQL database layer tightly integrated with the application or “client”> The database and the application runs in the same
process space> No special means of communication is needed as both
the application and the database are in the same process, calling MySQL is similar to calling any other library function
> The interface between the Application or client is different, but the exposed component, the API, is the same as with Client / Server MySQL
> Although the API looks the same, the implementation is different!
![Page 9: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/9.jpg)
What is libmysqld and why is it different?
• Libmysqld configuration> The Application IS the server!> There is just 1 process> Data is stored on the local disk> NOTE: The API for the
application is the same, but the implementation is different! There is just one implementation: In C
> All data is local> Only one “client” Application
process
libmysqld
Client system
![Page 10: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/10.jpg)
So where do I find libmysqld?
• Libmysqld is part of the usual MySQL distribution• Just download the MySQL Server and you find
> On Windows: libmysqld is in the Embedded subdirectory, both debug and release versions are available, and in DLL and static builds
> On Linux: libmysqld.a is in the lib subdirectory where MySQL is installed
• Includefiles:> Use the normal MySQL C API includefiles
![Page 11: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/11.jpg)
Building libmysqld applications
• Develop applications in C> Add calls to mysql_library_init() etc.
• Compile as usual• Link with libmysqld
> Can be linked dynamically or statically> A dynamic link is easier, just as a dynamic link with the
MySQL C Client library is easier• Set up an option file• Ship application, possibly the libmysqld shared
library and option file
![Page 12: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/12.jpg)
The libmysqld API
• VERY similar to the MySQL C API> Does NOT implement the MySQL Protocol!> Only looks the same, implementation is different!
• Key functions that are different> mysql_library_init() – Starts the internal “MySQL Server”> mysql_library_end() – Shuts down the internal “MySQL
Server” gracefully> mysql_thread_init() / mysql_thread_end()> mysql_options() – Set
MYSQL_OPT_USE_EMBEDDED_CONNECTION before connecting
![Page 13: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/13.jpg)
The mysql_library_init() function
• Initializes the MySQL internal server• Passes parameters to the server in that process• Must be passed information on initialization files and
sections in those> No server is already running!> This means all necessary server parameters needs to be
specified!• This means that this call is mostly different from when
NOT using libmysqld!• MUST be called before mysql_init()!
![Page 14: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/14.jpg)
The mysql_library_init() parameters
• Argument count (argc)• Arguments (argv) – Like the parameters to mysqld,
the first argument (as in argv[0] beging name of the program) being ignored.
• A list of option file sections to read> If a --defaults-file or any other argument with an option
file is passed, the config files section specifies whicg sections in this file to read
> The last section name is NULL> If no option file is passed, this argument may be NULL.
![Page 15: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/15.jpg)
static char *server_options[] = {"mysql_test","--datadir=C:/mydata", NULL};
static char *server_groups[] = { "libmysqld_server", NULL};
int main(argc, argv){MYSQL mysql;
mysql_library_init(sizeof(server_options)/ sizeof(char *) – 1, server_options, (char **) server_gropus);mysql = mysql_init(NULL);mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);mysql_real_connect(mysql, NULL, NULL, NULL, "test", 0, NULL, 0);
Initializing a libmysqld application
![Page 16: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/16.jpg)
Configuring a libmysqld application
• Use a my.cnf file> Set server_options as per the previous slide, to include
--defaults-file=my.cnf > Note that you must be careful with the path here!> Set up the my.cnf file
• Or set all options in the application itself> Set all options in the server_options variable
![Page 17: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/17.jpg)
Dynamic linking of a libmysqld application
• As a shared object / DLL> An import library is provided to
be linked with your application> Your application will need to be
distributed with the libmysqld shared object / DLL
> Not available on all platforms
Application
Libmysqld Import Library
Libmysqld shared library
![Page 18: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/18.jpg)
Static linking of a libmysqld application
• As a static library> The libmysqld library is linked
directly into the application> No additional files needs to be
distributed with the application> The only option on some
platforms> Has a slight performance benefit
Application
Libmysqld Library
![Page 19: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/19.jpg)
Linking a libmysqld application
• Note that dynamic linking does NOT necessarily allow you to update libmysqld by just changing the DLL / Shared object> Typically, an application linked with MySQL version X
needs libmysqld version X, even with dynamic linking• For Windows, I recommed dynamic linking
> Windows static linking is complex, as the static library depends on a particular version of Visual C++ is used
• For Linux, I recommend static linking> Not the least because that is the only option right now.
But it is less problematic than on windows, and has a slight performance advantage
![Page 20: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/20.jpg)
Managing libmysqld data
• Data format are the same for libmysqld as it is for the MySQL Server mysqld> Hence: Data that can be freely copied with mysqld can
be freely copied with libmysqld> This relates to most storage engines, but in some cases
with limitations• MyISAM may use compression (which is common)
and other MyISAM variations• Starting a libmysqld application with InnoDB
enabled (not using --skip-innodb) will create InnoDB data and transaction log files, as usual
![Page 21: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/21.jpg)
Managing libmysqld data
• Offline tools for mysqld data may be used on libmysqld data> myisampack> myisamchk> etc
• Online tools need to be implementedas part of the application> mysqldump> mysqlshow> etc.
![Page 22: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/22.jpg)
Libmysqld demo!
• The following demo application is available> On sourceforge:
http://sourceforge.net/projects/libmysqlddemo/> On MySQL Forge:
http://forge.mysql.com/projects/project.php?id=323• The application is Windows dialog-based and
written in C using the Win32 API• Should be self contained, except for libmysqld of
course, and the language file
![Page 23: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/23.jpg)
Limitations of libmysqld
• No replication• No events• InnoDB is single threaded• No authentication / authorization• Single application access
> But that is the whole pointof libmysqld
• Only C / C++
![Page 24: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/24.jpg)
Libmysqld performance
100.000 operations each, time in average after several runs
INSERT
UPDATE
SELECT
libmysqld mysqld
6.82 s
6.54 s
6.92 s
20.91 s
21.59 s
21.73 s
Note: This is very far from a scientific benchmark! The operations executed are simple single-row operations, searching on the PRIMARY KEY
![Page 25: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/25.jpg)
Libmysqld performance
• So does this mean that libmysqld is faster than mysqld?> No, it means that libmysqld has less overhead, that
performance is better is a side-effect of that• So why is this useful data then
> As it means that I need less OS and Hardwareresources to achieve the same levelof performance!
![Page 26: Anders Karlsson Principal Sales Engineer, MySQL MySQL Embedded - Getting started with libmysqld](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513effd55034674748b5b88/html5/thumbnails/26.jpg)
Resources and contacts
• Email me at:[email protected]
• Read my blog at:http://karlssonondatabases.blogspot.com/
• MySQL Forum for Embedded systems:http://forums.mysql.com/list.php?58
• Contribute to the community with samples and ideas, we need more of those for libmysqldhttp://forge.mysql.com