Monday, April 29, 2013

"Tools" of the trade

I figured it might be worth creating a list of the top tools of the trade that we all use.
First and foremost this is to say thanks to everyone who helps create these tools.
Second it is to allow others who do not use these to see and learn how these can be used and why.
  • MySQL command line client
    • This is a given I know but it is the best access to MySQL. 
    • mysql -p --prompt="\u@\h [\d]>\_ Master >"
  • Xtrabackup
    • ./xtrabackup --defaults-file=/etc/my.cnf --backup --stats --target-dir=~/backups/  --prepare  --export --user=root --innodb_data_home_dir=/var/lib/mysql/ --innodb_data_file_path=/var/lib/mysql/ 
  • mysqlbinlog 
  • mysqltuner 
    • A good overall view into what you could have going on with your database. 
  • Percona Toolkit 
    • exampes:
      • pt-query-digest 
        • example: pt-query-digest --ask-pass /var/lib/mysql/mysql-slow.log
      • pt-table-checksum
        • example: pt-table-checksum --ask-pass
      • pt-table-sync
        • example: pt-table-sync --no-check-triggers --ask-pass --no-check-triggers --execute --print
      • pt-show-grants 
        • pt-show-grants  --ask-pass
  • MySQL Utilities 
    • exampes:
      • python mysqldiskusage --server=root:password@localhost
      • python mysqlindexcheck --server= root:password@localhost ps_helper.schema_index_statistics
      • python mysqlprocgrep --server= root:password@localhost --match-user=root
    • This might make some people question but... Knowledge is power. This is the best location to learn about MySQL. 
    • If you have to interview a MySQL DBA candidate, pick some common active authors and ask if they know who that person is. It will allow you to understand if they research the latest trends and information around MySQL or are just content with their experience.  
  • MySQL Sandbox 
  • mytop 
    • inspired by top but with a focus on MySQL
  • innotop
    • innotop is a 'top' clone for MySQL 
    • This might also make some question. I add this because it is a quick and good way for people to get started and create a my.cnf that is better than the default installed version. If nothing else you can use it just to compare with what you think it should be. 
You might notice I am not a fan of the GUI. No offense to those GUI tools available but why GUI if you do not need it. But that is just my opinion and here is a list of GUI tools just to be fair. After all schema map is helpful and easily done with MySQL Workbench

Other tools for review:

Sunday, April 28, 2013


Now that MySQL 5.6 has been out for a little while and Percona 5.6 is, at the time of writing this, in alpha, we will all start to look more into the NoSQL solutions related to MySQL. ( I will continue to watch and see what MariaDB does in this regard. ) But anyway... since developers and management continue to be curious about what NoSQL can offer....

This is just to give a high level overview of using Memcache as a NoSQL solution and keep the  MySQL & InnoDB database and data in tact.

While this blog is not the first on this topic, I hope it ties a few things together for some people.

Good reads and references are here:

# php --version

PHP 5.4.14 (cli) (built: Apr 27 2013 14:22:04)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies

Otherwise my php install also has this.... 

more /etc/php.d/memcache.ini

; ----- Options to use the memcache session handler
;  Use memcache as a session handler
;  Defines a comma separated of server urls to use for session storage

First activate the plugin...

mysql> install plugin daemon_memcached soname "";

Create a database so we do not just use the standard test db and demo_test used in all other examples. Just to show it is not locked to just that.

CREATE DATABASE nosql_mysql_innodb_memcache;
use nosql_mysql_innodb_memcache;

CREATE TABLE nosql_mysql (
   `demo_key` VARCHAR(32),
   `demo_value` VARCHAR(1024),
   `demo_flag` INT,
   `demo_cas` BIGINT UNSIGNED,
   `demo_expire` INT,
   primary key(demo_key)

use innodb_memcache;

INSERT INTO containers VALUES ('DEMO','nosql_mysql_innodb_memcache','nosql_mysql','demo_key','demo_value','demo_flag','demo_cas','demo_expire','PRIMARY');

select * from containers\G
*************************** 1. row ***************************
                  name: DEMO
             db_schema: nosql_mysql_innodb_memcache
              db_table: nosql_mysql
           key_columns: demo_key
         value_columns: demo_value
                 flags: demo_flag
            cas_column: demo_cas
    expire_time_column: demo_expire
unique_idx_name_on_key: PRIMARY

OK now lets just have some data in this table to prove that it runs like normal MySQL. 

mysql>use nosql_mysql_innodb_memcache;
mysql> insert into nosql_mysql VALUES ('key1','demo data','1',1,1);
Query OK, 1 row affected (0.04 sec)

select * from nosql_mysql  \G
*************************** 1. row ***************************
   demo_key: key1
 demo_value: demo data
  demo_flag: 1
   demo_cas: 1
demo_expire: 1
1 row in set (0.00 sec)

So let us see what we can do with PHP now... 

$memcache->connect('localhost', 11211) or die ("Could not connect");

$version = $memcache->getVersion();
echo "Server's version: ".$version."<br/>\n";

$memcache->set('key3', 'FROM PHP') or die ("Failed to save data at the server");
echo "Data from the cache:<br/>\n";
echo $memcache->get('key3');

Server's version: 5.6.10
Data from the cache:

Just for grins we can do the typical example we see and use telnet as well.

telnet 11211
Connected to
Escape character is '^]'.
set key2 1 0 11
Hello World
get key2
VALUE key2 1 11
Hello World
get key3
VALUE key3 0 8
OK let us see what we have in the MySQL database

set session TRANSACTION ISOLATION LEVEL read uncommitted;
SELECT @@GLOBAL.tx_isolation, @@tx_isolation \G
*************************** 1. row ***************************
@@tx_isolation: READ-UNCOMMITTED

> select * from nosql_mysql\G
*************************** 1. row ***************************
   demo_key: key1
 demo_value: demo data
  demo_flag: 1
   demo_cas: 1
demo_expire: 1
*************************** 2. row ***************************
   demo_key: key2
 demo_value: Hello World
  demo_flag: 1
   demo_cas: 2
demo_expire: 0
*************************** 3. row ***************************
   demo_key: key3
 demo_value: FROM PHP
  demo_flag: 0
   demo_cas: 2
demo_expire: 0

So with PHP and using MySQL InnoDB memcache we can see that we are able to address code directly and easily. 

Of course others have their own opinions ( but I think this is just the start of how we will be able to use NoSQL with MySQL.