Wednesday, May 23, 2018

Proxy MySQL :: HAproxy || ProxySQL & KeepAlived

So when it comes to routing your MySQL traffic several options exist.

Now I have seen HAproxy used more often with clients, it is pretty straight forward to set up. Percona has an example for those interested: 

Personally I like ProxySQL. Percona also has  few blogs on this as well
Percona also has ProxySQL version available 

I was thinking I would write up some examples but overall Percona has explained it all very well.  I do not want to take anything away from those posts, instead point out that a lot of good information is available via those urls. So instead of rewriting what has already been written, I will create a collection of information for those interested. 

First compare and decide for yourself what you need and want. The following link of course is going to be biased towards ProxySQL but it gives you an overall scope for you to consider. 
If you have a cluster or master to master and you do not care which server the writes vs reads go onto, just as long as you have a connection; then HAproxy is likely a simple fast set up for you. 

The bonus with ProxySQL is the ability to sort traffic in a weighted fashion, EASY. So you can have writes go to node 1, and selects pull from node 2 and node 3. Documentation on this can be found here:
Yes it can be done with HAproxy but you have to instruct the application accordingly. 
This is handled in ProxySQL based on your query rules.

Now the obvious question here: OK so how do you keep ProxySQL from becoming the single point of failure?  

You can invest is a robust load balancer and etc etc etc ... Toss hardware at it.... Or make it easy on yourself and support open source and use KeepAlived.  This is VERY easy to set up and all of it is documented again well here: 
If you ever dealt with lua and mysql-proxy, ProxySQL and Keepalived should be very simple for you. If you still want it for some reason: https://launchpad.net/mysql-proxy

Regardless if you choose HAproxy, ProxySQL or another solution, you need to ensure not to replace once single point of failure with another and keepalived is a great for that. So little reason to not do this if you are using a proxy. 

So a few more things on ProxySQL. 





Happy Birthday MySQL 1995

Happy Birthday MySQL  ! Turned 23 today !

Tuesday, March 20, 2018

MySQL 8.0.4rc

MySQL 8.0.4rc was just released as "Pre-General Availability Draft: 2018-03-19".

I decided to take a quick peek and note my impressions here.  Some of this is old news for many as this release has been talked about for awhile but I added my thoughts anyway.. 

First thing I noticed was a simple issue of using the updated mysql client. My older version was still in my path that resulted in 

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded
So simple fix and make sure you are using the valid updated mysql client. Of course other options existed like changing the authentication plugin back to  mysql_native_password but why bother, use the secure method.  This is a very good enhancement for security so do not be shocked if you have some connection issues while you get your connections using this more secure method. 


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.4-rc-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

So the first very cool enhancement... 

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_role_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_role_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Password_reuse_history` smallint(5) unsigned DEFAULT NULL,
  `Password_reuse_time` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`Host`,`User`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Users and global privileges'
1 row in set (0.00 sec)

Yep user table is InnoDB and has own TableSpace. 

With the addition of the new Data Dictionary you will now notice Information_schema changes. 
So as a simple example the Columns table historically has not been a view but that has now changed , along with many others as you can see via the url provided. 


mysql> show create table COLUMNS \G
*************************** 1. row ***************************
                View: COLUMNS
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` 

This appears to be done to help performance with the information_schema but removing the temporary table creations per queries into the information_schema. 

Chapter 14 of the documentation goes into depth on this, the provided url below will help you find more information and future blog posts might touch more on this. 
The previously mentioned Data Dictionary then also leads into the ability to have atomic Data Definition Language (DDL) statements or  atomic DDL. 


This is likely to trip up a few transactions if you do not review your queries before setting up replication to a new MySQL 8.0 instance. I say that because of how the handling of table maintenance could be impacted. If you write clean queries with "If Exists" it won't be a big problem. Overall it is a more transaction based feature that protects your data and rollback options. 


Resource management looks very interesting and I will have to take more time to focus on this as it is a new feature with MySQL 8.0. Overall you can assign groups and no longer have to set priority of query but let your grouping define how a query should behave and resources allotted to it. 

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)

mysql> desc INFORMATION_SCHEMA.RESOURCE_GROUPS;
ERROR 1109 (42S02): Unknown table 'RESOURCE_GROUPS' in information_schema

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql> desc INFORMATION_SCHEMA.RESOURCE_GROUPS;
+------------------------+-----------------------+------+-----+---------+-------+
| Field                  | Type                  | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| RESOURCE_GROUP_NAME    | varchar(64)           | NO   |     | NULL    |       |
| RESOURCE_GROUP_TYPE    | enum('SYSTEM','USER') | NO   |     | NULL    |       |
| RESOURCE_GROUP_ENABLED | tinyint(1)            | NO   |     | NULL    |       |
| VCPU_IDS               | blob                  | YES  |     | NULL    |       |
| THREAD_PRIORITY        | int(11)               | NO   |     | NULL    |       |
+------------------------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


More insight into your InnoDB buffer pool cache in regards to the indexes that are in it is now available. 

mysql> desc INFORMATION_SCHEMA.INNODB_CACHED_INDEXES ;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| SPACE_ID       | int(11) unsigned    | NO   |     |         |       |
| INDEX_ID       | bigint(21) unsigned | NO   |     |         |       |
| N_CACHED_PAGES | bigint(21) unsigned | NO   |     |         |       |
+----------------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)


If you are unsure what to set the InnoDB Buffer pool , log_sizes or flush method MySQL will set these for you now based on the available memory. 

innodb_dedicated_server

[mysqld]
innodb-dedicated-server=1

mysql> select @@innodb_dedicated_server;
+---------------------------+
| @@innodb_dedicated_server |
+---------------------------+
|                         1 |
+---------------------------+

This simple test set my innodb_buffer_pool_size to 6GB  for example when the default is 128MB. 

Numerous JSON additions have been made as well as regular expression changes. Both of which look promising. 

The only replication enhancement per this release itself is that is now supports binary logging of partial updates to JSON documents using a compact binary format. 

However overall many features are available ( you can read all about them here) ,  one of which (I wish my client had tomorrow ) is replication filers per channel. 
My test instance already had binary logs enabled, but they are on by default now along with TABLE based versus file based master & slave info, ( big fan of having that transaction based by default )

Overall keep in mind this is just a first glance at this release and very high level thoughts on it, many other changes exist. Looking over other blog posts about this release as well as the manual and release notes will also help. Certainly download and review as it looks to be very promising for administration, security and replication points of view. 

Tuesday, March 13, 2018

MySQL Cheat Sheet

So first I have posted in sometime as felt I should be. I have been very busy still working with MySQL and all related forks and failed to put out blogs as I felt I should. So I will work on that.

Now That being said I recalled the other day a website I used to love because it was a common VI cheat sheet list. The syntax you know , you know you need it, but type it 3 times until it right. When it does get entered right you look at it dumbfounded , I thought I wrote that already.

So I figured why not a simple list of common MySQL commands that we all either type 50 times a month or should know like the back of our hand but forget when the client is looking over our shoulder.
For starters..
We set up a new MySQL 5.7.6+ server and log in..
Need to change password before we can do anything. But it is Alter user not Set pass.
We want to know how to read the password still as it is in clear text.

ALTER USER
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Set Password is
SET PASSWORD FOR 'bob''@'localhost' = PASSWORD('cleartext password');

Purge Binary Logs
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 00:00:00
PURGE BINARY LOGS BEFORE NOW() - interval 3 DAY;


MySQL Dump
# COMPACT WILL REMOVE DROP STATEMENTS
mysqldump --events --master-data=2 --routines --triggers --compact --all-databases > db.sql
mysqldump --events --master-data=2 --routines --triggers --all-databases > NAME.sql
mysqldump --opt --routines --triggers dbname > dbname.sql
mysqldump --opt --routines --triggers --no-create-info joomla jforms > dataonly.sql

Turn off Foreign Keys for a moment
SET GLOBAL foreign_key_checks=0;



Skip Grants
/usr/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf --skip-grant-tables
vi /etc/mysql/my.cnf
[mysqld]
skip-grant-tables



BinLog reviews
--base64-output=DECODE-ROWS & --verbose
mysqlbinlog --defaults-file=/home/anothermysqldba/.my.cnf --base64-output=DECODE-ROWS --verbose binlog.005862 > 005862.sql


MYSQL SECURE CLIENT
mysql_config_editor print --all
mysql_config_editor set --user=mysql --password --login-path=localhost --host=localhost
mysql --login-path=localhost -e 'SELECT NOW()';


Swap
sudo swapoff -a
To set the new value to 10: echo 10 | sudo tee /proc/sys/vm/swappiness
sudo swapon -a

IF INFORMATION SCHEMA IS SLOW
set global innodb_stats_on_metadata=0;

AWS Variables
CALL mysql.rds_show_configuration;
> call mysql.rds_set_configuration('binlog retention hours', 24);
> call mysql.rds_set_configuration('slow_launch_time', 2);


Find what table a column name is in
SELECT TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'fieldname' ;
Client says it is in TableA but they have 50 databases.. What schema has TableA
SELECT TABLE_SCHEMA , TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME = 'TableA' ;

Adjust Slave workers
Select @@slave_parallel_workers;
Stop Slave; Set GLOBAL  slave_parallel_workers=5; Start Slave;


MySQL Multi
5.6>
To start both : mysqld_multi start 1,2
To check on status of both: mysqld_multi report 1,2
To check on status or other options you can use just one

5.7<
[mysqld1] BECOMES [mysqld@mysqld1]
systemctl start mysqld@mysqld1
systemctl start mysqld@mysqld2
systemctl start mysqld@mysqld3
systemctl start mysqld@mysqld4
MySQL Upgrade System tables only
mysql_upgrade --defaults-file=/home/anothermysqldba/.my.cnf --upgrade-system-tables

SKIP REPLICATION ERROR
STOP SLAVE; SET GLOBAL sql_slave_skip_counter =1; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G

Friday, October 13, 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.

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

touch /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)


OK great you have a table.
What about backups.

Well from my current experience innobackupex does not work, but you can still use xtrabackup.

xtrabackup --defaults-file=/home/anothermysqldba/.my.cnf   --port=3306 --socket=/tmp/mysql.sock  --backup  --keyring-file-data=/var/lib/mysql/keyring --server_id=1 --target-dir=/backups/database/ ; xtrabackup --prepare --keyring-file-data=/var/lib/mysql/keyring  --target-dir=/backups/database/