Install the memcached plugin on MySQL and access it from Java

Put the memcached plugin in MySQL

1. Install libevent-devel

/bin/bash


# yum -y install libevent-devel
2. Add memcache settings to my.cnf

/etc/my.cnf


daemon_memcached_option = "-p11211"
daemon_memcached_engine_lib_name = innodb_engine.so
daemon_memcached_r_batch_size = 1
daemon_memcached_w_batch_size = 1
3. Install the memcached plugin

mysql


mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
mysql> show plugins;

Confirm that the plugin called daemon_memcached is included

4. Create a test table

mysql


mysql> use test
mysql> create table user (id varchar(16), passwd varchar(256), primary key(id)) engine=InnoDB;
mysql> insert into user (id, passwd) values ('user01', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8');
5. Make it accessible via memcache

mysql


mysql> insert into innodb_memcache.containers (`name`,`db_schema`,`db_table`,`key_columns`,`value_columns`,`flags`,`cas_column`,`expire_time_column`,`unique_idx_name_on_key`) VALUES ('user', 'test', 'user', 'id', 'passwd',0,0,0,'PRIMARY');
mysql> UNINSTALL PLUGIN daemon_memcached;INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

** @@ (value in name column of innodb_memcache.containers) accessible as .key **

6. Implement the Java side

↓ Download spymemcached from around http://www.java2s.com/Code/Jar/s/Downloadspymemcached27jar.htm

This time, when POSTing ID and Pass in JSON, 200 is returned if there is user registration, and 401 is returned if there is no user registration (ID and Pass do not match). Create Rest API with JavaEE JAX-RS. I tried to.

Memcache.java


package memcached;

import java.net.InetSocketAddress;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.Map;

import javax.enterprise.context.RequestScoped;
import javax.ws.rs.Consumes;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.Response.ResponseBuilder;

import net.spy.memcached.MemcachedClient;

@RequestScoped
@Path("/auth")
@Produces("application/json")
@Consumes("application/json")
public class Memcache {

	@POST
	public Response auth (Map<String, String> param) {
		ResponseBuilder response = null;
		try {
			MemcachedClient memcachedClient = new MemcachedClient (new InetSocketAddress("192.168.56.102", 11211));
			if ( sha256( param.get("password") ).equals( memcachedClient.get( "@@user." + param.get("id") ).toString() ) ) {
				response = Response.status(200);
			} else {
				response = Response.status(401);
			}
		} catch (Exception e) {
			response = Response.status(400);
		}
		return response.build();
	}

	private String sha256(String plaintext) throws Exception{
		MessageDigest md = MessageDigest.getInstance("SHA-256");
		StringBuilder sb = new StringBuilder();
		md.update(plaintext.getBytes());
	    for (byte b : md.digest()) {
	        String hex = String.format("%02x", b);
	        sb.append(hex);
	    }
	    return sb.toString();
	}
}

** "value_columns: must be mapped to CHAR, VARCHAR, or BLOB columns" **, so it is impossible to handle JSON type fields as they are with memcached **: cry: (telnet) There is a response when I try it, but the contents are messed up) → https://dev.mysql.com/doc/refman/5.6/ja/innodb-memcached-internals.html I'm not sure if CAST is okay, but please be able to handle it without doing anything. > Oracle

(Addition) Using generated column, I generated a TEXT type column from a JSON type column and specified it in value_columns.

mysql


mysql> create table userinfo (id varchar(16), infojson json, userinfo text as (cast(infojson as char) engine=InnoDB;
mysql> insert into innodb_memcache.containers (`name`,`db_schema`,`db_table`,`key_columns`,`value_columns`,`flags`,`cas_column`,`expire_time_column`,`unique_idx_name_on_key`) VALUES ('userinfo', 'test', 'userinfo', 'id', 'userinfo',0,0,0,'PRIMARY');
mysql> UNINSTALL PLUGIN daemon_memcached;INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

As a result, ** NG! ** It would be possible if the reverse (from text type to json type is generated column), but since the update query using the json function cannot be used, the taste is halved. ..

Recommended Posts

Install the memcached plugin on MySQL and access it from Java
Install Java 9 on windows 10 and CentOS 7
Kick ShellScript on the server from Java
Install OpenJDK (Java) on the latest Ubuntu
Access MySQL on a Docker container from a local (host OS) Java program
Copying the repository and getting a mysql2 error on the first bundle install
Compile and run Java on the command line
Install Java on Mac
Import the instance and use it on another screen
Java language from the perspective of Kotlin and C #
Install the latest hardware drivers from ELRepo on CentOS
Install java and maven using brew on new mac
Access API.AI from Java
Install Eclipse on Mac and translate it into Japanese
21 Load the script from a file and execute it
Install MySQL 5.6 on CentOS6 [How to specify the version]
Install mecab-ipadic-neologd on Sakura VPS (ubuntu18.04) with low memory and use it from python
Access Web API on Android with Get and process Json (Java for the time being)
Find the address class and address type from the IP address with Java
JSON in Java and Jackson Part 1 Return JSON from the server
[JDBC] I tried to access the SQLite3 database from Java.
Implement Java Interface in JRuby class and call it from Java
Install docker and docker-compose on ubuntu in the shortest process
[Kotlin] Get Java Constructor / Method from KFunction and call it
Correct the character code in Java and read from the URL
Install OpenJDK7 (JAVA) on ubuntu 14.04
Install the plugin in Eclipse
Install Java on WSL Ubuntu 18.04
Install java 1.8.0 on Amazon linux2
Steps to install samba on CentOS 8 and connect from Windows 10 Explorer
I opened the menu bar (option menu) on Android and saw it.
How to install and configure the monitoring tool "Graphite" on Ubuntu
Access the in-memory data grid Apache Ignite from a Java client
Notes on building Kotlin development environment and migrating from Java to Kotlin
From fledgling Java (3 years) to Node.js (4 years). And the impression of returning to Java
Install Ubuntu20.04 on RaspberryPi 4 and build Kubernetes to run the container
[Java] How to convert from String to Path type and get the path
Steps to install Maven on Mac and use it in Eclipse
Access the network interface in Java
Install Java and Tomcat with Ansible
Install JDK and JRE on Ubuntu 16.10
Install Java with zip on Windows
Install Java Open JDK 8 on CentOS 7
Access Teradata from a Java application
Install and configure jenv on macOS
Upgrade from MYSQL5.7 to 8.0 on CentOS 6.7
Consideration on the 2017 Java Persistence Framework (1)
Install apache 2.4.46 from source on CentOS7
[MySQL] [java] Receive date and time
About go get and go install from Go1.16
Steps to install MySQL 8 on CentOS 8
Enable Java 8 and Java 11 SDKs on Ubuntu
The road from JavaScript to Java
Notes on Java path and Package
Install Java development environment on Mac
Find the address class and address type from the IP address with Java [No. 2 decoction]
Update JAVA to the latest version to 1.8.0_144 (when downloading from the web and updating)
JSON in Java and Jackson Part ③ Embed JSON in HTML and use it from JavaScript
[Java] Get Json from URL and handle it with standard API (javax.script)
[Java] How to retrieve the parameters passed from html on the server side
A note on the differences between interfaces and abstract classes in Java