Friday, December 16, 2011

In Case you missed these

Just in case you missed these posts....

Facebook has some interesting blogs on benchmarks, InnoDB and etc...

W hat is in the InnoDB buffer pool? 12/15/2011 02:14 PM

Dynamic padding for tpcc-mysql 11/21/2011 11:51 AM

The shutdown benchmark 11/15/2011 05:29 PM

The effect of page size on InnoDB compression 11/07/2011 11:40 AM

A big thank you to the MySQL web team and engineers for helping combat spam and keeping the bugs.mysql.com site spam free.

reCaptcha is now installed and is a requirement when adding a comment to a current bug. We want the community to be able to review and comment on bugs with little spam as possible getting in the way.

Sunday, December 11, 2011

MySQL Cluster to MySQL Server ... Part 2

I previously posted blog about the topic of a MySQL Cluster set up and replication to a MySQL Server. The first blog was just the installation, now lets test the cluster and get replication started. Both are extremely easy.

Some people might ask, “Why set up replication from a cluster? It is a HA system why replication?”

The ability to move data out of the cluster and allow others to do reporting, data exports, simple backups all can be done easily with it replicated into another MySQL server. Yes a Cluster can do all of those queries, but taking advantage of the replication to keep some users and queries out of your production database is rarely a bad thing.

Testing the Cluster Install. Of course more MySQL Documentation:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-quick.html



mysql> SHOW ENGINES\G 

*************************** 1. row ***************************

Engine: ndbcluster

Support: YES

Comment: Clustered, fault-tolerant tables

Transactions: YES

XA: NO

Savepoints: NO



*************************** 3. row ***************************

Engine: ndbinfo

Support: YES

Comment: MySQL Cluster system information storage engine

Transactions: NO

XA: NO

Savepoints: NO



mysql>  USE test;

Database changed

mysql>;  CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;

Query OK, 0 rows affected (0.28 sec)



mysql>  SHOW CREATE TABLE ctest \G

*************************** 1. row ***************************

Table: ctest

Create Table: CREATE TABLE `ctest` (

`i` int(11) DEFAULT NULL

) ENGINE=ndbcluster DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

That was easy enough...Table is built and using the ndbcluster engine.

So lets set up replication from a cluster to a MySQL Innodb Server.

1^st create a user for the replication.



GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'10.%' IDENTIFIED BY '

<password>

';

flush privileges;

For this example I am jut using a Windows Virtual Box instance. The MySQL windows installer worked great. For a guy who ever runs windows, it was nice that this was so easy. (http://dev.mysql.com/downloads/installer/)

On the Cluster server, I made sure to have my server_id and big logs set.



vi /etc/my.cnf 

[mysqld]

server-id=1

log-bin                                 = /var/lib/mysql/mysql_demo-bin

restarted the sql server and confirmed it was running:



mysql> show master status\G< 

*************************** 1. row *************************** 

File: mysql_demo-bin.000001 

Position: 112 Binlog_Do_DB:  

Binlog_Ignore_DB:   1 

row in set (0.00 sec)

So lets create a simple table that we can use for test later.

PLEASE take note... This is just a table to show it replicated. If you create tables for real use in a Cluster and replicated ... ALWAYS have a primary key!



mysql>  CREATE TABLE slave_test (i INT) ENGINE=NDBCLUSTER;

Query OK, 0 rows affected (0.28 sec)

Now back on my windows mysql server. Since it came with workbench I used this to set the server_id (server_id =2) and restart the server. I then opened the sql editor and added the master settings.

via_mysql_workbench>

 CHANGE MASTER TO

-> MASTER_HOST='10.132.241.18',

-> MASTER_PORT=3306,

-> MASTER_USER='slave_user',

-> MASTER_PASSWORD='<password>';

via_mysql_workbench> start slave;

via_mysql_workbench>use test;

via_mysql_workbench> SHOW CREATE TABLE slave_test ;

'slave_test', 'CREATE TABLE `slave_test` (

`i` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1'

mysql> show slave status;

...

Slave_IO_Running, Yes

Slave_SQL_Running Yes

...

So now via these blog posts.. I have a cluster with a geolocated node both on Oracle Linux 6 being replicated to another remote location MySQL server running Windows via a Virtual box instance. I can also now use Workbench to connect to the local windows server as well as the cluster if needed. This is not a production system but it does show that Cluster can be installed easily, remotely, and takes advantage of commodity systems.

Part 1 -- The Install
Part 2 -- Testing Cluster more and Replication Setup

Friday, December 9, 2011

Congrats to three more MySQL Oracle ACE members...

It is truly amazing the amount of knowledge and talent that work with and support MySQL and the MySQL community. Thanks to all of you.

Over the last month the MySQL ACE list has grown even more:

George J. Trujillo is now an Oracle ACE and a MySQL Oracle ACE.

Wagner Bianchi became a MySQL Oracle ACE not long ago.

Roland Bouman is the latest addition as a MySQL Oracle ACE.

Congrats to all of them !

MySQL Cluster to MySQL Server Part 1

Recently I was working on a MySQL Cluster that is replicated to a MySQL server. I will create a few blog posts to show how all of this can be done. This is of course not the only way to do this.. Any feedback or other options are welcomed in comments for the community to use.

Part one: the install

First the MySQL Documentation about MySQL Cluster is very good I will make references to it for your convenience.
You can go about this a few different ways but this is a simple RPM install:

Download all the related rpms from http://dev.mysql.com/downloads/cluster/ and install.

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-linux-rpm.html) is here to help you with the installs as well.

rpm -ihv MySQL-Cluster-gpl-*.rpm

Ok that was easy enough ... Make sure we have what we expected...



rpm -qa | grep MySQL



MySQL-Cluster-gpl-debuginfo-7.1.17-1.el6.i686

MySQL-Cluster-gpl-client-7.1.17-1.el6.i686

MySQL-Cluster-gpl-clusterj-7.1.17-1.el6.i686

MySQL-Cluster-gpl-test-7.1.17-1.el6.i686

MySQL-Cluster-gpl-server-7.1.17-1.el6.i686

MySQL-Cluster-gpl-devel-7.1.17-1.el6.i686

MySQL-Cluster-gpl-management-7.1.17-1.el6.i686

MySQL-Cluster-gpl-extra-7.1.17-1.el6.i686

MySQL-Cluster-gpl-storage-7.1.17-1.el6.i686

Just so this can be a tiny bit of a more real world example, I also installed an external node in a remote (out of the subnet/datacenter) location.



[root@remote_cluster src]# rpm -ihv MySQL-Cluster-gpl-storage-*.rpm

OK software install is straight forward and easy, but what do you do now ?

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-configuration.html) on how to configure the cluster are straight forward and very easy to follow.

Based on that documentation, I have set all the ips in all the related configuration files ...

My Config file looks like this after removal of comments. I have a local node and a remote node for this simple example.



#/var/lib/mysql-cluster/config.ini 

[ndb_mgmd]

# Management process options:

hostname=10.132.241.18                  # Hostname or IP address of MGM node

datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

NodeId=1



[ndbd default]

# Options affecting ndbd processes on all data nodes:

NoOfReplicas=4                            # Number of replicas

datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

DataMemory=80M                # How much memory to allocate for data storage

IndexMemory=18M               # How much memory to allocate for index storage



[ndbd]

hostname=mysql_demo.localdomain

NodeId=3



[ndbd]

hostname=10.159.37.130

NodeId=4



[mysqld]

Nodeid=10



[mysqld]

Nodeid=30



[mysqld]

Nodeid=40

Nodes... Start your engines..... ok so really, lets get it started...

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-first-start.html) on how to start it up for the 1st time..

On Localhost



/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini

MySQL Cluster Management Server mysql-5.1.56 ndb-7.1.17

On Localhost / Data Node



/usr/sbin/ndbd

2011-12-08 12:21:16 [ndbd] INFO     -- Angel connected to 'localhost:1186'

2011-12-08 12:21:16 [ndbd] INFO     -- Angel allocated nodeid: 3

On remote node :



./ndbd

2011-12-08 12:25:39 [ndbd] INFO     -- Angel connected to '10.132.241.18:1186'

2011-12-08 12:25:40 [ndbd] INFO     -- Angel allocated nodeid: 6

On Localhost Start the sql server and check the nodes.



# /etc/init.d/mysql start



# /usr/local/bin/ndb_mgm -e show

Connected to Management Server at: localhost:1186

Cluster Configuration



---------------------

[ndbd(NDB)]     4 node(s)

id=3    @127.0.0.1  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)

id=4 (not connected, accepting connect from mysql_demo.localdomain)

id=5    @10.159.37.130  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)

id=6    @10.159.37.130  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)



[ndb_mgmd(MGM)] 1 node(s)

id=1    @10.132.241.18  (mysql-5.1.56 ndb-7.1.17)



[mysqld(API)]   3 node(s)

id=10 (not connected, accepting connect from any host)

id=30 (not connected, accepting connect from any host)

id=40 (not connected, accepting connect from any host)

OK lets log in and test it ?



# mysql



Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.56-ndb-7.1.17-cluster-gpl MySQL Cluster Server (GPL)

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

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> select VERSION();

+-------------------------------+

| VERSION() |

+-------------------------------+

| 5.1.56-ndb-7.1.17-cluster-gpl |

+-------------------------------+

1 row in set (0.00 sec)

Of course if you need to stop and restart (MySQL Documentation: http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-install-shutdown-restart.html) a cluster, it is a simple process.



/usr/local/bin/ndb_mgm -e shutdown

/etc/init.d/mysql stop

Others posts on cluster installs:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-installation.html

http://alexyu.se/comment/37

http://dbperf.wordpress.com/2011/03/04/mysql-cluster-setup-and-replication-between-ndb-and-non-ndb-engines-2/

Mine:
Part 1 -- The Install

Part 2 -- Testing Cluster more and Replication Setup

Wednesday, November 30, 2011

XFCE TO LXDE

Wow how things change quickly sometimes. As per a previous post I have recently moved to XFCE. It was great and things worked very well. Then, it just went nuts...

Active windows failed to work and I was stuck on a single desktop (I live with multi-desktop), mouse failures and etc. To quote the great Dr. Peter Venkman, "Human sacrifice, dogs and cats living together... mass hysteria! "

Anyway, I needed to get something done at the moment and not fight with it so I did a quick yum install of LXDE. I had to go to the gnome session to do this, that is how crazy the XFCE went. But I am currently happy with the LXDE.

I will get back to XFCE and see if I can find out what in the world made it go nuts but everything takes time.

Now I am not going to say I am at home yet, after all I did that with XFCE and things went crazy. But it is working very well so far.

Tuesday, November 29, 2011

OTN MySQL User Forum

One thing I enjoy about this job is being able to meet face to face the with MySQL Community. We have a big community and it is hard to keep up ! Sadly, I will not be able to make the OTN MySQL User Forum in Brazil but I want to encourage the community to attend the event.

It is a free forum, full of technical sessions, taught by engineers and leaders of the MySQL open source community will also explore Oracle's strategy with regard to MySQL, as well as the new MySQL Enterprise innovation and what to expect from MySQL 5.6.

Best of luck to the MySQL Brazil Community.

Monday, November 28, 2011

MySQL and Debian Linux

It is available now ! MySQL 5.5.18 now offers Debian Linux (.deb) packages.

They are available via the http://dev.mysql.com/downloads/mysql site !

Finally the Debian community can upgrade easily to MySQL 5.5 !

Tuesday, November 22, 2011

Database Chain

I was at an event recently and the topic of replication stirred the curiosity of the audience. A few audience members had Master to Master but they wanted to move away from that. Others had multiple slaves but wanted little downtime and backend work if a master failed. Relayed replication or a database chain is an option to solve some of their issues. Another option would be Cluster but it depends on your infrastructure, budget and application, some of them are looking into this as well. Is a chain the best solution for everyone, of course not. While I cannot do consultant work to help them, I can blog about it…

A relayed replication environment allows you to, of course, have replicated databases but also have a replication environment that is still available if or when the master fails. The image to the left shows a simple example of how the databases work together.

One of the first things to keep in mind is that your application/environment needs to be able to handle a master switch. That means do not reference the master by IP or hostname. Instead use NIS, LDAP, alias in host files, or just DNS. Something simple as Server A == Master, then your MySQL clients and applications reference master when doing connections. This allows you to adjust the reference point quickly when a master fails. So if Server A crashed, adjust reference to Server B == Master. You still have 2 slaves running as well. You can then stop slave on Server B, examine Server A and gather any data you feel might have been lost. Sometimes starting the Slave on Server B to pull anything left over from Server A can work but auto increment ids could cause a conflict easily, so use caution when doing this. Once I am done with Server A, I move that to become a new slave of Server D, I now have four servers again.

I like to have at least four servers when doing a chain. If I only had three servers then I am very dependant on Server B, the 1^st slave. If that box was to fail I am left building a slave from backups or off the master. If I have four and Server B crashes I can move the Master to Server C and still have a replicated system. Now, this of course is entirely dependant on your slaves staying up to date. Building your application to process data that will not slow down a slave can help support this type of environment.

Another nice feature coming soon with MySQL is timed delayed replication. This option can be used with a chain as well. The time delayed option pulls all data and only applies it to the db based on the threshold you set. So you could build out a live backup solution using a database chain. Server A is your slave, Server B can get time delayed set to a Day, Server C gets 6 Days (So a week back from master), Server D gets set to 24 Days back (~month from master). Now you have full access to historical data if needed. If a user drops a table that shouldn’t have, it is a lot easier to dump that table and import it rather than pulling it off a tape or disk 1^st.

Do not let the slave servers go to waste either. Allow your database handles to use the all of the slaves accordingly. Keep all the reads you can off the master. Create a script/class that uses a handle for your master that uses an account with appropriate access. Then create another script/class to handle connections to the slaves. This slave connection can be created to accomplish a few things. First allow it to have a default of seconds back from master per query passed to it. Second create it to use the aliases of all the slaves and connect to check what is the seconds back, then use the valid server per threshold. This is a very fast check but the process can be enhanced to have times set on a schedule with another script, depends on your application. Now this allows your slave connection to take a query that has a threshold of 1 day (running a report for all of yesterday for example) and push that down to execute on Slave D as long as it is within the threshold.

Monday, November 21, 2011

Gnome 3 to XFCE

So I recently moved my laptop onto Fedora 16. I had some issues with the previous Linux distro and figured I would go with this for my laptop.

Issues with Gnome 3 are not new but I figured I would collect a few links for everyone so hey can get past the issues with this.

1st - Get icons back on your desktop -
http://json8.wordpress.com/2011/08/05/enabling-desktop-icons-in-gnome-3/

2nd - Get workspaces to work on more than 1 monitor. ( THIS DROVE ME CRAZY)
http://gregcor.com/2011/05/07/fix-dual-monitors-in-gnome-3-aka-my-workspaces-are-broken/

3rd. The wireless worked great so that was helpful.

4th Set your shortcuts to switch to workspace because going to activities everytime to get anyplace drives me crazy. Shows some outside users testing was needed more.

So if this is enough for you then great! But remember gnome 3 is not the only option :
http://digitizor.com/2011/08/04/linus-torvalds-ditches-gnome-for-xfce/
http://linux.slashdot.org/story/11/08/04/0115232/linus-torvalds-ditches-gnome-3-for-xfce

A quick yum install of xfce and my life was so much better.

If your a long time Gnome user, like myself, KDE was not an option. No reason I am just not a KDE guy. However, I did come to find that I am a XFCE guy. Within seconds of entering the XFCE environment I was home.

Thursday, November 17, 2011

OTN Developer Day: MySQL - Minneapolis, MN

Thanks to the community for a great turn out at the OTN Developer Day: MySQL in Minneapolis, MN.

The room was packed and we had to bring more tables in to handle the crowd. Sessions covered enterprise, replication, overviews and perfomance.

I hope the local MySQL user group can support the new members in their area.

http://www.meetup.com/mn-mysql/

Best of luck to everyone and thanks again.

Tuesday, November 8, 2011

Easy Phone Number Checks

So this is a simple free example of how to check a phone number via a mysql database. Yes more complete validation options are available, some even have to pay for, but this is better than no check at all.

The data is available here : wget -q http://www.telcodata.us/custom/telcodata.dum p

It loads a table like this:

CREATE TABLE `telcodata` (

`npa` int(3) NOT NULL,

`exchange` int(3) NOT NULL,

`thousands` int(3) NOT NULL DEFAULT '-1',

`company` text,

`ratecenter` text,

`clli` text,

`type` text,

`emaildomain` text,

`parentco` text,

`state` text,

`zip` text,

`lat` float(9,5) DEFAULT NULL,

`lon` float(9,5) DEFAULT NULL,

`latanum` int(11) DEFAULT NULL,

`ocn` varchar(4) NOT NULL,

`ilec` varchar(4) DEFAULT NULL,

`ilecname` text,

`tandem` text,

KEY `rcstate` (`ratecenter`(11),`state`(2)),

KEY `npaexch` (`npa`,`exchange`,`thousands`)

)

Once the data is loaded you can use this with data you have gathered. This could be from websites or data warehouse data who knows...

Then you can check to see what type of a phone number it is ?

SELECT npa,exchange,thousands, company , ratecenter , type, parentco, CONCAT(npa,exchange,'0000','@',emaildomain) as send_a_txt

FROM telcodata where npa=303 and exchange=335 and ( thousands=8 or thousands < 0 ) ORDER BY thousands desc LIMIT 1 \G

npa: 303

exchange: 335

thousands: 8

company: CELLCO PARTNERSHIP DBA VERIZON WIRELESS - CO

ratecenter: DENVER

type: WIRELESS

parentco: Verizon

send_a_txt: 3033350000@vtext.com

1 row in set (0.00 sec)

I have placed 0000 in place of the thousands field, if you wanted a valid thousands option you would replace it with your data.

A none cell looks like this

SELECT npa,exchange,thousands, company , ratecenter , type, parentco , CONCAT(npa,exchange,'0000','@',emaildomain) as send_a_txt FROM telcodata where npa=303 and exchange=623 and ( thousands=9 or thousands < 0 ) \G
*************************** 1. row ***************************
       npa: 303
exchange: 623
thousands: -1
   company: QWEST CORPORATION
ratecenter: DENVER
      type: RBOC
parentco: NULL
send_a_txt: NULL
1 row in set (0.05 sec)

In this case you can check to see if the data given is a cell phone or not by checking the type field. Your tests are only as good as your data but this is a start.

Friday, November 4, 2011

MySQL Utilities

I recently explored the mysql-utilities scripts available on launchpad ( https://launchpad.net/mysql-utilities ) The examples below are from my local test db.

The code is available via a quick bzr download after you sign in with your launchpad id of course.

bzr launchpad-login <user_id_ from_launchpad>

bzr branch lp:~mysql/mysql-utilities/trunk

MySQL Utilities code does have some requirements, Python between 2.6 and 3.x . So check your python version , some of the distributions have older versions. ( # python –version ) If you want the manuals, then Sphinx (which also requires Jinja) is also required. Of course a connector to the database would be helpful. You can get the latest python connector, via launchpad as well, at https://launchpad.net/myconnpy.

If your curious about more of the options for setup the help commands will give you a full list. ( python setup.py –help-commands ). Otherwise a quick set up is as simple as python setup.py install. I happen to not have the /etc/profile.d/mysql-utilities.sh on my system and the set up tossed an error. A simple touch of this file and all worked fine.

So you have it all installed, now what? You will find inside the scripts directory (../trunk/scripts) approximately 15 different python scripts.

mysqldbcompare.py , mysqldbcopy.py, mysqldbexport.py ,mysqldbimport.py , mysqldiff.py , mysqldiskusage.py , mysqlindexcheck.py, mysqlmetagrep.py , mysqlprocgrep.py , mysqlreplicate.py, mysqlrplcheck.py , mmysqlrplshow.py , mmysqlserverclone.py, mmysqlserverinfo.py , mysqluserclone.py

Some quick examples via the employee database.

Index Checks:

./mysqlindexcheck –server=<username>:<password>@<HOST>:<PORT> employees

# Source on 192.168.0.2: ... connected.

# The following indexes are duplicates or redundant for table employees.dept_emp:

CREATE INDEX emp_no ON employees.dept_emp (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.dept_emp ADD PRIMARY KEY (emp_no, dept_no)

# The following indexes are duplicates or redundant for table employees.dept_manager:

CREATE INDEX emp_no ON employees.dept_manager (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.dept_manager ADD PRIMARY KEY (emp_no, dept_no)

# The following indexes are duplicates or redundant for table employees.salaries:

CREATE INDEX emp_no ON employees.salaries (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.salaries ADD PRIMARY KEY (emp_no, from_date)

# The following indexes are duplicates or redundant for table employees.titles:

CREATE INDEX emp_no ON employees.titles (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.titles ADD PRIMARY KEY (emp_no, title, from_date)

Quick Server information:

./mysqlserverinfo --server=<username>:<password>@<HOST>:<PORT> employees

# Source on 192.168.0.2: ... connected.

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

If you need grep the tables to find something?

./mysqlmetagrep --server=<username>:<password>@<HOST>:<PORT> --pattern=emp_no

+----------------------------+--------------+---------------+------------+-------------+----------+

+----------------------------+--------------+---------------+------------+-------------+----------+

+----------------------------+--------------+---------------+------------+-------------+----------+

Disk Usage:

./mysqldiskusage --server=<username>:<password>@<HOST>:<PORT>

# Source on 192.168.0.2: ... connected.

# Database totals:

+---------------------+-----------------+

| db_name | total |

+---------------------+-----------------+

….

Total database disk usage = 24,721,809,136 bytes or 23.00 GB

They are tools that are quick and easy to get installed and use to help and enhance your MySQL experience.

MySQL Utilities are part of the MySQL Workbench but they can be used as a stand alone option.

Also available is the testing suite “mut” which is available under the mysql-test directory.

# ./mut.py --server=<username>:<password>@<HOST>:<PORT>

MySQL Utilities Testing - MUT

Parameters used:

Display Width = 75

Sorted = True

Force = False

Test directory = './t'

Utilities directory = '../scripts'

Starting port = 3310

Servers:

Connecting to 192.168.0.2 as user kdl on port 3306: CONNECTED

---------------------------------------------------------------------------

TEST NAME STATUS TIME

===========================================================================

experimental.t1 [pass] 48

experimental.t2 [pass] 14

experimental.t3 [pass] 3

experimental.t4 [pass] 10

main.check_index [FAIL]

ERROR: Result file mismatch:

- # Source on localhost: ... connected.