Shantanu's Blog

Here is a patched version of mysqldump that will improve the restoration speed by removing the secondary and unique keys from the table. This new mysqldump.static will add one line "alter table drop key xyz", immediately after the create table statement. The insert statement speed is improved significantly once the extra keys are removed. Once the data is inserted, we can put the keys back as and when required. This type of data without keys will be very useful for testing and backup purpose. This utility can be downloaded from...

http://code.google.com/p/mysqldump/downloads/detail?name=mysqldump.static

This is a fix provided as per the feature request mentioned here...

http://bugs.mysql.com/bug.php?id=64248

Following is the standard dump that we all are familiar with...

# mysqldump cas > cas.sql

CREATE TABLE `cas_level_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cas_user__id` bigint(20) NOT NULL,
  `cas_user_created_datetime` datetime DEFAULT NULL,
  `cas_is_valid_reference` tinyint(4) DEFAULT NULL,
  `cas_level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cas_user__id` (`cas_user_id`),
  KEY `idx_cas_is_valid_reference` (`cas_is_valid_reference`),
  KEY `idx_cas_level` (`cas_level`),
  KEY `idx_cas_user_created_datetime_cas_is_valid_reference` (`cas_user_created_datetime`,`cas_is_valid_reference`)
) ENGINE=InnoDB AUTO_INCREMENT=1267454 DEFAULT CHARSET=latin1;

I will like to remove the ENGINE=InnoDB part from the above statement. I will add "default-storage-engine=MyISAM" parameter to my.cnf
If there is no engine specified in the create table statement, it will be created as a MyISAM table. This will make the insert statements faster.
_____

# mysqldump.static cas --compatible=no_table_options --keys-remove --socket=/var/lib/mysql/mysql.sock > cas_static.sql

CREATE TABLE `cas_level_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cas_user_id` bigint(20) NOT NULL,
  `cas_user_created_datetime` datetime DEFAULT NULL,
  `cas_is_valid_reference` tinyint(4) DEFAULT NULL,
  `cas_level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cas_user_caller_id` (`cas_user_id`),
  KEY `idx_cas_is_valid_reference` (`cas_is_valid_reference`),
  KEY `idx_cas_level` (`cas_level`),
  KEY `idx_cas_user_created_datetime_cas_is_valid_reference` (`cas_user_created_datetime`,`cas_is_valid_reference`)
);

ALTER TABLE `cas_level_info` DROP KEY `idx_cas_user_id` , DROP KEY `idx_cas_is_valid_reference` , DROP KEY `idx_cas_level` , DROP KEY `idx_cas_user_created_datetime_cas_is_valid_reference` ;

The alter table drop key statement is added immediately next to create table statement. This will make sure that all the non-primary keys are removed before data is inserted. If you want the keys to be added once all the data is restored, use "--keys-last" option instead of "--keys-remove"

# mysqldump.static cas --compatible=no_table_options --keys-last --socket=/var/lib/mysql/mysql.sock > cas_static.sql

The keys last option will add the following alter table add key statement.

ALTER TABLE `cas_level_info` ADD KEY `idx_cas_user_id` (`cas_user_id`), ADD KEY `idx_cas_is_valid_reference` (`cas_is_valid_reference`), ADD KEY `idx_cas_level` (`cas_level`), ADD KEY `idx_cas_user_created_datetime_cas_is_valid_reference` (`cas_user_created_datetime`,`cas_is_valid_reference`);

*** Removing keys can restore the data upto 50% faster. ***
_____


# You can decide to build the keys later using keys-last or simply remove the secondary and unique keys except primary.
# Bug #64248 create secondary indexes after inserting rows statements in mysqldump
# http://bugs.mysql.com/bug.php?id=64248

--keys-last
--keys-remove 
# Certain SQL statements can be written at the top as well as at the end of the dump file.
# Bug #39233 --sql-append --sql-prepend options to mysqldump
# http://bugs.mysql.com/bug.php?id=39233

--sql-prepend="SET bulk_insert_buffer_size = 1024 * 1024 * 256;"
--sql-append="show variables like '%buffer%';"  
# You can now add schema names to the insert into statements. This will be especially helpful while using with 'skip-extended-insert'
# create table db_name.table_name and insert into db_name.table_name
# Bug #62069 adding schema name in mysqldump output
# http://bugs.mysql.com/bug.php?id=62069

--add-schema-name 
# You can now change the table options like auto-increment, default engine and also default character-set
# Bug #20786 mysqldump always includes AUTO_INCREMENT
# http://bugs.mysql.com/bug.php?id=20786

--remove-auto-increment
--default-engine=myisam
--default-charset=utf8_____

You may be asked to provide the socket address for e.g.
--socket=/var/lib/mysql/mysql.sock

The new dump command will now look something like this...

mysqldump.static  --socket=/var/lib/mysql/mysql.sock --keys-remove --sql-prepend="SET bulk_insert_buffer_size = 1024 * 1024 * 256;" --sql-append="show variables like '%buffer%'; select now();"  --add-schema-name  --remove-auto-increment --default-engine=myisam db_name table_name > new_dump.sql
_____

# use the following 2 commands to install
wget http://mysqldump.googlecode.com/files/mysqldump.static --output-document=/usr/bin/mysqldump.static
chmod 777 /usr/bin/mysqldump.static