sqlhjalp: November 2011

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.