Friday, June 10, 2016

Python App connecting to MySQL with mysql.connector + Failover



The following is the very basic Python App connecting to MySQL Database. (01-connect.py)
It connects to the MySQL Database (127.0.0.1:3306 with root and empty password). 
import mysql.connector
import sys

if len(sys.argv) > 1 :
        _pass = sys.argv[1]
else :
        _pass = ''
cnx = mysql.connector.connect(user='root', password=_pass, host='127.0.0.1', port=3306, database='mysql')

cnx.close()

The alternative using Python Dictionary parameter -

This can be useful way to connect to MySQL Database with named parameters. (02-connect.py)
import mysql.connector
import sys

_pass = ''
if len(sys.argv) > 1 :
        _pass = sys.argv[1]

config = {
        'user': 'root',
        'password': _pass,
        'host': '127.0.0.1',
        'database': 'mysql',
        'raise_on_warnings': True,
}

cnx = mysql.connector.connect(**config)
cnx.close()

Error Handling with MySQL  (03-connectTry.py)
The following example shows the connection to the default db ("test") with 127.0.0.1:3306 using root empty password user.    The error handling processing using try/except/else together with mysql.connector.errorcode.  By providing first argument with invalid password, the python app will get  exception - "errorcode.ER_ACCESS_DENIED_ERROR".  Whereas by giving 2nd argument with a non-exists database, the python app will generate exception "errorcode.ER_BAD_DB_ERROR".

import mysql.connector
from mysql.connector import errorcode
import sys

_pass=''
_db='test'
if len(sys.argv) > 1 :
        _pass = sys.argv[1]
if len(sys.argv) > 2 :
        _db = sys.argv[2]

try:
  cnx = mysql.connector.connect(user='root', host='127.0.0.1', port=3306,
                                password=_pass,
                                database=_db)

  print("connect success")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
       print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
       print("Database does not exist")
  else:
       print(err)
else:
  print ("Finally - close connection")
  cnx.close()
Lastly,  mysql.connector provides connection property "failover".
Please find more details on 
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

The following sample (04-failover.py) provides a sample to connect to databases (3306 and 3316).  If 3306 is stopped, the connection goes to the 3316. 

import mysql.connector
from mysql.connector import errorcode

DB_NAME = 'mysql'

operation = 'SELECT @@hostname, @@port'

config = {
        'raise_on_warnings': True,
        'failover' : [{
                'user': 'root',
                'password': '',
                'host': '127.0.0.1',
                'port': 3306,
                'database': 'mysql',
                }, {
                'user': 'root',
                'password': '',
                'host': '127.0.0.1',
                'port': 3316,
                'database': 'mysql',
                }]
}
cnx = mysql.connector.connect(**config)

cursor = cnx.cursor()

try:
  for result in cursor.execute(operation, multi=True):
     if result.with_rows:
        print("rows producted by statement '{}':".format(result.statement))
        row = cursor.fetchone()
        while row:
           print(row)
           row = cursor.fetchone()
     else:
        print("Number of rows affeted by statement '{}':{}".format(result.statement, result.rowcount))
except mysql.connector.Error as err:
        print(err.msg)

Enjoy!


Tuesday, April 5, 2016

Running SYS Schema - "diagnostics" to get insight of your MySQL Database



You may want to dump diagnostic information about the running MySQL Database.  
 
For MySQL 5.7, the SYS schema comes with the installation.  You can find the corresponding information on



For MySQL 5.6,the SYS schema can be downloaded from the following URL

The zip file as ‘mysql-sys-master.zip’ is expanded, where the sys_56.sql can be loaded into the MySQL 5.6 database.

# mysql -uroot -h127.0.0.1 < sys_56.sql

Using MySQL Workbench and connecting to the Server, you can find the SYS schema installed.


Running the “diagnostics” stored procedure :
                Parameter :
120 : the max execution time for diagnostics
30 :  The interval time for output
current : The mode of diagnostics information
                                               
mysql > tee output.txt;
mysql > call sys.diagnostics (120, 30, ‘current’);
mysql > tee off;



The following section gives you a brief about this diagnostic output.

Part I : The System Environment and Configuration
It shows the basic information about the OS and the MySQL Server.  
+-------------------------+---------------------------------------------------------+
| Name                    | Value                                                   |
+-------------------------+---------------------------------------------------------+
| Hostname                | virtual-21.localhost                                    |
| Port                    | 3306                                                    |
| Socket                  | /var/lib/mysql/data/EE/EE.sock                          |
| Datadir                 | /var/lib/mysql/data/EE/data1/                           |
| Server UUID             | e0a37616-a942-11e5-9d3f-080027dbc0db                    |
| ----------------------- | ------------------------------------------------------- |
| MySQL Version           | 5.6.24-enterprise-commercial-advanced-log               |
| Sys Schema Version      | 1.5.0                                                   |
| Version Comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| Version Compile OS      | linux-glibc2.5                                          |
| Version Compile Machine | x86_64                                                  |
| ----------------------- | ------------------------------------------------------- |
| UTC Time                | 2015-12-23 08:39:01                                     |
| Local Time              | 2015-12-23 16:39:01                                     |
| Time Zone               | SYSTEM                                                  |
| System Time Zone        | HKT                                                     |
| Time Zone Offset        | 08:00:00                                                |
+-------------------------+---------------------------------------------------------+
17 rows in set (0.01 sec)

The Configuration part has the Global Variables listing at the time of the execution.
-------------------------------------------------------------------------------------------|
| Variable_name                                          | value                           |
+--------------------------------------------------------+---------------------------------|
| autocommit                                             | ON                              |
| automatic_sp_privileges                                | ON                              |
| wait_timeout                                           | 28800                           |
+--------------------------------------------------------+---------------------------------|
444 rows in set (0.02 sec)


Part II : The Performance Schema Info

Listing about what has been setup about Performance Schema (Actors, Consumers, Instruments, Objects, Threads)



Part III : Replication Status
For my case, it is empty.
+---------------------------+
| Replication Status        |
+---------------------------+
| No Replication Configured |
+---------------------------+
1 row in set (0.05 sec)


Part IV – For each Iteration, the following list of information will be printed out to the output file.  (in my case, each iteration is about 30 seconds interval until 120seconds max time)
+---------------------+--------------------------+

| NOW()               | The following output is: |
+---------------------+--------------------------+
| 2015-12-23 16:39:01 | Iteration Number 1       |
+---------------------+--------------------------+
1 row in set (0.34 sec)

+--------------------------+
| The following output is: |
+--------------------------+
| SHOW MASTER STATUS       |
+--------------------------+
1 row in set (0.34 sec)

+---------------------------+----------+--------------+------------------+--------------------------------------------+
| File                      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+---------------------------+----------+--------------+------------------+--------------------------------------------+
| myreplication1-bin.000004 |      191 |              |                  | e0a37616-a942-11e5-9d3f-080027dbc0db:1-416 |
+---------------------------+----------+--------------+------------------+--------------------------------------------+
The Show Engine InnoDB status, Transaction, and process list are listed.
The details is not discussed in this blog.



Part V – End of execution, the following information is captured and compared.

a.   The performance schema status
b.   The Schema Information
+------------------------+
| Total Number of Tables |
+------------------------+
|                    231 |
+------------------------+
1 row in set (1 min 30.56 sec)

c.   The Overall Status
a.   Performance Schema Statement Digest Average Latency Histogram
  . = 1 unit
  * = 2 units
  # = 3 units

(0 - 5729ms)      321 | ###########################################################################################################
(5729 - 11458ms)  0   |
(11458 - 17187ms) 1   | .
(17187 - 22916ms) 0   |
(22916 - 28645ms) 0   |
(28645 - 34374ms) 0   |
(34374 - 40104ms) 0   |
(40104 - 45833ms) 0   |
(45833 - 51562ms) 0   |
(51562 - 57291ms) 0   |
(57291 - 63020ms) 1   | .
(63020 - 68749ms) 0   |
(68749 - 74478ms) 1   | .
(74478 - 80207ms) 0   |
(80207 - 85936ms) 0   |
(85936 - 91665ms) 0   |

  Total Statements: 325; Buckets: 16; Bucket Size: 5729 ms;
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (1 min 30.64 sec)



There are some configuration values for the diagnostics - sys.sys_config as shown :





‘diagnostics.allow_i_s_tables’  - ON/OFF, if ON, the information schema about the tables information will be dumped. 
‘diagnostics.include_raw’ – ON/OFF, if ON, the raw information will be included.