Saturday, October 14, 2017

MySQL InnoDB Tablespace Encryption

MySQL Tablespace Encryption is relatively simple to set up. You have to be using MySQL 5.7, innodb_file_per_table


The MySQL documentation covers everything you need to set this up:


But my blog post would be very short if I just shared a link. So....

Once upgraded and on MySQL 5.7 you need to have a valid location for your key file.  I have a MySQL datadir as /var/lib/mysql/data  this allows default locations for people to find it if needed but also allows a simple mounted location for more disks if needed as well. This also avoids having to do additional edits to selinux and etc since it already allows /var/lib/mysql.

touch /var/lib/mysql/keyring
chmod 750  /var/lib/mysql/keyring*
chown mysql:mysql  /var/lib/mysql/keyring

Edit the mysql .cnf file :

 ls  /usr/lib64/mysql/plugin/keyring_file.so
/usr/lib64/mysql/plugin/keyring_file.so

[mysqld]
# Keyring Plugin Installation
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring

After MySQL startup check your plugins:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        | 
| keyring_udf  | ACTIVE        |
+--------------+---------------+


INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_generate RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME 'keyring_udf.so';


mysql>  use test;
mysql>  SELECT keyring_key_generate('MyKey1', 'AES', 32);

mysql>  CREATE TABLE `enc_test` (
  `enc_test_id` varchar(255) NOT NULL,
  PRIMARY KEY (`enc_test_id`)
) ENGINE=InnoDB ;
 
mysql>  alter table test.enc_test encryption='Y';

mysql>  show create table enc_test \G
*************************** 1. row ***************************
       Table: enc_test
Create Table: CREATE TABLE `enc_test` (
  `enc_test_id` varchar(255) NOT NULL,
  PRIMARY KEY (`enc_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y'
1 row in set (0.00 sec)


Friday, April 22, 2016

Math with MySQL

I thought I posted this long ago... oh well....

We all know that math is the fundamental aspect of all life and the common language used around the world if not beyond. MySQL, like all databases, can help you with numerous aspects of math.

Here is a list of the functions: https://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html

Here are some simple examples to get you started. 
  • The Quadratic Formula  ax^2 + bx + c = 0

# 2x^2  – 4x – 3 = 0.
SET @a=  1;
SET @b=  3;
SET @c= -4;
SET @XX = ( -(@b) - SQRT(  POW(@b,2)  -4 * @a * @c) / POW(@a,2) ) ;
SET @YY = ( -(@b) + SQRT(  POW(@b,2)  -4 * @a * @c) / POW(@a,2) ) ; 
SET @XXX = MOD(@YY, @XX);

SELECT @XX / @XXX  as X;
+------+
| X    |
+------+
|   -4 |
+------+
SELECT @YY / @XXX  as X ;
+------+
| X    |
+------+
|    1 |
+------+


  • The Pythagorean Theorem (remember geometry 101):  A^2 + B^2 = C^2

SET @A = 14;
SET @B = 48;
SELECT @C := SQRT(POW(@A,2) +  POW(@B,2) );
+-------------------------------------+
| @C := SQRT(POW(@A,2) +  POW(@B,2) ) |
+-------------------------------------+
|                                  50 |
+-------------------------------------+


So that solves C and of course you use this to solve for A as well.

SELECT @A := SQRT(POW(@C,2) - POW(@B,2)) ; 
+-----------------------------------+
| @A := SQRT(POW(@C,2) - POW(@B,2)) |
+-----------------------------------+
|                                14 |
+-----------------------------------+


  • The logarithm and its identities   log xy = log x + log y 
http://www.businessinsider.com/the-17-equations-that-changed-the-world-2012-7#the-logarithm-and-its-identities-2


SET @X = 2;
SET @Y = 3;
SELECT concat(log(@X * @Y) ,' = ', log(@X) + log(@Y) ) as "logarithm and its identities"  ;
+---------------------------------------+
| logarithm and its identities          |
+---------------------------------------+
| 1.791759469228055 = 1.791759469228055 |
+---------------------------------------+

  • Euler's formula for polyhedra :   F - E + V = 2
http://www.businessinsider.com/the-17-equations-that-changed-the-world-2012-7#eulers-formula-for-polyhedra-6

SET @V = 4;  # Vertices
SET @E = 6;  # Edges
SET @F = 4;  # Faces
SELECT @V - @E + @F as Tetrahedron;

SET @V = 8;  # Vertices
SET @E = 12;  # Edges
SET @F = 6;  # Faces
SELECT @V - @E + @F as Hexahedron;

SET @V = 12;  # Vertices
SET @E = 30;  # Edges
SET @F = 20;  # Faces
SELECT @V - @E + @F as Icosahedron;

SET @V = 12;  # Vertices
SET @E = 30;  # Edges
SET @F = 20;  # Faces
SELECT @V - @E + @F as Icosahedron;

  • Einstein's theory of relativity  E = mc^2

SET @lbs = 190; # lbs 
SET @lb2gram = 453.6;  # 1 lbs = 453.6g 
SET @lbstograms := @lbs * @lb2gram  / 1;
SET @m := @lbstograms * 1 / 1000;
SET @c := POW(3.00 * POW(10,8), 2 );
SELECT @E := @m  * @c ;
+----------------+
| @E := @m  * @c |
+----------------+
|     7.75656e18 |
+----------------+ 

  • 1 = 0.9999.....

SELECT SUM(.9/(9/10));
+----------------+
| SUM(.9/(9/10)) |
+----------------+
|        1.00000 |
+----------------+
 



Saturday, July 25, 2015

Quick Password generation with MySQL

Here is a quick and simple way to generate a password your application using MySQL.
This query will create a upper and lower case randomly generated password in length and values.


SELECT CONCAT(UCASE(SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) )   , FLOOR( 1 + (RAND() * 4 )) , FLOOR( 4 + (RAND() * 12 )) )),
SUBSTRING('!@#$%^&*', FLOOR( 1 + (RAND() * 4))  , FLOOR( 1 + (RAND() * 8))  ),
SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , 5)) as password \G


This query will take a random value for the 1st part of the dynamic password and upper case it when applicable then the second half it adds some symbols then the remaining is in lower case because MD5 does that automatically. You of course can adjust whatever symbols you would prefer.

Example of results:

 CREATE TABLE `generated` (
  `password` varchar(255) NOT NULL
) ENGINE=InnoDB ;

MariaDB [(none)]> INSERT INTO test.generated SELECT CONCAT(UCASE(SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) )   , FLOOR( 1 + (RAND() * 4 )) , FLOOR( 4 + (RAND() * 12 )) )), SUBSTRING('!@#$%^&*', FLOOR( 1 + (RAND() * 4))  , FLOOR( 1 + (RAND() * 8))  ),SUBSTRING(  SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , 5)) as password; 

MariaDB [(none)]> select * from test.generated;
+-----------------------------------+
| password                          |
+-----------------------------------+
| 7232E1D9$%^&*f7de22c1b14f15c8a6   |
| DEE22B7F7#$%541adcdd1b8e2         |
| 2C4BB81001@#$%^&d0d9a2126         |
| 941D3B92D@#$%^a4b7be92            |
| 1CF2563254FC@#a79bb5              |
| 2AE86E7D6!@#$%^&*4ca03f2e8        |
| 074DD8D3#$e70a1                   |
| B2DDC!@#$%^47a252f79              |
| 3A6D0A#$%^&*01ce9278a2a           |
| E9FB4CD16E19!739db9faa1616505c    |
| 1ED7A2E1379B4!@#$%ebe60b          |
| E9B57D71DB1@#$%^e9a4f8c2e94bf3d35 |
| C97982!@#$8b5c534653c06           |
| DE8998057C8!@#$%^8977a807         |
| 7C7172EB1AE5$%^&*79d2b27          |
| B52CE71@#$%^&*4508                |
| AB17714F!@#$%^&203ba4ff80f8a6     |
| 558D@#$04087871e29ff54            |
| 27A78E8EF#$%^b8cee8628d81593      |
| 315F2EC4#ad9913ec0595c            |
+-----------------------------------+


Sunday, June 28, 2015

Fibonacci Sequence Procedure

Well it has been awhile since I posted, sorry about that. Today I was watching reruns of the TV show Fringe and when Walter referenced the Fibonacci Sequence I got side tracked with MySQL options for this.

Now this post already existed:

So I took that post and expanded on it a little, the result is a procedure that you can call and return the range within the Fibonacci Sequence that you are after.

The procedure is below:

delimiter //
CREATE PROCEDURE `Fibonacci`(IN POS INT, IN RANG INT, IN LIMTED INT)
BEGIN
       select FORMAT(Fibonacci,0) AS Fibonacci from (
       select @f0 Fibonacci, @fn:=@f1+@f0, @f0:=@f1, @f1:=@fn
       from   (select @f0:=0, @f1:=1, @fn:=1) x,
       information_schema.STATISTICS p limit LIMTED) y LIMIT POS, RANG;
END//
delimiter ;


You can call this and pass whatever values and ranges you are after.

So if you want the 5th value (starting from 0) in the sequence and the next value

> CALL Fibonacci(5,2,100);
+-----------+
| Fibonacci |
+-----------+
|         5 |
|         8 |
+-----------+


So if you want the 30th value (starting from 0) in the sequence and the next value

> CALL Fibonacci(30,2,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
+-----------+


So if you want the 150th value (starting from 0) in the sequence and the next value

> CALL Fibonacci(150,2,1000);
+--------------------------------------------+
| Fibonacci                                  |
+--------------------------------------------+
| 9,969,216,677,189,305,000,000,000,000,000  |
| 16,130,531,424,904,583,000,000,000,000,000 |
+--------------------------------------------+


 So you get the idea. Now you can also expand the range of results if you want more than 2 just change the 2nd value in the procedure call.

> CALL Fibonacci(0,10,100);
+-----------+
| Fibonacci |
+-----------+
| 0         |
| 1         |
| 1         |
| 2         |
| 3         |
| 5         |
| 8         |
| 13        |
| 21        |
| 34        |
+-----------+

> CALL Fibonacci(30,5,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
| 2,178,309 |
| 3,524,578 |
| 5,702,887 |
+-----------+


 Anyway, hope someone finds it helpful and credit for the base of query does go to the original post.

Friday, December 26, 2014

Find/parse a string from within a string

So I noticed a few different questions and posts about parsing a string out of another string recently. While some solutions included creating new functions and etc it can also be done within a single query in some cases.

For example, let us say that we are looking to pull out the domain from a URL. I will try to go into detail as to why and how this works.
We have the following table.

CREATE TABLE `parse_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `urldemo` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
+----+----------------------------+
| id | urldemo                    |
+----+----------------------------+
|  1 | http://www.mysql.com/      |
|  2 | http://www.percona.com/    |
|  3 | https://tools.percona.com/ |
|  4 | https://mariadb.com/       |
|  5 | http://planet.mysql.com/   |
|  6 | http://dev.mysql.com/doc/  |
+----+----------------------------+


The goal for this example is to disregard the http:// or https:// and anything after the .com. So we use LOCATE to find the locations.

The .com reference is easy since that is constant so we can start with that.

SELECT LOCATE('.com', urldemo), urldemo FROM parse_example;
+-------------------------+----------------------------+
| LOCATE('.com', urldemo) | urldemo                    |
+-------------------------+----------------------------+
|                      17 | http://www.mysql.com/      |
|                      19 | http://www.percona.com/    |
|                      22 | https://tools.percona.com/ |
|                      16 | https://mariadb.com/       |
|                      20 | http://planet.mysql.com/   |
|                      17 | http://dev.mysql.com/doc/  |
+-------------------------+----------------------------+


OK so we want to remove the / , what location is that?

SELECT LOCATE('.com', urldemo) as start, LOCATE('.com', urldemo) +4 as end, SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )  AS resulting , urldemo FROM parse_example;
+-------+-----+-----------+----------------------------+
| start | end | resulting | urldemo                    |
+-------+-----+-----------+----------------------------+
|    17 |  21 | /         | http://www.mysql.com/      |
|    19 |  23 | /         | http://www.percona.com/    |
|    22 |  26 | /         | https://tools.percona.com/ |
|    16 |  20 | /         | https://mariadb.com/       |
|    20 |  24 | /         | http://planet.mysql.com/   |
|    17 |  21 | /doc/     | http://dev.mysql.com/doc/  |
+-------+-----+-----------+----------------------------+

This gives us our end position, I only put the field aliases in to make the results easier to follow.

Now sorting out after http and https is actually very easy as well as they both have :// after them, so we just need the location of the second / in the string.


SELECT LOCATE('/', urldemo) as first, LOCATE('/', urldemo) +1 as second, urldemo
FROM parse_example;
+-------+--------+----------------------------+
| first | second | urldemo                    |
+-------+--------+----------------------------+
|     6 |      7 | http://www.mysql.com/      |
|     6 |      7 | http://www.percona.com/    |
|     7 |      8 | https://tools.percona.com/ |
|     7 |      8 | https://mariadb.com/       |
|     6 |      7 | http://planet.mysql.com/   |
|     6 |      7 | http://dev.mysql.com/doc/  |
+-------+--------+----------------------------+


These queries are just showing what the different aspects of the final query will be doing. So let us put it all together.


SELECT
TRIM(TRAILING SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )
FROM SUBSTRING(urldemo FROM LOCATE('/', urldemo) + 2 ) )  AS parsed_domain ,
urldemo as original_url
FROM parse_example;
+-------------------+----------------------------+
| parsed_domain     | original_url               |
+-------------------+----------------------------+
| www.mysql.com     | http://www.mysql.com/      |
| www.percona.com   | http://www.percona.com/    |
| tools.percona.com | https://tools.percona.com/ |
| mariadb.com       | https://mariadb.com/       |
| planet.mysql.com  | http://planet.mysql.com/   |
| dev.mysql.com     | http://dev.mysql.com/doc/  |
+-------------------+----------------------------+


Now hopefully that helps you be able to parse out whatever you need. This example is limited to a url. But since some examples of functions already here is my example of a function that you can use to parse whatever you need.



CREATE FUNCTION PARSE_STRING(delimiterA VARCHAR(50), delimiterB VARCHAR(50),  passed_string VARCHAR(255) )
RETURNS VARCHAR(255) DETERMINISTIC
RETURN
TRIM(TRAILING SUBSTRING(passed_string FROM LOCATE(delimiterB, passed_string)  )
FROM SUBSTRING(passed_string FROM LOCATE(delimiterA, passed_string) + CHAR_LENGTH(delimiterA) ) ) ;

SELECT PARSE_STRING('//','.com', urldemo) FROM parse_example;
+------------------------------------+
| PARSE_STRING('//','.com', urldemo) |
+------------------------------------+
| www.mysql                          |
| www.percona                        |
| tools.percona                      |
| mariadb                            |
| planet.mysql                       |
| dev.mysql                          |
+------------------------------------+


Pull a last name from a full name field:

SELECT PARSE_STRING('John ','', 'John Smith') ;
+----------------------------------------+
| PARSE_STRING('John ','', 'John Smith') |
+----------------------------------------+
| Smith                                  |
+----------------------------------------+


Pull the first name

SELECT PARSE_STRING('',' Smith', 'John Smith') ;
+-----------------------------------------+
| PARSE_STRING('',' Smith', 'John Smith') |
+-----------------------------------------+
| John                                    |
+-----------------------------------------+


Granted with the name examples you would need to know the delimiter values. But this is just an example you can build on.