Showing posts with label Mysql. Show all posts
Showing posts with label Mysql. Show all posts

Monday, January 12, 2015

Check The Number Of MySQL Open Database Connections on Linux Or Unix-like Server

I'm a new MySQL server user. My server is running on a CentOS Linux. How can I check the number of active MySQL connections on Linux based system?

You can use the following commands on Linux or Unix-like systems:

a) mysqladmin status command
b) MySQL show status command
c) netstat or ss commands

mysqladmin status command example

Open the terminal App or login to the remote server using ssh:
 
ssh vivek@server1.cyberciti.biz
 
Type the following command to get a short status message from the MySQL server:
 
mysqladmin status
## OR ##
mysqladmin status -u root -p
## OR ##
mysqladmin status -h db1.cyberciti.biz -u root -p
 
Sample outputs:
Uptime: 691356  Threads: 5  Questions: 83237956  Slow queries: 102736  Opens: 3585  Flush tables: 1  Open tables: 1019  Queries per second avg: 120.398

MySQL show status command to see open database connections example

First, connect to the your mysql server:
 
mysql -u root -p
 
Type the following sql query to see the number of connection attempts to the MySQL server includes both failed and successful connection attempts:
mysql> show status like 'Conn%';
Sample outputs:
Fig.01: "show status like 'Conn%';" in action
Fig.01: "show status like 'Conn%';" in action

You can use the following sql command to see the number of currently open connectionsat mysql> prompt:
mysql> show status like '%onn%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Aborted_connects         | 7       |
| Connections              | 6304067 |
| Max_used_connections     | 85      |
| Ssl_client_connects      | 0       |
| Ssl_connect_renegotiates | 0       |
| Ssl_finished_connects    | 0       |
| Threads_connected        | 7       | <---- No of currently open connections
+--------------------------+---------+
7 rows in set (0.00 sec)

Use show processlist sql command to see the number of open connections

Type the following sql command at mysql> prompt to see the number of currently open connections:
mysql> show processlist;
+---------+------------+-------------------+------------+---------+------+-------+------------------+
| Id      | User       | Host              | db         | Command | Time | State | Info             |
+---------+------------+-------------------+------------+---------+------+-------+------------------+
| 6297128 | root       | localhost         | NULL       | Query   |    0 | NULL  | show processlist |
| 6308321 | faqwpblogu | 10.10.29.66:42945 | lesaibkfaq | Sleep   |    1 |       | NULL             |
| 6308323 | faqwpblogu | 10.10.29.74:46993 | lesaibkfaq | Sleep   |    0 |       | NULL             |
| 6308325 | faqwpblogu | 10.10.29.74:46995 | lesaibkfaq | Sleep   |    1 |       | NULL             |
| 6308326 | faqwpblogu | 10.10.29.74:46996 | lesaibkfaq | Sleep   |    0 |       | NULL             |
+---------+------------+-------------------+------------+---------+------+-------+------------------+
5 rows in set (0.00 sec)
The above output indicates four currently open connection for user called 'faqwpblogu' from app server located at 10.10.29.66 and 10.10.29.74.

MySQL show status sql command summary

I suggest that you read the following pages for more info:
  1. SHOW STATUS Syntax
  2. Server Status Variables

Use netstat or ss (Linux only) command to list open database connections

The syntax is as follows for netstat command or ss command:
 
netstat -nat | grep 10.10.29.68:3306
 
This will just give you an overview. I suggest that you use above sql commands only.
1 comment:
Labels: , , ,

Wednesday, January 7, 2015

Linux Tutorial: How to Build Apache, PHP5 and MySQL From Source

I’m going to show you how to do it the hard way, building everything from source, the way Linus intended. Let’s get started.

Step 1: Determine if it’s right for you.

This is not the only way to get a LAMP server going and not always the best way. You can get these files from your package manager in your favorite distribution or use something like XAMPP like I mentioned in previous articles. For most developers this a perfectly acceptable way to do it, and likely a better use of your time. Here are the reasons you would want to build from source:
You should only build from source if:
  • You have a good idea of what you’re doing.
  • You have time and patience.
  • You want the latest and greatest stuff and your package manager doesn’t have it yet.
  • You want to do something really custom and need to change crucial options.
  • You want full control over the installation details.
  • You need the maximum performance and stability possible.
  • You want to learn something.
If those don’t apply to you, then head over to this site and get XAMPP, it’s an awesome project.

Step 2: Get Sources.

Here is all the stuff you’re going to need. I put asterisks next to the “maybe” stuff that you might not need depending on how updated your system is.
Apache:
PHP:
MySQL:
Cmake: *
Mcrypt: *
APR:
PCRE:
Additional reading about setting up an Apache server can be found here:
Verify your files!
Make sure and download the .asc file also and do a quick checksum on it. This extra few seconds is well worth the benefit. Here is how you do it with Apache:
You should verify all your files before building, just to make sure you didn’t get a corrupt file.
Notes before you start:
You will see version numbers here, and those numbers are for the latest version of the software available when I wrote this. By the time you read this, they will have changed. If you want you can get these exact version numbers and build them exactly as in the article, but usually the newest version of the software is best.
I use “nice -20” before running make to ensure it gets priority. You don’t have to do this, and usually its not even required as most kernels will prioritize it pretty well.
You do not have to be root to build this stuff, in fact you shouldn’t be. You only need root privileges to install the software but you can build it with a regular user.

Step 3: Start Building

Install cmake:
./configure –prefix=/usr –mandir=/usr/share/man gmake
Install MySql:
groupadd mysql useradd -g mysql mysql
tar zxvf mysql-5.5.24.tar.gz (file name will differ)
cd mysql-5.5.24 ./configure –prefix=/usr/local/mysql make make install cd /usr/local/mysql chown -R mysql . chgrp -R mysql . scripts/mysql_install_db –user=mysql chown -R root . chown -R mysql var cp support-files/my-medium.cnf /etc/my.cnf
bin/mysqld_safe –user=mysql &
This will install your MySQL server. If you have issues getting it working, consult this page and follow those instructions.
Install Mcrypt
bunzip2 libmcrypt-2.5.8.tar.bz2 tar xvf libmcrypt-2.5.8.tar cd libmcrypt-2.5.8 ./configure –prefix=/usr –mandir=/usr/share/man nice -20 make make install
Install APR
tar xvzf apr-1.4.6.tar.gz /usr/src/http-2.4.2/srclib cd apr-1.4.6 ./configure nice -20 make
APR Utils:
tar xvzf apr-util-0.9.19.tar.gz ./buildconf –with-apr=/usr/src/apr-1.4.6/ ./configure
Install PCRE
tar xvzf pcre-8.30.tar.gz ./configure nice -20 make make install
Install Apache
cd httpd-2.4.2 CFLAGS=”-O2” ./buildconf –with-apr-util=/usr/src/httpd-2.4.2/srclib/apr-util-1.4.1 –with-apr=/usr/src/httpd-2.4.2/srclib/apr-1.4.6/ –enable-so –prefix=/usr/local/apache2 CFLAGS=”-O2” ./configure nice -20 make make install
Install PHP
We are going to install PHP 5.4.3 which was the most current at the time of this writing.
tar -xvzf php-5.4.3.tar.gz cd php-5.4.3
Note: The configure paramaters are where you really customize your installation. The following is the one I used for my machine, but your needs may be different. I will show you two of them and if mine doesn’t work, you can use a failsafe one.
My config:
./configure –with-apxs2=/usr/local/apache2/bin/apxs –with-mysql –with-curl=/usr/bin/curl –enable-bcmath –enable-calendar –with-curl=/usr/bin/curl –with-gdbm –enable-exif –enable-ftp –with-gd –with-freetype-dir=/usr –with-jpeg-dir=/usr –with-png-dir=/usr/bin/libpng-config –enable-gd-native-ttf –with-gettext –with-mcrypt=/usr/bin/libmcrypt-config –with-mhash –with-mysql=/usr/local/mysql/ –with-mysqli=/usr/bin/mysql_config –with-openssl –with-pdo-mysql=/usr –enable-soap –enable-wddx –with-xsl –with-zlib –enable-mbstring –enable-zip
Failsafe:
./configure –with-apxs2=/usr/local/apache2/bin/apxs –with-mysql
Make sure and get what you really need, but remember you can always recompile later. Now copy over a PHP.ini
cp php.ini-development /usr/local/apache2/php/php.ini
NOTE: Here’s an error you might run into:
configure: error: Cannot find MySQL header files under /usr. Note that the MySQL client library is not bundled anymore!
If you do install the mysql client libraries:
git clone git://github.com/Sannis/node-mysql-libmysqlclient.git cd node-mysql-libmysqlclient nice -20 make make test make clean sudo make install
After it’s built you’ll need to do a couple things:
vi /usr/local/apache2/conf/httpd.conf
Make sure this is here:
LoadModule php5_module modules/libphp5.so
And add this to the file also:
SetHandler application/x-httpd-php
Then restart Apache:
/usr/local/bin/apache2/apachectl restart
This will give you a nice running system! You will run into problems along the way, nothing that should be a showstopper. Good luck and if you have any questions, feel free to comment!
No comments:
Labels: , , , , , , ,

Tutorial: How to Connect to MySQL With Python

The more I jump into Python the more I like it. This tutorial is about one of the more basic parts of Python - connecting it to a MySQL database.
The reason I chose MySQL is purely because of ubiquity, I figure this will be the one people will be connecting to the most if they’re using Python especially in a web environment.

Get The Database Setup

If you’re following this excersize exactly, you’ll want to create a table on your MySQL database that holds names. This is just a simple dumb table for this excersize.

SQL to create the table for this tutorial.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE  TABLE `test`.`name` (
  `nameid` INT NOT NULL AUTO_INCREMENT ,
  `firstname` VARCHAR(45) NULL ,
  `lastname` VARCHAR(45) NULL ,
  PRIMARY KEY (`nameid`)
);

INSERT INTO `test`.`name`
(`firstname`,`lastname`)
VALUES
("Cookie","Monster"),
("Guy","Smiley"),
("Big","Bird"),
("Oscar","Grouch"),
("Alastair","Cookie");

Now that you have your highly sophisticated database set up, let’s connect to it and start playing around.

Create Your Python Script

The first step of course is to create your Python Script. Create a file called “datademo.py” (or whatever you want to call it).

Your Initial Python Script.
1
2
3

#!/usr/bin/python
# filename: datademo.py 
# a simple script to pull some data from a MySQL table

Connect to the Database

The first thing you’ll need do is import the MySQL modules with the following line:

1

import MySQLdb

This assumes you have MySQLdb installed. If not, don’t worry it’s a quick install.
Now that you have that set up, let’s get connected:

1

db = MySQLdb.connect(host="localhost", user="root", passwd="", db="test")

With this string you can connect using your MySQL credentials. If you want you can store these credentials in variables elsewhere.

The Cursor

Python uses a “cursor” when dealing with data. A cursor is a simple data structure that transverses the records in the database. Cursors perform CRUD ( Create Read Update Delete ) operations on the database.

1
2

#create a cursor for the select
cur = db.cursor()

This intializes the cursor so you can use the “cur “object wherever needed. So the next thing we need to do is come up with an SQL command.

1

SELECT firstname,lastname FROM test.name;

This of course selects a first and last name from our database. We want to stuff that SQL command into a parameter for the execute method of the cursor:

1
2

#execute an sql query
cur.execute("SELECT firstname,lastname FROM test.name")

Iteration and Display

The next part of this is iterating through the database result and displaying it.

1
2
3
4
5
6
7
8
9

# loop to iterate
for row in cur.fetchall() :
      #data from rows
        firstname = str(row[0])
        lastname = str(row[1])

      #print it
        print "The first name is " + firstname
        print "The last name is " + lastname

Pretty simple huh? The for loop iterates through the data and produces an array, in this case it’s “row”. You then select the index of that row to get the data from it.
When you run it you should see this output:
The first name is Cookie
The last name is Monster
The first name is Guy
The last name is Smiley
The first name is Big
The last name is Bird
The first name is Oscar
The last name is Grouch
The first name is Alastair
The last name is Cookie
This is just a straight dump of the database. Let’s clean it up little.

1
2
3
4
5
6
7
8

# loop to iterate
for row in cur.fetchall() :
      #data from rows
        firstname = str(row[0])
        lastname = str(row[1])

      #print i
        print "This Person's name is " + firstname + " " + lastname

This obviously is a cleaned up version of the same thing. Just remember, for iterates but the cursor is the important part.
Your output will look like this:
This Person's name is Cookie Monster
This Person's name is Guy Smiley
This Person's name is Big Bird
This Person's name is Oscar Grouch
This Person's name is Alastair Cookie
You can also simply print out the row and look at the raw data:

1
2
3

# loop to iterate
for row in cur.fetchall() :
  print row

Your output will look something like this:
('Cookie', 'Monster')
('Guy', 'Smiley')
('Big', 'Bird')
('Oscar', 'Grouch')
('Alastair', 'Cookie')
This allows you to look at the data structure to determine what you want to grab.

Closing it all up

One quick way to bring down a server is leaving your connections open. Since there are persistent connections, when you end your script that doesn’t mean the database session ends with it, generally it does not. So here is how you close it up:

1
2
3
4
5

# close the cursor
cur.close()

# close the connection
db.close ()

Notice how we call the close() method for both objects. closing them. You are actually closing two things: the cursor and the connection. It’s actually a good thing you have to do them separate, as opposed to one function. There may be a need to close a cursor yet leave the connection open. This is why we do it in two steps.

The full script

Here is the full code for this article, in case you are one of those people who skip down to the code, then download it and play around.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#!/usr/bin/python
# datademo.py 
# a simple script to pull some data from MySQL

import MySQLdb

db = MySQLdb.connect(host="localhost", user="root", passwd="", db="test")

#create a cursor for the select
cur = db.cursor()

#execute an sql query
cur.execute("SELECT firstname,lastname FROM test.name")

##Iterate 
for row in cur.fetchall() :
      #data from rows
        firstname = str(row[0])
        lastname = str(row[1])

      #print 
        print "This Person's name is " + firstname + " " + lastname

# close the cursor
cur.close()

# close the connection
db.close ()

There it is, easy as that! In the next article I’ll be diving in a little deeper and we’ll build some tests to demonstrate the MySQL usage.
Good luck!
No comments:
Labels: , , , , , , ,
Subscribe to: Comments (Atom)