MySQL 5.7 labs and the HTTP Plugin – inserting, updating and deleting records in MySQL via HTTP
January 15, 2015 10 Comments
In the MySQL Labs version of MySQL version 5.7, there is a new HTTP plugin. The HTTP plugin documentation from the labs site provides this information (from MySQL Labs):
The HTTP Plugin for MySQL adds HTTP(S) interfaces to MySQL. Clients can use the HTTP respectively HTTPS (SSL) protocol to query data stored in MySQL. The query language is SQL but other, simpler interfaces exist. All data is serialized as JSON. This version of MySQL Server HTTP Plugin is a Labs release, which means it’s at an early development stage. It contains several known bugs and limitation, and is meant primarily to give you a rough idea how this plugin will look some day. Likewise, the user API is anything but finalized. Be aware it will change in many respects.
In other words, with a simple HTTP URL, you can access and modify your data stored in MySQL. Here is an overview from the documentation:
The HTTP Plugin for MySQL is a proof-of concept of a HTTP(S) interface for MySQL 5.7.
The plugin adds a new protocol to the list of protocols understood by the server. It adds the HTTP respectively HTTPS (SSL) protocol to the list of protocols that can be used to issue SQL commands. Clients can now connect to MySQL either using the MySQL Client Server protocol and programming language-dependent drivers, the MySQL Connectors, or using an arbitrary HTTP client.
Results for SQL commands are returned using the JSON format.
The server plugin is most useful in environments where protocols other than HTTP are blocked:
• JavaScript code run in a browser
• an application server behind a firewall and restricted to HTTP access
• a web services oriented environment
In such environments the plugin can be used instead of a self developed proxy which translates HTTP requests into MySQL requests. Compared to a user-developed proxy, the plugin means less latency, lower complexity and the benefit of using a MySQL product. Please note, for very large deployments an architecture using a proxy not integrated into MySQL may be a better solution to clearly separate software layers and physical hardware used for the different layers.
The HTTP plugin implements multiple HTTP interfaces, for example:
• plain SQL access including meta data
• a CRUD (Create-Read-Update-Delete) interface to relational tables
• an interface for storing JSON documents in relational tables
Some of the interfaces follow Representational State Transfer (REST) ideas, some don’t. See below for a description of the various interfaces.
The plugin maps all HTTP accesses to SQL statements internally. Using SQL greatly simplifies the development of the public HTTP interface. Please note, at this early stage of development performance is not a primary goal. For example, it is possible to develop a similar plugin that uses lower level APIs of the MySQL server to overcome SQL parsing and query planning overhead.
In this post, I will show you how to install the plugin and use HTTP commands to retrieve data. The documentation also provides other examples. We aren’t going to explain everything about the plugin, as you will need to download the documentation.
First, you will need to download the MySQL Labs 5.7 version which includes the plugin. This download is available from the MySQL Labs web site.
After MySQL 5.7 is installed, you will want to add these lines to your my.cnf/my.ini file under the [mysqld] section:
# # Default database, if no database given in URL # myhttp_default_db = httptest # # Non-SSL default MySQL SQL user # myhttp_default_mysql_user_name = http_sql_user myhttp_default_mysql_user_passwd = sql_secret myhttp_default_mysql_user_host = 127.0.0.1
There are other options for the plugin, but we will skip them for this post.
# Change only, if need be to run the examples! # # General settings # # myhttp_http_enabled = 1 # myhttp_http_port = 8080 # myhttp_crud_url_prefix = /crud/ # myhttp_document_url_prefix = /doc/ # myhttp_sql_url_prefix = /sql/ # # # # Non-SSL HTTP basic authentication # # myhttp_basic_auth_user_name = basic_auth_user # myhttp_basic_auth_user_passwd = basic_auth_passwd # # SSL # # myhttp_https_enabled = 1 # myhttp_https_port = 8081 # myhttp_https_ssl_key = /path/to/mysql/lib/plugin/myhttp_sslkey.pem
After modifying the my.cnf/my.ini file, restart mysql and then install the plugin from a mysql prompt. Before proceeding, be sure to also check to make sure the plugin is installed:
mysql> INSTALL PLUGIN myhttp SONAME 'libmyhttp.so';
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='myhttp'\G
*************************** 1. row ***************************
PLUGIN_NAME: myhttp
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: DAEMON
PLUGIN_TYPE_VERSION: 50705.0
PLUGIN_LIBRARY: libmyhttp.so
PLUGIN_LIBRARY_VERSION: 1.5
PLUGIN_AUTHOR: Andrey Hristov, Ulf Wendel
PLUGIN_DESCRIPTION: HTTP Plugin for MySQL
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.03 sec)
We will need to create the user for accessing our database, and grant permissions:
mysql> CREATE USER 'http_sql_user'@'127.0.0.1' IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (1.89 sec)
mysql> SET old_passwords = 0;
Query OK, 0 rows affected (0.05 sec)
mysql> SET PASSWORD FOR 'http_sql_user'@'127.0.0.1' = PASSWORD('sql_secret');
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT ALL ON myhttp.* TO
-> 'http_sql_user'@'127.0.0.1';
Query OK, 0 rows affected (0.12 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.58 sec)
We will need to create a table for our example. The table will be a very simple table with three fields – ID, first and last names:
mysql> CREATE TABLE `names` (
-> `id` int(11) NOT NULL DEFAULT '1000',
-> `name_first` varchar(40) DEFAULT NULL,
-> `name_last` varchar(40) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)
We need to insert some data into the table:
INSERT INTO `names` (name_first, name_last) VALUES ('Clark','Kent');
INSERT INTO `names` (name_first, name_last) VALUES ('Bruce','Wayne');
INSERT INTO `names` (name_first, name_last) VALUES ('Hal','Jordan');
INSERT INTO `names` (name_first, name_last) VALUES ('Barry','Allen');
INSERT INTO `names` (name_first, name_last) VALUES ('Diana','Prince');
INSERT INTO `names` (name_first, name_last) VALUES ('Arthur','Curry');
INSERT INTO `names` (name_first, name_last) VALUES ('Oliver','Queen');
INSERT INTO `names` (name_first, name_last) VALUES ('Ray','Palmer');
INSERT INTO `names` (name_first, name_last) VALUES ('Carter','Hall');
Query OK, 9 rows affected (0.01 sec)
Now that we have our table and table data, we can test a select statement with an HTTP URL. You may use a browser for this, but since I like to work with command line tools, I am going to use curl, a command line tool for doing all sorts of URL manipulations and transfers. Here is a simple select statement via curl. Use the plus sign (+) for spaces.
Select all of the names in the table:
$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names"
[
{
"meta":[
{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_first","org_column":"name_first","charset":33,"length":120,"flags":0,"decimals":0},
{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_last","org_column":"name_last","charset":33,"length":120,"flags":0,"decimals":0}
],
"data":[
["Clark","Kent"],
["Bruce","Wayne"],
["Hal","Jordan"],
["Barry","Allen"],
["Diana","Prince"],
["Arthur","Curry"],
["Oliver","Queen"],
["Ray","Palmer"],
["Carter","Hall"]
],
"status":[{"server_status":34,"warning_count":0}]
}
]
If you want to use a browser, you might have to authenticate the connection (enter the user name and password):
And here is the output from submitting the URL in a browser:
URL: http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names
Selecting a single name:
$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names+where+name_first+=+'Clark'"
[
{
"meta":[
{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_first","org_column":"name_first","charset":33,"length":120,"flags":0,"decimals":0},
{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_last","org_column":"name_last","charset":33,"length":120,"flags":0,"decimals":0}
],
"data":[
["Clark","Kent"]
],
"status":[{"server_status":34,"warning_count":0}]
}
]
Deleting a row:
$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/delete+from+names+where+name_first+=+'Hal'"
{"server_status":34,"warning_count":0,"affected_rows":1,"last_insert_id":0}
Inserting a row:
$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/INSERT+INTO+names+(name_first,+name_last)+VALUES+('Hal','Jordan');"
{"server_status":2,"warning_count":0,"affected_rows":1,"last_insert_id":1018}
In a future post, I will show you how to use Perl to connect via HTTP and then parse the results.
 |
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. |
|
Tony is the author of Twenty Forty-Four: The League of Patriots
Visit http://2044thebook.com for more information. |
info [at] Scripting
Scripting MySQL 