… that enables you to ALTER large tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online-schema-change performs the ALTER… blocker in the critical path of your project plan. No more! The pt-online-schema-change tool is not new to…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… may not be enough since there can be a lot more events from the time of the snapshots until you discover….15 sec) mysql> drop table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add column age smallint… is when you are planning to execute a long running ALTER or server upgrade, this method can be good quick rollback…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
…-per-table –target-dir=/mnt/mysql/export/ –tables=data # xtrabackup_55 –prepare –export –innodb-file-per-table –target-dir=/mnt/mysql/export ALTER TABLE data DISCARD TABLESPACE; # replace ibd with the export files SET GLOBAL innodb_import_table_from_xtrabackup = 1; ALTER TABLE data IMPORT…/refman/5.5/en/forcing-innodb-recovery.html InnoDB: for more information.
Comment: Best kept MySQLDump Secret
March 25, 2012 By 11 Comments
I wrote about the alter table issue a few years ago and filed bug http://bugs.mysql.com/bug.php?id=28432 which was incorrectly closed as fixed. I’m surprised that more people weren’t upset about it. Maybe I should have added notes about mysqldump.
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
… ENUM values. When it comes to changes that really require table to be rebuilt – adding/dropping columns or indexes, changing data… assigned to db1 at this stage. If you’re altering gigabyte size tables, this step may take some time. Go get a… if you’re not using MMM though it takes few more steps i.e. you must change application to use different…
Post: MySQL Slow query log in the table
May 31, 2007 By Leave a Comment
… log table implementation in MySQL is well though of in regards to avoiding trouble. For example you can’t ALTER log table while… as normal DELETE statements are blocked. More than that. I found even if I convert table to MyISAM, disable concurrent inserts and… queries touching given table quickly which is now hard to do reliably. I guess over time we would have more tools provided…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int… works well with both – InnoDB and MyISAM. Now that more and more folks hit the InnoDB auto-inc scalability issue with MySQL… auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to rebuild…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… to take a lot of time. Think about 500GB table for example – ALTER TABLE make take days or even weeks depending on your…” solutions but have to do more complicated things instead. You typically can’t simply run ALTER TABLE because table will stay locked for too long you would need to do careful process of ALTERing table on the…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
… of recovering by hand nearly 200 tables was not really exciting. I needed something faster and more automatic so, after reading this… by creating fake Innodb tables Once the Innodb tablespace id is minus one of a customer table alter the table definition from MyISAM to Innodb Discard the tablespace Replace the table ibd file with the…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… rows from a table in order to recover the table later. Most of these limitations are solved on Percona Server . More info about… only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy… to import tables from different Server instance, when table was altered or truncated in the meanwhile. Though this only works if table was “exported…
