Menu

MySQL 5.7 Labs — Inserting, Updating, and Deleting Records via HTTP

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.

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:

1

2

3

4

5

6

7

8

9

10

#

# 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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

# 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:

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:

1

2

3

4

5

6

7

mysql>CREATE TABLE`names`(

->`id`int(11)NOTNULLDEFAULT'1000',

->`name_first`varchar(40)DEFAULTNULL,

->`name_last`varchar(40)DEFAULTNULL,

->PRIMARY KEY(`id`)

->)ENGINE=InnoDB DEFAULTCHARSET=latin1;

Query OK,0rows affected(0.04sec)

We need to insert some data into the table:

1

2

3

4

5

6

7

8

9

10

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,9rows affected(0.01sec)

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.