Tag Archives: MySQL

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

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

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 :)

10 Jan

MySQL variable innodb_flush_method – summarized

innodb_flush_method

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 ;

05 Jul

Generate random test data for MySQL using routines

At times you’ll find yourself responsible for generating test data for newly created tables for testing or sampling purpose. There are tools that will generate random data for you but they’re not free. At-times you’ll write scripts to generate data but those will be table specific.

I hate generating dummy data, yes I do and I assume you do too! I think that’s the major reason I wrote these MySQL functions and procedures for Generating dummy test data.

People can manage these things with a simple perl / shell script with loops but again that always need your time.
Why can’t MySQL generate data for it’s own table when MySQL better knows the table than anyone else !! :)

Below are a set of functions and a stored procedure that will make our life easy and of-course it’s free ;).

Download the sql code: Generate dummy data for MySQL.

How to install and generate dummy data:
mysql -uUSER -pPASSWORD DATABASE < populate_dummy_data.txt

How use installed functions to generate test data:
- To generate test data of 1000 rows for sakila.film table execute following sql command:
call populate('sakila','film',1000,'N');

MySQL set of functions to get random values generated for individual data-types.

## MySQL function to generate random string of specified length
DROP function if exists get_string;
delimiter $$
CREATE FUNCTION get_string(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
set @var:='';
while(in_strlen>0) do
set @var:=concat(@var,IFNULL(ELT(1+FLOOR(RAND() * 53), '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',' ','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'),'Kedar'));
set in_strlen:=in_strlen-1;
end while;
RETURN @var;
END $$
delimiter ;

## MySQL function to generate random Enum-ID from specified enum definition
DELIMITER $$
DROP FUNCTION IF EXISTS get_enum $$
CREATE FUNCTION get_enum(col_type varchar(100)) RETURNS VARCHAR(100) DETERMINISTIC
RETURN if((@var:=ceil(rand()*10)) > (length(col_type)-length(replace(col_type,',',''))+1),(length(col_type)-length(replace(col_type,',',''))+1),@var);
$$
DELIMITER ;

## MySQL function to generate random float value from specified precision and scale.
DELIMITER $$
DROP FUNCTION IF EXISTS get_float $$
CREATE FUNCTION get_float(in_precision int, in_scale int) RETURNS VARCHAR(100) DETERMINISTIC
RETURN round(rand()*pow(10,(in_precision-in_scale)),in_scale)
$$
DELIMITER ;

## MySQL function to generate random date (of year 2012).
DELIMITER $$
DROP FUNCTION IF EXISTS get_date $$
CREATE FUNCTION get_date() RETURNS VARCHAR(10) DETERMINISTIC
RETURN DATE(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))
$$
DELIMITER ;

## MySQL function to generate random time.
DELIMITER $$
DROP FUNCTION IF EXISTS get_time $$
CREATE FUNCTION get_time() RETURNS INTEGER DETERMINISTIC
RETURN TIME(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))
$$
DELIMITER ;

## MySQL function to generate random int.
DELIMITER $$
DROP FUNCTION IF EXISTS get_int $$
CREATE FUNCTION get_int() RETURNS INTEGER DETERMINISTIC
RETURN floor(rand()*10000000)
$$
DELIMITER ;

## MySQL function to generate random tinyint.
DELIMITER $$
DROP FUNCTION IF EXISTS get_tinyint $$
CREATE FUNCTION get_tinyint() RETURNS INTEGER DETERMINISTIC
RETURN floor(rand()*100)
$$
DELIMITER ;

## MySQL function to generate random varchar column of specified length(alpha-numeric string).
DELIMITER $$
DROP FUNCTION IF EXISTS get_varchar $$
CREATE FUNCTION get_varchar(in_length int) RETURNS VARCHAR(500) DETERMINISTIC
RETURN SUBSTRING(MD5(RAND()) FROM 1 FOR in_length)
$$
DELIMITER ;

## MySQL function to generate random datetime value (any datetime of year 2012).
DELIMITER $$
DROP FUNCTION IF EXISTS get_datetime $$
CREATE FUNCTION get_datetime() RETURNS VARCHAR(20) DETERMINISTIC
RETURN FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200)
$$
DELIMITER ;

The MySQL Stored procedure that populates MySQL tables with dummy data:

DELIMITER $$
DROP PROCEDURE IF EXISTS populate $$
CREATE PROCEDURE populate(in_db varchar(20), in_table varchar(20), in_rows int, in_debug char(1))
BEGIN
/*
|
| Developer: Kedar Vaijanapurkar
| USAGE: call populate('DATABASE-NAME','TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE);
| EXAMPLE: call populate('sakila','film',100,'N');
| Debug-mode will print an SQL that's executed and iterated.
|
*/

DECLARE col_name VARCHAR(100);
DECLARE col_type VARCHAR(100);
DECLARE col_datatype VARCHAR(100);
DECLARE col_maxlen VARCHAR(100);
DECLARE col_extra VARCHAR(100);
DECLARE col_num_precision VARCHAR(100);
DECLARE col_num_scale VARCHAR(100);
DECLARE func_query VARCHAR(1000);
DECLARE i INT;

DECLARE done INT DEFAULT 0;
DECLARE cur_datatype cursor FOR
SELECT column_name,COLUMN_TYPE,data_type,CHARACTER_MAXIMUM_LENGTH,EXTRA,NUMERIC_PRECISION,NUMERIC_SCALE FROM information_schema.columns WHERE table_name=in_table AND table_schema=in_db;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET func_query='';

OPEN cur_datatype;
datatype_loop: loop
FETCH cur_datatype INTO col_name, col_type, col_datatype, col_maxlen, col_extra, col_num_precision, col_num_scale;
#SELECT CONCAT(col_name,"-", col_type,"-", col_datatype,"-", IFNULL(col_maxlen,'NULL'),"-", IFNULL(col_extra,'NULL')) AS VALS;

IF (done = 1) THEN
leave datatype_loop;
END IF;

CASE
WHEN col_extra='auto_increment' THEN SET func_query=concat(func_query,'NULL, ');
WHEN col_datatype in ('int','bigint') THEN SET func_query=concat(func_query,'get_int(), ');
WHEN col_datatype in ('varchar','char') THEN SET func_query=concat(func_query,'get_string(',ifnull(col_maxlen,0),'), ');
WHEN col_datatype in ('tinyint', 'smallint','year') or col_datatype='mediumint' THEN SET func_query=concat(func_query,'get_tinyint(), ');
WHEN col_datatype in ('datetime','timestamp') THEN SET func_query=concat(func_query,'get_datetime(), ');
WHEN col_datatype in ('float', 'decimal') THEN SET func_query=concat(func_query,'get_float(',col_num_precision,',',col_num_scale,'), ');
WHEN col_datatype in ('enum','set') THEN SET func_query=concat(func_query,'get_enum("',col_type,'"), ');
ELSE SET func_query=concat(func_query,'get_varchar(',ifnull(col_maxlen,0),'), ');
END CASE;
end loop datatype_loop;
close cur_datatype;

SET func_query=trim(trailing ', ' FROM func_query);
SET @func_query=concat("INSERT INTO ", in_db,".",in_table," VALUES (",func_query,");");
IF in_debug='Y' THEN
select @func_query;
END IF;
SET i=in_rows;
populate :loop
WHILE (i>0) DO
PREPARE t_stmt FROM @func_query;
EXECUTE t_stmt;
SET i=i-1;
END WHILE;
LEAVE populate;
END LOOP populate;
SELECT "Kedar Vaijanapurkar" AS "Developed by";
END
$$
DELIMITER ;

Let me know if you like it and share if you find it useful. Please report any bug.
Below is the code for review:

Edit: I'd like to mention Ronald Speelman's post [http://moinne.com/blog/ronald/mysql/howto-generate-meaningful-test-data-using-a-mysql-function] of generating meaningful test data is great and it's something I'd like to work towards.

-- Kedar Vaijanapurkar --