Percona XtraBackup 2.1.4 is now available

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.1.4 on August 9th, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release is the current GA (Generally Available) stable release in the 2.1 series. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

New Features:

  • Percona XtraBackup has introduced additional options to handle the locking during the FLUSH TABLES WITH READ LOCK. These options can be used to minimize the amount of the time when MySQL operates in the read-only mode.
  • Percona XtraBackup has now been rebased on MySQL versions 5.1.70, 5.5.30, 5.6.11 and Percona Server versions 5.1.70-rel14.8 and 5.5.31-rel30.3 server versions.
  • In order to speed up the backup process, slave thread is not stopped during copying non-InnoDB data when innobackupex –no-lock option is used as using this option requires absence of DDL or DML to non-transaction tables during backup.
  • Source tarball (and Debian source) now include all MySQL source trees required for the build. This means internet connection during package build isn’t required anymore.
  • Two new options options, innobackupex –decrypt and innobackupex –decompress, have been implemented to make decryption and decompression processes more user friendly.

Bugs Fixed:

  • There were no 2.1.x release packages available for Ubuntu Raring. Bug fixed #1199257.
  • During the backup process loading tablespaces was started before the log copying, this could lead to a race between the datafiles state in the resulting backup and xtrabackup_logfile. Tablespace created at a sensitive time would be missing in both the backup itself and as the corresponding log record in xtrabackup_logfile, so it would not be created on innobackupex –apply-log either. Bug fixed #1177206.
  • Fixed the libssl.so.6 dependency issues in binary tarballs releases. Bug fixed #1172916.
  • innobackupex did not encrypt non-InnoDB files when doing local (i.e. non-streaming) backups. Bug fixed #1160778.
  • Difference in behavior between InnoDB 5.5 and 5.6 codebases in cases when a newly created tablespace has uninitialized first page at the time when XtraBackup opens it while creating a list of tablespaces to backup would cause assertion error. Bug fixed #1187071.
  • xbcrypt could sometimes fail when reading encrypted stream from a pipe or network. Bug fixed #1190610.
  • innobackupex could not prepare the backup if there was no xtrabackup_binary file in the backup directory and the xtrabackup binary was not specified explicitly with innobackupex –ibbackup option. Bug fixed #1199190.
  • Debug builds would fail due to compiler errors on Ubuntu Quantal/Raring builds. Fixed compiler warnings by backporting the corresponding changes from upstream. Bug fixed #1192454.
  • innobackupex would terminate with an error if innobackupex –safe-slave-backup option was used for backing up the master server. Bug fixed #1190716.
  • Under some circumstances XtraBackup could fail on a backup prepare with innodb_flush_method=O_DIRECT when XFS filesystem was being used. Bug fixed #1190779.
  • Percona XtraBackup didn’t recognize checkpoint #0 as a valid checkpoint on xtrabackup –prepare which would cause an error. Bug fixed #1196475.
  • Percona XtraBackup didn’t recognize the O_DIRECT_NO_FSYNC value for innodb_flush_method which was introduced in MySQL 5.6.7. Fixed by adding the value to the list of supported values for innodb_flush_method in xtrabackup_56. Bug fixed #1206363.
  • innobackupex would terminate if innobackupex –galera-info option was specified when backing up non-galera server. Bug fixed #1192347.

Other bug fixes: bug fixed #1097434, bug fixed #1201599, bug fixed #1198220, bug fixed #1097444, bug fixed #1042796, bug fixed #1204463, bug fixed #1197644, bug fixed #1197249, bug fixed #1196894, bug fixed #1194813, bug fixed #1183500, bug fixed #1181432, bug fixed #1201686, bug fixed #1182995, bug fixed #1204085, bug fixed #1204083, bug fixed #1204075, bug fixed #1203672, bug fixed #1190876, bug fixed #1194879, bug fixed #1194837.

Known issues:

  • Backups of MySQL/Percona Server 5.6 versions prior to 5.6.11 cannot be prepared with Percona XtraBackup 2.1.4. Until the upstream bug #69780 is fixed and merged into Percona XtraBackup, Percona XtraBackup 2.1.3 should be used to prepare and restore such backups. This issue is reported as bug #1203669.

Release notes with all the bugfixes for Percona XtraBackup 2.1.4 are available in our online documentation. All of Percona‘s software is open source and free, all the details of the release can be found in the 2.1.4 milestone at Launchpad. Bugs can be reported on the launchpad bug tracker.

The post Percona XtraBackup 2.1.4 is now available appeared first on MySQL Performance Blog.

Can you really be Bias free ?

When we started Percona about 7 years ago one of the ideas behind the business was to provide customer focused services, which I refereed to as “bias free” at the time.

What do I mean by customer focused services ? Is not this something most of the company out there would claim ? Not in our definition! What we have observed years ago is what many companies view the services organization as the part of sales force, looking to recommend the product and services companies sell whenever they fit or not. In fact I had conversation with consultants from number of companies asking them direct question – if you found during project delivery your solution is the better fit can you recommend against implementing it ? In most cases the answer is no, either being direct management instructions or a fear of confrontation – Sales team could be quite unhappy with sale which they have done rolling back because of “consultants fault”. We wanted to be different. We wanted to ensure our services are the best value and the best customer fit to our knowledge.

Our first focus was Bias because in the smaller organization the other things are less of the issue – if I both sell and when deliver the project there is no one to be upset about if I decide something is better fit. I just have my own integrity and bias to care about.

As the time passed though I came to realize it is quite Utopian to strive to be bias free. You are biased whenever you want it or not and attempt to be “bias free” would much more likely to cause you into denial about your existing biases than anything else. We are biased by our knowledge and experiences this is just how life goes and I think the most important thing we can to do in this regard as professionals is to acknowledge our biases to ourselves. For example I’m biased against using Windows on Servers, and much more Biased towards Linux compared to other operating systems… and of course I will have a lot of facts to back up my bias. (This is what happens – we tend to filter information which supports our predispositions).

As I came to understanding myself as well as other members of our team can’t be Bias free I needed a different idea about how we can provide customer services with highest integrity possible ? Here are my current thoughts:

Acknowledge your Bias You are biased. Be honest to yourself about that. This is a first step towards reducing your bias impact.
Maintain your Integrity Being biased is OK. Lacking integrity is not. When you’re working with the customer you need to act in this customer interests. Your and your company interest come second. Some other industries have the laws on the subjects ours do not, but I think if we all enforce this rule on as we all will be much better.
Understand the Customer Reality The Integrity is not enough if you do not really understand your customer as your best recommendations coming right from your heart may be very wrong if you do not really understand customer situation. For example I already told you I’m biased towards Linux when it comes to the Server Operating System of Choice but I’ve recommended customer more than once to stay on Windows because it was completely Windows shop with no Linux experience whatsoever and getting people to install Linux for the first time in their life for the purpose of running mission critical database is not very wise. Another example would be Percona Server product. We do recommend this product to many customers, due to some specific valuable features we have added to the stock MySQL. We also may recommend MariaDB if some of its advanced features are needed. In many cases we however do not recommend any change because the system is working fine with Community MySQL and any change comes with its risks and costs.
Problems have Multiple solutions The big mistake often comes from “School-like” approach to the problems thinking it has “the solution”. In fact there are probably more than one solution to the same problem and it if often impossible to predict in advance which one would be better. Accepting existence of multiple solution and your limits in understanding all implications of them help to keep an open mind. Frankly it was very eye opening experience for me when I would think there is no way the client’s approach would work but it actually worked out quite fine, better than more complicated solution I was advocating.

There is an other danger though with this approach – if you do not push your product hard (as other vendors may do) you might come across as unsure about your product or not interested in the business. It is a tight line to walk. I teach my team to come up with the options and highlight the benefits and drawbacks we’re aware for any solutions allowing customers to make the choice. Even if we strive to understand Customer’s Reality there is a lot of internal process and politics which you would unlikely ever know.

We can also use this as a learning opportunity – if we’re not a fit is this an opportunity to improve our products and services ? Sometimes it and sometimes it simply falls outside of the area you want to focus on as a business.

So I do not claim to be bias free any more. I also do not claim us to be perfect – over last 7 years there have been many mistakes done by the team causing pains to our customers. At the same time I am staying committed working hard at having products and Services which would be great fit for our customers and really pursuing the customers where we can make a positive difference in their life, understanding there are always situations when something else is better fit.

The post Can you really be Bias free ? appeared first on MySQL Performance Blog.

ipcalc using mysql function

Aku cuba menggunakan sepenuhnya function mysql yang ada. Salah satunya penggunaan ipcalc; ipcalc ini dalam fedora ada (/bin/ipcalc), gunanya untuk calculate network address.

INSTALL:
1) Copy code dekat bawah
2) Connect ke mysql atau guna phpmyadmin
3) paste

Anda boleh check ade 3 functions yang akan dicreate
1) ipcalc
2) mask2prefix = convert netmask to prefix
3) prefix2mask = convert prefix to netmask

CODE:

-- ipcalc: calculate IPv4 address
-- nawawi <mohd.nawawi(at)gmail.com>
-- http://www.ronggeg.net/

DELIMITER //

CREATE FUNCTION `mask2prefix`(mask VARCHAR(15)) RETURNS bigint(10)
    DETERMINISTIC
BEGIN

DECLARE m BIGINT(10);
DECLARE c INT;

SET m=INET_ATON(mask);
SET c=0;
WHILE(m > 0) DO
 SET m=m << 1 & 0xffffffff;
 SET c=c+1;
END WHILE;
RETURN c;
END//

CREATE FUNCTION `prefix2mask`(prefix INT(3)) RETURNS varchar(15) CHARSET utf8
    DETERMINISTIC
BEGIN
RETURN INET_NTOA(~((1 << (32 - prefix)) - 1) & 0xffffffff);
END//

CREATE FUNCTION `ipcalc`(opt CHAR(1), ip VARCHAR(15), mask VARCHAR(15)) RETURNS varchar(15) CHARSET utf8
    DETERMINISTIC
BEGIN

DECLARE i BIGINT(10);
DECLARE m BIGINT(10);
DECLARE r BIGINT(10);

SET i=(INET_ATON(ip));
SET m=(INET_ATON(mask));

CASE opt
 WHEN 'p' THEN RETURN mask2prefix(mask);
 WHEN 'n' THEN SET r=i & m;
 WHEN 'b' THEN SET r=(i & m) | ~m & 0xffffffff;
ELSE
 SET r=NULL;
END CASE;
RETURN INET_NTOA(r);

END//

DELIMITER ;

CARA GUNA:
ipcalc(option,ip,netmask);
option: n = network, b = broadcast, p = prefix

mysql> select ipcalc('n','192.168.0.1','255.255.255.0') as network;
+-------------+
| network     |
+-------------+
| 192.168.0.0 |
+-------------+
1 row in set (0.00 sec)

mysql> select ipcalc('b','192.168.0.1','255.255.255.0') as broadcast;
+---------------+
| broadcast     |
+---------------+
| 192.168.0.255 |
+---------------+
1 row in set (0.00 sec)

mysql> select ipcalc('p','192.168.0.1','255.255.255.0') as prefix;
+--------+
| prefix |
+--------+
| 24     |
+--------+
1 row in set (0.00 sec)

mysql> select ipcalc('n','192.168.0.1',prefix2mask(24)) as network;
+-------------+
| network     |
+-------------+
| 192.168.0.0 |
+-------------+
1 row in set (0.00 sec)

mysql> select ipcalc('b','192.168.0.1',prefix2mask(24)) as broadcast;
+---------------+
| broadcast     |
+---------------+
| 192.168.0.255 |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE  `kejap` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use kejap;
Database changed
mysql> CREATE TABLE  kejap.ip (ip VARCHAR( 15 ) NOT NULL ,mask VARCHAR( 15 ) NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO  kejap.ip (ip,mask) VALUES ('192.168.0.1',  '255.255.255.0'), ('192.168.2.1',  '255.255.255.0');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select ip,mask,ipcalc('n',ip,mask) as network from ip;
+-------------+---------------+-------------+
| ip          | mask          | network     |
+-------------+---------------+-------------+
| 192.168.0.1 | 255.255.255.0 | 192.168.0.0 |
| 192.168.2.1 | 255.255.0.0   | 192.168.0.0 |
+-------------+---------------+-------------+
2 rows in set (0.00 sec)

mysql> select ip,mask,ipcalc('n',ip,mask) as network,mask2prefix(mask) as prefix from ip;
+-------------+---------------+-------------+--------+
| ip          | mask          | network     | prefix |
+-------------+---------------+-------------+--------+
| 192.168.0.1 | 255.255.255.0 | 192.168.0.0 |     24 |
| 192.168.2.1 | 255.255.0.0   | 192.168.0.0 |     16 |
+-------------+---------------+-------------+--------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE `ip` CHANGE `ip` `ip` VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, CHANGE `mask` `mask` VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
    -> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select ip,mask,CONCAT(ipcalc('n',ip,mask),'/',mask) as network,mask2prefix(mask) as prefix from ip;
+-------------+---------------+---------------------------+--------+
| ip          | mask          | network                   | prefix |
+-------------+---------------+---------------------------+--------+
| 192.168.0.1 | 255.255.255.0 | 192.168.0.0/255.255.255.0 |     24 |
| 192.168.2.1 | 255.255.0.0   | 192.168.0.0/255.255.0.0   |     16 |
+-------------+---------------+---------------------------+--------+
2 rows in set (0.00 sec)

Selamat mencuba!

__pgrepf()

This function implementing “pgrep -f” command in pure bash. In term of performance wise, a little bit slow than “pgrep -f”.

# __pgrepf() -- get pid match program from /proc
# Usage: __pgrepf [name]
# alternative depend: basename(): http://www.ronggeng.net/index.php/2009/04/24/basename/
# dirname(): http://www.ronggeng.net/index.php/2009/04/24/dirname/
#
# Example:
#
# if ! __pgrepf httpd; then
#      /etc/init.d/httpd restart
# fi
#

__pgrepf() {
        [ -n "$1" ] && [ -d "/proc" ] || return 1;
        local _mt="$1" _buf _f _pid _pidr="" _me=$$;
        _mt=${_mt// /};
        for _f in /proc/*/cmdline; do
                _pid=$(basename $(dirname $_f));
                ! [[ $_pid =~ ^([0-9]+)$ ]] && continue;
                (( $_pid < = 9 )) && continue;
                [ "$_pid" = "$_me" ] && continue;
                _buf=$(< $_f);
                [ -z "$_buf" ] && continue;
                [[ $_buf = *$_mt* ]] && _pidr+="$_pid ";
                unset _pid _buf;
        done;
        [ -n "$_pidr" ] && {
                echo "$_pidr";
                return 0;
        };
        return 1;
}

emptydir()

# emptydir() -- return true if directory empty
# Usage: emptydir directory
#
# Example:
#
# if ! emptydir /tmp; do
#  echo "/tmp not empty";
#done
#

emptydir() {
        local _dir="$1" _d;
        [ ! -d "$_dir" ] && return 2;
        for _d in $_dir/* $_dir/.*; do
                [ "$_d" = "$_dir/." ] || [ "$_d" = "$_dir/.." ] && continue;
                [ -e "$_d" ] && return 1;
        done;
        return 0;
}

strlen()

# strlen() -- Get string length
# Usage: strlen string
#
# Example:
#
# string="saya suka makan";
# leng=$(strlen "$string");
#
# echo $leng;
#

strlen() {
	echo ${#1};
}

strtolower()

# strtolower() -- — Make a string lowercase
# Usage: strtolower string
#
# Example 1:
#
# echo $(strtolower "SAYA SUKAN MAKAN");
#
# Example 2:
#
# string="$(strtolower "SAYA SUKAN MAKAN")";
# echo $string;

if [ -n "${BASH_VERSINFO[0]}" ] && [ ${BASH_VERSINFO[0]} -gt 3 ]; then
	# Updated: 20-Aug-2009 - for bash version 4
	strtolower() {
		[ $# -eq 1 ] || return 1;
		echo ${1,,};
		return 0;
	}
else
	strtolower() {
		[ $# -eq 1 ] || return 1;
		local _str _cu _cl _x;
		_cu=(A B C D E F G H I J K L M N O P Q R S T U V W X Y Z);
		_cl=(a b c d e f g h i j k l m n o p q r s t u v w x y z);
		_str=$1;
		for ((_x=0;_x<${#_cl[*]};_x++)); do
			_str=${_str//${_cu[$_x]}/${_cl[$_x]}};
		done;
		echo $_str;
		return 0;
	}
fi

strtoupper()

# strtoupper() -- — Make a string uppercase
# Usage: strtoupper string
#
# Example 1:
#
# echo $(strtoupper "saya suka makan");
#
# Example 2:
#
# string="$(strtoupper "saya sukan makan")";
# echo $string;

if [ -n "${BASH_VERSINFO[0]}" ] && [ ${BASH_VERSINFO[0]} -gt 3 ]; then
	# Updated: 20-Aug-2009 - for bash version 4
	strtoupper() {
		[ $# -eq 1 ] || return 1;
		echo ${1^^};
		return 0;
	}
else
	strtoupper() {
		[ $# -eq 1 ] || return 1;
		local _str _cu _cl _x;
		_cu=(A B C D E F G H I J K L M N O P Q R S T U V W X Y Z);
		_cl=(a b c d e f g h i j k l m n o p q r s t u v w x y z);
		_str=$1;
		for ((_x=0;_x<${#_cl[*]};_x++)); do
			_str=${_str//${_cl[$_x]}/${_cu[$_x]}};
		done;
		echo $_str;
		return 0;
	}
fi

stristr()

# stristr() -- Case-insensitive strstr()
# Usage: stristsr string match
# depend: strtolower(): http://www.ronggeng.net/index.php/2009/04/24/strtolower/
#
# Example 1:
#
# string="saya SUKA Makan";
# if stristr "${string}" "suk"; then
# 	echo "OK";
# fi
#
# Example 2:
#
# buff="$(ps ax)";
# if ! stirstr "${buff}" "httpd"; then
#	echo "service httpd down";
# fi

stristr() {
	[ $# -eq 2 ] || return 1;
	! type -t strtolower &>/dev/null && return 1;
	local _str1=$(strtolower "$1");
	local _str2=$(strtolower "$2");

	[ "$_str1" = "$_str2" ] || [[ "$_str1" = *$_str2* ]] && return 0;
	return 1;
}

strstr()

# strstr() -- locate a substring
# Usage: strstsr string match
#
# Example 1:
#
# string="saya suka makan";
# if strstr "${string}" "suk"; then
# 	echo "OK";
# fi
#
# Example 2:
#
# buff="$(ps ax)";
# if ! strstr "${buff}" "httpd"; then
#	echo "service httpd down";
# fi

strstr() {
	[ $# -eq 2 ] || return 1;
	[ "$1" = "$2" ] || [[ "$1" = *$2* ]] && return 0;
	return 1;
}