Improving Performance with MySQL Index Column

In addition to creating new indexes to improve performance, you can improve database performance with additional schema optimizations. These optimizations include using specific data types and/or column types. The benefit is a smaller disk footprint producing less disk I/O and results in more index data being packed in available system memory.

Data Types

Several data types can be replaced or modified with little or no impact to an existing schema.

BIGINT vs. INT

When a primary key is defined as a BIGINT AUTO_INCREMENT data type, there is generally no requirement why this datatype is required. An INT UNSIGNED AUTO_INCREMENT datatype is capable of supporting a maximum value of 4.3 billion. If the table holds more than 4.3 billion rows, other architecture considerations are generally necessary before this requirement.

The impact of modifying a BIGINT data type to an INT data type is a 50 percent reduction in per row size of the primary key from 8 bytes to 4 bytes. The impact is also not just for a primary key; all foreign keys that are defined as BIGINT can now be modified to INT. This savings can significantly reduce the space required for indexes in a heavily normalized database.
For a more detailed explanation, see
http://ronaldbradford.com/blog/bigint-v-int-is-there-a-big-deal-2008-07-18/

TIP

The change of BIGINT to INT UNSIGNED for an AUTO_INCREMENT column can be one of the best immediate schema improvements for a database server with limited memory and a heavily normalized data model.

DATETIME vs. TIMESTAMP

Is the requirement for recording a date/time value an epoch value—that is, the number of seconds since 1/1/1970? If the value being stored is only an epoch value, then a TIMESTAMP column supports all necessary values. A DATETIME column supports all possible date/time values. A DATETIME data type is 8 bytes, and a TIMESTAMP data type is 4 bytes.

The disadvantage of using a TIMESTAMP is the implied DEFAULT 0, which is incompatible with any related SQL_MODE settings that disable zero date settings. The TIMESTAMP data type also does not support a NULL value. This column type is ideal for creating the date and time of a created or an update value for a row when a value always exists.

ENUM

MySQL provides the ENUM data type, which is ideal for static code values. For example, when recording the values for gender, you could define a column as either of the following.

gender1     VARCHAR(6) NOT NULL
gender2     ENUM (‘Male’,’Female’) NOT NULL

There are three benefits of using the ENUM data type:

• An ENUM column provides additional data integrity with an implied check constraint.
• An ENUM column uses only 1 byte for up to 255 distinct values.
• The values of an ENUM column are better for readability. For example, if you have a status field, the use of an ENUM is compact in data size and provides a meaningful description of the column value.

     status1  CHAR(1) NOT NULL DEFAULT ‘N’,  // ‘N’,’A’,’X’
     status2  ENUM (‘New’,’Active’,’Archived’) NOT NULL DEFAULT ‘New’

Historically, the impact of using an ENUM was the database dependence of changing the range of values when a new value was required. This was an ALTER DDL statement, which is a blocking statement. Starting with MySQL 5.1, adding a value to an ENUM column is very fast and unrelated to the size of the table.

NULL vs. NOT NULL

Unless you are sure that a column can contain an unknown value (a NULL), it is best to define this column as NOT NULL. Frameworks, for example Ruby on Rails, are notorious for not defining mandatory columns as NOT NULL. When the column is defined within an index, there is an improved size reduction and simplification of index processing, as no additional NULL conditions are required. In addition, having NOT NULL places an extra integrity constraint on the data in the column, ensuring all rows have a value for the column.

Implied Conversions

When you are choosing an indexed data type for table joins, it is important that the data type is identified. Implied type conversion can be an unnecessary overhead. For numeric integer columns, ensure that SIGNED and UNSIGNED is consistent. For a variable data type, the added complexity includes the character set and collation. When you are defining indexed columns for table join conditions, ensure that these match. A common problem is an implied conversion between LATIN1 and UTF8 character sets.

Global Spare or Dedicated Spare?

Global hot spare:
                              A global hot spare is not assigned to a specific logical drive, it will protect any redundant logical drive on the controller. (RAID 0 logical drives and simple volumes are non-redundant and are not protected by hot spares.) You can designate a global hot spare before or after you build logical drives on a controller.

Dedicated hot spare: 
                                   A dedicated hot spare is assigned to one or more specific logical drives and will only protect those logical drives. A dedicated hot spare that is assigned to protect more than one logical drive is called a pool spare. You must create the logical drive before you can assign a dedicated hot spare to protect it. 

Clearing disk space when a file is deleted but still consuming space

[root@test01 /]# lsof  | grep deleted

Once you are sure of the culprit file, specifically grep that in lsof.

[root@test01 /]# lsof  | grep deleted | grep rabbit@test01.log-20151108
beam.smp   43563      root   10w      REG              253,0 29471244165          1371295 /var/log/rabbitmq/rabbit@test01.log-20151108 (deleted)

Find the entry in /proc/<pid>/fd/ that corresponds to the filehandle (for above case:)

[root@test01 /]# ll /proc/43563/fd/10

Now, just cat /dev/null into the fd:

[root@test01 /]# cat /dev/null > /proc/43563/fd/10

And we are good to go :-) 

Advantages and Disadvantages of Percona Xtrabackup, Installation and Steps to take DB backup through innobackupex tool

PERCONA XTRABACKUP:-

           This post is about advantages, disadvantages, Installation and DB backup steps of percona xtrabackup/innobackupex and I will let you know about innobackupex tool which is written in Perl,  It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, It perform point in time backup for Innodb, MyISAM , Archive engines. 

  I have picked a DB which size is 28GB to test the same. I got below results: 

  Backup Time Taken with innobackupex: 5 minutes
  Backup Size : - 5.8 GB

and below are the findings (Advantages and Disadvantages):-

Advantages:- 
1) It supports completely non-blocking backups of InnoDB/XtraDB (Which support Transactions) storage engines.
2) It supports on fly compression which help to cater disk space with (--stream=xbstream).
3) We can take incremental backup as well with this. This can be done because each InnoDB page has a log sequence number, LSN, which acts as a version number of the entire database. Every time the database is modified, this number gets incremented. An incremental backup copies all pages since a specific LSN.
4) We can take partial backup as well which means that we can take backup only for some specific tables. But for this there must be innodb_file_per_table enabled 
5) Use --slave-info when we are backing up from backup slave server because it will provide binlog file name and position of the master server. It also writes this information to the xtrabackup_slave_info file.

How To Use HAProxy For MySQL Load Balancing

HAProxy is an open source software which can load balance HTTP and TCP servers. All your MySQL servers have to be configured to perform Master-Master replication as load balancing involves both reading and writing to all the backends.

HAproxy over MySQL

Prepare MySQL Servers

We need to prepare the MySQL servers by creating one additional user for HAProxy. This user will be used by HAProxy to check the status of a server and health of server.

mysqlchk

We need to create a script which detects the MySQL replication role on the database node as per below:

• if master (SHOW SLAVE HOSTS > 1 AND read_only = OFF),
• return 'MySQL master is running.'
• if slave (Slave_IO_Running = Yes AND Slave_SQL_Running = Yes AND (Seconds_Behind_Master = 0 OR Seconds_Behind_Master < SLAVE_LAG_LIMIT))
• Set limit of SLAVE_LAG_LIMIT which will be bear able for your environment.
• return 'MySQL slave is running. (slave lag: 0)'
• else
• return 'MySQL is *down*'

How to use

• Create a script in any path like I created at '/opt/mysqlcheck' and make it executable. 

#!/bin/bash

SLAVE_LAG_LIMIT=5

MYSQL_HOST="127.0.0.1"

MYSQL_PORT="3306"

MYSQL_USERNAME='USERNAME'

MYSQL_PASSWORD='PASSWORD'

MYSQL_BIN='/usr/bin/mysql'

MYSQL_OPTS="-q -A --connect-timeout=10"

TMP_FILE="/tmp/mysqlchk.$$.out"

ERR_FILE="/tmp/mysqlchk.$$.err"

FORCE_FAIL="/tmp/proxyoff"

preflight_check()

{

    for I in "$TMP_FILE" "$ERR_FILE"; do

        if [ -f "$I" ]; then

            if [ ! -w $I ]; then

                echo -e "HTTP/1.1 503 Service Unavailable\r\n"

                echo -e "Content-Type: Content-Type: text/plain\r\n"

                echo -e "\r\n"

                echo -e "Cannot write to $I\r\n"

                echo -e "\r\n"

                exit 1

            fi

        fi

    done

}

return_ok()

{

    echo -e "HTTP/1.1 200 OK\r\n"

    echo -e "Content-Type: text/html\r\n"

    echo -e "Content-Length: 43\r\n"

    echo -e "\r\n"

    if [ $role == "master" ]; then

        echo -e "<html><body>MySQL master is running.</body></html>\r\n"

    elif [ $role == "slave" ]; then

        echo -e "<html><body>MySQL slave is running. (Slave lag: $SLAVE_LAG)</body></html>\r\n"

    else

        echo -e "<html><body>MySQL is running.</body></html>\r\n"

    fi

    echo -e "\r\n"

  #  rm $ERR_FILE $TMP_FILE

    exit 0

}

return_fail()

{

    echo -e "HTTP/1.1 503 Service Unavailable\r\n"

    echo -e "Content-Type: text/html\r\n"

    echo -e "Content-Length: 42\r\n"

    echo -e "\r\n"

    echo -e "<html><body>MySQL is *down*.</body></html>\r\n"

    echo -e "\r\n"

    exit 1

}

preflight_check

if [ -f "$FORCE_FAIL" ]; then

        echo "$FORCE_FAIL found" > $ERR_FILE

        return_fail

fi

CMDLINE="$MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"

SLAVE_IO=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_IO_Running |  tail -1 | awk {'print $2'})

SLAVE_SQL=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_SQL_Running | head -1 | awk {'print $2'})

echo $CMDLINE

if [[ "${SLAVE_IO}" == "Yes" ]] && [[ "${SLAVE_SQL}" == "Yes" ]]; then

    role='slave'

    SLAVE_LAG=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Seconds_Behind_Source | tail -1 | awk {'print $2'})

    if [[ $SLAVE_LAG = 0 ]]; then

        return_ok

    elif [ $SLAVE_LAG -lt $SLAVE_LAG_LIMIT ] ; then

        return_ok

    fi

else

    role='master'

    READ_ONLY=$($CMDLINE 'SHOW GLOBAL VARIABLES LIKE "read_only"' --vertical 2>/dev/null | tail -1 | awk {'print $2'})

echo "$READ_ONLY"

    [[ "${READ_ONLY}" == "OFF" ]] && return_ok

fi

return_fail

• Create a service in /etc/xinetd.d/ and accordingly change /etc/service with port number and service name.

service mysqlchk

{

        flags           = REUSE

        socket_type     = stream

        port            = 9300

        wait            = no

        server          = /root/mysqlchk

        user            =  mysql

        log_on_failure  += USERID

        disable         = no

        only_from       = 0.0.0.0/0 # recommended to put the IPs that need

                                    # to connect exclusively (security purposes)

        per_source      = UNLIMITED # Recently added (May 20, 2010)

}

• restart xinetd service 
• Install HAProxy on the your selected node.
• Update require information (SLAVE_LAG_LIMIT is in seconds):
• SLAVE_LAG_LIMIT=5
• MYSQL_HOST="localhost"
• MYSQL_PORT="3306"
• MYSQL_USERNAME='root'
• MYSQL_PASSWORD='password'
• You are good to go. Now ensure in HAProxy you have 2 listeners (3307 for write, 3308 for read) and use option tcp-check & tcp-check expect to distinguish master/slave similar to example below:

Enable Slow Query Logging in mysql 5.6

This tutorial will show you the settings you need to make in order to enable slow query logging

slow_query_log = 1  <0|1>
log-queries-not-using-indexes <if you want to log all queries that don't use indexes>
long_query_time=1   <Set this to the number of seconds a query must take to be considered "slow">
log-slow-queries=/var/log/mysql/log-slow-queries.log <Log file path>

'XR’ (Crossroads) Load Balancer for Web Servers on RHEL/CentOS

Crossroads is a service independent, open source load balance and fail-over utility for Linux and TCP based services. It can be used for HTTP, HTTPS, SSH, SMTP and DNS etc. It is also a multi-threaded utility which consumes only one memory space which leads to increase the performance when balancing load.

Let’s have a look at how XR works. We can locate XR between network clients and a nest of servers which dispatches client requests to the servers balancing the load.

If a server is down, XR forwards next client request to the next server in line, so client feels no down time. Have a look at the below diagram to understand what kind of a situation we are going to handle with XR.

MySQL Data Types

in this tutorial, you will learn about MySQL data types and how to use them effectively in the MySQL database design.
Database table contains multiple columns with specific data types such as numeric or string. MySQL provides more data types other than just numeric or string. Each data type in MySQL can be determined by the following characteristics:

• Kind of values it can represent.
• The space that takes up and whether the values are fixed-length or variable-length.
• Does the values of the data type can be indexed.
• How MySQL compares the value of a specific data type.

Numeric Data Types

You can find all SQL standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except the BIT type. The following table shows you the summary of numeric types in MySQL:

Numeric Types	Description
TINYINT	A very small integer
SMALLINT	A small integer
MEDIUMINT	A medium-sized integer
INT	A standard integer
BIGINT	A large integer
DECIMAL	A fixed-point number
FLOAT	A single-precision floating-point number
DOUBLE	A double-precision floating-point number
BIT	A bit field

String Data Types

In MySQL, string can hold anything from plain text to binary data such as images and files. String can be compared and searched based on pattern matching by using the LIKE operator or regular expression. The following table shows you the string data types in MySQL:

String Types	Description
CHAR	A fixed-length non-binary (character) string
VARCHAR	A variable-length non-binary string
BINARY	A fixed-length binary string
VARBINARY	A variable-length binary string
TINYBLOB	A very small BLOB (binary large object)
BLOB	A small BLOB
MEDIUMBLOB	A medium-sized BLOB
LONGBLOB	A large BLOB
TINYTEXT	A very small non-binary string
TEXT	A small non-binary string
MEDIUMTEXT	A medium-sized non-binary string
LONGTEXT	A large non-binary string
ENUM	An enumeration; each column value may be assigned one enumeration member
SET	A set; each column value may be assigned zero or more set members

Understanding MySQL Table Types, or Storage Engines

In this tutorial, you will learn various MySQL table types, or storage engines. It is essential to understand the features of each table type in MySQL so that you can use them effectively to maximize the performance of your databases.
MySQL provides various storage engines for its tables as below:

• MyISAM
• InnoDB
• MERGE
• MEMORY (HEAP)
• ARCHIVE
• CSV
• FEDERATED

Each storage engine has its own advantages and disadvantages. It is crucial to understand each storage engine features and choose the most appropriate one for your tables to maximize the performance of the database. In the following sections we will discuss about each storage engine and its features so that you can decide which one to use.

MyISAM

MyISAM extends the former ISAM storage engine. The MyISAM tables are optimized for compression an speed. MyISAM tables are also portable between platforms and OSes.
The size of MyISAM table can be up to 256TB, which is huge. In addition, MyISAM tables can be compressed into read-only tables to save space. At startup, MySQL checks MyISAM tables for corruption and even repair them in case of errors. The MyISAM tables are not transaction-safe.
Before MySQL version 5.5, MyISAM is the default storage engine when you create a table without explicitly specify the storage engine. From version 5.5, MySQL uses InnoDB as the default storage engine.

InnoDB

The InnoDB tables fully support ACID-compliant and transactions. They are also very optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-and forward operations. The size of the InnoDB table can be up to 64TB.
Like MyISAM, the InnoDB tables are portable between different platforms and OSes. MySQL also checks and repair InnoDB tables, if necessary, at startup.

MERGE

A MERGE table is a virtual table that combines multiple MyISAM tables, which has similar structure, into one table. The MERGE storage engine is also known as the MRG_MyISAM engine. The MERGE table does not have its own indexes; it uses indexes of the component tables instead.
Using MERGE table, you can speed up performance in joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables. If you use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.

Memory

The memory tables are stored in memory and used hash indexes so that they are faster than MyISAM tables. The lifetime of the data of the memory tables depends on the up time of the database server. The memory storage engine is formerly known as HEAP.

Archive

The archive storage engine allows you to store a large number of records, which for archiving purpose, into a compressed format to save disk space. The archive storage engine compresses a record when it is inserted and decompress it using zlib library as it is read.
The archive tables only allow INSERT and SELECT commands. The archive tables do not support indexes, so reading records requires a full table scanning.

CSV

The CSV storage engine stores data in comma-separated values file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software.
CSV table does not support NULL data type and read operation requires a full table scan.

FEDERATED

The FEDERATED storage engine allows you to manage data from a remote MySQL server without using cluster or replication technology. The local federated table stores no data. When you query data from a local federated table, the data is pull automatically from the remote federated tables.

Secrets of Redo Logging in InnoDB

Why InnoDB engine need Redo Log Files ?

InnoDB is a general-purpose storage engine that balances high reliability and high performance. It is a transactional storage engine and is fully ACID compliant, as would be expected from any relational database. The durability guarantee provided by InnoDB is made possible by the redo logs.

How it is generated  ?

InnoDB (.ibd files) are considered to be a sequence of equal sized pages. These pages are uniquely identified within the InnoDB system using the space_id, page_no combination. If we want to read or modify any page, it needs to be loaded into memory. So there are two copies of the pages - on disk and in memory.

1. Any changes to a page is first done to the in-memory copy of the page. The page that is modified in memory and not yet flushed to disk is marked as the dirty page in memory.
2. An associated redo log is generated in memory, in the local mini transaction (mtr) buffer. This will then be transferred to the global in-memory redo log buffer.
3. The redo log record is written from the redo log buffer in memory to the redo log file on disk. This is subsequently flushed. These two steps are considered separate - writing to the redo log file and flushing the redo log file to the disk. This is to account for the file buffering done by the operating system.
4. The dirty page is flushed from memory to disk at some later point of time as part of the checkpointing operation.The order of these steps are important. The redo log record of a change must be flushed to disk before flushing the corresponding dirty page to the disk. This is the concept of write-ahead logging (WAL).

How it works ?

By default, InnoDB creates two redo log files (or just log files) ib_logfile0 and ib_logfile1 within the data directory of MySQL. In MySQL versions 5.6.8 and above, the default size of each redo log file is 48 MB each. This can be configured by the user by making use of innodb_log_file_size server option. The number of log files is controlled byinnodb_log_files_in_group server option.
A log group consists of a number of log files, each of same size. As of MySQL 5.6, InnoDB supports only one log group. So I'll not discuss this further.
The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.
The log files are viewed as a sequence of blocks called "log blocks" whose size is given by OS_FILE_LOG_BLOCK_SIZE which is equal to 512 bytes. Each log file has a header whose size is given by LOG_FILE_HDR_SIZE, which is defined as 4*OS_FILE_LOG_BLOCK_SIZE.

Terminology 

Redo Log File Header

Each redo log file contains a header occupying four log blocks with the following information:
• The first 4 bytes contain the log group number to which the log file belongs.
• The next 8 bytes contain the lsn of the start of data in this log file.
• First checkpoint field located in the beginning of the second log block.
• Second checkpoint field located in the beginning of the fourth log block.

Log Blocks 

A redo log file can be viewed as a sequence of log blocks. All log blocks, except the ones belonging to the log file header, contain a header and a footer.
The log block header contains the following information:
• The log block number. This field is of 4 bytes.
• Number of bytes of log written to this block. This field is of 2 bytes.
• Offset of the first start of an mtr log record group in this log block or 0 if none
• The checkpoint number to which this log block belongs
The log block trailer contains checksum of the log block contents.

Log Sequence Number (LSN)

It’s a most talked terminology while among Database Admins :) The LSN is an offset into the redo log file. Within InnoDB the log sequence number is represented by the type lsn_t, which is an 8-byte unsigned integer. The LSN is what that links the dirty page, the redo log record, and the redo log file. Each redo log record when it is copied to the in-memory log buffer, it gets an associated LSN. When each database page is modified, redo log records are generated. So each database page is also associated to an LSN. The page lsn is stored in a header for each page. The page lsn gives the lsn upto which the redo log file must be flushed before flushing the page.

Global In-memory Redo Log Buffer

The in-memory redo log buffer is global and all redo logs generated by user transactions will be written into this buffer. The size of this buffer is configurable and is given by the innodb_log_buffer_size. The default size of this redo log buffer is 8MB.

• When it comes into picture ?
• Running transaction is modifying database contents
• This log buffer will be written or flushed to the log file, either when the transaction commits
• When the log buffer gets full

Mini transaction (mtr)

A mini transaction (mtr) must be used to generate all the redo log records. A mini transaction contains a local buffer (called the mini transaction buffer) into which the generated redo log records will be stored. If we need to generate a group of redo log records such that either all make it to the redo log file or none makes it, then we need to put them in a single mini transaction. Apart from the redo log records, the mini transaction also maintains a list of pages that has been modified (dirty pages).

Redo Log Record Types

When we modify a database page, a redo log record is generated. This redo log record either contains what information has been changed in the page (physical redo log) or how to perform the change again (logical redo log). InnoDB uses a combination of physical redo logs and logical redo logs.

Life cycle of a redo log record

The life cycle of a redo log record is as follows:

• The redo log record is first created by a mini transaction and stored in the mini transaction buffer. It has information necessary to redo the same operation again in the time of database recovery.
• When mtr_commit() is done, the redo log record is transferred to the global in-memory redo log buffer. If necessary, the redo log buffer will be flushed to the redo log file, to make space for the new redo log record.
• The redo log record has a specific lsn associated with it. This association is established during mtr_commit(), when the redo log record is transferred from mtr buffer to log buffer. Once the lsn is established for a redo log record, then its location in the redo log file is also established.
• The redo log record is then transferred from the log buffer to redo log file when it is written + flushed. This means that the redo log record is now durably stored on the disk.
• Each redo log record has an associated list of dirty pages. This relationship is established via LSN. A redo log record must be flushed to disk, before its associated dirty pages. A redo log record can be discarded only after all the associated dirty pages are flushed to the disk.
• A redo log record can be used to re-create its associated list of dirty pages. This happens during database recovery.

It should be helpful to understand concept of redo logging which makes InnoDB a dependent transaction safe engine to keep our data in safe hands .

Managing Crons In linux

Not a nightmare anymore ..

What is Cron ?
Cron is a time-based job scheduler in Unix-like computer operating systems. People who set up and maintain software environments use cron to schedule jobs (commands or shell scripts) to run periodically at fixed times, dates, or intervals.

With increasing servers, applications, crons (schedule jobs) it was becoming pain to manage and monitor all crons.
We thus built NCConfig – a tool to monitor all crons which includes their status - Success/Failure, executing time, output, etc

Few major problems which we used to face before building NCConfig

- Did my job actually run?
Generally all crons were used to be monitored via email now because the typical way to monitor cron jobs is via email it is often extremely hard to find out if a job has actually run successfully. Ideally your monitoring shouldn’t involve people being awake and willing to read automatically generated emails. Especially if the cron job is important.

- Email, email everywhere!
Cron used to email the output of a given job to the user that the job is running as. This can be very useful when some aspect of the job requires alerting the user.
The problem is that you very often do not want emails from cron jobs. And usually when they start they can flood your inbox very fast.

- Where is my cron running ?
With more than 100 serves and single applications running on multiple servers its sometime get difficault to find where actually is my cron running.

- Execution Statistics
You may see the cron job execution statistics (including the total number of successful executions,  total number of failed executions (TF) and number of consecutive failed executions (CF)) in the cron job list

How to start and stop MySQL replication on a slave server?

#mysqladmin  -u root -ptmppassword stop-slave
Slave stopped

# mysqladmin  -u root -ptmppassword start-slave

slave started

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

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2836691
Server version: 5.5.25 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> stop slave;
Query OK, 0 rows affected (14.39 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: master server
                  Master_User: user
                  Master_Port: mysql master port
                Connect_Retry: 60
              Master_Log_File: bin.00001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.0001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.00001

            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0
1 row in set (0.48 sec)

How to combine multiple mysqladmin commands together

In the example below, you can combine process-list, status and version command to get all the output together as shown below.

# mysqladmin  -u root -ptmppassword process status version
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 43 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Uptime: 3135
Threads: 1  Questions: 80  Slow queries: 0  Opens: 15  Flush tables: 3
Open tables: 0  Queries per second avg: 0.25

mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 52 min 15 sec

You can also use the short form as shown below:

# mysqladmin  -u root -ptmppassword pro stat ver

Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# mysqladmin  -h serverIP -u root -ptmppassword pro stat ver

List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts
# mysqladmin -u root -ptmppassword flush-logs
# mysqladmin -u root -ptmppassword flush-privileges
# mysqladmin -u root -ptmppassword flush-status
# mysqladmin -u root -ptmppassword flush-tables
# mysqladmin -u root -ptmppassword flush-threads

• flush-hosts: Flush all information in the host cache.
• flush-privileges: Reload the grant tables (same as reload).
• flush-status: Clear status variables.
• flush-threads: Flush the thread cache.

What is the safe method to shutdown the MySQL server

# mysqladmin -u root -ptmppassword shutdown

# mysql -u root -ptmppassword
ERROR 2002 (HY000): Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock'

Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To start the server, execute “/etc/rc.d/init.d/mysql start”

How to reload/refresh the privilege or the grants tables

# mysqladmin -u root -ptmppassword reload;

Refresh command will flush all the tables and close/open log files.

# mysqladmin -u root -ptmppassword refresh

What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword status 

Uptime: 9267148 

Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067 

Flush tables: 1 Open tables: 64 Queries per second avg: 0.25

The status command displays the following information:

• Uptime: Uptime of the mysql server in seconds
• Threads: Total number of clients connected to the server.
• Questions: Total number of queries the server has executed since the startup.
• Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.
• Opens: Total number of tables opened by the server.
• Flush tables: How many times the tables were flushed.
• Open tables: Total number of open tables in the database.

How to display all the running process/queries in the mysql database

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below.

# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

How to display all MySQL server system variables and the values?

# mysqladmin  -u root -p******** variables
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| auto_increment_increment        | 1                               |
| basedir                         | /                               |
| big_tables                      | OFF                             |
| binlog_format                   | MIXED                           |
| bulk_insert_buffer_size         | 8388608                         |
| character_set_client            | latin1                          |
| character_set_database          | latin1                          |
| character_set_filesystem        | binary                          |

skip.....

| time_format                     | %H:%i:%s                        |
| time_zone                       | SYSTEM                          |
| timed_mutexes                   | OFF                             |
| tmpdir                          | /tmp                            |
| tx_isolation                    | REPEATABLE-READ                 |
| unique_checks                   | ON                              |
| updatable_views_with_limit      | YES                             |
| version                         | 5.1.25-rc-community             |
| version_comment                 | MySQL Community Server (GPL)    |
| version_compile_machine         | i686                            |
| version_compile_os              | redhat-linux-gnu                |
| wait_timeout                    | 28800                           |
+---------------------------------+---------------------------------+

How do I find out what version of MySQL I am running?

Apart from giving the ‘Server version’, this command also displays the current status of the mysql server.

# mysqladmin -u root -ptmppassword version
mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 107 days 6 hours 11 min 44 sec

Threads: 1  Questions: 231976  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25