10 MySQL variables that you should monitor

This article is also available as a TechRepublic download.
Regardless of whether you're running a single MySQL server or a cluster of multiple servers, one thing you are always interested in is squeezing the maximum performance out of your system. MySQL's developers were well aware of this, and so they provided a fairly comprehensive list of performance variables that you can monitor in real time to check the health and performance of your MySQL server.

These variables are accessible via the SHOW STATUS command. In Table A, we've listed 10 of the most important performance variables you should monitor when using MySQL, and we explain which particular attribute each of them reflects.

Table A

Variable	What it represents	Why you should monitor it
Threads_connected	This variable indicates the total number of clients that have currently open connections to the server.	It provides real-time information on how many clients are currently connected to the server. This can help in traffic analysis or in deciding the best time for a server re-start.
Created_tmp_disk_tables	This variable indicates the number of temporary tables that have been created on disk instead of in-memory.	Accessing tables on disk is typically slower than accessing the same tables in memory. So queries that use the CREATE TEMPORARY TABLE syntax are likely to be slow when this value is high.
Handler_read_first	This variable indicates the number of times a table handler made a request to read the first row of a table index.	If MySQL is frequently accessing the first row of a table index, it suggests that it is performing a sequential scan of the entire index. This indicates that the corresponding table is not properly indexed.
Innodb_buffer_pool_wait_free	This variable indicates the number of times MySQL has to wait for memory pages to be flushed.	If this variable is high, it suggests that MySQL's memory buffer is incorrectly configured for the amount of writes the server is currently performing.
Key_reads	This variable indicates the number of filesystem accesses MySQL performed to fetch database indexes.	Performing filesystem reads for database indexes slows query performance. If this variable is high, it indicates that MySQL's key cache is overloaded and should be reconfigured.
Max_used_connections	This variable indicates the maximum number of connections MySQL has had open at the same time since the server was last restarted.	This value provides a benchmark to help you decide the maximum number of connections your server should support. It can also help in traffic analysis.
Open_tables	This variable indicates the number of tables that are currently open.	This value is best analyzed in combination with the size of the table cache. If this value is low and the table_cache value is high, it's probably safe to reduce the cache size without affecting performance. On the other hand, if this value is high and close to the table_cache value, there is benefit in increasing the size of the table cache.
Select_full_join	This variable indicates the number of full joins MySQL has performed to satisfy client queries.	A high value indicates that MySQL is being forced to perform full table joins (which are performance-intensive) instead of using indexes. This suggests a need for greater indexing of the corresponding tables.
Slow_queries	This variable indicates the number of queries that have taken longer than usual to execute.	A high value indicates that many queries are not being optimally executed. A necessary next step would be to examine the slow query log and identify these slow queries for optimization.
Uptime	This variable indicates the number of seconds since the server was last restarted.	This value is useful to analyze server uptime, as well as to generate reports on overall system performance. A consistent low value indicates that the server is being frequently restarted, thereby causing frequent interruptions to client service.

Source: http://www.techrepublic.com/blog/linux-and-open-source/10-mysql-variables-that-you-should-monitor/

Guide install MySQL

set sql_mode = NO_ENGINE_SUBSTITUTION;


#Guideline MySQL
###Install gmake
tar zxvf cmake-VERSION.tar.gz
cd cmake-VERSION
./bootstrap
gmake
gmake install

###Required:
rpm -ivh ncurses-devel-5.7-3.20090208.el6.x86_64.rpm

###Install mysql
groupadd mysql
useradd -r -g mysql mysql
passwd mysql
tar zxvf mysql-VERSION.tar.gz
cd mysql-VERSION
cmake .
make -j4
make install

cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
bin/mysql_install_db --user=mysql
chown -R root .
chown -R mysql:mysql /var/lib/mysql/
bin/mysqld_safe --user=mysql &
bin/mysql_secure_installation
cp support-files/mysql.server /etc/init.d/mysql.server
chkconfig mysql.server on


#Cau hinh /etc/my.cnf  (luu y cau hinh server-id, auto_increment_increment, auto_increment_offset
#Restart lai ung dung
#Grant quyen tuong ung

GRANT SELECT, INSERT, DELETE, UPDATE ON <database name>.* TO '<user>'@'<ip>' IDENTIFIED BY 'ke-34#$klZ' WITH GRANT OPTION;


GRANT SELECT, INSERT, DELETE, UPDATE, EVENT, EXECUTE ON <database name>.* TO '<user>'@'<ip>' IDENTIFIED BY 'wurfl-383#$k5Z' WITH GRANT OPTION;


#Cau hinh replication

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<ip>' IDENTIFIED BY '<password>';
CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='<user>', MASTER_PORT=3306,MASTER_LOG_FILE='pe-mysql3-bin.000001', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;


CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='slave', MASTER_PORT=3306,MASTER_LOG_FILE='pe-mysql5-bin.000001', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;
CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='slave', MASTER_PORT=3306,MASTER_LOG_FILE='pe-mysql6-bin.000001', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;

Guide install Nginx + PHP

cd SETUP/

tar -xzf zlib-1.2.8.tar.gz
tar -xzf pcre-8.33.tar.gz

#openssl
tar -xzf openssl-1.0.1h.tar.gz
cd openssl-1.0.1h
CFLAGS=-fPIC ./config shared --prefix=<home dir>/env/openssl-1.0.1h
make -j4
make install
cd ..



#Nginx
tar -xzf nginx-1.6.1.tar.gz
cd nginx-1.6.1
./configure --prefix=<home dir>/nginx --without-mail_pop3_module --without-mail_imap_module --without-mail_smtp_module --with-http_stub_status_module --with-http_gzip_static_module --with-pcre=../pcre-8.33 --with-zlib=../zlib-1.2.8 --with-http_ssl_module  --with-openssl=../openssl-1.0.1h
make -j4
make install
cd ..


tar -xzf php-5.5.16.tar.gz
cd php-5.5.16
./configure --prefix=<home dir>/php --with-mysql=mysqlnd --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --enable-calendar --enable-soap --with-curl --with-gd --with-jpeg-dir=/usr/lib --with-png-dir=/usr/lib --with-zlib-dir=/usr/lib --with-freetype-dir=/usr/include/freetype2/  --enable-exif --enable-zip  --enable-mbstring   --with-openssl=<home dir>/env/openssl-1.0.1h  --enable-fpm  --enable-opcache  --with-mcrypt  --enable-soap
make -j4
make install
cd ..


###memcached
tar -xf memcache-2.2.7.tar
cd memcache-2.2.7
<home dir>/php/bin/phpize
./configure --with-php-config=<home dir>/php/bin/php-config
make -j4
make install

tar -xf ssh2-0.12.tar
cd ssh2-0.12
<home dir>/php/bin/phpize
./configure --with-php-config=<home dir>/php/bin/php-config
make -j4
make install


#copy fpm config. Change dir "Need change"
#<home dir>/php/etc/php-fpm.conf

#copy nginx config. Change dir "Need change"
#<home dir>/nginx/conf

#create php.ini

#killall -9 php-fpm;php/sbin/php-fpm

#insert to php.ini:
#extension=memcache.so




mkdir nginx/ssl
cd nginx/ssl
openssl genrsa -des3 -out self-ssl.key 1024
#(pass: 123456)
openssl req -new -key self-ssl.key -out self-ssl.csr
openssl x509 -req -days 3650 -in self-ssl.csr -signkey self-ssl.key -out self-ssl.crt

cp self-ssl.key self-ssl.key.org
openssl rsa -in self-ssl.key.org -out self-ssl.key

Iptable accept multicast

-A INPUT -m state --state NEW,ESTABLISHED,RELATED -m udp -p udp -d 224.0.0.0/24 --dport 7000:9000 -j ACCEPT

-A OUTPUT -m state --state NEW,ESTABLISHED,RELATED -m udp -p udp -d 224.0.0.0/24 --dport 7000:9000 -j ACCEPT

Grant permission MySQL

GRANT SELECT,UPDATE,INSERT,DELETE ON <ten database>.* TO 'user muon tao'@'192.168.5.%' IDENTIFIED BY 'pass muon tao' WITH GRANT OPTION;

GRANT SELECT,UPDATE,INSERT,DELETE,EVENT,EXECUTE ON <ten database terawurfl>.* TO 'user muon tao'@'192.168.5.%' IDENTIFIED BY 'pass muon tao' WITH GRANT OPTION;

Tìm 1 đoạn text trong thư mục hiện tại

find . -type f | xargs grep -rl '<search text>'

Rename file, chuyển ký tự space thành "_"

#!/bin/bash

ls | while read -r FILE
do
    mv -v "$FILE" `echo $FILE | tr ' ' '_' `
done