27 May

xtrabackup_51: not found & no ‘mysqld’ group in MySQL options

Recently I happen to setup a new MySQL instance with my tools – a standard MySQL 5.1+, xtrabackup setup and last-hotbackup.tar.gz.

To restore from the backup we used xtrabackup binaries and ran into issues following standard commands (assuming no changes):

To prepare the backup I used apply-log as follows:

$] innobackupex-1.5.1 --defaults-file=/usr/local/mysql/data/backup-my.cnf --apply-log /usr/local/mysql/data --ibbackup xtrabackup_51

This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex-1.5.1 prints "completed OK!".

sh: 1: xtrabackup_51: not found innobackupex-1.5.1: fatal error: no 'mysqld' group in MySQL options

To my surprise I really didn’t find xtrabackup_51 under path – Later I verified the version for Xtrabackup is 2.1.1 & learnt that xtrabackup_51 is absent with percona-xtrabackup 2.1.1 binaries.
~/bin$ ls -lhtr total 157M drwxr-xr-x 4 kedar kedar 4.0K May 13 21:24 percona-xtrabackup-2.1.1 -rwxr-xr-x 1 kedar kedar 16M May 13 21:30 xtrabackup_55 -rwxr-xr-x 1 kedar kedar 78M May 13 21:36 xtrabackup_56 -rwxr-xr-x 1 kedar kedar 13M May 13 21:42 xtrabackup -rwxr-xr-x 1 kedar kedar 2.2M May 13 21:42 xbstream -rwxr-xr-x 1 kedar kedar 2.2M May 13 21:42 xbcrypt lrwxrwxrwx 1 kedar kedar 12 May 13 21:42 innobackupex-1.5.1 -> innobackupex -rwxr-xr-x 1 kedar kedar 108K May 13 21:42 innobackupex -rw-rw-r-- 1 kedar kedar 47M May 13 21:44 percona-xtrabackup-2.1.1-600.tar.gz

That’s not cool, I thought until I read an important release note: “Support for InnoDB 5.0 and InnoDB 5.1 builtin has been removed from Percona XtraBackup.” [Release notes: http://www.percona.com/doc/percona-xtrabackup/2.1/release-notes/2.1/2.1.1.html] .

Options with me were:
1. Disable built-in Innodb and enable Innodb plugin or use Percona’s MySQL build
2. Use –ibbbackup xtarbackup (not xtrabackup_51)
3. Use XtraBackup 2.0 series for default built-in Innodb

- I upgraded the MySQL to use Percona’s build and executed –apply-logs specifying –ibbackup xtrabackup

The bug report [https://bugs.launchpad.net/percona-xtrabackup/+bug/1180905] is already in place and the issue has been resolved in the later version 2.1.2. Fixes: “innobackupex automatic version detection did not work correctly for latest Percona Server and MySQL 5.1 releases which could cause innobackupex to fail.”

For using MySQL 5.1+ with built-in support we can use Percona XtraBackup 2.0.
Support for MySQL 5.1 with builtin InnoDB (not the plugin) was removed in Percona XtraBackup 2.1. The last version to support MySQL 5.1 with builtin InnoDB was Percona XtraBackup 2.0. – conveyed @productiondba

Take out:
- Upgrade your xtrabackup to latest Percona XtraBackup Version (Yep, today it’s 2.1.3, not 2.1.2)
- Make sure we read release notes before using the latest build

04 Mar

Why we need a tester / QA – Software testing

“Why Testing”, “Importance of Software testing”, “Scope of testing”, “How to become a tester”, “Training for software quality assurance / software testing” …

Stop there… Above are the topics well discussed and explained at a lot many places and a lot many times, but this is not what we’re here for. I happened to face the question recently “Why’d you need a software tester as a dedicated resource?”

I did not want to get into deep technicalities of the SDL or STL Cycles to prove the roles but in plain simple words I’d try explaining the importance of the role that a tester plays and why a tester is required as a dedicated resource.

For any (software) product, testing is must. And, software tester can do better testing as compared to the developer or programmer who’s written the software.

Programmers, don’t feel bad. No camel sees his hunch!

“Why Tester”… hmmm let me make some points now:

1. A development team should avoid testing its own program (unit testing is a responsibility) – tester will check from his / her doubtful eyes

Developer may be biased with the fact that he has developed it, so may not test some areas as he’s confident enough that he has developed it bug free. (it’s like one always favours own baby) They usually have an attitude of:
We take pride on what we developed. We defend ourselves “it is not my fault!”
He may not think out of the box, or all possible test scenarios that can break the system as he is used to work in the same shell.

2. A software should be tested in a neutral way to have unbiased testing results. This can be done only by a third person (not developer himself).

3. A developer is usually working under high pressures to implement requirements asap. Due which quality is compromised a lot many times. So, it’s better to have a separate tester.

4. Testing is not about just validating that system is performing what it is supposed to, but importantly it is also about assuring that the system is not performing what it is not supposed to perform. A tester delicately does that and does it better.

Testing needs a NEGATIVE approach, which a developer doesn’t have. TESTING IS DESTRUCTIVE PROCESS : A CREATIVE DESTRUCTION
Testing is not just after-programming evaluation, but it is key integral part of each phase of SDLC.
Testers try to find faults, developers try to take pride. (oops.. no hard feeling guys)

5. The more early a defect is found the less costly it is to fix.

I may sound aggressive at some lines; it’s not because I hate developers or there is some personal grudge against, but it’s rather to emphasize the necessity and my answer to Why Tester :).

Jointly, developers and testers fulfill the requirement successfully in favour of their employers and clients.

Anyways, I wanted to answer “Why Tester” without using technical-words and in fun way. I hope this is sufficient to explain why a software tester is required.

I’ll leave you with two lines as post-script:

If we’re perfect, there’d not be tester or QA and we know that nobody is perfect!!

Testers pursue defects, not the people behind the defects!!

10 Jan

MySQL variable innodb_flush_method – summarized

innodb_flush_method variable specifies how InnoDB opens and flushes log and data files. In Innodb optimization, setting the variable innodb_flush_method tweaks the performance most of the times but there are cases of otherwise though. If innodb_flush_method is set to O_DIRECT it’d avoid double buffering and reduce swap pressure and helps improving performance.

innodb_flush_method has been covered at a lot of places and this is a quick reference to myself.

innodb_flush_method:

innodb_flush_method On non-Windows systems:

1. fdatasync:
- default
- InnoDB uses fsync() to flush both data and log files (though fdatasync is specified).
- fdatasync() flushes only data while fsync() also flushes metadata along with file’s data ( & thus causes more IO).
- fsync() causes double buffering. i.e. operating system buffers at least some of the data in its own cache even though InnoDB is managing it in it’s own buffers.
- When innodb_file_per_table is set, writes on multiple tables causes multiple fsync() calls as they cannot be combined in single IO.

2. O_DIRECT:
- Applies only for data files and not logs.
- O_DIRECT uses fsync() method to flush files to disk and ensures no double buffering on system.
- All reads and writes goes directly to disk.
- This setting disbles OS’s double buffering & read ahead while flushing.
- Implemented by setting O_DIRECT flag of fcntl() or directio() on Solaris.
- If innodb_file_per_table is not set, performance suffers due to serialized IO caused by inode-level mutex.

3. O_DSYNC:
- Applies only to log files and not data files.
- Sets O_SYNC flag of open() and makes all writes synchronous.
- O_SYNC doesn’t disable double buffering / caching at system level.
- O_DSYNC flushes only data while O_SYNC flushes both data and metadata.

innodb_flush_method On Windows systems:

1. async_unbuffered:
- unbuffered IO => data has been written / flushed before continue next.
- InnoDB to use unbuffered I/O for most writes.
- If innodb_flush_log_at_trx_commit = 2, InnoDB uses buffered I/O to the log files.
- This causes InnoDB to use Windows native Async IO for both Reads/Writes.
- For older version of Windows, InnoDB uses it’s own Async IO.

2. unbuffered:
- Same as async_unbuffered except here native async. IO is not used.

3. Normal:
- This option tells to use neither unbuffered IO nor native async. IO.

17 Dec

Is your backup complete? – backup MySQL users privileges / grants – pt-show-grants

Everybody knows the criticality of backup. You might have had your database dumped, data directory copied, flat files exported and even config and binary log files backed-up. But did you backup your MySQL user privileges?

One can easily forget to backup the user privileges but it is much easier to do and you should backup your MySQL server user privileges / grants regularly.

1. Backup MySQL user privileges using mysqldump:

$] mysqldump -uUSER -pPASS --databases mysql > mysql.sql

2. Copy MySQL data directory:

$] cd /var/lib/mysql/ [ assuming default data directory ] mysql> flush tables with read lock; $] cp -R mysql /path/to/backup/mysql_grants_dir mysql> unlock tables

3. Using Percona tool pt-show-grants:

pt-show-grants shows grants (user privileges) from a MySQL server. It can also write REVOKE and DROP user statements as required.

If you donot have Percona Tools already installed you may download individual tool as follows:
$] wget percona.com/get/pt-show-grants $] chmod +x pt-show-grants $] pt-show-grants -uUSER -pPASSWORD > user_grants.sql

You may also include following options:
–separate: Write grants with individual privileges
–revoke: Write revoke grants for all grants
–drop: Add drop user command before grant syntax

For more info:
$] perldoc pt-show-grants

Using pt-show-grants:
Getting MySQL user grants for specified user:
root@ubuntu:~# pt-show-grants -uroot -pkedar --revoke --drop --only kedar -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.5.27-0ubuntu2-log at 2012-12-17 04:54:34 -- Revoke statements for 'kedar'@'localhost' REVOKE LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* FROM 'kedar'@'localhost'; DROP USER 'kedar'@'localhost'; DELETE FROM `mysql`.`user` WHERE `User`='kedar' AND `Host`='localhost'; -- Grants for 'kedar'@'localhost' GRANT LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO 'kedar'@'localhost';
You can also specify comma separated list of users.

Advantages / use:
- Easily replicate users from one server to another.

// Creating user USER_X on server B like that on server A pt-show-grants -uUSER -pPASS -hSERVER_A --only USER_X | mysql -uUSER -pPASS -hSERVER_B

- Unified sorted canonicalized grant syntaxes to put in version control
- As pt-show-grants canonicalize the grant syntaxes, it’d be good to do “diff” the grants of two different MySQL instances.
- Easily generate revoke syntaxes for particular user/ users:
root@ubuntu:~# pt-show-grants -uroot -pkedar --revoke --only kedar | grep REVOKE REVOKE LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* FROM 'kedar'@'localhost';

13 Sep

MySQL function returns dates of specified day & between date range

This mysql stored function will return you all the dates between given dates having specified day.
In short if you say: give me all Sundays in this month.

I have following for you:

mysql> select Give_Me_Dates_Days(1,'2012-09-01','2012-10-01') as All_Sundays; +--------------------------------------------------------+ | All_Sundays | +--------------------------------------------------------+ | 2012-09-02,2012-09-09,2012-09-16,2012-09-23,2012-09-30 | +--------------------------------------------------------+

The function parameters are as follows: Give_Me_Dates_Days(DAY_IDENTIFIER,START_DATE,END_DATE)

Sunday = 1 (and rest you can figure-out).

[ Ofcourse I've not done much validations eg. start_date < end_date. ]

Check the code for getting dates of specified days between a date-range as follows. You may download the sql here:Give_Me_Dates_Days.sql

DELIMITER $$ DROP function IF EXISTS `Give_Me_Dates_Days` $$ CREATE function `Give_Me_Dates_Days` (in_day int, in_date1 timestamp, in_date2 timestamp) returns varchar(4000) deterministic BEGIN DECLARE tot_dates int; DECLARE proc_date timestamp; DECLARE dates varchar(4000) default ''; #1= sunday # drop temporary table if exists selecteddates; # create temporary table selecteddates ( dates timestamp ); set proc_date=in_date1; while proc_date < in_date2 do if (dayofweek(proc_date)=in_day) then set dates=concat(dates,date(proc_date),','); # insert into selecteddates values (proc_date); end if; set proc_date=date_add(proc_date, interval 1 day); end while; return trim(trailing ',' from dates); END $$ DELIMITER ;